How to catch return value of INSERT?
lazarus / 16-Databases

----------------------------------------------------------------------
ccezar · 2007-06-07 18:50

Hello,

I'm using Lazarus 0.9.23-20070602 runing under Linux with fpc 2.0.4.

I've got table called 'groups' at PostgresQL 8.2 server, with two
fields:

- 'id'  which is definied as primary key serial
- 'descr' which is definied as varchar(40)

When I insert data into this table I'd like to know what was the 'id'
value assigned to freshly added record. In pure SQL (from the pgsql
CLI level) it's quite trivial:

Code: [Select]INSERT INTO groups(descr) VALUES ('this is test')
RETRUNING id;

and it returns what I need:

Code: [Select]id
___
1
(1 row)


So I've tried to grab it in lazarus giving to TSQLQuery object SQL
property following value: 'Code: [Select]INSERT INTO groups(descr)
VALUES ('this is test') RETRUNING id INTO :WYN;'

The problem is that at run-time my app says: 'pq: Unknown fieldtype
for parameter "WYN"'. After googling a bit I've tried the following:

Code: [Select]TSQLQuery1.Params[0].AsIntereger := 0;

and

Code: [Select]TSQLQuery1.Params.ParamByName('WYN').AsInteger := 0;

but it doesn't help. There were no run-time errors on thouse lines,
they compiled and run fine - but still when I was trying to catch
returning value of INSERT systems says: 'pq: Unknown fieldtype for
parameter "WYN"'

What I'm doing wrong? Any suggestions? Flames? Obvious RTFM FAQ
pointers? :wink:

TIA,

Cezar

----------------------------------------------------------------------
RudieD · 2007-06-08 14:54

Before you execute the script after setting the SQL, try

Code: [Select]
  with SQLQuery1.Params[0] do
  begin
    DataType := ftInteger;
    ParamType := ptOutput;
  end;

----------------------------------------------------------------------
ccezar · 2007-06-09 09:24

Quote from: "RudieD"Before you execute the script after setting the
SQL, try

Code: [Select]
  with SQLQuery1.Params[0] do
  begin
    DataType := ftInteger;
    ParamType := ptOutput;
  end;


Thank you Rudie, I'll try it. BTW - I was trying to play a little bit
with this two options via the Object Inspector properties, but nothing
happened - eg. they returned to default values.

regards,

Cezar

----------------------------------------------------------------------
ccezar · 2007-06-11 14:19

Hello,

I'm being really frustrated! :-(  I can't grab in no way value
returned by 'INSERT INTO database VALUES values RETURNING fieldname'.
Lazarus can't grab 'default naswer value' from such a question (I've
haven't found anything about it), and when I play with questions like:

Code: [Select]INSERT INTO database VALUES values RETURNING fieldname
INTO :wyn

Lazarus pq driver translates this at the database level into phrase:

Code: [Select] "INSERT INTO database VALUES values RETURNING fieldname
INTO $1"

and dies with following error:

Code: [Select]"PQConnection1: Preparation of query failed (PostgresQL:
ERROR: syntax error at or near "$1")

The error pointer (dash -> ^) points the "RETURNING" keyword.

So it looks like Lazarus makes wrong query preparation and trys to ask
PostgresQL to fill the value - but it should just read the value
returned by the query and assign it to the parameter (which is of
ptOutput type).  Or maybe I'm doing something wrong? Any help? Code
snippets? Urls?

Thanx in advance,

Cezar

----------------------------------------------------------------------
ccezar · 2007-06-11 22:25

OK, problem has been solved by... switching to ZEOS 6.6.1 database
abstraction layer :-P 

My opinion from last few days of playing with Lazarus is that the
native database components set seems to be seriously buggy, or (at
last) unmaintained. Even date and time fields are displayed wrong when
accesed via them. So as for now my choice is ZEOS - it simply works.

I think I'll give native set a try after Lazarus 1.00 and fpc 2.1.5.

Anyway - Lazarus and fpc are really great software and I'd like to say
big THANK YOU VERY MUCH! to the developers and community! :-)

----------------------------------------------------------------------
Vincent Snijders · 2007-06-12 06:20

Quote from: "ccezar"
My opinion from last few days of playing with Lazarus is that the
native database components set seems to be seriously buggy, or (at
last) unmaintained. Even date and time fields are displayed wrong when
accesed via them. So as for now my choice is ZEOS - it simply works.

I think I'll give native set a try after Lazarus 1.00 and fpc 2.1.5.


Since the database components (the non visual parts that you can
replace by ZEOS) are part of fpc - only the lpk's are part of lazarus,
not the actual units with components -, you don't need to wait until
Lazarus 1.0.

My opinion is that the the database components from fpc are very much
maintained and that is why use see such much improvement between 2.0.4
and 2.1.5. So you could say there may have been seriously buggy, but
at least very much maintained.

BTW: I think this forum is not the best place to get support for using
the sqldb components, you have a much better chance to get good
answers (from the maintainers) on the fpc-pascal and the lazarus
mailing list.