Hi All, 

I am testing the date, time, timestamp literal on h2 and found 
inconsistency in ResultSetMetaData when creating date literal using "DATE 
'1970-01-01'" vs "{d '1970-01-01'}"
This is similar to issue raised in 
https://groups.google.com/forum/#!searchin/h2-database/jdbc$20escape$20syntax/h2-database/1PEmxTjwQmg/Hn_9DRExMkUJ

Test Code: 
        ResultSet rs = stat.executeQuery("select {d '1970-01-01'} , date 
'1970-01-01' from dual");
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1 ; i <= rsmd.getColumnCount() ; i ++){
            System.out.println("Column Name:" + rsmd.getColumnName(i) + "| 
Column Type:" + rsmd.getColumnType(i) + "| Column Type Name:" + 
rsmd.getColumnTypeName(i));                }

Output:
     Column Name:'1970-01-01'| Column Type:12| Column Type Name:VARCHAR
     Column Name:DATE '1970-01-01'| Column Type:91| Column Type Name:DATE

Expected Output: 
     Same column type should be produce when using either option to create 
date literal. 

Similar problem is using JDBC escape syntax for time and timestamp

Thank you.

Rgds,
jay

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
/*
 * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0,
 * and the EPL 1.0 (http://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.samples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;

/**
 * A very simple class that shows how to load the driver, create a database,
 * create a table, and insert some data.
 */
public class TestDateTimeLiteral {

    /**
     * Called when ran from command line.
     *
     * @param args ignored
     */
    public static void main(String... args) throws Exception {
        // delete the database named 'test' in the user home directory
        DeleteDbFiles.execute("~", "test", true);

        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/test");
        Statement stat = conn.createStatement();

        // this line would initialize the database
        // from the SQL script file 'init.sql'
        // stat.execute("runscript from 'init.sql'");
        System.out.println("Test Date");
        ResultSet rs = stat.executeQuery("select {d '1970-01-01'} , date '1970-01-01' from dual");
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1 ; i <= rsmd.getColumnCount() ; i ++){
            System.out.println("Column Name:" + rsmd.getColumnName(i) + "| Column Type:" + rsmd.getColumnType(i) + "| Column Type Name:" + rsmd.getColumnTypeName(i));        	
        }

        System.out.println("\nTest time");
        rs = stat.executeQuery("select {t '23:59:59'} , time '23:59:59' from dual");
        rsmd = rs.getMetaData();
        for (int i = 1 ; i <= rsmd.getColumnCount() ; i ++){
            System.out.println("Column Name:" + rsmd.getColumnName(i) + "| Column Type:" + rsmd.getColumnType(i) + "| Column Type Name:" + rsmd.getColumnTypeName(i));        	
        }

        System.out.println("\nTest timestamp");
        rs = stat.executeQuery("select {ts '1970-01-01 23:59:59'} , timestamp '1970-01-01 23:59:59' from dual");
        rsmd = rs.getMetaData();
        for (int i = 1 ; i <= rsmd.getColumnCount() ; i ++){
            System.out.println("Column Name:" + rsmd.getColumnName(i) + "| Column Type:" + rsmd.getColumnType(i) + "| Column Type Name:" + rsmd.getColumnTypeName(i));        	
        }

        stat.close();
        conn.close();
    }

}

Reply via email to