插入选择不起作用 [英] INSERT SELECT not working

问题描述

使用Informix 11.7，我试图在select语句中使用jdbc位置参数执行INSERT SELECT查询:

Using Informix 11.7, I'm trying to execute a INSERT SELECT query with jdbc positional parameters in the select statement like this :

INSERT INTO table1(id, code, label) 
SELECT ?, ?, ? FROM table2
WHERE ...

参数设置如下:

 stmt.setString(1, "auniqueid");
 stmt.setString(2, "code");
 stmt.setString(3, "coollabel");

我收到以下错误:

线程"main"中的异常java.sql.SQLException:发生语法错误.

Exception in thread "main" java.sql.SQLException: A syntax error has occurred.

当位置参数为?"时放在其他地方也可以.使用PostgreSQL我没有这个问题.我的查询出了什么问题?我使用Informix JDBC驱动程序v3.70 JC1.

When positional parmeters "?" are placed elsewhere it works fine. I have not this problem using PostgreSQL. What's wrong with my query ? I use the Informix JDBC Driver v3.70 JC1.

感谢您的帮助.

推荐答案

警告:我没有使用Informix的经验，答案基于一般观察

Warning: I have no experience with Informix, answer is based on general observations

指定参数时，数据库将需要知道每个参数的类型.如果参数出现在选择列表中，则数据库无法推断参数的类型.某些数据库可能能够延迟该决定，直到它实际接收到参数为止，但是大多数数据库将需要在解析时知道这一点.这可能是您收到错误的原因.

When specifying parameters the database will need to know the type of each parameter. If a parameter occurs in the select-list, then there is no way for the database to infer the type of the parameter. Some database might be capable of delaying that decision until it actually receives the parameters, but most database will need to know this at parse time. This is probably the reason why you receive the error.

某些数据库-我不知道这是否适用于Informix-允许您强制转换参数.例如:

Some databases - I don't know if this applies to Informix - allow you to cast parameters. So for example:

SELECT CAST(? AS VARCHAR(20)), CAST(? AS VARCHAR(10)), CAST(? AS VARCHAR(5)) FROM ...

在这种情况下，数据库将能够推断参数类型并能够正确解析查询.

In that case the database will be able to infer the parameter types and be able to parse the query correctly.

使用此方法，我确实假设您不尝试使用参数为选择列表指定列名，因为这是不可能的.

With this I do assume you are not trying to specify columnnames for the select-list using parameters, as that is not possible.

这篇关于插入选择不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！