Dear Support!

I wanna ask that have a way to get an AutoInc field value after insert in Sensitive Query?

In my programmes I tried to replace the Table with Query to working with less data. But if I want to get the new value of AI field (ID), it returns with zero.

Q.Open;
if Q.RecordCount = 0 then
 Q.Append;
 ...
 Q.Post;
 item_id := Q.FieldByName('id').AsInteger; // item_id = 0....
end;
..


Thanks for your help!
  

Laszlo


It should work depending on what Q and your table definitions are.

Can you post both please.

Roy Lambert [Team Elevate]

Roy Lambert wrote:
>It should work depending on what Q and your table definitions are.
>Can you post both please.

Hi!

From this account I answered. A demo code:


   q.SQL.Text := 'select * from st_ertek where kod = :kod';
   q.ParamByName('kod').Asinteger := 112;
   q.Open; // not found, we add a new...
   q.Append;
   q['ertek'] := IntToStr(GetTickCount);
   q['stc_tul_kod'] := 111;
   q['st_szerszam_kod'] := 111;
   q.Post;
   Caption := IntToStr(q.FieldByName('kod').AsInteger);

= 0

the table is:

st_ertek"
(
"kod" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),
"st_tul_kod" INTEGER NOT NULL,
"st_szerszam_kod" INTEGER NOT NULL,
"ertek" VARCHAR(250) COLLATE "ANSI",
"modositva" TIMESTAMP,
CONSTRAINT "p" PRIMARY KEY ("kod")
)

And I experienced same things formerly...

May the where is the problem? Without were in another table I can get the AI field value...

Thanks: dd

durumdara wrote:

Without "where" (condition)... sorry...

durumdara


I can guess as to what is going on. To be certain I'd need your table.

1. Taking into account the parameter you set a condition kod = 112
2. On opening the query you find zero rows
3. You add a new row
4. The value of kod for the new row you add isn't 112 (it could be either more or less but its not exactly 112)
5. Checking the value of kod you are given 0 which is what an null integer field will return


A sensitive result set is essentially the same as a table with a filter set. If the row inserted / appended / altered is outside the filter condition it will "vanish".

The easy way to achieve what you want is a second query

SELECT MAX(kod) FROM st_ertek

Roy Lambert [Team Elevate]

<< From this account I answered. A demo code: >>

Roy is correct - you've got a "fly-away" issue with the row due to the WHERE
clause.   As of EDB 2.08, you can now use this function also to get the last
IDENTITY value for a particular table/column:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=LASTIDENTITY

Notice that you don't need a FROM clause anymore for such queries:

SELECT LASTIDENTITY('Customer', 'CustNo') AS LastCustNo

--
Tim Young
Elevate Software
www.elevatesoft.com

>>As of EDB 2.08, you can now use this function also to get the last IDENTITY value

NOTE: With multi-user systems the last identity value might not be the value your user has inserted, if there is a time gap (particularly network latency) between the INSERT and the LAST IDENTITY function.

To get round this I use a DEFAULT for my ID instead & set it using a FUNCTION:

ALTER COLUMN ID AS INTEGER DEFAULT UID() NOT NULL;

WHERE UID() goes away & gets a guaranteed unique sequenced number.

In such a situation with a query component the value of the ID is available as soon as INSERT is called, so you can use it to avoid flyaway.

Adam,

<< NOTE: With multi-user systems the last identity value might not be the
value your user has inserted, if there is a time gap (particularly network
latency) between the INSERT and the LAST IDENTITY function. >>

That isn't correct - the LASTIDENTITY() function is specifically
session-based and only returns the last identity value assigned to a
particular column in a particular table for the current session.  To test
this, just call it on a new session that hasn't inserted any new rows - it
will return 0.

However, I know what you're thinking of...DBISAM's LASTAUTOINC()

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>That isn't correct - the LASTIDENTITY() function is specifically
>session-based and only returns the last identity value assigned to a
>particular column in a particular table for the current session. To test
>this, just call it on a new session that hasn't inserted any new rows - it
>will return 0.

When you add this to th OLH can you stress that point otherwise I can see some moron (probably called Roy) thinking it applies to the table as a whole and screwing the data up in some way. Difficult I know but I'm brilliant at cocking up <vbg>

Roy Lambert

That isn't correct - the LASTIDENTITY() function is specifically
session-based and only returns the last identity value assigned to a
particular column in a particular table for the current session.  

_Extremely_ cool. I stand corrected!

I will definitely be using this function.