Consistent method of inserting TEXT column to Informix database using JDBC and ODBC

Question

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')

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

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

I searched for such problem and found messages from 2003:

http://groups.google.com/group/comp.databases.informix/browse_thread/thread/4dab38472e521269?ie=UTF-8&oe=utf-8&q=Informix+jdbc+%22A+blob+data+type+must+be+supplied+within+this%22

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 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')

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

Version info:

• 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

Solution

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.

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.

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.

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.

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.

Comments

[Michał Niklas]

Thanks for your answer. TEXT column is used because project started when there was no LVARCHAR column on both server side (IDS7.2) and client side (SQLLinks from Delphi4). I think I will convert TEXT columns to LVARCHAR when client side upgrade to newer Delphi with dbExpress drivers.

[Gabz]

Couldn't him save the desired piece of strings into an external table and then load it as BLOB/Text?