1 package com.omnisci.jdbc;
3 import static org.junit.Assert.*;
5 import org.junit.After;
6 import org.junit.Before;
7 import org.junit.Ignore;
11 import java.util.Properties;
15 static final String
url = PROPERTIES.getProperty(
"default_db_connection_url");
16 static final String
user = PROPERTIES.getProperty(
"default_super_user");
17 static final String
password = PROPERTIES.getProperty(
"default_user_password");
22 public void setUp() throws Exception {
23 Properties pt =
new Properties();
24 pt.setProperty(
"user",
user);
25 pt.setProperty(
"password",
password);
26 m_conn = DriverManager.getConnection(
url, pt);
36 Statement statement = m_conn.createStatement();
38 statement.executeUpdate(PROPERTIES.getProperty(
"drop_base_t1"));
39 statement.executeUpdate(PROPERTIES.getProperty(
"create_base_t1"));
40 String prepare_insert_statement =
41 "insert into test_prepare_table1 (cc, bb, aa) values (?,?,?)";
42 PreparedStatement pr1 = m_conn.prepareStatement(prepare_insert_statement);
50 ResultSet rs = statement.executeQuery(
"select cc, bb, aa from test_prepare_table1");
53 int returned_cc = rs.getInt(
"cc");
54 assertEquals(cc_i, returned_cc);
55 int returned_bb = rs.getInt(
"bb");
56 assertEquals(bb_i, returned_bb);
57 int returned_aa = rs.getInt(
"aa");
58 assertEquals(aa_i, returned_aa);
60 statement.executeUpdate(PROPERTIES.getProperty(
"drop_base_t1"));
63 statement.executeUpdate(PROPERTIES.getProperty(
"drop_base_t2"));
64 statement.executeUpdate(PROPERTIES.getProperty(
"create_base_t2"));
65 String prepare_insert_statement2 =
66 "insert into test_prepare_table2 (cc, bb, aa) values (?,?,?)";
67 PreparedStatement pr2 = m_conn.prepareStatement(prepare_insert_statement2);
70 String cc_s =
"10000";
71 pr2.setString(1, cc_s);
72 pr2.setString(2, bb_s);
73 pr2.setString(3, aa_s);
76 statement.executeQuery(
"select cc, bb, aa from test_prepare_table2");
79 String returned_cc = rs2.getString(
"cc");
80 assertEquals(cc_s, returned_cc);
81 String returned_bb = rs2.getString(
"bb");
82 assertEquals(bb_s, returned_bb);
83 String returned_aa = rs2.getString(
"aa");
84 assertEquals(aa_s, returned_aa);
86 statement.executeUpdate(PROPERTIES.getProperty(
"drop_base_t2"));
92 Statement statement = m_conn.createStatement();
93 statement.executeUpdate(PROPERTIES.getProperty(
"drop_base_t3"));
94 statement.executeUpdate(PROPERTIES.getProperty(
"create_base_t3"));
95 statement.executeQuery(
"insert into test_prepare_table3 values(1, 1.1, 'one')");
96 ResultSetMetaData md = null;
98 PreparedStatement pr_select_no_params =
99 m_conn.prepareStatement(
"select aa, bb, cc from test_prepare_table3");
100 md = pr_select_no_params.getMetaData();
102 assertEquals(md.getColumnCount(), 3);
103 assertEquals(md.getColumnName(1),
"aa");
104 assertEquals(md.getColumnType(1), Types.INTEGER);
105 assertEquals(md.getColumnType(2), Types.DOUBLE);
107 PreparedStatement pr_select_with_params = m_conn.prepareStatement(
108 "select bb, aa from test_prepare_table3 where cc <> ? and aa > ?");
109 md = pr_select_with_params.getMetaData();
111 assertEquals(md.getColumnCount(), 2);
112 assertEquals(md.getColumnName(1),
"bb");
113 assertEquals(md.getColumnType(1), Types.DOUBLE);
114 assertEquals(md.getColumnType(2), Types.INTEGER);
116 String commented_sql_statement =
" \n \n"
122 +
"-- comment inside comment\n"
124 +
"-- another /*tricky edge case/*\n"
125 +
" select bb, aa from test_prepare_table3 where cc <> ? and aa > ?";
126 PreparedStatement pr_select_with_params_and_comments =
127 m_conn.prepareStatement(commented_sql_statement);
128 md = pr_select_with_params_and_comments.getMetaData();
130 assertEquals(md.getColumnCount(), 2);
132 PreparedStatement pr_insert = m_conn.prepareStatement(
133 "insert into test_prepare_table3(aa, bb, cc) values (?, ?, ?)");
134 md = pr_insert.getMetaData();
137 PreparedStatement pr_insert_from_select = m_conn.prepareStatement(
138 "insert into test_prepare_table3(aa, bb, cc) select aa, bb, cc from test_prepare_table3 where cc <> ?");
139 md = pr_insert_from_select.getMetaData();
142 statement.executeUpdate(PROPERTIES.getProperty(
"drop_base_t3"));
145 private void formBatch(
int start,
int end, PreparedStatement ps, Integer[][] ia)
147 for (
int i = start; i < end; ++i) {
149 ps.setTimestamp(2,
new Timestamp(System.currentTimeMillis()));
151 ps.setArray(3, m_conn.createArrayOf(
"INT", ia[i]));
153 ps.setNull(3, Types.ARRAY);
161 Statement stmt = m_conn.createStatement();
162 stmt.executeUpdate(
"DROP TABLE IF EXISTS batch_tbl");
163 stmt.executeUpdate(
"CREATE TABLE batch_tbl ("
167 Integer[][] ia = {{1, 10, 100}, {null}, null, {543, null, null, 123, 543}, {17}};
168 Integer[][] ia2 = {{12345, 12, null, 1234, null}, {1, -1, -2, 2, 3, -3, -4, 4, -5}};
169 PreparedStatement ps =
170 m_conn.prepareStatement(
"INSERT INTO batch_tbl VALUES(?, ?, ?)");
172 int[]
result = ps.executeBatch();
173 for (
int i : result) {
179 result = ps.executeBatch();
180 assertEquals(result.length, 1);
181 assertEquals(result[0], 1);
184 ResultSet rs = stmt.executeQuery(
"SELECT i, ia FROM batch_tbl");
187 assertEquals(rs.getInt(
"i"), i);
189 assertNull(rs.getArray(
"ia"));
191 assertArrayEquals((Integer[]) rs.getArray(
"ia").getArray(), ia[i]);
200 Statement stmt = m_conn.createStatement();
201 stmt.executeUpdate(
"DROP TABLE IF EXISTS partial_batch_tbl");
202 stmt.executeUpdate(
"CREATE TABLE partial_batch_tbl ("
205 +
"s TEXT ENCODING DICT,"
207 PreparedStatement ps = m_conn.prepareStatement(
"INSERT INTO partial_batch_tbl(i, ia)"
209 Integer[] is = {1, 2, null, 4};
210 Integer[][] ias = {{1, 2, 3}, {10, 20, 30}, null, {1000, 2000, 3000}};
211 String[] ss = {null, null,
"One",
"Two"};
212 String[] lss = {null, null,
"LINESTRING (0 1,2 2)",
"LINESTRING (4 1,5 3)"};
214 ps.setArray(2, m_conn.createArrayOf(
"INT", ias[0]));
217 ps.setArray(2, m_conn.createArrayOf(
"INT", ias[1]));
221 ps = m_conn.prepareStatement(
"INSERT INTO partial_batch_tbl(s, ls)"
223 ps.setString(1, ss[2]);
224 ps.setString(2, lss[2]);
228 ps = m_conn.prepareStatement(
"INSERT INTO partial_batch_tbl(i, ia, s, ls)"
229 +
" VALUES(?, ?, ?, ?)");
231 ps.setArray(2, m_conn.createArrayOf(
"INT", ias[3]));
232 ps.setString(3, ss[3]);
233 ps.setString(4, lss[3]);
238 ResultSet rs = stmt.executeQuery(
"SELECT i, ia, s, ls FROM partial_batch_tbl");
241 assertEquals(rs.getObject(
"i"), is[i] == null ? null : is[i].longValue());
242 if (ias[i] == null) {
243 assertNull(rs.getArray(
"ia"));
245 assertArrayEquals((Integer[]) rs.getArray(
"ia").getArray(), ias[i]);
247 assertEquals(rs.getString(
"s"), ss[i]);
248 assertEquals(rs.getString(
"ls"), lss[i]);
static final String password
static Properties PROPERTIES
void formBatch(int start, int end, PreparedStatement ps, Integer[][] ia)