Ask Your Question

Revision history [back]

Prepared Statement Question Mark within WHERE LIKE '%?%'

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

    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:

image description

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 "Tom" as part of their name.

SELECT 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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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

    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:

image description

Anyone know how to get around this problem so I can pass a function parameter to the WHERE LIKE?WHERE LIKE '%?%' ?

If I execute the following statement directly in MySQL, it returns exactly the results expected, accounts with "Tom" as part of their name.

SELECT 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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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

    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:

image description

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 "Tom" "John" as part of their name.

SELECT 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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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

    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:

image description

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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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

    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:

image descriptionimage description

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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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.setLong(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:

image description

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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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.setLong(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:

image description

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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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.setLong(3, 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:

image description

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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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?

Prepared Statement Question Mark within WHERE LIKE '%?%'

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:

image description

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?