All Downloads are FREE. Search and download functionalities are using the official Maven repository.

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