/**********************************************************************
* 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;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.