字符串文字中的Informix JDBC，MONEY和小数分隔符问题 [英] Problem with Informix JDBC, MONEY and decimal separator in string literals

问题描述

我在使用MONEY数据类型的JDBC应用程序时遇到问题。
当我插入MONEY栏时：

I have problem with JDBC application that uses MONEY data type. When I insert into MONEY column:

insert into _money_test (amt) values ('123.45')

我有例外：

Character to numeric conversion error

使用ODBC驱动程序从本机Windows应用程序运行相同的SQL。
我居住在波兰并且有波兰语语言环境，在我的国家/地区逗号分隔
小数部分，所以我试过：

The same SQL works from native Windows application using ODBC driver. I live in Poland and have Polish locale and in my country comma separates decimal part of number, so I tried:

insert into _money_test (amt) values ('123,45')

它工作。
我在PreparedStatement中检查过我必须使用点分隔符： 123.45 。
我当然可以使用：

And it worked. I checked that in PreparedStatement I must use dot separator: 123.45. And of course I can use:

insert into _money_test (amt) values (123.45)

但有些代码是通用，它从csv文件导入数据，将数字放入字符串文字是安全的。

But some code is "general", it imports data from csv file and it was safe to put number into string literal.

如何强制JDBC在文字中使用DBMONEY（或简单点）？

How to force JDBC to use DBMONEY (or simply dot) in literals?

我的工作站是WinXP。
我在版本3.50 TC5 / JC5中有ODBC和JDBC Informix客户端。
我已将DBMONEY设置为dot：

My workstation is WinXP. I have ODBC and JDBC Informix client in version 3.50 TC5/JC5. I have set DBMONEY to just dot:

DBMONEY=.

编辑：

Jython中的测试代码：

Test code in Jython:

import sys
import traceback
from java.sql import DriverManager
from java.lang import Class

Class.forName("com.informix.jdbc.IfxDriver")

QUERY = "insert into _money_test (amt) values ('123.45')"

def test_money(driver, db_url, usr, passwd):
    try:
        print("\n\n%s\n--------------" % (driver))
        db = DriverManager.getConnection(db_url, usr, passwd)
        c = db.createStatement()
        c.execute("delete from _money_test")
        c.execute(QUERY)
        rs = c.executeQuery("select amt from _money_test")
        while (rs.next()):
            print('[%s]' % (rs.getString(1)))
        rs.close()
        c.close()
        db.close()
    except:
        print("there were errors!")
        s = traceback.format_exc()
        sys.stderr.write("%s\n" % (s))



print(QUERY)
test_money("com.informix.jdbc.IfxDriver", 'jdbc:informix-sqli://169.0.1.225:9088/test:informixserver=ol_225;DB_LOCALE=pl_PL.CP1250;CLIENT_LOCALE=pl_PL.CP1250;charSet=CP1250', 'informix', 'passwd')
test_money("sun.jdbc.odbc.JdbcOdbcDriver", 'jdbc:odbc:test', 'informix', 'passwd')

当我使用点和逗号运行货币字面时的结果：

Results when I run money literal with dot and comma:

C:\db_examples>jython ifx_jdbc_money.py
insert into _money_test (amt) values ('123,45')


com.informix.jdbc.IfxDriver
--------------
[123.45]


sun.jdbc.odbc.JdbcOdbcDriver
--------------
there were errors!
Traceback (most recent call last):
    File "ifx_jdbc_money.py", line 16, in test_money
        c.execute(QUERY)
SQLException: java.sql.SQLException: [Informix][Informix ODBC Driver][Informix]Character to numeric conversion error


C:\db_examples>jython ifx_jdbc_money.py
insert into _money_test (amt) values ('123.45')


com.informix.jdbc.IfxDriver
--------------
there were errors!
Traceback (most recent call last):
    File "ifx_jdbc_money.py", line 16, in test_money
        c.execute(QUERY)
SQLException: java.sql.SQLException: Character to numeric conversion error



sun.jdbc.odbc.JdbcOdbcDriver
--------------
[123.45]

推荐答案

我使用PreparedStatement解决了这个问题。我认为字符到数字转换错误是Informix JDBC驱动程序中的一个错误。

I solved this problem by using PreparedStatement. I think that "Character to numeric conversion error" is a bug in Informix JDBC driver.

在我经常使用的其他数据库中，PostgreSQL，如果我运行查询没有区别通过本机JDBC驱动程序或通过JDBC-ODBC桥。我发现PostgreSQL不接受数字形式 123.45 。 PostgreSQL接受带有点的字符串文字，但此点作为千位分隔符处理。唯一正确接受的值是字符串文字，其中逗号分隔小数部分。

In other database I often use, PostgreSQL, there is no difference if I run query via native JDBC driver or via JDBC-ODBC bridge. I found that PostgreSQL do not accept numeric form 123.45. PostgreSQL accepts string literal with dot, but this dot is handled as a thousand separator. The only correctly accepted value is string literal where comma separates decimal part.

编辑：

可以通过在服务器端设置 DBMONEY =。来解决，然后所有连接（ODBC，JDBC）都可以使用该设置。

It can be solved by setting DBMONEY=. on server side, then all connections (ODBC, JDBC) will work with that setting.

这篇关于字符串文字中的Informix JDBC，MONEY和小数分隔符问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！