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.