/*******************************************************************************
**
** Source File Name = dtlob.c 1.5
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** Shows how to read/write LOBs.
**
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "utilcli.h" /* Header file for CLI sample code */
int BlobReadAsAWhole( SQLHANDLE) ;
int BlobReadInPieces( SQLHANDLE) ;
int BlobWriteAsAWhole( SQLHANDLE) ;
int BlobWriteInPieces( SQLHANDLE) ;
int ClobReadASelectedPiece( SQLHANDLE) ;
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] )
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handles */
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
if ( rc != 0 ) return( rc ) ;
printf("\n\nDATA TYPES: HOW TO READ/WRITE BLOBs.\n");
/* initialize the CLI application */
rc = CLIAppInit( dbAlias, user, pswd, &henv, &hdbc,
(SQLPOINTER)SQL_AUTOCOMMIT_ON);
if ( rc != 0 ) return( rc ) ;
rc = BlobReadAsAWhole( hdbc) ;
rc = BlobReadInPieces( hdbc) ;
rc = BlobWriteAsAWhole( hdbc) ;
rc = BlobWriteInPieces( hdbc) ;
rc = ClobReadASelectedPiece( hdbc) ;
/* terminate the CLI application */
rc = CLIAppTerm( &henv, &hdbc, dbAlias);
return( rc ) ;
} /* end main */
/******************************************************************************
** BlobReadAsAWhole
******************************************************************************/
int BlobReadAsAWhole( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"SELECT picture FROM emp_photo "
"WHERE empno = ? AND photo_format = ?" ;
char empno[10], photo_format[10];
SQLUINTEGER fileOption = SQL_FILE_OVERWRITE;
SQLINTEGER fileInd = 0;
SQLSMALLINT fileNameLength = 14;
SQLCHAR fileNameBase[] = "photo1";
SQLCHAR fileName[14] = "";
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameters\n");
printf("-SQLBindFileToCol\n-SQLExecute\n");
printf("-SQLFetch\n-SQLCloseCursor\n");
printf("-SQLFreeHandle\n");
printf("TO READ BLOB DATA AS A WHOLE:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf("\n Prepare the statement\n");
printf(" SELECT picture FROM emp_photo\n");
printf(" WHERE empno = ? AND photo_format = ?\n");
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind parameters to the statement */
printf(" Bind parameters to the statement\n");
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 10, 0, empno, 10, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 10, 0, photo_format, 10, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind a file to the BLOB column */
rc = SQLBindFileToCol(hstmt, 1, fileName, &fileNameLength, &fileOption,
14, NULL, &fileInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ----------------read data in a *.bmp file ------------------------*/
/* execute the statement */
printf(" Execute the prepared statement for\n");
printf(" empno = '000140'\n");
printf(" photo_format = 'bitmap'\n");
strcpy( empno, "000140");
strcpy( photo_format, "bitmap");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* set the value for the fileName */
sprintf( (char*)fileName, "%s.bmp", fileNameBase);
/* fetch the result */
printf(" Fetch BLOB data in the file '%s'.\n", fileName);
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
/* close the cursor */
sqlrc = SQLCloseCursor( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ----------------read data in a *.gif file ------------------------*/
/* execute the statement */
printf(" Execute the prepared statement for\n");
printf(" empno = '000140'\n");
printf(" photo_format = 'gif'\n");
strcpy( empno, "000140");
strcpy( photo_format, "gif");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* set the value for the fileName */
sprintf( (char*)fileName, "%s.gif", fileNameBase);
/* fetch the result */
printf(" Fetch BLOB data in the file '%s'.\n", fileName);
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
/* close the cursor */
sqlrc = SQLCloseCursor( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ----------------read data in a *.xwd file ------------------------*/
/* execute the statement */
printf(" Execute the prepared statement for\n");
printf(" empno = '000140'\n");
printf(" photo_format = 'xwd'\n");
strcpy( empno, "000140");
strcpy( photo_format, "xwd");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* set the value for the fileName */
sprintf( (char*)fileName, "%s.xwd", fileNameBase);
/* fetch the result */
printf(" Fetch BLOB data in the file '%s'.\n", fileName);
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** BlobReadInPieces
******************************************************************************/
int BlobReadInPieces( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"SELECT picture FROM emp_photo "
"WHERE empno = ? AND photo_format = ?" ;
char empno[10], photo_format[10];
SQLCHAR fileNameBase[] = "photo2";
/* SQLCHAR fileName[14] = ""; */
char fileName[14] = "";
FILE *pFile;
SQLCHAR buffer[BUFSIZ];
SQLINTEGER bufInd;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameters\n");
printf("-SQLExecute\n-SQLFetch\n");
printf("-SQLGetData\n-SQLFreeHandle\n");
printf("TO READ BLOB DATA IN PIECES:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf("\n Prepare the statement\n");
printf(" SELECT picture FROM emp_photo\n");
printf(" WHERE empno = ? AND photo_format = ?\n");
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind parameters to the statement */
printf(" Bind parameters to the statement\n");
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 10, 0, empno, 10, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 10, 0, photo_format, 10, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ----------------read data in a *.bmp file ------------------------*/
/* execute the statement */
printf(" Execute the prepared statement for\n");
printf(" empno = '000140'\n");
printf(" photo_format = 'bitmap'\n");
strcpy( empno, "000140");
strcpy( photo_format, "bitmap");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch the result */
sprintf( fileName, "%s.bmp", fileNameBase);
printf(" Fetch BLOB data in the file %s.\n", fileName);
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
/* open the file */
pFile = fopen(fileName, "w+b");
if (pFile == NULL)
{ printf(">---- ERROR Opening File -------");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return 1 ;
}
/* get BUFSIZ bytes at a time, bufInd indicates number of Bytes LEFT */
sqlrc = SQLGetData(hstmt, 1, SQL_C_BINARY, (SQLPOINTER) buffer,
BUFSIZ, &bufInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while( sqlrc == SQL_SUCCESS_WITH_INFO || sqlrc == SQL_SUCCESS )
{ if ( bufInd > BUFSIZ) /* full buffer */
{ fwrite( buffer, sizeof(char), BUFSIZ, pFile);
}
else /* partial buffer on last GetData */
{ fwrite( buffer, sizeof(char), bufInd, pFile);
}
sqlrc = SQLGetData( hstmt, 1, SQL_C_BINARY, (SQLPOINTER)buffer,
BUFSIZ, &bufInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* close the file */
fflush( pFile);
fclose( pFile);
/* close the cursor */
sqlrc = SQLCloseCursor( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ----------------read data in a *.gif file ------------------------*/
/* execute the statement */
printf(" Execute the prepared statement for\n");
printf(" empno = '000140'\n");
printf(" photo_format = 'gif'\n");
strcpy( empno, "000140");
strcpy( photo_format, "gif");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch the result */
sprintf( fileName, "%s.gif", fileNameBase);
printf(" Fetch BLOB data in the file %s.\n", fileName);
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
/* open the file */
pFile = fopen(fileName, "w+b");
if (pFile == NULL)
{ printf(">---- ERROR Opening File -------");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return 1 ;
}
/* get BUFSIZ bytes at a time, bufInd indicates number of Bytes LEFT */
sqlrc = SQLGetData(hstmt, 1, SQL_C_BINARY, (SQLPOINTER) buffer,
BUFSIZ, &bufInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while( sqlrc == SQL_SUCCESS_WITH_INFO || sqlrc == SQL_SUCCESS )
{ if ( bufInd > BUFSIZ) /* full buffer */
{ fwrite( buffer, sizeof(char), BUFSIZ, pFile);
}
else /* partial buffer on last GetData */
{ fwrite( buffer, sizeof(char), bufInd, pFile);
}
sqlrc = SQLGetData( hstmt, 1, SQL_C_BINARY, (SQLPOINTER)buffer,
BUFSIZ, &bufInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* close the file */
fflush( pFile);
fclose( pFile);
/* close the cursor */
sqlrc = SQLCloseCursor( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ----------------read data in a *.xwd file ------------------------*/
/* execute the statement */
printf(" Execute the prepared statement for\n");
printf(" empno = '000140'\n");
printf(" photo_format = 'xwd'\n");
strcpy( empno, "000140");
strcpy( photo_format, "xwd");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch the result */
sprintf( fileName, "%s.xwd", fileNameBase);
printf(" Fetch BLOB data in the file %s.\n", fileName);
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
/* open the file */
pFile = fopen(fileName, "w+b");
if (pFile == NULL)
{ printf(">---- ERROR Opening File -------");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return 1 ;
}
/* get BUFSIZ bytes at a time, bufInd indicates number of Bytes LEFT */
sqlrc = SQLGetData(hstmt, 1, SQL_C_BINARY, (SQLPOINTER) buffer,
BUFSIZ, &bufInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while( sqlrc == SQL_SUCCESS_WITH_INFO || sqlrc == SQL_SUCCESS )
{ if ( bufInd > BUFSIZ) /* full buffer */
{ fwrite( buffer, sizeof(char), BUFSIZ, pFile);
}
else /* partial buffer on last GetData */
{ fwrite( buffer, sizeof(char), bufInd, pFile);
}
sqlrc = SQLGetData( hstmt, 1, SQL_C_BINARY, (SQLPOINTER)buffer,
BUFSIZ, &bufInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* close the file */
fflush( pFile);
fclose( pFile);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** BlobWriteAsAWhole
******************************************************************************/
int BlobWriteAsAWhole( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"INSERT INTO emp_photo (empno, photo_format, picture) VALUES (?, ?, ?)" ;
SQLCHAR empno[10], photo_format[10];
SQLUINTEGER fileOption = SQL_FILE_READ;
SQLINTEGER fileInd = 0;
SQLSMALLINT fileNameLength = 14;
SQLCHAR fileNameBase[] = "photo1";
SQLCHAR fileName[14] = "";
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameters\n");
printf("-SQLBindFileToParam\n-SQLExecute\n");
printf("-SQLEndTran\n-SQLFreeHandle\n");
printf("TO WRITE BLOB DATA AS A WHOLE:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf("\n Prepare the statement\n");
printf(" INSERT INTO emp_photo (empno, photo_format, picture)\n");
printf(" VALUES (?, ?, ?)\n");
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind parameters to the statement */
printf(" Bind parameters to the statement\n");
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 10, 0, empno, 10, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 10, 0, photo_format, 10, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind the file-parameter */
rc = SQLBindFileToParam(hstmt, 3, SQL_BLOB, fileName, &fileNameLength,
&fileOption, 14, &fileInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ----------------write data from a *.bmp file ------------------------*/
/* execute the statement */
strcpy( (char*)empno, "000240");
strcpy( (char*)photo_format, "bitmap");
sprintf( (char*)fileName, "%s.bmp", fileNameBase);
printf(" Execute the prepared statement for\n");
printf(" empno = '000240'\n");
printf(" photo_format = 'bitmap'\n");
printf(" fileName = %s\n", fileName);
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** BlobWriteInPieces
******************************************************************************/
int BlobWriteInPieces( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"INSERT INTO emp_photo (empno, photo_format, picture) VALUES (?, ?, ?)" ;
SQLCHAR empno[10], photo_format[10];
SQLCHAR inputParam[] = "Photo Data";
SQLINTEGER blobInd ;
SQLCHAR fileNameBase[] = "photo1";
SQLCHAR fileName[14] = "";
FILE *pFile;
SQLCHAR buffer[BUFSIZ];
size_t n = 0;
size_t fileSize = 0;
SQLPOINTER valuePtr;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameter\n");
printf("-SQLExecute\n-SQLCancel\n");
printf("-SQLParamData\n-SQLPutData\n");
printf("-SQLEndTran\n-SQLFreeHandle\n");
printf("TO WRITE BLOB DATA IN PIECES:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled.\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf("\n Prepare the statement\n");
printf(" INSERT INTO emp_photo (empno, photo_format, picture)\n");
printf(" VALUES (?, ?, ?)\n");
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind parameters to the statement */
printf(" Bind parameters to the statement\n");
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 10, 0, empno, 10, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 10, 0, photo_format, 10, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/*
* This paramter will use SQLPutData
*/
blobInd = SQL_DATA_AT_EXEC;
sqlrc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BLOB,
BUFSIZ, 0, (SQLPOINTER)inputParam, BUFSIZ, &blobInd);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ----------------write data from a *.bmp file ------------------------*/
/* execute the statement */
strcpy( (char*)empno, "000240");
strcpy( (char*)photo_format, "bitmap");
sprintf( (char*)fileName, "%s.bmp", fileNameBase);
printf(" Execute the prepared statement for\n");
printf(" empno = '000240'\n");
printf(" photo_format = 'bitmap'\n");
printf(" fileName = %s\n", fileName);
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
if ( sqlrc == SQL_NEED_DATA )
{ pFile = fopen((char *)fileName, "rb");
if ( pFile == NULL)
{ printf(">---- ERROR Opening File -------");
/* cancel the DATA AT EXEC state for hstmt */
sqlrc = SQLCancel(hstmt);
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
else
{ sqlrc = SQLParamData( hstmt, (SQLPOINTER *) &valuePtr);
STMT_HANDLE_CHECK( hstmt, sqlrc);
while ( sqlrc == SQL_NEED_DATA)
{ /*
if more than 1 parms used DATA_AT_EXEC then valuePtr would
have to be checked to determine which param needed data
*/
while ( feof( pFile ) == 0 )
{ n = fread( buffer, sizeof(char), BUFSIZ, pFile);
sqlrc = SQLPutData(hstmt, buffer, n);
STMT_HANDLE_CHECK( hstmt, sqlrc);
fileSize = fileSize + n;
if ( fileSize > 102400u)
{ /* BLOB column defined as 100K MAX */
printf(">---- ERROR: File > 100K -------");
break;
}
}
printf(" Written a total of %u bytes from %s\n",
fileSize, fileName);
sqlrc = SQLParamData( hstmt, (SQLPOINTER *) &valuePtr);
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
}
}
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** ClobReadASelectedPiece
******************************************************************************/
int ClobReadASelectedPiece( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmtClobFetch, hstmtLocUse, hstmtLocFree ;
SQLCHAR * stmtClobSelect =
( SQLCHAR * ) "SELECT resume FROM emp_resume "
"WHERE empno = '000140' AND resume_format = 'ascii'" ;
SQLCHAR * stmtLocFree = ( SQLCHAR * ) "FREE LOCATOR ?" ;
SQLINTEGER clobLoc ; /* A LOB locator used for resume */
SQLINTEGER pcbValue ;
SQLINTEGER clobPieceLen, clobLen ;
SQLUINTEGER clobPiecePos ;
SQLINTEGER ind ;
SQLCHAR * buffer ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLBindCol\n");
printf("-SQLFetch\n-SQLGetLength\n");
printf("-SQLGetPosition\n-SQLGetSubString\n");
printf("-SQLSetParam\n-SQLFreeHandle\n");
printf("TO READ A SELECTED PIECE OF CLOB DATA:\n");
printf( "\nSet the value of UDF Shared Memory Set Size (udf_mem_sz)\n" ) ;
printf( " at least 2 pages larger than the size of the input\n" ) ;
printf( " arguments and the result of the external function.\n\n" ) ;
printf( " (i.e. db2 UPDATE DBM CFG USING udf_mem_sz 1024 )\n\n") ;
printf("The server must then be stopped and re-started for the\n") ;
printf(" change to take effect.\n\n") ;
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* --------------- fetch CLOB data --------------------------------------*/
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtClobFetch ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the statement */
printf("\n Execute directly the statement\n");
printf(" SELECT resume FROM emp_resume\n");
printf(" WHERE empno = '000140' AND resume_format = 'ascii'\n");
sqlrc = SQLExecDirect( hstmtClobFetch, stmtClobSelect, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtClobFetch, sqlrc);
/* bind CLOB column to LOB locator */
sqlrc = SQLBindCol( hstmtClobFetch, 1, SQL_C_CLOB_LOCATOR,
&clobLoc, 0, &pcbValue);
STMT_HANDLE_CHECK( hstmtClobFetch, sqlrc);
/* fetch the CLOB data */
printf(" Fetch the CLOB data( resume).\n");
sqlrc = SQLFetch( hstmtClobFetch );
STMT_HANDLE_CHECK( hstmtClobFetch, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
/* ---------------- work with the LOB locator -----------------------------*/
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtLocUse ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf(" Extract the piece of CLOB data.\n");
/* get the length of the whole CLOB data */
sqlrc = SQLGetLength( hstmtLocUse, SQL_C_CLOB_LOCATOR,
clobLoc, &clobLen, &ind ) ;
STMT_HANDLE_CHECK( hstmtLocUse, sqlrc);
/* get the starting postion of the CLOB piece of data */
sqlrc = SQLGetPosition( hstmtLocUse,
SQL_C_CLOB_LOCATOR,
clobLoc,
0,
( SQLCHAR * ) "Interests",
strlen( "Interests"),
1,
&clobPiecePos,
&ind ) ;
STMT_HANDLE_CHECK( hstmtLocUse, sqlrc);
/* allocate a buufer to read the piece of CLOB data */
buffer = ( SQLCHAR * ) malloc( clobLen - clobPiecePos + 1 ) ;
/* read the piece of CLOB data in buffer */
sqlrc = SQLGetSubString( hstmtLocUse,
SQL_C_CLOB_LOCATOR,
clobLoc,
clobPiecePos,
clobLen - clobPiecePos,
SQL_C_CHAR,
buffer,
clobLen - clobPiecePos + 1,
&clobPieceLen,
&ind ) ;
STMT_HANDLE_CHECK( hstmtLocUse, sqlrc);
/* print the buffer */
printf(" Print the piece of CLOB data.\n");
printf( "\n%s\n", buffer ) ;
free( buffer);
/* ---------------- free the LOB locator ----------------------------------*/
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmtLocFree ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
sqlrc = SQLSetParam( hstmtLocFree,
1,
SQL_C_CLOB_LOCATOR,
SQL_CLOB_LOCATOR,
0,
0,
&clobLoc,
NULL ) ;
STMT_HANDLE_CHECK( hstmtLocFree, sqlrc);
/* execute directly the statement */
printf("\n Free the LOB locator.\n");
sqlrc = SQLExecDirect( hstmtLocFree, stmtLocFree, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmtLocFree, sqlrc);
/* ------------------ free the statement handles ---------------------------*/
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtClobFetch ) ;
STMT_HANDLE_CHECK( hstmtClobFetch, sqlrc);
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtLocUse ) ;
STMT_HANDLE_CHECK( hstmtLocUse, sqlrc);
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmtLocFree ) ;
STMT_HANDLE_CHECK( hstmtLocFree, sqlrc);
return(rc);
}