使用JDBC和ODBC将TEXT列插入Informix数据库的一致方法 [英] Consistent method of inserting TEXT column to Informix database using JDBC and ODBC

问题描述

当我尝试将一些数据插入Informix TEXT列时遇到问题 通过JDBC.在ODBC中，我可以像这样简单地运行SQL:

I have problem when I try insert some data to Informix TEXT column via JDBC. In ODBC I can simply run SQL like this:

INSERT INTO test_table (text_column) VALUES ('insert')

但是这在JDBC中不起作用，并且出现错误:

but this do not work in JDBC and I got error:

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

我搜索了此类问题，并发现了2003年的消息:

I searched for such problem and found messages from 2003:

我将代码更改为使用PreparedStatement.现在，它可以与JDBC一起使用， 但是在ODBC中，当我尝试使用PreparedStatement时出现错误:

I changed my code to use PreparedStatement. Now it works with JDBC, but in ODBC when I try using PreparedStatement I got error:

Error: [Informix][Informix ODBC Driver][Informix]
Illegal attempt to convert Text/Byte blob type.
[SQLCode: -608], [SQLState: S1000]

测试表是通过以下方式创建的:

Test table was created with:

CREATE TABLE _text_test (id serial PRIMARY KEY, txt TEXT)

Jython代码可以测试两个驱动程序:

Jython code to test both drivers:

# for Jython 2.5 invoke with --verify
# beacuse of bug: http://bugs.jython.org/issue1127

import traceback
import sys
from com.ziclix.python.sql import zxJDBC

def test_text(driver, db_url, usr, passwd):
    arr = db_url.split(':', 2)
    dbname = arr[1]
    if dbname == 'odbc':
        dbname = db_url
    print "\n\n%s\n--------------" % (dbname)
    try:
        connection = zxJDBC.connect(db_url, usr, passwd, driver)
    except:
        ex = sys.exc_info()
        s = 'Exception: %s: %s\n%s' % (ex[0], ex[1], db_url)
        print s
        return
    Errors = []
    try:
        cursor = connection.cursor()
        cursor.execute("DELETE FROM _text_test")
        try:
            cursor.execute("INSERT INTO _text_test (txt) VALUES (?)", ['prepared', ])
            print "prepared insert ok"
        except:
            ex = sys.exc_info()
            s = 'Exception in prepared insert: %s: %s\n%s\n' % (ex[0], ex[1], traceback.format_exc())
            Errors.append(s)
        try:
            cursor.execute("INSERT INTO _text_test (txt) VALUES ('normal')")
            print "insert ok"
        except:
            ex = sys.exc_info()
            s = 'Exception in insert: %s: %s\n%s' % (ex[0], ex[1], traceback.format_exc())
            Errors.append(s)
        cursor.execute("SELECT id, txt FROM _text_test")
        print "\nData:"
        for row in cursor.fetchall():
            print '[%s]\t[%s]' % (row[0], row[1])
        if Errors:
            print "\nErrors:"
            print "\n".join(Errors)
    finally:
        cursor.close()
        connection.commit()
        connection.close()


#test_varchar(driver, db_url, usr, passwd)
test_text("sun.jdbc.odbc.JdbcOdbcDriver", 'jdbc:odbc:test_db', 'usr', 'passwd')
test_text("com.informix.jdbc.IfxDriver", 'jdbc:informix-sqli://169.0.1.225:9088/test_db:informixserver=ol_225;DB_LOCALE=pl_PL.CP1250;CLIENT_LOCALE=pl_PL.CP1250;charSet=CP1250', 'usr', 'passwd')

在JDBC或ODBC中是否有任何设置具有一个版本的代码 对于两个驱动程序?

Is there any setting in JDBC or ODBC to have one version of code for both drivers?

版本信息:

