Retrieve Formatted Procedure DDL

[Former Member]

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.

[Breck_Carter · Accepted solution]

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 🙂

---

[Former Member · Accepted solution]

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?

[Former Member · Accepted solution]

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.

[Breck_Carter · Accepted solution]

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).

[Former Member · Accepted solution]

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.

[MCMartin · Accepted solution]

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...)