| /* Copyright (c) 2001-2009, The HSQL Development Group |
| * All rights reserved. |
| * |
| * Redistribution and use in source and binary forms, with or without |
| * modification, are permitted provided that the following conditions are met: |
| * |
| * Redistributions of source code must retain the above copyright notice, this |
| * list of conditions and the following disclaimer. |
| * |
| * Redistributions in binary form must reproduce the above copyright notice, |
| * this list of conditions and the following disclaimer in the documentation |
| * and/or other materials provided with the distribution. |
| * |
| * Neither the name of the HSQL Development Group nor the names of its |
| * contributors may be used to endorse or promote products derived from this |
| * software without specific prior written permission. |
| * |
| * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" |
| * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE |
| * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE |
| * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, |
| * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, |
| * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, |
| * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; |
| * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND |
| * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT |
| * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS |
| * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
| */ |
| |
| |
| package org.hsqldb.test; |
| |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.Statement; |
| import java.util.TimeZone; |
| import java.sql.SQLException; |
| |
| /** |
| * Date Test Case. |
| */ |
| public class TestDatetimeSimple extends junit.framework.TestCase { |
| |
| static { |
| try { |
| Class.forName("org.hsqldb.jdbcDriver"); |
| } catch (ClassNotFoundException cnfe) { |
| throw new RuntimeException( |
| "<clinit> failed. JDBC Driver class not in CLASSPATH"); |
| } |
| } |
| |
| public void testSimple() throws SQLException { |
| |
| Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:m", |
| "SA", ""); |
| ResultSet rs; |
| PreparedStatement ps; |
| Statement st = conn.createStatement(); |
| |
| st.executeUpdate("CREATE TABLE t(i int, d date)"); |
| st.executeUpdate("INSERT INTO t VALUES(1, '2008-11-27')"); |
| |
| rs = st.executeQuery("SELECT d FROM t"); |
| |
| rs.next(); |
| System.out.println("Object: " + rs.getObject("d") // |
| + " ; Timestamp: " + rs.getTimestamp("d") // |
| + " ; Date: " + rs.getDate("d") // |
| + " ; String: " + rs.getString("d")); |
| rs.close(); |
| |
| rs = st.executeQuery("SELECT count(*) c FROM t WHERE d = " |
| + "'2008-11-27'"); |
| |
| rs.next(); |
| System.out.println("Match? " + (rs.getInt("c") > 0)); |
| st.executeUpdate("DELETE FROM t"); |
| |
| /* This is prohibited: |
| |
| st.executeUpdate("INSERT INTO t VALUES(2, '2008-11-27 0:00:00')"); |
| |
| Q: Do we want to prohibit this, even though we permit the same |
| usage with PreparedStatement using a Timestamp, as follows? |
| |
| A: In the disallowed case, a String that is not a data string is used, |
| while in the other case, a timestamp object is used. It follows the |
| cast specification, which requires the String to be a valid date |
| string, and allows casting from a TIMESTAMP object to DATE |
| |
| */ |
| ps = conn.prepareStatement("INSERT INTO t VALUES(3, ?)"); |
| |
| ps.setTimestamp(1, java.sql.Timestamp.valueOf("2008-10-27 0:00:00")); |
| ps.execute(); |
| ps.close(); |
| |
| rs = st.executeQuery("SELECT d FROM t"); |
| |
| rs.next(); |
| System.out.println("Object: " + rs.getObject("d") // |
| + " ; Date: " + rs.getDate("d") // |
| + " ; Timestamp: " + rs.getTimestamp("d") + // |
| "; String: " + rs.getString("d")); |
| rs.close(); |
| |
| rs = st.executeQuery("SELECT count(*) c FROM t WHERE d = " |
| + "'2008-10-27'"); |
| |
| /* FRED: When the DATE value is inserted with a TIMESTAMP, |
| * all matches using a date fail. The query here fails regardless |
| * of what date I use. */ |
| rs.next(); |
| System.out.println("Match? " + (rs.getInt("c") > 0)); |
| |
| /** ******** TIMESTAMP COL BELOW ********* */ |
| st.executeUpdate("CREATE TABLE t2(i int, ts timestamp)"); |
| /* These all fail: |
| st.executeUpdate("INSERT INTO t2 VALUES(1, '2008-11-27')"); |
| st.executeUpdate("INSERT INTO t2 VALUES(1, timestamp '2008-11-27')"); |
| in both cases, the string is not a valid timestamp string |
| */ |
| st.executeUpdate( |
| "INSERT INTO t2 VALUES(1, timestamp '2008-11-27 12:30:00')"); |
| st.executeUpdate("INSERT INTO t2 VALUES(1, '2008-11-27 12:30:00')"); |
| |
| /** FOLLOWING ALL WORK AS EXPECTED: */ |
| ps = conn.prepareStatement("INSERT INTO t2 VALUES(2, ?)"); |
| |
| ps.setTimestamp(1, java.sql.Timestamp.valueOf("2008-10-27 0:00:00")); |
| ps.execute(); |
| ps.close(); |
| |
| rs = st.executeQuery("SELECT ts FROM t2"); |
| |
| rs.next(); |
| System.out.println("Object: " + rs.getObject("ts") // |
| + " ; Timestamp: " + rs.getTimestamp("ts") // |
| + " ; Date: " + rs.getObject("ts") // |
| + "; String: " + rs.getString("ts")); |
| rs.close(); |
| st.executeUpdate("SHUTDOWN"); |
| conn.close(); |
| } |
| |
| public void testValues() throws SQLException { |
| |
| Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:m", |
| "SA", ""); |
| ResultSet rs; |
| PreparedStatement ps; |
| String s; |
| Object o; |
| java.sql.Date d; |
| java.sql.Timestamp ts; |
| Statement st = conn.createStatement(); |
| |
| st.executeUpdate("CREATE TABLE t(d date)"); |
| st.executeUpdate("INSERT INTO t VALUES('2008-11-27')"); |
| |
| rs = st.executeQuery("SELECT d FROM t"); |
| |
| rs.next(); |
| |
| s = rs.getString("d"); |
| o = rs.getObject("d"); |
| d = rs.getDate("d"); |
| ts = rs.getTimestamp("d"); |
| |
| System.out.println("2008-11-27 INSERTED" + "\n String: " + s |
| + "\n Object: " + o + "\n Date: " + dump(d) |
| + "\n Timestamp: " + dump(ts) + '\n'); |
| rs.close(); |
| |
| rs = st.executeQuery("CALL CURRENT_DATE"); |
| |
| rs.next(); |
| |
| o = rs.getObject(1); |
| d = rs.getDate(1); |
| s = rs.getString(1); |
| ts = rs.getTimestamp(1); |
| |
| System.out.println("CURRENT_DATE @" + new java.util.Date() |
| + "\n String: " + s + "\n Object: " + o |
| + "\n Date: " + dump(d) + "\n Timestamp: " |
| + dump(ts) + '\n'); |
| rs.close(); |
| |
| rs = st.executeQuery("CALL LOCALTIMESTAMP"); |
| |
| rs.next(); |
| |
| o = rs.getObject(1); |
| s = rs.getString(1); |
| ts = rs.getTimestamp(1); |
| |
| System.out.println("LOCALTIMESTAMP @" + new java.util.Date() |
| + "\n String: " + s + "\n Object: " + o |
| + "\n Timestamp: " + dump(ts) + '\n'); |
| rs.close(); |
| |
| rs = st.executeQuery("CALL CURRENT_TIMESTAMP"); |
| |
| rs.next(); |
| |
| s = rs.getString(1); |
| o = rs.getObject(1); |
| ts = rs.getTimestamp(1); |
| |
| System.out.println("CURRENT_TIMESTAMP @" + new java.util.Date() |
| + "\n String: " + s + "\n Object: " + o |
| + "\n Timestamp: " + dump(ts) + '\n'); |
| rs.close(); |
| st.executeUpdate("SHUTDOWN"); |
| conn.close(); |
| } |
| |
| static public String dump(java.sql.Timestamp t) { |
| return "String (" + t.toString() + "), GMTString (" + t.toGMTString() |
| + "), LocalString (" + t.toLocaleString() + ')'; |
| } |
| |
| static public String dump(java.sql.Date d) { |
| return "String (" + d.toString() + "), GMTString (" + d.toGMTString() |
| + "), LocalString (" + d.toLocaleString() + ')'; |
| } |
| |
| public static void main(String[] argv) { |
| |
| TestDatetimeSimple testA = new TestDatetimeSimple(); |
| String[] zones = { "GMT-05:00" }; |
| |
| try { |
| for (int i = 0; i < zones.length; i++) { |
| TimeZone timeZone = TimeZone.getTimeZone(zones[i]); |
| |
| TimeZone.setDefault(timeZone); |
| testA.testSimple(); |
| testA.testValues(); |
| } |
| } catch (SQLException e) { |
| e.printStackTrace(); |
| } |
| } |
| } |
