Sorry for preaching, but honestly if you work with strings prepareStatement is always needed. The crucial point with a statement like
Code: Select all
sSQLString = "UPDATE ""Data"" SET ""Description""='" + oDescriptionCtrlNew.Text + "' WHERE ""Description""='" + oDescriptionCtrlOld.Text + "'"
is that the content of the Textfield can have any possible character and sometimes this is a surname like O'Henry. The apostrophe will mess up your carefully made up qouting because the SQL parser will see something like:
WHERE "Description"='O'Henry'
prepareStatement instead ensures that no matter what you have in your text field the SQL parser will always see
WHERE "Description"=?, which is always correct. Later on you tell the SQL engine explicitly that you want to bind a string to the question mark with the
setString method. And because you explicitly saying this with the method name you don't need to mess with single quotes for sql string delimiters.
In short most of the times it is the missing patience of the coding person and not the fault of prepareStatement. Please don't read any offence into this last sentence, I don't refer to this issue, rather to many other cases where I have seen folks giving up to early on
prepareStatement because they think it is too complicated. More likely it expresses my frustration about PHP programmers that open the door for SQL injection because they ignore
prepareStatement.
I am pretty sure that the following code should work as well (and in a more robust way).
Code: Select all
oStmt = oCon.prepareStatement("UPDATE ""Data"" SET ""Description""=? WHERE ""Description""=?")
'Print sSQLString
oStmt.setString(1, oDescriptionCtrlNew.Text)
oStmt.setString(2, oDescriptionCtrlOld.Text)
iNumUpdated = oStmt.executeUpdate()
See also
PreparedStatement UNO API and
XParameters for setString() and friends.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.