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

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

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

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

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

exec sql type intarray is int[AMOUNT];

typedef int intarray[AMOUNT];
26

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

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

	strcpy(msg, "connect");
63 64 65 66
	exec sql connect to mm as main;

	strcpy(msg, "connect");
        exec sql connect to pm;
67 68

	strcpy(msg, "create");
69 70
	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));
71 72

	strcpy(msg, "commit");
73
	exec sql at main commit;
74
	exec sql commit;
75

76
	strcpy(msg, "set connection");
Michael Meskes's avatar
Michael Meskes committed
77
	exec sql set connection to main;
78

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

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

91
        strcpy(msg, "execute insert 3");
Michael Meskes's avatar
Michael Meskes committed
92
        sprintf(command, "insert into \"Test\" (name, amount, letter) select name, amount+10, letter from \"Test\"");
93 94
        exec sql execute immediate :command;
        
95
	printf("Inserted %ld tuples via execute immediate\n", sqlca.sqlerrd[2]);
96

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

104 105
	strcpy(msg, "commit");
	exec sql commit;
Michael Meskes's avatar
Michael Meskes committed
106

Michael Meskes's avatar
Michael Meskes committed
107 108 109
	/* Start automatic transactioning for connection pm. */
	exec sql at pm set autocommit to on;
	exec sql at pm begin transaction;
110 111

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

Michael Meskes's avatar
Michael Meskes committed
114
	printf("Database: mm\n");
Michael Meskes's avatar
Michael Meskes committed
115
        for (i=0, how_many=j=sqlca.sqlerrd[2]; i<j; i++)
Michael Meskes's avatar
Michael Meskes committed
116
	{
Michael Meskes's avatar
Michael Meskes committed
117 118 119 120 121
	    exec sql begin declare section;
	    char n[8], l = letter[i][0];
	    int a = amount[i];
	    exec sql end declare section;

122
	    strncpy(n, name[i], NAMELEN);
Michael Meskes's avatar
Michael Meskes committed
123
            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
124
	    amount[i]+=1000;
125

Michael Meskes's avatar
Michael Meskes committed
126 127 128
	    strcpy(msg, "insert");
	    exec sql at pm insert into "Test" (name, amount, letter) values (:n, :a, :l);	     
	}
Michael Meskes's avatar
Michael Meskes committed
129

130 131 132
	strcpy(msg, "commit");
	exec sql at pm commit;

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

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

        strcpy(msg, "select");
        exec sql at pm select name, amount, letter into :name_letter from "Test";

168 169
	strcpy(msg, "commit");
	exec sql commit;
170 171 172 173 174 175 176

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

Michael Meskes's avatar
Michael Meskes committed
178 179 180 181 182 183 184
	/* 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";

185
	strcpy(msg, "disconnect"); 
Michael Meskes's avatar
Michael Meskes committed
186 187
        exec sql disconnect main;
	exec sql disconnect pm;
188 189 190 191 192 193
        
	if (dbgs != NULL)
                fclose(dbgs);

	return (0);
}