[Lazarus] Postgres database again...

[Damien Gerard]

Le May 27, 2008 à 10:28 AM, Dave Coventry a écrit :

> Damien this is the code I'm using:
>
>      sqlq:='INSERT INTO registration
> (enquiryreferencenumber
> ,fname
> ,sname
> ,initials
> ,id
> ,titlecombobox
> ,postaladdy,town,country,pcode,cell,email,emailtld,enquirydate)
> ';
>      sqlq:=sqlq+'VALUES
> (nextval(''reg_seq''),'''+FName.Text+''','''+SName.Text 
> +''','''+Initials.Text+''','''+ID.Text+''','''+TitleComboBox.Text 
> +''','''+PostalAddy.Text+''','''+Town.Text+''','''+Country.Text 
> +''','''+PCode.Text+''','''+Cell.Text+''','''+email.Text 
> +''','''+emailTLD.Text+''','''+EnquiryDate.Text+''')';

As said before, you *should not* use nextval in your sql queries. Use  
a default value for the column instead and use the keyword RETURNING

>
>      PQConnection1.Connected:=True;
>      PQConnection1.ExecuteDirect('Begin Work;');

Transactions are useless here.

>
>      PQConnection1.ExecuteDirect(sqlq);
>      PQConnection1.ExecuteDirect('Commit Work;');
>      PQConnection1.Connected:=False;
>      SQLQuery1.SQL.Clear;
>      SQLQUery1.SQL.Append('SELECT enquiryreferencenumber FROM
> registration WHERE fname = '''+FName.Text+''' AND sname = '''+
> Sname.Text+''';');
>      SQLQuery1.Open;
>      enquiryreferencenumber.Text:=SQLQuery1.Fields[0].AsString;
>      SQLQuery1.Close;
>
> The error I get is because SQLQuery1.Fields[0]. is out of range, so
> there is nothing in SQLQuery1.Fields.
>

I am not an expert of SQL using fpc but if I remember well you should  
have a `Count` value to know how many rows you get.
I suppose that only means you got no results.

You should use `LIMIT 1` in your query to be sure to have a single row.

Instead of Clear/Append may be you should use SQLQuery1.SQL.Text :=  
'<my query>' but ask for confirmation.


> (I'm also, rather clumsily trying to access the last record using the
> fname and sname; I would prefer to have a more reiable way of
> retrieving it)

This should be your last record :
SELECT * FROM registration ORDER BY enquiryreferencenumber DESC LIMIT 1


If you want some advice about your table structure you can send me a  
private mail.


--
Damien Gerard
milipili at shikami.org

"Intelligence is 10 million rules."
    -- Douglas Lenat