How to catch return value of INSERT?

Forum > Databases

(1/2) > >>

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: ---INSERT INTO groups(descr) VALUES ('this is test') RETRUNING id;
--- End code ---

and it returns what I need:

--- Code: ---id
___
1
(1 row)

--- End code ---

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

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: ---TSQLQuery1.Params[0].AsIntereger := 0;
--- End code ---

and

--- Code: ---TSQLQuery1.Params.ParamByName('WYN').AsInteger := 0;
--- End code ---

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: ---
with SQLQuery1.Params[0] do
begin
DataType := ftInteger;
ParamType := ptOutput;
end;

--- End code ---

ccezar:

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

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

--- End code ---

--- End quote ---

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: ---INSERT INTO database VALUES values RETURNING fieldname INTO :wyn
--- End code ---

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

--- Code: --- "INSERT INTO database VALUES values RETURNING fieldname INTO $1"
--- End code ---

and dies with following error:

--- Code: ---"PQConnection1: Preparation of query failed (PostgresQL: ERROR: syntax error at or near "$1")
--- End code ---

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! :-)

Navigation

[0] Message Index

[#] Next page