# Revision history [back]

Figured it out, sort of.

All the examples of using prepared statements in LibreOffice Base macro documentation show the question marks on the right side of the equation, for example:

stSql = "UPDATE author " _
& "SET lastname = ?, firstname = ?" _
& "WHERE ID = ?"


Apparently it is not possible to replace column names and table names with ? and insert via function parameter. So you can't do SET ? = ?, ? = ? WHERE ? = ? and insert all the column names. Kinda lame. However, I came up with a compromise. It's a hybrid between a prepared statement and not-so-prepared. The table and column name are concatenated, and the search term is prepared. I have to put the percentage signs in the passed parameter to make it work.

So my new function looks like this... still in development, but this much works, and the msgbox outputs the matching record names:

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 " & sDatabaseTable & " WHERE " & sWhereColumn & " LIKE ? " 'base SQL
oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement
oSqlStatement.setString(1, sWhereLike) 'insert search term

findRecordsWhereSQL = oSqlStatement.executeQuery()

While findRecordsWhereSQL.next
sRecord = findRecordsWhereSQL.getString(1)
msgbox sRecord, 0, "Record"
Wend
End Function


Then, when I call the function, I have to include the %% wildcards IN the passed search term...

Global Sub findAccount()
Dim oResult As Object

oResult = findRecordsWhereSQL("account", "name", "%John%")
End Sub


Figured it out, sort of.

All the examples of using prepared statements in LibreOffice Base macro documentation show the question marks on the right side of the equation, equation (after the = equal sign), for example:

stSql = "UPDATE author " _
& "SET lastname = ?, firstname = ?" _
& "WHERE ID = ?"


Apparently it is not possible to replace column names and table names with ? and insert via function parameter. So you can't do SET ? = ?, ? = ? WHERE ? = ? and insert all the column names. Kinda lame. However, I came up with a compromise. It's a hybrid between a prepared statement and not-so-prepared. The table and column name are concatenated, and the search term is prepared. I have to put the percentage signs in the passed parameter to make it work.

So my new function looks like this... still in development, but this much works, and the msgbox outputs the matching record names:

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 " & sDatabaseTable & " WHERE " & sWhereColumn & " LIKE ? " 'base SQL
oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement
oSqlStatement.setString(1, sWhereLike) 'insert search term

findRecordsWhereSQL = oSqlStatement.executeQuery()

While findRecordsWhereSQL.next
sRecord = findRecordsWhereSQL.getString(1)
msgbox sRecord, 0, "Record"
Wend
End Function


Then, when I call the function, I have to include the %% wildcards IN the passed search term...

Global Sub findAccount()
Dim oResult As Object

oResult = findRecordsWhereSQL("account", "name", "%John%")
End Sub


Figured it out, sort of.

All the examples of using prepared statements in LibreOffice Base macro documentation show the question marks on the right side of the equation (after the = equal sign), for example:

stSql = "UPDATE author " _
& "SET lastname = ?, firstname = ?" _
& "WHERE ID = ?"


Apparently it is not possible to replace column names and table names with ? and insert via function parameter. So you can't do SET ? = ?, ? = ? WHERE ? = ? replace author, lastname, firstname and ID with question marks and insert all the column names. them via .setString(). Kinda lame. However, I came up with a compromise. It's a hybrid between a prepared statement and not-so-prepared. The table and column name are concatenated, and the search term is prepared. I have to put the percentage signs in the passed parameter to make it work.

So my new function looks like this... still in development, but this much works, and the msgbox outputs the matching record names:

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 " & sDatabaseTable & " WHERE " & sWhereColumn & " LIKE ? " 'base SQL
oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement
oSqlStatement.setString(1, sWhereLike) 'insert search term

findRecordsWhereSQL = oSqlStatement.executeQuery()

While findRecordsWhereSQL.next
sRecord = findRecordsWhereSQL.getString(1)
msgbox sRecord, 0, "Record"
Wend
End Function


Then, when I call the function, I have to include the %% wildcards IN the passed search term...

Global Sub findAccount()
Dim oResult As Object

oResult = findRecordsWhereSQL("account", "name", "%John%")
End Sub


