PREPARE and single/double quotes.

I am currently having problems trying to build up an SQL statement in a string and then PREPARE a statement. A simple version of the statement I am trying to prepare is as follows. SELECT address_code FROM address WHERE address_line = "ST.MARY'S ROAD" This I can do. By using the following code LET l_address = "ST.MARY'S ROAD" LET l_sql = "SELECT address_code FROM address ", "WHERE address_line = \\"", l_address CLIPPED, "\\"" and then preparing it. The problem comes when I the address contains a double quote instead of a single quote. For example LET l_address = '"BLUELAKES", THE GREEN' LET l__sql = "SELECT address_code FROM address ", "WHERE address_line = \\"", l_address CLIPPED, "\\"" as this produces a syntax error when preparing it. I could check for whether the address contained double quotes and thus use LET l_sql = "SELECT address_code FROM address ", "WHERE address_line = '", l_address CLIPPED, "'" but this approach will eventually fail when we get an address of "BLUE LAKES", ST.MARY'S ROAD 123456789-123456789-12345678 YUK. Double quotes and single quotes in the same string. How do I quote this? The current solution we have used is place holders. So that the string we build up is LET l_sql = "SELECT address_code FROM address ", "WHERE address_line = ?" and then pass the address line when we open the cursor. We briefly considered the approach of substrings to build up and prepare the following SELECT address_code FROM address WHERE address_line[1,1] = '"' AND address_line[2,11] = "BLUE LAKES" AND address_line[12,12] = '"' AND address_line[13,21] = "ST.MARY" AND address_line[22,22] = "'" AND address_line[23,40] = "S ROAD " This idea was discarded due the complication of building up the SQL and consideration of the performance hit on the engine. The main problem with the place holder solution is that the address record we are actually looking for has six lines of text, any combination of which can be null. This means that we can end up with between one and six place holders in the prepared sql statement. If a statement has two place holders, informix expects that we pass exactly two parameters when opening the cursor. So in order to open the cursor, we need to have six open cursors with varying parameter counts for one parameter to six parameters. In addition have the hassle of passing the correct address line as the parameter, for example With the address Line 1: ST MARY'S ROAD Line 2: <NULL> Line 3: BARNSTABLE Line 4: <NULL> Line 5: <NULL> Line 6: <NULL> we have to build up a string as LET l_sql ="SELECT address_code FROM address ", "WHERE address_line1 = ? ", "AND address_line2 IS NULL ", "AND address_line3 = ? ", "AND address_line4 IS NULL ", "AND address_line5 IS NULL ", "AND address_line6 IS NULL" then store the values of the address lines in the correct variables LET l_param1 = l_address_line1 LET l_param2 = l_address_line3 before PREPARING the SQL PREPARE st_get_address_code FROM l_sql DECLARE c_get_address_code CURSOR FOR st_get_address_code and then opening the "two parameter" cursor OPEN c_get_address_code USING l_param1, l_param2 As you can see, not a pretty solution, but it works. The question I want to know, it there a better way of doing this? Thanks in advance PS: We have to use the DECLARE/OPEN/FETCH/CLOSE method as the EXECUTE INTO method is not available on our version of informix. -- Steve Wright

