How would you retrieve formatted stored procedure source from the SQL Anywhere system tables. I use the following:
But it cuts of the source for anything that has more than 30,000 characters. syscomments doesn't seem to be much better. asked 13 Jan '10, 00:12 Brad Wery |
This seems to be a client problem (limitation). I have just done a small test with 11.0.1.2341 and I can't reproduce your observation. Try it with dbisql and set beforehand in the options the truncation length of the displayed value to a big number. (see http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbjten10/jt-isql-options-appearance.html) answered 13 Jan '10, 12:16 Martin |
I'm with Martin... It probably depends on the client software and/or database interface you are using. For example, if you are using PowerBuilder and ODBC, you might have to use SELECTBLOB to get anything over 32,767 bytes in length (see sample code below). As Martin suggests, consider dbisql in SQL Anywhere 11.0.1... it's just another client program, and it defaults to a small value (256?) in the Tools - Options - SQL Anywhere - Results - Truncation length field. The effect of that truncation shows up if you run a SELECT and then do right mouse - Copy - Copy cell, and then paste into Wordpad or whatever. Change the Truncation length to a huge number like 1000000 and then the copy and paste works. Here's the PowerBuilder 10.5 sample code... pay close attention to the various length values shown in the MessageBox display at the end, especially the funky doubled-length 549748: Long ll_proc_length String ls_proc_name String ls_source Blob lb_source SQLCA.DBMS = 'ODB' SQLCA.DBParm & = "ConnectString='DSN=foxhound'," & + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'" CONNECT USING SQLCA; IF SQLCA.SQLCODE 0 THEN MessageBox ( 'Error', & 'CONNECT failed in bbb:' & + '~r~nSQLCode = ' & + String ( SQLCA.SQLCode ) & + '~r~nSQLDBCode = ' & + String ( SQLCA.SQLDBCode ) & + '~r~n' & + SQLCA.SQLErrText ) RETURN END IF SELECT TOP 1 LENGTH ( SYSPROCEDURE.source ) AS proc_length, SYSPROCEDURE.proc_name, SYSPROCEDURE.source INTO :ll_proc_length, :ls_proc_name, :ls_source FROM SYSPROCEDURE WHERE USER_NAME ( SYSPROCEDURE.creator ) = 'DBA' ORDER BY proc_length DESC USING SQLCA; IF SQLCA.SQLCODE 0 THEN MessageBox ( 'Error', & 'SELECT failed:' & + '~r~nSQLCode = ' & + String ( SQLCA.SQLCode ) & + '~r~nSQLDBCode = ' & + String ( SQLCA.SQLDBCode ) & + '~r~n' & + SQLCA.SQLErrText ) RETURN END IF SELECTBLOB SYSPROCEDURE.source INTO :lb_source FROM SYSPROCEDURE WHERE SYSPROCEDURE.proc_name = :ls_proc_name USING SQLCA; IF SQLCA.SQLCODE 0 THEN MessageBox ( 'Error', & 'SELECTBLOB failed:' & + '~r~nSQLCode = ' & + String ( SQLCA.SQLCode ) & + '~r~nSQLDBCode = ' & + String ( SQLCA.SQLDBCode ) & + '~r~n' & + SQLCA.SQLErrText ) RETURN END IF MessageBox ( "SELECT and SELECTBLOB", & String ( ll_proc_length ) & + '~r~n' & + ls_proc_name & + '~r~n' & + String ( Len ( ls_source ) ) & + '~r~n' & + String ( Len ( lb_source ) ) & + '~r~n' & + ls_proc_name & + '~r~n' & + Mid ( String ( lb_source ), 1, 500 ) & + '~r~n' & + " ... " & + '~r~n' & + Mid ( String ( lb_source ), ll_proc_length - 500 ) ) MessageBox ( "Everything", "OK" ) ...and yes, there is a 269K stored procedure in Foxhound :) answered 13 Jan '10, 12:33 Breck Carter I'm sure I'm doing something wrong. The column in SYSPROCEDURE that has the source is defined as varchar(32,767) and there is only one row for the procedure. Why would that be? Or is it just that the internals are hidden? Okay, I should have analyzed your response a little better. It confirms that retrieving this syntax can not be done by selecting from the system tables. This clarifies things. Thanks. SYSPROCEDURE.source is not VARCHAR(32767), it is LONG VARCHAR. Also, I don't know what you mean by "this syntax can not..." etcetera... the code I show was tested and it works and it uses the system tables (well, SYSPROCEDURE is a view in Version 11, but it's the same thing, and backward compatible to V9 and earlier). Sorry, what I meant was that I couldn't use a normal SELECT statement on this particular column. I had to use SELECTBLOB. Your suggested worked like a charm. Thanks. |