[Lazarus] SQLQuery1: Field not Found: "enquiryreferencenumber"

[Damien Gerard]

Le May 28, 2008 à 6:02 AM, Dave Coventry a écrit :

> Hi.
>
> Following Damien's instructions I have DROPped my original table and
> created a new one in it's place:
>
> CREATE table registration (
> fname char(50),
> sname char(50),
> enquiryreferencenumber INT4 PRIMARY KEY DEFAULT nextval('reg_seq'))
> WITHOUT OIDS;

You can verify that the expression `WITHOUT OIDS` sticks to your needs
http://www.postgresql.org/docs/8.3/static/sql-createtable.html  
(postgres 8.3/8.2)


Note that fname and sname could be null here.
If you don't want this behavior, append `NOT NULL` after char(50).

I don't think you want char(X) in your case. I think You would prefer  
varchar(50)
If I store 'A sample text' :

varchar(50) gives 'A sample text'
    char(50) gives 'A sample  
text                                       '
(all unused char will be replaced by a space, it is a waste of disk  
space and you may have some strange behavior if you are not plenty  
aware of this)



> I have run my INSERT Query under PQConnection1 using the following  
> arguments:
>
>      sqlq:='INSERT INTO registration (fname,sname) ';
>      sqlq:=sqlq+'VALUES
> ('''+FName.Text+''','''+SName.Text+''')RETURNING
> enquiryreferencenumber;';
>      PQConnection1.Connected:=True;
>      PQConnection1.ExecuteDirect('Begin Work;');

As said before, transactions here are useless.
Transactions would be usefull for multiple insert/update, not for a  
single one.

>
>      PQConnection1.ExecuteDirect(sqlq);
>      PQConnection1.ExecuteDirect('Commit Work;');
>      PQConnection1.Connected:=False;
>
> Then I have attempted to retrieve the enquiryreferencenumber using
> SQLQuery1 as follows:
>      SQLQuery1.SQL.Text:='SELECT enquiryreferencenumber FROM
> registration WHERE fname='''+fname.Text+''' AND
> sname='''+sname.Text+'''ORDER BY enquiryreferencenumber DESC LIMIT
> 1;';

You should always use bind values to avoid sql injection (essentially  
sql errors).
After a quick look in examples of fpc, it should be like this :
SQLQuery1.SQL.Text := 'SELECT enquiryreferencenumber FROM registration  
WHERE fname = :fname AND sname = :sname ORDER BY  
enquiryreferencenumber DESC LIMIT 1;';
SQLQuery1.Params.ParamByName('fname').AsString := fname.Text;
SQLQuery1.Params.ParamByName('sname').AsString := sname.Text;

It is true for Insert, Update and Select of course.

>
>      SQLQuery1.Open;
>       
> enquiryreferencenumber 
> .Text:=SQLQuery1.FieldByName('enquiryreferencenumber').AsString;

I think
enquiryreferencenumber.Text :=  
IntToStr(SQLQuery1.FieldByName('enquiryreferencenumber').AsInteger);
would be more appropriated


>
>      SQLQuery1.Close;
>
> This gives me the following error:
>     SQLQuery1: Field not Found: "enquiryreferencenumber"
>
> Can anyone help?
>
> Is it possible that the fact that the field 'enquiryreferencenumber'
> is an integer and I'm accessing it AsString?


Tip: Set your search path before any queries.
Do not forget postgres structure is a bit more complicated than others  
DB.
Here is the structure :
<Database>
   + Schemas
        + Tables

Actually when you are logged on a postgres database, you should be  
attached to a database (for a standard configuration you have an user  
<-> a database).
But you may have several shemas, depanding your needs.

Assuming you have the following structure :
DBNameForExample / SchemaNameForExample / registration

I execute this query before all others (right after each new  
connection of course):
SET search_path TO SchemaNameForExample;

If you don't do this, you should have to give the full name of your  
tables (SELECT * FROM SchemaNameForExample.registration for example)



--
Damien Gerard
milipili at shikami.org

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