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
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;
What OS are you using? (From the export path, I guess it's Windows.)
AFAIK, SQL Anywhere uses \x0A as the line delimiter. That's more appropriate on Unix than on Windows which usually uses \x0D\x0A.
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\x0A line break (CR\LF) as output - it still writes just a \x0A (LF).
To get the CR, too, for SQL Anywhere 9 you might want to replace this explicitly:
unload select replace(proc_defn, '\x0A', '\x0D\x0A') 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;
Try adding the ESCAPES OFF clause.
answered 25 Jun '11, 07:04
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 -
answered 27 Jun '11, 03:59
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.
answered 27 Jun '11, 09:28