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
|
/*
* Test @@identity
*/
#include <stdio.h>
#include <stdlib.h>
#include "common.h"
static HSTMT hstmt = SQL_NULL_HSTMT;
/*
* The driver has to parse the insert statement to extract the table name,
* so it might or might not work depending on the table name. This function
* tests it with a table whose name is given as argument.
*
* NOTE: The table name is injected directly into the SQL statement. It should
* already contain any escaping or quoting needed!
*/
void
test_identity_col(const char *createsql, const char *insertsql, const char *verifysql)
{
SQLRETURN rc;
SQLLEN rowcount;
/* Create the test table */
rc = SQLExecDirect(hstmt, (SQLCHAR *) createsql, SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating temp table", hstmt);
/* Insert some rows using the statement given by the caller */
rc = SQLExecDirect(hstmt, (SQLCHAR *) insertsql, SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLRowCount(hstmt, &rowcount);
CHECK_STMT_RESULT(rc, "SQLRowCount failed", hstmt);
printf("# of rows inserted: %d\n", (int) rowcount);
rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT @@IDENTITY AS last_insert", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
/* Fetch result */
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Print the contents of the table after the updates to verify */
rc = SQLExecDirect(hstmt, (SQLCHAR *) verifysql, SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
}
int main(int argc, char **argv)
{
SQLRETURN rc;
char server_version[32], v10 = 0;
test_connect();
rc = SQLGetInfo(conn, SQL_DBMS_VER, server_version, sizeof(server_version), NULL);
if (SQL_SUCCEEDED(rc))
{
int top_ver = 0;
if (sscanf(server_version, "%d.%*d", &top_ver) > 0)
if (top_ver > 9)
v10 = 1;
}
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
if (!SQL_SUCCEEDED(rc))
{
print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
exit(1);
}
test_identity_col(
v10 ?
"CREATE TEMPORARY TABLE tmptable (id int generated by default as identity primary key, t text)" :
"CREATE TEMPORARY TABLE tmptable (id serial primary key, t text)",
"INSERT INTO tmptable (t) VALUES ('simple')",
"SELECT * FROM tmptable");
test_identity_col(
v10 ?
"CREATE TEMPORARY TABLE tmptable2 (id int generated always as identity primary key, t text)" :
"CREATE TEMPORARY TABLE tmptable2 (id serial primary key, t text)",
"INSERT INTO tmptable2 (t) VALUES ('value.with.dots')",
"SELECT * FROM tmptable2");
test_identity_col(
v10 ?
"CREATE TEMPORARY TABLE \"tmp table\" (id int generated by default as identity primary key, t text)" :
"CREATE TEMPORARY TABLE \"tmp table\" (id serial primary key, t text)",
"INSERT INTO \"tmp table\" (t) VALUES ('space in table name')",
"SELECT * FROM \"tmp table\"");
/* Clean up */
test_disconnect();
return 0;
}
|
