StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POConsistent method of inserting TEXT column to Informix database using JDBC and ODBC
text
Body
copied!<p>I have problem when I try insert some data to Informix TEXT column via JDBC. In ODBC I can simply run SQL like this:</p> <pre><code>INSERT INTO test_table (text_column) VALUES ('insert') </code></pre> <p>but this do not work in JDBC and I got error:</p> <pre><code>617: A blob data type must be supplied within this context. </code></pre> <p>I searched for such problem and found messages from 2003:</p> <p><a href="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" rel="noreferrer">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</a></p> <p>I changed my code to use PreparedStatement. Now it works with JDBC, but in ODBC when I try using PreparedStatement I got error:</p> <pre><code>Error: [Informix][Informix ODBC Driver][Informix] Illegal attempt to convert Text/Byte blob type. [SQLCode: -608], [SQLState: S1000] </code></pre> <p>Test table was created with:</p> <pre><code>CREATE TABLE _text_test (id serial PRIMARY KEY, txt TEXT) </code></pre> <p>Jython code to test both drivers:</p> <pre><code># 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') </code></pre> <p>Is there any setting in JDBC or ODBC to have one version of code for both drivers?</p> <p>Version info:</p> <ul> <li>Server: IBM Informix Dynamic Server Version 11.50.TC2DE</li> <li>Client: <ul> <li>ODBC driver 3.50.TC3DE</li> <li>IBM Informix JDBC Driver for IBM Informix Dynamic Server 3.50.JC3DE</li> </ul></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload