How to include a complex string in an SQL query statement

[PhilipK]

Working with LO Base 6.4 on a Mac, communicating with a database in SQLite3 through ODBC.

In one of my tables I have a long memo field, which may include text between both single and double quotation marks. In order to pass this through a .csv file and a spreadsheet to get it into this database, some of the single quotes had to be changed to backticks (in order not to be misunderstood by the spreadsheet). I now want to change these back. I have devised a way of scrolling through the table, reading the field as a string and revising it - but have not succeeded in updating the record in the table. I have constructed an UPDATE SQL statement, but because there are sometimes quotation marks within the string, the execution of the SQL statement gets confused as to where the string begins and ends. I tried enclosing the variable in square brackets instead of double quotes - sSQL.executeUpdate("UPDATE ""myTable"" SET ""ThisField"" = [" & NewString & "] WHERE ... ;"), but the execution interpreted that as a column-name (not found!); it didn’t like curly brackets {} either.

How can I get round this difficulty?

[Ratslinger]

Have withdrawn my answer. Have re-read your question and it appears I am simply guessing what you are talking about. I learned that ' is an apostrophe and " is a quote mark. Now it is a guess what people are actually referring to.

If ' is a single quote and ``is a back-tick, then what is an apostrophe? And if "` is a double quote then you need two double quotes on each end of a mixed case HSQLDB table or field name in a macro SQL statement.

[Ratslinger]

Hello,

New answer. Don’t know what you have. Don’t know how you intend to decipher what you have.

Here is what worked for me.

There really is no change for SQL statement in a macro. This works:

sSQL.executeUpdate("UPDATE ""myTable"" SET ""ThisField"" = '" & NewString & "' WHERE ... ;")

Apostrophes surrounding the variable.

Both quotes and apostrophes needed two to show one.

With this code:

ResultA = sSQL.executeQuery("Select * FROM ""Table1""")
ResultA.next
QString = ResultA.getString(2)
QString = Replace(QString, "`","''")
QString = Replace(QString, "?","""")
sSQL.executeUpdate("INSERT INTO ""Table1"" VALUES ( '12', '" & QString & "')")

This input line:

want apostrophe here ` and single quote ? and another apostrophe `

became:

want apostrophe here ' and single quote " and another apostrophe '

Again, will repeat - don’t know what you have but should be able to make combination of some type to get desired results.

[PhilipK]

Thank you. Difficult to describe, since both natural and formal languages cause confusion here. Yes, apostrophe is the same character as single quote, but what I have called backtick (chr 1311, QUOTELEFT) I could not display on this page since it is interpreted as a special character introducing code.

However, I have reached a successful conclusion. The SQL update statement used single quotes/apostrophes to enclose the variable (as in your final line), but before that it was necessary to adjust the variable string in two stages:

ChString = Replace(ChString,"`","'")	'Replace quoteleft by single quote
ChString = Replace(ChString,"'","''")	'Double up all single quotes

This had to be done this way as some memos already contained single quotes rather than backticks. When this was done, the strings were interpreted correctly and both single quotes and backticks came out as single quotes in the scanned records.

[Ratslinger]

It is no wonder things get so goofed up - giving in to made up terminology.