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