How to get trailing spaces from varchar column in Informix using ODBC

Question

I cannot get trailing spaces from varchar column in Informix database.

I created test table, filled it with field with some trailing spaces, but they are not returned by SELECT while it seems they are stored in db.

CREATE TABLE tmptable (txt varchar(240));
INSERT INTO tmptable (txt) VALUES ('123   ');
SELECT txt, txt || '***', LENGTH(txt) FROM tmptable;

And I got fields:

1: '123' : no trailing spaces!!!
2: '123   ***' : it seems that spaces are stored!!!
3: 3 : LENGTH() do not count trailing spaces!!!

Other databases I tested: Oracle and PostgreSQL return varchar fields with trailing spaces. I tried RPAD() but with no success. Is there any way to get trailing spaces?

Server: IBM Informix Dynamic Server Version 11.50.TC2DE

Client: tested with both ISA (no spaces in HTML page source) and ODBC driver 3.50.TC3DE

EDIT Simple Python test program (tested with ActivePytnon 2.6 on Windows, you must change connection string in the last lines)

import odbc

def test_varchar(db_alias, dbname):
    print
    print
    arr = db_alias.split('/')
    print '%s    %s' % (arr[0], dbname)
    print '--------------'
    connection = odbc.odbc(db_alias)
    try:
     cursor = connection.cursor()
     cursor.execute("DELETE FROM tmptable;")
     cursor.execute("INSERT INTO tmptable (txt) VALUES ('   %s   ')" %  (dbname))
     #cursor.commit()
     cursor.execute("SELECT txt, txt || '***', LENGTH(txt) FROM tmptable;")
     for row in cursor.fetchall():
      print '[%s]\t[%s]\t[%s]' % (row[0], row[1], row[2])
    finally:
     connection.close()

#test_varchar('database/user/passwd',   'DBproducer')
test_varchar('oracledb/usr/passwd',     'Oracle    ')
test_varchar('informixdb/usr/passwd',   'Informix  ')
test_varchar('postgresqldb/usr/passwd', 'PostgreSQL')

And results:

c:\tools\pyscripts\scripts\db_examples>test_odbc.py
oracledb    Oracle
--------------
[   Oracle       ]      [   Oracle       ***]   [16]


informixdb    Informix
--------------
[   Informix]   [   Informix     ***]   [11]


postgresqldb    PostgreSQL
--------------
[   PostgreSQL   ]      [   PostgreSQL   ***]   [16]

Similar program in Jython using JDBC:

• works (do not trim trailing spaces) with native JDBC driver
• doesn't work (trim trailing spacec) with JDBC-ODBC bridge

Source:

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

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

def test_varchar(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
    cursor = connection.cursor()
    cursor.execute("SELECT txt, txt || '***', LENGTH(txt) FROM tmptable")
    for row in cursor.fetchall():
     print '[%s]\t[%s]\t[%s]' % (row[0], row[1], row[2])


#test_varchar(driver, db_url, usr, passwd)
test_varchar("org.postgresql.Driver", 'jdbc:postgresql://127.0.0.1/pg_testdb', 'postgres', 'postgres')
test_varchar("oracle.jdbc.driver.OracleDriver", 'jdbc:oracle:oci:@MNTEST', 'user', 'passwd')
test_varchar("com.informix.jdbc.IfxDriver", 'jdbc:informix-sqli://127:0:0:1:9088/test_td:informixserver=ol_mn;DB_LOCALE=pl_PL.CP1250;CLIENT_LOCALE=pl_PL.CP1250;charSet=CP1250', 'user', 'passwd')

# db_url = jdbc:odbc:[ODBC source name]
test_varchar("sun.jdbc.odbc.JdbcOdbcDriver", 'jdbc:odbc:inf_test_db_odbc', 'user', 'passwd')
test_varchar("sun.jdbc.odbc.JdbcOdbcDriver", 'jdbc:odbc:ora_testdb_odbc', 'user', 'passwd')
test_varchar("sun.jdbc.odbc.JdbcOdbcDriver", 'jdbc:odbc:pg_testdb_odbc', 'postgres', 'postgres')

Results (for Informix only):

C:\tools\pyscripts\scripts\db_examples>jython --verify test_jdbc2.py


informix-sqli
--------------
[   Informix     ]      [   Informix     ***]   [11]


jdbc:odbc:inf_test_db_odbc
--------------
[   Informix]   [   Informix     ***]   [11]

Answer 1

In ESQL/C, it is most certainly possible to get the trailing spaces from a VARCHAR column; my SQLCMD program (available from the IIUG Software Archive) does it. But you have to be extremely careful to use the correct type for the variables that hold the result. By default, the various char types are treated as CHAR rather than VARCHAR, and the libraries strip trailing blanks from CHAR values unless you direct otherwise (and blank pad to full length when you do direct otherwise).

Regarding ISA: I don't know how you established what it returns. I'm not altogether surprised that it loses the trailing blanks. Similar comments would apply to DB-Access.

Regarding ODBC: can you show the code, please, because although it is possible that there's a bug in the code (thank you for including the version information - it helps, and reassures me you are effectively up-to-date with your system), it is more likely that there is something up with the code you wrote to use it.

Regarding LENGTH(): it is defined to remove trailing blanks before calculating the length; it always treats its argument as if it was a CHAR value rather than as a VARCHAR value.

Taking your code and using SQLCMD:

Black JL: sqlcmd -d stores - <<!
        > CREATE TABLE tmptable (txt varchar(240));
        > INSERT INTO tmptable (txt) VALUES ('123   ');
        > SELECT txt, txt || '***', LENGTH(txt) FROM tmptable;
        > !
123   |123   ***|3
Black JL:

'Black JL:' is my Unix prompt on machine 'black'; as you can see, I got the trailing blanks OK (but I wrote SQLCMD about 20 years ago, now, in part because DB-Access, or rather its predecessor ISQL, didn't do things carefully enough for my purposes).

Answer 2

For anyone that doesn't feel like guessing what the "correct type" is, I've gotten my esql program to work by specifying a "lvarchar" type. (at least one version of the esql/c guide implies that "varchar" should work, but it didn't for me)