Use a replaceable parameter for the address and substitute it at OPEN or EXECUTE time with a USING clause. So: LET l_address = "ST.MARY'S ROAD" or LET l_address = '"BLUELAKES", THE GREEN' LET l_sql = "SELECT address_code FROM address ", "WHERE address_line = ?" PREPARE l_stmt FROM l_sql Then either: DECLARE l_curs CURSOR FOR l_stmt OPEN l_curs USING l_address FOREACH l_curs INTO local_address_code ... END FOREACH Or: EXECUTE IMMEDIATE l_stmt INTO local_address_code USING l_address Art S. Kagel Steve Wright wrote: > > I am currently having problems trying to build up an SQL statement in a > string and then PREPARE a statement. A simple version of the statement I > am trying to prepare is as follows. > > SELECT address_code > FROM address > WHERE address_line = "ST.MARY'S ROAD"> > This I can do. By using the following code > > LET l_address = "ST.MARY'S ROAD" > LET l_sql = "SELECT address_code FROM address ", > "WHERE address_line = \\"", l_address CLIPPED, "\\"" > > and then preparing it. > > The problem comes when I the address contains a double quote instead of > a single quote. For example > > LET l_address = '"BLUELAKES", THE GREEN' > LET l__sql = "SELECT address_code FROM address ", > "WHERE address_line = \\"", l_address CLIPPED, "\\"" > > as this produces a syntax error when preparing it. I could check for > whether the address contained double quotes and thus use > > LET l_sql = "SELECT address_code FROM address ", > "WHERE address_line = '", l_address CLIPPED, "'" > > but this approach will eventually fail when we get an address of > > "BLUE LAKES", ST.MARY'S ROAD > 123456789-123456789-12345678 > YUK. Double quotes and single quotes in the same string. How do I quote > this? > > The current solution we have used is place holders. So that the string > we build up is > > LET l_sql = "SELECT address_code FROM address ", > "WHERE address_line = ?" > > and then pass the address line when we open the cursor. We briefly > considered the approach of substrings to build up and prepare the > following > > SELECT address_code FROM address > WHERE address_line[1,1] = '"' > AND address_line[2,11] = "BLUE LAKES" > AND address_line[12,12] = '"' > AND address_line[13,21] = "ST.MARY" > AND address_line[22,22] = "'" > AND address_line[23,40] = "S ROAD "> > This idea was discarded due the complication of building up the SQL and > consideration of the performance hit on the engine. > > The main problem with the place holder solution is that the address > record we are actually looking for has six lines of text, any > combination of which can be null. > > This means that we can end up with between one and six place holders in > the prepared sql statement. If a statement has two place holders, > informix expects that we pass exactly two parameters when opening the > cursor. So in order to open the cursor, we need to have six open cursors > with varying parameter counts for one parameter to six parameters. In > addition have the hassle of passing the correct address line as the > parameter, for example > > With the address > > Line 1: ST MARY'S ROAD > Line 2: <NULL> > Line 3: BARNSTABLE > Line 4: <NULL> > Line 5: <NULL> > Line 6: <NULL> > > we have to build up a string as > > LET l_sql ="SELECT address_code FROM address ", > "WHERE address_line1 = ? ", > "AND address_line2 IS NULL ", > "AND address_line3 = ? ", > "AND address_line4 IS NULL ", > "AND address_line5 IS NULL ", > "AND address_line6 IS NULL" > > then store the values of the address lines in the correct variables > > LET l_param1 = l_address_line1 > LET l_param2 = l_address_line3 > > before PREPARING the SQL > > PREPARE st_get_address_code FROM l_sql > DECLARE c_get_address_code CURSOR FOR st_get_address_code > > and then opening the "two parameter" cursor > > OPEN c_get_address_code USING l_param1, l_param2 > > As you can see, not a pretty solution, but it works. > > The question I want to know, it there a better way of doing this? > > Thanks in advance > > PS: We have to use the DECLARE/OPEN/FETCH/CLOSE method as the EXECUTE > INTO method is not available on our version of informix. > > -- > Steve Wright

One possibility would require elimination of nulls in the address_line columns of the database. During the insert convert null address_lines to blank. Similarly, when retrieving, convert null local variables to blank prior to using the single prepared sql with 6 place-holders. Another possibility could be available if at least 1 address_line has a NOT NULL constraint. You could then use a single SQL with a WHERE clause against all the address_line columns which are NOT NULL, filtering out rows within the FOREACH by testing them against the remaining address_lines. This could be expensive, though, depending on the uniqueness of the non-null address_line columns. HTH Rudy Steve Wright wrote: > ... > This means that we can end up with between one and six place holders in > the prepared sql statement. If a statement has two place holders, > informix expects that we pass exactly two parameters when opening the > cursor. So in order to open the cursor, we need to have six open cursors > with varying parameter counts for one parameter to six parameters. In > addition have the hassle of passing the correct address line as the > parameter, for example > ...

I am sorry I did not read the bottom of your post originally, rush rush all the time. I know my response was non-sequitor. I have a suggestion for the parameterized query you present near the bottom, skip down. Steve Wright wrote: [SNIP] > The main problem with the place holder solution is that the address > record we are actually looking for has six lines of text, any > combination of which can be null. > > This means that we can end up with between one and six place holders in > the prepared sql statement. If a statement has two place holders, > informix expects that we pass exactly two parameters when opening the > cursor. So in order to open the cursor, we need to have six open cursors > with varying parameter counts for one parameter to six parameters. In > addition have the hassle of passing the correct address line as the > parameter, for example > > With the address > > Line 1: ST MARY'S ROAD > Line 2: <NULL> > Line 3: BARNSTABLE > Line 4: <NULL> > Line 5: <NULL> > Line 6: <NULL> > > we have to build up a string as > > LET l_sql ="SELECT address_code FROM address ", > "WHERE address_line1 = ? ", > "AND address_line2 IS NULL ", > "AND address_line3 = ? ", > "AND address_line4 IS NULL ", > "AND address_line5 IS NULL ", > "AND address_line6 IS NULL" > > then store the values of the address lines in the correct variables > > LET l_param1 = l_address_line1 > LET l_param2 = l_address_line3 > > before PREPARING the SQL > > PREPARE st_get_address_code FROM l_sql > DECLARE c_get_address_code CURSOR FOR st_get_address_code > > and then opening the "two parameter" cursor > > OPEN c_get_address_code USING l_param1, l_param2 > > As you can see, not a pretty solution, but it works. > > The question I want to know, it there a better way of doing this? Try this one: LET l_sql ="SELECT address_code ", "FROM address ", "WHERE (address_line1 = ? OR address_line1 IS NULL) ", "AND (address_line2 = ? OR address_line2 IS NULL) ", "AND (address_line3 = ? OR address_line3 IS NULL) ", "AND (address_line4 = ? OR address_line4 IS NULL) ", "AND (address_line5 = ? OR address_line5 IS NULL) ", "AND (address_line6 = ? OR address_line6 IS NULL)" Then for the example query: LET l_param1 = l_address_line1 LET l_param2 = "" LET l_param3 = l_address_line3 LET l_param4 = "" LET l_param5 = "" LET l_param6 = "" Art S. Kagel

