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