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
|
/*
* Program type: API Interface
*
* Description:
* This program performs a multi-database transaction
* with a two-phase commit. A 'currency' field is updated
* in database 1 for table country, and corresponding
* 'from_currency' or 'to_currency' fields are updated in
* database 2 for table cross_rate. The transaction is
* committed only if all instances of the string are
* changed successfully in both databases.
* The contents of this file are subject to the Interbase Public
* License Version 1.0 (the "License"); you may not use this file
* except in compliance with the License. You may obtain a copy
* of the License at http://www.Inprise.com/IPL.html
*
* Software distributed under the License is distributed on an
* "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express
* or implied. See the License for the specific language governing
* rights and limitations under the License.
*
* The Original Code was created by Inprise Corporation
* and its predecessors. Portions created by Inprise Corporation are
* Copyright (C) Inprise Corporation.
*
* All Rights Reserved.
* Contributor(s): ______________________________________.
*/
#include <stdlib.h>
#include <string.h>
#include <ibase.h>
#include <stdio.h>
#include "example.h"
#define BUFLEN 512
#define CURRENLEN 10
char *sel_str1 =
"SELECT currency FROM country WHERE country = 'Canada'";
int main (int argc, char** argv)
{
char *country = "Canada"; /* passed as a parameter */
char *new_name = "CdnDollar"; /* passed as a parameter */
char orig_name[CURRENLEN + 1];
char buf[BUFLEN + 1];
isc_db_handle db1 = NULL, /* handle for database 1 */
db2 = NULL; /* handle for database 2 */
isc_tr_handle trans1 = NULL; /* transaction handle */
ISC_STATUS_ARRAY status;
XSQLDA * sel_sqlda;
isc_stmt_handle stmt = NULL;
long stat1, stat2, stat3;
char empdb[128], empdb2[128];
if (argc > 1)
strcpy(empdb, argv[1]);
else
strcpy(empdb, "employee.fdb");
if (argc > 2)
strcpy(empdb2, argv[2]);
else
strcpy(empdb2, "employe2.fdb");
/* Open database 1. */
printf("Attaching to database %s\n", empdb);
if (isc_attach_database(status, 0, empdb, &db1, 0, NULL))
{
ERREXIT(status, 1)
}
/* Open database 2. */
printf ("Attaching to database %s\n", empdb2);
if (isc_attach_database(status, 0, empdb2, &db2, 0, NULL))
{
ERREXIT(status, 1)
}
/* Start a two-database transaction. */
if (isc_start_transaction(status, &trans1, 2, &db1, 0, NULL, &db2, 0, NULL))
{
ERREXIT(status, 1)
}
/*
* Get the string, which is to be globally changed.
*/
sprintf(buf, "SELECT currency FROM country WHERE country = '%s'", country);
sel_sqlda = (XSQLDA *) malloc(XSQLDA_LENGTH(1));
sel_sqlda->sqln = 1;
sel_sqlda->version = 1;
if (isc_dsql_allocate_statement(status, &db1, &stmt))
{
ERREXIT(status, 1)
}
if (isc_dsql_prepare(status, &trans1, &stmt, 0, sel_str1, 1, sel_sqlda))
{
ERREXIT(status, 1)
}
sel_sqlda->sqlvar[0].sqldata = orig_name;
sel_sqlda->sqlvar[0].sqltype = SQL_TEXT;
sel_sqlda->sqlvar[0].sqllen = CURRENLEN;
if (isc_dsql_execute(status, &trans1, &stmt, 1, NULL))
{
ERREXIT(status, 1)
}
if (isc_dsql_fetch(status, &stmt, 1, sel_sqlda))
{
ERREXIT(status, 1)
}
orig_name[CURRENLEN] = '\0';
printf("Modifying currency string: %s\n", orig_name);
/*
* Change the string in database 1.
*/
sprintf(buf, "UPDATE country SET currency = '%s' WHERE country = 'Canada'",
new_name);
stat1 = 0L;
if (isc_dsql_execute_immediate(status, &db1, &trans1, 0, buf, 1, NULL))
{
isc_print_status(status);
stat1 = isc_sqlcode(status);
}
/*
* Change all corresponding occurences of the string in database 2.
*/
sprintf(buf, "UPDATE cross_rate SET from_currency = '%s' WHERE \
from_currency = '%s'", new_name, orig_name);
stat2 = 0L;
if (isc_dsql_execute_immediate(status, &db2, &trans1, 0, buf, 1, NULL))
{
isc_print_status(status);
stat2 = isc_sqlcode(status);
}
sprintf(buf, "UPDATE cross_rate SET to_currency = '%s' WHERE \
to_currency = '%s'", new_name, orig_name);
stat3 = 0L;
if (isc_dsql_execute_immediate(status, &db2, &trans1, 0, buf, 1, NULL))
{
isc_print_status(status);
stat3 = isc_sqlcode(status);
}
if (isc_dsql_free_statement(status, &stmt, DSQL_close))
isc_print_status(status);
/*
* If all statements executed successfully, commit the transaction.
* Otherwise, undo all work.
*/
if (!stat1 && !stat2 && !stat3)
{
if (isc_commit_transaction (status, &trans1))
isc_print_status(status);
printf("Changes committed.\n");
}
else
{
printf("update1: %d\n", stat1);
printf("update2: %d\n", stat2);
printf("update3: %d\n", stat3);
if (isc_rollback_transaction(status, &trans1))
isc_print_status(status);
printf("Changes undone.\n");
}
/* Close database 1. */
if (isc_detach_database(status, &db1))
isc_print_status(status);
/* Close database 2. */
if (isc_detach_database(status, &db2))
isc_print_status(status);
free(sel_sqlda);
return 0;
}
|
