witam
dodaje do tabeli T1 (id-_pk,nazwa) wiersz '2'
jak moge wyciagnac id dodanego wiersza?
id zwieksza sie co 1(mssql) - potrzebny mi jest ta liczba - poniewaz mam druga tabele T2w ktorej na podstawie tej liczby tworze kilka wierszy.
(id_pk,id_t1,opis)
'2', 'malowanie'
'2', 'spawanie'
nie moge wykonac zapytania max(id) - poniewaz w tym czasie juz ktos inny moze dodac wiersz i bede mial "moj id" +1
prosze o pomoc
Funkcja wyciągająca ostatni dodany id to:
SELECT LAST_INSERT_ID();
Pozdrawiam
tylko, że to nie musi być id rekordu, który Ty dodałeśbo po tym mógł dodać rekord ktoś inny.
Witam, obił mi się kiedyś o ucho temat SQLCA, jest to prawdopodobnie rekord posiadajacy nformacje o ilosci "przetwozonych wierszy"
Pozdrawiam
EDIT_____________
Prosze cos takiego znalazłem... może się przyda
"
First we need to define our structures. If you have the chance to take a look at the Informix Client SDK, don't miss to check the header files for additional informations about SQLCA or other Informix stuff like sqlcaw and sqlerrd.
sqlcaw is interesting on connect events to the database server and
carries important informations.
type
PSQLCAW = ^sqlcaw_s;
sqlcaw_s = record
sqlwarn0: char; // * = W if any of sqlwarn[1-7] = W */
sqlwarn1: char; // * = W if any truncation occurred or database has transactions * /
sqlwarn2: char; // * = W if a null value returned or ANSI database * /
sqlwarn3: char; // * = W if no. in select list ! = no. in into list or turbo backend * /
sqlwarn4: char; // * = W if no where clause on prepared update, delete or incompatible float format * /
sqlwarn5: char; // * = W if non - ANSI statement * /
sqlwarn6: char; // * reserved * /
sqlwarn7: char // * reserved * /
end;
/* Now we define SQLCA itself */
PSQLCA = ^sqlca_s;
sqlca_s = record
sqlcode: longint;
sqlerrm: array[0..71] of char; //* error message parameters */
sqlerrp: array[0..7] of char;
sqlerrd: array[0..5] of longint;
sqlwarn: sqlcaw_s;
end;
(* meaning of sqlerrd:
/* 0 - estimated number of rows returned /
/ 1 - serial value after insert or ISAM error code /
/ 2 - number of rows processed /
/ 3 - estimated cost /
/ 4 - offset of the error into the SQL statement /
/ 5 - rowid after insert */
*)
The only thing we need at this point is an additional function to retrieve a specific information, I took the db_serial after insertion
because thats the resaon I got in touch with this stuff in Delphi 3.
The Function we are using is DbiGetProp, located in the BDE.DCU (so include it in your unit).
function DBSERIAL(H: hDBIDB): Integer;
var
LOCAL_SQLCA: PSQLCA;
len: Word;
begin
(*
function DbiGetProp (hObj: hDBIObj;
iProp: Longint;
PropValue: Pointer;
iMaxLen: Word;
var iLen: Word): DBIResult stdcall;
dbNATIVEPASSTHRUHNDL
stmtNATIVEHNDL
curNATIVEHNDL
dbNATIVEHNDL
/* We will call the DbiGetProp like this:
correct it to your needs.
*/
DbiGetProp(hDBIObj(TDatabase.Handle),
drvNATIVESQLCA,
LOCAL_SQLCA,
sizeof(SQLCA_S),
len);
*)
LOCAL_SQLCA := new(PSQLCA);
try
DbiGetProp(hDBIObj(H), drvNATIVESQLCA, LOCAL_SQLCA, sizeof(SQLCA_S), len);
finally
/* At this point we already have our informations,
the only important thing is to check sqlca.sqlcode
if an error occured. If so you can be sure whatever you
retrieve from sqlca.sqlerrd[1], it is not the serial.
*/
if LOCAL_SQLCA^.sqlcode = 0 then
/ retrieve the serial */
Result := LOCAL_SQLCA^.sqlerrd[1]
else
/* return it in an error code */
Result := -(Abs(LOCAL_SQLCA^.sqlcode));
Dispose(LOCAL_SQLCA);
end;
end;
Thats it. You will have this special kind of function after the execution of a TQuery to retrieve the serial. As you can see at the top there are some more (interesting) comments behind the definitions of SQLCA. Be aware to execute such queries after an execution but before you close your TQuery (the internal cursorhandle seems to be lost after close).
Additional informations: