When writing a prepared statement in a Base macro, I can’t seem to replace the question mark within
WHERE LIKE '%?%'. My guess is that it is because one might actually want to search for a question mark instead of replacing it in a prepared statement. Can I escape the question mark somehow so it can be replaced via
.setString() in the prepared statement?
For example, take my generic function to find anything in any table and column:
Global Function findRecordsWhereSQL(sDatabaseTable As String, sWhereColumn As String, sWhereLike As String) Dim oForm As Object Dim oConnection As Object Dim oSqlStatement As Object 'the object that executes the SQL command Dim sSql As String 'the actual text of the SQL query/statement Dim oResult As Object oConnection = thisComponent.Drawpage.Forms(0).activeConnection() sSql = "SELECT * FROM ? WHERE ? LIKE '%?%' " 'base SQL oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement oSqlStatement.setString(1, sDatabaseTable) 'insert search table oSqlStatement.setString(2, sWhereColumn) 'insert search column oSqlStatement.setString(3, sWhereLike) 'insert search term findRecordsWhereSQL = oSqlStatement.executeQuery() oConnection.close() End Function
It causes an error, but I don’t know why. If I call the function like this:
Dim oResult As Object oResult = findRecordsWhereSQL("account", "name", "John")
I get the following error:
Anyone know how to get around this problem so I can pass a function parameter to the
WHERE LIKE '%?%' ?
If I execute the following statement directly in MySQL, it returns exactly the results expected, accounts with “John” as part of their name.
SELECT * FROM account WHERE name LIKE '%John%'
So how to get the function to pass my string variable into the prepared statement where the last question mark is?