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

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2757?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16186825#comment-16186825 ] 

ASF GitHub Bot commented on TRAFODION-2757:
-------------------------------------------

GitHub user CoderSong2015 opened a pull request:

    https://github.com/apache/incubator-trafodion/pull/1249

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

    The jdbcT4 driver can't check the sql statement type succefully if the beginning of the query is 'with ... select'. 


You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/CoderSong2015/incubator-trafodion master

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/incubator-trafodion/pull/1249.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #1249
    
----
commit fdbb5dff3df8730243a752238727296d033065b9
Author: CoderSong2015 <40...@qq.com>
Date:   2017-09-30T02:30:46Z

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

----


> 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)