Solved

Returning Into

Posted on 2004-04-13
6
780 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
  • 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now