Le May 28, 2008 ? 6:02 AM, Dave Coventry a ?crit :
Show quoted text 10 lines
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)
Show quoted text 9 lines
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.
Show quoted text 11 lines
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.
Show quoted text 5 lines
SQLQuery1.Open;
enquiryreferencenumber
.Text:=SQLQuery1.FieldByName('enquiryreferencenumber').AsString;
I think
enquiryreferencenumber.Text :=
IntToStr(SQLQuery1.FieldByName('enquiryreferencenumber').AsInteger);
would be more appropriated
Show quoted text 10 lines
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
"Intelligence is 10 million rules."
-- Douglas Lenat
