[jira] [Created] (TRAFODION-2757) JDBC executeQuery() throws exception on the "with ... select" stmt

["haolin.song (JIRA)" <ji...@apache.org>]

haolin.song created TRAFODION-2757:
--------------------------------------

             Summary: JDBC executeQuery() throws exception on the "with ... select" stmt
                 Key: TRAFODION-2757
                 URL: https://issues.apache.org/jira/browse/TRAFODION-2757
             Project: Apache Trafodion
          Issue Type: Bug
          Components: client-jdbc-t4
    Affects Versions: 2.3-incubating, any
            Reporter: haolin.song


As shown below, EsgynDB supports the "with ... select" syntax. It runs fine in sqlci and trafci. But when using executeQuery() to run it in a JDBC program with our jdbc client, it throws an exception complaining that the query is a non-select sql statement.

This is seen on the AdvEnt2.3 20170912 daily build.

The query runs fine in sqlci or trafci:

>>drop schema if exists mytest cascade;

--- SQL operation complete.
>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>
>>create table mytable (a int, b int);

--- SQL operation complete.
>>insert into mytable values (1,1),(2,2),(3,3);

--- 3 row(s) inserted.
>>
>>with t1 as (select b from mytable where a > 1) select * from t1 where b < 3;

B
-----------

          2

--- 1 row(s) selected.

In a JDBC program, executeQuery() can't handle such a query:

$ cat mytest.java
import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.io.*;

public class mytest {

  public static void main(String[] args) {

    String stmtStr = null;
    Connection conn = null;
    Statement stmt = null;
    PreparedStatement pStmt = null;
    ResultSet rs = null;
    String my_catalog = null;
    String my_schema = null;
    int i;

    try {
      FileInputStream fs = new FileInputStream("./myprop");
      Properties props = new Properties();
      props.load(fs);

      String url = props.getProperty("url");
      my_catalog = props.getProperty("catalog");
      my_schema = props.getProperty("schema");

      Class.forName("org.trafodion.jdbc.t4.T4Driver"); // T4 driver
      conn = DriverManager.getConnection(url, props);
    } catch (Exception e) {
      e.printStackTrace();
    }

    try {
      stmt = conn.createStatement();
      stmt.execute("drop schema if exists " + my_catalog + "." + my_schema + " cascade");
      stmt.execute("create schema " + my_catalog + "." + my_schema);
      stmt.execute("set schema " + my_catalog + "." + my_schema);
      stmt.execute("create table mytable (a int, b int)");
      stmt.execute("insert into mytable values (1,1),(2,2),(3,3)");
      rs = stmt.executeQuery("with t1 as (select b from mytable where a > 1) select * from t1 where b < 3");
      while (rs.next()) {
        System.out.println(rs.getInt(1));
      }
      stmt.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (stmt != null)
          stmt.close();
        if (conn!= null)
          conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
  }
};


$ javac -cp .:$TRAF_HOME/export/lib/jdbcT4-2.3.0.jar mytest.java
$ java -cp .:$TRAF_HOME/export/lib/jdbcT4-2.3.0.jar mytest
org.trafodion.jdbc.t4.TrafT4Exception: Non-Select SQL statement is invalid in executeQuery() method
        at org.trafodion.jdbc.t4.TrafT4Messages.createSQLException(TrafT4Messages.java:284)
        at org.trafodion.jdbc.t4.TrafT4Statement.executeQuery(TrafT4Statement.java:463)
        at mytest.main(mytest.java:41)



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)