|
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;
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;
(14 Apr '12, 00:51)
G_G
Replies hidden
sorry, some how the backward slash before x27 is missing in the above answer
(14 Apr '12, 17:51)
G_G
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
(14 Apr '12, 21:07)
G_G
|
|
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. |
|
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 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.
(27 Jun '11, 09:24)
trexco
Replies hidden
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.
(27 Jun '11, 09:43)
Volker Barth
|
|
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. |
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:)
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.