net.snowflake.client.jdbcapi.ResultSetIT Maven / Gradle / Ivy
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package net.snowflake.client.jdbcapi;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.DatabaseMetaData;
import java.util.Properties;
import static org.junit.Assert.*;
import net.snowflake.client.jdbc.ErrorCode;
import net.snowflake.client.jdbc.SnowflakeSQLException;
import org.junit.Before;
import org.junit.After;
import org.junit.Test;
/**
*
* @author hyu
*/
public class ResultSetIT extends BaseJDBCTest
{
private Connection connection = null;
private Statement statement = null;
private ResultSet resultSet = null;
private ResultSetMetaData resultSetMetaData = null;
private final String createTestTableSQL = "create or replace table test_rs (colA string)";
private final String selectAllSQL = "select * from test_rs";
@Before
public void setUp() throws SQLException{
Connection con = getConnection();
con.createStatement().execute(createTestTableSQL);
con.createStatement().execute("insert into test_rs values('rowOne')");
con.createStatement().execute("insert into test_rs values('rowTwo')");
con.createStatement().execute("insert into test_rs values('rowThree')");
con.close();
}
@After
public void tearDown() throws SQLException{
Connection con = getConnection();
con.createStatement().execute("drop table if exists test_rs");
con.close();
}
//@Test
public void testMovingCursor() throws SQLException{
connection = getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(selectAllSQL);
assertTrue(resultSet.next());
assertTrue(resultSet.next());
assertTrue(resultSet.next());
resultSet.first();
// assertEquals("rowOne", resultSet.getString(1));
statement.close();
connection.close();
}
@Test
public void testFindColumn() throws SQLException{
connection = getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(selectAllSQL);
assertEquals(1, resultSet.findColumn("COLA"));
statement.close();
connection.close();
}
@Test
public void testGetMethod() throws SQLException{
String prepInsertString = "insert into test_get values(?, ?, ?, ?, ?, ?, ?)";
int bigInt = Integer.MAX_VALUE;
long bigLong = Long.MAX_VALUE;
short bigShort = Short.MAX_VALUE;
String str = "hello";
double bigDouble = Double.MAX_VALUE;
float bigFloat = Float.MAX_VALUE;
connection = getConnection();
statement = connection.createStatement();
statement.execute("create or replace table test_get(colA integer, colB number, colC number, "
+ "colD string, colE double, colF float, colG boolean)");
PreparedStatement prepStatement = connection.prepareStatement(prepInsertString);
prepStatement.setInt(1, bigInt);
prepStatement.setLong(2, bigLong);
prepStatement.setLong(3, bigShort);
prepStatement.setString(4, str);
prepStatement.setDouble(5, bigDouble);
prepStatement.setFloat(6, bigFloat);
prepStatement.setBoolean(7, true);
prepStatement.execute();
statement.execute("select * from test_get");
resultSet = statement.getResultSet();
resultSet.next();
assertEquals(bigInt, resultSet.getInt(1));
assertEquals(bigInt, resultSet.getInt("COLA"));
assertEquals(bigLong, resultSet.getLong(2));
assertEquals(bigLong, resultSet.getLong("COLB"));
assertEquals(bigShort, resultSet.getShort(3));
assertEquals(bigShort, resultSet.getShort("COLC"));
assertEquals(str, resultSet.getString(4));
assertEquals(str, resultSet.getString("COLD"));
//assertEquals(bigDouble, resultSet.getDouble(5), 0);
//assertEquals(bigDouble, resultSet.getDouble("COLE"), 0);
assertEquals(bigFloat, resultSet.getFloat(6), 0);
assertEquals(bigFloat, resultSet.getFloat("COLF"), 0);
assertEquals(true, resultSet.getBoolean(7));
assertEquals(true, resultSet.getBoolean("COLG"));
//test getStatement method
assertEquals(statement, resultSet.getStatement());
prepStatement.close();
statement.execute("drop table if exists table_get");
statement.close();
resultSet.close();
connection.close();
}
@Test
public void testGetObjectOnDatabaseMetadataResultSet()
throws SQLException
{
connection = getConnection();
DatabaseMetaData databaseMetaData = connection.getMetaData();
resultSet = databaseMetaData.getTypeInfo();
resultSet.next();
// SNOW-21375 "NULLABLE" Column is a SMALLINT TYPE
assertEquals(DatabaseMetaData.typeNullable, resultSet.getObject("NULLABLE"));
resultSet.close();
connection.close();
}
@Test
public void testGetBigDecimal() throws SQLException
{
connection = getConnection();
statement = connection.createStatement();
statement.execute("create or replace table test_get(colA real)");
PreparedStatement preparedStatement = connection.prepareStatement(
"insert into test_get values(?)");
BigDecimal bigDecimal1 = new BigDecimal("10000000000");
preparedStatement.setBigDecimal(1, bigDecimal1);
preparedStatement.executeUpdate();
BigDecimal bigDecimal2= new BigDecimal("100000000.123456789");
preparedStatement.setBigDecimal(1, bigDecimal2);
preparedStatement.execute();
statement.execute("select * from test_get");
resultSet = statement.getResultSet();
resultSet.next();
assertEquals(bigDecimal1, resultSet.getBigDecimal(1));
assertEquals(bigDecimal1, resultSet.getBigDecimal("COLA"));
System.out.println(resultSet.next());
assertEquals(new BigDecimal("100000000.123"), resultSet.getBigDecimal(1, 3));
assertEquals(new BigDecimal("100000000.123"), resultSet.getBigDecimal("COLA", 3));
preparedStatement.close();
statement.execute("drop table if exists test_get");
statement.close();
resultSet.close();
connection.close();
}
@Test
public void testCursorPosition() throws SQLException{
connection = getConnection();
statement = connection.createStatement();
statement.execute(selectAllSQL);
resultSet = statement.getResultSet();
resultSet.next();
assertTrue(resultSet.isFirst());
assertEquals(1, resultSet.getRow());
resultSet.next();
assertTrue(!resultSet.isFirst());
assertEquals(2, resultSet.getRow());
/* assertTrue(!resultSet.isLast());
resultSet.next();
assertEquals(3, resultSet.getRow());
assertTrue(!resultSet.isLast());
resultSet.next();
assertTrue(resultSet.isLast());*/
statement.close();
connection.close();
}
@Test
public void testGetDateAndTime() throws SQLException{
final String insertTime = "insert into datetime values(?, ?, ?)";
connection = getConnection();
statement = connection.createStatement();
statement.execute(
"create or replace table dateTime(colA Date, colB Timestamp, colC Time)");
java.util.Date today = new java.util.Date();
Date date;
date = new Date(2016, 3, 20);
Timestamp ts = new Timestamp(today.getTime());
Time tm = new Time(12345678); // 03:25:45.678
PreparedStatement prepStatement = connection.prepareStatement(insertTime);
prepStatement.setDate(1, date);
prepStatement.setTimestamp(2, ts);
prepStatement.setTime(3, tm);
prepStatement.execute();
resultSet = statement.executeQuery("select * from datetime");
resultSet.next();
System.out.println(date.toString());
assertEquals(date, resultSet.getDate(1));
assertEquals(date, resultSet.getDate("COLA"));
assertEquals(ts, resultSet.getTimestamp(2));
assertEquals(ts, resultSet.getTimestamp("COLB"));
assertEquals(tm, resultSet.getTime(3));
assertEquals(tm, resultSet.getTime("COLC"));
statement.execute("drop table if exists datetime");
connection.close();
}
// SNOW-25029: The driver should reduce Time milliseconds mod 24h.
@Test
public void testTimeRange() throws SQLException
{
final String insertTime = "insert into timeTest values (?), (?), (?), (?)";
connection = getConnection();
statement = connection.createStatement();
statement.execute("create or replace table timeTest (c1 time)");
long ms1 = -2202968667333L; // 1900-03-11 09:15:33.667
long ms2 = -1; // 1969-12-31 23:59:99.999
long ms3 = 86400 * 1000; // 1970-01-02 00:00:00
long ms4 = 1451680250123L; // 2016-01-01 12:30:50.123
Time tm1 = new Time(ms1);
Time tm2 = new Time(ms2);
Time tm3 = new Time(ms3);
Time tm4 = new Time(ms4);
PreparedStatement prepStatement = connection.prepareStatement(insertTime);
prepStatement.setTime(1, tm1);
prepStatement.setTime(2, tm2);
prepStatement.setTime(3, tm3);
prepStatement.setTime(4, tm4);
prepStatement.execute();
// Note that the resulting Time objects are NOT equal because they have
// their milliseconds in the range 0 to 86,399,999, i.e. inside Jan 1, 1970.
// PreparedStatement accepts Time objects outside this range, but it reduces
// modulo 24 hours to discard the date information before sending to GS.
final long M = 86400 * 1000;
resultSet = statement.executeQuery("select * from timeTest");
resultSet.next();
assertFalse(tm1.equals(resultSet.getTime(1)));
assertEquals(new Time((ms1 % M + M) % M), resultSet.getTime(1));
resultSet.next();
assertFalse(tm2.equals(resultSet.getTime(1)));
assertEquals(new Time((ms2 % M + M) % M), resultSet.getTime(1));
resultSet.next();
assertFalse(tm3.equals(resultSet.getTime(1)));
assertEquals(new Time((ms3 % M + M) % M), resultSet.getTime(1));
resultSet.next();
assertFalse(tm4.equals(resultSet.getTime(1)));
assertEquals(new Time((ms4 % M + M) % M), resultSet.getTime(1));
statement.execute("drop table if exists timeTest");
connection.close();
}
@Test
public void testCurrentTime() throws SQLException
{
final String insertTime = "insert into datetime values (?, ?, ?)";
connection = getConnection();
assertTrue(connection.createStatement().
execute("alter session set TIMEZONE='UTC'"));
statement = connection.createStatement();
statement.execute(
"create or replace table datetime (d date, ts timestamp, tm time)");
PreparedStatement prepStatement = connection.prepareStatement(insertTime);
long currentMillis = System.currentTimeMillis();
Date currentDate = new Date(currentMillis);
Timestamp currentTS = new Timestamp(currentMillis);
Time currentTime = new Time(currentMillis);
prepStatement.setDate(1, currentDate);
prepStatement.setTimestamp(2, currentTS);
prepStatement.setTime(3, currentTime);
prepStatement.execute();
resultSet = statement.executeQuery("select ts::date = d from datetime");
resultSet.next();
assertEquals(true, resultSet.getBoolean(1));
resultSet = statement.executeQuery("select ts::time = tm from datetime");
resultSet.next();
assertEquals(true, resultSet.getBoolean(1));
statement.execute("drop table if exists datetime");
connection.close();
}
@Test
public void testGetBytes() throws SQLException
{
Properties props = new Properties();
props.setProperty("enable_binary_datatype", "true");
connection = getConnection(props);
statement = connection.createStatement();
statement.execute("create or replace table bin (b Binary)");
byte[] bytes1 = new byte[0];
byte[] bytes2 = {(byte)0xAB, (byte)0xCD, (byte)0x12};
byte[] bytes3 = {(byte)0x00, (byte)0xFF, (byte)0x42, (byte)0x01};
PreparedStatement prepStatement = connection.prepareStatement(
"insert into bin values (?), (?), (?)");
prepStatement.setBytes(1, bytes1);
prepStatement.setBytes(2, bytes2);
prepStatement.setBytes(3, bytes3);
prepStatement.execute();
// Get results in hex format (default).
resultSet = statement.executeQuery("select * from bin");
resultSet.next();
assertArrayEquals(bytes1, resultSet.getBytes(1));
assertEquals("", resultSet.getString(1));
resultSet.next();
assertArrayEquals(bytes2, resultSet.getBytes(1));
assertEquals("ABCD12", resultSet.getString(1));
resultSet.next();
assertArrayEquals(bytes3, resultSet.getBytes(1));
assertEquals("00FF4201", resultSet.getString(1));
// Get results in base64 format.
props.setProperty("binary_output_format", "BAse64");
connection = getConnection(props);
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from bin");
resultSet.next();
assertArrayEquals(bytes1, resultSet.getBytes(1));
assertEquals("", resultSet.getString(1));
resultSet.next();
assertArrayEquals(bytes2, resultSet.getBytes(1));
assertEquals("q80S", resultSet.getString(1));
resultSet.next();
assertArrayEquals(bytes3, resultSet.getBytes(1));
assertEquals("AP9CAQ==", resultSet.getString(1));
statement.execute("drop table if exists bin");
connection.close();
}
@Test
public void testResultSetMetadata() throws SQLException{
connection = getConnection();
statement = connection.createStatement();
statement.execute("create or replace table test_rsmd(colA number(20, 5), colB string)");
statement.execute("insert into test_rsmd values(1.00, 'str')");
statement.execute("insert into test_rsmd values(2.00, 'str2')");
resultSet = statement.executeQuery("select * from test_rsmd");
resultSetMetaData = resultSet.getMetaData();
assertEquals("TESTDB", resultSetMetaData.getCatalogName(1));
assertEquals("TESTSCHEMA", resultSetMetaData.getSchemaName(1));
assertEquals("TEST_RSMD", resultSetMetaData.getTableName(1));
assertEquals(String.class.getName(), resultSetMetaData.getColumnClassName(2));
assertEquals(2, resultSetMetaData.getColumnCount());
assertEquals(20, resultSetMetaData.getColumnDisplaySize(1));
assertEquals("COLA", resultSetMetaData.getColumnLabel(1));
assertEquals("COLA", resultSetMetaData.getColumnName(1));
assertEquals(3, resultSetMetaData.getColumnType(1));
assertEquals("NUMBER", resultSetMetaData.getColumnTypeName(1));
assertEquals(20, resultSetMetaData.getPrecision(1));
assertEquals(5, resultSetMetaData.getScale(1));
assertTrue(!resultSetMetaData.isAutoIncrement(1));
assertTrue(!resultSetMetaData.isCaseSensitive(1));
assertTrue(!resultSetMetaData.isCurrency(1));
assertTrue(!resultSetMetaData.isDefinitelyWritable(1));
assertEquals(resultSetMetaData.columnNullable, resultSetMetaData.isNullable(1));
assertTrue(resultSetMetaData.isReadOnly(1));
assertTrue(resultSetMetaData.isSearchable(1));
assertTrue(resultSetMetaData.isSigned(1));
statement.execute("drop table if exists test_rsmd");
statement.close();
connection.close();
}
@Test
public void testGetOldDate() throws SQLException
{
connection = getConnection();
statement = connection.createStatement();
statement.execute("create or replace table testOldDate(d date)");
statement.execute("insert into testOldDate values ('0001-01-01'), " +
"(to_date('1000-01-01')), ('1300-01-01'), ('1400-02-02'), " +
"('1500-01-01'), ('1600-02-03')");
resultSet = statement.executeQuery("select * from testOldDate order by d");
resultSet.next();
assertEquals("0001-01-01", resultSet.getString(1));
assertEquals(Date.valueOf("0001-01-01"), resultSet.getDate(1));
resultSet.next();
assertEquals("1000-01-01", resultSet.getString(1));
assertEquals(Date.valueOf("1000-01-01"), resultSet.getDate(1));
resultSet.next();
assertEquals("1300-01-01", resultSet.getString(1));
assertEquals(Date.valueOf("1300-01-01"), resultSet.getDate(1));
resultSet.next();
assertEquals("1400-02-02", resultSet.getString(1));
assertEquals(Date.valueOf("1400-02-02"), resultSet.getDate(1));
resultSet.next();
assertEquals("1500-01-01", resultSet.getString(1));
assertEquals(Date.valueOf("1500-01-01"), resultSet.getDate(1));
resultSet.next();
assertEquals("1600-02-03", resultSet.getString(1));
assertEquals(Date.valueOf("1600-02-03"), resultSet.getDate(1));
resultSet.close();
statement.execute("drop table if exists testOldDate");
statement.close();
connection.close();
}
@Test
public void testGetObjectOnFixedView() throws SQLException, Exception
{
String sfProjectRoot = getSFProjectRootString();
connection = getConnection();
statement = connection.createStatement();
statement.executeUpdate("create or replace table testFixedView" +
"(C1 STRING NOT NULL COMMENT 'JDBC', "
+ "C2 STRING, C3 STRING, C4 STRING, C5 STRING, C6 STRING, "
+ "C7 STRING, C8 STRING, C9 STRING) "
+ "stage_file_format = (field_delimiter='|' "
+ "error_on_column_count_mismatch=false)");
resultSet = statement.executeQuery("PUT file://" + sfProjectRoot +
"/ExecPlatform/Database/data/orders_10*.csv @%testFixedView");
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
while(resultSet.next())
{
for (int i=0; i200000))");
// new json parser
assertTrue(connection.createStatement().
execute("alter session set JDBC_USE_JSON_PARSER=true"));
// inefficient chunk storage
assertTrue(connection.createStatement().
execute("alter session set JDBC_EFFICIENT_CHUNK_STORAGE=false"));
Statement statement1 = connection.createStatement();
ResultSet resultSet1 = statement1.executeQuery(
"select current_date(), true,2345234, 2343.0, 'testrgint\\n\\t' from table(generator(rowcount=>200000))");
// efficient chunk storage
assertTrue(connection.createStatement().
execute("alter session set JDBC_EFFICIENT_CHUNK_STORAGE=true"));
Statement statement2 = connection.createStatement();
ResultSet resultSet2 = statement2.executeQuery(
"select current_date(), true,2345234, 2343.0, 'testrgint\\n\\t' from table(generator(rowcount=>200000))");
// compare the three variants for equality
while(resultSet0.next())
{
resultSet1.next();
resultSet2.next();
for (int i = 1; i <= 5; i++)
{
assertEquals(resultSet0.getString(i), resultSet1.getString(i));
assertEquals(resultSet1.getString(i), resultSet2.getString(i));
}
}
statement1.close();
statement2.close();
connection.close();
}
@Test
public void testDateTimeRelatedTypeConversion() throws SQLException
{
connection = getConnection();
statement = connection.createStatement();
statement.execute("create or replace table testDateTime" +
"(colDate DATE, colTS timestamp_ltz, colTime TIME, colString string)");
PreparedStatement preparedStatement = connection.prepareStatement(
"insert into testDateTime values(?, ?, ?, ?)");
long currentMillis = System.currentTimeMillis();
Date date = new Date(2016, 3, 20);
Timestamp ts = new Timestamp(2016, 3, 20, 3, 25, 45, 67800000);
Time time = new Time(12345678); // 03:25:45.678
preparedStatement.setDate(1, date);
preparedStatement.setTimestamp(2, ts);
preparedStatement.setTime(3, time);
preparedStatement.setString(4, "aaa");
preparedStatement.execute();
resultSet = statement.executeQuery("select * from testDateTime");
resultSet.next();
// ResultSet.getDate()
assertEquals(date, resultSet.getDate("COLDATE"));
assertEquals(date.toString(), resultSet.getDate("COLTS").toString());
try
{
resultSet.getDate("COLTIME");
fail();
}
catch (SnowflakeSQLException e)
{
assertEquals((int)ErrorCode.INVALID_VALUE_CONVERT.getMessageCode(), e.getErrorCode());
assertEquals(ErrorCode.INVALID_VALUE_CONVERT.getSqlState(), e.getSQLState());
}
// ResultSet.getTimestamp()
assertEquals(new Timestamp(date.getTime()), resultSet.getTimestamp("COLDATE"));
assertEquals(ts, resultSet.getTimestamp("COLTS"));
assertEquals(new Timestamp(time.getTime()), resultSet.getTimestamp("COLTIME"));
try
{
resultSet.getTimestamp("COLSTRING");
fail();
}
catch (SnowflakeSQLException e)
{
assertEquals((int)ErrorCode.INVALID_VALUE_CONVERT.getMessageCode(), e.getErrorCode());
assertEquals(ErrorCode.INVALID_VALUE_CONVERT.getSqlState(), e.getSQLState());
}
// ResultSet.getTime()
try
{
resultSet.getTime("COLDATE");
fail();
}
catch (SnowflakeSQLException e)
{
assertEquals((int)ErrorCode.INVALID_VALUE_CONVERT.getMessageCode(), e.getErrorCode());
assertEquals(ErrorCode.INVALID_VALUE_CONVERT.getSqlState(), e.getSQLState());
}
assertEquals(time, resultSet.getTime("COLTIME"));
assertEquals(new Time(ts.getTime()), resultSet.getTime("COLTS"));
statement.execute("drop table if exists testDateTime");
}
@Test
public void testResultColumnSearchCaseSensitive() throws SQLException
{
Properties prop = new Properties();
prop.put("tracing", "FINEST");
connection = getConnection(prop);
statement = connection.createStatement();
resultSet = statement.executeQuery("select 1 AS TESTCOL");
resultSet.next();
assertEquals("1", resultSet.getString("TESTCOL"));
assertEquals("1", resultSet.getString("TESTCOL"));
try
{
resultSet.getString("testcol");
fail();
}
catch(SQLException e)
{
assertEquals("Column not found: testcol", e.getMessage());
}
// try to do case-insensitive search
resultSet = statement.executeQuery("alter session set JDBC_RS_COLUMN_CASE_INSENSITIVE=true");
resultSet = statement.executeQuery("select 1 AS TESTCOL");
resultSet.next();
// get twice so that the code path can hit the place where
// we use cached key pair (columnName, index)
assertEquals("1", resultSet.getString("TESTCOL"));
assertEquals("1", resultSet.getString("TESTCOL"));
assertEquals("1", resultSet.getString("testcol"));
assertEquals("1", resultSet.getString("testcol"));
}
@Test
public void testInvalidColumnIndex() throws SQLException
{
connection = getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(selectAllSQL);
resultSet.next();
try
{
resultSet.getString(0);
fail();
}
catch (SQLException e)
{
assertEquals(200032, e.getErrorCode());
}
try
{
resultSet.getString(2);
fail();
}
catch (SQLException e)
{
assertEquals(200032, e.getErrorCode());
}
resultSet.close();
statement.close();
connection.close();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy