RE: Pipe Command

---------- From: Jim Mershon[SMTP:shenango@shenango.com] Sent: Thursday, April 09, 1998 9:11 AM I'm new at using ESQL/C and need some help. Can anyone explain how to use a pipe command to move data from a btrieve database to an informix database? What I do in c is move each record from btrieve and convert them to strings. Some told me to use the pipe command from that point on to move the records to informix. Really could use some help on this. JL: Well, I haven't noticed anybody else answering, so I'll try... Your question is a bit vague, so I'm going to have make some assumptions as I go. I'm assuming that you have a C program which reads a Btrieve database and can get records into variables in memory. Since you want to get the data into an Informix database, I assume that you'd either (a) like to execute a Informix LOAD statement in some shape or form, or (b) like to generate a stream of INSERT statements which will do the same job, albeit probably less efficiently. Let's deal with Method B first, since it is easier. You have to write code in your C/Btrieve program which will create syntactically correct INSERT statements for the data you collect from Btrieve. This is not very hard to do; you have to embed strings inside quotes, doubling up any occurrences of the same quote character in the string, separating the statements by semi-colons - newlines are optional but recommended. The main restriction you'll run into is that character strings longer than 256 characters cannot be handled with this sort of INSERT statement. You might encounter problems with formatting date and datetime values; you cannot insert strings which contain newline characters; and you won't be able to insert blobs (BYTE and TEXT) values this way. (Yes, I'll come back to the pipe bit shortly) Method A requires you to generate output records in the format which will be understood by the Informix loading commands. Basically, you write each record on a single line with a delimiter between each field, normally a '|' but you can choose what you like, more or less. You do not enclose strings in quotes. If you have characters strings containing newlines, escape the newline with a backslash. If your data contains the delimiter, escape that with a backslash. I don't think you have to escape backslashes, but my memory could be failing. If you are dealing with TEXT data, treat it as a giant string. If you are dealing with BYTE data, encode it as a sequence of hex digits with no newlines anywhere - so 'this' becomes '74686973'. The record ends with an unescaped newline. OK, we know what format of data we need to write; how do we actually write it. You said you wanted to use a pipe. Presumably you have a program in mind which will read from the pipe. Also, presumably, you are using a POSIX-compliant system (eg Unix). The answer is that you use the popen() and pclose() functions: fp = popen("program and arguments", "w"); if (fp != 0) { /* write output on fp using fprintf(fp, ...) etc */ pclose(fp); } Next question: which program do you run? Method B is easier, again; you run either DB-Access or ISQL or you get some other command interpreter such as SQLCMD from the IIUG archives (http://www.iiug.org/ -- I wrote SQLCMD). With dbaccess, the program you execute is: fp = popen("dbaccess your_ix_dbase -", "w"); If you're using ISQL, substitute 'isql' for 'dbaccess'. You can create the command string using sprintf or any other technique you like. Method A requires a loader program that will read the load data from standard input. The only tool I know which does that is SQLCMD and one of its alternative names, SQLRELOAD. I'd use SQLRELOAD: fp = popen("sqlreload -d your_ix_dbase -t table", "w"); As far as I know, DBLOAD, DBIMPORT, ISQL, DB-Access all require load data to be in a file, in which case your C/Btrieve program has to write the data to a file and then arrange to run a LOAD command via one of these programs. This really doesn't need a pipe. OK, that's probably verging on information overload. Enough for now. Ask if you need more help. Yours, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h> PS: SQLCMD has some reasonably robust ESQL/C code in it.