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
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
|
/* -*- C -*- */
/* The first example illustrates creating a table, adding some data
* to it, and selecting the inserted data. The second example shows
* interactive ad hoc query processing.
*
* Actual applications include more complete error checking following
* calls to SQL/CLI routines. That material is omitted from this
* Appendix for the sake of clarity.
*
* This file is adapted for PostgreSQL
* from the CLI Annex in the SQL98 August 1994 draft standard.
* Thomas G. Lockhart 1999-06-16
*/
/*
* B.2 Interactive Query
*
* This sample function uses the concise CLI functions to
* interactively execute a SQL statement supplied as an argument.
* In the case where the user types a SELECT statement, the function
* fetches and displays all rows of the result set.
*
* This example illustrates the use of GetDiagField() to identify
* the type of SQL statement executed and, for SQL statements where
* the row count is defined on all implementations, the use of
* GetDiagField() to obtain the row count.
*/
/*
* Sample program - uses concise CLI functions to execute
* interactively an ad hoc statement.
*/
#include "sqlcli.h"
#include <string.h>
#include <stdlib.h>
#define MAXCOLS 100
#define max(a,b) (a>b?a:b)
int print_err(SQLSMALLINT handletype, SQLINTEGER handle);
int build_indicator_message(SQLCHAR *errmsg,
SQLPOINTER * data,
SQLINTEGER collen,
SQLINTEGER *outlen,
SQLSMALLINT colnum);
SQLINTEGER display_length(SQLSMALLINT coltype,
SQLINTEGER collen,
SQLCHAR *colname);
example2(SQLCHAR *server, SQLCHAR *uid, SQLCHAR *authen, SQLCHAR *sqlstr)
{
int i;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLCHAR errmsg[256];
SQLCHAR colname[64];
SQLSMALLINT coltype;
SQLSMALLINT colnamelen;
SQLSMALLINT nullable;
SQLINTEGER collen[MAXCOLS];
SQLSMALLINT scale;
SQLINTEGER outlen[MAXCOLS];
SQLCHAR *data[MAXCOLS];
SQLSMALLINT nresultcols;
SQLINTEGER rowcount;
SQLINTEGER stmttype;
SQLRETURN rc;
/* allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
/* allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
/* connect to database */
if (SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, authen, SQL_NTS)
!= SQL_SUCCESS)
return (print_err(SQL_HANDLE_DBC, hdbc));
/* allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
/* execute the SQL statement */
if (SQLExecDirect(hstmt, sqlstr, SQL_NTS) != SQL_SUCCESS)
return (print_err(SQL_HANDLE_STMT, hstmt));
/* see what kind of statement it was */
SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0,
SQL_DIAG_DYNAMIC_FUNCTION_CODE,
(SQLPOINTER) & stmttype, 0, (SQLSMALLINT *) NULL);
switch (stmttype)
{
/* SELECT statement */
case SQL_SELECT_CURSOR:
/* determine number of result columns */
SQLNumResultCols(hstmt, &nresultcols);
/* display column names */
for (i = 0; i < nresultcols; i++)
{
SQLDescribeCol(hstmt, i + 1, colname, sizeof(colname),
&colnamelen, &coltype, &collen[i], &scale, &nullable);
/*
* assume there is a display_length function which
* computes correct length given the data type
*/
collen[i] = display_length(coltype, collen[i], colname);
(void) printf("%*.*s", collen[i], collen[i], colname);
/* allocate memory to bind column */
data[i] = (SQLCHAR *) malloc(collen[i]);
/*
* bind columns to program vars, converting all types to
* CHAR
*/
SQLBindCol(hstmt, i + 1, SQL_CHAR, data[i], collen[i],
&outlen[i]);
}
/* display result rows */
while ((rc = SQLFetch(hstmt)) != SQL_ERROR)
{
errmsg[0] = '\0';
if (rc == SQL_SUCCESS_WITH_INFO)
{
for (i = 0; i < nresultcols; i++)
{
if (outlen[i] == SQL_NULL_DATA || outlen[i] >= collen[i])
build_indicator_message(errmsg,
(SQLPOINTER *) & data[i], collen[i],
&outlen[i], i);
(void) printf("%*.*s ", outlen[i], outlen[i],
data[i]);
} /* for all columns in this row */
/* print any truncation messages */
(void) printf("\n%s", errmsg);
}
} /* while rows to fetch */
SQLClose(hstmt);
break;
/* searched DELETE, INSERT or searched UPDATE statement */
case SQL_DELETE_WHERE:
case SQL_INSERT:
case SQL_UPDATE_WHERE:
/* check rowcount */
SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0,
SQL_DIAG_ROW_COUNT, (SQLPOINTER) & rowcount, 0,
(SQLSMALLINT *) NULL);
if (SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT)
== SQL_SUCCESS)
(void) printf("Operation successful\n");
else
(void) printf("Operation failed\n");
(void) printf("%ld rows affected\n", rowcount);
break;
/* other statements */
case SQL_ALTER_TABLE:
case SQL_CREATE_TABLE:
case SQL_CREATE_VIEW:
case SQL_DROP_TABLE:
case SQL_DROP_VIEW:
case SQL_DYNAMIC_DELETE_CURSOR:
case SQL_DYNAMIC_UPDATE_CURSOR:
case SQL_GRANT:
case SQL_REVOKE:
if (SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT)
== SQL_SUCCESS)
(void) printf("Operation successful\n");
else
(void) printf("Operation failed\n");
break;
/* implementation-defined statement */
default:
(void) printf("Statement type=%ld\n", stmttype);
break;
}
/* free data buffers */
for (i = 0; i < nresultcols; i++)
(void) free(data[i]);
/* free statement handle */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
/* disconnect from database */
SQLDisconnect(hdbc);
/* free connection handle */
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
/* free environment handle */
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return (0);
}
/***********************************************************
The following functions are given for completeness, but are
not relevant for understanding the database processing
nature of CLI
***********************************************************/
#define MAX_NUM_PRECISION 15
/*#define max length of char string representation of no. as:
= max(precision) + leading sign + E + exp sign + max exp length
= 15 + 1 + 1 + 1 + 2
= 15 + 5
*/
#define MAX_NUM_STRING_SIZE (MAX_NUM_PRECISION + 5)
SQLINTEGER
display_length(SQLSMALLINT coltype, SQLINTEGER collen,
SQLCHAR *colname)
{
switch (coltype)
{
case SQL_VARCHAR:
case SQL_CHAR:
return (max(collen, strlen((char *) colname)));
break;
case SQL_FLOAT:
case SQL_DOUBLE:
case SQL_NUMERIC:
case SQL_REAL:
case SQL_DECIMAL:
return (max(MAX_NUM_STRING_SIZE, strlen((char *) colname)));
break;
case SQL_DATETIME:
return (max(SQL_TIMESTAMP_LEN, strlen((char *) colname)));
break;
case SQL_INTEGER:
return (max(10, strlen((char *) colname)));
break;
case SQL_SMALLINT:
return (max(5, strlen((char *) colname)));
break;
default:
(void) printf("Unknown datatype, %d\n", coltype);
return (0);
break;
}
}
int
build_indicator_message(SQLCHAR *errmsg, SQLPOINTER * data,
SQLINTEGER collen, SQLINTEGER *outlen, SQLSMALLINT colnum)
{
if (*outlen == SQL_NULL_DATA)
{
(void) strcpy((char *) data, "NULL");
*outlen = 4;
}
else
{
sprintf((char *) errmsg + strlen((char *) errmsg),
"%d chars truncated, col %d\n", *outlen - collen + 1,
colnum);
*outlen = 255;
}
}
|
