INSERT with default values (out serial)

yapt · Post by **yapt** » Wed 25 Mar 2009 17:28

Hello,

I have a Query like this:

INSERT INTO log
VALUES
(:PTIMESTAMP1
,:PSERIALV1
,:PDIRIP
,:PMSG);

on a PgQuery on the SQL-text.

But I have two problems.
1)
:PTIMESTAMP parameter (pgtimestamp) is _always_ getting 'Object' as default value. In spite of I am deleting and checking null. Close/Open and again it is there.

2)
:PSERIALV1 parameter (serial), I would like to get what is the sequence number assigned after the pgquery.execute method.

How can I do that ?

Greetings.

Plash · Post by **Plash** » Thu 26 Mar 2009 08:46

Parameters in you SQL statement are input. So you can assing values to these parameters before executing the query. But you should not read values after executing.

You can modify the statement in the following way to get the values that were inserted:

INSERT INTO log (PDIRIP, PMSG)
VALUES (:PDIRIP, :PMSG)
RETURNING PTIMESTAMP1, PSERIALV1

This statement returns a recordset like SELECT stetement. You should open it with the Open method, and read values from the fields:

d := PgQuery.FieldByName('PTIMESTAMP1').AsDateTime;
i := PgQuery.FieldByName('PSERIALV1').AsInteger;

yapt · Post by **yapt** » Thu 26 Mar 2009 08:56

Great.. I will try it....

Thanks...

yapt · Post by **yapt** » Thu 26 Mar 2009 09:14

I have try it:

INSERT INTO mwi.log
(empresa, dirip, /*hora(timestamp), evento(serial)*/ usuario, aplicacion, mensaje, texto)
VALUES
(:PEMPRESA, :PDIRIP, :PUSUARIO, :PAPP, :PMENSAJE, :PTEXTO)
RETURNING evento;

But I have an error:
'Parameter 'evento' not found'

In the delphi-code next line:
QryLogAccion.Open;
---> result := QryLogAccion.ParamByName('evento').AsLargeInt;
QryLogAccion.Close;

Plash · Post by **Plash** » Thu 26 Mar 2009 09:19

You should use a field instead of parameter:

Code: Select all

QryLogAccion.FieldByName('evento').AsInteger;

yapt · Post by **yapt** » Thu 26 Mar 2009 09:26

Oppppppppppppppssssss.....

sorry...

It is working right now...

Thanks...