Exporting Stored procedures and functions

[Former Member]

I have written a function to export the procedure definitions from my ASA 10 database. The idea was to create a seperate sql file for each of them so that we could add them to our source control system.

  declare view_defs no scroll cursor for select proc_name,proc_defn from sys.sysprocedure 
            where creator in (1,655) order by proc_name;
  declare err_notfound exception for sqlstate value '02000';

  open view_defs;
      export_defs: loop
        fetch next view_defs into @ls_name,@ls_def;
        if sqlstate = err_notfound then
          leave export_defs
        end if;

        SELECT DB_PROPERTY ( 'alias' ) into @ls_dbname;

        if @ls_dbname = 'vprod' then
            set @ls_dir = 'prod1\\\\';
        else
            set @ls_dir = 'test\\\\';
        end if;

        Set @ls_filename = '\\\\\\\\mycomp\\\\c$\\\\nsiapps\\\\db\\\\'||@ls_dir||@ls_name||'.sql';

        unload select proc_defn from sys.sysprocedure where proc_name = @ls_name to @ls_filename
            quotes off format ascii;

      end loop export_defs;
  close view_defs;

The problem that I have is I get \ for line feeds and everthing on one line. I have tried specifing rows delimited by \ and many other of the options listed for unload in the help text. Cant seem to come up with a combination that would give me the correctly formatted text. I can copy and paste them into the files one by one but that seems like a rather time consuming process. Any help would be appreciated.

[VolkerBarth]

Just something to add:

Of course the whole CR\\LF problem is moot if you store your SQL scripts in the source control system before/while you build your database objects - then you don't have to extract them afterwards...

And as Breck has stated, if option PRESERVE_SOURCE_FORMAT is set (as default), the database will store your texts as is.

Yes, I'm aware that this is some kind of post-mortem advice:)

[Former Member]

Agreed on the moot aspect, however the database has never had anything stored in source control before. Seemed like a good idea to move it over there. I am a developer that has been 'elected' to be the dba, kind of learning on the fly here. We do full nightly backups and periodically I export and save the schema, but I thought moving things to source control would be a good idea. We use MS Source Safe currently, not ideal but what we have in house. The database is approx 26 gig. So once we get the scripts in source control we will edit them there in the future.

Thanks for the insights.

[Breck_Carter · Accepted solution]

In addition to John's suggestion for ESCAPES OFF, you might want to use SYSPROCEDURE.source if formatting has been preserved; see the COALESCE() call in the example below.

Also, the cursor FETCH has already filled in @ls_def so the UNLOAD SELECT doesn't need to retrieve from SYSPROCEDURE again.

The following code has been slightly changed to work on my computer...

BEGIN
DECLARE @ls_name     VARCHAR ( 1000 );
DECLARE @ls_def      LONG VARCHAR;
DECLARE @ls_filename VARCHAR ( 1000 );

declare view_defs no scroll cursor for select proc_name,coalesce ( source, proc_defn ) from sys.sysprocedure 
            where USER_NAME ( creator ) = 'DBA' order by proc_name;
  declare err_notfound exception for sqlstate value '02000';

open view_defs;
      export_defs: loop
        fetch next view_defs into @ls_name,@ls_def;
        if sqlstate = err_notfound then
          leave export_defs
        end if;

Set @ls_filename = 'c:\\\\temp\\\\procs\\\\'||@ls_name||'.sql';

unload select @ls_def to @ls_filename
            escapes off quotes off format ascii;

end loop export_defs;
  close view_defs;

END;

[Former Member · Accepted solution]

Brecks export code is excellent. I would like to point out that it is possible to add the codes for recreating the comments on procedure and functions. comments on procedure is remarks in sys.sysprocedure

so include that in the cursor and retrieve into @ls_remark. one could recreate the comment at the end of the procedure definition by replacing the unload statement with unload select @ls_def||'x0Dx0ACOMMENT on PROCEDURE DBA.' || @ls_name ||' is x27' || @ls_remarks || 'x27' to @ls_filename escapes off quotes off format ascii;

[Former Member · Accepted solution]

sorry, some how the backward slash before x27 is missing in the above answer

[Former Member · Accepted solution]

BTW, I discovered the hard way on sql anywhere version 11.0.1.2506 that using declare for the remarks lead to truncation of long comment (only 548 bytes) despite the type being long varchar or varchar(2000)

I had to use instead create variable for @ls_remarks as long varchar to avoid the truncation problem

Caution, create variabe must be after all declare including cursor.

if used in stored procedure, one should drop the variable before ending the stored procedure

[Former Member]

Thanks for all of the ideas, I reformatted the query using the example that Breck supplied and all seems to be well with the exported files now. I did verify that preserve_source_format is on. Thanks again for all the various tips and responses, great to find this resource as I am sure I will have many questions in the future.

[Former Member]

Is this a one-off exercise or are you expecting to continually query your db for updated source?

I am tempted to wonder if your source control system is not being used to its full capacity, can't it query dbs and extract itself?

I have just run the Sybase Central Documentation Wizard and it perfectly formats the stored procedures in an html document(s). alternatively, this might meet your needs -

http://www.xsqlsoftware.com/LiteEdition.aspx

Cheers

Robin

[Former Member]

This was just to get the initial load into the source control system. Not sure what the capability is of MS Source Safe to query the database, but will investigate. Thanks for the thought.

[VolkerBarth]

Don't try to investigate - Visual SourceSafe does not have the ability to query databases, not even MS ones.

But that's no disadvantage IMHO, just manage your SQL scripts like other text files. FWIW, starting with SQL Anywhere 10.0, Interactive SQL can integrate with SCC systems like Visual SourceSafe.

[johnsmirnios]

Try adding the ESCAPES OFF clause.

[VolkerBarth]

What OS are you using? (From the export path, I guess it's Windows.)

AFAIK, SQL Anywhere uses \ as the line delimiter. That's more appropriate on Unix than on Windows which usually uses \\x0D\ .

When on Windows, I would try to open the exported scripts in Wordpad or other editors that can handle both kind of delimiters and check it the line breaks are displayed as wanted.

Alternatively, you might want to use the replace() function to change the contents - before writing to output file or in a further cycle to read the output/adapt it and write it back with the help of xp_readfile()/xp_writefile().

The following Q&A gives more hints...

---

EDIT: IMHO, even the "escapes off quotes off" option does not force SQL Anywhere to write a DOSish \\x0D\ line break (CR\\LF) as output - it still writes just a \ (LF).

To get the CR, too, for SQL Anywhere 9 you might want to replace this explicitly:

unload select replace(proc_defn, '\
', '\\x0D\
')
from sys.sysprocedure where proc_name = @ls_name
to @ls_filename escapes off quotes off format ascii;

For SQL Anywhere 10 and above, you could use the sa_split_list() procedure to replace the newline with the result set row delimiter - which will always be appropriate for the according platform, as documented in the cited Q&A. The following uses this method and works with SQL Anywhere 11 and above:

unload select row_value
from (select proc_defn from sysprocedure where proc_name = @ls_name) SP
   outer apply sa_split_list (SP.proc_defn, '\\n') 
to @ls_filename escapes off quotes off format ascii;