/**********************************************************************
* FILENAME :        ReadingMultipleLongTextFields.c
*
* DESCRIPTION :
*		This example retrieves data fram a choice of tables but shows
*               how to handle more than one long text fields in the record (i.e.
*               in the case of TestTBL4 which has LONGVARCHAR fields Memo1 and
*               Memo2).
*
* 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 all but long text fields
*		SQLGetData to return text data on each long text field
*               until SQL_NO_DATA
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"

#define NAMELEN 30
#define BIRTHDAYLEN 256
#define M1DATASIZE  520+1 // How much of the Memo1 to read in one go
#define M2DATASIZE  520+1 // How much of the Memo2 to read in one go

#define PERSONID_LEN  2
#define LASTNAME_LEN  255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN  255

#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;

    SQLINTEGER colNo;

    SQLUINTEGER PersonID;
    SQLCHAR FirstName[LASTNAME_LEN];
    SQLCHAR LastName[FIRSTNAME_LEN];
    SQLCHAR Address[ADDRESS_LEN];
    SQLCHAR City[CITY_LEN];

    SQLLEN siPersonID, siFirstName, siLastName, siAddress, siCity;

    SQLCHAR	name[NAMELEN];
    SQLINTEGER age;
    SQLCHAR birthday[BIRTHDAYLEN];
    SQLCHAR Memo1[M1DATASIZE];
    SQLCHAR Memo2[M2DATASIZE];

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

    char sqlStatement[5][256]= {
    {"SELECT PersonID, FirstName, LastName, Address, City FROM TestTBL1"},
    {"SELECT PersonID, FirstName, LastName, Address, City FROM TestTBL1Copy"},
    {"SELECT FirstName, LastName, Address, City FROM TestTBL2"},
    {"SELECT NAME, AGE, BIRTHDAY, Memo1 FROM TestTBL3"},
    {"SELECT NAME, AGE, BIRTHDAY, Memo1, Memo2 FROM TestTBL4"}
	};

    int tableNo=-1;

    // Loop round tables until 0 (quit) entered
	while (tableNo != 0) {
		getInt ("\nWhich Table"
                "\n0 (Quit)"
                "\n1 (TestTBL1 ID)"
                "\n2 (TestTBL1Copy ID)"
                "\n3 (TestTBL2 NoID)"
                "\n4 (TestTBL3 Memo1)"
                "\n5 (TestTBL4 Memo1, Memo2)"
                "\n  ? ", &tableNo, 'N', 0);
		if (tableNo==0 ||
            (tableNo!=1 && tableNo!=2 && tableNo!=3 && tableNo!=4))
			goto exit;

	    printf ("\n");

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

            // Connect to the DSN
	    retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;",
                            SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
	    CHECK_ERROR(retcode, "SQLDriverConnect(DSN=DATASOURCE;)",
                    hdbc, SQL_HANDLE_DBC);

	    // Allocate statement handle.
	    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
                    hdbc, SQL_HANDLE_STMT);

	    // Execute the command.
	    retcode = SQLExecDirect(hstmt, &sqlStatement[tableNo-1][0], SQL_NTS);
	    CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);

            // Call SQLFetch until SQL_NO_DATA (or error) is returned
		while (TRUE) {
			retcode = SQLFetch(hstmt);
			if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
				extract_error("SQLFetch(hstmt)", hstmt, SQL_HANDLE_STMT);
	            goto exit;
			}

	        if (retcode == SQL_NO_DATA) {
	            break;
	        }

	        if (retcode == SQL_SUCCESS){
	            switch (tableNo)
	            {
	                case 1:        // TestTBL1 Has PersonID identity column
	                case 2:        // TestTBL2 Has no PersonID identity Column
	            		memset (FirstName, ' ', FIRSTNAME_LEN);
	            		memset (LastName, ' ', LASTNAME_LEN);
	            		memset (Address, ' ', ADDRESS_LEN);
	            		memset (City, ' ', CITY_LEN);
						colNo=1;

						// If table 1, 1st field is PersonID identity field
						if (tableNo==1) {
		            		retcode = SQLGetData(hstmt, colNo++, SQL_C_ULONG,
                                                    &PersonID, 0, &siPersonID);
						}

						// Common fields in both Table1 and Table2
						retcode = SQLGetData(hstmt, colNo++, SQL_C_CHAR,
                                    FirstName, FIRSTNAME_LEN, &siFirstName);
	             		retcode = SQLGetData(hstmt, colNo++, SQL_C_CHAR,
                                    LastName, LASTNAME_LEN, &siLastName);
	            		retcode = SQLGetData(hstmt, colNo++, SQL_C_CHAR,
                                    Address, ADDRESS_LEN, &siAddress);
	            		retcode = SQLGetData(hstmt, colNo++, SQL_C_CHAR,
                                    City, CITY_LEN, &siCity);

	                    // Print the record
	                    if (tableNo==1) {
	                        printf("%i, %.10s, %.10s, %.10s, %.10s\n",
                                        PersonID, FirstName, LastName, Address, City);
	                    } else {
	                        printf("%.10s, %.10s, %.10s, %.10s\n",
                                                  FirstName, LastName, Address, City);
	                    }
	                    break;

	                case 3:     // TestTBL3 has 1 text field Memo1
	                case 4:     // TestTBL4 has 2 text fields Memo1 and Memo2
	            		memset (name, ' ', NAMELEN);
	            		memset (birthday, ' ', BIRTHDAYLEN);

						// Common fields in both Table3 and Table4
	            		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);

						// Tables 3 and 4 have text field Memo1 in column 4.
                        // Read until SQL_NO_DATA ...
	            		do {
	            			memset (Memo1, ' ', sizeof (Memo1));
	            			retcode = SQLGetData(hstmt, 4, SQL_CHAR, Memo1,
                                                            M1DATASIZE, &siMemo1);
	            		} while (retcode == SQL_SUCCESS_WITH_INFO &&
                                    SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 1, 4,
                                                    &status,
                                                    SQL_INTEGER, &statuslen)
                                    != SQL_NO_DATA);

						// Table4 has second text field Memo2 in column 5.
                        // Read until SQL_NO_DATA ...
	                    if (tableNo==4) {
	                        do {
	                        	memset (Memo2, ' ', sizeof (Memo2));
	                        	retcode = SQLGetData(hstmt, 5, SQL_CHAR,
                                                     Memo2, M2DATASIZE,
                                                     &siMemo2);
	                        } while (retcode == SQL_SUCCESS_WITH_INFO &&
                                        SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 1,
                                                        5, &status, SQL_INTEGER,
                                                        &statuslen)
                                        != SQL_NO_DATA);
	                    }

	                    // Print the record
	                    if (tableNo==3) {
	                        printf("%.10s, %i, %.10s, %.10s\n",
                                            name, age, birthday, Memo1);
	                    } else {
	                        printf("%.10s, %i, %.10s, %.10s, %.10s\n",
                                            name, age, birthday, Memo1, Memo2);
	                    }
	                    break;
	                default:
	                    break;
	            }
	        }
		}
	}

    printf ("\nComplete.\n");

exit:

	if (hstmt!=SQL_NULL_HSTMT) {
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		hstmt = SQL_NULL_HSTMT;
	}

    if (hdbc!=SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
		hdbc = SQL_NULL_HDBC;
    }

    if (henv!=SQL_NULL_HENV) {
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		hstmt = SQL_NULL_HSTMT;
    }

    return 0;
}

See Also


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