Figured it out, sort of.

All the examples of using prepared statements in LibreOffice Base macro documentation show the question marks on the right side of the equation (after the = equal sign), for example:

stSql = "UPDATE author " _
& "SET lastname = ?, firstname = ?" _
& "WHERE ID = ?"


Apparently it is not possible to replace column names and table names with ? and insert via function parameter. So you can't replace author, lastname, firstname and ID in the example above with question marks and insert them via .setString(). Kinda lame. However, I came up with a compromise. It's a hybrid between a prepared statement and not-so-prepared. The table and column name are concatenated, and the search term is prepared. I have to put the percentage signs in the passed parameter to make it work.

So my new function looks like this... still in development, but this much works, and the msgbox outputs the matching record names:

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 " & sDatabaseTable & " WHERE " & sWhereColumn & " LIKE ? " 'base SQL
oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement
oSqlStatement.setString(1, sWhereLike) 'insert search term

findRecordsWhereSQL = oSqlStatement.executeQuery()

While findRecordsWhereSQL.next
sRecord = findRecordsWhereSQL.getString(1)
msgbox sRecord, 0, "Record"
Wend
End Function


Then, when I call the function, I have to include the %% wildcards IN the passed search term...

Global Sub findAccount()
Dim oResult As Object

oResult = findRecordsWhereSQL("account", "name", "%John%")
End Sub


Figured it out, sort of.

All the examples of using prepared statements in LibreOffice Base macro documentation show the question marks on the right side of the equation (after the = equal sign), for example:

stSql = "UPDATE author " _
& "SET lastname = ?, firstname = ?" _
& "WHERE ID = ?"


Apparently it is not possible to replace column names and table names with ? and insert via function parameter. So you can't replace author, lastname, firstname and ID in the example above with question marks and insert them via .setString(). Kinda lame. However, I came up with a compromise. It's a hybrid between a prepared statement and not-so-prepared. The table and column name are concatenated, and the search term is prepared. I have to put the percentage signs in sign %% wildcards within the passed parameter to make it work.

So my new function looks like this... still in development, but this much works, and the msgbox outputs the matching record names:

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 " & sDatabaseTable & " WHERE " & sWhereColumn & " LIKE ? " 'base SQL
oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement
oSqlStatement.setString(1, sWhereLike) 'insert search term

findRecordsWhereSQL = oSqlStatement.executeQuery()

While findRecordsWhereSQL.next
sRecord = findRecordsWhereSQL.getString(1)
msgbox sRecord, 0, "Record"
Wend
End Function


Then, when I call the function, I have to include the %% wildcards IN the passed search term...

Global Sub findAccount()
Dim oResult As Object

oResult = findRecordsWhereSQL("account", "name", "%John%")
End Sub


Figured it out, sort of.

All the examples of using prepared statements in LibreOffice Base macro documentation show the question marks on the right side of the equation (after the = equal sign), for example:

stSql = "UPDATE author " _
& "SET lastname = ?, firstname = ?" _
& "WHERE ID = ?"


Apparently it is not possible to replace column names and table names with ? and insert via function parameter. So you can't replace author, lastname, firstname and ID in the example above with question marks and insert them via .setString(). Or if you can do that, I have found no such code examples online. Kinda lame. However, I came up with a compromise. It's a hybrid between a prepared statement and not-so-prepared. The table and column name are concatenated, and the search term is prepared. I have to put the percentage sign %% wildcards within the passed parameter to make it work.

So my new function looks like this... still in development, but this much works, and the msgbox outputs the matching record names:

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 " & sDatabaseTable & " WHERE " & sWhereColumn & " LIKE ? " 'base SQL
oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement
oSqlStatement.setString(1, sWhereLike) 'insert search term

findRecordsWhereSQL = oSqlStatement.executeQuery()

While findRecordsWhereSQL.next
sRecord = findRecordsWhereSQL.getString(1)
msgbox sRecord, 0, "Record"
Wend
End Function


Then, when I call the function, I have to include the %% wildcards IN the passed search term...

Global Sub findAccount()
Dim oResult As Object

oResult = findRecordsWhereSQL("account", "name", "%John%")
End Sub