Re: informix unloads converting the data

On Fri, Dec 19, 2008 at 2:47 PM, Floyd Wellershaus <floyd@fwellers.com> wrote: > Hi, > This is on ids10.0 and aix5.3 > > We're trying to convert some data over to another database. When unloading > certain fields that have encrypted data inserted into them ( not informix > encryption ), we noticed that the data is getting converted a bit. How are you doing the unload? With the UNLOAD statement? If so, then it always emits two backslashes for each actual backslash in the data; it also emits backslash-pipe for any pipe in the data (assuming DBDELIMITER is unset or is set to pipe; change the delimiter, and any occurrence of the delimiter in the data is emitted as backslash-delimiter). > For instance, an \\F in the character string in the database, comes out as a > \\\\F in the unload file. That would be correct. What did you have in mind? > The delimiter doesn't matter. It is still putting some extra escape quotes > in certain places. > > We can't use the HP load internal format, because the other database > wouldn't know what to do with it. > > Are there any ideas as to how to prevent this, or why it happens ? It happens because the input (LOAD) process expects backslash to escape the following character. If your data file contained just \\F, then a single character, F, would be loaded. If you don't like the Informix UNLOAD format, you are faced with a variety of options. You can write your own unloader that formats the data the way you think is correct. You can try using SQLCMD; it has a variety of output formats and one of those may suit you. However, by default, it uses backslash as an escape for backslash and the other delimiters. You can, however, change the delimiter and the escape (whereas standard Informix tools do not allow you to change the escape). It also has CSV and XML output formats. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.