Re: dbload (or other tool) and TEXT columns

On Thu, 13 Nov 1997, SteveO wrote: > I am moving a table from a non-informix DB to an informix DB. > The other DB didn't have TEXT columns and they were implemented as > a table (key, seq_num, varchar text field). > > I can get the data from the other table into a flat file and basically > concatinate the multiple "text" fields into a single TEXT column Good; that's probably the hardest part. > My question is this... > dbload appears to operate in two modes, column oriented (which would > seem inappropriate given the variable length of the "text" column) > And delimiter oriented... Correct, both on the two modes and the inappropriateness of the column-oriented mode for dealing with blobs. > I am going to try to see if I can find a delimiter not used anywhere in > the text... Don't struggle with this; there is an escape convention which allows almost any valid character to be used as a delimiter. > If I can, should the record look like: > > field1|field2|...|text_stuff|<newline> (Note: newlines do appear in > the text_stuff) No. You should do a litle reverse engineering by loading some representative data into a blob field and then unloading it again. The easiest way to do this, assuming you have I-SQL installed as one of your products, is to create a dummy table with a blob column and a form to manipulate it and then use the form to enter the data. If you don't have I-SQL, then you're stuck with a boot-strapping operation. There are tools out in the IIUG archives (http://www.iiug.org) which can do this (updblob, for example, though the code in the archive dates from 1992; I've got a newer version which works better on MODE ANSI databases). However, the correct format, assuming that the delimiter is '|' is: field1|field2|...|text stuff\\ with new lines escaped by backslashes\\ and with interpolated escaped \\| pipe symbols and \\\\ backslashes\\ | The loaded data for the text column should be text stuff with new lines escaped by backslashes and with interpolated escaped | pipe symbols and \\ backslashes Note that I'm assuming that the last line of the text stuff ends with a new line; you then get to see the delimiter on the next line. If there were other columns in the table after the text field, the data for them would occur after the delimiter at the beginning of the line. > Is there a better way to upload text columns? Not really. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>