www.mimer.com/developer

BLOBSAMP Program Using Dynamic SQL for Handling Binary Data

---

The BLOBSAMP program demonstrates the use of the VARCHAR data type to store BLOB's (Binary Large OBjects). The program copies the contents of a file into the database as a BLOB, it then performs a SELECT to retrieve it again and compares the result with the original file. The username, password and file name should be specified as input parameters to the program and the program assumes that the ident in the database identified by username has access to a databank where a table can be created.

The BLOBSAMP program expects parameters to be specified on the command-line. If no parameters are the usage text below is printed:

 Usage: blobsamp username password filename

Building the BLOBSAMP Program

UNIX: The following example assumes MIMER is installed in the directory /opt/mimer821A and illustrates how the BLOBSAMP program is compiled and built by using the distributed example makefile: $ mkdir blobsamp# Do everything in a sub directory $ cd blobsamp $ cp /opt/mimer821A/examples/ex_makefile ./makefile $ cp /opt/mimer821A/examples/blobsamp.ec . Take a copy of the makefile and update it so that the MYPROG symbol is set to "blobsamp", as the name of the program to be created (avoid trailing spaces). MYPROG = blobsamp Then make the blobsamp program: $ export MIMER_HOME=/opt/mimer821A $ make . .

VMS: The following example illustrates how the BLOBSAMP program is compiled and linked under VMS. The program is supplied in C. $ ESQL/C MIMEXAMPLES8:BLOBSAMP ! Preprocess source code$ CC BLOBSAMP ! Compile sample program$ LINK BLOBSAMP,MIMLIB8:MIMER/OPT ! Link executable program $ BLOBSAMP:=$disk:[current.dir]BLOBSAMP ! Define BLOBSAMP so it ! can be run

Win: On Windows platforms, the examples files are installed in the dev directory which is located below the installation directory. The dev directory contains a makefile called makefile.mak, which will build all the example programs at the same time. The examples have been tested using Microsoft Visual C. These instructions assume that Microsoft Visual C is used and that the make command is being executed from the dev directory, appropriate adjustments must be made if this is not the case. $ nmake -f makefile.mak