In article <38738256.96C7CC55@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> writes >One possibility would require elimination of nulls in the address_line >columns of the database. During the insert convert null address_lines to >blank. Similarly, when retrieving, convert null local variables to blank >prior to using the single prepared sql with 6 place-holders. > This is the ideal long-term solution, however with our systems this is not practical. We have five front end systems that generate address that eventually find their way into this database. There is a considerable amount of effort to change and test all these systems. >Another possibility could be available if at least 1 address_line has a NOT >NULL constraint. You could then use a single SQL with a WHERE clause >against all the address_line columns which are NOT NULL, filtering out rows >within the FOREACH by testing them against the remaining address_lines. >This could be expensive, though, depending on the uniqueness of the >non-null address_line columns. > Again a solution, which due to nature of our data, didn't even reach the drawing board. Unfortunately, the only address lines that I *think* always exists are the one that has "COLLECT" when the customer collects instead of us delivering and the one which holds the name of the principle town/city. Reading all the addresses which have delivered to over a four year period to a particular town/city is going to be expensive. However it is valid solution we have applied in other circumstances where the data is more appropriate. >HTH >Rudy > > >Steve Wright wrote: > >> ... >> This means that we can end up with between one and six place holders in >> the prepared sql statement. If a statement has two place holders, >> informix expects that we pass exactly two parameters when opening the >> cursor. So in order to open the cursor, we need to have six open cursors >> with varying parameter counts for one parameter to six parameters. In >> addition have the hassle of passing the correct address line as the >> parameter, for example >> ... > -- Steve Wright

In article <3873976A.C764C7A0@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >Steve Wright wrote: >[SNIP] > >> The main problem with the place holder solution is that the address >> record we are actually looking for has six lines of text, any >> combination of which can be null. >> >> This means that we can end up with between one and six place holders in >> the prepared sql statement. If a statement has two place holders, >> informix expects that we pass exactly two parameters when opening the >> cursor. So in order to open the cursor, we need to have six open cursors >> with varying parameter counts for one parameter to six parameters. In >> addition have the hassle of passing the correct address line as the >> parameter >> The question I want to know, it there a better way of doing this? > >Try this one: > > LET l_sql ="SELECT address_code ", > "FROM address ", > "WHERE (address_line1 = ? OR address_line1 IS NULL) ", > "AND (address_line2 = ? OR address_line2 IS NULL) ", > "AND (address_line3 = ? OR address_line3 IS NULL) ", > "AND (address_line4 = ? OR address_line4 IS NULL) ", > "AND (address_line5 = ? OR address_line5 IS NULL) ", > "AND (address_line6 = ? OR address_line6 IS NULL)" >Then for the example query: > >LET l_param1 = l_address_line1 >LET l_param2 = "" >LET l_param3 = l_address_line3 >LET l_param4 = "" >LET l_param5 = "" >LET l_param6 = "" > >Art S. Kagel Given that we have an index on (address_line1,address_line6) as one of them has a non-null value 99.99% of the time, what index path will the query use? Will the use of 'OR' on the address_line1 check force a sequential scan? (I can't test this at the moment as I've got no access to a development machine until next week) -- Steve Wright

Steve Wright wrote: > In article <38738256.96C7CC55@americasm01.nt.com>, Rudy Fernandes > <rferdy@americasm01.nt.com> writes > >One possibility would require elimination of nulls in the address_line > >columns of the database. During the insert convert null address_lines to > >blank. Similarly, when retrieving, convert null local variables to blank > >prior to using the single prepared sql with 6 place-holders. > > > > This is the ideal long-term solution, however with our systems this is > not practical. We have five front end systems that generate address that > eventually find their way into this database. There is a considerable > amount of effort to change and test all these systems. > Steve, You could use a database insert trigger to replace nulls with blanks from rows as they get inserted - will impact insert performance, though. Cheers Rudy