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
|
#include <stdlib.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
/* just a test comment */ exec sql whenever sqlerror do PrintAndStop(msg);
exec sql whenever sqlwarning do warn();
static void PrintAndStop(char *msg)
{
fprintf(stderr, "Error in statement '%s':\n", msg);
sqlprint();
exit(-1);
}
static void warn(void)
{
fprintf(stderr, "Warning: At least one column was truncated\n");
}
/* comment */
exec sql define AMOUNT 6;
exec sql define NAMELEN 8;
exec sql type intarray is int[AMOUNT];
typedef int intarray[AMOUNT];
int
main(void)
{
exec sql begin declare section;
exec sql ifdef NAMELEN;
typedef char string[NAMELEN];
intarray amount;
int increment=100;
char name[AMOUNT][NAMELEN];
char letter[AMOUNT][1];
struct name_letter_struct
{
char name[NAMELEN];
int amount;
char letter;
} name_letter[AMOUNT];
exec sql endif;
struct ind_struct
{
short a;
short b;
short c;
} ind[AMOUNT];
char command[128];
char *connection="pm";
int how_many;
char *user="postgres";
exec sql end declare section;
exec sql var name is string[AMOUNT];
char msg[128];
FILE *dbgs;
int i,j;
if ((dbgs = fopen("log", "w")) != NULL)
ECPGdebug(1, dbgs);
strcpy(msg, "connect");
exec sql connect to mm as main;
strcpy(msg, "connect");
exec sql connect to pm user :user;
strcpy(msg, "create");
exec sql at main create table "Test" (name char(NAMELEN), amount int, letter char(1));
exec sql create table "Test" (name char(NAMELEN), amount int, letter char(1));
strcpy(msg, "commit");
exec sql at main commit;
exec sql commit;
strcpy(msg, "set connection");
exec sql set connection to main;
strcpy(msg, "execute insert 1");
sprintf(command, "insert into \"Test\" (name, amount, letter) values ('db: ''mm''', 1, 'f')");
exec sql execute immediate :command;
printf("New tuple got OID = %ld\n", sqlca.sqlerrd[1]);
sprintf(command, "insert into \"Test\" (name, amount, letter) values ('db: ''mm''', 2, 't')");
exec sql execute immediate :command;
strcpy(msg, "execute insert 2");
sprintf(command, "insert into \"Test\" (name, amount, letter) values ('db: ''pm''', 1, 'f')");
exec sql at pm execute immediate :command;
strcpy(msg, "execute insert 3");
sprintf(command, "insert into \"Test\" (name, amount, letter) select name, amount+10, letter from \"Test\"");
exec sql execute immediate :command;
printf("Inserted %ld tuples via execute immediate\n", sqlca.sqlerrd[2]);
strcpy(msg, "execute insert 4");
sprintf(command, "insert into \"Test\" (name, amount, letter) select name, amount+?, letter from \"Test\"");
exec sql prepare I from :command;
exec sql at pm execute I using :increment;
printf("Inserted %ld tuples via prepared execute\n", sqlca.sqlerrd[2]);
strcpy(msg, "commit");
exec sql commit;
/* Start automatic transactioning for connection pm. */
exec sql at pm set autocommit to on;
exec sql at pm begin transaction;
strcpy(msg, "select");
exec sql select * into :name, :amount, :letter from "Test";
printf("Database: mm\n");
for (i=0, how_many=j=sqlca.sqlerrd[2]; i<j; i++)
{
exec sql begin declare section;
char n[8], l = letter[i][0];
int a = amount[i];
exec sql end declare section;
strncpy(n, name[i], NAMELEN);
printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, n, i, a, i, l);
amount[i]+=1000;
strcpy(msg, "insert");
exec sql at pm insert into "Test" (name, amount, letter) values (:n, :amount[i], :l);
}
strcpy(msg, "commit");
exec sql at pm commit;
sprintf (command, "select * from \"Test\"");
exec sql prepare F from :command;
exec sql declare CUR cursor for F;
strcpy(msg, "open");
exec sql open CUR;
strcpy(msg, "fetch");
exec sql fetch :how_many in CUR into :name, :amount, :letter;
printf("Database: mm\n");
for (i=0, j=sqlca.sqlerrd[2]; i<j; i++)
{
exec sql begin declare section;
char n[8], l = letter[i][0];
int a = amount[i];
exec sql end declare section;
strncpy(n, name[i], 8);
printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, n, i, a, i, l);
}
exec sql close CUR;
strcpy(msg, "select");
exec sql at :connection select name, amount, letter into :name, :amount, :letter from "Test";
printf("Database: %s\n", connection);
for (i=0, j=sqlca.sqlerrd[2]; i<j; i++)
printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, name[i], i, amount[i],i, letter[i][0]);
strcpy(msg, "commit");
exec sql commit;
strcpy(msg, "select");
exec sql at pm select name, amount, letter into :name_letter:ind from "Test";
printf("Database: pm\n");
for (i=0, j=sqlca.sqlerrd[2]; i<j; i++)
printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, name_letter[i].name, i, name_letter[i].amount,i, name_letter[i].letter);
name_letter[4].amount=1407;
strcpy(msg, "insert");
exec sql insert into "Test" (name, amount, letter) values (:name_letter[4]);
strcpy(msg, "select");
exec sql select name, amount, letter into :name_letter[2] from "Test" where amount = 1407;
printf("Database: mm\n");
printf("name[2]=%8.8s\tamount[2]=%d\tletter[2]=%c\n", name_letter[2].name, name_letter[2].amount, name_letter[2].letter);
/* Start automatic transactioning for connection main. */
exec sql set autocommit to on;
strcpy(msg, "drop");
exec sql drop table "Test";
exec sql at pm drop table "Test";
strcpy(msg, "disconnect");
exec sql disconnect main;
exec sql disconnect pm;
if (dbgs != NULL)
fclose(dbgs);
return (0);
}
|
