Site navigation
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
/**********************************************************************
* FILENAME : WritingMultipleLongTextFields.c
*
* DESCRIPTION :
* Example mainly illustrates use of SQLPutData () to insert record
* with 'text' fields uses either TestTBL3 (one text field) or
* TestTBL4 (two text fields) in chunks which are requested at
* execute time thus illustrating DAE and how long text fields
* are handled.
*
* Can also be used to insert records into two other tables used
* for testing, TestTBL1 and TestTBL2, which do not have text fields.
*
* ODBC USAGE :
*
* Asks user to select table (1, 2 3 or 4)
* 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 PERSONID_LEN 2
#define LASTNAME_LEN 255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN 255
SQLLEN cPersonId;
SQLCHAR strFirstName[FIRSTNAME_LEN];
SQLCHAR strLastName[LASTNAME_LEN];
SQLCHAR strAddress[ADDRESS_LEN];
SQLCHAR strCity[CITY_LEN];
SQLLEN lenFirstName=0, lenLastName=0, lenAddress=0, lenCity=0;
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
// Tables
// TestTBL1 has identity field 1 PersonID
// TestTBL2 is same as TestTBL1 but without the identity field
// TestTBL3 has one text field Memo1 in column 4
// TestTBL4 is same as TestTBL3 plus another text field, Memo2 in columns 5
char sqlStatement[4][256]= {
{"INSERT INTO TestTBL1 VALUES (?,?,?,?)"},
{"INSERT INTO TestTBL2 VALUES (?,?,?,?)"},
{"INSERT INTO TestTBL3 VALUES ('A N Other',55,'1958-12-25 00:00:00',?)"},
{"INSERT INTO TestTBL4 VALUES ('A N Other',55,'1958-12-25 00:00:00',?,?)"}
};
int tableNo, numRecs;
char confirm='N';
char reply=' ';
// SQLBindParameter variables (for TestTBL3 and TestTBL4).
SQLLEN lenMemo1TextSize, lMemo1Bytes;
SQLLEN lenMemo2TextSize, lMemo2Bytes;
PTR pParamID;
SQLSMALLINT NumParams, i, DataType, DecimalDigits, Nullable, paramNo;
SQLULEN bytesRemaining;
// SQLPutData text data for Memo1 (for TestTBL3 and TestTBL4).
UCHAR Memo1Data[] =
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
"abcdefghijklmnopqrstuvwxyz";
SDWORD lenMemo1Batch = (SDWORD)sizeof(Memo1Data) - 1;
// SQLPutData text data for Memo1 (for TestTBL4).
UCHAR Memo2Data[] =
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
"ABCDEFGHIJKLMNOPQRSTUVWXYZ";
SDWORD lenMemo2Batch = (SDWORD)sizeof(Memo2Data) - 1;
// used for each text parameter needing data
SQLLEN lBytes;
SDWORD lenBatch;
UCHAR *Data;
char strRecs[32];
// get table for inserts ?
reply=getInt ("Which Table"
"\n0 (Quit)"
"\n1 (TestTBL1 ID)"
"\n2 (TestTBL2 NoID)"
"\n3 (TestTBL3 Memo1)"
"\n4 (TestTBL4 Memo1, Memo2)"
"\n ?", &tableNo, confirm, 0);
// or quit
if (tableNo==0) goto exit;
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
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);
printf ("Preparing : %s\n", &sqlStatement[tableNo-1][0]);
retcode = SQLPrepare(hstmt, &sqlStatement[tableNo-1][0], SQL_NTS);
CHECK_ERROR(retcode, "SQLPrepare(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
printf("SQLPrepare OK\n");
retcode = SQLNumParams(hstmt, &NumParams);
CHECK_ERROR(retcode, "SQLNumParams()", hstmt, SQL_HANDLE_STMT);
printf("SQLNumParams(hstmt, &NumParams) OK\n");
// Expecting DAE (Data at Execution) for :
// if table 1 or 2 - 4 parameters - FirstName, LastName, Address, City
// if table 3 - 1 parameter - for Memo1
// if table 4 - 2 parameters - Memo1 and Memo2
printf ("Num Params : %i\n", NumParams);
if (NumParams) {
switch (tableNo)
{
case 1: // TestTBL1 - should be asking for 4 params
case 2: // TestTBL2 - should be asking for 4 params
// Get Records to Add
memset (strRecs, ' ', sizeof(strRecs));
reply=getStr ("Number of Recs", strRecs, sizeof(strRecs), 'N');
numRecs=atoi(strRecs);
printf ("Writing %i records\n", numRecs);
// Bind Parameters to all fields
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, FIRSTNAME_LEN,
0, strFirstName, FIRSTNAME_LEN,
&lenFirstName);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, LASTNAME_LEN,
0, strLastName, LASTNAME_LEN,
&lenLastName);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, ADDRESS_LEN,
0, strAddress, ADDRESS_LEN,
&lenAddress);
retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, CITY_LEN,
0, strCity, CITY_LEN, &lenCity);
for (i=0;i<numRecs;i++) {
// Setup data and lengths
memset (strFirstName, ' ', FIRSTNAME_LEN);
memset (strLastName, ' ', LASTNAME_LEN);
memset (strAddress, ' ', ADDRESS_LEN);
memset (strCity, ' ', CITY_LEN);
// Get data from user
getStr ("First Name", strFirstName, FIRSTNAME_LEN, 'N');
getStr ("Last Name", strLastName, LASTNAME_LEN, 'N');
getStr ("Address ", strAddress, ADDRESS_LEN, 'N');
getStr ("City ", strCity, CITY_LEN, 'N');
// Remove Nulls
strFirstName[strlen(strFirstName)]=' ';
strLastName[strlen(strLastName)]=' ';
strAddress[strlen(strAddress)]=' ';
strCity[strlen(strCity)]=' ';
// Set sizes
lenFirstName=sizeof(strFirstName);
lenLastName=sizeof(strLastName);
lenAddress=sizeof(strAddress);
lenCity=sizeof(strCity);
// Execute statement
retcode = SQLExecute(hstmt);
if (retcode == SQL_SUCCESS ||
retcode == SQL_SUCCESS_WITH_INFO) {
printf ("Status : ok\n");
} else {
printf ("Status : Error %i\n", retcode);
}
}
break;
case 3: // TestTBL3 - has text field Memo1
case 4: // TestTBL4 - has text fields Memo1, Memo2
// Get Records to Add
memset (strRecs, ' ', sizeof(strRecs));
reply=getStr ("Number of Recs", strRecs, sizeof(strRecs), 'N');
numRecs=atoi(strRecs);
for (i = 0; i < NumParams; i++) {
// Describe the parameter.
retcode = SQLDescribeParam(hstmt, i+1,
&DataType,
&bytesRemaining,
&DecimalDigits,
&Nullable);
CHECK_ERROR(retcode, "SQLDescribeParam()",
hstmt, SQL_HANDLE_STMT);
printf("\nSQLDescribeParam() OK\n");
printf("Data Type : %i, bytesRemaining : %i, DecimalDigits : %i, Nullable %i\n",
(int)DataType, (int)bytesRemaining,
(int)DecimalDigits, (int)Nullable);
// Assume first param is for Memo1 text field
if (i==0) {
// Total number of bytes to write overall
lMemo1Bytes=(SDWORD) MEMO1TEXTSIZE;
// Chunk size to be written each time
lenMemo1TextSize=SQL_LEN_DATA_AT_EXEC(lMemo1Bytes);
printf ("Binding MEMO field 1 - ");
printf ("Bytes Remaining : %i, ", (int)lMemo1Bytes);
printf ("LenOrIndArray : %i\n", (int)lenMemo1TextSize);
// For DATA_AT_EXEC parameter, whatever is passed in
// parameter 8, is passed back in the second parameter
// of SQLParamData () and used to find the field for
// which more data is being requested.
retcode = SQLBindParameter(
hstmt, // Statment Handle
i + 1, // Parameter Number
SQL_PARAM_INPUT, // Type is INPUT
SQL_C_CHAR, // C Type
SQL_LONGVARCHAR, // SQL Type
lMemo1Bytes, // Overall size
0, // Decimal Digits
Memo1Data, // Param value Pointer
0, // Buffer Length
&lenMemo1TextSize); // Chunk size
CHECK_ERROR(retcode,
"SQLBindParameter(3 SQL_PARAM_INPUT)",
hstmt, SQL_HANDLE_STMT);
}
// Assume second param is for Memo2 text field
if (i==1) {
// Total number of bytes to write overall
lMemo2Bytes=(SDWORD) MEMO2TEXTSIZE;
// Chunk size to be written each time
lenMemo2TextSize=SQL_LEN_DATA_AT_EXEC(lMemo2Bytes);
printf ("Binding MEMO field 2 - ");
printf ("Bytes Remaining : %i, ", (int)lMemo2Bytes);
printf ("LenOrIndArray : %i\n", (int)lenMemo2TextSize);
// For DATA_AT_EXEC parameter, whatever is passed in
// parameter 8, is passed back in the second parameter
// of SQLParamData () and used to find the field for
// which more data is being requested.
retcode = SQLBindParameter(
hstmt, // Statment Handle
i + 1, // Parameter Number
SQL_PARAM_INPUT, // Type is INPUT
SQL_C_CHAR, // C Type
SQL_LONGVARCHAR, // SQL Type
lMemo2Bytes, // Overall size
0, // Decimal Digits
Memo2Data, // Param value Pointer
0, // Buffer Length
&lenMemo2TextSize); // Chunk size
CHECK_ERROR(retcode,
"SQLBindParameter(4 SQL_PARAM_INPUT)",
hstmt, SQL_HANDLE_STMT);
}
}
// write 'numrecs' records to selected table
for (i=0;i<numRecs;i++) {
// Execute the command.
printf ("\nCall SQLExecute\n");
retcode = SQLExecute(hstmt);
if ( (retcode != SQL_SUCCESS) &&
(retcode != SQL_NEED_DATA) &&
(retcode != SQL_SUCCESS_WITH_INFO) ) {
CHECK_ERROR(retcode, "SQLExecute()",
hstmt, SQL_HANDLE_STMT);
}
// we would expect SQLExecute to return SQL_NEED_DATA for
// the DATA AT EXEC fields set up for either Memo1 or Memo2
if (retcode==SQL_NEED_DATA) {
// Find the parameter/column for which more data is need
// SQLParamData returns (in pParamID) the value of
// whatever was given in param 8 passed in the
// SQLBindParameter call earlier, which in this example
// is the address of the buffer for that parameter.
printf ("First Call - SQLParamData\n");
retcode = SQLParamData(hstmt, &pParamID);
// parameter is Memo1
if (pParamID==Memo1Data) {
printf ("Need data for Memo1\n");
lBytes=lMemo1Bytes;
lenBatch=lenMemo1Batch;
Data=Memo1Data;
}
// parameter is Memo2
if (pParamID==Memo2Data) {
printf ("Need data for Memo2\n");
lBytes=lMemo2Bytes;
lenBatch=lenMemo2Batch;
Data=Memo2Data;
}
}
// Loop round while asking for more data
while (retcode == SQL_NEED_DATA) {
printf ("Param ID : %p\n", pParamID);
while (lBytes > lenBatch) {
printf ("SQL_NEED_DATA Call SQLPutData : lBytes %i, lenBatch %i\n",
(int)lBytes, (int)lenBatch);
SQLPutData(hstmt, Data, lenBatch);
lBytes -= lenBatch;
}
// Put final chunk.
printf ("Final Call - SQLPutData : lMemo1Bytes %i\n",
(int)lBytes);
retcode = SQLPutData(hstmt, Data, lBytes);
CHECK_ERROR(retcode, "SQLPutData()",
hstmt, SQL_HANDLE_STMT);
// Make final SQLParamData call for this paramater.
printf ("Final Call - SQLParamData\n");
retcode = SQLParamData(hstmt, &pParamID);
if ( (retcode != SQL_SUCCESS) &&
(retcode != SQL_SUCCESS_WITH_INFO) &&
(retcode != SQL_NEED_DATA) ) {
CHECK_ERROR(retcode, "SQLPutData()",
hstmt, SQL_HANDLE_STMT);
} else {
// if the last call to SQLParamData returns
// SQL_NEED_DATA again then another parameter
// needs more data. (NOTE: the parameter
// ordering is not necessarily guaranteed so
// we check for which one more data is needed
// for).
if (retcode == SQL_NEED_DATA) {
if (pParamID==Memo1Data) {
printf ("Need data for Memo1\n");
lBytes=lMemo1Bytes;
lenBatch=lenMemo1Batch;
Data=Memo1Data;
}
if (pParamID==Memo2Data) {
printf ("Need data for Memo2\n");
lBytes=lMemo2Bytes;
lenBatch=lenMemo2Batch;
Data=Memo2Data;
}
}
}
}
}
break;
}
} else {
printf ("No Params\n");
}
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;
}
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.