BEWARE: Identity columns and datwindows... a very bad implementation
Matthew Quinlan
After many many hours of analysis and days of getting bounced
around by Powersoft tech support I've found a bug in the way
that PB6.0 implements IDENTITY COLUMNS in datawindows (the
particular incident was using MSSQL6.5 but I suspect it would
be exactly the same in Sybase SQLServer).
You CANNOT be sure that the value given back to you in the
datawindow after an insert is correct!
Below is an excerpt from the email I sent to Powersoft tech
support:
> Setup:
> I am running PB6.0 on win95 against MSSQLServer6.5 on NT4.0sp4.
> There are also 9 webservers (IIS) hitting the SQLserver database at
> the same time. There are approximately 450 open database connections
> at any given time (obviously a multiprocessor NT box).
>
> Process:
> 1. I define a datawindow specifying an identity column (which happens
> to be the primary key of the updateable table).
> 2. I do a dw.insertrow() and let the user enter information into the fields
> (obviously the identity column field is not enabled)
> 3. I do a dw.update() and let the datawindow generate the associated
> insert statement which maps to the data entered by the user
> 4. The datawindow automatically populates the IDENTITY field with a
> value from the database.
>
> Question:
> HOW does powerbuilder get the value of the IDENTITY column from the
> database?
> a. Does it do a SELECT @@IDENTITY ?
> b. Does it do a SELECT MAX( <identity_columnname> ) FROM <tablename> ?
> c. Does it generate a SELECT <identity_columnname> FROM <tablename>
> WHERE <field1> = <field1_value> AND <field2> = <field2_value> ....
>
> Observations:
> I often have 3 or more inserts happening within 1/2 second of each
> other from different client machines (including the web servers).
> When I get to this level of contention I am finding that the value
> being returned to the client datawindow identity field is not
> accurate. It seems that the value is sometimes 1 or 2 values
> higher than the value assigned by the database. It is my theory
> therefor that PB6.0 is using method (b) to find the value:
> (SELECT MAX( <identity_columnname> ) FROM <tablename>
>
> Please confirm my suspicion or tell me how the value is actually
> retrieved from the database server after the insert has been done.
THE ANSWER I GOT WAS B!!! Instead of doing something intelligent
like SELECT @@IDENTITY, they actually SELECT MAX() to populate
the datawindow identity column! This is great for single-user
applications but anyone doing REAL multi-user transactional systems,
BEWARE!
Here is the scenario where this breaks down:
* user A calls dw.update() which generates an insert into table
* user B calls dw.update() which generates an insert into table
* user A's datawindow selects max(PK) from the table to populate the DW
* user B's datawindow selects max(PK) from the table to populate the DW
At this point both user A and user B have the same PK in the window!
This is true even if both are done in a transaction b/c you have row-level
locking on inserts in MSSQL6.5 (if enabled) or if you have a table which
is wide enough so each row is on it's own page. I am very suspicious
that Sybase SQL server would exhibit the same behavior.
-Quin'
----------------------
Matthew Quinlan
Project Manager
Business Data Services
----------------------
Paul Horan
Matthew,
That must be some kind of parm in an INI file somewhere, because PB6.0 to
both SQLA 5.5.x and ASA 6.x use 'Select @@identity from dummy'. That syntax
is stored as the GetIdentity parm in the PBODB0n0.INI file, in the
WATCOM50_SYNTAX section. I don't work with MS SQL (thank god), but I'd
imagine that there must be some kind of similar setting for that DBMS.
Paul Horan
VCI
Springfield, MA
Matthew Quinlan <ma...@quinlan.net> wrote in message
news:3784F463...@quinlan.net...
Ben Gong
help solve your problem? We use it and we never have any problems with a unique
in number in Oracle.
ScottHuang
Hi,
How about define the identity property into backend database table,
and disregard the column of that datawindow.
or you can code a process in the table's insert trigger, you can do many
things in
that trigger, by the help of "lock" mechanism, you will not get duplicate
numbers.
Chinese says, If the mountain won't move, then roads make moves.,
regards,
scott
Matthew Quinlan 撰寫於文章 <3784F463...@quinlan.net>...
Simon Caldwell
Surely this is only a problem if AutoCommit is set to TRUE? Otherwise, each
user's Update cannot be seen by anyone else until a Commit is issued. So
Select Max(...) will be OK.
I have seen people recommend that AutoCommit be set to TRUE for SQLServer,
but I would strongly recommend the opposite. What happens when inserting
master/detail records? You insert the master, which is committed, you then
try to insert the detail, which fails, you are left with the master and no
details.
We only ever use it when we have to, eg with Blob data which SQLServer can't
process in a transaction.
Simon
Matthew Quinlan wrote in message <3784F463...@quinlan.net>...
Ray Charbonneau
Using Select Max(...) is not a valid solution regardless of the AutoCommit
setting. I hope that this is not how the dw implements the identity lookup.
For me, I use stored procedures for all updates. This eliminates the entire
problem. Also, PB 7.0 supports stored procedure update directly in the
datawindow (so no sqlpreview event coding :)). Another thing you have to watch
out for is triggers. If the table in which you are inserting has an insert
trigger that subsequently inserts a row into another table (with its own
identity value) the @@identity value gets overwritten. In addition, version 7.0
of MS SQL Server has a bug where the identity value returns Null when an insert
trigger inserts a row in any other table (even if the other table does not have
a identity column - MS Bug #53856).
hope this helps
On Fri, 9 Jul 1999 09:00:55 +0100,
in
powersoft.public.powerbuilder.datawindow,powersoft.public.powerbuilder.database
raycharb
Simon Caldwell
Ray Charbonneau wrote in message ...
Matthew Quinlan
Unfortunately I'm not using ODBC so this setting is not used.
Matthew Quinlan
We are moving to Oracle and because Oracle gets the sequence value first
and then does the insert I do not expect to see this behavior in Oracle.
-Quin'
----------------------
Matthew Quinlan
Project Manager
Business Data Services
----------------------
Paul Horan
That's why I suggested looking in the parm file for SQL Server (if one
exists), and provided our setting from ODBC as an example.
Paul
Matthew Quinlan <ma...@quinlan.net> wrote in message
news:378625E2...@quinlan.net...
Paul Horan
Because you can't guarantee that the max() value of the column is from the
row YOU just inserted... Someone might have gotten one in underneath you
between the time you committed your insert, and ran the Select Max() query
on the table.
Paul Horan
VCI
Springfield, MA
Simon Caldwell <simonATgetrealsystemsDOTcom> wrote in message
news:csjKwWiy#GA....@forums.sybase.com...
> Why not?
>
> Ray Charbonneau wrote in message ...
Steve Field
"Simon Caldwell" <simonATgetrealsystemsDOTcom> wrote:
> What happens when inserting
> master/detail records? You insert the master, which is committed, you
> then try to insert the detail, which fails, you are left with the
> master and no details.
When Autocommit=true, some DBMS have a command for "BEGIN TRANSACTION"
which opens a transaction for the condition you mention. The trans is
closed by the COMMIT or ROLLBACK. Others, such as SQLAnywhere achieve
this by toggling the autocommit setting.
Autocommit=true gives you the greatest flexibility by not having to
remember to issue commits after every select (records held open just in
case you do an update). In our old system, AC=false, we needed to have
multiple connections; 1 for the open transaction, 1 for secondary
lookups, 1 for the security package... AC=true allowed compaction of
the entire system to a single connection and we can control the
transaction timing.
HTH, My $.02,
--
+ Steve Field (Sci...@csi.com)
+ On 07/09/99 13:03 PT
Using OUI 1.8 Pro from http://www.peaktopeak.com
Ray Charbonneau
Actually, in Oracle, you have to get the value first (Oracle doesn't get it for
you) by calling a sequence generator. Don't think that the grass in greener
over there. Once you get there you will discover the poison ivy.
regards
On Fri, 09 Jul 1999 12:40:53 -0400,
in
powersoft.public.powerbuilder.datawindow,powersoft.public.powerbuilder.database
raycharb
Philip Salgannik
Paul Horan <pa...@NOSPAMmindspring.com> wrote in message
news:5JRXJdjy#GA....@forums.sybase.com...
> Because you can't guarantee that the max() value of the column is from the
> row YOU just inserted... Someone might have gotten one in underneath you
> between the time you committed your insert, and ran the Select Max() query
> on the table.
Still not that much worse the @@identity which is global...
John Michael
I've utilized Oracle 7.3 sequences with no problems. It's easy to write a
wrapper function to utilize the CURRVAL and NEXTVAL pseudocolumns. What's
nice is that Oracle takes care of any locking, so you utilize NEXTVAL to
return the next value in your sequence and set that value in your
datawindow's "record id" column via SetItem function.
Sure, it requires a little upfront coding in writing some dynamic SQL to
access NEXTVAL for your sequence, but all you need to do is through the
function in your transaction object's ancestor (e.g., n_tr for PFC).
Ray Charbonneau <francis.c...@sybase.com> wrote in message
news:OqxyShky#GA....@forums.sybase.com...
Simon Caldwell
Isn't that the same behaviour as @@IDENTITY?
(This is a genuine question, I don't know the answer :-) )
S.
Paul Horan wrote in message <5JRXJdjy#GA....@forums.sybase.com>...
>Because you can't guarantee that the max() value of the column is from the
>row YOU just inserted... Someone might have gotten one in underneath you
>between the time you committed your insert, and ran the Select Max() query
>on the table.
>
>Paul Horan
>VCI
>Springfield, MA
>
>Simon Caldwell <simonATgetrealsystemsDOTcom> wrote in message
>news:csjKwWiy#GA....@forums.sybase.com...
>> Why not?
>>
>> Ray Charbonneau wrote in message ...
Philip Salgannik
Simon Caldwell <simonATgetrealsystemsDOTcom> wrote in message
news:nye7#rFz#GA...@forums.sybase.com...
> Isn't that the same behaviour as @@IDENTITY?
> (This is a genuine question, I don't know the answer :-) )
Essentially yes, since @@identity is a global variable...
Paul Horan
It's much worse. Let me see if I can explain...
@@identity may be a global variable, but it is 'session' specific. Let's
assume that you insert into Table A, and I insert into Table B, both which
have autoincrement columns. If we both do a Select @@identity, I'll get the
value from Table B, and you'll get the value from Table A.
If we both insert into Table A, but I get there first, you'll be blocked
until I commit the transaction. So, I can get the value of @@identity and
be sure it's mine.
Paul
Philip Salgannik <phi...@msn.com> wrote in message
news:DhC6G6oy#GA....@forums.sybase.com...
>
> Paul Horan <pa...@NOSPAMmindspring.com> wrote in message
> news:5JRXJdjy#GA....@forums.sybase.com...
> > Because you can't guarantee that the max() value of the column is from
the
> > row YOU just inserted... Someone might have gotten one in underneath
you
> > between the time you committed your insert, and ran the Select Max()
query
> > on the table.
>
Paul Horan
Not quite.... See my response above.
Paul
Philip Salgannik <phi...@msn.com> wrote in message
news:typKG6Gz#GA...@forums.sybase.com...
Paul Horan
No, not even close.
Select Max() from a column will always return the max value of the column.
Select @@identity will always return the last value that was *attempted* to
be inserted into an autoincrement column, whether that insert succeeded or
failed. So,
Insert...
ROLLBACK
Select @@identity from dummy;
will not return the same value as:
Insert..
ROLLBACK
Select max( pk ) from table;
We have about 2 or 3 dozen tables coded with autoincrement/identity columns,
some even grid-style datawindows, and they work flawlessly. I happen to
think it's an excellent implementation.
Paul
Simon Caldwell <simonATgetrealsystemsDOTcom> wrote in message
news:nye7#rFz#GA...@forums.sybase.com...
> Isn't that the same behaviour as @@IDENTITY?
> (This is a genuine question, I don't know the answer :-) )
>
> S.
>
> Paul Horan wrote in message <5JRXJdjy#GA....@forums.sybase.com>...
> >Because you can't guarantee that the max() value of the column is from
the
> >row YOU just inserted... Someone might have gotten one in underneath you
> >between the time you committed your insert, and ran the Select Max()
query
> >on the table.
> >
> >Paul Horan
> >VCI
> >Springfield, MA
> >
> >Simon Caldwell <simonATgetrealsystemsDOTcom> wrote in message
> >news:csjKwWiy#GA....@forums.sybase.com...
> >> Why not?
> >>
> >> Ray Charbonneau wrote in message ...
Philip Salgannik
Paul Horan <pa...@NOSPAMmindspring.com> wrote in message
news:EVzgzTJz#GA...@forums.sybase.com...
> It's much worse. Let me see if I can explain...
>
> @@identity may be a global variable, but it is 'session' specific. Let's
> assume that you insert into Table A, and I insert into Table B, both which
> have autoincrement columns. If we both do a Select @@identity, I'll get
the
> value from Table B, and you'll get the value from Table A.
> If we both insert into Table A, but I get there first, you'll be blocked
> until I commit the transaction. So, I can get the value of @@identity and
> be sure it's mine.
On the other hand if my table A does not have an autoincrement column and my
insert happened just a fraction of time later then yours, then according to
SQL Sever Help, what you'll get with Select @@identity is undefined...
Bruce Armstrong [TeamSybase]
only global to your connection. Every other user has a seperate one of their
own. What you do during your update won't affect my update, nor my own
@@identify variable.
On Mon, 12 Jul 1999 14:49:34 -0400,
in powersoft.public.powerbuilder.datawindow
---
Bruce Armstrong [TeamSybase] | Romac/Source International
mailto:Bruce.A...@teamsybase.com | mailto:jo...@sourcela.com
| http://www.romac-source.com
Preach the gospel at all times. If necessary, use words. [Francis of Assisi]
http://www.kidbrothers.org http://www.fccwc.org
http://www.harvest.org/knowgod/index.htm
-----------== Posted via the PFCGuide Web Newsreader ==----------
http://www.pfcguide.com/_newsgroups/group_list.asp
Philip Salgannik
And who ever mentioned a different session. Tables A and B could be modified
by the same connection...
Bruce Armstrong [TeamSybase] <NOCANSPAM_br...@teamsybase.com>
wrote in message news:5YmaIPLz#GA...@forums.sybase.com...
Paul Horan
Philip,
Take a step back and think about that a minute... How could there be any
ambiguity in @@identity if Table A and Table B are updated by a single
connection?
Insert TableA...
Select @@identity -> returns new Key from Table A
Insert TableB...
Select @@identity -> returns new Key from Table B.
Where's the confusion? You can't insert into two different tables at the
same time with a single connection. Now granted, if you don't do the first
Select @@identity, you'll have lost the TableA value that was created, but I
think that's the fault of the developer, not the DBMS.
From our experience, @@identity works just fine. It's more reliable than
Select Max() and performs better too.
Paul
Philip Salgannik <phi...@msn.com> wrote in message
news:bDQL5aLz#GA...@forums.sybase.com...
Oliver Willandsen [European Commission]
On Fri, 9 Jul 1999 14:24:20 -0400,
in powersoft.public.powerbuilder.datawindow
Paul Horan <pa...@NOSPAMmindspring.com> wrote:
>Because you can't guarantee that the max() value of the column is from the
>row YOU just inserted... Someone might have gotten one in underneath you
>between the time you committed your insert, and ran the Select Max() query
>on the table.
>
>Paul Horan
>VCI
>Springfield, MA
Who cares ? All you want is a _unique_ value
// public function long of_get_sequence (string as_table, transaction
atr_transaction)
long ll_next_seq
String ls_sql
as_table = Upper (as_table)
ls_sql = "SELECT SEQ_" + as_table + ".nextval INTO :ll_next_seq FROM DUAL"
DECLARE C_SEQ DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM :ls_sql ;
OPEN DYNAMIC C_SEQ ;
If SQLCA.SQLCODE <> 0 Then
MessageBox ("n_sequence::of_get_sequence - DataBase Error ",&
'Sequence : SEQ_' + as_table + ' : ' + &
string(atr_transaction.SqlCode)+ '~r~n' + atr_transaction.SqlErrText)
Return -1
End If
FETCH C_SEQ INTO :ll_next_seq ;
If SQLCA.SQLCODE <> 0 Then
MessageBox ("n_sequence::of_get_sequence - DataBase Error ",&
'Sequence : SEQ_' + as_table+ ' : ' + &
string(atr_transaction.SqlCode)+ '~r~n' + atr_transaction.SqlErrText)
Return -1
End If
CLOSE C_SEQ ;
If IsNull(ll_next_seq) Then ll_next_seq = -1
Return ll_next_seq
>Simon Caldwell <simonATgetrealsystemsDOTcom> wrote in message
>news:csjKwWiy#GA....@forums.sybase.com...
>> Why not?
>>
>> Ray Charbonneau wrote in message ...
>> >Using Select Max(...) is not a valid solution regardless of the
>AutoCommit
>> >setting.
>>
>>
>
>
Regards
Oliver Willandsen - [ European Commission - http://europa.eu.int ]
All comments represent my own opinion and may not in any circumstance
be regarded as stating an official position of the European Commission
Remove PAS_DE_PUB to reply by email
Paul Horan
Oracle sequences are _not_ the same as Select Max( column ) from Table,
which was the topic of discussion in this thread... Here's the scenario you
need to avoid:
User A: Select Max( column ) from table ; <-- returns value 123
User B: Select max( column ) from table ; <-- also returns value 123
User A: Insert into table values (123, ...) ; <- Insert succeeds
User A; COMMIT;
User B; Insert into table values (123, ...) ; <- 123 was the Max() at the
time User B did the query.
Obviously, this solution can't be used to guarantee a _unique_ value.
Paul
Oliver Willandsen [European Commission]
<oliverPAS_DE_...@sg.cec.be> wrote in message
news:YqqxpiRz#GA...@forums.sybase.com...
> >> >Using Select Max(...) is not a valid solution regardless of the
> >AutoCommit
> >> >setting.
> >>
> >>
> >
> >
Philip Salgannik
Paul Horan <pa...@NOSPAMmindspring.com> wrote in message
> Philip,
> Take a step back and think about that a minute... How could there be any
> ambiguity in @@identity if Table A and Table B are updated by a single
> connection?
>
> Insert TableA...
> Select @@identity -> returns new Key from Table A
> Insert TableB...
> Select @@identity -> returns new Key from Table B.
>
> Where's the confusion?
If it is not clear I'll paraphrase:
"On the other hand if table A does not have an autoincrement column and an
insert happened just a fraction of time later then into table B (which has
an autoincrement column), then according to
SQL Sever Help, @@identity will be reset..."
tk...@my-deja.com
approach, can anyone else verify this?
Just read the thread and wanted to comment that we're using
pb6.5/mssql7 and having the same problem with incorrect identities when
records are inserted near the same time. Gets pretty nasty when those
values are used to populate the keys on child tables. Mostly end up
with a lot of crossed up children, but also updating the wrong records
(if you get the wrong key on insert, when updating you are changing the
wrong record).
There was/is some question as to whether it is a sql server bug. I've
gotten second hand that MS has admitted that under load, sql server
will report incorrect identity values. The only thing I'm able to find
in newsgroups is that sql server sometimes forgets where it's at in the
sequence and starts reissuing numbers causing problems with duplicate
keys. Not the same problem.
If PB is doing a 'select max()' it would explain the crossing problem.
PROBLEM:
Tran1 inserts a row and is assigned an identity
Tran2 inserts a row and is assigned the next identity
Tran2 asks for max identity value
Tran2 commits
Tran1 asks for max identity value
Tran1 inserts child records using the reported identity value as part
of their composite key.
Tran1 commits
Tran2 will now have Tran1's child records and Tran1 will have no child
records.
In the meantime, where we can, we refresh the window after update to
get the values that were actually inserted, and where we can't (like
when we need to propagate the key to child records w/in a xaction) we
do a check after inserting the master record to see if there is a
record with the reported key and the values we had tried to insert. If
there isn't, we were likely given the wrong identity value and we just
rollback. Suppose could also do a 'select @@identity' to see if a
different value. Pain either way.
Terry Kroh
In article <3784F463...@quinlan.net>,
> -Quin'
>
> ----------------------
> Matthew Quinlan
> Project Manager
> Business Data Services
> ----------------------
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Oliver Willandsen [European Commission]
On Tue, 13 Jul 1999 09:01:07 -0400,
in powersoft.public.powerbuilder.datawindow
Paul Horan <pa...@NOSPAMmindspring.com> wrote:
>Oracle sequences are _not_ the same as Select Max( column ) from Table,
>which was the topic of discussion in this thread...
You're correct of course, some poster mentioned moving to Oracle and sequences
and I posted the wrong script...
> Here's the scenario you
>need to avoid:
>
>User A: Select Max( column ) from table ; <-- returns value 123
>User B: Select max( column ) from table ; <-- also returns value 123
>
>User A: Insert into table values (123, ...) ; <- Insert succeeds
>User A; COMMIT;
>User B; Insert into table values (123, ...) ; <- 123 was the Max() at the
>time User B did the query.
>
>Obviously, this solution can't be used to guarantee a _unique_ value.
>
>Paul
The following will, though :
long ll_row, ll_aa_cote, ll_nb_cote
string ls_type_cote, ls_test
integer li_sqlcode, li_rc
ii_sqldbcode = 0
ll_row = This.GetRow()
IF ll_row = 0 THEN return -1
// Si: - il s'agit d'un nouvel enregistrement
// - ce n'est pas un document en cours de finalisation (ue_final)
// - ce n'est pas un création d'une nouvelle version (ue_new_version)
// alors, on lui ajoute un numero de cote
IF This.GetItemStatus(ll_row, 0, PRIMARY!) = NewModified! AND &
IsNull(This.GetItemNumber(ll_row, "ct_nb_cote")) THEN
ll_aa_cote = This.GetItemNumber(ll_row, "ct_aa_cote")
ls_type_cote = This.GetItemString(ll_row, "ct_type_cote")
// Recherche dans la base le numero de cote max
SELECT DECODE(MAX("COTE"."CT_NB_COTE"), NULL, 0, MAX("COTE"."CT_NB_COTE"))
INTO :ll_nb_cote
FROM "COTE"
WHERE "COTE"."CT_AA_COTE" = :ll_aa_cote AND
"COTE"."CT_TYPE_COTE" = :ls_type_cote AND
"COTE"."CT_RESERVED" <> 'O';
IF SQLCA.SQLCODE <> 0 THEN
MessageBox("Application greffe","pfc_update - master - " +
string(SQLCA.SQLCODE) + SQLCA.SQLErrText)
Return -1
END IF
DO
DECLARE my_cursor CURSOR FOR
SELECT 'X' FROM "COTE"
WHERE "COTE"."CT_AA_COTE" = :ll_aa_cote AND
"COTE"."CT_TYPE_COTE" = :ls_type_cote AND
"COTE"."CT_NB_COTE" = :ll_nb_cote;
// Teste si le numero de cote suivant a deja ete attribué
li_sqlcode = 0
DO WHILE li_sqlcode = 0
ll_nb_cote = ll_nb_cote + 1
OPEN my_cursor;
FETCH my_cursor INTO :ls_test;
IF li_sqlcode = 0 THEN
li_sqlcode = sqlca.sqlcode
END IF
CLOSE my_cursor;
LOOP
IF li_sqlcode = -1 THEN
MessageBox("Application greffe","pfc_update - master - " +
string(SQLCA.SQLCODE) + SQLCA.SQLErrText)
Return -1
END IF
// Ajoute le numero de cote suivant
This.SetItem(ll_row, "ct_nb_cote", ll_nb_cote)
// Déclenche l'événement ancetre
li_rc = SUPER::Event pfc_update(ab_accepttext, ab_resetflag)
LOOP WHILE li_rc = -1 AND ii_sqldbcode = 1
ELSE
li_rc = SUPER::Event pfc_update(ab_accepttext, ab_resetflag)
END IF
Regards
>Oliver Willandsen [European Commission]
><oliverPAS_DE_...@sg.cec.be> wrote in message
>news:YqqxpiRz#GA...@forums.sybase.com...
>> On Fri, 9 Jul 1999 14:24:20 -0400,
>> in powersoft.public.powerbuilder.datawindow
>> Paul Horan <pa...@NOSPAMmindspring.com> wrote:
>> >Because you can't guarantee that the max() value of the column is from
>the
>> >row YOU just inserted... Someone might have gotten one in underneath you
>> >between the time you committed your insert, and ran the Select Max()
>query
>> >on the table.
>> >
>> >Paul Horan
>> >VCI
>> >Springfield, MA
>>
>>
[ snipped inappropriate sequence script ]
Matthew Quinlan
WOW!!! now THIS is a thread!
I never expected my post to generate this much interest but I'm
glad that it has. So here's a quick rehash of what I've found
out.
1. This behavior is ONLY found when using SQLserver (either
microsoft OR sybase), using tables with IDENTITY columns, and
using datawindows which specify the IDENTITY property.
2. Powerbuilder DOES in fact select MAX() from table to get the
value to populate the datawindow post-insert (regardless of
the settings in the PBODB0n0.INI file if you are using the
NATIVE drivers)
3. THIS IS NOT AN ISSUE WITH SEQUENCES!!! Oracle works fine.
4. The @@IDENTITY variable IS a session specific variable so
it would work splendidly as an alternative solution.
5. You can write your own functions to get around this problem
by not relying on the datwindow to populate your value.
6. my autocommit setting is TRUE
7. This behavior is ONLY observed in high transaction systems
(this due to the mathmatical probability that one process could
insert a record between the times that another process did an
insert and when it read the max value from the table)
The moral of the story is: DON'T RELY ON POWERBUILDER TO
POPULATE YOUR IDENTITY FIELDS FOR YOU!
I would also like to thank Jim O'Neil from Powersoft for taking
a personal interest in this issue.
Ray Charbonneau
Matthew:
You didn't mention the best approach (IMHO):
Use stored procedures for update. Eliminates all of these problems. Also, in PB
7.0 stored proc updates are supported directly in the datawindow. Based on the
dozens of PB (and other) apps I have written over the past few years, I will no
longer do direct table updates via a client app. You may think stored procs are
more work, I used to think that, in reality they save work over time. Not to
mention the added flexibility for handling transactions, cascading operations
(can even support two identity columns in same datawindow).
regards
On Tue, 13 Jul 1999 11:18:32 -0400,
in
powersoft.public.powerbuilder.datawindow,powersoft.public.powerbuilder.database
Matthew Quinlan <ma...@quinlan.net> wrote:
raycharb
Philip Salgannik
Here is even more:
...If the statement fires one or more triggers that perform inserts that
generate identity values, calling @@IDENTITY immediately after the statement
returns the last identity value generated by the triggers. The @@IDENTITY
value does not revert to a previous setting if the INSERT or SELECT INTO
statement or bulk copy fails, or if the transaction is rolled back.
Which means that there is a number of factors that might affect the GLOBAL
identity variable in even in one session...
Philip Salgannik <phi...@msn.com> wrote in message
news:#qcpXdTz#GA...@forums.sybase.com...
Philip Salgannik
>
> 7. This behavior is ONLY observed in high transaction systems
> (this due to the mathmatical probability that one process could
> insert a record between the times that another process did an
> insert and when it read the max value from the table)
>
> The moral of the story is: DON'T RELY ON POWERBUILDER TO
> POPULATE YOUR IDENTITY FIELDS FOR YOU!
IF you are implementing a HIGH TRANSACTION system...
Paul Horan
Matthew,
I beg to differ. We have a high transaction system (several hundred inserts
a minute? is that high??) that is written on SQL Anywhere 5.5.04. Several
key tables are written with autoincrement/identity columns in the
datawindows, and they work flawlessly. SQL Anywhere uses @@identity, not
Select Max().
The one thing we found that took some searching was to deploy the
PBODB050.INI file in the same directory as the compiled executables,
otherwise the value was not returned into the dw column following the
Update().
Paul Horan
VCI
Springfield, MA
Matthew Quinlan <ma...@quinlan.net> wrote in message
news:378B58C8...@quinlan.net...
> WOW!!! now THIS is a thread!
>
> I never expected my post to generate this much interest but I'm
> glad that it has. So here's a quick rehash of what I've found
> out.
>
> 1. This behavior is ONLY found when using SQLserver (either
> microsoft OR sybase), using tables with IDENTITY columns, and
> using datawindows which specify the IDENTITY property.
>
> 2. Powerbuilder DOES in fact select MAX() from table to get the
> value to populate the datawindow post-insert (regardless of
> the settings in the PBODB0n0.INI file if you are using the
> NATIVE drivers)
>
> 3. THIS IS NOT AN ISSUE WITH SEQUENCES!!! Oracle works fine.
>
> 4. The @@IDENTITY variable IS a session specific variable so
> it would work splendidly as an alternative solution.
>
> 5. You can write your own functions to get around this problem
> by not relying on the datwindow to populate your value.
>
> 6. my autocommit setting is TRUE
>
> 7. This behavior is ONLY observed in high transaction systems
> (this due to the mathmatical probability that one process could
> insert a record between the times that another process did an
> insert and when it read the max value from the table)
>
> The moral of the story is: DON'T RELY ON POWERBUILDER TO
> POPULATE YOUR IDENTITY FIELDS FOR YOU!
>
Paul Horan
Absolutely. And I, as the developer, can read the documentation, and design
my system to use @@identity correctly. Which means a) Don't try and use
the value of @@identity if you didn't just insert into a table with
autoincrement; b) Get the value of @@identity within the same transaction as
the Insert that generated it; and c) Don't write Insert triggers that
themselves do inserts into tables with autoincrement columns.
Follow these simple rules, and autoincrement/@@identity can be your friend
too.
Paul
Philip Salgannik <phi...@msn.com> wrote in message
news:mJd$QwUz#GA....@forums.sybase.com...
Paul Horan
Oliver,
Again, I disagree with using Select max( column ) + 1 from Table to attempt
to guarantee a unique primary key.
There is a window of opportunity between the time client A runs the Select
Max(), and actually does the Insert. Within that window, client B could
also run Select Max() and get the same exact value as Client A. Client B's
subsequent insert would fail due to a duplicate key.
A slightly different approach is working for us, because we have SQL
Anywhere, which supports BEFORE triggers. In the Before Insert trigger, if
the primary key column being inserted is NULL, the trigger uses Select
ax( key ) + 1 to determine a unique ascending value. It works there because
the key is generated as part of the Insert atomic statement, not in a prior
Select. There aren't three steps (a Select, a SetItem(), and an Update() )
that creates the small window of time for an error to occur.
Paul
Oliver Willandsen [European Commission]
<oliverPAS_DE_...@sg.cec.be> wrote in message
news:R9pdUFUz#GA...@forums.sybase.com...
> >> >Paul Horan
> >> >VCI
> >> >Springfield, MA
> >>
> >>
Matthew Quinlan
Are you using ODBC? Powersoft indicated to me that the
PBODB050.ini file setting was only if I was not using
the native driver. If this is not the case I would be
VERY interested in your implementation.
Another reason this is more well known is that most people do
not do anything with the value which is returned to the field.
This particular system did a dw.GetItemNumber() on the field
and then did a dw.SetItem() on a corresponding slave datawindow
(but it did it with the WRONG value given by the master DW!).
Were it not for the slave datawindow we may never have realized
this problem was occuring.
I agree with you completely that if the database uses "select
@@identity" then the DW's would "work flawlessly". But I have
been unable to get the PBODBO050.ini to make an impact when
using the Native driver as opposed to the ODBC driver.
-Quin'
Paul Horan wrote:
>
> Matthew,
>
> I beg to differ. We have a high transaction system (several hundred inserts
> a minute? is that high??) that is written on SQL Anywhere 5.5.04. Several
> key tables are written with autoincrement/identity columns in the
> datawindows, and they work flawlessly. SQL Anywhere uses @@identity, not
> Select Max().
> The one thing we found that took some searching was to deploy the
> PBODB050.INI file in the same directory as the compiled executables,
> otherwise the value was not returned into the dw column following the
> Update().
>
Philip Salgannik
ODBC only ... :-))
Matthew Quinlan <ma...@quinlan.net> wrote in message
news:378BA181...@quinlan.net...
> Are you using ODBC?
Matthew Quinlan
The "high transaction" portion of the warning was omitted because
Having a low-transaction system certainly does NOT keep this issue
from being a problem. It just statistically lowers your chances
of running into it... doesn't eliminate them.
----------------------
Matthew Quinlan
Project Manager
Business Data Services
----------------------
Philip Salgannik wrote:
>
> Matthew Quinlan <ma...@quinlan.net> wrote in message
> news:378B58C8...@quinlan.net...
> >
> > 7. This behavior is ONLY observed in high transaction systems
> > (this due to the mathmatical probability that one process could
> > insert a record between the times that another process did an
> > insert and when it read the max value from the table)
> >
> > The moral of the story is: DON'T RELY ON POWERBUILDER TO
> > POPULATE YOUR IDENTITY FIELDS FOR YOU!
>
Philip Salgannik
Paul Horan <pa...@NOSPAMmindspring.com> wrote in message
news:St5FamWz#GA....@forums.sybase.com...
>Which means a) Don't try and use
> the value of @@identity if you didn't just insert into a table with
> autoincrement; b) Get the value of @@identity within the same transaction
as
> the Insert that generated it; and c) Don't write Insert triggers that
> themselves do inserts into tables with autoincrement columns.
>
> Follow these simple rules, and autoincrement/@@identity can be your friend
> too.
>
> Paul
All in all I tend to share your point of view. I worked on a system that
used it extensively without ANY problems whatsoever.
That's why it bothers me that the moral of this story, is suddenly:
"DON'T RELY ON POWERBUILDER TO POPULATE YOUR IDENTITY FIELDS FOR YOU!"
(capitalization NOT mine :-)) )
Paul Horan
We're ODBC only (SQLA 5.5.04). I've not worked with MS SQL since PB 3.0
days. Isn't there something like a PBMSS050.INI file that does the same
thing?
Paul
Matthew Quinlan <ma...@quinlan.net> wrote in message
news:378BA181...@quinlan.net...
> Are you using ODBC? Powersoft indicated to me that the
> PBODB050.ini file setting was only if I was not using
> the native driver. If this is not the case I would be
> VERY interested in your implementation.
>
> Another reason this is more well known is that most people do
> not do anything with the value which is returned to the field.
> This particular system did a dw.GetItemNumber() on the field
> and then did a dw.SetItem() on a corresponding slave datawindow
> (but it did it with the WRONG value given by the master DW!).
> Were it not for the slave datawindow we may never have realized
> this problem was occuring.
>
> I agree with you completely that if the database uses "select
> @@identity" then the DW's would "work flawlessly". But I have
> been unable to get the PBODBO050.ini to make an impact when
> using the Native driver as opposed to the ODBC driver.
>
> -Quin'
>
>
>
>
> Paul Horan wrote:
> >
> > Matthew,
> >
> > I beg to differ. We have a high transaction system (several hundred
inserts
> > a minute? is that high??) that is written on SQL Anywhere 5.5.04.
Jim O'Neil [Sybase]
Paul Horan wrote:
No, there's not. Matthew has a bona fide bug that had been previously
reported and will be addressed in an upcoming build (no I don't know which one
;>)). If someone else is impacted by this same bug and wishes to pursue an
escalation with Technical Support, please feel free to do so - at a minimum
we'd require a business case indicating impact to development, revenue,
schedules, etc. to pursue the escalation. There are a potential number of
coding workarounds, none exceptionally pleasing, but if you're embarking on a
new project and you're aware of the problem it may be something you can design
for.
Jim O'Neil
Sybase Technical Support
Philip Salgannik
Paul Horan <pa...@NOSPAMmindspring.com> wrote in message
> Matthew,
> That must be some kind of parm in an INI file somewhere, because PB6.0 to
> both SQLA 5.5.x and ASA 6.x use 'Select @@identity from dummy'. That
syntax
> is stored as the GetIdentity parm in the PBODB0n0.INI file, in the
> WATCOM50_SYNTAX section. I don't work with MS SQL (thank god), but I'd
> imagine that there must be some kind of similar setting for that DBMS.
BTW, there is a setting like this in the PBODB0n0.INI file for MS SQL
Server...
Paul Horan
not the native MSS driver.
Paul
Philip Salgannik <phi...@msn.com> wrote in message
news:tyD2B$fz#GA....@forums.sybase.com...
Mike Kruchten
Actually, the proper way to connect to SQL Server, or Sybase for that
matter, is to set Autocommit =True and then control ALL transaction
statements yourself. We've created a transaction object function that issue
an Execute Immediate Begin Transaction. That way a transaction is NEVER
started unless the developer causes it.
IMHO, doing transaction management any other way with SQL Server is just
asking for trouble.
Mike Kruchten
DBA
Great River Energy
Simon Caldwell <simonATgetrealsystemsDOTcom> wrote in message
news:11lWkFey#GA...@forums.sybase.com...
> Surely this is only a problem if AutoCommit is set to TRUE? Otherwise,
each
> user's Update cannot be seen by anyone else until a Commit is issued. So
> Select Max(...) will be OK.
> I have seen people recommend that AutoCommit be set to TRUE for SQLServer,
> but I would strongly recommend the opposite. What happens when inserting
> master/detail records? You insert the master, which is committed, you
then
> try to insert the detail, which fails, you are left with the master and no
> details.
> We only ever use it when we have to, eg with Blob data which SQLServer
can't
> process in a transaction.
>
> Simon
>
> Matthew Quinlan wrote in message <3784F463...@quinlan.net>...
Simon Caldwell
any problems.
We have Autocommit = FALSE, and only set it to try when performing tasks
that cannot be carried out in a transaction, eg Blob operations, changing
passwords.
Simon
Mike Kruchten wrote in message ...