Re: Inserting BLOB into Database Table

You don't say what tools you have available... If you have ISQL, the following info may be of relevance: >From: johnl (Jonathan Leffler) >Date: Wed Nov 4 09:53:59 1992 >To: skidwell@jupiter >Subject: Re: loading into blob space thru sql > >>Date: Tue, 3 Nov 92 14:45:21 CST >>From: skidwell@jupiter (Sherri Kidwell) >>To: tech@jupiter >>Subject: loading into blob space thru sql > >>I have a customer that wants to load a gif file as a blob into >>a table through SQL only. Can this be done? If so, how? > >Dear Sherri, > >The only possible route is through The PROGRAM attribute in the blob field >attributes of a Perform screen -- the load command requires the data in its >own format, and nothing else gets close. > >The program specified by the form will be invoked with the name of a >temporary file as the blob. When the program returns, the contents of that >file will be used as the blob. So, the obvious solution seems to be a >script of some sort which allows the user to specify a GIF file name. I >tried a solution which removed the original temporary file, and did a >symbolic link to the GIF file. Perform should have cleaned up after itself >by removing the symbolic link. However, this did not work satisfactorily >-- it zapped the script I had developed (because I pretended that the >script was a GIF file), which was a nuisance though hardly serious. It >seems that the successful way of doing it is to copy the GIF file over the >temporary file, which leads to the following Bourne Shell script: > >echo >#echo -n "Enter name of GIF file: " >echo "Enter name of GIF file: \\c" >read file >cp $file $1 > >The first echo places the prompt on the line after the "Please wait!" >message issued by Perform. The commented out echo might be necessary on a >BSD system where the Bourne shell does not recognise the "\\c" >metacharacter, which means suppress the newline. > >Yours, >Jonathan Leffler (johnl@obelix) If you ESQL/C, you may be interested in the program in the shell archive below. You may not need getopt.c and/or memmove.c, but I've supplied them just in case. The code was developed on Intercative 386/IX 3.2.2 under OnLine and ESQL/C 5.00.UC1. You use the program updblob as: updblob -d database -t table -k column1=1234,column2=ABC -f blob-file Put quotes around the key strings if there are spaces in the key. I cannot guarantee the behaviour of the key parsing code if you have either commas or equals signs in the key string. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h> : "@(#)shar.sh 1.8" #! /bin/sh # # This is a shell archive. # Remove everything above this line and run sh on the resulting file. # If this archive is complete, you will see this message at the end: # "All files extracted" # # Created: Thu Nov 5 10:02:33 GMT 1992 by johnl at Informix Software Ltd. # Files archived in this archive: # updblob.ec # getopt.c # getopt.h # stderr.c # stderr.h # memmove.c # #-------------------- if [ -f updblob.ec -a "$1" != "-c" ] then echo shar: updblob.ec already exists else echo 'x - updblob.ec (4395 characters)' sed -e 's/^X//' >updblob.ec <<'SHAR-EOF' X/* X@(#)File: updblob.ec X@(#)Version: 1.1 X@(#)Last changed: 92/10/13 X@(#)Purpose: General program to update a blob from file X@(#)Author: J Leffler X@(#)Copyright: (C) JLSS 1992 X@(#)Product: :PRODUCT: X*/ X X/*TABSTOP=4*/ X X#include <stdio.h> X#include <string.h> X#include <sqlca.h> X#include <locator.h> X#include <sqltypes.h> X#include "stderr.h" X#include "getopt.h" X X#define BUFFSIZE 2048 X#define NIL(x) ((x)0) X#define DIM(x) (sizeof(x)/sizeof(*(x))) X Xextern void sql_error(); Xextern void locate_file(); X Xstatic char usestr[] = X "[-Vx] -d dbase -t table -c blobcolumn -k column=value,... [-f] blobfile"; X X#ifndef lint Xstatic char sccs[] = "@(#)updblob.ec 1.1 92/10/13"; X#endif X Xmain(argc, argv) Xint argc; Xchar **argv; X{ X EXEC SQL BEGIN DECLARE SECTION; X char *dbase = (char *)0; X loc_t blob; X char stmt[BUFFSIZE]; X EXEC SQL END DECLARE SECTION; X char *table = (char *)0; X char *blobcol = (char *)0; X int nkeycols = 0; X char *keycol[16]; X char *keyval[16]; X char *blobfile = (char *)0; X int opt; X char *pad; X char *s; X int i; X int xflag = 0; X int len; X X setarg0(argv[0]); X while ((opt = getopt(argc, argv, "Vxd:t:c:k:f:")) != EOF) X { X switch (opt) X { X case 'x': X xflag = 1; X break; X case 'V': X puts(&"@(#):UPDBLOB:"[4]); X exit(0); X /*NOTREACHED*/ X case 't': X table = optarg; X break; X case 'c': X blobcol = optarg; X break; X case 'd': X dbase = optarg; X break; X case 'f': X blobfile = optarg; X break; X case 'k': X nkeycols = parse_columns(optarg, keycol, keyval, DIM(keyval)); X break; X default: X usage(usestr); X /*NOTREACHED*/ X } X } X X if (blobfile == (char *)0 && optind == argc - 1) X blobfile = argv[optind++]; X if (optind != argc || blobfile == NIL(char *) || dbase == NIL(char *) || X table == NIL(char *) || nkeycols == 0 || blobcol == NIL(char *)) X usage(usestr); X X locate_file(&blob, SQLBYTES, blobfile); X EXEC SQL DATABASE :dbase; X if (sqlca.sqlcode != 0) X sql_error("database", dbase); X X sprintf(stmt, "UPDATE %s SET %s = ? WHERE", table, blobcol); X pad = ""; X s = stmt + strlen(stmt); X for (i = 0; i < nkeycols; i++) X { X sprintf(s, "%s %s = '%s'", pad, keycol[i], keyval[i]); X pad = " AND"; X s += strlen(s); X } X X if (xflag) X { X len = strlen(stmt); X for (i = 0; i < len; i += 75) X printf("%.75s\\n", &stmt[i]); X } X X EXEC SQL PREPARE p_update FROM :stmt; X if (sqlca.sqlcode != 0) X sql_error("prepare update", table); X EXEC SQL EXECUTE p_update USING :blob; X if (sqlca.sqlcode != 0) X sql_error("execute update", table); X X return(0); X} X Xvoid locate_file(blob, type, file) Xloc_t *blob; Xint type; Xchar *file; X{ X blob->loc_indicator = 0; X blob->loc_type = type; X blob->loc_loctype = LOCFNAME; X blob->loc_fname = file; X blob->loc_oflags = LOC_RONLY; X blob->loc_size = -1; X} X X/* Database generated error */ Xvoid sql_error(s1, s2) Xchar *s1; Xchar *s2; X{ X char buffer[512]; X X fflush(stdout); X rgetmsg(sqlca.sqlcode, buffer, sizeof(buffer)); X fprintf(stderr, "SQL %d: ", sqlca.sqlcode); X if (sqlca.sqlerrd[1] != 0) X fprintf(stderr, "(ISAM %d) ", sqlca.sqlerrd[1]); X fprintf(stderr, buffer, sqlca.sqlerrm); X if (s1 !