Re: ODBC and binary data in character sql type

Hi! There are three problems you need to take care of: - How the ESQL/C API interprets your string with embedded ASCII NULLs. - The ODBC driver (have we seen THIS one before!!) - How a strings with embedded ASCII NULLs are stored. In ESQL/C a string of the type char or string is seen as a nullterminated string. You can use varchar or fixchar to achieve what you want. The problem is that you use ODBC, and in this case you depend on how the ODBC driver you use map SQL_C_CHAR. It might be that it maps it to CSTRING (i.e. a null-terminated string). In this case, you have a problem. It really should map it to CFIXCHARTYPE. I don't have all the manuals in fron of me, so there might be an error or two in here, but basically I think I'm correct in what I'm stating here. Thirdly, an Informix CHAR string as stored in the database can contain any character, including embedded ASCII NULL's, EXCEPT IN THE FIRST POSITION, in which case the string, when stored in the database, is interpreted as beging SQL NULL by the database. This is REAL stupid, but it is the way things work. As it's only the first position that is a problem, you can fix this by making the column CHAR(17) and put some dummy character first in the string (can be any character, except ASCII NULL of course :-)). Now, if your ODBC driver has the problem mentioned above, one way of dealing with this problem could be to store the value in 2 DOUBLE's instead, as they are 8 bytes wide (usually). This IS a kludge, but what the heck, it should work, and then you created an index on these two columns together. There are some drawbacks to this, of course, not the least that it means that this will only work on different machines in a network if these machines have the same byte ordering.... Rgds Karlsson Marvin Boswell wrote: > I have a binary column in a table. I want to place an index on it so the > Byte datatype is out. It is just a 16 byte field so I defined it as > CHAR(16). However, it can contain embedded nulls. When I insert and > then fetch the column any data after an embedded null is replaced with > 0x20. > I reference the column as C type SQL_C_BINARY and sql type SQL_CHAR > (using C type sql_c_char does not work either). > The ODBC reference does state that "applications should always handle > character data that can contain embedded null characters as binary data." > > Anyone have any suggestions of how to accomplish this on Informix? In > DB2 and Oracle it is easy, just define the column as char(16) for bit > data or raw(16) respectively and access as sql_c_binary and sql_binary. > Informix does not seem to have as flexible a set of binary types. > -- > > Marvin Boswell (919) 543-9687 > Internet: boswell@us.ibm.com -- ==================================================================== Anders Hackin' Karlsson Licensed Database Dude - Ainigma Solutions AB Email: andersk@ainigma.com or andersk.karlsson@karlsson.pp.se "Every time I've built character, I've regretted it" Calvin ====================================================================