Loading BLOB's into a BLOBspace

IDS 7.31 AIX 4.3.2 Client SDK 2.02 I've two questions on using BLOB's: 1. How do you use "dbload" or "load" to insert a row containing TEXT/BYTE columns into a table? Informix Manuals in various places say that these two utilities can be used to insert TEXT and BYTE data. I see how TEXT data can be used as part of the text file that "dbload" and "load" require as the source. But how does one insert a BYTE column that contains binary data? 2. I can use ESQL/C to load both TEXT and BYTE fields into corresponding columns in a table. But how do you ensure that the TEXT and BYTE data get loaded into a predefined BLOBspace and not into the regular DBspace? I tried a few sample insertions and saw that the BLOB data also get loaded into the normal DBSPACE, whereas the BLOBspace remains empty. Thanks in advance. Alanoly J. Andrews

Alanoly Andrews wrote: > > IDS 7.31 > AIX 4.3.2 > Client SDK 2.02 > > I've two questions on using BLOB's: > > 1. How do you use "dbload" or "load" to insert a row containing TEXT/BYTE > columns into a table? Informix Manuals in various places say that these > two > utilities can be used to insert TEXT and BYTE data. I see how TEXT data > can > be used as part of the text file that "dbload" and "load" require as the > source. > But how does one insert a BYTE column that contains binary data? Encode each binary byte as an escaped octal value. Just UNLOAD a BYTE column to see an example. > 2. I can use ESQL/C to load both TEXT and BYTE fields into corresponding > columns > in a table. But how do you ensure that the TEXT and BYTE data get loaded > into > a predefined BLOBspace and not into the regular DBspace? I tried a few > sample > insertions and saw that the BLOB data also get loaded into the normal > DBSPACE, > whereas the BLOBspace remains empty. You define where the BLOB columns are to be stored at the time a table is created. Thus either: create table media ( key serial, image BYTE );OR create table media ( key serial, image BYTE IN TABLESPACE ); create a table with a BYTE type column in TABLESPACE. While: create table media ( key serial, image BYTE IN my_blob_space ); creates a table with a BYTE column in the BLOBSPACE named "my_blob_space". Art S. Kagel

Art S. Kagel wrote: > Alanoly Andrews wrote: > > IDS 7.31 > > AIX 4.3.2 > > Client SDK 2.02 > > > > I've two questions on using BLOB's: > > > > 1. How do you use "dbload" or "load" to insert a row containing > > TEXT/BYTE columns into a table? Informix Manuals in various > > places say that these two utilities can be used to insert TEXT > > and BYTE data. I see how TEXT data can be used as part of the > > text file that "dbload" and "load" require as the source. But > > how does one insert a BYTE column that contains binary data? > > Encode each binary byte as an escaped octal value. Just UNLOAD a > BYTE column to see an example. Hmm; I dislike disagreeing with people (contrary perhaps to general appearances), but this is not accurate. Each byte of a BYTE blob is encoded as 2 hexadecimal (not octal) characters, with the first character representing the more significant 4 bits of the byte, and the second representing the less significant 4 bits. In other words, the UNLOAD format for a 1 KB BYTE blob occupies 2 KB. Although the Informix commands always generate upper-case letters for the hexadecimal digits (unless my brain has gone on holiday), both upper and lower case letters are accepted on input. There's a detailed description of the Informix UNLOAD format, including the various historical variants on the current format, in the file unload.format which is part of the distribution of SQLCMD available from the IIUG software archive. If you do find any inaccuracies or insufficiently precise language in that file, please let me know so I can fix it. > > 2. I can use ESQL/C to load both TEXT and BYTE fields into corresponding > > columns in a table. But how do you ensure that the TEXT and BYTE data > > get loaded into a predefined BLOBspace and not into the regular DBspace? > > I tried a few sample insertions and saw that the BLOB data also get > > loaded into the normal DBSPACE, whereas the BLOBspace remains empty. > > You define where the BLOB columns are to be stored at the time a table is > created. Thus either: > > create table media ( > key serial, > image BYTE );> OR > create table media ( > key serial, > image BYTE IN TABLESPACE );> > create a table with a BYTE type column in TABLESPACE. The gist of this is correct. In point of detail, the keyword indicating that the blob is stored in the same dbspace as the rest of the table is TABLE, not TABLESPACE. >While: > > create table media ( > key serial, > image BYTE IN my_blob_space );> > creates a table with a BYTE column in the BLOBSPACE named "my_blob_space". Sorry, Art; it's just me being fussy. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>