Code in proc never executes?

[former_member319447]

Hi All, I am writing my first IQ proc. I am having a small problem. After selecting data from a table, the code after the select never executes.

Any ideas what I am doing wrong, if anything?

I set option DBA.on_tsql_error = 'Stop' and am logged on as DBA. There are no errors that I can see.

Here is the Watcom version:

create or replace procedure sp_proc2 ()

begin

    message 'hello sp_proc2' to client;

    message 'SQLSTATE ' || SQLSTATE to client;

    message 'SQLCODE ' || SQLCODE to client;

    select swapNum from t_Swap;

    message 'SQLSTATE ' || SQLSTATE to client;

    message 'SQLCODE ' || SQLCODE to client;

    message 'goodby sp_proc2' to client;

end;

grant execute on sp_proc2 to public;

Here is the TSQL version:

create or replace procedure sp_proc2 ()

as

begin

    message 'hello sp_proc2' to client

    message 'SQLSTATE ' || SQLSTATE to client

    message 'SQLCODE ' || SQLCODE to client

    select swapNum from t_Swap

    message 'SQLSTATE ' || SQLSTATE to client

    message 'SQLCODE ' || SQLCODE to client

    message 'goodby sp_proc2' to client

end

go

grant execute on sp_proc2 to public go

[jong-kil_park · Accepted solution]

Hi Mark,

I think you can resolve this problem by specifying a specific database option before executing the procedure as below.

---------------------------------------------------------------------------------------------------------------------------------

iq16:/home/iq16/WORK]

SET TEMPORARY OPTION ISQL_PRINT_RESULT_SET='ALL' ;

sp_proc2;

iq16:/home/iq16/WORK] dbisql -c dsn=jerry16 -nogui scn.sql

hello sp_proc2

SQLSTATE 00000

SQLCODE 0

          a

-----------

          1

(1 rows)

SQLSTATE 00000

SQLCODE 0

goodby sp_proc2

Execution time: 0.24 seconds

iq16:/home/iq16/WORK]

---------------------------------------------------------------------------------------------------------------------------------

HTH

Best Regards

Jerry

[former_member319447 · Accepted solution]

Ah yes. You guys are right. It's nice to have an actual example of what to type, so thanks Jerry.

BTW, I was also able to use isql (not dbisql) to do the same thing without the database option.

So, its a dbisql thing...

It looks like this makes the whole thing permanent:

(DBA)>set option isql_show_multiple_result_sets=on

(DBA)>set option isql_print_result_set=all

(DBA)>commit

thanks

[tayeb_hadjou]

For non GUI :

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00168.1604/doc/html/jcu1299098619103...

[former_member319447]

I am running the non GUI mode.   -nogui.

Your hyperlink references a solution for multiple result sets.

So "message" statements and a select statement in a proc constitutes multiple result sets?

[tayeb_hadjou]

Options

ISQL_SHOW_MULTIPLE_RESULT_SETS [=ON | OFF], Default OFF.

and/or

ISQL_PRINT_RESULT_SET [ = ALL | LAST | NONE]. Default LAAST.
impact message outputs too.

Regards,

Tayeb.

[markmumy]

Yes and no.  When dbisql receives the first result set from IQ, it stops processing ALL further operations.  In your case it is MESSAGE.  It could just as easily be a SET statement, SELECT, etc.  Regardless of subsequent operations, once dbisql sees results it stops processing the batch.  More legacy than anything else.  It was originally designed to get one result and move on to something different while ASE and Open Client were designed to process multiple result sets.

The easiest method is to simple set the database options so that regardless of the client type it will always get all results.

Mark

[carlosjuvenal_delgado]

Please Note:

None of these two options

( ISQL_SHOW_MULTIPLE_RESULT_SETS [=ON | OFF], Default OFF ,

ISQL_PRINT_RESULT_SET [ = ALL | LAST | NONE]. Default LAAST)  can be considered database options. If so ; you could set them within any stored proc. And , you can not.

They both are "clientish options "  only from the Realms of the client ( dbisql ) perspective. 

As pointed out in your message. There should be a database-server option for this behaviour making stored procs consistent regardless of the client used for its execution.

How may  stored proc code out there being  partially run by dbisql,perl , etc by not processing the whole result set .. Probably more than many !!

Regards,

cjd

　

[markmumy]

Keep in mind that the issue is not with the IQ server nor any options in the IQ server.  The issue is that the Interactive SQL (dbisql) tool, by default, shows the first result then stops.  To change the behavior, you need to change the tool configuration.

The option(s) that you set are actually intercepted by dbisql and proceeded inside the tool, not sent to the server.  When you wrap those options in a procedure, the client tool can't see the SQL to change its behavior.

You can either set the options manually when you login, change the dbisql options via the guy, or modify the dbisql preferences file (this is the file that the GUI changes).

Mark

[carlosjuvenal_delgado]

Mark,
That's the reason why they can not be called database options.. They are client (dbisql ) options..

Regards,
cjd

[markmumy]

I know and agree.  But we have the same thing with Open Client isql, too.  There are some "options" that are intercepted by isql, too.

[tayeb_hadjou]

Hi Mark,

Are you using dbisql GUI ?

For dbisql, check options : Messages. check box "Show separate messages pan"

SAP IQ/Result processing : select  2nd options :

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00168.1604/doc/html/jcu1286558875337...

Regards,

Tayeb.