* * *

Author Topic: prepared statements on postgresql  (Read 809 times)

rgh

  • New member
  • *
  • Posts: 40
prepared statements on postgresql
« on: May 21, 2017, 06:22:13 pm »
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.
Code: SQL  [Select]
  1. SELECT * FROM pg_prepared_statements
So, to test that, I create a TSQLQuery, and set its SQL.text
Code: Pascal  [Select]
  1. 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?

Code: Pascal  [Select]
  1. Program prepare_test0;
  2.  
  3. {$MODE DELPHI}
  4. {$H+}
  5.  
  6. uses
  7.   pqconnection, sqldb, sysutils, db;  
  8.  
  9. var  
  10.   connection  : TPQConnection;
  11.   transaction : TSQLTransaction;
  12.   query       : TSQLQuery;
  13.  
  14. function getConnection: TPQConnection;
  15. begin
  16.   result := TPQConnection.Create(nil);
  17.   result.Hostname := '10.1.100.110';
  18.   result.Params.Add('port=5432');            
  19.   result.DatabaseName := 'test';
  20.   result.UserName := 'user1';
  21.   result.Password := 'user1';
  22. end;
  23.  
  24. procedure listPreparedStatements(connection:TPQConnection);
  25. var
  26.   query       : TSQLQuery;
  27. begin
  28.   try
  29.     query := TSQLQuery.Create(nil);
  30.     query.Database := connection;
  31.     query.SQL.Text := 'select * from pg_prepared_statements';
  32.     query.Open;
  33.     Writeln('Prepared statements:');
  34.     while not query.Eof do
  35.       begin
  36.         Writeln(Format('  name: %s statement: %s', [query.FieldByName('name').AsString, query.FieldByName('statement').AsString]));
  37.         query.Next();
  38.       end;
  39.     query.Close;
  40.     query.Free;
  41.   except
  42.     on e:Exception do Writeln('Error in listPreparedStatements: ' + e.Message);
  43.   end;
  44. end;
  45.  
  46. procedure listStaff(query:TSQLQuery; sal:integer);
  47. begin
  48.   Writeln(Format('Listing staff with salary > %d :', [sal]));
  49.   try
  50.     query.Params.ParamByName('sal').AsInteger := sal;
  51.     query.Open;
  52.     while not query.Eof do
  53.       begin
  54.         Writeln(Format('  Surname: %-10s Forename: %-8s',[Query.FieldByName('last_name').AsString, Query.FieldByName('first_name').AsString]));
  55.         query.Next;
  56.       end;
  57.     query.Close;
  58.   except
  59.     on e:Exception do Writeln('Error in listStaff: ' + e.Message);
  60.   end;
  61. end;
  62.  
  63. begin
  64.   connection := getConnection;
  65.   transaction := TSQLTransaction.Create(nil);
  66.   connection.Transaction := transaction;
  67.   connection.Open;
  68.   if connection.Connected then
  69.     begin
  70.       writeln('connected ok!');
  71.       query := TSQLQuery.Create(nil);
  72.       query.Database := connection;
  73.       query.SQL.Text := 'select * from employee where salary>:sal;';
  74.       query.Prepare;
  75.       listPreparedStatements(connection);   // prepared statement exists on the server at this point
  76.       listStaff(query, 5000);  
  77.       listPreparedStatements(connection);   // prepared statement has gone!
  78.       listStaff(query, 7000);        
  79.       listPreparedStatements(connection);              
  80.       query.Unprepare;
  81.       query.Free;
  82.     end;
  83.   transaction.Free;
  84.   connection.Close;
  85.   connection.Free;
  86. end.
  87.  

output:

Code: Text  [Select]
  1. connected ok!
  2. Prepared statements:
  3.   name: prepst0 statement: prepare prepst0 ( unknown ) as select * from employee where salary>$1;
  4.   name: prepst1 statement: prepare prepst1  as select * from pg_prepared_statements
  5. Listing staff with salary > 5000 :
  6.   Surname: Waters     Forename: David  
  7.   Surname: Thomas     Forename: Sarah  
  8.   Surname: Deer       Forename: Robert  
  9.   Surname: Fowler     Forename: Mary    
  10.   Surname: Johnson    Forename: Lisa    
  11.   Surname: Brown      Forename: Bill    
  12.   Surname: Akin       Forename: Seth    
  13. Prepared statements:
  14.   name: prepst4 statement: prepare prepst4  as select * from pg_prepared_statements
  15. Listing staff with salary > 7000 :
  16.   Surname: Waters     Forename: David  
  17.   Surname: Thomas     Forename: Sarah  
  18.   Surname: Akin       Forename: Seth    
  19. Prepared statements:
  20.   name: prepst8 statement: prepare prepst8  as select * from pg_prepared_statements
  21.  
  22.  

sky_khan

  • Guest
Re: prepared statements on postgresql
« Reply #1 on: May 21, 2017, 07:04:06 pm »
I dont have Postgresql server to check but did you try controlling transaction manually ?

Code: Pascal  [Select]
  1. transaction := TSQLTransaction.Create(nil);
  2. transaction.Action=caNone;
  3. transaction.StartTransaction;
  4.  
...
Code: Pascal  [Select]
  1. transaction.Commit;
  2. transaction.free;
  3.  

rgh

  • New member
  • *
  • Posts: 40
Re: prepared statements on postgresql
« Reply #2 on: May 22, 2017, 06:25:28 pm »
did you try controlling transaction manually ?

Yes, I tried both putting the two calls to listStaff inside a manually invoke transaction:
Code: Pascal  [Select]
  1. begin
  2.   connection := getConnection;
  3.   transaction := TSQLTransaction.Create(nil);
  4.   transaction.Options := [stoExplicitStart];
  5.   connection.Transaction := transaction;
  6.   connection.Open;
  7.   if connection.Connected then
  8.     begin
  9.       writeln('connected ok!');
  10.       query := TSQLQuery.Create(nil);
  11.       query.Database := connection;
  12.       transaction.StartTransaction;
  13.       query.SQL.Text := 'select * from employee where salary>:sal;';
  14.       query.Prepare;
  15.       listPreparedStatements(connection);                         // prepared statement exists on the server at this point
  16.       listStaff(query, 5000);  
  17.       listPreparedStatements(connection);                         // prepared statement has gone!
  18.       listStaff(query, 7000);            
  19.       query.Unprepare;
  20.       transaction.Commit;
  21.       query.Free;
  22.     end;
  23.   transaction.Free;
  24.   connection.Close;
  25.   connection.Free;
  26. end.
which make no difference. By the time the program gets to line 17, after listStaff as been called the first time, the previously prepared statement has gone.

The other database interaction that could be within a transaction, is the query that  selects from pg_prepared_statements. Putting that in a transaction

Code: Pascal  [Select]
  1. procedure listPreparedStatementsIndependentTransaction(connection:TPQConnection);
  2. var
  3.   query       : TSQLQuery;
  4.   transaction : TSQLTransaction;
  5. begin
  6.   Writeln('Prepared statements from independent transaction:');
  7.   try
  8.     query := TSQLQuery.Create(nil);
  9.     transaction := TSQLTransaction.Create(nil);
  10.     transaction.Database := connection;
  11.     transaction.Options := [stoExplicitStart];
  12.     query.SQLTransaction := transaction;
  13.     transaction.StartTransaction;
  14.     query.SQL.Text := 'select * from pg_prepared_statements';
  15.     query.Open;
  16.     while not query.Eof do
  17.       begin
  18.         Writeln(Format('  name: %s statement: %s', [query.FieldByName('name').AsString, query.FieldByName('statement').AsString]));
  19.         query.Next();
  20.       end;
  21.     query.Close;
  22.     transaction.Commit;
  23.     transaction.Free;
  24.     query.Free;
  25.   except
  26.     on e:Exception do Writeln('Error in listPreparedStatementsIndependentTransaction: ' + e.Message);
  27.   end;
  28. end;
  29.  
  30.  
results in the prepared statement not being visible at all, not even at the first attempt, after
Code: SQL  [Select]
  1. 'select * from employee where salary>:sal;';
has been initially prepared but the query hasn't been opened.

So I'm supposing that  TSQLQuery doesn't have the granularity to interact with postgresql at this level of detail.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus