Re: PREPARE and single/double quotes.

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"<SNIP> > 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. Have you looked at using the CONSTRUCT statement? > 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, <SNIP> You could use a fixed number of place holders, but use MATCHES instead of '='. All you have to do then is replace any NULL parameters with '*' before OPENing the CURSOR. This also makes optimisation tuning easier as well. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+