Ask Your Question

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