Author: jolee Date: 2014-04-23 13:48:59 -0400 (Wed, 23 Apr 2014) New Revision: 4634 Modified: branches/7.7.x/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java branches/7.7.x/test-integration/common/src/test/java/org/teiid/transport/TestODBCSocketTransport.java Log: TEIID-2930: SCROLL keyword for DECLARE CURSOR not being parsed correctly Modified: branches/7.7.x/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java =================================================================== --- branches/7.7.x/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java 2014-04-22 14:11:47 UTC (rev 4633) +++ branches/7.7.x/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java 2014-04-23 17:48:59 UTC (rev 4634) @@ -149,8 +149,8 @@ private static Pattern preparedAutoIncrement = Pattern.compile("select 1 \\s*from pg_catalog.pg_attrdef \\s*where adrelid = \\$1 AND adnum = \\$2 " + //$NON-NLS-1$ "\\s*and pg_catalog.pg_get_expr\\(adbin, adrelid\\) \\s*like '%nextval\\(%'", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$ - - private static Pattern cursorSelectPattern = Pattern.compile("DECLARE \"(\\w+)\" CURSOR(\\s(WITH HOLD|SCROLL))? FOR (.*)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL); //$NON-NLS-1$ + + private static Pattern cursorSelectPattern = Pattern.compile("DECLARE\\s+\"(\\w+)\"(?:\\s+INSENSITIVE)?(\\s+(NO\\s+)?SCROLL)?\\s+CURSOR\\s+FOR\\s+(.*)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL); //$NON-NLS-1$ private static Pattern fetchPattern = Pattern.compile("FETCH (\\d+) IN \"(\\w+)\".*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$ private static Pattern movePattern = Pattern.compile("MOVE (\\d+) IN \"(\\w+)\".*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$ private static Pattern closePattern = Pattern.compile("CLOSE \"(\\w+)\"", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$ @@ -250,38 +250,46 @@ } } - private void cursorExecute(final String cursorName, final String sql, final ResultsFuture<Integer> completion) { - if (sql != null) { - try { - // close if the name is already used or the unnamed prepare; otherwise - // stmt is alive until session ends. - Prepared previous = this.preparedMap.remove(cursorName); - if (previous != null) { - previous.stmt.close(); - } - - final PreparedStatementImpl stmt = this.connection.prepareStatement(sql); - this.executionFuture = stmt.submitExecute(ResultsMode.RESULTSET); - this.executionFuture.addCompletionListener(new ResultsFuture.CompletionListener<Boolean>() { - @Override - public void onCompletion(ResultsFuture<Boolean> future) { - executionFuture = null; - try { - if (future.get()) { - List<PgColInfo> cols = getPgColInfo(stmt.getResultSet().getMetaData()); - cursorMap.put(cursorName, new Cursor(cursorName, sql, stmt, null, stmt.getResultSet(), cols)); - client.sendCommandComplete("DECLARE CURSOR", null); //$NON-NLS-1$ - completion.getResultsReceiver().receiveResults(0); - } - } catch (Throwable e) { - completion.getResultsReceiver().exceptionOccurred(e); - } - } - }); - } catch (SQLException e) { - completion.getResultsReceiver().exceptionOccurred(e); - } - } + private void cursorExecute(String cursorName, final String sql, final ResultsFuture<Integer> completion, boolean scroll) { + try { + // close if the name is already used or the unnamed prepare; otherwise + // stmt is alive until session ends. + this.preparedMap.remove(UNNAMED); + Portal p = this.portalMap.remove(UNNAMED); + if (p != null) { + closePortal(p); + } + if (cursorName == null || cursorName.length() == 0) { + cursorName = UNNAMED; + } + Cursor cursor = cursorMap.get(cursorName); + if (cursor != null) { + errorOccurred("Cannot use the portal name " + cursorName + " until the exiting instance is closed."); + return; + } + + final PreparedStatementImpl stmt = this.connection.prepareStatement(sql, scroll?ResultSet.TYPE_SCROLL_INSENSITIVE:ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); + this.executionFuture = stmt.submitExecute(ResultsMode.RESULTSET); + final String name = cursorName; + this.executionFuture.addCompletionListener(new ResultsFuture.CompletionListener<Boolean>() { + @Override + public void onCompletion(ResultsFuture<Boolean> future) { + executionFuture = null; + try { + if (future.get()) { + List<PgColInfo> cols = getPgColInfo(stmt.getResultSet().getMetaData()); + cursorMap.put(name, new Cursor(name, sql, stmt, null, stmt.getResultSet(), cols)); + client.sendCommandComplete("DECLARE CURSOR", null); //$NON-NLS-1$ + completion.getResultsReceiver().receiveResults(0); + } + } catch (Throwable e) { + completion.getResultsReceiver().exceptionOccurred(e); + } + } + }); + } catch (SQLException e) { + completion.getResultsReceiver().exceptionOccurred(e); + } } private void cursorFetch(String cursorName, int rows, final ResultsFuture<Integer> completion) throws SQLException { @@ -788,6 +796,23 @@ } } } + + private void closePortal(Portal query) { + ResultSet rs = query.rs; + if (rs != null) { + try { + rs.close(); + } catch (SQLException e) { + LogManager.logDetail(LogConstants.CTX_ODBC, e, "Did not successfully close portal", query.name); //$NON-NLS-1$ + } + query.rs = null; + } + try { + query.stmt.close(); + } catch (SQLException e) { + LogManager.logDetail(LogConstants.CTX_ODBC, e, "Did not successfully close portal", query.name); //$NON-NLS-1$ + } + } @Override public void closePreparedStatement(String preparedName) { @@ -930,7 +955,11 @@ Matcher m = null; if ((m = cursorSelectPattern.matcher(sql)).matches()){ - cursorExecute(m.group(1), fixSQL(m.group(4)), results); + boolean scroll = false; + if (m.group(2) != null && m.group(3) == null ) { + scroll = true; + } + cursorExecute(m.group(1), fixSQL(m.group(4)), results, scroll); } else if ((m = fetchPattern.matcher(sql)).matches()){ cursorFetch(m.group(2), Integer.parseInt(m.group(1)), results); @@ -1064,6 +1093,8 @@ * The format used in the result set columns (if set). */ int[] resultColumnFormat; + + volatile ResultSetImpl rs; /** * The prepared statement. Modified: branches/7.7.x/test-integration/common/src/test/java/org/teiid/transport/TestODBCSocketTransport.java =================================================================== --- branches/7.7.x/test-integration/common/src/test/java/org/teiid/transport/TestODBCSocketTransport.java 2014-04-22 14:11:47 UTC (rev 4633) +++ branches/7.7.x/test-integration/common/src/test/java/org/teiid/transport/TestODBCSocketTransport.java 2014-04-23 17:48:59 UTC (rev 4634) @@ -50,6 +50,7 @@ import org.mockito.Mockito; import org.postgresql.Driver; import org.teiid.client.security.ILogon; +import org.postgresql.core.v3.ExtendedQueryExectutorImpl; import org.teiid.common.buffer.BufferManagerFactory; import org.teiid.core.util.UnitTestUtil; import org.teiid.jdbc.FakeServer; @@ -318,6 +319,26 @@ rs.next(); } + @Test public void testScrollCursor() throws Exception { + Statement stmt = conn.createStatement(); + ExtendedQueryExectutorImpl.simplePortal = "foo"; + try { + assertFalse(stmt.execute("declare \"foo\" insensitive scroll cursor for select * from pg_proc;")); + assertFalse(stmt.execute("move 5 in \"foo\"")); + stmt.execute("fetch 10 in \"foo\""); + ResultSet rs = stmt.getResultSet(); + int rowCount = 0; + while (rs.next()) { + rowCount++; + } + assertEquals(6, rowCount); + stmt.execute("close \"foo\""); + } finally { + ExtendedQueryExectutorImpl.simplePortal = null; + } + + } + @Test public void testPgProcedure() throws Exception { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select has_function_privilege(100, 'foo')");