prepared statements on postgresql

Forum > Database

(1/1)

rgh:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT * FROM pg_prepared_statementsSo, to test that, I create a TSQLQuery, and set its SQL.text

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:

--- Code: Text [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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_statementsListing 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_statementsListing 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

sky_khan:
I dont have Postgresql server to check but did you try controlling transaction manually ?

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---transaction := TSQLTransaction.Create(nil);transaction.Action=caNone;transaction.StartTransaction; ...

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---transaction.Commit;transaction.free;

rgh:

--- Quote from: SkyKhan on May 21, 2017, 07:04:06 pm ---did you try controlling transaction manually ?
--- End quote ---

Yes, I tried both putting the two calls to listStaff inside a manually invoke transaction:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---begin connection := getConnection; transaction := TSQLTransaction.Create(nil); transaction.Options := [stoExplicitStart]; connection.Transaction := transaction; connection.Open; if connection.Connected then begin writeln('connected ok!'); query := TSQLQuery.Create(nil); query.Database := connection; transaction.StartTransaction; 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); query.Unprepare; transaction.Commit; query.Free; end; transaction.Free; connection.Close; connection.Free;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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure listPreparedStatementsIndependentTransaction(connection:TPQConnection);var query : TSQLQuery; transaction : TSQLTransaction;begin Writeln('Prepared statements from independent transaction:'); try query := TSQLQuery.Create(nil); transaction := TSQLTransaction.Create(nil); transaction.Database := connection; transaction.Options := [stoExplicitStart]; query.SQLTransaction := transaction; transaction.StartTransaction; query.SQL.Text := 'select * from pg_prepared_statements'; query.Open; 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; transaction.Commit; transaction.Free; query.Free; except on e:Exception do Writeln('Error in listPreparedStatementsIndependentTransaction: ' + e.Message); end;end; results in the prepared statement not being visible at all, not even at the first attempt, after

--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---'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.

Navigation

[0] Message Index