I'm playing with a little application to talk to a postgresql server, using pqconnection & sqldb.
Using TSQLQuery, the abilty to prepare statements doesn't really seem to work in a useful way. I've posted the full code of my test program below, but the essential details are like this:
In postgresql, you can see what statements have been prepared in the current session by from pg_prepared_statements.
SELECT * FROM pg_prepared_statements
So, to test that, I create a TSQLQuery, and set its SQL.text
query.SQL.Text := 'select * from employee where salary>:sal;';
then prepare it. Selecting from pg_prepared_statements at this point, shows the prepared statement on the server. Then I set the query :sal parameter to a particular value, open it & print out the records.
Now I look again at pg_prepared_statements. The previously prepared statement has gone! So, if I want to run the query again with a different salary parameter value, it will have to be prepared again.
Kind of defeats the point of preparing the query in the first place.
Is this some aspect of TSQLQuery behaviour I can have control over? Maybe by hooking up the transaction component differently? Or can I use the methods of TPQConnection directly in such as way as to have this level of control?
Program prepare_test0;
{$MODE DELPHI}
{$H+}
uses
pqconnection, sqldb, sysutils, db;
var
connection : TPQConnection;
transaction : TSQLTransaction;
query : TSQLQuery;
function getConnection: TPQConnection;
begin
result := TPQConnection.Create(nil);
result.Hostname := '10.1.100.110';
result.Params.Add('port=5432');
result.DatabaseName := 'test';
result.UserName := 'user1';
result.Password := 'user1';
end;
procedure listPreparedStatements(connection:TPQConnection);
var
query : TSQLQuery;
begin
try
query := TSQLQuery.Create(nil);
query.Database := connection;
query.SQL.Text := 'select * from pg_prepared_statements';
query.Open;
Writeln('Prepared statements:');
while not query.Eof do
begin
Writeln(Format(' name: %s statement: %s', [query.FieldByName('name').AsString, query.FieldByName('statement').AsString]));
query.Next();
end;
query.Close;
query.Free;
except
on e:Exception do Writeln('Error in listPreparedStatements: ' + e.Message);
end;
end;
procedure listStaff(query:TSQLQuery; sal:integer);
begin
Writeln(Format('Listing staff with salary > %d :', [sal]));
try
query.Params.ParamByName('sal').AsInteger := sal;
query.Open;
while not query.Eof do
begin
Writeln(Format(' Surname: %-10s Forename: %-8s',[Query.FieldByName('last_name').AsString, Query.FieldByName('first_name').AsString]));
query.Next;
end;
query.Close;
except
on e:Exception do Writeln('Error in listStaff: ' + e.Message);
end;
end;
begin
connection := getConnection;
transaction := TSQLTransaction.Create(nil);
connection.Transaction := transaction;
connection.Open;
if connection.Connected then
begin
writeln('connected ok!');
query := TSQLQuery.Create(nil);
query.Database := connection;
query.SQL.Text := 'select * from employee where salary>:sal;';
query.Prepare;
listPreparedStatements(connection); // prepared statement exists on the server at this point
listStaff(query, 5000);
listPreparedStatements(connection); // prepared statement has gone!
listStaff(query, 7000);
listPreparedStatements(connection);
query.Unprepare;
query.Free;
end;
transaction.Free;
connection.Close;
connection.Free;
end.
output:
connected ok!
Prepared statements:
name: prepst0 statement: prepare prepst0 ( unknown ) as select * from employee where salary>$1;
name: prepst1 statement: prepare prepst1 as select * from pg_prepared_statements
Listing staff with salary > 5000 :
Surname: Waters Forename: David
Surname: Thomas Forename: Sarah
Surname: Deer Forename: Robert
Surname: Fowler Forename: Mary
Surname: Johnson Forename: Lisa
Surname: Brown Forename: Bill
Surname: Akin Forename: Seth
Prepared statements:
name: prepst4 statement: prepare prepst4 as select * from pg_prepared_statements
Listing staff with salary > 7000 :
Surname: Waters Forename: David
Surname: Thomas Forename: Sarah
Surname: Akin Forename: Seth
Prepared statements:
name: prepst8 statement: prepare prepst8 as select * from pg_prepared_statements
Recent
