From Ingres Community Wiki
/*
** File: test_blob.c
**
** This program assumes you have an ODBC DSN configured to make a connection
** to the database. The DSN name is provided as a command-line argument:
** test_blob myDSN
**
** ...connects you to the database defined by the ODBC DSN name "myDSN".
**
** The program reads a file called "tt.txt", which can be any
** ASCII file. Test_blob reads the file, creates a table called
** test_ingres_longv, and inserts the contents of the file into the
** long varchar column in test_ingres_longv. The blob is then
** fetched and the results are displayed on the screen.
**
** The size of the file should be no longer than MAX_BUFFER_SIZE.
*/
# ifdef _WIN32
# include <windows.h>
# endif
# include <stdio.h>
# include <sql.h>
# include <sqlext.h>
void check_error( SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char *);
void print_error( SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char *);
#define CHECK_HANDLE( htype, hndl, rc ) if ( rc != SQL_SUCCESS ) {check_error (htype,hndl,rc,__LINE__,__FILE__);exit(0); }
#define MAX_BUFFER_SIZE 200000
char BlobBuffer[MAX_BUFFER_SIZE];
FILE *blob;
int main(int argc, char **argv)
{
RETCODE rc,ret;
HENV henv=NULL;
HDBC hdbc=NULL;
HSTMT hstmt = SQL_NULL_HSTMT;
SQLINTEGER variableLen=0, Strlen_or_Ind=SQL_NTS,orind=0;
int i=0;
char *z = malloc(5000000), *pz = &z[0];
SQLPOINTER pToken;
SWORD cbLen;
int iChunkSize;
int blobhandle;
long iBlobLen;
int iBuffSz = 2000;
char *szBlobName;
SQLINTEGER rowCount=-1;
if (argc != 2)
{
printf("Usage: test dsn\n");
exit(0);
}
szBlobName = "tt.txt";
rc = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);\
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );
rc = SQLConnect(hdbc, argv[1], SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
printf("set autocommit off\n");
rc = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
rc = SQLAllocStmt(hdbc,&hstmt);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
rc = SQLExecDirect(hstmt,
"create table test_ingres_longv ( x long varchar)", SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
if((blob = fopen(szBlobName, "rb")) == NULL )
{
printf("Error opening file %s\n",szBlobName);
exit (0);
}
else
{
blobhandle = _fileno(blob);
iBlobLen = _filelength(blobhandle);
if (ferror(blob))
{
printf("IO error on obtaing file length\n");
exit(0);
}
}
printf("size of the file in bytes: %d\n",iBlobLen);
printf("Binding the Blob\n");
rc = SQLBindParameter(hstmt,1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_LONGVARCHAR, 0, 0, (SQLPOINTER)1, 0, &variableLen);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
if (iBlobLen)
variableLen = SQL_LEN_DATA_AT_EXEC(iBlobLen);
else
variableLen = 0;
printf("Performing SQLExecDirect\n");
rc = SQLExecDirect(hstmt,
"insert into test_ingres_longv(x) values (?)", SQL_NTS);
if (rc != SQL_NEED_DATA)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
iChunkSize = 1;
while (rc == SQL_NEED_DATA)
{
if (iChunkSize)
{
rc = SQLParamData(hstmt, &pToken);
printf("ParamData: token is %d with rc %d\n",pToken, rc);
}
else
break;
if (rc == SQL_NEED_DATA)
{
while (!feof(blob))
{
iChunkSize = fread(BlobBuffer,1,iBuffSz,blob);
if (ferror(blob))
{
printf("IO error\n");
exit (-1);
}
if (iChunkSize != 0)
{
printf("putting %d bytes\n",iChunkSize);
ret = SQLPutData(hstmt,BlobBuffer,iChunkSize);
}
else
{
rc = SQLParamData(hstmt, &pToken);
printf("ParamData: token is %d with rc %d\n",pToken, rc);
break;
}
}
}
else
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
} // if SQL_NEED_DATA
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLRowCount(hstmt, &rowCount);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("Row count is %d\n", rowCount);
rc = SQLFreeStmt(hstmt, SQL_DROP);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
fclose(blob);
rc = SQLAllocStmt(hdbc,&hstmt);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("Binding %d bytes\n",iBlobLen);
rc = SQLBindCol(hstmt,1, SQL_C_CHAR, z, iBlobLen, &Strlen_or_Ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("Performing SQLExecDirect\n");
rc = SQLExecDirect(hstmt,
"select x from test_ingres_longv", SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
while (1)
{
printf("Calling SQLFetch\n");
rc = SQLFetch(hstmt);
if (rc == SQL_NO_DATA)
{
printf("EOD\n");
break;
}
if (rc == SQL_ERROR)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
while (1)
{
Strlen_or_Ind = SQL_NTS;
printf("Doing SQLGetData for column x\n");
ret = SQLGetData(hstmt,1,SQL_C_CHAR, pz, iBuffSz,
&Strlen_or_Ind);
printf("returned len is %d\n",Strlen_or_Ind);
if (ret == SQL_NO_DATA || ret == SQL_SUCCESS)
break;
else if (ret != SQL_ERROR)
pz += (iBuffSz - 1);
else
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, ret );
}
}
printf("Result of fetch:\n%s\n\n---DONE fetching---\n",z);
rc = SQLFreeStmt(hstmt, SQL_DROP);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("rollback\n");
rc = SQLTransact(SQL_NULL_HENV, hdbc,SQL_ROLLBACK);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
printf("disconnecting\n");
rc = SQLDisconnect(hdbc);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc );
rc = SQLFreeConnect(hdbc);
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
rc = SQLFreeEnv(henv);
CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc );
return(0);
}
void check_error( SQLSMALLINT htype, SQLHANDLE hndl,
SQLRETURN frc, int line, char * file )
{
SQLCHAR cli_sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER cli_sqlcode;
SQLSMALLINT lenngth;
switch (frc)
{
case SQL_SUCCESS:
break;
case SQL_INVALID_HANDLE:
printf("check_error> SQL_INVALID HANDLE \n");
break;
case SQL_ERROR:
printf("check_error> SQL_ERROR\n");
break;
case SQL_SUCCESS_WITH_INFO:
printf("check_error> SQL_SUCCESS_WITH_INFO\n");
break;
case SQL_NO_DATA_FOUND:
printf("check_error> SQL_NO_DATA_FOUND\n");
break;
default:
printf("check_error> Received rc from api rc=%i\n",frc);
break;
} /*end switch*/
print_error(htype,hndl,frc,line,file);
} /* end check_error*/
/******************************************************************/
/* print_error */
/* calls SQLGetDiagRec()displays SQLSTATE and message */
/******************************************************************/ void print_error( SQLSMALLINT htype, SQLHANDLE hndl, SQLRETURN frc,
int line, char * file)
{
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1] ;
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1] ;
SQLINTEGER sqlcode ;
SQLSMALLINT length, i ;
SQLRETURN prc;
printf("return code = %d reported from file: %s, line: %d\n",
frc, file, line );
i = 1 ;
while ( SQLGetDiagRec( htype, hndl, i, sqlstate,
&sqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1,
&length ) == SQL_SUCCESS )
{
printf( "SQLSTATE: %s\n", sqlstate ) ;
printf( "Native Error Code: %ld\n", sqlcode ) ;
printf( "buffer: %s \n", buffer ) ;
i++ ;
}
} /* end print_error */
