RE: serial field with delphi
This is a multi-part message in MIME format.
------=_NextPart_000_0030_01BF0B65.D6E49410
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
Hi JM!
One from many ways solving of this problem is:
1) In Delphi
Query.SQL.Clear;
Query.SQL.Add('execute procedure MyInsertIntoTable(:code, :libelle)');
...
Query.ParamByName('code').AsInteger :=3D 0;
Query.ParamByName('libelle').AsString :=3D 'toto';
Query.Open;
if (Query.RecordCount <> 1) then begin
raise Exception.Create('somebody problem');
end;=20
if (trim(Query.Fields[0].AsString) =3D 'ERROR') then
raise Exception.Create('Record not inserted');
end;
InsertedSerialNumber :=3D StrToInt(trim(Query.Fields[0].AsString))
...
2) In SPL
create procedure MyInsertIntoTable (code integer, libelle char(100))
returning char(11);=20define esql integer;=20
define eisam integer;
define eMyErr char(11);
begin
on exception set esql, eisam, eMyErr
return eMyErr;
end exception;
insert into NameTable values (code, libelle); if dbinfo('sqlca.sqlerrd2')<>1then
raise exception -746, 0, 'ERROR';
end if;
return dbinfo('sqlca.sqlerrd1'); --last inserted serial value
end;
end procedure;
I hope that I helped to you.
Dmitry A. Yepifanov
D.Yepifanov@belpsb.minsk.by
------=_NextPart_000_0030_01BF0B65.D6E49410
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>
<META content=3Dtext/html;charset=3Dkoi8-r http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>
<P><FONT size=3D2>Hi JM!</FONT></P>
<DIV><FONT color=3D#000000><FONT size=3D2>One from many ways solving of =
this problem=20
is:</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2></FONT></FONT><FONT=20
size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>1) In =
Delphi</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2></FONT></FONT><FONT=20
size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000><FONT =
size=3D2>Query.SQL.Clear;</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>Query.SQL.Add('execute =
procedure=20
MyInsertIntoTable(:code, :libelle)');</FONT></FONT></DIV>
<DIV><FONT size=3D2>...</FONT></DIV>
<DIV><FONT color=3D#000000><FONT =
size=3D2>Query.ParamByName('code').AsInteger :=3D=20
0;</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT =
size=3D2>Query.ParamByName('libelle').AsString :=3D=20
'toto';</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>Query.Open;</FONT></FONT><FONT =
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>if (Query.RecordCount <> =
1) then=20
begin</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> raise =
Exception.Create('somebody problem');</FONT></FONT><FONT =
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>end; </FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>if =
(trim(Query.Fields[0].AsString) =3D=20
'ERROR') then</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> raise =
Exception.Create('Record not inserted');</FONT></FONT><FONT =
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>end;</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>InsertedSerialNumber :=3D=20
StrToInt(trim(Query.Fields[0].AsString))</FONT></FONT><FONT =
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>...</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2></FONT></FONT><FONT=20
size=3D2></FONT> </DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>2) In SPL</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>create procedure =
MyInsertIntoTable (code=20
integer, libelle char(100))</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>returning char(11); =
</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>define esql integer; =
</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>define eisam =
integer;</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>define eMyErr =
char(11);</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>begin</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> on exception set =
esql, eisam,=20
eMyErr</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> =
return=20
eMyErr;</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> end=20
exception;</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> insert into =
NameTable values=20
(code, libelle);</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> if=20
dbinfo('sqlca.sqlerrd2')<>1then</FONT></FONT><FONT =size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> raise =
exception=20
-746, 0, 'ERROR';</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> end =
if;</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2> return=20
dbinfo('sqlca.sqlerrd1'); --last inserted serial =value</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>end;</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>end =
procedure;</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2></FONT></FONT><FONT=20
size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000><FONT size=3D2></FONT></FONT><FONT=20
size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>I hope that I helped to=20
you.</FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT color=3D#000000><FONT size=3D2></FONT></FONT><FONT=20
size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000><FONT size=3D2>Dmitry A. =
Yepifanov</FONT></FONT><FONT=20
size=3D2></FONT></DIV>
<DIV><A href=3D"mailto:D.Yepifanov@belpsb.minsk.by"><FONT =
color=3D#000000 face=3D""=20
size=3D2>D.Yepifanov@belpsb.minsk.by</FONT></A></DIV></DIV></BODY></HTML>=
------=_NextPart_000_0030_01BF0B65.D6E49410--