One thing I do to make this whole process less confusing is assign the quote character to a variable, let's call it Q, so you get code like this:

Q = |'|

sql_query = "select * from t_names where lastname = " + quote + "Jones" + quote

That simply makes the line easier to read and keep track of whether you've closed all your quotes.

 

---

From: lnotes-l-new@yahoogroups.com [mailto:lnotes-l-new@yahoogroups.com] On Behalf Of David Clark
Sent: Thursday, July 02, 2009 10:45 AM
To: lnotes-l-new@yahoogroups.com
Subject: [lnotes-l] Generating a SQL query string

I’m trying to create a string to be used in a SQL query where field values are used as part of the string.  It seems that these must be enclosed in quotes to cope with empty values, the problem is that I always get a double set so instead of “test” I get “”test””.

Two methods are suggested for this:

1. Use different delimiters such as |    When I use this as the outer delimiter I get the above effect so |Where pen_id = “| + doc.penid(0) + |“|  leads to “Where pen_id = “”test”””  (in the debugger anyway).  If I use as the inner delimiter around the variable it works in so far as it gives “Where pen_id = |test|” but this isn’t valid SQL.

2. The other method I found says at if you want to include a quotation mark ( or pipe) then enter a double one. This is, I suppose, analogous to ‘escaping’ it.  This becomes quite comical as |Where pen_id = “”| + doc.penid(0) + |””|  leads to “Where pend_id = “””test”””  and “Where pen_id = “”” + doc.penid(0) + “”””  isn’t much better.

In the debugger, of course, the values of string variables are shown with quotes around them – it’s like I don’t need the quotes but if miss them out of the string generation I don’t get any.

I don’t have much hair as it is so help gratefully received.