INF: ESQL: How to Handle Binary Data in ESQL

ID: Q184564

The information in this article applies to:

SUMMARY

This article describes how to handle binary data while inputting and outputting through ESQL.


MORE INFORMATION

Input host variables are those through which data is input to ESQL. A variable in an INSERT statement or UPDATE statement or a variable in a WHERE clause is considered to be an input host variable. Output host variables are those to which ESQL feeds data; a host variable that contains the result of a SELECT statement is considered an output host variable.

How to Handle Input Host Variables for Binary Data

The following passage comes from the ESQL documentation: 

   When input, a C pointer datatype can be mapped to a SQL Server binary,
   varbinary, or image column using dynamic SQL statements. To do this, use
   two question marks (??) instead of one as parameter markers. If you
   include at least one space between the parameter marker and its
   indicator, you can use indicator variables. You cannot use static SQL
   statements to map binary data on input parameters, but you can use
   dynamic SQL statements with PREPARE/EXECUTE statements or the EXEC flag
   to avoid this limitation.

Therefore, binary data can only be input using dynamic ESQL, as stated above; you cannot use static ESQL.

NOTE: There is a hotfix that can be used to input binary data using static ESQL as long as the user edits the generated .c file to provide the correct size for the host variable. For more information, see the following article in the Microsoft Knowledge Base:
Q184559 : BUG: ESQL: AV Using Void * for Host Variable for Binary Datatype

How to Handle Output Host Variables for Binary Data

On singleton selects and cursor fetches, you can simply map SQL Server binary column data to C host variables declared as unsigned character arrays without any data conversion. You should be able to use both static and dynamic ESQL to do this.

The following is a sample, which shows how to insert and retrieve binary data from a table: 

   // 
   // requirements: pubs..tblBinary exists
   // 

  /******************************************************

   use pubs
   go
   create table tblBinary
   (
           Id     int,
           ColBinary binary(10)
   )
   go

   *******************************************************/ 
   #include <stdio.h>
   #include <stdlib.h>

   // 
   // local prototypes
   // 

   void ErrorHandler(void);

   void main(int argc, char *argv[])
   {
      int i = 0;
      EXEC SQL BEGIN DECLARE SECTION;
         unsigned char uColBinary[10] = "";
         unsigned char uColBinary2[10] = "";
         int  iId     = 0;
         int  iId2    = 0;
         char strStmtBuffer[1024] = "";
      EXEC SQL END DECLARE SECTION;

      // 
      // error handler
      // 

      EXEC SQL WHENEVER SQLERROR CALL ErrorHandler();

      // 
      // connect to sql server, replace myserver with your server name
      // and sapassword with your password
      // 

      EXEC SQL CONNECT TO myserver.pubs USER sa.sapassword;
      if(SQLCODE == 0)
      {
         // 
         // simple insert - input host variable
         // 
         iId = 28;
         strcpy(strStmtBuffer, "INSERT into tblBinary values( ?, ?? )");
         EXEC SQL PREPARE prepInsert FROM :strStmtBuffer;
         if(SQLCODE == 0)
         {
         memcpy(uColBinary,"\x44\x49\x00\x00\x0c\x42\x42\x42\x42\x42",10);
            EXEC SQL EXECUTE prepInsert USING :iId, :uColBinary;
            if(SQLCODE == 0)
            {
               // 
               // Select based on a where clause - input host variable
               // 
               strcpy(strStmtBuffer, "SELECT Id from tblBinary WHERE
               ColBinary = ?? ");

               EXEC SQL DECLARE IdCursor CURSOR FOR selStmt;

               EXEC SQL PREPARE selStmt FROM :strStmtBuffer;
          if (SQLCODE == 0)
               {
                 EXEC SQL OPEN IdCursor USING :uColBinary;
                 // perform until sqlcode not = zero.
                 while (SQLCODE == 0)
                 {
                 EXEC SQL FETCH IdCursor INTO :iId2;

                    if (SQLCODE == 0)
                       printf("The ID is = %d\n", iId2);
                 }
                 // Close cursor
                 EXEC SQL CLOSE IdCursor;
              }

               // 
               // simple Select using static esql - output host variable
               // 

               EXEC SQL
                  SELECT ColBinary INTO :uColBinary2
                  FROM tblBinary WHERE Id = :iId;

               if (SQLCODE == 0)
               {
                  printf("The colBinary is =  ");
                  for(i=0; i < sizeof(uColBinary2); i++)
                     printf("%2x", uColBinary2[i]);
                  printf("\n");
               }
               memset(uColBinary2, 0, sizeof(uColBinary2));

               // 
               // simple Select using dynamic esql - output host variable
               // 
               strcpy(strStmtBuffer, "SELECT ColBinary from tblBinary WHERE
               Id = ? ");
               EXEC SQL DECLARE BinCursor CURSOR FOR selStmt2;

               EXEC SQL PREPARE selStmt2 FROM :strStmtBuffer;
          if (SQLCODE == 0)
               {
                 EXEC SQL OPEN BinCursor USING :iId;
                 // perform until sqlcode not = zero.
                 while (SQLCODE == 0)
                 {
                    EXEC SQL FETCH BinCursor INTO :uColBinary2;

                    if (SQLCODE == 0)
                    {
                       printf("The colBinary is =  ");
                       for(i=0; i < sizeof(uColBinary2); i++)
                           printf("%2x", uColBinary2[i]);
                       printf("\n");
                    }
                 }

                 // Close cursor
                 EXEC SQL CLOSE BinCursor;
              }
           }
         }
       }

       // 
       // cleanup
       // 

       EXEC SQL DISCONNECT ALL;
   }

   // 
   // ESQL error handler
   // 

   void ErrorHandler(void)
   {
      printf("    SQL Code = %li\n", SQLCODE);
      printf("    Error %li (%li): %s\n\n", SQLERRD1, SQLERRD2, SQLERRMC);
   }

Additional query words: bin 


Keywords          : SSrvESQL_C SSrvProg 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: April 16, 1999