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
|
#include <stdio.h>
#include <stdlib.h>
#include "sqltypes.h"
EXEC SQL include sqlca.h;
EXEC SQL include ../regression;
EXEC SQL DEFINE MAXDBLEN 30;
/* Check SQLCODE, and produce a "standard error" if it's wrong! */
static void sql_check(char *fn, char *caller, int ignore)
{
char errorstring[255];
if (SQLCODE == ignore)
return;
else
{
if (SQLCODE != 0)
{
sprintf(errorstring, "**SQL error %ld doing '%s' in function '%s'. [%s]",
SQLCODE, caller, fn, sqlca.sqlerrm.sqlerrmc);
fprintf(stderr, "%s", errorstring);
printf("%s\n", errorstring);
/* attempt a ROLLBACK */
EXEC SQL rollback;
if (SQLCODE == 0)
{
sprintf(errorstring, "Rollback successful.\n");
} else {
sprintf(errorstring, "Rollback failed with code %ld.\n", SQLCODE);
}
fprintf(stderr, "%s", errorstring);
printf("%s\n", errorstring);
exit(1);
}
}
}
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
int c;
timestamp d;
timestamp e;
timestamp maxd;
char dbname[30];
EXEC SQL END DECLARE SECTION;
interval *intvl;
EXEC SQL whenever sqlerror sqlprint;
ECPGdebug(1, stderr);
strcpy(dbname, "ecpg1_regression");
EXEC SQL connect to :dbname;
sql_check("main", "connect", 0);
EXEC SQL SET DateStyle TO 'DMY';
EXEC SQL create table history (customerid integer, timestamp timestamp without time zone, action_taken char(5), narrative varchar(100));
sql_check("main", "create", 0);
EXEC SQL insert into history
(customerid, timestamp, action_taken, narrative)
values(1, '2003-05-07 13:28:34 CEST', 'test', 'test');
sql_check("main", "insert", 0);
EXEC SQL select max(timestamp)
into :maxd
from history;
sql_check("main", "select max", 100);
EXEC SQL select customerid, timestamp
into :c, :d
from history
where timestamp = :maxd
limit 1;
sql_check("main", "select", 0);
printf("Read in customer %d\n", c);
intvl = PGTYPESinterval_from_asc("1 day 2 hours 24 minutes 65 seconds", NULL);
PGTYPEStimestamp_add_interval(&d, intvl, &e);
free(intvl);
c++;
EXEC SQL insert into history
(customerid, timestamp, action_taken, narrative)
values(:c, :e, 'test', 'test');
sql_check("main", "update", 0);
EXEC SQL commit;
EXEC SQL drop table history;
sql_check("main", "drop", 0);
EXEC SQL commit;
EXEC SQL disconnect;
sql_check("main", "disconnect", 0);
printf("All OK!\n");
exit(0);
/*
Table "public.history"
Column | Type | Modifiers
--------------+-----------------------------+-----------
customerid | integer | not null
timestamp | timestamp without time zone | not null
action_taken | character(5) | not null
narrative | character varying(100) |
*/
}
|
