SelectSQL, ParamArray

ebloom

Automatic prepared statements in SelectSQL
Allow Variant data type in ParamArray

I really like the automatic prepared statements that Xojo included with SelectSQL in API2. I use a jacket routine around all my Select and Execute statements for consistent error handling throughout the application, without needed to duplicate the code with every database call.
Here's my Xojo code:

Public Function DBSelect(ByRef db As MySQLCommunityServer, ByRef rs As RowSet, sql As String, ParamArray params() As Variant) As Boolean
  Try
    rs = db.SelectSQL(sql, params)
  Catch err As DatabaseException
    // application specific error handling goes here
    Return False
  End Try
 
  Return True
End Function

Objo has prepared statements, but they require manually setting each value. It might be possible to do this in Objo but the ParamArray would need to be Variant. I understand the desire to keep variants out of the language, but this might be a valid exception. Or maybe there's another way to do it?

bgrommes

ebloom It might be possible to do this in Objo but the ParamArray would need to be Variant. I understand the desire to keep variants out of the language, but this might be a valid exception. Or maybe there's another way to do it?

An Array(Of Object) should suffice, no? Especially since TypeOf(someObjectReference) returns the actual type, not Object. You might need a Select/Case to cycle through the possibilities and a relevant cast to fully access and use the actual type, but due to the need to customize the mapping of native Objo types to the DB types available in different DBs, it seems likely that you'd need such a decision tree anyway.

ebloom

bgrommes Looks like a good workaround for the variant issue. I should be able to use this to "manually" set up the prepared statement in the jacket routine. Thank you!

Garry

This is implemented and will ship in the next release. Thanks for the nudge, and bgrommes was right that no Variant exception was needed.

https://feedback.objo.dev/feature/1405

SelectSQL, ExecuteSQL, and their Async variants now take optional parameters on every database class (SQLiteDatabase, MySQLDatabase, PostgreSQLDatabase, SQLServerDatabase, ODBCDatabase) and on the shared Database interface. Arguments bind to the ? placeholders in order, and the prepared statement is created, executed, and disposed for you:

Var rs As RecordSet = db.SelectSQL("SELECT name, age FROM users WHERE age > ? AND city = ?", 21, "Perth")

db.ExecuteSQL("INSERT INTO users (name, age) VALUES (?, ?)", "Alice", 30)

Each argument's runtime type decides how it binds, using the same mapping as PreparedStatement.Bind: String, Integer, Double, Decimal, Boolean, MemoryBlock (binary), and Nothing (SQL NULL). Supplying a number of values that doesn't match the ? placeholders throws a DatabaseException naming both counts, and a ? inside a string literal in your SQL is not treated as a placeholder.

For your jacket routine, one nuance worth knowing: a captured ParamArray forwards as a single value today, so db.SelectSQL(sql, params) inside the jacket won't splat into individual bindings. The pattern that works is discrete Object parameters, which keep the full runtime type detection:

Public Function DBSelect(db As Database, ByRef rs As RecordSet, sql As String, p0 As Object, p1 As Object) As Boolean
  Try
    rs = db.SelectSQL(sql, p0, p1)
    Return True
  Catch e As DatabaseException
    # Application-specific error handling goes here
    Return False
  End Try
End Function

If most of your calls share a shape, a couple of overloads like that cover the common cases, and call sites that don't fit can use db.SelectSQL(...) directly inside their own Try/Catch - which is now a one-line call instead of a Prepare plus per-index Binds.

ebloom

Garry Thanks! I'm having a lot of trouble getting the jacket routine working with ParamArray - I keep getting empty recordsets. I'll wait until you release the "automatic prepare and bind" and try again.

Additional background: The jacket routine really helps with database applications - the most common issue is the server closes an inactive session, all that's needed is to catch the error, reconnect, and try again. As the application developer I don't always get to control settings on the server, or how long a user leave the app unattended. I've also found that minor network glitches will cause a connection to close.

Garry

That explains the empty recordsets. A captured ParamArray was being passed as one array-valued argument instead of being expanded into individual bindings. I’ve now fixed that missing case.

Your jacket routine will work directly in the next release: Database calls accept forwarded parameter arrays.

Public Function DBSelect(db As Database, ByRef rs As RecordSet, sql As String, ParamArray parameters() As Object) As Boolean
    Try
        rs = db.SelectSQL(sql, parameters)
        Return True
    Catch firstError As DatabaseException
        If Not firstError.IsConnectionFailure Then
            # Application-specific error handling.
            Return False
        End If
    End Try

    # The connection was unavailable or broken, so reconnect and retry the read once.
    Try
        db.Close()
        db.Connect()
        rs = db.SelectSQL(sql, parameters)
        Return True
    Catch retryError As DatabaseException
        # Application-specific error handling.
        Return False
    End Try
End Function

SelectSQL, ExecuteSQL, and their async variants now have an Object() overload alongside the existing ParamArray overload. This is available through the shared Database interface and on SQLite, MySQL, PostgreSQL, SQL Server, and ODBC databases.

I’ve also added DatabaseException.IsConnectionFailure. It is True when there is no open connection or the provider reports a connection-level/network failure, but remains False for ordinary SQL, constraint, authentication, and command-timeout errors. This lets the jacket routine decide when reconnecting is appropriate without relying on provider-specific error codes.

One important caveat: retrying a read is generally safe, but don’t automatically retry ExecuteSQL unless the write is idempotent or otherwise protected against duplication. A connection can disappear after the server committed the write but before the client received the response.