PB crash when calling datawindow update via Stored Procedure

[Former Member]

Hi,

we are trying to update a datawindow via stored procedure update.

The stored has an output parameter.

When we call the update method, pb crashes.

if we sets the output parameter as UNUSED, all works fine.

any idea?

thanks in advance for any help

We are using pb 11.5.1 and oracle 11.2

[Former Member]

hi Chris,

we use the PB native driver and the oracle client 11.2 32 bit

these are the connection parameters:

SQLCA.DBMS = "ORA Oracle"

SQLCA.LogPass = "mylogpass"

SQLCA.ServerName = "myservername"

SQLCA.LogId = "mylogid"

SQLCA.AutoCommit = False

SQLCA.DBParm = "CommitOnDisconnect='No',PackageProcs=1,DisableBind=1,BindSPInput=1,Date=' ''''dd/mm/yyyy'''' ',DecimalSeparator=','"

[Former Member]

ok,

after some tests we found the scenario causing the crash:

in stored procedute update window

the first parameter is used in OUTPUT,

the second parameter is set as UNUSED,

the other parameters are in INPUT.

after a retrieve and a modify of a value of the dw the

calling dw_1.update, pb crash.

here an example for  test:

TABLE to update:

create table DUMMY_TABLE

(

  a VARCHAR2(256),

  b VARCHAR2(256)

)

;

insert into DUMMY_TABLE (a, b)

values ('uno', 'aa');

commit;

PROCEDURE:

create or replace procedure upd_dummy_table(p_result out varchar2,p_unused in varchar2 ,p_a in varchar2,p_b in varchar2) is

begin

  begin

    update dummy_table set b=p_b where a=p_a;

    p_result:='Ok';

    exception when others then

       p_result:='Ko';

       end ;

  return;

end upd_dummy_table;

datawindow:

in attached file: d_dummy_sp_update.srd

d_dummy_sp_update.srd.zip

[CobyKako]

Hello Alberto,

As I said in my first answer, root cause may be due to the argument's order in your DW stored procedure update.

If you change your SP DDL to map the order shown below, you won't get any crash !!!

create or replace

PROCEDURE upd_dummy_table(p_unused in varchar2 ,p_a in varchar2,p_b in varchar2, p_result out varchar2) AS

BEGIN

  begin

    update dummy_table set b=p_b where a=p_a;

    p_result:='Ok';

    exception when others then

       p_result:='Ko';

       end ;

  return;

END upd_dummy_table;

P.S: I know it is a workaround but it works

HTH

Jacob

[Former Member]

Thank you Jacob, we will try this workaround.

[Former Member]

Hi,

Thanks Chris and jacob for your reply,

we tested the same datawindow with pb 12.6 and we got the same result:

[Former Member]

OUCH .. what client connection mechanism are you using to talk to O11G?

[CobyKako]

Hello,

Can you share:

• The stored procedure DDL
• an export of your DataWindow
• the database connection settings used in your application

Can you enable the database tracing (e.g. SQLCA.DBMS = "trace ORA") and see what SQL update syntax the dbtrace.log file has generated (to redo it in a SQL*Plus session)?

Can you tell us if the last stored procedure parameter is an INPUT or not?

Jacob

[Former Member]

Hi Alberto;

  That sounds like a bug in PB 11.x. Can you try the SP with a supported version of PB - ie: 12.5 or 12.6?

Regards .. Chris

[CobyKako]

Hello Alberto,

I remember a very old Sybase bug (Change Request 304902) where the root cause was identified as is:

If the last parameter of your stored procedure is not passed by reference (e.g. INPUT) and previous parameters mapped to your DataWindow columns are defined as INPUT/OUTPUT, a PowerBuilder crash may occur.

You can find the CR description here: Sybase Search Results

Enter 304902 in the 1st field

HTH,

Jacob