| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| package org.apache.phoenix.end2end; |
| |
| import static org.apache.phoenix.util.TestUtil.ENTITYHISTID3; |
| import static org.apache.phoenix.util.TestUtil.ENTITYHISTID7; |
| import static org.apache.phoenix.util.TestUtil.PARENTID3; |
| import static org.apache.phoenix.util.TestUtil.PARENTID7; |
| import static org.apache.phoenix.util.TestUtil.ROW1; |
| import static org.apache.phoenix.util.TestUtil.ROW2; |
| import static org.apache.phoenix.util.TestUtil.ROW3; |
| import static org.apache.phoenix.util.TestUtil.ROW4; |
| import static org.apache.phoenix.util.TestUtil.ROW7; |
| import static org.apache.phoenix.util.TestUtil.ROW8; |
| import static org.apache.phoenix.util.TestUtil.ROW9; |
| import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertFalse; |
| import static org.junit.Assert.assertTrue; |
| import static org.junit.Assert.fail; |
| |
| import java.math.BigDecimal; |
| import java.sql.Connection; |
| import java.sql.Date; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Timestamp; |
| import java.util.Properties; |
| import java.util.Random; |
| |
| import org.apache.phoenix.util.CursorUtil; |
| import org.apache.phoenix.util.DateUtil; |
| import org.apache.phoenix.util.PropertiesUtil; |
| import org.junit.Test; |
| |
| |
| public class CursorWithRowValueConstructorIT extends ParallelStatsDisabledIT { |
| private String tableName = generateUniqueName(); |
| |
| public void createAndInitializeTestTable() throws SQLException { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| tableName = generateUniqueName(); |
| PreparedStatement stmt = conn.prepareStatement("CREATE TABLE " + tableName + |
| "(a_id INTEGER NOT NULL, " + |
| "a_data INTEGER, " + |
| "CONSTRAINT my_pk PRIMARY KEY (a_id))"); |
| stmt.execute(); |
| conn.commit(); |
| |
| //Upsert test values into the test table |
| Random rand = new Random(); |
| stmt = conn.prepareStatement("UPSERT INTO " + tableName + |
| "(a_id, a_data) VALUES (?,?)"); |
| int rowCount = 0; |
| while(rowCount < 100){ |
| stmt.setInt(1, rowCount); |
| stmt.setInt(2, rand.nextInt(501)); |
| stmt.execute(); |
| ++rowCount; |
| } |
| conn.commit(); |
| conn.close(); |
| } |
| |
| @Test |
| public void testCursorsOnTestTablePK() throws SQLException { |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl())) { |
| createAndInitializeTestTable(); |
| String querySQL = "SELECT a_id FROM " + tableName; |
| |
| //Test actual cursor implementation |
| String cursorSQL = "DECLARE " + cursorName + " CURSOR FOR " + querySQL; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "OPEN " + cursorName; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursorSQL).executeQuery(); |
| int rowID = 0; |
| while(rs.next()){ |
| assertEquals(rowID,rs.getInt(1)); |
| ++rowID; |
| rs = conn.createStatement().executeQuery(cursorSQL); |
| } |
| conn.prepareStatement("CLOSE " + cursorName).execute(); |
| } |
| |
| } |
| |
| @Test |
| public void testCursorsOnRandomTableData() throws SQLException { |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl())) { |
| createAndInitializeTestTable(); |
| String querySQL = "SELECT a_id,a_data FROM " + tableName + " ORDER BY a_data"; |
| String cursorSQL = "DECLARE " + cursorName + " CURSOR FOR " + querySQL; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "OPEN " + cursorName; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "FETCH NEXT FROM " + cursorName; |
| ResultSet cursorRS = conn.prepareStatement(cursorSQL).executeQuery(); |
| ResultSet rs = conn.prepareStatement(querySQL).executeQuery(); |
| int rowCount = 0; |
| while(rs.next() && cursorRS.next()){ |
| assertEquals(rs.getInt(2),cursorRS.getInt(2)); |
| ++rowCount; |
| cursorRS = conn.prepareStatement(cursorSQL).executeQuery(); |
| } |
| assertEquals(100, rowCount); |
| conn.prepareStatement("CLOSE " + cursorName).execute(); |
| } |
| } |
| |
| @Test |
| public void testCursorsOnTestTablePKDesc() throws SQLException { |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl())) { |
| createAndInitializeTestTable(); |
| String dummySQL = "SELECT a_id FROM " + tableName + " ORDER BY a_id DESC"; |
| |
| String cursorSQL = "DECLARE " + cursorName + " CURSOR FOR " + dummySQL; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "OPEN " + cursorName; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursorSQL).executeQuery(); |
| int rowCount = 0; |
| while(rs.next()){ |
| assertEquals(99-rowCount, rs.getInt(1)); |
| rs = conn.prepareStatement(cursorSQL).executeQuery(); |
| ++rowCount; |
| } |
| assertEquals(100, rowCount); |
| conn.prepareStatement("CLOSE " + cursorName).execute(); |
| } |
| } |
| |
| @Test |
| public void testCursorsOnTestTableNonPKDesc() throws SQLException { |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl())) { |
| createAndInitializeTestTable(); |
| String dummySQL = "SELECT a_data FROM " + tableName + " ORDER BY a_data DESC"; |
| |
| String cursorSQL = "DECLARE " + cursorName + " CURSOR FOR " + dummySQL; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "OPEN " + cursorName; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursorSQL).executeQuery(); |
| int rowCount = 0; |
| while(rs.next()){ |
| rs = conn.prepareStatement(cursorSQL).executeQuery(); |
| ++rowCount; |
| } |
| assertEquals(100, rowCount); |
| conn.prepareStatement("CLOSE " + cursorName).execute(); |
| } |
| } |
| |
| @Test |
| public void testCursorsOnWildcardSelect() throws SQLException { |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl())) { |
| createAndInitializeTestTable(); |
| String querySQL = "SELECT * FROM " + tableName; |
| ResultSet rs = conn.prepareStatement(querySQL).executeQuery(); |
| |
| String cursorSQL = "DECLARE " + cursorName + " CURSOR FOR "+querySQL; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "OPEN " + cursorName; |
| conn.prepareStatement(cursorSQL).execute(); |
| cursorSQL = "FETCH NEXT FROM " + cursorName; |
| ResultSet cursorRS = conn.prepareStatement(cursorSQL).executeQuery(); |
| int rowCount = 0; |
| while(rs.next() && cursorRS.next()){ |
| assertEquals(rs.getInt(1),cursorRS.getInt(1)); |
| ++rowCount; |
| cursorRS = conn.prepareStatement(cursorSQL).executeQuery(); |
| } |
| assertEquals(100, rowCount); |
| conn.prepareStatement("CLOSE " + cursorName).execute(); |
| } |
| } |
| |
| @Test |
| public void testCursorsWithBindings() throws Exception { |
| String tenantId = getOrganizationId(); |
| String cursorName = generateUniqueName(); |
| final String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String query = "SELECT a_integer, x_integer FROM "+ aTable +" WHERE ?=organization_id AND (a_integer, x_integer) = (7, 5)"; |
| try (Connection conn = DriverManager.getConnection(getUrl())) { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| try { |
| PreparedStatement statement = conn.prepareStatement(cursor); |
| statement.setString(1, tenantId); |
| statement.execute(); |
| }catch(SQLException e){ |
| assertTrue(e.getMessage().equalsIgnoreCase("Cannot declare cursor, internal SELECT statement contains bindings!")); |
| assertFalse(CursorUtil.cursorDeclared(cursorName)); |
| return; |
| } finally { |
| cursor = "CLOSE " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| } |
| fail(); |
| } |
| } |
| |
| @Test |
| public void testCursorsInWhereWithEqualsExpression() throws Exception { |
| String tenantId = getOrganizationId(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String query = "SELECT a_integer, x_integer FROM "+aTable+" WHERE '"+tenantId+"'=organization_id AND (a_integer, x_integer) = (7, 5)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl(), props)) { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| try { |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| assertTrue(rs.getInt(1) == 7); |
| assertTrue(rs.getInt(2) == 5); |
| count++; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| assertTrue(count == 1); |
| } finally { |
| cursor = "CLOSE " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| } |
| } |
| } |
| |
| @Test |
| public void testCursorsInWhereWithGreaterThanExpression() throws Exception { |
| String tenantId = getOrganizationId(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String query = "SELECT a_integer, x_integer FROM "+aTable+" WHERE '"+tenantId+"'=organization_id AND (a_integer, x_integer) >= (4, 4)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl(), props)) { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| try { |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| assertTrue(rs.getInt(1) >= 4); |
| assertTrue(rs.getInt(1) == 4 ? rs.getInt(2) >= 4 : rs.getInt(2) >= 0); |
| count++; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| assertTrue(count == 5); |
| } finally { |
| cursor = "CLOSE " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| } |
| } |
| } |
| |
| @Test |
| public void testCursorsInWhereWithUnEqualNumberArgs() throws Exception { |
| String tenantId = getOrganizationId(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String query = "SELECT a_integer, x_integer FROM "+ aTable+" WHERE '"+tenantId+"'=organization_id AND (a_integer, x_integer, y_integer) >= (7, 5)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl(), props)) { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| try { |
| double startTime = System.nanoTime(); |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| assertTrue(rs.getInt(1) >= 7); |
| assertTrue(rs.getInt(1) == 7 ? rs.getInt(2) >= 5 : rs.getInt(2) >= 0); |
| count++; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records. |
| assertTrue(count == 3); |
| double endTime = System.nanoTime(); |
| System.out.println("Method Time in milliseconds: "+Double.toString((endTime-startTime)/1000000)); |
| } finally { |
| cursor = "CLOSE " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| } |
| } |
| } |
| |
| @Test |
| public void testCursorsOnLHSAndLiteralExpressionOnRHS() throws Exception { |
| String tenantId = getOrganizationId(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String query = "SELECT a_integer, x_integer FROM "+ aTable +" WHERE '"+tenantId+"'=organization_id AND (a_integer, x_integer) >= 7"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl(), props)) { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| try { |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| count++; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records. |
| assertTrue(count == 3); |
| } finally { |
| cursor = "CLOSE " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| } |
| } |
| } |
| |
| @Test |
| public void testCursorsOnRHSLiteralExpressionOnLHS() throws Exception { |
| String tenantId = getOrganizationId(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String query = "SELECT a_integer, x_integer FROM "+ aTable+" WHERE '"+tenantId+"'=organization_id AND 7 <= (a_integer, x_integer)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl(), props)) { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| try { |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| count++; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records. |
| assertTrue(count == 3); |
| } finally { |
| cursor = "CLOSE " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| } |
| } |
| } |
| |
| @Test |
| public void testCursorsOnBuiltInFunctionOperatingOnIntegerLiteral() throws Exception { |
| String tenantId = getOrganizationId(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String query = "SELECT a_integer, x_integer FROM "+aTable+" WHERE '"+tenantId+"'=organization_id AND (a_integer, x_integer) >= to_number('7')"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| String cursorName = generateUniqueName(); |
| try (Connection conn = DriverManager.getConnection(getUrl(), props)) { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| try { |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| count++; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records. |
| assertEquals(3, count); |
| } finally { |
| cursor = "CLOSE " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| } |
| } |
| } |
| |
| @Test |
| /** |
| * Test for the precision of Date datatype when used as part of a filter within the internal Select statement. |
| */ |
| public void testCursorsWithDateDatatypeFilter() throws Exception { |
| String tenantId = getOrganizationId(); |
| long currentTime = System.currentTimeMillis(); |
| java.sql.Date date = new java.sql.Date(currentTime); |
| String strCurrentDate = date.toString(); |
| |
| //Sets date to <yesterday's date> 23:59:59.999 |
| while(date.toString().equals(strCurrentDate)){ |
| currentTime -= 1; |
| date = new Date(currentTime); |
| } |
| //Sets date to <today's date> 00:00:00.001 |
| date = new Date(currentTime+2); |
| java.sql.Date midnight = new Date(currentTime+1); |
| |
| |
| String tableName = initEntityHistoryTableValues(null, tenantId, getDefaultSplits(tenantId), date, null); |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| |
| |
| String query = "select parent_id from " + tableName + |
| " WHERE (organization_id, parent_id, created_date, entity_history_id) IN ((?,?,?,?),(?,?,?,?))"; |
| |
| query = query.replaceFirst("\\?", "'"+tenantId+"'"); |
| query = query.replaceFirst("\\?", "'"+PARENTID3+"'"); |
| query = query.replaceFirst("\\?", "TO_DATE('"+DateUtil.getDateFormatter(DateUtil.DEFAULT_DATE_FORMAT).format(date)+"')"); |
| query = query.replaceFirst("\\?", "'"+ENTITYHISTID3+"'"); |
| query = query.replaceFirst("\\?", "'"+tenantId+"'"); |
| query = query.replaceFirst("\\?", "'"+PARENTID7+"'"); |
| query = query.replaceFirst("\\?", "TO_DATE('"+DateUtil.getDateFormatter(DateUtil.DEFAULT_DATE_FORMAT).format(date)+"')"); |
| query = query.replaceFirst("\\?", "'"+ENTITYHISTID7+"'"); |
| String cursorName = generateUniqueName(); |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(PARENTID3, rs.getString(1)); |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(PARENTID7, rs.getString(1)); |
| assertFalse(rs.next()); |
| |
| //Test against the same table for the same records, but this time use the 'midnight' java.sql.Date instance. |
| //'midnight' is identical to 'date' to the tens of millisecond precision. |
| query = "select parent_id from " + tableName + |
| " WHERE (organization_id, parent_id, created_date, entity_history_id) IN ((?,?,?,?),(?,?,?,?))"; |
| query = query.replaceFirst("\\?", "'"+tenantId+"'"); |
| query = query.replaceFirst("\\?", "'"+PARENTID3+"'"); |
| query = query.replaceFirst("\\?", "TO_DATE('"+DateUtil.getDateFormatter(DateUtil.DEFAULT_DATE_FORMAT).format(midnight)+"')"); |
| query = query.replaceFirst("\\?", "'"+ENTITYHISTID3+"'"); |
| query = query.replaceFirst("\\?", "'"+tenantId+"'"); |
| query = query.replaceFirst("\\?", "'"+PARENTID7+"'"); |
| query = query.replaceFirst("\\?", "TO_DATE('"+DateUtil.getDateFormatter(DateUtil.DEFAULT_DATE_FORMAT).format(midnight)+"')"); |
| query = query.replaceFirst("\\?", "'"+ENTITYHISTID7+"'"); |
| String cursorName2 = generateUniqueName(); |
| cursor = "DECLARE " + cursorName2 + " CURSOR FOR "+query; |
| |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName2; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName2; |
| |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| assertTrue(!rs.next()); |
| String sql = "CLOSE " + cursorName; |
| conn.prepareStatement(sql).execute(); |
| sql = "CLOSE " + cursorName2; |
| conn.prepareStatement(sql).execute(); |
| } |
| |
| @Test |
| public void testCursorsWithNonLeadingPkColsOfTypesTimeStampAndVarchar() throws Exception { |
| String tenantId = getOrganizationId(); |
| String cursorName = generateUniqueName(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String updateStmt = |
| "upsert into " + aTable+ |
| "(" + |
| " ORGANIZATION_ID, " + |
| " ENTITY_ID, " + |
| " A_TIMESTAMP) " + |
| "VALUES (?, ?, ?)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection upsertConn = DriverManager.getConnection(url, props); |
| upsertConn.setAutoCommit(true); |
| PreparedStatement stmt = upsertConn.prepareStatement(updateStmt); |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW4); |
| Timestamp tsValue = new Timestamp(System.nanoTime()); |
| stmt.setTimestamp(3, tsValue); |
| stmt.execute(); |
| |
| String query = "SELECT a_timestamp, a_string FROM "+aTable+" WHERE ?=organization_id AND (a_timestamp, a_string) = (?, 'a')"; |
| query = query.replaceFirst("\\?", "'"+tenantId+"'"); |
| query = query.replaceFirst("\\?", "TO_DATE('"+DateUtil.getDateFormatter(DateUtil.DEFAULT_TIMESTAMP_FORMAT).format(tsValue)+"')"); |
| |
| props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| assertTrue(rs.getTimestamp(1).equals(tsValue)); |
| assertTrue(rs.getString(2).compareTo("a") == 0); |
| count++; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| assertTrue(count == 1); |
| } finally { |
| String sql = "CLOSE " + cursorName; |
| conn.prepareStatement(sql).execute(); |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testCursorsQueryMoreWithInListClausePossibleNullValues() throws Exception { |
| String tenantId = getOrganizationId(); |
| String cursorName = generateUniqueName(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String updateStmt = |
| "upsert into " +aTable+ |
| "(ORGANIZATION_ID, ENTITY_ID, Y_INTEGER, X_INTEGER) VALUES (?, ?, ?, ?)"; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection upsertConn = DriverManager.getConnection(url, props); |
| upsertConn.setAutoCommit(true); |
| PreparedStatement stmt = upsertConn.prepareStatement(updateStmt); |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW4); |
| stmt.setInt(3, 4); |
| stmt.setInt(4, 5); |
| stmt.execute(); |
| |
| //we have a row present in aTable where x_integer = 5 and y_integer = NULL which gets translated to 0 when retriving from HBase. |
| String query = "SELECT x_integer, y_integer FROM "+ aTable+" WHERE ? = organization_id AND (x_integer) IN ((5))"; |
| |
| query = query.replaceFirst("\\?", "'"+tenantId+"'"); |
| |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| |
| try { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(5, rs.getInt(1)); |
| assertEquals(4, rs.getInt(2)); |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(5, rs.getInt(1)); |
| assertEquals(0, rs.getInt(2)); |
| } finally { |
| String sql = "CLOSE " + cursorName; |
| conn.prepareStatement(sql).execute(); |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testCursorsWithColsOfTypesDecimal() throws Exception { |
| String cursorName = generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| |
| String query = "SELECT x_decimal FROM "+ aTable+" WHERE ?=organization_id AND entity_id IN (?,?,?)"; |
| query = query.replaceFirst("\\?", "'"+tenantId+"'"); |
| query = query.replaceFirst("\\?", "'"+ROW7+"'"); |
| query = query.replaceFirst("\\?", "'"+ROW8+"'"); |
| query = query.replaceFirst("\\?", "'"+ROW9+"'"); |
| |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| assertTrue(BigDecimal.valueOf(0.1).equals(rs.getBigDecimal(1)) || BigDecimal.valueOf(3.9).equals(rs.getBigDecimal(1)) || BigDecimal.valueOf(3.3).equals(rs.getBigDecimal(1))); |
| count++; |
| if(count == 3) break; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| assertTrue(count == 3); |
| } finally { |
| String sql = "CLOSE " + cursorName; |
| conn.prepareStatement(sql).execute(); |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testCursorsWithColsOfTypesTinyintSmallintFloatDouble() throws Exception { |
| String cursorName = generateUniqueName(); |
| String tenantId = getOrganizationId(); |
| String aTable = initATableValues(null, tenantId, |
| getDefaultSplits(tenantId), null, null, getUrl(), null); |
| String query = "SELECT a_byte,a_short,a_float,a_double FROM "+ aTable+" WHERE ?=organization_id AND entity_id IN (?,?,?)"; |
| query = query.replaceFirst("\\?", "'"+tenantId+"'"); |
| query = query.replaceFirst("\\?", "'"+ROW1+"'"); |
| query = query.replaceFirst("\\?", "'"+ROW2+"'"); |
| query = query.replaceFirst("\\?", "'"+ROW3+"'"); |
| |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "OPEN " + cursorName; |
| conn.prepareStatement(cursor).execute(); |
| cursor = "FETCH NEXT FROM " + cursorName; |
| |
| ResultSet rs = conn.prepareStatement(cursor).executeQuery(); |
| int count = 0; |
| while(rs.next()) { |
| assertTrue((byte)1 == (rs.getByte(1)) || (byte)2 == (rs.getByte(1)) || (byte)3 == (rs.getByte(1))); |
| assertTrue((short)128 == (rs.getShort(2)) || (short)129 == (rs.getShort(2)) || (short)130 == (rs.getShort(2))); |
| assertTrue(0.01f == (rs.getFloat(3)) || 0.02f == (rs.getFloat(3)) || 0.03f == (rs.getFloat(3))); |
| assertTrue(0.0001 == (rs.getDouble(4)) || 0.0002 == (rs.getDouble(4)) || 0.0003 == (rs.getDouble(4))); |
| count++; |
| if(count == 3) break; |
| rs = conn.prepareStatement(cursor).executeQuery(); |
| } |
| assertTrue(count == 3); |
| } finally { |
| String sql = "CLOSE " + cursorName; |
| conn.prepareStatement(sql).execute(); |
| conn.close(); |
| } |
| } |
| } |
