Solved

Returning Into

Posted on 2004-04-13
6
786 Views
Last Modified: 2013-12-26
PB Version 8 using 8 oracle drivers
Database is Oracle 9i

I have a table I want to add a new row into.  The table has a primary key which is being created using a sequence.  The sequence is generated via a trigger.

I need to be able to get the new primary key from the new row to use in children rows.

I have heard of an oracle feature called RETURNING INTO.  It works something like this:

Insert Into schema.table
(col2, col3)
Values (:bind1, :bind2)
Returning col1 Into :variable
using sqlca;

if sqlca.sqlcode = -1 then
messagebox("Database Error",sqlca.sqlerrtext,Exclamation!)
rollback;
else
commit;
end if

This above statement executes fine but I can't seem to capture the col1 (aka primary key column) into a variable for later use.
0
Comment
Question by:Lordain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10819114
Is col defined as sequence column. If so see link below:

http://www.experts-exchange.com/Programming/Programming_Languages/PowerBuilder/Q_20882236.html

regards-
0
 
LVL 4

Author Comment

by:Lordain
ID: 10819929
Namasi -

The column I want to capture is the primary key (a sequence value set up in oracle).  The database has been set up to use a before insert trigger to get the nextval of the sequence to insert into the row.  Therefore, I can not get the sequence this way because I will not be able to insert a value into the row as the trigger will fire inserting a different number into the row.

I need to be able to determine what the trigger inserted for the primary key (sequence) at the time the insert call is made.  I understand returning into should do this.  The above insert SQL statement does work but not the returning into part (Oracle does not complain about the SQL statement).  The row is inserted with the sequence but I unable to determine what number was used for the primary key insertion because the trigger handles that insert for me.

What I believe is happening is Oracle does execute the Returning into part of the statment just fine but I can't seem to figure out how to capture the returning value in powerbuilder.
0
 
LVL 18

Accepted Solution

by:
diasroshan earned 250 total points
ID: 10820319
hi lordain,
i tried the following,,,,what u need is a workaround....
..instead of writing the insert statement in powerbuilder why don't u use a procedure to
insert into the table with the returning clause and return that value from the procedure....
...now u will get ur desired pk value as the procedures return value in powerbuilder script....

create table auto_increment (
  id                number not null constraint pk_ai primary key,
  something         number
);


create sequence seq_auto_increment start with 1 increment by 1;


create or replace trigger ai
  before insert on auto_increment
  for each row
  begin
    if :new.id is null then
      select seq_auto_increment.nextval into :new.id from dual;
    end if;
  end;
/


...use a procedure here to insert into ur table..... i'm sure u'll get the procedures return value in powerbuilder....

declare
  i number;
begin
  insert into auto_increment (something) values (9) returning id into i;
  dbms_output.put_line('id was: '||i);


  insert into auto_increment (something) values (8) returning id into i;
  dbms_output.put_line('id was: '||i);


  insert into auto_increment (something) values (4) returning id into i;
  dbms_output.put_line('id was: '||i);


  insert into auto_increment (something) values (5) returning id into i;
  dbms_output.put_line('id was: '||i);
end;
/

...hope it works....

Cheers,
Rosh
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:diasroshan
ID: 10866232
hi lordain,

did any of the comments help u...
...in case u have found some other solution do let us know ...it can help us too....

Cheers,
Rosh
0
 
LVL 4

Author Comment

by:Lordain
ID: 10871412
Hi Rosh -

Our team came up with a generic function residing on the oracle side.    It accepts 4 parameters.  The schema owner, the view, a string array of column names and a string array of values.  It returns the primary key as a string.  If an error occurs it returns the error message as a string.

On the powerbuilder side of things I created a local external function in a standard class object inherited from transaction which I called u_rpcfunc.  I had to change the sqlca object from type transaction to u_rpcfunc.

In the global variables area I typed the following:
u_rpcfunc sqlca

To call this function I just type string_var = sqlca.function_name(parameters)

This solution allows for easy to maintain remote procedure function calls and is generic enough to use with any powerbuilder application which ultimately was what we were looking for.

Our IT team came up with this solution prior to getting your response.  I did forward your solution to them but we decided to go ahead and keep the solution we have in place now.

I appreciate your answer though although we went with another solution your answer seems plausible but I am unable to confirm.

Thanks!
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 10875187
hi lordain,

its nice to know that u have got a generic solution to ur problem...
...well as far as ur question goes... i sent u the solution to ur question regarding 'RETURNING INTO' and i sent it on the same day u posted ur question....
...well i feel my comment catered to ur question and now its up to u to award the points to the right answer to the question posted by u...

hope u do the needful...

Cheers,
Rosh
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question