Extracting long metadata Textstore (>32K)

[DavePrinsloo]

I need to extract and create text files for each stored process in a metadata path.   I got a macro that uses the metadata API - metadata object  functions to  identify each relevant stored process and extracts the source code from the TextStore metadata object.  Unfortunately, some stored processes have more code than fits into the longest possible  SAS variable $32767.    How can I access the rest of the TextStore object?

Once I identify the TextStore object I have code :

length temp_storedtext $32767 ;
objrc=metadata_getattr(&text_uri.,"StoredText",temp_storedtext);

The stored processes have been generated in Enterprise Guide over a number of years by a number of business and power users.   

The macro will have to run on a userid that has read  metadata access on all relevant metadata folders.    

It is NOT an option to go into each STP to manually copy the source code. 

https://communities.sas.com/t5/SAS-Programming/SAS-9-4-Extract-long-texts-from-SAS-Metadata-TextStor... hints at a solution, that the metadata is stored in multiple rows in one of the underlying tables.  I would like to know how I could access these tables!

[Patrick · Accepted solution]

You could use Proc Metadata to pull out the XML and then parse out the text between the <TextStore> tags.

You could also create a .spk from one of the Stored Processes with a lot of code in TextStore, then open the package with 7-zip or the like and inspect the XML if a text store really can contain more than 32KB or if it gets split into multiple ones and you just would need to amend your data step query.

[DavePrinsloo]

The TextsStore object in the .spk is indeed one very long string.
I will now try with proc metadata and parse the TextStore.

My current code splits lines on line-feeds and carriage returns and on semicolons which was pretty good for code < 32K. i.e it splits on '0A'x '0D'x and ';' which makes for readable code

[Patrick]

It always takes me an unreasonable amount of time and try-and-error to get to a request XML which returns a response XML with what I want in it. But I guess you're smarter than I am and especially because you're already able to get the TextStore URI it's eventually no more that hard for you to create "reasonable" XML's which you then can parse to pull out the code.

Not sure how the code in the response XML will be formatted. May be you're going to have the joy to read it as a data stream (recfm=s)...

[DavePrinsloo]

Thats good to know:) I will then use the function and if the result string is long - and does not include %STPEND then I know it is cut ff, so I will collect the URIs and the use proc metadata just for the "problem" URIs.

I will share the full solution here when finished. (Its not top priority now, so I wont get a chance to do this in the next week or so)

[DavePrinsloo]

Here is the code I promised a couple weeks ago.  This macro get_stpcode_via_xml is called from inside  a "fairly" standard another macro which uses metadata functions to find objects using code like

metadata_getnobj("omsobj:ClassifierMap?@Id contains '.'",obj_count,obj_uri);
...
rc = metadata_getattr(obj_uri, 'publictype', publictype_nm);

and then and restricting parsing to publictype_nm='StoredProcess'

/* Macro get_stpcode_via_xml uses proc metadata to extract the source code
*of a stored process for a specific uri and cleans xml escape strings  
* --------------------------------------------------------------------------------*/
%macro get_stpcode_via_xml(text_uri=, out_table=stp_code
         , stp_name=, parent_name=, fullpath_txt=, source_code_outpath=, 
          MetadataUpdated_dttm=);



/* debug: 
%put CALLING: get_stpcode_via_xml(&=text_uri  &=out_table. ;
%put       &=stp_name= &=parent_name ;
%put       &=fullpath_txt ;
%put       &=source_code_outpath;
%put       &=MetadataUpdated_dttm.);*/
filename _in_xml TEMP;
filename _out_xml TEMP;

data _null_;
length string $100;
string=' <TextStore Id="'||"&text_uri."||'"/>';
file _in_xml;
put '<GetMetadata>'
  / ' <Metadata>'
  / string
  / ' </Metadata>'
  / ' <Ns>SAS</Ns>'
  / ' <flags>8</flags>'
  / ' <Options/>'
  / ' </GetMetadata>'
  /;
run;

proc metadata in=_in_xml out=_out_xml header=none ;
run;

data &out_table.(keep=uri code_txt);
length code_txt $1024;
/* Code lines are in a very long string and separated by &#x0a; */
infile  _out_xml dlmstr='&#x0a;' recfm=V lrecl=16000000 flowover end=eof;
/* output to .sas file */
file stp_file;

retain uri "&text_uri." now ;

input code_txt @@;
%gen_stp_header_text 

if eof and index(code_txt,'TextRole="StoredProcessSourceCode"') then delete;

/* translate tab-marker to blanks */
code_txt=tranwrd(code_txt,'&#x09;','09'x);
code_txt=tranwrd(code_txt,'&amp;','&');
code_txt=tranwrd(code_txt,'&quot;','"');

put code_txt $char.;
run;
filename _in_xml clear;
filename _out_xml clear;
%mend get_stpcode_via_xml;
/* test ...
%get_stpcode_via_xml(text_uri=A5JGVV7O.AG002RRL);


/* The code calls another macro to write some information into the output .sas file using this macro*/
%macro gen_stp_header_text;
if _n_=1 then do;
    now = datetime();
    /* write header */
    put "/* Code for STP: &stp_name.*/";
	put "/* STP Location: &fullpath_txt.*/";
    put "/* MetadataUpdated: &MetadataUpdated_dttm.   Metadata Id : &textstore_uri.*/"; 
	put "/* STP Location: &fullpath_txt.*/";
    put "/* Code Extracted on : " now datetime. " */"; 
    put "/* -------------------------------------------------------------------------*/"; 
    /* find StoredText=*/
    findpos1=index(code_txt,'StoredText');
    /* skip over Stored Text= */
	code_txt=substrn(code_txt,findpos1+12);
end;
%mend gen_stp_header_text;