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.

asked 13 Jan '10, 00:12

Brad%20Wery's gravatar image

Brad Wery
382192126
accept rate: 0%


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)

permanent link

answered 13 Jan '10, 12:16

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

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


alt text

permanent link

answered 13 Jan '10, 12:33

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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?

(13 Jan '10, 14:51) Brad Wery

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.

(13 Jan '10, 15:25) Brad Wery

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

(14 Jan '10, 07:24) Breck Carter

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.

(14 Jan '10, 20:39) Brad Wery
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×32

question asked: 13 Jan '10, 00:12

question was seen: 2,985 times

last updated: 13 Jan '10, 12:33