How to catch return value of INSERT?

[ccezar]

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]

Before you execute the script after setting the SQL, try

Code: [Select]


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


[ccezar]

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]

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]

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]

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.