SQL single quotes being removed

[owlbrudder]

I am building an SQL string to be executed. It contains the supplier name enclosed in single quote, as the supplier name is often multiple words.

When I use Print to display my SQL, it shows the single quotes around the supplier name, but when I execute it I receive an error message containing the SQL string where the single quotes have disappeared.

The two dialogs in the following image show the problem:

The following dialog shows the result when I use two single quotes, which I tried on the principle that one pair might be stripped, leaving the remaining pair to do their work. Note that neither of the single quotes has been removed:

What am I doing wrong?

[rautamiekka]

First, questions: your operating system release ? LO version ? Are you using the Base-integrated database (called HSQLDB) ? What’s the full query you’re using ? Edit your question to include these.

[Ratslinger]

Just tested a similar string using PostgreSQL and had no problem so it appears it’s the construction of your SELECT string. It should be:

sSQL="Select ""strSupplier"", ""serItemId"" FROM ""tblStockItems"" WHERE ""strSupplier"" = 'Penn State' ORDER BY ""strSupplierPartNumber"""

Since you’re creating a string and field and table names need to be quoted when using lowercase or spaces, you need extra set around each. If you had used all uppercase for these, you wouldn’t need any quotes around them making things easier.

Then run your query using “sSQL” (or whatever variable you are using).

Edit: Sample code used:

Sub MainSQL
	oForm1 = ThisComponent.Drawpage.Forms.getByName("Form") 'Get Form
  oStatement = oForm1.ActiveConnection.createStatement() 'Create an SQL statement object
 	sSQL = "Select ""type"" FROM ""playground"" WHERE ""color"" = 'yellow blue'"
 	result = oStatement.executeQuery(sSQL)
 	result.next
 	myAnswer = result.getString(1)
 	MsgBox myAnswer
 	sSQL = "Select ""type"" FROM ""playground"" WHERE ""color"" = 'blue Yellow'"
 	result = oStatement.executeQuery(sSQL)
 	result.next
 	myAnswer = result.getString(1)
 	MsgBox myAnswer
End Sub

[owlbrudder]

Thanks for replying Ratslinger. The problem I am having is that I am inserting single quotes around the supplier name - in this case ‘Penn State’ - but something is stripping them out, leaving the bare words Penn State without the single quotes. I agree with your version of the SQL string and I am trying to get it accepted. Any ideas why or where the quotes are being stripped? It would appear to be in Base, not in PostgreSQL, but it might be in the odbc driver?

[Ratslinger]

Conceptually, I did the same thing just used a different table and different value - ‘blue Yellow’. I had no problems. I’ll post my code in my answer above. If your SQL statement is correct, then something else is wrong. If you can’t figure it out from my example, need to see your code.

[Ratslinger]

OK, I almost overlooked something in your comment. It appears you are using ODBC whereas I connect using JDBC so this could be a possibility. I have experienced quite a few problems using ODBC so have been sticking with JDBC except on occasion for a specific test.

[owlbrudder]

Thanks Ratslinger - JDBC seems to have solved that problem for now.