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]
Best Answer
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:
' 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).