on 2010 Jan 13 12:12 AM
How would you retrieve formatted stored procedure source from the SQL Anywhere system tables. I use the following:
SELECT IsNull(source, proc_defn) as 'text' FROM SYSPROCEDURE
where object_id = Object_Id('%1')
But it cuts of the source for anything that has more than 30,000 characters. syscomments doesn't seem to be much better.
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 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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).
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...)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
79 | |
11 | |
10 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.