• 服务器:IBM Informix Dynamic Server版本11.50.TC2DE
• 客户:
  • ODBC驱动程序3.50.TC3DE
  • 用于IBM Informix Dynamic Server 3.50.JC3DE的IBM Informix JDBC驱动程序
  • Server: IBM Informix Dynamic Server Version 11.50.TC2DE
  • Client:
    • ODBC driver 3.50.TC3DE
    • IBM Informix JDBC Driver for IBM Informix Dynamic Server 3.50.JC3DE

    推荐答案

    首先，您确定要使用Informix TEXT类型吗?该类型使用起来很麻烦，部分原因是您面临的问题.它在任何SQL标准中都早于大型对象的内容(尽管在SQL-2003中TEXT仍然不存在TEXT，尽管CLOB和BLOB大约是等效的结构).自那以后，BYTE和TEXT Blob的功能就没有发生过变化-哦，可以说1996年，尽管我怀疑有一种选择较早日期的情况，例如1991年.

    First off, are you really sure you want to use an Informix TEXT type? The type is a nuisance to use, in part because of the problems you are facing. It pre-dates anything in any SQL standard with respect to large objects (TEXT still isn't in SQL-2003 - though approximately equivalent structures, CLOB and BLOB, are). And the functionality of BYTE and TEXT blobs has not been changed since - oh, let's say 1996, though I suspect there's a case for choosing an earlier date, like 1991.

    特别是，您打算在TEXT列中存储多少数据?您的示例显示了字符串插入"；也就是说，我认为比实际使用的要小得多.您应该知道，BYTE或TEXT列在表中使用56字节的描述符加上单独的页面(或页面集)来存储实际数据.因此，对于像这样的微小字符串，这会浪费空间和带宽(因为BYTE或TEXT对象的数据将在客户端和服务器之间与行的其余部分分开传送).如果您的大小不会超过32 KB，那么您应该考虑使用LVARCHAR而不是TEXT.如果将使用大于此大小的数据，则BYTE或TEXT或BLOB或CLOB是明智的选择，但您应该考虑配置Blob空间(对于BYTE或TEXT)或智能Blob空间(对于BLOB或CLOB).您可以并且正在使用TEXT IN TABLE，而不是在Blob空间中使用；请注意，这样做会影响您的逻辑日志，而使用Blob空间不会对其产生多大的影响.

    In particular, how much data are you planning to store in the TEXT columns? Your example shows the string 'insert'; that is, I presume, much much smaller than you would really use. You should be aware that a BYTE or TEXT columns uses a 56-byte descriptor in the table plus a separate page (or set of pages) to store the actual data. So, for tiny strings like that, it is a waste of space and bandwidth (because the data for the BYTE or TEXT objects will be shipped between client and server separately from the rest of the row). If your size won't get above about 32 KB, then you should look at using LVARCHAR instead of TEXT. If you will be using data sizes above that, then BYTE or TEXT or BLOB or CLOB are sensible alternatives, but you should look at configuring either blob spaces (for BYTE or TEXT) or smart blob spaces (for BLOB or CLOB). You can, and are, using TEXT IN TABLE, rather than in a blob space; be aware that doing so impacts your logical logs whereas using a blob space does not impact them anything like as much.

    我十年来一直致力于开发的功能之一就是能够将SQL语句中的字符串文字作为TEXT文字(或BYTE文字)传递.部分原因是因为像您这样的人的经验.我尚未成功地将其优先级放在需要进行的其他更改之前.当然，您需要注意，SQL语句的最大大小为64 KB文本，因此，如果不小心，可能会创建太大的SQL语句. SQL中的占位符(问号)通常可以防止出现此问题-增大SQL语句的大小是我一直在努力争取的另一个功能请求，但是热情不高.

    One of the features I've been campaigning for a decade or so is the ability to pass string literals in SQL statements as TEXT literals (or BYTE literals). That is in part because of the experience of people like you. I haven't yet been successful in getting it prioritized ahead of other changes that need to be made. Of course, you need to be aware that the maximum size of an SQL statement is 64 KB text, so you could create too big an SQL statement if you aren't careful; placeholders (question marks) in the SQL normally prevent that being a problem - and increasing the size of an SQL statement is another feature request which I've been campaigning for, but a little less ardently.

    好的，假设您有充分的理由使用TEXT ...下一步.我不清楚Java(JDBC驱动程序)在幕后做什么-除了太多之外-可以肯定的是，它注意到需要TEXT'locator'结构并且正在以正确的方式传递参数格式.看来ODBC驱动程序不会让您沉迷于类似的恶作剧.

    OK, assuming that you have sound reasons for using TEXT...what next. I'm not clear what Java (the JDBC driver) is doing behind the scenes - apart from too much - but it is a fair bet that it is noticing that a TEXT 'locator' structure is needed and is shipping the parameter in the correct format. It appears that the ODBC driver is not indulging you with similar shenanigans.

    在我通常工作的ESQL/C中，代码对BYTE和TEXT的处理必须不同于其他所有处理(并且BLOB和CLOB的处理必须再次进行不同的处理).但是您可以创建并填充定位器结构(locator.h中的loc_t或ifx_loc_t-可能不在ODBC目录中；默认情况下位于$ INFORMIXDIR/incl/esql中)，并将其作为SQL语句中相关占位符的宿主变量.原则上，可能有一个并行方法可用于ODBC.不过，您可能必须查看Informix ODBC驱动程序手册才能找到它.

    In ESQL/C, where I normally work, then the code has to deal with BYTE and TEXT differently from everything else (and BLOB and CLOB have to be dealt with differently again). But you can create and populate a locator structure (loc_t or ifx_loc_t from locator.h - which may not be in the ODBC directory; it is in $INFORMIXDIR/incl/esql by default) and pass that to the ESQL/C code as the host variable for the relevant placeholder in the SQL statement. In principle, there is probably a parallel method available for ODBC. You may have to look at the Informix ODBC driver manual to find it, though.

    这篇关于使用JDBC和ODBC将TEXT列插入Informix数据库的一致方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！