/**********************************************************************
* FILENAME : WritingSingleLongTextFields.c
*
DESCRIPTION :
* Example mainly illustrates use of Data at Execution and SQLPutData ()
* to insert a record which has a long 'text' field.
*
*
* ODBC USAGE :
*
* SQLPrepare () - to prepare statement for selected table
* SQLNumParams () - to establish how many parameters are needed
* Asks for how many records are to be written
* For TestTBL1 and TestTBL2
* Uses SQLBindParam () to bind the 4 paramaters
* For each record,
* prompts for data.
* calls SQLExecute () to execute the insert
* For TestTBL3 and TestTBL4
* Uses SQLDescribeParam () to get paramater attributes
* Uses SQLBindParameter () to bind the paramater with
* overall param size and, using SQL_LEN_DATA_AT_EXEC,
* the size of the chunks to be written each time when
* execute asks for more data.
* For each record,
* calls SQLExecute () to execute the insert
* Checks for SQL_NEED_DATA and calls SQLParamData ()
* to find out which of the text paramaters needs more
* data.
* While SQL_NEED_DATA returned,
* calls SQLPutData () to write data in chunks
* call SQLParamData () which will complete the
* current parameter and return a new param ID
* and SQL_NEED_DATA if any more paramaters need
* more data (e.g. the second Memo paramater).
*
* Use CreateTable to create TestTBL3 and/or TestTBL4
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define MEMO1TEXTSIZE 12000
#define MEMO2TEXTSIZE 12000
#define MAXBUFLEN 256
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
void Cleanup() {
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main () {
RETCODE retcode;
// SQLBindParameter variables.
SQLLEN lenTextSize, lbytes;
// SQLParamData variable.
PTR pParamID;
// SQLPutData text data.
UCHAR Memo1Data[] =
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyz";
SDWORD lenBatch = (SDWORD)sizeof(Memo1Data) - 1;
// Create table ?
char table[256];
char confirm='N';
char reply=' ';
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_STMT)", henv, SQL_HANDLE_ENV);
// Notify ODBC that this is an ODBC 3.0 app.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr (SQL_ATTR_ODBC_VERSION)", henv, SQL_HANDLE_ENV);
// Allocate ODBC connection handle and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_DBC)", hdbc, SQL_HANDLE_DBC);
retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
CHECK_ERROR(retcode, "SQLDriverConnect (DATASOURCE)", hdbc, SQL_HANDLE_DBC);
// Allocate statement handle.
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_STMT)", hstmt, SQL_HANDLE_STMT);
// Set parameters based on total data to send.
lbytes = (SDWORD)MEMO1TEXTSIZE; //12000
lenTextSize = SQL_LEN_DATA_AT_EXEC(lbytes);
printf ("lbytes %i\n", (int)lbytes);
printf ("lenTextSize %i\n", (int)lenTextSize);
// Bind the parameter marker.
retcode = SQLBindParameter (hstmt, // hstmt
1, // Parameter Number
SQL_PARAM_INPUT, // Input/Output Type
SQL_C_CHAR, // Value Type
SQL_LONGVARCHAR, // Parameter Type
lbytes, // Column Size
0, // Decimal Digits
(VOID *)1, // Parameter Value Pointer
0, // Buffer Length
&lenTextSize); // Len or Indicator Pointer
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_STMT)", hstmt, SQL_HANDLE_STMT);
// Execute the command.
printf ("Call SQLExecDirect\n");
retcode = SQLExecDirect(hstmt, (UCHAR*)"INSERT INTO TestTBL3 VALUES('Buzz Aldrin', 56,'1957-12-30 00:00:00', ?)", SQL_NTS);
if (retcode != SQL_NEED_DATA) {
CHECK_ERROR(retcode, "SQLExecDirect (INSERT)", hstmt, SQL_HANDLE_STMT);
}
// Check to see if NEED_DATA; if yes, use SQLPutData.
printf ("First Call - SQLParamData\n");
retcode = SQLParamData(hstmt, &pParamID);
if (retcode == SQL_NEED_DATA) {
while (lbytes > lenBatch) {
printf ("SQL_NEED_DATA Call - SQLPutData : lbytes %i, lenBatch %i\n", (int)lbytes, (int)lenBatch);
SQLPutData(hstmt, Memo1Data, lenBatch);
lbytes -= lenBatch;
}
// Put final batch.
printf ("Final Call - SQLPutData : lbytes %i\n", (int)lbytes);
retcode = SQLPutData(hstmt, Memo1Data, lbytes);
CHECK_ERROR(retcode, "SQLPutData (SQL_HANDLE_STMT)", hstmt, SQL_HANDLE_STMT);
// Make final SQLParamData call.
printf ("Final Call - SQLParamData\n");
retcode = SQLParamData(hstmt, &pParamID);
CHECK_ERROR(retcode, "SQLParamData (SQL_HANDLE_STMT)", hstmt, SQL_HANDLE_STMT);
}
exit:
printf ("\nComplete.\n");
// Free handles
// Statement
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
// Connection
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// Environment
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
Further information
