Host vars and code page conversion question

Hello, Does anybody know what is the documented and known behavior of inserting/updating binary columns using host variables from a client to a server which have different code pages? Will any code page / character set conversion take place? I am particulary interested in insert/update from subqueries. eg: insert into t1(binarycol) select :HV1 from t2versus insert into t1(binarycol) select :HV1||charcol from t2 update t1 set bytecol=:HV1versus update t1 set bytecol=:HV1||'abc' insert into t1 (bytecol) values(:HV1)versus insert into t1 (bytecol) values(:HV1||'abc') Is the conversion dependent on the context? Thanks Aakash

Aakash Bordia wrote: > Hello, > Does anybody know what is the documented and known behavior of > inserting/updating binary columns using host variables from a client to a > server which have different code pages? Will any code page / character set > conversion take place? I am particulary interested in insert/update from > subqueries. > > eg: > insert into t1(binarycol) select :HV1 from t2> versus > insert into t1(binarycol) select :HV1||charcol from t2> > update t1 set bytecol=:HV1> versus > update t1 set bytecol=:HV1||'abc'> > insert into t1 (bytecol) values(:HV1)> versus > insert into t1 (bytecol) values(:HV1||'abc')> > Is the conversion dependent on the context? What do you mean by a 'binary column'? BYTE values should not be affected by code set conversions - provided you tell the code that it is a BYTE column and do not let it think it is a TEXT column. Which programming language are you using? It looks a bit as if it might be ESQL/C, but you can't do concatenation of host variables with character strings as you seem to be attempting - at least, not if they are BYTE columns. So, I'm a little puzzled... -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

> What do you mean by a 'binary column'? BYTE values should not be > affected by code set conversions - provided you tell the code that it > is a BYTE column and do not let it think it is a TEXT column. By binary, I meant BYTE/TEXT. > Which programming language are you using? It looks a bit as if it > might be ESQL/C, but you can't do concatenation of host variables with > character strings as you seem to be attempting - at least, not if they > are BYTE columns. So, I'm a little puzzled... > This question was more generic than specific to any programming language actually. I guess if Informix does not allow insert into t1(bytecol) select charcol from t2then it gives me my answer anyways (irrespective of whether there is a HV or not) I should have made my question more specific to Informix, and added this: "Do my SQL statements make sense on the data source at all?". Thanks Aakash

Aakash Bordia wrote: >>What do you mean by a 'binary column'? BYTE values should not be >>affected by code set conversions - provided you tell the code that it >>is a BYTE column and do not let it think it is a TEXT column. > > By binary, I meant BYTE/TEXT. BYTE columns are binary; TEXT columns are not. Although they are interchangeable in many respects, this is likely to be an important difference (I confess, I've not tried to push the limits of code set conversions). So, it would be crucial to ensure that BYTE data is sent to the server as BYTE data and not accidentally treated as TEXT data. >>Which programming language are you using? It looks a bit as if it >>might be ESQL/C, but you can't do concatenation of host variables with >>character strings as you seem to be attempting - at least, not if they > > This question was more generic than specific to any programming language > actually. > I guess if Informix does not allow > insert into t1(bytecol) select charcol from t2> then it gives me my answer anyways (irrespective of whether there is a HV or > not) There is no default conversion from CHAR to TEXT (or BYTE). In IDS 9.x, you could add one - as a C UDR - but it is not there as standard. > I should have made my question more specific to Informix, and added this: > "Do my SQL statements make sense on the data source at all?". They weren't too badly off target - in fact, they were perfectly reasonable questions. It is just that there are some oddities in the support for BYTE and TEXT in Informix databases. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/