Re: Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?

Am Donnerstag, 27. September 2012 15:02:28 UTC+2 schrieb Florent Guillaume:

> Oracle is well known for its V$SESSION and other similar tables.

Hi,
even if Oracle uses V$SESSIONs, the $ here is not at the beginning, and it also does not fit the sql standard.

I made a small patch doing what was said above. While parsing the sql code it checks if there is a $ while in the state
IN_SQLCODE,if so it stops the parsing and returns the original sql. 

I ran the testsuite and it passed with no error. I also checked my example and the result returned is what would be
expected.


--- /org/postgresql/jdbc2/AbstractJdbc2Statement.java_orig    Tue Oct 09 10:25:10 2012
+++ /org/postgresql/jdbc2/AbstractJdbc2Statement.java    Tue Oct 09 10:31:50 2012
@@ -895,6 +895,7 @@
             int len = p_sql.length();
             StringBuffer newsql = new StringBuffer(len);
             int i=0;
+            try {
             while (i<len){
                 i=parseSql(p_sql,i,newsql,false,connection.getStandardConformingStrings());
                 // We need to loop here in case we encounter invalid
@@ -907,6 +908,11 @@
                     i++;
                 }
             }
+        } catch (final PGDollarQuoteParsingException e) {
+                // found dollar quoting in the sql string. do not parse for
+                // escape clauses and return the original sql.
+                return p_sql;
+        }
             return newsql.toString();
         }
         else
@@ -929,7 +935,7 @@
      * @return the position we stopped processing at
      */
     protected static int parseSql(String p_sql,int i,StringBuffer newsql, boolean stopOnComma,
-                                  boolean stdStrings)throws SQLException{
+                                  boolean stdStrings)throws SQLException, PGDollarQuoteParsingException {
         short state = IN_SQLCODE;
         int len = p_sql.length();
         int nestedParenthesis=0;
@@ -955,6 +961,10 @@
                         endOfNested=true;
                         break;
                     }
+            } else if (c == '$') { // start of a dollar quoted string
+                // dollar quoted strings are postgreSql only, throw an
+                // exception to stop parsing for db indepentend syntax.
+                throw new PGDollarQuoteParsingException();
                 } else if (stopOnComma && c==',' && nestedParenthesis==0) {
                     endOfNested=true;
                     break;
@@ -1066,7 +1076,7 @@
      * @param stdStrings whether standard_conforming_strings is on
      * @return the right postgreSql sql
      */
-    protected static String escapeFunction(String functionName, String args, boolean stdStrings) throws SQLException{
+    protected static String escapeFunction(String functionName, String args, boolean stdStrings) throws SQLException,
PGDollarQuoteParsingException{
         // parse function arguments
         int len = args.length();
         int i=0;




And i added an exception for this:


package org.postgresql.core;

public class PGDollarQuoteParsingException extends Exception {

}


Using this exception i handle to stop the parsing. It could also be used to print a warning or whatever.