奇怪的 DB2 数据库问题：Websphere 连接池
我正在从 Websphere 容器中运行的基于 java 的 Web 应用程序运行查询。 然而，这个查询非常简单，失败并出现一个奇怪的错误，如下所示：
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R com.ibm.db2.jcc.b.zd: Invalid data conversion:Requested conversion would result in a loss of precision of 40000
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.q.a(q.java:137)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.q.a(q.java:1189)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.a(ad.java:1217)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.kb(ad.java:2977)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.d(ad.java:1970)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.d(ad.java:2342)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.U(ad.java:489)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.executeQuery(ad.java:472)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:559)
该查询非常简单：它就像
select field1, field2 from <xyz table> where <xyz_pk> = ?
主键是一个 INTEGER(4) 并且具有值高达 99999999 的数据一样简单。但是， 当我在从 websphere 连接池获取的连接上运行此查询时，它在我的 Web 应用程序中运行，对于 pk 值 > ，它开始失败 35k+。 在jdbc绑定代码中，我尝试执行preparedStatement.setInt()和preparedStatement.setFloat()。 但似乎没有任何作用！ 它仅适用于低于 35k+ 的任何内容，而对于高于该值的所有内容均无效。
Java 的 int 大小远大于 35k+，那么为什么此查询会因此错误而失败呢？ 这种情况仅发生在我的应用程序中，当我使用我选择的数据库客户端尝试相同的查询时，pkey 的所有值都会获得正确的结果！
以前有人遇到过这个问题吗？ 如果是的话，你是如何解决这个问题的？
I am running a query from my java based web app running in a Websphere container.
This query however, being pretty simple, fails with a weird erorr as follows:
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R com.ibm.db2.jcc.b.zd: Invalid data conversion:Requested conversion would result in a loss of precision of 40000
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.q.a(q.java:137)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.q.a(q.java:1189)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.a(ad.java:1217)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.kb(ad.java:2977)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.d(ad.java:1970)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.d(ad.java:2342)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.U(ad.java:489)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.db2.jcc.b.ad.executeQuery(ad.java:472)
[5/15/09 16:50:33:828 IST] 0000001e SystemErr R at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:559)
The query is pretty simple : it is as simple as
select field1, field2 from <xyz table> where <xyz_pk> = ?
The primary key is a INTEGER(4) and has data that has values up to 99999999. But however,
when I run this query is run in my web app on a connection obtained from websphere connection pool, it starts failing for pk values > 35k+. In the jdbc binding code, I tried doing a preparedStatement.setInt() and preparedStatement.setFloat(). But nothing seems to work!! It just works for anything below 35k+ and fails for everything above that.
Java's int size is much bigger than 35k+, so why would this query fail with this error? This happens just from my application, when I try the same query with a database client of my choice, proper results are being obtained for all values of the pkey!
Did anyone faced this issue before? If yes, how did you get around it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我的错..问题出在约束力上。 我的查询有一个整数和小 int 字段，我将small 绑定到int，将 int 绑定到smallint，因此出现了问题。
My bad.. the issue was in binding. My query had an integer and small int field and I was binding small to int and int to smallint, hence the problem.
整数没有精度。 变量绑定的列链中的某处必须有浮点数或实数。 在这种情况下，无论您是否以 int、Int、long 甚至字节开头都没有关系；都可以。 整体和真实之间的转换会触发警告。 查看从该列到最后一个变量发生的所有绑定。 您准备好的语句是否重新定义了任何字段类型？
Integers don't have precision. There must be a float or real somewhere in the chain of column to variable binding. In that case it doesn't matter whether you start with an int, Int, long, or even a byte; the conversion between whole and real would trigger the warning. Look at all the the bindings that happen from the column to the last variable. Does your prepared statement redefine any of your field types?
我认为您能做的最好的事情就是在 WAS 中为 JDBC 资源适配器打开跟踪日志，然后查看实际向数据库发出的 sql。
抱歉不能提供更多帮助。
卡尔
I think the best thing you can do is to turn on the trace logs in WAS for the JDBC resource adapter and then look at the sql that is actually being issues to the DB.
Sorry cannot be much more help.
Karl
您能否发布导致失败的代码？
我记得 websphere 上有类似的东西，并且必须更改数据库字段的精度。 这与数据库和java空间的转换有关，因为在java方面，数据类型远远大于数据库上的数据类型。
我们更改了数据库的数据类型，情况得到了改善。
Could you maybe post the code that causes the failure?
I remember having something like this on websphere and had to change the precision of the database fields. It had to do with the conversion the database and the java space in that on the java side the data type was far bigger than the datatype on the database.
We changed the data type on the database and things improved.
我记得不久前就遇到过这个问题。 我想我是通过直接在SQL语句中转换值来解决这个问题的。 像这样：
select DECIMAL(field1) AS field1, field2 from ...
I remember having this problem a while ago. I think I solved it by converting the value directly in the SQL statement. Something like this:
select DECIMAL(field1) AS field1, field2 from ...