Source Code for the BLOBSAMP Program in C

 /********************************************************************/
 /* blobsamp.ec - sample program for put/get binary data using ESQL  */
 /********************************************************************/
 /*                                                                  */
 /* Created by Upright Database Technology AB.                       */
 /*                                                                  */
 /* You have a free right to use, modify, reproduce, and distribute  */
 /* the sample files (and/or any modified version) in any way you    */
 /* find useful, provided that you agree that Upright Database       */
 /* Technology AB has no warranty obligations or liability for any   */
 /* sample files which are modified.                                 */
 /*                                                                  */
 /********************************************************************/
 /*                                                                  */
 /* This sample program just shows how to read a file and insert it  */
 /* into the table:                                                  */
 /*                                                                  */
 /*              BLOB(ID   int,                                      */
 /*                   SEQ  int,                                      */
 /*                   DATA varbinary(15000) not null,                */
 /*                   primary key(ID,SEQ))                           */
 /*                                                                  */
 /* and then how to fetch the data from the table BLOB and compare   */
 /* it with the file.                                                */
 /*                                                                  */
 /********************************************************************/
 
 #include <stdlib.h>
 #include <stdio.h>
 #include <string.h>
 
 #define SQL_NO_DATA "02000"
 
 #define SQL_BINARY_VARCHAR -13  /* MIMER-specific type code */
 
 exec sql begin declare section;
 static char buffer[15001];      /* data buffer */
 static char sqlstate[6];
 static char sqlselect[35] = "select DATA from BLOB where ID = ?";
 static char sqlinsert[31] = "insert into BLOB values(?,?,?)";
 exec sql end declare section;
 
 static int putblob (int,char*);
 static int chkblob (int,char*);
 static int error   (char*,int);
 
 /********************************************************************/
 int main(int argc,char** argv)
 /********************************************************************/
 {
     exec sql whenever sqlerror goto exception;
     exec sql begin declare section;
     char  uid[129];
     char  pwd[19];
     exec sql end declare section;
     char* filename;
     int   id = 1;
 
     if (argc != 4)
     {
         printf("\nUsage: blobsamp username password filename\n\n");
         return 0;
     }
     strcpy(uid,argv[1]);
     strcpy(pwd,argv[2]);
     filename = argv[3];
 
     exec sql connect to '' user :uid using :pwd;
 
     exec sql create table BLOB(ID   int,
                                SEQ  int,
                                DATA varbinary(15000) not null,
                                primary key(ID,SEQ));
     exec sql commit;
 
     if (putblob(id,filename) == 0) /* Store file contents in table BLOB */
     {
         chkblob(id,filename);      /* Check that we can read the data     */
     }
     exec sql drop table BLOB;
     exec sql commit;
 
     exec sql disconnect;
     return 0;
 
 exception:
 
     error(buffer,sizeof(buffer));
     printf("%s\n",buffer);
     exec sql disconnect;
     return 0;
 }
 /********************************************************************/
 /*                                                                  */
 /*  Put the contents of a file into the database                    */
 /*                                                                  */
 /*  Parameters:                                                     */
 /*                                                                  */
 /*      int   blobid   : identifier for the data                    */
 /*      char* filename : name of the file                           */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*      0 : OK                                                      */
 /*     -1 : error                                                   */
 /*                                                                  */
 /********************************************************************/
 static int putblob(int blobid,char* filename)
 /********************************************************************/
 {
     exec  sql whenever sqlerror goto exception;
     exec  sql begin declare section;
     int   id;
     int   seq;
     int   type;
     int   length;
     exec  sql end declare section;
     FILE* file;
 
     if ((file = fopen(filename,"rb")) == NULL)
     {
         printf("open error\n");
         return -1;
     }
     id   = blobid;
     type = SQL_BINARY_VARCHAR;
 
     exec sql prepare statement from :sqlinsert;
     exec sql allocate descriptor 'input' with max 3;
     exec sql describe input statement using sql descriptor 'input';
 
     for (seq = 1; !feof(file); seq++)
     {
         length = fread(buffer,1,15000,file);
         if (ferror(file))
         {
             printf("read error\n");
             return -1;
         }
 
         exec sql set descriptor 'input' value 1 data   = :id;
         exec sql set descriptor 'input' value 2 data   = :seq;
         exec sql set descriptor 'input' value 3 data   = :buffer,
                                                 type   = :type,
                                                 length = :length;
 
         exec sql execute statement using sql descriptor 'input';
     }
     exec sql commit;
     exec sql deallocate descriptor 'input';
     exec sql deallocate prepare statement;
     fclose(file);
     return 0;
 
 exception:
 
     error(buffer,sizeof(buffer));
     printf("%s\n",buffer);
     return -1;
 }
 /********************************************************************/
 /*                                                                  */
 /* Check that stored data is exactly the same as the file contents. */
 /*                                                                  */
 /*  Parameters:                                                     */
 /*                                                                  */
 /*      int   blobid   : identifier for the data                    */
 /*      char* filename : name of the file                           */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*      0 : OK                                                      */
 /*     -1 : error                                                   */
 /*                                                                  */
 /********************************************************************/
 static int chkblob(int blobid,char* filename)
 /********************************************************************/
 {
     exec  sql whenever sqlerror goto exception;
     exec  sql begin declare section;
     int   id;
     int   type;
     int   length;
     exec  sql end declare section;
     FILE* file;
     char  record[15000];
 
 
     if ((file = fopen(filename,"rb")) == NULL)
     {
         printf("open error\n");
         return -1;
     }
     id   = blobid;
     type = SQL_BINARY_VARCHAR;
 
     exec sql prepare statement from :sqlselect;
 
     exec sql allocate descriptor 'input'  with max 1;
     exec sql allocate descriptor 'output' with max 1;
 
     exec sql describe input  statement using sql descriptor 'input';
     exec sql describe output statement using sql descriptor 'output';
 
     exec sql set descriptor 'input'  value 1 data = :id;
 
     exec sql declare c cursor for statement;
     exec sql open c using sql descriptor 'input';
     for (;;)
     {
         exec sql fetch next from c into sql descriptor 'output';
 
         if (strcmp(sqlstate,SQL_NO_DATA) == 0) break;
 
         exec sql get descriptor 'output' value 1 :buffer = data,
                                                  :type   = type,
                                                  :length = returned_length;
 
         if (fread(record,1,15000,file)   != (size_t)length ||
             memcmp(buffer,record,length) != 0)
         {
             printf("compare error\n");
             return -1;
         }
     }
     exec sql close c;
     exec sql deallocate descriptor 'output';
     exec sql deallocate descriptor 'input';
     exec sql deallocate prepare statement;
     fclose(file);
     printf("compare ok!\n");
     return 0;
 
 exception:
 
     error(buffer,sizeof(buffer));
     printf("%s\n",buffer);
     return -1;
 }
 /********************************************************************/
 /*                                                                  */
 /*  Get error message text.                                         */
 /*                                                                  */
 /*  Parameters:                                                     */
 /*                                                                  */
 /*      char* message : buffer for the result string                */
 /*      int   len     : length of the buffer (must be >= 32)        */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*      0 : OK                                                      */
 /*     -1 : error (cannot get message text)                         */
 /*                                                                  */
 /*  Note:                                                           */
 /*                                                                  */
 /*  The message text is returned in a newline ('\n') separated      */
 /*  string. If the string is too large, it is truncated.            */
 /*                                                                  */
 /********************************************************************/
 static int error(char* message,int len)
 /********************************************************************/
 {
     exec sql whenever sqlerror goto exception;
     exec sql begin declare section;
     int   count;
     int   length;
     char  state [6]; /* returned_sqlstate may not be stored in sqlstate */
     int   n;
     exec sql end declare section;
     char* p;
 
     /*
      *  Check arguments.
      */
     if (message == NULL || len < 32) return -1;
     /*
      *  Get number of errors.
      */
     exec sql get diagnostics :count = number;
     for (n = 1; n <= count; n++)
     {
         /*
          *  Get diagnostics of an error.
          */
         exec sql get diagnostics exception :n :buffer = message_text,
                                               :length = message_length,
                                               :state  = returned_sqlstate;
         /*
          *  Prepare message area.
          */
         if (length > len - 18) length = len - 18;
         memcpy(message,buffer,length);
         p = strchr(message,':');
         if (p) *p = '\n';
         if (length > 78)
         {
             p = strchr(&message[78],' ');
             if (p) *p = '\n';
         }
         message += length;
         len     -= length;
         *message++ = '\n';
         /*
          *  Add SQLSTATE code.
          */
         strcpy(message,"SQLSTATE:");
         message += 9;
         strcpy(message,state);
         message += 5;
         *message++ = '\n';
         *message++ = '\n';
         len -= 17;
         if (len < 32) break;
     }
     *message = '\0';
     return 0;
 
 exception:
 
     strcpy(message,"cannot get diagnostics\n\n");
     return -1;
 }

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se