• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • PowerServer Datastore insert with Oracle Stored Procedure Output parameter not populating datastore column

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved PowerServer Datastore insert with Oracle Stored Procedure Output parameter not populating datastore column

  1. Issue
0
Votes
Undo
  1. Jim Reese
    Jim Reese
  2. PowerServer
  3. Friday, 24 February 2023 17:15 PM UTC

We have a datastore using an Oracle Stored Procedure Update. For the insert stored proc, one of the columns is mapped to an output argument, to capture the new transaction sequence ID generated by the stored procedure. Client Server app correctly populates the datastore row with the new ID, but when running as a PowerServer app, the datastore column remains null after the insert takes place. The insert is completing in the DB, but the datastore column is not getting populated.

Is this a known issue? I can't find anything in the PowerServer Help about this.

 

  1. Oracle
  2. PowerServer
  3. Stored Procedure
  4. DataStore
Powerserver failed to run
Powerserserver and bitmap
Responses (4)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 24 February 2023 17:37 PM UTC
  2. PowerServer
  3. # 1

Hi Jim;

   Is this PowerServer 2021 or version 2022 and what build is it as well? Also, what Oracle version & build are you using?

Regards .... Chris 

Comment
Jim Reese
  1. Jim Reese
  2. Friday, 24 February 2023 18:24 PM UTC
PowerServer 2022 latest build 1892. Oracle version 19.0.0.0.0
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Friday, 24 February 2023 18:37 PM UTC
  2. PowerServer
  3. # 2

Hi Jim,

With PowerServer there are some unsupported features and differences compared to client/server.  Some of this is because of architectural difference between client/server and cloud.  Some of it is because of differences when executing the data access in .NET.  So when something doesn't work in PowerServer, I would recommend first taking careful look at the unsupported features guide: https://docs.appeon.com/ps2022/unsupported_features_guide.html

Best regards,
Armeen

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 25 February 2023 13:59 PM UTC
Apart from what Armeen said, which DBMS are you using in powerserver? Some time ago, I was told that I have to use the O10 driver, which really surprised me.

regards.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Saturday, 25 February 2023 17:49 PM UTC
Hi Miguel, PowerServer does all the data access in C# running on .NET 6 so must use the Oracle .NET provider. The configuration screen will automatically download the from Nuget the necessary package: https://docs.appeon.com/ps2022/Configuring_in_PowerBuilder_IDE_for_app_runtime.html
  1. Helpful 1
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 25 February 2023 18:48 PM UTC
Thanks Armeen,

Hmm, it must have been a powerserver version some years ago then. Good to hear that now it's easy!
  1. Helpful 1
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Sunday, 26 February 2023 16:52 PM UTC
  2. PowerServer
  3. # 3

Just a thought....

In the Oracle stored procedure, assign the OUTPUT argument to a separate internal variable, and use that internal variable to update the table.

Comment
Load more comments
Olan Knight
  1. Olan Knight
  2. Tuesday, 28 February 2023 17:56 PM UTC
Jim, you might be doing it already. This is what I meant:



// Stored Procedure script

procedure_name (string var_name_1 IN, int trans_seq OUT)

{ int local_trans_seq

begin

.

.

local_trans_seq := trans_seq;

.

.

// insert new row

// Assign local_trans_seq to the new row, NOT trans_seq

// Finish code

end;

/

}
  1. Helpful
Jim Reese
  1. Jim Reese
  2. Tuesday, 28 February 2023 18:20 PM UTC
Thanks for the clarification. This procedure predates me by many years, I'm less than a year on this job. But here it is, stripped of other non-essentials:



PROCEDURE p_cbxml_insert

(

p_cbid IN cb6.cbxml.cbid%TYPE

...

,p_transaction_seq OUT cb6.cbxml.transaction_seq%TYPE

) AS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN



SELECT cb6.cbseq_cbxml_seq.nextval

INTO p_transaction_seq /*Using the Out parm to hold the new sequence number, you're suggesting adding a new local variable and then setting the out to the local instead?*/

FROM dual;



INSERT INTO cb6.cbxml

(transaction_seq

,cbid

...

)

VALUES

(p_transaction_seq

,p_cbid

...

);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

RAISE;

ROLLBACK;

END p_cbxml_insert;
  1. Helpful
Olan Knight
  1. Olan Knight
  2. Tuesday, 28 February 2023 18:26 PM UTC
Correct. In the script above, replace "VALUES (p_transaction_seq, " with "VALUES (local_transaction_seq, "

where the first step in the script is to set

local_transaction_seq := p_transaction_seq;

  1. Helpful
There are no comments made yet.
David Xiong @Appeon
David Xiong @Appeon Accepted Answer Pending Moderation
  1. Monday, 27 February 2023 09:06 AM UTC
  2. PowerServer
  3. # 4

Hi Jim,

Thanks for reporting this issue. We have other customers reporting a similar issue. We suggest that you open a ticket at https://www.appeon.com/standardsupport/newbug and provide a reproduce test case, so that we can notify you to verify it when the issue is fixed.

Regards,
David

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.