How PostgreSQL works

Working with PostgreSQL works best using PreparedStatements. No doubt about it.
What puzzles me is how I understand PostgreSQL should work with PreparedStatements.

  1. prepare a PreparedStatement;

  2. bind the parameters to the values

  3. execute the PreparedStatement for the first time

  4. — database is being updated now

  5. bind new values to the same PreparedStatement

  6. execute it for the second time

  7. — database is again being updated now

My Q:
a. will the PostgreSQL database-engine cash the PreparedStatement when executed for the first time ?
b. and only get new parametervalues being transfered and execute the cashed PreparedStatement with these parameters ?
c. PostgreSQL engine clears it’s cashed PreparedStatements when the connection gets terminated ?

I do measure performance improvements when I only prepare the statement once in my application. The first time for example takes 16 ms, the second etc. time takes around 10 ms. (remote database) , which I don’t see if I re-prepare the statement every time.

@Kem Tekinay and other PostgreSQL experts, can you give you’re thoughts about it.

PostgreSQL “expert”? Me?!? HA!

However, once you prepare the statement, yes, it’s cached and reused with new values. In pure SQL, you’d release the statement or terminate the connection to get rid of it. In Xojo, your only option right now is to terminate the connection.

In practice, I only reuse a PreparedStatement within the confines of a method or, if appropriate, a class. Do not leave a PreparedStatement lying around if you’re unsure if the database connection will be the same one that was originally used to prepare the statement.

If you use exactly the same SQL to prepare two different statements, I think PSQL will cache two individual Prepared Statements without checking to see if they are, in fact, the same, but I don’t recall if I actually tested that.

[[quote=333841:@Kem Tekinay]In practice, I only reuse a PreparedStatement within the confines of a method or, if appropriate, a class[/quote]
So do I and no worries here since you confirm my way. Thanks Kem.

I use a four step process with prepared statements for INSERT and UPDATE commands via SQLDatabaseMBS. I create an array for the field names, types and values once. I pass these arrays to a method to build these three arrays with their types and new values. Then I use these arrays to build the SQL statement. Then I bind them according to the array types. Then I run the SQL Command. It seems clean to me and works with PostgreSQL, MySQL and SQLite, so far.

[code]'Step 1: Create the prepared statement and arrays
Dim db As SQLDatabaseMBS = getMyDatabaseFromHere
Dim tempSQLPreparedStatementMBS As SQLPreparedStatementMBS
Dim tempSQL As String
Dim fieldNames(-1) As String
Dim fieldTypes(-1) As String
Dim fieldValues(-1) As Variant

'Step 2: Populate the matching Prepared Statement arrays e.g.
commonSQL.doPreparedArraysWAD(fieldNames, fieldTypes, fieldValues, “getwinlogs”, “Boolean”, getWinLogsCheckbox.Value)
commonSQL.doPreparedArraysWAD(fieldNames, fieldTypes, fieldValues, “scannewfilelogsminutes”, “Integer”, scanNewFileLogsMinutes.Text.val)
commonSQL.doPreparedArraysWAD(fieldNames, fieldTypes, fieldValues, “restartcollectittime”, “Text”, restartCollectITTimeHoursPopupMenu.Text + “:” + restartCollectITTimeMinutesPopupMenu.Text)
…

'Step 3: Build the SQL statement, prepare the SQLDatabaseMBS database, prepare the bind types
tempSQL = commonSQL.getPreparedSQLWAD(“UPDATE hosts SET”, fieldNames, "WHERE id = " + str(collectorID))
tempSQLPreparedStatementMBS = db.Prepare(tempSQL)
commonSQL.doPreparedBindTypeWAD(tempSQLPreparedStatementMBS, fieldNames, fieldTypes, fieldValues)

'Step 4: run the SQL prepared statement
tempSQLPreparedStatementMBS.SQLExecuteMT
if db.Error then commonDatabases.doDisplayDatabaseErrorWAD(db)
[/code]