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
|
/* -*- 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.1 Create table, insert, select
*
* This example function creates a table, inserts data into the table,
* and selects the inserted data.
*
* This example illustrates the execution of SQL statement text
* both using the Prepare() and Execute() method and using the
* ExecDirect() method. The example also illustrates both the case
* where the application uses the automatically-generated descriptors
* and the case where the application allocates a descriptor of its
* own and associates this descriptor with the SQL statement.
*
* Code comments include the equivalent statements in embedded SQL
* to show how embedded SQL operations correspond to SQL/CLI function
* calls.
*/
#include "sqlcli.h"
#include <string.h>
#ifndef NULL
#define NULL 0
#endif
int print_err(SQLSMALLINT handletype, SQLINTEGER handle);
int
example1(SQLCHAR * server, SQLCHAR * uid, SQLCHAR * authen)
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHDESC hdesc;
SQLHDESC hdesc1;
SQLHDESC hdesc2;
SQLHSTMT hstmt;
SQLINTEGER id;
SQLSMALLINT idind;
SQLCHAR name[51];
SQLINTEGER namelen;
SQLSMALLINT nameind;
/* EXEC SQL CONNECT TO :server USER :uid; */
/* 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);
/* EXEC SQL CREATE TABLE NAMEID (ID integer, NAME varchar(50)); */
{
SQLCHAR create[] = "CREATE TABLE NAMEID (ID integer,"
" NAME varchar(50))";
/* execute the CREATE TABLE statement */
if (SQLExecDirect(hstmt, create, SQL_NTS) != SQL_SUCCESS)
return (print_err(SQL_HANDLE_STMT, hstmt));
}
/* EXEC SQL COMMIT WORK; */
/* commit CREATE TABLE */
SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT);
/* EXEC SQL INSERT INTO NAMEID VALUES ( :id, :name ); */
{
SQLCHAR insert[] = "INSERT INTO NAMEID VALUES (?, ?)";
/* show the use of SQLPrepare/SQLExecute method */
/* prepare the INSERT */
if (SQLPrepare(hstmt, insert, SQL_NTS) != SQL_SUCCESS)
return (print_err(SQL_HANDLE_STMT, hstmt));
/* application parameter descriptor */
SQLGetStmtAttr(hstmt, SQL_ATTR_APP_PARAM_
DESC, &hdesc1, 0L,
(SQLINTEGER *) NULL);
SQLSetDescRec(hdesc1, 1, SQL_INTEGER, 0, 0L, 0, 0,
(SQLPOINTER) & id, (SQLINTEGER *) NULL, (SQLSMALLINT *) NULL);
SQLSetDescRec(hdesc1, 2, SQL_CHAR, 0, 0L, 0, 0,
(SQLPOINTER) name, (SQLINTEGER *) NULL,
(SQLSMALLINT *) NULL);
/* implementation parameter descriptor */
SQLGetStmtAttr(hstmt, SQL_ATTR_IMP_PARAM_
DESC, &hdesc2, 0L,
(SQLINTEGER *) NULL);
SQLSetDescRec(hdesc2, 1, SQL_INTEGER, 0, 0L, 0, 0,
(SQLPOINTER) NULL, (SQLINTEGER *) NULL,
(SQLSMALLINT *) NULL);
SQLSetDescRec(hdesc2, 2, SQL_VARCHAR, 0, 50L, 0, 0,
(SQLPOINTER) NULL, (SQLINTEGER *) NULL,
(SQLSMALLINT *) NULL);
/* assign parameter values and execute the INSERT */
id = 500;
(void) strcpy(name, "Babbage");
if (SQLExecute(hstmt) != SQL_SUCCESS)
return (print_err(SQL_HANDLE_STMT, hstmt));
}
/* EXEC SQL COMMIT WORK; */
SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT);
/* commit inserts */
/* EXEC SQL DECLARE c1 CURSOR FOR SELECT ID, NAME FROM NAMEID; */
/* EXEC SQL OPEN c1; */
/* The application doesn't specify "declare c1 cursor for" */
{
SQLCHAR select[] = "select ID, NAME from NAMEID";
if (SQLExecDirect(hstmt, select, SQL_NTS) != SQL_SUCCESS)
return (print_err(SQL_HANDLE_STMT, hstmt));
}
/* EXEC SQL FETCH c1 INTO :id, :name; */
/* this time, explicitly allocate an application row descriptor */
SQLAllocHandle(SQL_HANDLE_DESC, hdbc, &hdesc);
SQLSetDescRec(hdesc, 1, SQL_INTEGER, 0, 0L, 0, 0,
(SQLPOINTER) & id, (SQLINTEGER *) NULL, (SQLSMALLINT *) & idind);
SQLSetDescRec(hdesc, 2, SQL_
CHAR, 0, (SQLINTEGER) sizeof(name),
0, 0, (SQLPOINTER) & name, (SQLINTEGER *) & namelen,
(SQLSMALLINT *) & nameind);
/* associate descriptor with statement handle */
SQLSetStmtAttr(hstmt, SQL_ATTR_APP_ROW_DESC, &hdesc, 0);
/* execute the fetch */
SQLFetch(hstmt);
/* EXEC SQL COMMIT WORK; */
/* commit the transaction */
SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT);
/* EXEC SQL CLOSE c1; */
SQLClose(hstmt);
/* free the statement handle */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
/* EXEC SQL DISCONNECT; */
/* disconnect from the database */
SQLDisconnect(hdbc);
/* free descriptor handle */
SQLFreeHandle(SQL_HANDLE_DESC, hdesc);
/* free descriptor handle */
SQLFreeHandle(SQL_HANDLE_DESC, hdesc1);
/* free descriptor handle */
SQLFreeHandle(SQL_HANDLE_DESC, hdesc2);
/* free connection handle */
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
/* free environment handle */
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return (0);
}
|
