test1.pgc 5.74 KB
Newer Older
Peter Eisentraut's avatar
Peter Eisentraut committed
1
#include <stdlib.h>
2
#include <string.h>
3 4
#include <stdlib.h>
#include <stdio.h>
5

6
/* just a test comment */ exec sql whenever sqlerror do PrintAndStop(msg);
Michael Meskes's avatar
Michael Meskes committed
7 8
exec sql whenever sqlwarning do warn();

9
static void PrintAndStop(char *msg)
Michael Meskes's avatar
Michael Meskes committed
10 11 12 13 14 15
{
	fprintf(stderr, "Error in statement '%s':\n", msg);
	sqlprint();
	exit(-1);
}

16
static void warn(void)
Michael Meskes's avatar
Michael Meskes committed
17 18 19
{
	fprintf(stderr, "Warning: At least one column was truncated\n");
}
20

Michael Meskes's avatar
Michael Meskes committed
21
/* comment */
Michael Meskes's avatar
Michael Meskes committed
22
exec sql define AMOUNT 6;
23
exec sql define NAMELEN 8;
24 25 26 27

exec sql type intarray is int[AMOUNT];

typedef int intarray[AMOUNT];
28

29
int
30
main(void)
31 32
{
exec sql begin declare section;
33 34
exec sql ifdef NAMELEN;
	typedef char string[NAMELEN];
35 36
        intarray amount;
	int increment=100;
37
        char name[AMOUNT][NAMELEN];
38
	char letter[AMOUNT][1];
39 40
	struct name_letter_struct
	{
41
		char name[NAMELEN];
42 43 44
		int amount;
		char letter;
	} name_letter[AMOUNT];
45 46 47
#if 0
	int not_used;
#endif
48
exec sql endif;
49 50 51 52 53 54
        struct ind_struct
        {
                short a;
                short b;
                short c;
        } ind[AMOUNT];
55
	char command[128];
56
	char *connection="pm";
Michael Meskes's avatar
Michael Meskes committed
57
	int how_many;
58
	char *user="postgres";
59
exec sql end declare section;
60
	exec sql var name is string[AMOUNT];
61
	char msg[128];
62 63 64 65 66 67 68
        FILE *dbgs;
        int i,j;

	if ((dbgs = fopen("log", "w")) != NULL)
                ECPGdebug(1, dbgs);

	strcpy(msg, "connect");
69 70 71
	exec sql connect to mm as main;

	strcpy(msg, "connect");
72
        exec sql connect to pm user :user;
73 74

	strcpy(msg, "create");
75 76
	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));
77 78

	strcpy(msg, "commit");
79
	exec sql at main commit;
80
	exec sql commit;
81

82
	strcpy(msg, "set connection");
Michael Meskes's avatar
Michael Meskes committed
83
	exec sql set connection to main;
84

85
	strcpy(msg, "execute insert 1");
Michael Meskes's avatar
Michael Meskes committed
86
        sprintf(command, "insert into \"Test\" (name, amount, letter) values ('db: ''mm''', 1, 'f')");
87
        exec sql execute immediate :command;
88
	printf("New tuple got OID = %ld\n", sqlca.sqlerrd[1]);
Michael Meskes's avatar
Michael Meskes committed
89
 
90
        sprintf(command, "insert into \"Test\" (name, amount, letter) values ('db: ''mm''', 2, 't')");
91 92
        exec sql execute immediate :command;

93
        strcpy(msg, "execute insert 2");
Michael Meskes's avatar
Michael Meskes committed
94
        sprintf(command, "insert into \"Test\" (name, amount, letter) values ('db: ''pm''', 1, 'f')");
95
        exec sql at pm execute immediate :command;
96

97
        strcpy(msg, "execute insert 3");
Michael Meskes's avatar
Michael Meskes committed
98
        sprintf(command, "insert into \"Test\" (name, amount, letter) select name, amount+10, letter from \"Test\"");
99 100
        exec sql execute immediate :command;
        
101
	printf("Inserted %ld tuples via execute immediate\n", sqlca.sqlerrd[2]);
102

103
        strcpy(msg, "execute insert 4");
Michael Meskes's avatar
Michael Meskes committed
104
        sprintf(command, "insert into \"Test\" (name, amount, letter) select name, amount+?, letter from \"Test\"");
105
	exec sql prepare I from :command;
106
        exec sql at pm execute I using :increment;
107
        
108
	printf("Inserted %ld tuples via prepared execute\n", sqlca.sqlerrd[2]);
109

110 111
	strcpy(msg, "commit");
	exec sql commit;
Michael Meskes's avatar
Michael Meskes committed
112

Michael Meskes's avatar
Michael Meskes committed
113 114 115
	/* Start automatic transactioning for connection pm. */
	exec sql at pm set autocommit to on;
	exec sql at pm begin transaction;
116 117

        strcpy(msg, "select");
118
        exec sql select * into :name, :amount, :letter from "Test";
119

Michael Meskes's avatar
Michael Meskes committed
120
	printf("Database: mm\n");
Michael Meskes's avatar
Michael Meskes committed
121
        for (i=0, how_many=j=sqlca.sqlerrd[2]; i<j; i++)
Michael Meskes's avatar
Michael Meskes committed
122
	{
Michael Meskes's avatar
Michael Meskes committed
123 124 125 126 127
	    exec sql begin declare section;
	    char n[8], l = letter[i][0];
	    int a = amount[i];
	    exec sql end declare section;

128
	    strncpy(n, name[i], NAMELEN);
Michael Meskes's avatar
Michael Meskes committed
129
            printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, n, i, a, i, l);
Michael Meskes's avatar
Michael Meskes committed
130
	    amount[i]+=1000;
131

Michael Meskes's avatar
Michael Meskes committed
132
	    strcpy(msg, "insert");
Michael Meskes's avatar
Michael Meskes committed
133
	    exec sql at pm insert into "Test" (name, amount, letter) values (:n, :amount[i], :l);
Michael Meskes's avatar
Michael Meskes committed
134
	}
Michael Meskes's avatar
Michael Meskes committed
135

136 137 138
	strcpy(msg, "commit");
	exec sql at pm commit;

Michael Meskes's avatar
Michael Meskes committed
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
	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;
	
Michael Meskes's avatar
Michael Meskes committed
164
        strcpy(msg, "select");
165
        exec sql at :connection select name, amount, letter into :name, :amount, :letter from "Test";
166

167
	printf("Database: %s\n", connection);
168
        for (i=0, j=sqlca.sqlerrd[2]; i<j; i++)
Michael Meskes's avatar
Michael Meskes committed
169
            printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, name[i], i, amount[i],i, letter[i][0]);
170

171 172
	strcpy(msg, "commit");
	exec sql commit;
173 174 175 176 177 178 179

        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);
180

Michael Meskes's avatar
Michael Meskes committed
181 182 183 184 185 186 187 188 189 190
	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);

Michael Meskes's avatar
Michael Meskes committed
191 192 193 194 195 196 197
	/* 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";

198
	strcpy(msg, "disconnect"); 
Michael Meskes's avatar
Michael Meskes committed
199 200
        exec sql disconnect main;
	exec sql disconnect pm;
201 202 203 204 205 206
        
	if (dbgs != NULL)
                fclose(dbgs);

	return (0);
}