1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test prepared statements API", "[api]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a (i TINYINT)"));
13 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (11), (12), (13)"));
14 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s VARCHAR)"));
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES (NULL), ('test')"));
16
17 // query using a prepared statement
18 // integer:
19 result = con.Query("SELECT COUNT(*) FROM a WHERE i=$1", 12);
20 REQUIRE(CHECK_COLUMN(result, 0, {1}));
21 // strings:
22 result = con.Query("SELECT COUNT(*) FROM strings WHERE s=$1", "test");
23 REQUIRE(CHECK_COLUMN(result, 0, {1}));
24 // multiple parameters
25 result = con.Query("SELECT COUNT(*) FROM a WHERE i>$1 AND i<$2", 10, 13);
26 REQUIRE(CHECK_COLUMN(result, 0, {2}));
27
28 // test various integer types
29 result = con.Query("SELECT COUNT(*) FROM a WHERE i=$1", (int8_t)12);
30 REQUIRE(CHECK_COLUMN(result, 0, {1}));
31 result = con.Query("SELECT COUNT(*) FROM a WHERE i=$1", (int16_t)12);
32 REQUIRE(CHECK_COLUMN(result, 0, {1}));
33 result = con.Query("SELECT COUNT(*) FROM a WHERE i=$1", (int32_t)12);
34 REQUIRE(CHECK_COLUMN(result, 0, {1}));
35 result = con.Query("SELECT COUNT(*) FROM a WHERE i=$1", (int64_t)12);
36 REQUIRE(CHECK_COLUMN(result, 0, {1}));
37
38 // create a prepared statement and use it to query
39 auto prepare = con.Prepare("SELECT COUNT(*) FROM a WHERE i=$1");
40
41 result = prepare->Execute(12);
42 REQUIRE(CHECK_COLUMN(result, 0, {1}));
43 result = prepare->Execute(13);
44 REQUIRE(CHECK_COLUMN(result, 0, {1}));
45 REQUIRE(prepare->n_param == 1);
46
47 string prepare_name = prepare->name;
48 // we can execute the prepared statement ourselves as well using the name
49 result = con.Query("EXECUTE " + prepare_name + "(12)");
50 REQUIRE(CHECK_COLUMN(result, 0, {1}));
51 // if we destroy the prepared statement it goes away
52 prepare.reset();
53 REQUIRE_FAIL(con.Query("EXECUTE " + prepare_name + "(12)"));
54}
55
56TEST_CASE("Test prepared statements and dependencies", "[api]") {
57 unique_ptr<QueryResult> result;
58 DuckDB db(nullptr);
59 Connection con(db), con2(db);
60
61 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a(i TINYINT)"));
62 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (11), (12), (13)"));
63
64 // query using a prepared statement in con1
65 result = con.Query("SELECT COUNT(*) FROM a WHERE i=$1", 12);
66 REQUIRE(CHECK_COLUMN(result, 0, {1}));
67 // now delete the table in con2
68 REQUIRE_NO_FAIL(con2.Query("DROP TABLE a"));
69
70 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a(i TINYINT)"));
71
72 // keep a prepared statement around
73 auto prepare = con.Prepare("SELECT COUNT(*) FROM a WHERE i=$1");
74
75 // now we can't drop the table
76 REQUIRE_FAIL(con2.Query("DROP TABLE a"));
77
78 // until we delete the prepared statement
79 prepare.reset();
80
81 REQUIRE_NO_FAIL(con2.Query("DROP TABLE a"));
82}
83
84TEST_CASE("Dropping connection with prepared statement resets dependencies", "[api]") {
85 unique_ptr<QueryResult> result;
86 DuckDB db(nullptr);
87 auto con = make_unique<Connection>(db);
88 Connection con2(db);
89
90 REQUIRE_NO_FAIL(con->Query("CREATE TABLE a(i TINYINT)"));
91 REQUIRE_NO_FAIL(con->Query("INSERT INTO a VALUES (11), (12), (13)"));
92
93 auto prepared = con->Prepare("SELECT COUNT(*) FROM a WHERE i=$1");
94 result = prepared->Execute(12);
95 REQUIRE(CHECK_COLUMN(result, 0, {1}));
96
97 // now we can't drop the table
98 REQUIRE_FAIL(con2.Query("DROP TABLE a"));
99
100 // now drop con
101 con.reset();
102
103 // now we can
104 REQUIRE_NO_FAIL(con2.Query("DROP TABLE a"));
105}
106
107TEST_CASE("Test destructors of prepared statements", "[api]") {
108 unique_ptr<DuckDB> db;
109 unique_ptr<Connection> con;
110 unique_ptr<PreparedStatement> prepare;
111 unique_ptr<QueryResult> result;
112
113 // test destruction of connection
114 db = make_unique<DuckDB>(nullptr);
115 con = make_unique<Connection>(*db);
116 // create a prepared statement
117 prepare = con->Prepare("SELECT $1::INTEGER+$2::INTEGER");
118 // we can execute it
119 result = prepare->Execute(3, 5);
120 REQUIRE(CHECK_COLUMN(result, 0, {8}));
121 // now destroy the connection
122 con.reset();
123 // now we can't use the prepared statement anymore
124 REQUIRE_FAIL(prepare->Execute(3, 5));
125 // destroying the prepared statement is fine
126 prepare.reset();
127
128 // test destruction of db
129 // create a connection and prepared statement again
130 con = make_unique<Connection>(*db);
131 prepare = con->Prepare("SELECT $1::INTEGER+$2::INTEGER");
132 // we can execute it
133 result = prepare->Execute(3, 5);
134 REQUIRE(CHECK_COLUMN(result, 0, {8}));
135 // destroy the db
136 db.reset();
137 // now we can't use the prepared statement anymore
138 REQUIRE_FAIL(prepare->Execute(3, 5));
139 // neither can we use the connection
140 REQUIRE_FAIL(con->Query("SELECT 42"));
141 // or prepare new statements
142 prepare = con->Prepare("SELECT $1::INTEGER+$2::INTEGER");
143 REQUIRE(!prepare->success);
144}
145
146TEST_CASE("Test incorrect usage of prepared statements API", "[api]") {
147 unique_ptr<QueryResult> result;
148 DuckDB db(nullptr);
149 Connection con(db);
150
151 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a (i TINYINT)"));
152 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (11), (12), (13)"));
153
154 // this fails if there is a mismatch between number of arguments in prepare and in variadic
155 // too few:
156 REQUIRE_FAIL(con.Query("SELECT COUNT(*) FROM a WHERE i=$1 AND i>$2", 11));
157 // too many:
158 REQUIRE_FAIL(con.Query("SELECT COUNT(*) FROM a WHERE i=$1 AND i>$2", 11, 13, 17));
159
160 // prepare an SQL string with a parse error
161 auto prepare = con.Prepare("SELEC COUNT(*) FROM a WHERE i=$1");
162 // we cannot execute this prepared statement
163 REQUIRE_FAIL(prepare->Execute(12));
164
165 // cannot prepare multiple statements at once
166 prepare = con.Prepare("SELECT COUNT(*) FROM a WHERE i=$1; SELECT 42+$2;");
167 REQUIRE_FAIL(prepare->Execute(12));
168
169 // also not in the Query syntax
170 REQUIRE_FAIL(con.Query("SELECT COUNT(*) FROM a WHERE i=$1; SELECT 42+$2", 11));
171}
172
173TEST_CASE("Test multiple prepared statements", "[api]") {
174 unique_ptr<QueryResult> result;
175 DuckDB db(nullptr);
176 Connection con(db);
177
178 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a (i TINYINT)"));
179 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (11), (12), (13)"));
180
181 // test that we can have multiple open prepared statements at a time
182 auto prepare = con.Prepare("SELECT COUNT(*) FROM a WHERE i=$1");
183 auto prepare2 = con.Prepare("SELECT COUNT(*) FROM a WHERE i>$1");
184
185 result = prepare->Execute(12);
186 REQUIRE(CHECK_COLUMN(result, 0, {1}));
187 result = prepare2->Execute(11);
188 REQUIRE(CHECK_COLUMN(result, 0, {2}));
189}
190
191TEST_CASE("Test prepared statements and transactions", "[api]") {
192 unique_ptr<QueryResult> result;
193 DuckDB db(nullptr);
194 Connection con(db);
195
196 // create prepared statements in a transaction
197 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
198 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a (i TINYINT)"));
199 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (11), (12), (13)"));
200
201 auto prepare = con.Prepare("SELECT COUNT(*) FROM a WHERE i=$1");
202 auto prepare2 = con.Prepare("SELECT COUNT(*) FROM a WHERE i>$1");
203
204 result = prepare->Execute(12);
205 REQUIRE(CHECK_COLUMN(result, 0, {1}));
206 result = prepare2->Execute(11);
207 REQUIRE(CHECK_COLUMN(result, 0, {2}));
208 // now if we rollback our prepared statements are invalidated
209 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
210
211 REQUIRE_FAIL(prepare->Execute(12));
212 REQUIRE_FAIL(prepare2->Execute(11));
213}
214
215TEST_CASE("Test prepared statement parameter counting", "[api]") {
216 unique_ptr<QueryResult> result;
217 DuckDB db(nullptr);
218 Connection con(db);
219
220 auto p0 = con.Prepare("SELECT 42");
221 REQUIRE(p0->success);
222 REQUIRE(p0->n_param == 0);
223
224 auto p1 = con.Prepare("SELECT $1::int");
225 REQUIRE(p1->success);
226 REQUIRE(p1->n_param == 1);
227
228 p1 = con.Prepare("SELECT ?::int");
229 REQUIRE(p1->success);
230 REQUIRE(p1->n_param == 1);
231
232 auto p2 = con.Prepare("SELECT $1::int");
233 REQUIRE(p2->success);
234 REQUIRE(p2->n_param == 1);
235
236 auto p3 = con.Prepare("SELECT ?::int, ?::string");
237 REQUIRE(p3->success);
238 REQUIRE(p3->n_param == 2);
239
240 auto p4 = con.Prepare("SELECT $1::int, $2::string");
241 REQUIRE(p4->success);
242 REQUIRE(p4->n_param == 2);
243
244 auto p5 = con.Prepare("SELECT $2::int, $2::string");
245 REQUIRE(!p5->success);
246}
247