How do you obtain IDENTITY column value after Insert ?
Shaun Richards
PB5 User Guide states:
"By specifying an identity column in the Specify Update Properties dialog
box, you tell PowerBuilder to bring back the value of a new row's identity
column after an insert in the DataWindow so the user can see it"
Question:
Can someone please tell me how to obtain the value of an Identity column
following the insert of a new row ? How does PB5 "bring back" the value
of a new row's identity column - and how can I acccess it ?
P.S. I am using PB5 and SQL Anywhere.
TIA & Cheers,
Shaun Richards
Leonid Rudi
Sorry for my english :)
Value of an Identity column after INSERT stored in @@identity variable
But @@identity don't accssesiable from script :(
I am do this :
In SQLCEntral I create stored proc
--------
create function
"dba".Current_Id()
returns integer
begin
declare Cur_id integer;
select @@identity into Cur_id;
return(Cur_id)
end
-------
And in the script i am after INSERT or InsertRow() write
SELECT Current_id() INTO :new_id FROM dummy;
And thats all !
Leonid
ANTISPAM ! SORRY!!!
My e-mail is not correct: zzz...@softincom.ru
Remove 'ZZZ_' from E-mail
ICQ 7281017
Shaun Richards пишет в сообщении <01bd2e04$07675000$afecaec7@newmicronpc>
...
Shaun Richards
Rudi,
Thanks for the suggestion, but I have read (someone please confirm this if
you can...) that the @@identity global variable may not be reliable in a
muliuser application (ie. value returned is not related to the current
connection - may be the result of someone else's insert).
I was hoping that I would not need a stored proc or additional embedded SQL
(additional database access) to solve this issue (after all - it seemed
like such a simple issue when reading the documentation)...
So then, am I to assume that PB does NOT actually "bring back" the identity
value (as the PB User Guide claims...) ???
TIA & Cheers,
Shaun
Leonid Rudi <zzz...@softincom.ru> wrote in article
<QD8Aohg...@forums.powersoft.com>...
Ray Torkelson
PB will backfill the key on the datawindow after the update completes. You
can get the value right after.
rtn = dw_1.Update(True, False)
IF rtn = 1 THEN
NewKey = dw_1.getItemNumber(1, 'key')
dw_2.SetItem(1, 'foreign_key', new_key)
rtn = dw_2.Update(True, False)
IF rtn = 1 THEN
COMMIT;
dw_1.ResetUpdate()
dw_2.ResetUpdate()
END IF
END IF
IF rtn <> 1 THEN
ROLLBACK;
END IF
Of course this can get tricky if you're trying to insert
multiple master and detail rows all at once.
Ray
Shaun Richards wrote in message <01bd2e51$8171f600$86e9aec7@newmicronpc>...
Shaun Richards
Ray,
Thanks for the reply - but have you actually tried and run the pseudo code
in your last response ?
I have tried the same and do NOT get the Identity column back after the
update. I tried the following simple code....
Long ll_id
dw_1.Update(True, False)
ll_id = dw_1.GetItemNumber(dw_1.GetRow(), 'cust_id') // where
'cust_id' is autoincrement/identity/primary key column
Problem is... all I "get back" is zero (ie. ll_id = 0) !!!
I still fail to see how PB "backfills" or "brings back" the incremented key
value after inserting new row.
I have re-read all doumentation and re-verified all Update Property
Information in datawindow dw_1 in this case - and it appears correct.
Any further thoughts?
TIA & Cheers,
Shaun
Ray Torkelson <tor...@inxpress.net> wrote in article
<01bd2ebf$605bc4a0$1d92aec7@newmicronpc>...
Shaun Richards
Ray,
Any further thoughts?
TIA & Cheers,
Shaun
Shaun Richards <shaunr...@compuserve.com> wrote in article
Paul A. Horan
Shaun,
Ray left out one important point. We've found (thru trial and error) that
the value in the key column at the time of Update() MUST be NULL, and that
the column must NOT be in the list of updateable columns.
If these are all true, then the new identity value will magically "appear"
in the column following a successful update.
Paul Horan CPD-P
VCI
Springfield, MA
Shaun Richards wrote in message <01bd2eca$cd37ce60$5cd6aec7@newmicronpc>...
Victor Cekvenich, Instructor
Wrong wrong!
@@identity is by far the best way (especially as an rpc to a transaction
object).
It is always stable, because each connection has a "local" @@idenity and is
therefore safe.
Look at the SQL Any or Sybase newsgroups, they have a more technical
discussion.
Leonid Rudi
My PB 5.0.0.3 return identity of inserted row after Update() if datatype
this column
is integer.For numeric - random number 8-0
Paul A. Horan пишет в сообщении <0V11sXQ...@forums.powersoft.com> ...
Shaun Richards
Paul,
Thanks for the additional info... I'll watch out for those aspects of
datwindow update properties in the future.
However (for anyone else who may care...) Turns out that my problem was
that I'm using the PB 5.02 release !!
This problem is a documented bug (see Sybase Solved Cases - Bug #43937).
Identity colun values will NOT be "brought back"/returned to the datawindow
following an Update() afetr inserting a new row.
This problem occurs with both native and ODBC drivers. In the case if
ODBC drivers, (ie. SQL Anywhere) this bug is fixed in release PB 5.04 and
6.0.
Cheers,
Shaun
Paul A. Horan <pa...@twoplus.com> wrote in article
Shaun Richards
Leonid,
Identity column values should be returned foro ANY numeric data type (ie.
not just Integer).
See my response to Ray...
Cheers,
Shaun
Leonid Rudi <zzz...@softincom.ru> wrote in article
<#qG2WFZM...@forums.powersoft.com>...
> My PB 5.0.0.3 return identity of inserted row after Update() if datatype
> this column
> is integer.For numeric - random number 8-0
>
> Paul A. Horan пишет в сообщении <0V11sXQ...@forums.powersoft.com>
..
Jeff Van Dusseldorp
@@Identity is a global variable and therefore inherently dangerous.
Now, I believe that global in Sybase terms means global to your connection,
so multi-user interference shouldn't be a problem (I think).
One place you may run into difficulty is if you have an insert trigger
which creates rows in other tables. If the other rows have identity
columns as well, @@identity will be re-populated with the sub-tables
identity value. When PB retrieves the identity value, it will be the value
for the wrong table.
Having said that, they are damn handy; we use them throughout our
application. In some cases, we've needed to know the next primary key
value before we start the transaction begins and in those cases we've had
to create stored procedures.
If you need to be database independant, you probably want to avoid using
identity columns because all databases won't support it. Actually, you
will probably want to avoid the stored procedures as well, but that is
another story.
HTH
Jeff Van Dusseldorp
Victor Cekvenich, Instructor
@@ identity is globally visible variable but local to connection.
Sunil Ramineni
Hi,
I tried this to get the value of the identity column using the
following statements but the result was NULL.
dw_application.InsertRow(0)
integer stuid
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM "SELECT @@identity" ;
OPEN DYNAMIC my_cursor ;
FETCH my_cursor INTO :stuid ;
CLOSE my_cursor ;
if isNull(stuid) then
MessageBox('Identity Value', 'Identity Value is Null.')
else
MessageBox('Identity Value', String(stuid))
end if
Could any one pls help me figuring this problem...
thanks,
sunil
Eirik Mangseth
guys, why all this fuzz.
If a table contains an identity column, you can specify this under Update
Properties in the dw painter. Sometimes PB will have chosen the correct
column, sometimes you will have to do it manually.
I''m using pb5.0.2 and 5.0.3 and it works fine. Do an insert, call Update()
and the value is there!
No need to read the @@identity variable or call sored proc and what not.
eirik mangseth
e...@uc-unitedconsultants.com
Eirik Mangseth
You're using a datawindow, right? If so, an insert in a datawindow is local to
the client, nothing happens in the database until you issue an update request
through a call to the Update() function, right? So you won't have an identity
value for the new row unless you call Update(). Selecting @@identity won't help
because it contains an "old" value. Secondly, in Sybase SQLServer, you're not
allowed to insert values into an identiy column, unless you specifically issued
the SET identity_insert ON statement beforehand. Now, if you're issuing the
insert as an embedded sql call the situation is different and you probably will
have to fall back on one of the other methods mentioned in this thread.
If you find I'm rambling, please have me excused
eirik mangseth
e...@uc-unitedconsultants.com
Sunil Ramineni wrote:
> Eirik,
> I need the value of the identity column after the insert and
> before an update.
>
> Thanks,
> Sunil
Sunil Ramineni
Nick Simino
From looking at your previous post it seems that you are trying to get
the value of the identity column after you do an insertrow() on your
datawindow.
When you do an insertrow() on a datawindow it does not insert a row in
the database.
It only inserts a row in the datawindow and sets the status of that row
to New!
Rows with a status of New! do not get inserted into the database even if
you do an update.
If you modify the newly inserted row the status of the row gets changed
to NewModified!
This row will now be inserted into the database when you do an update.
At this point you can get the value of the identity column.
Sorry if I am not understanding your question correctly and you already
know all this.
Nick
Oded Netanel
Hi,
If you are using a datawindow for the insert (not embedded SQL) then you
have an option in the "update properties" dialog where you can tell PB who
the identity column is. After a successful dw.update() you can just use
getitemnumber() or dot notation to get the value in the identity column for
new rows.
HTH
Sunil Ramineni <sun...@pacbell.net> wrote in article
<34fc624c...@forums.powersoft.com>...
Sunil Ramineni
hi guys,
thank you for some good tips.
:Sunil
Maxwell Avagliano
Shaun
Would that it were true.
I have PB 6.0 and Adaptive server enterprise (SQL Sever 11.5) for NT.
There is still no magic retrieval of the
identity/primarykey/autoincrement field after insert. I have viewed
infobase reports of this problem back to January 1996 and there is still
no adequate fix.
By using the adjustments suggested in the SQLPreview event of the
datawindow (ie the @@identity value), the performance of the datawindow
decreases as any adjustments made in the SQLpreview event vastly slow
down the operation of the datawindow. Anyway, it should not be
necessary to have to script a fix for something as well documented as
the failure to return identity column values. The use of referential
integrity requires identity values to be available after inserts in
every application I have been involved with. Why isn't it addressed in
PB 6.0 for SQLSever 11.5??
We have a situation were there are regular imports (inserts) of a few
thousand portfolio transactions and the difference in speed when coding
the SQLPreview event precludes that fix. It is not that great that an
obvious need for developers documented back in Jan 96 is still being
discussed and work arounds being offered in May 98.
Hoping that this will galvanise someone into action!
Regards
Max Avagliano
NetWorth Technology PL
Shaun Richards wrote:
>
> Paul,
>
> Thanks for the additional info... I'll watch out for those aspects of
> datwindow update properties in the future.
>
> However (for anyone else who may care...) Turns out that my problem was
> that I'm using the PB 5.02 release !!
>
> This problem is a documented bug (see Sybase Solved Cases - Bug #43937).
> Identity colun values will NOT be "brought back"/returned to the datawindow
> following an Update() afetr inserting a new row.
>
> This problem occurs with both native and ODBC drivers. In the case if
> ODBC drivers, (ie. SQL Anywhere) this bug is fixed in release PB 5.04 and
> 6.0.
>
> Cheers,
> Shaun
>
> Paul A. Horan <pa...@twoplus.com> wrote in article
> <0V11sXQ...@forums.powersoft.com>...