OmniSciDB  7bf56492aa
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Pages
OmniSciStatementTest.java
Go to the documentation of this file.
1 package com.omnisci.jdbc;
2 
3 import static org.junit.Assert.*;
4 
5 import static java.lang.Math.ulp;
6 
7 import org.junit.After;
8 import org.junit.Before;
9 import org.junit.Ignore;
10 import org.junit.Test;
11 
12 import java.sql.*;
13 import java.text.DateFormat;
14 import java.text.SimpleDateFormat;
15 import java.util.Properties;
16 import java.util.TimeZone;
17 
18 public class OmniSciStatementTest {
19  static Properties PROPERTIES = new Property_loader("connection.properties");
20  static final String url = PROPERTIES.getProperty("default_db_connection_url");
21  static final String user = PROPERTIES.getProperty("default_super_user");
22  static final String password = PROPERTIES.getProperty("default_user_password");
23 
24  private Connection m_conn = null;
25 
26  @Before
27  public void setUp() throws Exception {
28  Properties pt = new Properties();
29  pt.setProperty("user", user);
30  pt.setProperty("password", password);
31  m_conn = DriverManager.getConnection(url, pt);
32  }
33 
34  @After
35  public void tearDown() throws Exception {
36  m_conn.close();
37  }
38 
39  static String sql_drop_tbl_tm = "drop table if exists test_jdbc_tm_tble";
40 
41  static String sql_create_tbl_tm = "CREATE table test_jdbc_tm_tble("
42  + "m_timestamp TIMESTAMP,"
43  + "m_timestamp_3 TIMESTAMP(3),"
44  + "m_timestamp_6 TIMESTAMP(6),"
45  + "m_timestamp_9 TIMESTAMP(9))";
46 
47  static String sql_insert_tm_1 =
48  "insert into test_jdbc_tm_tble values ('1910-01-01 00:00:10', '1910-01-01 00:00:10.001', '1910-01-01 00:00:10.555556', '1910-01-01 00:00:10.999999999')";
49 
50  static String sql_insert_tm_2 =
51  "insert into test_jdbc_tm_tble values ('1969-12-31 23:00:00', '1969-12-31 23:00:00.001', '1969-12-31 23:00:00.000001', '1969-12-31 23:00:00.000000001')";
52 
53  static String sql_insert_tm_3 =
54  "insert into test_jdbc_tm_tble values ('1970-01-01 00:00:10', '1970-01-01 00:00:10.001', '1970-01-01 00:00:10.000001', '1970-01-01 00:00:10.000000001')";
55 
56  static String insert_prepare_tm =
57  "insert into test_jdbc_tm_tble (m_timestamp, m_timestamp_3, m_timestamp_6, m_timestamp_9) values (?, ?, ?, ?)";
58 
59  // Note 2262-04-11 23:47:16.85 is very close to the limit for Timestamp(9)
60  static String sql_insert_tm_4 =
61  "insert into test_jdbc_tm_tble values ('2970-01-01 00:00:10', '2970-01-01 00:00:10.001', '2970-01-01 00:00:10.000001', '2262-04-11 23:47:16.850000001')";
62  static String sql_select_tm = "select * from test_jdbc_tm_tble";
63 
64  @Ignore
65  public void insert_times() throws Exception {
66  Statement statement = m_conn.createStatement();
67  statement.executeUpdate(sql_drop_tbl_tm);
68  statement.executeUpdate(sql_create_tbl_tm);
69 
70  TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
71  DateFormat date_format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSS");
72 
73  java.util.Date date_tm = date_format.parse("1918-11-11 11:11:00.000");
74  Timestamp tm = new Timestamp(date_tm.getTime());
75 
76  date_tm = date_format.parse("1918-11-11 11:11:00.001");
77  Timestamp tm_3 = new Timestamp(date_tm.getTime());
78 
79  date_tm = date_format.parse("1918-11-11 11:11:00.000");
80  Timestamp tm_6 = new Timestamp(date_tm.getTime());
81  tm_6.setNanos(999999000);
82 
83  date_tm = date_format.parse("1918-11-11 11:11:00.000");
84  Timestamp tm_9 = new Timestamp(date_tm.getTime());
85  tm_9.setNanos(123456789);
86 
87  PreparedStatement pr = m_conn.prepareStatement(insert_prepare_tm);
88  pr.setTimestamp(1, tm);
89  pr.setTimestamp(2, tm_3);
90  pr.setTimestamp(3, tm_6);
91  pr.setTimestamp(4, tm_9);
92 
93  pr.executeUpdate();
94 
95  ResultSet rs = statement.executeQuery(sql_select_tm);
96  while (rs.next()) {
97  Timestamp r_tm = rs.getTimestamp("m_timestamp");
98  assertTrue(r_tm.equals(tm));
99  Timestamp r_tm3 = rs.getTimestamp("m_timestamp_3");
100  assertTrue(r_tm3.equals(tm_3));
101  Timestamp r_tm6 = rs.getTimestamp("m_timestamp_6");
102  assertTrue(r_tm6.equals(tm_6));
103  Timestamp r_tm9 = rs.getTimestamp("m_timestamp_9");
104  assertTrue(r_tm9.equals(tm_9));
105  }
106 
107  statement.executeUpdate(sql_drop_tbl_tm);
108  }
109 
110  @Ignore
111  public void create_times() throws Exception {
112  Statement statement = m_conn.createStatement();
113  statement.executeUpdate(sql_drop_tbl_tm);
114  statement.executeUpdate(sql_create_tbl_tm);
115 
116  statement.executeUpdate(sql_insert_tm_1);
117  statement.executeUpdate(sql_insert_tm_2);
118  statement.executeUpdate(sql_insert_tm_3);
119  statement.executeUpdate(sql_insert_tm_4);
120  ResultSet rs = statement.executeQuery(sql_select_tm);
121 
122  for (int i = 0; rs.next(); ++i) {
123  TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
124  Timestamp timestamp = rs.getTimestamp("m_timestamp");
125 
126  Timestamp timestamp_3 = rs.getTimestamp("m_timestamp_3");
127  Timestamp timestamp_6 = rs.getTimestamp("m_timestamp_6");
128  Timestamp timestamp_9 = rs.getTimestamp("m_timestamp_9");
129  if (i == 0) {
130  assertEquals("1910-01-01 00:00:10.0", timestamp.toString());
131  assertEquals("1910-01-01 00:00:10.001", timestamp_3.toString());
132  assertEquals("1910-01-01 00:00:10.555556", timestamp_6.toString());
133  assertEquals("1910-01-01 00:00:10.999999999", timestamp_9.toString());
134  }
135  if (i == 1) {
136  assertEquals("1969-12-31 23:00:00.0", timestamp.toString());
137  assertEquals("1969-12-31 23:00:00.001", timestamp_3.toString());
138  assertEquals("1969-12-31 23:00:00.000001", timestamp_6.toString());
139  assertEquals("1969-12-31 23:00:00.000000001", timestamp_9.toString());
140  }
141  if (i == 2) {
142  assertEquals("1970-01-01 00:00:10.0", timestamp.toString());
143  assertEquals("1970-01-01 00:00:10.001", timestamp_3.toString());
144  assertEquals("1970-01-01 00:00:10.000001", timestamp_6.toString());
145  assertEquals("1970-01-01 00:00:10.000000001", timestamp_9.toString());
146  }
147  if (i == 3) {
148  assertEquals("2970-01-01 00:00:10.0", timestamp.toString());
149  assertEquals("2970-01-01 00:00:10.001", timestamp_3.toString());
150  assertEquals("2970-01-01 00:00:10.000001", timestamp_6.toString());
151  assertEquals("2262-04-11 23:47:16.850000001", timestamp_9.toString());
152  }
153  }
154 
155  statement.executeUpdate(sql_drop_tbl_tm);
156  }
157 
158  static String sql_drop_tbl = "drop table if exists test_jdbc_types_tble";
159 
160  static String sql_create_tbl = "CREATE table test_jdbc_types_tble("
161  + "m_decimal DECIMAL(8,3),"
162  + "m_int int,"
163  + "m_float float,"
164  + "m_double double,"
165  + "m_bigint BIGINT,"
166  + "m_smallint SMALLINT,"
167  + "m_tinyint TINYINT,"
168  + "m_boolean BOOLEAN,"
169  + "m_text_encoded TEXT ENCODING DICT,"
170  + "m_text_encoded_none TEXT ENCODING NONE,"
171  + "m_time TIME,"
172  + "m_date DATE,"
173  + "m_timestamp TIMESTAMP)";
174 
175  static String sql_insert = "insert into test_jdbc_types_tble values ("
176  + "12345.123" + +Integer.MAX_VALUE + "," + Integer.MAX_VALUE + ","
177  + Float.MAX_VALUE + "," + Double.MAX_VALUE + "," + Long.MAX_VALUE + ","
178  + Short.MAX_VALUE + "," + Byte.MAX_VALUE + ","
179  + "\'0\',"
180  + "'String 1 - encoded', 'String 2 - not encoded', '00:00:00', '1970-01-01', '1970-01-01 00:00:00')";
181 
182  static String sql_select_all = "select * from test_jdbc_types_tble";
183 
184  @Test
185  public void escape_function1() throws Exception {
186  Statement statement = m_conn.createStatement();
187  TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
188 
189  String d_select =
190  "select {fn cos(1)} as m_cos, {d '1999-01-09'} as m_date, {t '20:00:03'} as m_time, {ts '1990-01-09 20:00:03'} as m_timestamp, {fn week({d '2005-01-24'})} as m_week";
191  ResultSet rs = statement.executeQuery(d_select);
192  for (int i = 0; rs.next(); ++i) {
193  Date r_date = rs.getDate("m_date");
194  assertEquals("1999-01-09", r_date.toString());
195  Time r_time = rs.getTime("m_time");
196  assertEquals("20:00:03", r_time.toString());
197  Timestamp ts_time = rs.getTimestamp("m_timestamp");
198  assertEquals("1990-01-09 20:00:03.0", ts_time.toString());
199  double m_cos = rs.getDouble("m_cos");
200  assertEquals(Double.compare(m_cos, 0.5403023058681398), 0);
201  int m_week = rs.getInt("m_week");
202  assertEquals(m_week, 5);
203  }
204  d_select = "select {fn FLOOR(-1 * {fn dayofmonth({d '1990-01-31'})})} as WWW";
205  rs = statement.executeQuery(d_select);
206  for (int i = 0; rs.next(); ++i) {
207  int www = rs.getInt("WWW");
208  assertEquals(-31, www);
209  }
210  d_select =
211  "select {fn FLOOR(-1 * {fn dayofmonth(cast('1990-01-31' as date))})} as XXX";
212  rs = statement.executeQuery(d_select);
213  for (int i = 0; rs.next(); ++i) {
214  int xxx = rs.getInt("XXX");
215  assertEquals(-31, xxx);
216  }
217 
218  d_select = "select {fn floor(1.005)} as YYY";
219  rs = statement.executeQuery(d_select);
220  for (int i = 0; rs.next(); ++i) {
221  float yyy = rs.getFloat("YYY");
222  assertEquals(Float.compare(1.0F, yyy), 0);
223  }
224  d_select = "select {fn floor(1.005)} as YYY limit 1000 {";
225  try {
226  statement.executeQuery(d_select);
227  assertTrue(false);
228  } catch (RuntimeException rE) {
229  }
230 
231  d_select = "select ' {fn floor(1.005)} as YYY limit 1000 {";
232  try {
233  statement.executeQuery(d_select);
234  assertTrue(false);
235  } catch (RuntimeException rE) {
236  }
237 
238  d_select = "select ' {fn floor(1.005)} as YYY limit 1000 }";
239  try {
240  statement.executeQuery(d_select);
241  assertTrue(false);
242  } catch (RuntimeException rE) {
243  }
244 
245  String sql_drop_tester = "drop table if exists tester";
246  statement.executeUpdate(sql_drop_tester);
247  String sql_create_tester = "CREATE table tester(Connection_start TIMESTAMP)";
248  statement.executeUpdate(sql_create_tester);
249 
250  String sql_insert_tester1 = "insert into tester values ('2018-11-08 12:19:59')";
251  statement.executeUpdate(sql_insert_tester1);
252 
253  String sql_insert_tester2 = "insert into tester values ('2018-11-08 12:29:59')";
254  statement.executeUpdate(sql_insert_tester2);
255  String sql_insert_tester3 = "insert into tester values ('2018-11-08 12:39:59')";
256  statement.executeUpdate(sql_insert_tester3);
257  String sql_insert_tester4 = "insert into tester values ('2018-11-09 23:59:59')";
258  statement.executeUpdate(sql_insert_tester4);
259 
260  String x_select =
261  "SELECT TOP 1000 sum(1) AS sum_Number_of_Records_ok, {fn TIMESTAMPADD(SQL_TSI_HOUR, EXTRACT(HOUR FROM tester.Connection_Start), CAST(tester.Connection_Start as DATE))} AS thr_Connection_Start_ok FROM tester Where ((tester.Connection_Start >= {ts '2018-11-01 00:00:00'}) AND (tester.Connection_Start <= {ts '2018-11-08 23:59:59'})) GROUP BY 2";
262  rs = statement.executeQuery(x_select);
263 
264  for (int i = 0; rs.next(); ++i) {
265  int r_count = rs.getInt("sum_Number_of_Records_ok");
266  assertEquals(r_count, 3);
267  Timestamp ts_time = rs.getTimestamp("thr_Connection_Start_ok");
268  assertEquals(ts_time.toString(), "2018-11-08 12:00:00.0");
269  }
270 
271  // Test the simple date transformation in OmniSciStatment.
272  String d_simple_quarter = "select quarter(Connection_start) as m_quarter from tester";
273  rs = statement.executeQuery(d_simple_quarter);
274  for (int i = 0; rs.next(); ++i) {
275  int r_quarter = rs.getInt("m_quarter");
276  assertEquals(4, r_quarter);
277  }
278  d_simple_quarter =
279  "select quarter(cast('2019-04-03' as date)) as m_quarter from tester";
280  rs = statement.executeQuery(d_simple_quarter);
281  for (int i = 0; rs.next(); ++i) {
282  int r_quarter = rs.getInt("m_quarter");
283  assertEquals(2, r_quarter);
284  }
285  }
286 
287  @Test
288  public void escape_function2() throws Exception {
289  Statement statement = m_conn.createStatement();
290  TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
291 
292  String d_quarter = "select {fn quarter({d '2005-01-24'})} as m_quarter";
293 
294  ResultSet rs = statement.executeQuery(d_quarter);
295  for (int i = 0; rs.next(); ++i) {
296  int r_quarter = rs.getInt("m_quarter");
297  assertEquals(1, r_quarter);
298  }
299 
300  String d_dayofyear = "select {fn DAYOFYEAR({d '2005-01-24'})} as m_dayofyear";
301 
302  rs = statement.executeQuery(d_dayofyear);
303  for (int i = 0; rs.next(); ++i) {
304  int r_dayofyear = rs.getInt("m_dayofyear");
305  assertEquals(24, r_dayofyear);
306  }
307 
308  String d_dayofweek = "select {fn dayofweek({d '2005-01-24'})} as m_dayofweek";
309 
310  rs = statement.executeQuery(d_dayofweek);
311  for (int i = 0; rs.next(); ++i) {
312  int r_dayofweek = rs.getInt("m_dayofweek");
313  assertEquals(2, r_dayofweek);
314  }
315 
316  String d_trunc = "select {fn truncate(2.55555, 2)} as m_trunc";
317  rs = statement.executeQuery(d_trunc);
318  for (int i = 0; rs.next(); ++i) {
319  float r_trunc = rs.getFloat("m_trunc");
320  assertEquals(Float.compare(2.55F, r_trunc), 0);
321  }
322  String d_length = "select {fn length('fff')} as m_length";
323  rs = statement.executeQuery(d_length);
324  for (int i = 0; rs.next(); ++i) {
325  int r_length = rs.getInt("m_length");
326  assertEquals(3, r_length);
327  }
328  }
329 
330  @Test
331  public void create_types() throws Exception {
332  Statement statement = m_conn.createStatement();
333  statement.executeQuery(sql_drop_tbl);
334  statement.executeUpdate(sql_create_tbl);
335  statement.executeUpdate(sql_insert);
336  statement.executeUpdate(sql_insert);
337  ResultSet rs = statement.executeQuery(sql_select_all);
338 
339  int i = 0;
340  for (; rs.next(); ++i) {
341  int r_int = rs.getInt("m_int");
342  assertEquals(Integer.MAX_VALUE, r_int);
343  float r_float = rs.getFloat("m_float");
344  float delta_f = ulp(Float.MAX_VALUE);
345  assertEquals(Float.MAX_VALUE, r_float, delta_f);
346 
347  double r_double = rs.getDouble("m_double");
348  double delta_d = ulp(Double.MAX_VALUE);
349  assertEquals(Double.MAX_VALUE, r_double, delta_d);
350 
351  long r_long = rs.getLong("m_bigint");
352  assertEquals(Long.MAX_VALUE, r_long);
353 
354  short r_short = rs.getShort("m_smallint");
355  assertEquals(Short.MAX_VALUE, r_short);
356 
357  byte r_byte = (byte) rs.getShort("m_tinyint");
358  assertEquals(Byte.MAX_VALUE, r_byte);
359 
360  String decimal_str = rs.getString("m_decimal");
361  assertEquals("12345.123", decimal_str);
362 
363  // byte r_boolean = rs.getByte("m_boolean"); Not supported!
364  byte r_boolean = (byte) rs.getShort("m_boolean");
365  assertEquals(0, r_boolean);
366 
367  String r_text_encoded = rs.getString("m_text_encoded");
368  assertEquals("String 1 - encoded", r_text_encoded);
369 
370  String r_text_encoded_none = rs.getString("m_text_encoded_none");
371  assertEquals("String 2 - not encoded", r_text_encoded_none);
372 
373  // Set the tz to GMT to help with compares
374  TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
375 
376  Timestamp r_timestamp = rs.getTimestamp("m_timestamp");
377  assertEquals("1970-01-01 00:00:00.0", r_timestamp.toString());
378 
379  Date r_date = rs.getDate("m_date");
380  assertEquals("1970-01-01", r_date.toString());
381 
382  Time r_time = rs.getTime("m_time");
383  assertEquals("00:00:00", r_time.toString());
384  }
385 
386  assertEquals(2, i);
387 
388  statement.executeUpdate(sql_drop_tbl);
389  }
390 }