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

net.snowflake.client.jdbcapi.StatementIT 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 net.snowflake.client.jdbc.ErrorCode;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.SQLFeatureNotSupportedException;
import com.snowflake.gscommon.core.SqlState;
import java.sql.SQLTimeoutException;

import static org.junit.Assert.*;

/**
 *
 * @author hyu
 */
public class StatementIT extends BaseJDBCTest
{
  // cancel statement is tested under SnowflakeDriverIT
  //@Test
  //public void testCancelStatement(){}
  private final String insertSQL = "insert into JDBC_STATEMENT values(1, 'str')";
  private final String selectSQL = "select count(*) from JDBC_STATEMENT";
  private Connection connection = null;
  private Statement statement =null;
  private ResultSet resultSet = null;

  @Before
  public void setUp() throws SQLException{
    connection = getConnection();
    statement = connection.createStatement();
    statement.execute("create or replace table jdbc_statement(colA integer, colB string)");
    statement.execute("insert into jdbc_statement values(1, 'str1')");
    statement.execute("insert into jdbc_statement values(2, 'str2')");
    statement.execute("insert into jdbc_statement values(3, 'str3')");
    statement.close();
  }

  @After
  public void tearDown() throws SQLException{
    statement = connection.createStatement();
    statement.execute("drop table if exists JDBC_STATEMENT");
    statement.close();
    connection.close();
  }

  @Test 
  public void testFetchDirection() throws SQLException{
    statement = connection.createStatement();
    assertEquals(ResultSet.FETCH_FORWARD, statement.getFetchDirection()); 
    try{
      statement.setFetchDirection(ResultSet.FETCH_REVERSE);
    } catch (SQLFeatureNotSupportedException e){
      assertTrue(true);
    }
    statement.close();
  }
    
  // Not working for setFetchSize
  //@Test
  public void testFetchSize() throws SQLException{
    statement = connection.createStatement();
    assertEquals(50, statement.getFetchSize());
    statement.setFetchSize(1);
    ResultSet rs = statement.executeQuery("select * from JDBC_STATEMENT");
    assertEquals(1, getSizeOfResultSet(rs));
    
    
    statement.close();
  }

  @Test
  public void testMaxRows() throws SQLException{
    statement = connection.createStatement();
    String sqlSelect = "select * from JDBC_STATEMENT";
    assertEquals(0, statement.getMaxRows());

    statement.setMaxRows(1);
    assertEquals(1, statement.getMaxRows());
    ResultSet rs =  statement.executeQuery(sqlSelect);
    int resultSizeCount = getSizeOfResultSet(rs);
    assertEquals(1, resultSizeCount);

    statement.setMaxRows(0);
    rs = statement.executeQuery(sqlSelect);
    assertEquals(3, getSizeOfResultSet(rs));
    statement.close();

    statement = connection.createStatement();
    statement.setMaxRows(-1);
    rs = statement.executeQuery(sqlSelect);
    assertEquals(3, getSizeOfResultSet(rs)); 
    statement.close();
  }

  @Test
  public void testQueryTimeOut() throws SQLException{
    statement = connection.createStatement();
    assertEquals(0, statement.getQueryTimeout()); 
    statement.setQueryTimeout(5);
    assertEquals(5, statement.getQueryTimeout());
    try{
      statement.executeQuery("select count(*) from table(generator(timeLimit => 100))");
    } catch (SQLException e){
      assertTrue(true);
      assertEquals(SqlState.QUERY_CANCELED, e.getSQLState());
      assertEquals("SQL execution canceled", e.getMessage());
    }
    statement.close();
  }

  @Test
  public void testStatementClose() throws SQLException{
    statement = connection.createStatement();
    assertEquals(connection, statement.getConnection());
    assertTrue(!statement.isClosed());
    statement.close();
    assertTrue(statement.isClosed());
  }

  @Test
  public void testExecuteSelect() throws SQLException{
    statement = connection.createStatement();
    statement.execute("alter session set JDBC_EXECUTE_RETURN_COUNT_FOR_DML = true");

    ResultSet rs = null;
    boolean success;
    success = statement.execute("select * from JDBC_STATEMENT");
    assertEquals(true, success);
    rs = statement.getResultSet();
    assertEquals(3, getSizeOfResultSet(rs));
    assertEquals(-1, statement.getUpdateCount());

    rs = statement.executeQuery("select * from JDBC_STATEMENT");
    assertEquals(3, getSizeOfResultSet(rs));
    rs.close();
  }

  @Test
  public void testExecuteCreateAndDrop() throws SQLException{
    statement = connection.createStatement();
    statement.execute("alter session set JDBC_EXECUTE_RETURN_COUNT_FOR_DML = true");

    boolean success = true;
    int updateCount = 0;

    success = statement.execute("create or replace table test_update(colA integer)");
    assertEquals(false, success);
    assertEquals(0, statement.getUpdateCount());
    assertNull(statement.getResultSet());

    updateCount = statement.executeUpdate("create or replace table test_update_2(colA integer)");
    assertEquals(0, updateCount);
    
    success = statement.execute("drop table if exists TEST_UPDATE");
    assertEquals(false, success);
    assertEquals(0, statement.getUpdateCount());
    assertNull(statement.getResultSet());

    updateCount = statement.executeUpdate("drop table if exists TEST_UPDATE_2");
    assertEquals(0, updateCount);
    assertNull(statement.getResultSet());

    statement.close();
  }

