test1.pgc 3.83 KB
Newer Older
Michael Meskes's avatar
Michael Meskes committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
exec sql include sqlca;

exec sql whenever sqlerror do PrintAndStop(msg);
exec sql whenever sqlwarning do warn();

void PrintAndStop(msg)
{
	fprintf(stderr, "Error in statement '%s':\n", msg);
	sqlprint();
	exit(-1);
}

void warn(void)
{
	fprintf(stderr, "Warning: At least one column was truncated\n");
}
17

Michael Meskes's avatar
Michael Meskes committed
18
/* comment */
Michael Meskes's avatar
Michael Meskes committed
19
exec sql define AMOUNT 6;
20 21

exec sql type intarray is int[AMOUNT];
Michael Meskes's avatar
Michael Meskes committed
22
exec sql type string is char(8); 
23 24

typedef int intarray[AMOUNT];
25

26 27 28 29
int
main ()
{
exec sql begin declare section;
30 31
        intarray amount;
	int increment=100;
Michael Meskes's avatar
Michael Meskes committed
32
        char name[AMOUNT][8];
33 34
	char letter[AMOUNT][1];
	char command[128];
35
exec sql end declare section;
36 37
	exec sql var name is string(AMOUNT);
	char msg[128];
38 39 40 41 42 43 44
        FILE *dbgs;
        int i,j;

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

	strcpy(msg, "connect");
45 46 47 48
	exec sql connect to mm as main;

	strcpy(msg, "connect");
        exec sql connect to pm;
49 50

	strcpy(msg, "create");
Michael Meskes's avatar
Michael Meskes committed
51 52
	exec sql at main create table "Test" (name char(8), amount int, letter char(1));
	exec sql create table "Test" (name char(8), amount int, letter char(1));
53 54

	strcpy(msg, "commit");
55
	exec sql at main commit;
56
	exec sql commit;
57

58
	strcpy(msg, "set connection");
Michael Meskes's avatar
Michael Meskes committed
59
	exec sql set connection to main;
60

61
	strcpy(msg, "execute insert 1");
Michael Meskes's avatar
Michael Meskes committed
62
        sprintf(command, "insert into \"Test\" (name, amount, letter) values ('db: ''mm''', 1, 'f')");
63
        exec sql execute immediate :command;
Michael Meskes's avatar
Michael Meskes committed
64 65
	printf("New tuple got OID = %d\n", sqlca.sqlerrd[1]);
 
Michael Meskes's avatar
Michael Meskes committed
66
        sprintf(command, "insert into \"Test\" (name, amount, letter) values ('db: \\\'mm\\\'', 2, 't')");
67 68
        exec sql execute immediate :command;

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

73
        strcpy(msg, "execute insert 3");
Michael Meskes's avatar
Michael Meskes committed
74
        sprintf(command, "insert into \"Test\" (name, amount, letter) select name, amount+10, letter from \"Test\"");
75 76 77 78
        exec sql execute immediate :command;
        
	printf("Inserted %d tuples via execute immediate\n", sqlca.sqlerrd[2]);

79
        strcpy(msg, "execute insert 4");
Michael Meskes's avatar
Michael Meskes committed
80
        sprintf(command, "insert into \"Test\" (name, amount, letter) select name, amount+?, letter from \"Test\"");
81
	exec sql prepare I from :command;
82
        exec sql at pm execute I using :increment;
83 84 85
        
	printf("Inserted %d tuples via prepared execute\n", sqlca.sqlerrd[2]);

86 87
	strcpy(msg, "commit");
	exec sql commit;
Michael Meskes's avatar
Michael Meskes committed
88

Michael Meskes's avatar
Michael Meskes committed
89 90 91
	/* Start automatic transactioning for connection pm. */
	exec sql at pm set autocommit to on;
	exec sql at pm begin transaction;
92 93

        strcpy(msg, "select");
Michael Meskes's avatar
Michael Meskes committed
94
        exec sql select name, amount, letter into :name, :amount, :letter from "Test";
95

Michael Meskes's avatar
Michael Meskes committed
96
	printf("Database: mm\n");
97
        for (i=0, j=sqlca.sqlerrd[2]; i<j; i++)
Michael Meskes's avatar
Michael Meskes committed
98
	{
Michael Meskes's avatar
Michael Meskes committed
99 100 101 102 103 104 105
	    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);
Michael Meskes's avatar
Michael Meskes committed
106
	    amount[i]+=1000;
107

Michael Meskes's avatar
Michael Meskes committed
108 109 110
	    strcpy(msg, "insert");
	    exec sql at pm insert into "Test" (name, amount, letter) values (:n, :a, :l);	     
	}
Michael Meskes's avatar
Michael Meskes committed
111 112

        strcpy(msg, "select");
Michael Meskes's avatar
Michael Meskes committed
113
        exec sql at pm select * into :name, :amount, :letter from "Test";
114

Michael Meskes's avatar
Michael Meskes committed
115
	printf("Database: pm\n");
116
        for (i=0, j=sqlca.sqlerrd[2]; i<j; i++)
Michael Meskes's avatar
Michael Meskes committed
117
            printf("name[%d]=%8.8s\tamount[%d]=%d\tletter[%d]=%c\n", i, name[i], i, amount[i],i, letter[i][0]);
118 119 120
        
	strcpy(msg, "commit");
	exec sql commit;
121
	exec sql at pm commit;
122

Michael Meskes's avatar
Michael Meskes committed
123 124 125 126 127 128 129
	/* 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";

130
	strcpy(msg, "disconnect"); 
Michael Meskes's avatar
Michael Meskes committed
131 132
        exec sql disconnect main;
	exec sql disconnect pm;
133 134 135 136 137 138
        
	if (dbgs != NULL)
                fclose(dbgs);

	return (0);
}