Ask Your Question
1

SQL single quotes being removed

asked 2016-12-05 07:59:42 +0200

owlbrudder gravatar image

updated 2016-12-05 08:17:44 +0200

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:

Dialogs showing single quotes are being stripped

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:

SQL string with two single quotes

What am I doing wrong?

edit retag flag offensive close merge delete

Comments

1

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.

rautamiekka gravatar imagerautamiekka ( 2016-12-05 12:45:53 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2016-12-05 17:43:23 +0200

Ratslinger gravatar image

updated 2016-12-06 15:04:21 +0200

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
edit flag offensive delete link more

Comments

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?

owlbrudder gravatar imageowlbrudder ( 2016-12-06 10:28:39 +0200 )edit

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 gravatar imageRatslinger ( 2016-12-06 15:00:02 +0200 )edit
2

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.

Ratslinger gravatar imageRatslinger ( 2016-12-06 16:29:11 +0200 )edit

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

owlbrudder gravatar imageowlbrudder ( 2016-12-07 04:35:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-05 07:59:42 +0200

Seen: 236 times

Last updated: Dec 06 '16