22

✏️ Editing: resul.html

<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">
<TITLE>Fetching Data</TITLE>
<LINK REL="StyleSheet" Href="odbc.css">
</HEAD>
<BODY bgcolor="white">
<table width="90%" cols="3" border="0">
<TR>
 <TD colspan="3" class="big">Fetching data from a result set</TD>
</TR>
<TR>
 <TD colspan="3">If the execution of the statement went fine you are now able to fetch
 the data <A Href="gloss.html#col">column</A> by column. May be you would first like to know
 how many columns there are in the result set (if you use a <CODE> SELECT * FROM tkeyuser</CODE>
 you wouldn't know in your program). A call to <a href="gloss.html#ccol"><CODE>SQLNumResultCols</CODE></A>.
 This function takes the statement handle and a pointer to an integer variable which will yield the
 number of columns after the call.
 Knowing that much we can add this to our program:
 <CODE><PRE>
// At the beginning add:
SQLSMALLINT V_OD_colanz; // Num of columns

// At the end add:
 V_OD_erg=SQLNumResultCols(V_OD_hstmt,&V_OD_colanz);
 if ((V_OD_erg != SQL_SUCCESS) && 
 (V_OD_erg != SQL_SUCCESS_WITH_INFO))
 {
 printf("Fehler im ResultCols %d\n",V_OD_erg);
 SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
 SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
 SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
 exit(0);
 }
 printf("Number of Columns %d\n",V_OD_colanz);

</PRE></CODE>
 
 The next thing you need to know is how many <A href="gloss.html#row">rows</A> have
 been returned by the query. A call to <A HREF="gloss.html#crow"><CODE>SQLRowCount</CODE></A> should
 quench your thirst for knowledge. 
 
 
 The last thing to do is to fetch the data itself from the result set. You should call <A HREF="gloss.html#fetc"><CODE>SQLFetch</CODE></A>
 with a statement handle (which has been allocated and <CODE>SQLBind</CODE> has been called for all columns).
 <CODE>SQLFetch</CODE> returns <CODE>SQL_NO_DATA</CODE> if there is no more data in the result set.
 
 So here is the complete source code. Real C-programmers will moan in disgust
 how inefficient the program is coded, but I prefer it that way (doesn't it look a little bit like PASCAL?). 
 Take it as a starting point for your own endeavors with ODBC.
 <CODE class="list"><PRE>
/* odbc.c

testing unixODBC
*/
#include &lt;stdlib.h&gt;
#include &lt;stdio.h&gt;
#include &lt;odbc/sql.h&gt;
#include &lt;odbc/sqlext.h&gt;
#include &lt;odbc/sqltypes.h&gt;

SQLHENV			 V_OD_Env;			// Handle ODBC environment
long			 V_OD_erg;			// result of functions
SQLHDBC			 V_OD_hdbc;			// Handle connection

char			 V_OD_stat[10];		// Status SQL
SQLINTEGER		 V_OD_err,V_OD_rowanz,V_OD_id;
SQLSMALLINT		 V_OD_mlen,V_OD_colanz;
char V_OD_msg[200],V_OD_buffer[200];

int main(int argc,char *argv[])
{
	// 1. allocate Environment handle and register version 
	V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
	{
		printf("Error AllocHandle\n");
		exit(0);
	}
	V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
	{
		printf("Error SetEnv\n");
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
	// 2. allocate connection handle, set timeout
	V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); 
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
	{
		printf("Error AllocHDB %d\n",V_OD_erg);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
	SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);
	// 3. Connect to the datasource "web" 
	V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "web", SQL_NTS,
 (SQLCHAR*) "christa", SQL_NTS,
 (SQLCHAR*) "", SQL_NTS);
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
	{
		printf("Error SQLConnect %d\n",V_OD_erg);
		SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, 
		 V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen);
		printf("%s (%d)\n",V_OD_msg,V_OD_err);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
	printf("Connected !\n");
	V_OD_erg=SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
	{
		printf("Fehler im AllocStatement %d\n",V_OD_erg);
		SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
		printf("%s (%d)\n",V_OD_msg,V_OD_err);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
 SQLBindCol(V_OD_hstmt,1,SQL_C_CHAR, &V_OD_buffer,150,&V_OD_err);
 SQLBindCol(V_OD_hstmt,2,SQL_C_ULONG,&V_OD_id,150,&V_OD_err);
	
 V_OD_erg=SQLExecDirect(V_OD_hstmt,"SELECT dtname,iduser FROM tkeyuser order by iduser",SQL_NTS); 
 if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
 {
 printf("Error in Select %d\n",V_OD_erg);
 SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
 printf("%s (%d)\n",V_OD_msg,V_OD_err);
 SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
 SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
 SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
 exit(0);
 }
 V_OD_erg=SQLNumResultCols(V_OD_hstmt,&V_OD_colanz);
 if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
 {
 SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
 SQLDisconnect(V_OD_hdbc);
 SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
 SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
 exit(0);
 }
 printf("Number of Columns %d\n",V_OD_colanz);
 V_OD_erg=SQLRowCount(V_OD_hstmt,&V_OD_rowanz);
 if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
 {
 printf("Number ofRowCount %d\n",V_OD_erg);
 SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
 SQLDisconnect(V_OD_hdbc);
 SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
 SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
 exit(0);
 }
 printf("Number of Rows %d\n",V_OD_rowanz);
 V_OD_erg=SQLFetch(V_OD_hstmt); 
 while(V_OD_erg != SQL_NO_DATA)
 {
 printf("Result: %d %s\n",V_OD_id,V_OD_buffer);
 V_OD_erg=SQLFetch(V_OD_hstmt); 
 } ;
 SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
 SQLDisconnect(V_OD_hdbc);
 SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
 SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
 return(0);
}
 </PRE>
 </CODE>
 If I find some more time I will add something about diagnostics, cursor positioning updating and and and...

</TD>
</TR>
</TABLE>

</BODY>
</HTML>

MR SKB