/**********************************************************************
* FILENAME :        ReadingSingleLongTextFields .c
*
* DESCRIPTION :
*		This example retrieves data fram a choice of tables but shows
*               how to read a long text fields (i.e. in the case
*               of TestTBL3 and TestTBL4 which both have a LONGVARCHAR field
*               Memo1).
*
* ODBC USAGE :
*		Prompts for table number to get table name
*               Formats select based on table columns
*		SQLExecDirect to execute SELECT statement
*		SQLFetch to get the next rowset
*		SQLGetData to retrieve the data for non long text fields
*		SQLGetData to return text data for long text field Memo1
*               until SQL_NO_DATA
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"

#define TEXTSIZE  12000 // How big the Memo1 field is
#define NAMELEN 30
#define BIRTHDAYLEN 256
#define DATASIZE  520+1 // How much of the Memo1 field to read in one go

#define TRUE 1

int main () {

    SQLHENV  henv  = SQL_NULL_HENV;   // Environment
    SQLHDBC  hdbc  = SQL_NULL_HDBC;   // Connection handle
    SQLHSTMT hstmt = SQL_NULL_HSTMT;  // Statement handle

    SQLRETURN retcode;

    SQLCHAR	name[NAMELEN];
    SQLINTEGER age;
    SQLCHAR birthday[BIRTHDAYLEN];
    SQLCHAR Memo1[DATASIZE];

    SQLLEN  siname, siage, sibirthday, siMemo1;
    SQLLEN	status;
    SQLSMALLINT statuslen;

    // Table and SQL statement
    int tableNo=-1;
    char table[32];
    char sqlstr[100];

    getInt ("Which Table"
             "\n0 (Quit)"
             "\n1 (TestTBL3)"
             "\n2 (TestTBL4)"
             "\n  ?", &tableNo, 'N', 0);
    if (tableNo != 1 && tableNo != 2) {
    	goto exit;
    } else {
    	if (tableNo==1) strcpy (table, "TestTBL3");
    	if (tableNo==2) strcpy (table, "TestTBL4");
    }

    sprintf (sqlstr,
        "SELECT name, age, birthday, Memo1 from %s order by name", table);

    // 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);

    // Execute the command.
    printf ("Call SQLExecDirect with : %s \n", sqlstr);
    retcode = SQLExecDirect(hstmt, (UCHAR*)sqlstr, SQL_NTS);
    CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);

    while (TRUE) {
    	retcode = SQLFetch(hstmt);
    	if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
    		printf ("Status = %i\n", retcode);
    	}
    	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){

    		/* Get data for columns 1, 2, and 3 */
    		memset (name, ' ', NAMELEN);
    		//age=0;
    		memset (birthday, ' ', BIRTHDAYLEN);
    		retcode = SQLGetData(hstmt, 1,
                                 SQL_C_CHAR, name, NAMELEN,
                                 &siname);
    		retcode = SQLGetData(hstmt, 2,
                                 SQL_C_ULONG, &age, 0,
                                 &siage);
    		retcode = SQLGetData(hstmt, 3,
                                 SQL_C_CHAR, birthday, BIRTHDAYLEN,
                                 &sibirthday);
    		printf("%.10s, %i, %.10s\n", name, age, birthday);
    		// read Memo1 field until SQL_NO_DATA
    		do {
    			memset (Memo1, ' ', sizeof (Memo1));
    			retcode = SQLGetData(hstmt, 4, SQL_CHAR,
                                     Memo1, DATASIZE, &siMemo1);

    			// Print the row of data
                if (siMemo1!=-1) {
    				printf("%.75s\n", Memo1);
    			}
    		} while (retcode == SQL_SUCCESS_WITH_INFO &&
                        SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 1, 4,
                                        &status, SQL_INTEGER,
                                        &statuslen)
                        != SQL_NO_DATA);
    	} else {
    		break;
    	}
    }

exit:

    printf ("\nComplete.\n");

    // Clean up.
    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);

    return 0;
}

See Also


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