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