  @Test 
  public void testExecuteInsert() throws SQLException{
    statement = connection.createStatement();
    statement.execute("alter session set JDBC_EXECUTE_RETURN_COUNT_FOR_DML = true");

    int updateCount;
    boolean success;
    updateCount = statement.executeUpdate(insertSQL);
    assertEquals(1, updateCount);

    success = statement.execute(insertSQL);
    assertEquals(false, success);
    assertEquals(1, statement.getUpdateCount());
    assertNull(statement.getResultSet());

    ResultSet rs = statement.executeQuery(selectSQL);
    rs.next();
    assertEquals(5, rs.getInt(1));

    rs.close();
    statement.close();
  }

  @Test 
  public void testExecuteUpdateAndDelete() throws SQLException{
    statement = connection.createStatement();
    statement.execute("alter session set JDBC_EXECUTE_RETURN_COUNT_FOR_DML = true");

    int updateCount;
    boolean success;
    updateCount = statement.executeUpdate("update JDBC_STATEMENT set COLB = 'newStr' where COLA = 1");
    assertEquals(1, updateCount);

    success = statement.execute("update JDBC_STATEMENT set COLB = 'newStr' where COLA = 2");
    assertEquals(false, success);
    assertEquals(1, statement.getUpdateCount());
    assertNull(statement.getResultSet());

    updateCount = statement.executeUpdate("delete from JDBC_STATEMENT where colA = 1");
    assertEquals(1, updateCount);

    success = statement.execute("delete from JDBC_STATEMENT where colA = 2");
    assertEquals(false, success);
    assertEquals(1, statement.getUpdateCount());
    assertNull(statement.getResultSet());

    statement.close();
  }

  @Test
  public void testExecuteMerge() throws SQLException{
    String mergeSQL = "merge into target using source on target.id = source.id "
            + "when matched and source.sb =22 then update set ta = 'newStr' "
            + "when not matched then insert (ta, tb) values (source.sa, source.sb)";
    statement = connection.createStatement();
    statement.execute("create or replace table target(id integer, ta string, tb integer)");
    statement.execute("create or replace table source(id integer, sa string, sb integer)");
    statement.execute("insert into target values(1, 'str', 1)");
    statement.execute("insert into target values(2, 'str', 2)");
    statement.execute("insert into target values(3, 'str', 3)");
    statement.execute("insert into source values(1, 'str1', 11)");
    statement.execute("insert into source values(2, 'str2', 22)");
    statement.execute("insert into source values(3, 'str3', 33)");
    
    int updateCount = statement.executeUpdate(mergeSQL);

    assertEquals(1, updateCount);

    statement.execute("drop table if exists target");
    statement.execute("drop table if exists source");
  }

  @Test
  public void testExecuteMultiInsert() throws SQLException{
    String multiInsertionSQL = " insert all "
            + "into foo "
            + "into foo1 "
            + "into bar (b1, b2, b3) values (s3, s2, s1) "
            + "select s1, s2, s3 from source";

    statement = connection.createStatement();
    statement.execute("create or replace table foo (f1 integer, f2 integer, f3 integer)");
    statement.execute("create or replace table foo1 (f1 integer, f2 integer, f3 integer)");
    statement.execute("create or replace table bar (b1 integer, b2 integer, b3 integer)");
    statement.execute("create or replace table source(s1 integer, s2 integer, s3 integer)");
    statement.execute("insert into source values(1, 2, 3)");
    statement.execute("insert into source values(11, 22, 33)");
    statement.execute("insert into source values(111, 222, 333)");
    
    int updateCount = statement.executeUpdate(multiInsertionSQL);
    assertEquals(9, updateCount);

    statement.execute("drop table if exists foo");
    statement.execute("drop table if exists foo1");
    statement.execute("drop table if exists bar");
    statement.execute("drop table if exists source");

    statement.close();
    
  }

  @Test
  public void testCopyAndUnload() throws SQLException, Exception{
      statement = connection.createStatement();

      statement.executeUpdate("create or replace table orders_jdbc" +
          "(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)");

      statement.close();

      String sfProjectRoot = getSFProjectRootString();

            // put files
      boolean status =
      statement.execute("PUT file://" + sfProjectRoot +
                    "/ExecPlatform/Database/data/orders_10*.csv @%orders_jdbc");

      assertTrue("put command fails", status);

      int numRows =
      statement.executeUpdate("copy into orders_jdbc");

      assertTrue("Unexpected number of rows copied: " + numRows, numRows == 73);
      statement.execute("drop table if exists orders_jdbc");
  }
}




© 2015 - 2025 Weber Informatics LLC | Privacy Policy