[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=16188546#comment-16188546 ] 

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

Github user asfgit closed the pull request at:

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


> 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, trafodion 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.
> 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)