Informix apostrophe SQL error

Question

I get [Informix][Informix ODBC Driver][Informix]A syntax error has occurred when running the query below. When I remove the double apostrophes the query executes successfully. Everything I can find by Googling tells me I'm doing the right thing. Seven years ago I used Informix without this sort of problem so I think I've forgotten something important!

insert into ct_repairs (ct_job_no,inh_job_no,reference,tab
,rec_date,rec_time,priority,start_dte,start_tme,app_date
,app_time,card_date,card_time,est_date_comp,est_time_comp
,act_date_comp,act_time_comp,exp_code,ct_notes,ct_status
) values (
2090
,335706
,'23026002003'
,'P'
,NULL
,''
,1
,"22/02/2010"
,'10:47'
,NULL
,''
,"22/02/2010"
,'11:14'
,NULL
,''
,NULL
,''
,'DTD'
,'**PLS NOTE PLANNED WRKS GOING ON ON ASCOT RD,IE ROOFS,RENDERERING,AND HIGH LEVEL CLOSE BOARD FENCES:SPOKE TO THE LADY AT NO 2 SHE DOESN''T NO ANYTHING ABOUT FENCE ISSUES,CALLED AT NO 4@6 ASCOT NO ACCESS TO EITHER PROPERTIES**:YOU YOU PLS SEND A EMAIL TO TREVOR ON PLANNED ASKING IF NO 2 ASCOT RD IS DOWN FOR A NEW CLOSED BOARDED FENCE,OR IS THAT THE PROBLEM NO 4 BEING PRIVATE THAT THEY  HAVEN''T PUT ONE UP**'
,0
)

Answer 1

It would be helpful if you identified the version of ODBC you are using, and the version of IDS (IBM Informix Dynamic Server), and the platform where they are running.

When I copy'n'paste the code from the question into SQLCMD (equivalent to DB-Access) in a database without the table, I get the error:

SQL -206: The specified table (ct_repairs) is not in the database.

This indicates that the SQL is syntactically correct.

So, why are you seeing an error?

My first suspect for the guilty party is the long (400+) character string at the end. At one time (a while ago now, AFAICR) there was an upper bound of 255 on the length of a character string literal. If you are using an old enough version of the ODBC driver (or IDS), this could be a factor.

My second suspect is the occasional double-quoted date string. Informix is generally lax about whether you use single or double quotes around strings; this can be helpful. However, there is a way to make it pedantic like the SQL standard, requiring single quotes around strings and using double quotes only for 'delimited identifiers'. If the DELIMIDENT environment variable is set (perhaps via SETNET32 on Windows), the strict mode would be invoked, and when I do that in SQLCMD, I get:

SQL -201: A syntax error has occurred.

The third suspect is that the long column is a BYTE or TEXT (or possibly BLOB or CLOB) type, and there isn't a conversion from a string literal to that type. However, AFAIK, the ODBC driver jumps through hoops to deal with that problem, and the error would be different, probably something like:

SQL -617: A blob data type must be supplied within this context.

So, at the moment, I think the DELIMIDENT is well worth chasing - it is probably easily fixed (either by ensuring that dates are enclosed in single quotes or by unsetting DELIMIDENT). Failing that, try a shorter string and see whether that works.

But your basic understanding is correct - you are using doubled-up single quotes correctly.

Answer 2

Sorry Jonathan, I don't appear to be able to be able to log in and accept your answer.

It was really helpful, though.

I found that I can have apostrophes in strings <= 255 chars and I can have strings > 255 chars without apostrophes but I can't have apostrophes in strings > 255 chars!

I've worked around it and am happy.