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
|
/*
* The sqlvcp.pc program demonstrates how you can use the
* sqlvcp() function to determine the actual size of a
* VARCHAR struct. The size is then used as an offset to
* increment a pointer that steps through an array of
* VARCHARs.
*
* This program also demonstrates the use of the sqlgls()
* function, to get the text of the last SQL statement executed.
* sqlgls() is described in the "Error Handling" chapter of
* The Programmer’s Guide to the Oracle Pro*C/C++ Precompiler.
*/
#include <stdio.h>
#include <sqlca.h>
#include <sqlcpr.h>
/* Fake a VARCHAR pointer type. */
struct my_vc_ptr
{
unsigned short len;
unsigned char arr[32767];
};
/* Define a type for the VARCHAR pointer */
typedef struct my_vc_ptr my_vc_ptr;
my_vc_ptr *vc_ptr;
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR *names;
int limit; /* for use in FETCH FOR clause */
char *username = "scott/tiger";
EXEC SQL END DECLARE SECTION;
void sql_error();
extern void sqlvcp(), sqlgls();
main()
{
See Also: Chapter 9, "Handling Runtime Errors"
VARCHAR Variables
Datatypes and Host Variables 4-27
unsigned int vcplen, function_code, padlen, buflen;
int i;
char stmt_buf[120];
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL CONNECT :username;
printf("\nConnected.\n");
/* Find number of rows in table. */
EXEC SQL SELECT COUNT(*) INTO :limit FROM emp;
/* Declare a cursor for the FETCH statement. */
EXEC SQL DECLARE emp_name_cursor CURSOR FOR
SELECT ename FROM emp;
EXEC SQL FOR :limit OPEN emp_name_cursor;
/* Set the desired DATA length for the VARCHAR. */
vcplen = 10;
/* Use SQLVCP to help find the length to malloc. */
sqlvcp(&vcplen, &padlen);
printf("Actual array length of VARCHAR is %ld\n", padlen);
/* Allocate the names buffer for names.
Set the limit variable for the FOR clause. */
names = (VARCHAR *) malloc((sizeof (short) +
(int) padlen) * limit);
if (names == 0)
{
printf("Memory allocation error.\n");
exit(1);
}
/* Set the maximum lengths before the FETCH.
* Note the "trick" to get an effective VARCHAR *.
*/
for (vc_ptr = (my_vc_ptr *) names, i = 0; i < limit; i++)
{
vc_ptr->len = (short) padlen;
vc_ptr = (my_vc_ptr *)((char *) vc_ptr +
padlen + sizeof (short));
}
/* Execute the FETCH. */
EXEC SQL FOR :limit FETCH emp_name_cursor INTO :names;
VARCHAR Variables
4-28 Pro*C/C++ Precompiler Programmer’s Guide
/* Print the results. */
printf("Employee names--\n");
for (vc_ptr = (my_vc_ptr *) names, i = 0; i < limit; i++)
{
printf
("%.*s\t(%d)\n", vc_ptr->len, vc_ptr->arr, vc_ptr->len);
vc_ptr = (my_vc_ptr *)((char *) vc_ptr +
padlen + sizeof (short));
}
/* Get statistics about the most recent
* SQL statement using SQLGLS. Note that
* the most recent statement in this example
* is not a FETCH, but rather "SELECT ENAME FROM EMP"
* (the cursor).
*/
buflen = (long) sizeof (stmt_buf);
/* The returned value should be 1, indicating no error. */
sqlgls(stmt_buf, &buflen, &function_code);
if (buflen != 0)
{
/* Print out the SQL statement. */
printf("The SQL statement was--\n%.*s\n", buflen, stmt_buf);
/* Print the returned length. */
printf("The statement length is %ld\n", buflen);
/* Print the attributes. */
printf("The function code is %ld\n", function_code);
EXEC SQL COMMIT RELEASE;
exit(0);
}
else
{
printf("The SQLGLS function returned an error.\n");
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
}
void
sql_error()
{
char err_msg[512];
int buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(1);
} |
