Informix 撇号 SQL 错误
运行下面的查询时，我收到[Informix][Informix ODBC Driver][Informix]发生语法错误。当我删除双撇号时，查询成功执行。我通过谷歌搜索找到的所有内容都告诉我我正在做正确的事情。七年前我使用 Informix 时没有遇到此类问题，所以我想我忘记了一些重要的事情！
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
)
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
)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您确定正在使用的 ODBC 版本、IDS (IBM Informix Dynamic Server) 版本以及它们运行的平台，将会很有帮助。
当我将问题中的代码复制并粘贴到没有表的数据库中的 SQLCMD（相当于 DB-Access）时，出现错误：
这表明 SQL 在语法上是正确的。
那么，为什么您会看到错误？
我对有罪方的第一个怀疑是末尾的长（400+）字符串。曾经（不久前，AFAICR）字符串文字的长度上限为 255。如果您使用的是足够旧版本的 ODBC 驱动程序（或 IDS），这可能是一个因素。
我的第二个怀疑是偶尔出现的双引号日期字符串。 Informix 对于在字符串周围使用单引号还是双引号通常不太严格；这可能会有所帮助。然而，有一种方法可以使它像 SQL 标准一样迂腐，要求在字符串周围使用单引号，并且仅对“分隔标识符”使用双引号。如果设置了 DELIMIDENT 环境变量（可能通过 Windows 上的 SETNET32），则会调用严格模式，当我在 SQLCMD 中执行此操作时，我得到：
第三个怀疑是长列是 BYTE 或 TEXT（或可能是 BLOB）或 CLOB) 类型，并且没有从字符串文字到该类型的转换。然而，据我所知，ODBC 驱动程序会跳过障碍来处理这个问题，并且错误会有所不同，可能是这样的：
所以，目前，我认为 DELIMIDENT 非常值得追逐 - 它可能很容易修复（通过确保日期用单引号括起来或取消设置 DELIPIDENT)。如果失败，请尝试使用较短的字符串，看看是否有效。
但您的基本理解是正确的 - 您正确使用了双引号。
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:
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:
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:
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.