1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
<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"><P>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.</P>
Knowing that much we can add this to our program:
<CODE><PRE>
<FONT COLOR="green">// At the beginning add:</FONT>
SQLSMALLINT V_OD_colanz; <FONT COLOR="green">// Num of columns</FONT>
<FONT COLOR="green">// At the end add:</FONT>
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>
<P>
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.
</P>
<P>
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.
</P>
<P>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.</P>
<CODE class="list"><PRE>
/* odbc.c
testing unixODBC
*/
#include <stdlib.h>
#include <stdio.h>
#include <odbc/sql.h>
#include <odbc/sqlext.h>
#include <odbc/sqltypes.h>
SQLHENV V_OD_Env; <FONT COLOR="green">// Handle ODBC environment</FONT>
long V_OD_erg; <FONT COLOR="green">// result of functions</FONT>
SQLHDBC V_OD_hdbc; <FONT COLOR="green">// Handle connection</FONT>
char V_OD_stat[10]; <FONT COLOR="green">// Status SQL</FONT>
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[])
{
<FONT COLOR="green">// 1. allocate Environment handle and register version </FONT>
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);
}
<FONT COLOR="green">// 2. allocate connection handle, set timeout</FONT>
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);
<FONT COLOR="green">// 3. Connect to the datasource "web" </FONT>
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>
|
