You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Weiqing Xu (JIRA)" <ji...@apache.org> on 2017/09/30 03:26:00 UTC
[jira] [Updated] (TRAFODION-2757) JDBC executeQuery() throws
exception on the "with ... select" stmt
[ https://issues.apache.org/jira/browse/TRAFODION-2757?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Weiqing Xu updated TRAFODION-2757:
----------------------------------
Description:
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)
was:
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)
> 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)