Microsoft KB Archive/184564

= INF: ESQL: How to Handle Binary Data in ESQL =

PSS ID Number: 184564

Article Last Modified on 10/3/2003

-

The information in this article applies to:


 * Microsoft SQL Server 6.5

-



This article was previously published under Q184564



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:

184559 : 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    #include 

//   // 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: kbinfo KB184564

Technology: kbAudDeveloper kbSQLServ650 kbSQLServSearch

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.