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

See Also


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