open curser wth problem

Hey all. I am using IBM Informix Dynamic Server Version 11.50.FC9W2X2 but we are in the process of migrating to IBM Informix Dynamic Server Version 12.10.FC3 so the solution must work for both. I have a spl routine that builds a dynamic sql to run a name search. It works unless there is an apostrophe in the name like C'HAIR. I've tracked it down to the statements: "AND pn.last_name like ?" OPEN wn_search_cur using p_last_name; I've tried semi-hard coding the last name: 'AND pn.last_name like "'||p_last_name||'"' and this seems to work fine but does not meet my business needs. Is there a way to tell Informix that the ' char is not a string delimiter? Thanks

That's very strange. The single quote should be ignored inside of a host variable that's filling in a replaceable parameter (ie ?). Questions: 1. I assume you are using LIKE here because there is a possibility that the host variable p_last_name can contain wildcards. Yes? 2. Is p_last_name coming into the procedure as an argument? 3. Is the OPEN getting an error or just not returning what you think it should? 4. Have you traced the procedure to see exactly what's happening under the hood? Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 13, 2014 at 10:18 AM, BEVIS KENNEDY <bkennedy@utah.gov> wrote: > Hey all. I am using IBM Informix Dynamic Server Version 11.50.FC9W2X2 but > we > are in the process of migrating to IBM Informix Dynamic Server Version > 12.10.FC3 so the solution must work for both. > > I have a spl routine that builds a dynamic sql to run a name search. It > works > unless there is an apostrophe in the name like C'HAIR. I've tracked it > down to > the statements: > > "AND pn.last_name like ?" > > OPEN wn_search_cur using p_last_name; > > I've tried semi-hard coding the last name: > 'AND pn.last_name like "'||p_last_name||'"' > and this seems to work fine but does not meet my business needs. Is there a > way to tell Informix that the ' char is not a string delimiter? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d186ab936ae04f9494ac6