postgresql-jdbc.git / summary / log / commit / refs
commit 7ae0bc206da0496fa9a26e65fb66fce65755c94e
Author: Kris Jurka <books@ejurka.com>
Date: Tue Dec 14 06:23:40 2004 +0000
Implementation of standard escaped functions ( {fn func()} )
by rewriting to existing PostgreSQL functions.
Xavier Poinsard
.../jdbc2/AbstractJdbc2DatabaseMetaData.java | 59 ++-
org/postgresql/jdbc2/AbstractJdbc2Statement.java | 248 ++++++++---
org/postgresql/jdbc2/EscapedFunctions.java | 481 +++++++++++++++++++++
org/postgresql/test/jdbc2/CallableStmtTest.java | 27 +-
org/postgresql/test/jdbc2/StatementTest.java | 172 +++++++-
5 files changed, 897 insertions(+), 90 deletions(-)
diff --git a/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java b/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java
index ec360686..2fe32142 100644
--- a/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java
+++ b/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java
@@ -3,7 +3,7 @@
* Copyright (c) 2004, PostgreSQL Global Development Group
*
* IDENTIFICATION
-* $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v 1.15 2004/11/07 22:16:03 jurka Exp $
+* $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v 1.16 2004/11/09 08:48:31 jurka Exp $
*
*-------------------------------------------------------------------------
*/
@@ -464,32 +464,81 @@ public abstract class AbstractJdbc2DatabaseMetaData
return keywords;
}
+ /**
+ * get supported escaped numeric functions
+ * @return a comma separated list of function names
+ */
public String getNumericFunctions() throws SQLException
{
if (Driver.logDebug)
Driver.debug("getNumericFunctions");
- return "";
+ return EscapedFunctions.ABS+','+EscapedFunctions.ACOS+
+ ','+EscapedFunctions.ASIN+','+EscapedFunctions.ATAN+
+ ','+EscapedFunctions.ATAN2+','+EscapedFunctions.CEILING+
+ ','+EscapedFunctions.COS+','+EscapedFunctions.COT+
+ ','+EscapedFunctions.DEGREES+','+EscapedFunctions.EXP+
+ ','+EscapedFunctions.FLOOR+','+EscapedFunctions.LOG+
+ ','+EscapedFunctions.LOG10+','+EscapedFunctions.MOD+
+ ','+EscapedFunctions.PI+','+EscapedFunctions.POWER+
+ ','+EscapedFunctions.RADIANS+','+EscapedFunctions.RAND+
+ ','+EscapedFunctions.ROUND+','+EscapedFunctions.SIGN+
+ ','+EscapedFunctions.SIN+','+EscapedFunctions.SQRT+
+ ','+EscapedFunctions.TAN+','+EscapedFunctions.TRUNCATE;
+
}
public String getStringFunctions() throws SQLException
{
if (Driver.logDebug)
Driver.debug("getStringFunctions");
- return "";
+ String funcs = EscapedFunctions.ASCII+','+EscapedFunctions.CHAR+
+ ','+EscapedFunctions.CONCAT+
+ ','+EscapedFunctions.LCASE+','+EscapedFunctions.LEFT+
+ ','+EscapedFunctions.LENGTH+
+ ','+EscapedFunctions.LTRIM+','+EscapedFunctions.REPEAT+
+ ','+EscapedFunctions.RTRIM+
+ ','+EscapedFunctions.SPACE+','+EscapedFunctions.SUBSTRING+
+ ','+EscapedFunctions.UCASE;
+
+ // Currently these don't work correctly with parameterized
+ // arguments, so leave them out. They reorder the arguments
+ // when rewriting the query, but no translation layer is provided,
+ // so a setObject(N, obj) will not go to the correct parameter.
+ //','+EscapedFunctions.INSERT+','+EscapedFunctions.LOCATE+
+ //','+EscapedFunctions.RIGHT+
+
+ if (connection.haveMinimumServerVersion("7.3")) {
+ funcs += ','+EscapedFunctions.REPLACE;
+ }
+
+ return funcs;
}
public String getSystemFunctions() throws SQLException
{
if (Driver.logDebug)
Driver.debug("getSystemFunctions");
- return "";
+ if (connection.haveMinimumServerVersion("7.3")){
+ return EscapedFunctions.DATABASE+','+EscapedFunctions.IFNULL+
+ ','+EscapedFunctions.USER;
+ } else {
+ return EscapedFunctions.IFNULL+
+ ','+EscapedFunctions.USER;
+ }
}
public String getTimeDateFunctions() throws SQLException
{
if (Driver.logDebug)
Driver.debug("getTimeDateFunctions");
- return "";
+ return EscapedFunctions.CURDATE+','+EscapedFunctions.CURTIME+
+ ','+EscapedFunctions.DAYNAME+','+EscapedFunctions.DAYOFMONTH+
+ ','+EscapedFunctions.DAYOFWEEK+','+EscapedFunctions.DAYOFYEAR+
+ ','+EscapedFunctions.HOUR+','+EscapedFunctions.MINUTE+
+ ','+EscapedFunctions.MONTH+
+ ','+EscapedFunctions.MONTHNAME+','+EscapedFunctions.NOW+
+ ','+EscapedFunctions.QUARTER+','+EscapedFunctions.SECOND+
+ ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEAR;
}
/*
diff --git a/org/postgresql/jdbc2/AbstractJdbc2Statement.java b/org/postgresql/jdbc2/AbstractJdbc2Statement.java
index c66ff0bb..d0fa3513 100644
--- a/org/postgresql/jdbc2/AbstractJdbc2Statement.java
+++ b/org/postgresql/jdbc2/AbstractJdbc2Statement.java
@@ -3,7 +3,7 @@
* Copyright (c) 2004, PostgreSQL Global Development Group
*
* IDENTIFICATION
-* $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v 1.50 2004/11/17 02:43:49 jurka Exp $
+* $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v 1.51 2004/12/11 04:13:35 jurka Exp $
*
*-------------------------------------------------------------------------
*/
@@ -11,10 +11,13 @@ package org.postgresql.jdbc2;
import java.io.*;
+import java.lang.reflect.InvocationTargetException;
+import java.lang.reflect.Method;
import java.math.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;
+
import org.postgresql.Driver;
import org.postgresql.largeobject.*;
import org.postgresql.core.*;
@@ -61,12 +64,12 @@ public abstract class AbstractJdbc2Statement implements BaseStatement
// Static variables for parsing SQL when replaceProcessing is true.
private static final short IN_SQLCODE = 0;
private static final short IN_STRING = 1;
+ private static final short IN_IDENTIFIER = 6;
private static final short BACKSLASH = 2;
private static final short ESC_TIMEDATE = 3;
private static final short ESC_FUNCTION = 4;
private static final short ESC_OUTERJOIN = 5;
-
// Some performance caches
private StringBuffer sbuf = new StringBuffer(32);
@@ -659,95 +662,196 @@ public abstract class AbstractJdbc2Statement implements BaseStatement
*
* Currently implemented Escape clauses are those mentioned in 11.3
* in the specification. Basically we look through the sql string for
- * {d xxx}, {t xxx} or {ts xxx} in non-string sql code. When we find
- * them, we just strip the escape part leaving only the xxx part.
+ * {d xxx}, {t xxx}, {ts xxx}, {oj xxx} or {fn xxx} in non-string sql
+ * code. When we find them, we just strip the escape part leaving only
+ * the xxx part.
* So, something like "select * from x where d={d '2001-10-09'}" would
* return "select * from x where d= '2001-10-09'".
*/
- protected String replaceProcessing(String p_sql)
+ protected String replaceProcessing(String p_sql) throws SQLException
{
if (replaceProcessingEnabled)
{
// Since escape codes can only appear in SQL CODE, we keep track
// of if we enter a string or not.
StringBuffer newsql = new StringBuffer(p_sql.length());
- short state = IN_SQLCODE;
+ parseSql(p_sql,0,newsql,false);
+ return newsql.toString();
+ }
+ else
+ {
+ return p_sql;
+ }
+ }
+
+ /**
+ * parse the given sql from index i, appending it to the gven buffer
+ * until we hit an unmatched right parentheses or end of string. When
+ * the stopOnComma flag is set we also stop processing when a comma is
+ * found in sql text that isn't inside nested parenthesis.
+ *
+ * @param p_sql the original query text
+ * @param i starting position for replacing
+ * @param newsql where to write the replaced output
+ * @param stopOnComma should we stop after hitting the first comma in sql text?
+ * @return the position we stopped processing at
+ */
+ private int parseSql(String p_sql,int i,StringBuffer newsql, boolean stopOnComma)throws SQLException{
+ short state = IN_SQLCODE;
+ int len = p_sql.length();
+ int nestedParenthesis=0;
+ boolean endOfNested=false;
- int i = -1;
- int len = p_sql.length();
- while (++i < len)
+ // because of the ++i loop
+ i--;
+ while (!endOfNested && ++i < len)
+ {
+ char c = p_sql.charAt(i);
+ switch (state)
{
- char c = p_sql.charAt(i);
- switch (state)
- {
- case IN_SQLCODE:
- if (c == '\'') // start of a string?
- state = IN_STRING;
- else if (c == '{') // start of an escape code?
- if (i + 1 < len)
+ case IN_SQLCODE:
+ if (c == '\'') // start of a string?
+ state = IN_STRING;
+ else if (c == '"') // start of a identifer?
+ state = IN_IDENTIFIER;
+ else if (c=='(') { // begin nested sql
+ nestedParenthesis++;
+ } else if (c==')') { // end of nested sql
+ nestedParenthesis--;
+ if (nestedParenthesis<0){
+ endOfNested=true;
+ break;
+ }
+ } else if (stopOnComma && c==',' && nestedParenthesis==0) {
+ endOfNested=true;
+ break;
+ } else if (c == '{') { // start of an escape code?
+ if (i + 1 < len)
+ {
+ char next = p_sql.charAt(i + 1);
+ char nextnext = (i + 2 < len) ? p_sql.charAt(i + 2) : '\0';
+ if (next == 'd' || next == 'D')
{
- char next = p_sql.charAt(i + 1);
- char nextnext = (i + 2 < len) ? p_sql.charAt(i + 2) : '\0';
- if (next == 'd' || next == 'D')
- {
- state = ESC_TIMEDATE;
- i++;
- break;
- }
- else if (next == 't' || next == 'T')
- {
- state = ESC_TIMEDATE;
- i += (nextnext == 's' || nextnext == 'S') ? 2 : 1;
- break;
- }
- else if ( next == 'f' || next == 'F' )
- {
- state = ESC_FUNCTION;
- i += (nextnext == 'n' || nextnext == 'N') ? 2 : 1;
- break;
- }
- else if ( next == 'o' || next == 'O' )
- {
- state = ESC_OUTERJOIN;
- i += (nextnext == 'j' || nextnext == 'J') ? 2 : 1;
- break;
- }
-
+ state = ESC_TIMEDATE;
+ i++;
+ break;
}
- newsql.append(c);
- break;
-
- case IN_STRING:
- if (c == '\'') // end of string?
- state = IN_SQLCODE;
- else if (c == '\\') // a backslash?
- state = BACKSLASH;
+ else if (next == 't' || next == 'T')
+ {
+ state = ESC_TIMEDATE;
+ i += (nextnext == 's' || nextnext == 'S') ? 2 : 1;
+ break;
+ }
+ else if ( next == 'f' || next == 'F' )
+ {
+ state = ESC_FUNCTION;
+ i += (nextnext == 'n' || nextnext == 'N') ? 2 : 1;
+ break;
+ }
+ else if ( next == 'o' || next == 'O' )
+ {
+ state = ESC_OUTERJOIN;
+ i += (nextnext == 'j' || nextnext == 'J') ? 2 : 1;
+ break;
+ }
+ }
+ }
+ newsql.append(c);
+ break;
- newsql.append(c);
- break;
+ case IN_STRING:
+ if (c == '\'') // end of string?
+ state = IN_SQLCODE;
+ else if (c == '\\') // a backslash?
+ state = BACKSLASH;
- case BACKSLASH:
- state = IN_STRING;
+ newsql.append(c);
+ break;
+
+ case IN_IDENTIFIER:
+ if (c == '"') // end of identifier
+ state = IN_SQLCODE;
+ newsql.append(c);
+ break;
- newsql.append(c);
- break;
+ case BACKSLASH:
+ state = IN_STRING;
- case ESC_TIMEDATE:
- case ESC_FUNCTION:
- case ESC_OUTERJOIN:
- if (c == '}')
- state = IN_SQLCODE; // end of escape code.
- else
- newsql.append(c);
- break;
- } // end switch
- }
+ newsql.append(c);
+ break;
- return newsql.toString();
+ case ESC_FUNCTION:
+ // extract function name
+ String functionName;
+ int posArgs = p_sql.indexOf('(',i);
+ if (posArgs!=-1){
+ functionName=p_sql.substring(i,posArgs).trim();
+ // extract arguments
+ i= posArgs+1;// we start the scan after the first (
+ StringBuffer args=new StringBuffer();
+ i = parseSql(p_sql,i,args,false);
+ // translate the function and parse arguments
+ newsql.append(escapeFunction(functionName,args.toString()));
+ }
+ // go to the end of the function copying anything found
+ i++;
+ while (i<len && p_sql.charAt(i)!='}')
+ newsql.append(p_sql.charAt(i));
+ state = IN_SQLCODE; // end of escaped function (or query)
+ break;
+ case ESC_TIMEDATE:
+ case ESC_OUTERJOIN:
+ if (c == '}')
+ state = IN_SQLCODE; // end of escape code.
+ else
+ newsql.append(c);
+ break;
+ } // end switch
}
- else
- {
- return p_sql;
+ return i;
+ }
+
+ /**
+ * generate sql for escaped functions
+ * @param functionName the escaped function name
+ * @param args the arguments for this functin
+ * @return the right postgreSql sql
+ */
+ protected String escapeFunction(String functionName, String args) throws SQLException{
+ // parse function arguments
+ int len = args.length();
+ int i=0;
+ ArrayList parsedArgs = new ArrayList();
+ while (i<len){
+ StringBuffer arg = new StringBuffer();
+ int lastPos=i;
+ i=parseSql(args,i,arg,true);
+ int nestedCount=0;
+ if (lastPos!=i){
+ parsedArgs.add(arg);
+ }
+ i++;
+ }
+ // we can now tranlate escape functions
+ try{
+ Method escapeMethod = EscapedFunctions.getFunction(functionName);
+ return (String) escapeMethod.invoke(null,new Object[] {parsedArgs});
+ }catch(InvocationTargetException e){
+ if (e.getTargetException() instanceof SQLException)
+ throw (SQLException) e.getTargetException();
+ else
+ throw new SQLException(e.getTargetException().getMessage());
+ }catch (Exception e){
+ // by default the function name is kept unchanged
+ StringBuffer buf = new StringBuffer();
+ buf.append(functionName).append('(');
+ for (int iArg = 0;iArg<parsedArgs.size();iArg++){
+ buf.append(parsedArgs.get(iArg));
+ if (iArg!=(parsedArgs.size()-1))
+ buf.append(',');
+ }
+ buf.append(')');
+ return buf.toString();
}
}
diff --git a/org/postgresql/jdbc2/EscapedFunctions.java b/org/postgresql/jdbc2/EscapedFunctions.java
new file mode 100644
index 00000000..d8015b1a
--- /dev/null
+++ b/org/postgresql/jdbc2/EscapedFunctions.java
@@ -0,0 +1,481 @@
+/*-------------------------------------------------------------------------
+*
+* Copyright (c) 2004, PostgreSQL Global Development Group
+*
+* IDENTIFICATION
+* $PostgreSQL$
+*
+*-------------------------------------------------------------------------
+*/
+package org.postgresql.jdbc2;
+
+import java.lang.reflect.Method;
+import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import org.postgresql.util.GT;
+import org.postgresql.util.PSQLException;
+
+/**
+ * this class stores supported escaped function
+ * @author Xavier Poinsard
+ */
+public class EscapedFunctions {
+ // numeric functions names
+ public final static String ABS="abs";
+ public final static String ACOS="acos";
+ public final static String ASIN="asin";
+ public final static String ATAN="atan";
+ public final static String ATAN2="atan2";
+ public final static String CEILING="ceiling";
+ public final static String COS="cos";
+ public final static String COT="cot";
+ public final static String DEGREES="degrees";
+ public final static String EXP="exp";
+ public final static String FLOOR="floor";
+ public final static String LOG="log";
+ public final static String LOG10="log10";
+ public final static String MOD="mod";
+ public final static String PI="pi";
+ public final static String POWER="power";
+ public final static String RADIANS="radians";
+ public final static String RAND="rand";
+ public final static String ROUND="round";
+ public final static String SIGN="sign";
+ public final static String SIN="sin";
+ public final static String SQRT="sqrt";
+ public final static String TAN="tan";
+ public final static String TRUNCATE="truncate";
+
+ // string function names
+ public final static String ASCII="ascii";
+ public final static String CHAR="char";
+ public final static String CONCAT="concat";
+ public final static String INSERT="insert"; // change arguments order
+ public final static String LCASE="lcase";
+ public final static String LEFT="left";
+ public final static String LENGTH="length";
+ public final static String LOCATE="locate"; // the 3 args version duplicate args
+ public final static String LTRIM="ltrim";
+ public final static String REPEAT="repeat";
+ public final static String REPLACE="replace";
+ public final static String RIGHT="right"; // duplicate args
+ public final static String RTRIM="rtrim";
+ public final static String SPACE="space";
+ public final static String SUBSTRING="substring";
+ public final static String UCASE="ucase";
+ // soundex and difference are implemented on the server side by
+ // the contrib/fuzzystrmatch module. We provide a translation
+ // for this in the driver, but since we don't want to bother with run
+ // time detection of this module's installation we don't report these
+ // methods as supported in DatabaseMetaData.
+
+ // date time function names
+ public final static String CURDATE="curdate";
+ public final static String CURTIME="curtime";
+ public final static String DAYNAME="dayname";
+ public final static String DAYOFMONTH="dayofmonth";
+ public final static String DAYOFWEEK="dayofweek";
+ public final static String DAYOFYEAR="dayofyear";
+ public final static String HOUR="hour";
+ public final static String MINUTE="minute";
+ public final static String MONTH="month";
+ public final static String MONTHNAME="monthname";
+ public final static String NOW="now";
+ public final static String QUARTER="quarter";
+ public final static String SECOND="second";
+ public final static String WEEK="week";
+ public final static String YEAR="year";
+ // TODO : timestampadd and timestampdiff
+
+ // system functions
+ public final static String DATABASE="database";
+ public final static String IFNULL="ifnull";
+ public final static String USER="user";
+
+
+ /** storage for functions implementations */
+ private static Map functionMap = null;
+
+ /**
+ * get Method object implementing the given function
+ * @param functionName name of the searched function
+ * @return a Method object or null if not found
+ */
+ public static Method getFunction(String functionName){
+ if (functionMap==null){
+ Method[] arrayMeths = EscapedFunctions.class.getDeclaredMethods();
+ functionMap = new HashMap(arrayMeths.length*2);
+ for (int i=0;i<arrayMeths.length;i++){
+ Method meth = arrayMeths[i];
+ if (meth.getName().startsWith("sql"))
+ functionMap.put(meth.getName().toLowerCase(),meth);
+ }
+ }
+ return (Method) functionMap.get("sql"+functionName.toLowerCase());
+ }
+
+ // ** numeric functions translations **
+ /** ceiling to ceil translation */
+ public static String sqlceiling(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("ceil(");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","ceiling"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** log to ln translation */
+ public static String sqllog(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("ln(");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","log"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** log10 to log translation */
+ public static String sqllog10(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("log(");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","log10"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** power to pow translation */
+ public static String sqlpower(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("pow(");
+ if (parsedArgs.size()!=2){
+ throw new PSQLException(GT.tr("{0} function takes two and only two arguments.","power"));
+ }
+ buf.append(parsedArgs.get(0)).append(',').append(parsedArgs.get(1));
+ return buf.append(')').toString();
+ }
+
+ /** rand to setSeed + random translation */
+ public static String sqlrand(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()==0){
+ return "random()";
+ }else if (parsedArgs.size()==1){
+ return "(setseed("+parsedArgs.get(0)+")*0+random())";
+ }else{
+ throw new PSQLException(GT.tr("rand function only takes zero or one argument(the seed)."));
+ }
+ }
+
+ /** truncate to trunc translation */
+ public static String sqltruncate(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("trunc(");
+ if (parsedArgs.size()!=2){
+ throw new PSQLException(GT.tr("{0} function takes two and only two arguments.","truncate"));
+ }
+ buf.append(parsedArgs.get(0)).append(',').append(parsedArgs.get(1));
+ return buf.append(')').toString();
+ }
+
+ // ** string functions translations **
+ /** char to chr translation */
+ public static String sqlchar(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("chr(");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","char"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** concat translation */
+ public static String sqlconcat(List parsedArgs){
+ StringBuffer buf = new StringBuffer();
+ buf.append('(');
+ for (int iArg = 0;iArg<parsedArgs.size();iArg++){
+ buf.append(parsedArgs.get(iArg));
+ if (iArg!=(parsedArgs.size()-1))
+ buf.append(" || ");
+ }
+ return buf.append(')').toString();
+ }
+
+ /** insert to overlay translation */
+ public static String sqlinsert(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("overlay(");
+ if (parsedArgs.size()!=4){
+ throw new PSQLException(GT.tr("{0} function takes four and only four argument.","insert"));
+ }
+ buf.append(parsedArgs.get(0)).append(" placing ").append(parsedArgs.get(3));
+ buf.append(" from ").append(parsedArgs.get(1)).append(" for ").append(parsedArgs.get(2));
+ return buf.append(')').toString();
+ }
+
+ /** lcase to lower translation */
+ public static String sqllcase(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("lower(");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","lcase"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** left to substring translation */
+ public static String sqlleft(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("substring(");
+ if (parsedArgs.size()!=2){
+ throw new PSQLException(GT.tr("{0} function takes two and only two arguments.","left"));
+ }
+ buf.append(parsedArgs.get(0)).append(" for ").append(parsedArgs.get(1));
+ return buf.append(')').toString();
+ }
+
+ /** length translation */
+ public static String sqllength(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("length(trim(trailing from ");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","length"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append("))").toString();
+ }
+
+ /** locate translation */
+ public static String sqllocate(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()==2){
+ return "position("+parsedArgs.get(0)+" in "+parsedArgs.get(1)+")";
+ }else if (parsedArgs.size()==3){
+ String tmp = "position("+parsedArgs.get(0)+" in substring("+parsedArgs.get(1)+" from "+parsedArgs.get(2)+"))";
+ return "("+parsedArgs.get(2)+"*sign("+tmp+")+"+tmp+")";
+ }else{
+ throw new PSQLException(GT.tr("{0} function takes two or three arguments.","locate"));
+ }
+ }
+
+ /** ltrim translation */
+ public static String sqlltrim(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("trim(leading from ");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","ltrim"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** right to substring translation */
+ public static String sqlright(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("substring(");
+ if (parsedArgs.size()!=2){
+ throw new PSQLException(GT.tr("{0} function takes two and only two arguments.","right"));
+ }
+ buf.append(parsedArgs.get(0)).append(" from (length(").append(parsedArgs.get(0)).append(")+1-").append(parsedArgs.get(1));
+ return buf.append("))").toString();
+ }
+
+ /** rtrim translation */
+ public static String sqlrtrim(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("trim(trailing from ");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","rtrim"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** space translation */
+ public static String sqlspace(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("repeat(' ',");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","space"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** substring to substr translation */
+ public static String sqlsubstring(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()==2){
+ return "substr("+parsedArgs.get(0)+","+parsedArgs.get(1)+")";
+ }else if (parsedArgs.size()==3){
+ return "substr("+parsedArgs.get(0)+","+parsedArgs.get(1)+","+parsedArgs.get(2)+")";
+ }else{
+ throw new PSQLException(GT.tr("{0} function takes two or three arguments.","substring"));
+ }
+ }
+
+ /** ucase to upper translation */
+ public static String sqlucase(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("upper(");
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","ucase"));
+ }
+ buf.append(parsedArgs.get(0));
+ return buf.append(')').toString();
+ }
+
+ /** difference to levenshtein translation */
+ public static String sqldifference(List parsedArgs) throws SQLException{
+ StringBuffer buf = new StringBuffer();
+ buf.append("levenshtein(");
+ if (parsedArgs.size()!=2){
+ throw new PSQLException(GT.tr("{0} function takes two and only two arguments.","difference"));
+ }
+ buf.append(parsedArgs.get(0)).append(",").append(parsedArgs.get(1));
+ return buf.append(")").toString();
+ }
+
+
+ /** curdate to current_date translation */
+ public static String sqlcurdate(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=0){
+ throw new PSQLException(GT.tr("{0} function doesn't take any argument.","curdate"));
+ }
+ return "current_date";
+ }
+
+ /** curtime to current_time translation */
+ public static String sqlcurtime(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=0){
+ throw new PSQLException(GT.tr("{0} function doesn't take any argument.","curtime"));
+ }
+ return "current_time";
+ }
+
+ /** dayname translation */
+ public static String sqldayname(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","dayname"));
+ }
+ return "to_char("+parsedArgs.get(0)+",'Day')";
+ }
+
+ /** dayofmonth translation */
+ public static String sqldayofmonth(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","dayofmonth"));
+ }
+ return "extract(day from "+parsedArgs.get(0)+")";
+ }
+
+ /** dayofweek translation */
+ public static String sqldayofweek(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","dayofweek"));
+ }
+ return "extract(dow from "+parsedArgs.get(0)+")";
+ }
+
+ /** dayofyear translation */
+ public static String sqldayofyear(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","dayofyear"));
+ }
+ return "extract(doy from "+parsedArgs.get(0)+")";
+ }
+
+ /** hour translation */
+ public static String sqlhour(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","hour"));
+ }
+ return "extract(hour from "+parsedArgs.get(0)+")";
+ }
+
+ /** minute translation */
+ public static String sqlminute(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","minute"));
+ }
+ return "extract(minute from "+parsedArgs.get(0)+")";
+ }
+
+ /** month translation */
+ public static String sqlmonth(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","month"));
+ }
+ return "extract(month from "+parsedArgs.get(0)+")";
+ }
+
+ /** monthname translation */
+ public static String sqlmonthname(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","monthname"));
+ }
+ return "to_char("+parsedArgs.get(0)+",'Month')";
+ }
+
+ /** quarter translation */
+ public static String sqlquarter(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","quarter"));
+ }
+ return "extract(quarter from "+parsedArgs.get(0)+")";
+ }
+
+ /** second translation */
+ public static String sqlsecond(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","second"));
+ }
+ return "extract(second from "+parsedArgs.get(0)+")";
+ }
+
+ /** week translation */
+ public static String sqlweek(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","week"));
+ }
+ return "extract(week from "+parsedArgs.get(0)+")";
+ }
+
+ /** year translation */
+ public static String sqlyear(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=1){
+ throw new PSQLException(GT.tr("{0} function takes one and only one argument.","year"));
+ }
+ return "extract(year from "+parsedArgs.get(0)+")";
+ }
+
+ /** database translation */
+ public static String sqldatabase(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=0){
+ throw new PSQLException(GT.tr("{0} function doesn't take any argument.","database"));
+ }
+ return "current_database()";
+ }
+
+ /** ifnull translation */
+ public static String sqlifnull(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=2){
+ throw new PSQLException(GT.tr("{0} function takes two and only two arguments.","ifnull"));
+ }
+ return "coalesce("+parsedArgs.get(0)+","+parsedArgs.get(1)+")";
+ }
+
+ /** user translation */
+ public static String sqluser(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=0){
+ throw new PSQLException(GT.tr("{0} function doesn't take any argument.","user"));
+ }
+ return "user";
+ }
+}
diff --git a/org/postgresql/test/jdbc2/CallableStmtTest.java b/org/postgresql/test/jdbc2/CallableStmtTest.java
index aa687393..c70776e3 100644
--- a/org/postgresql/test/jdbc2/CallableStmtTest.java
+++ b/org/postgresql/test/jdbc2/CallableStmtTest.java
@@ -3,7 +3,7 @@
* Copyright (c) 2004, PostgreSQL Global Development Group
*
* IDENTIFICATION
-* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/CallableStmtTest.java,v 1.7 2004/11/07 22:16:42 jurka Exp $
+* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/CallableStmtTest.java,v 1.8 2004/11/09 08:54:08 jurka Exp $
*
*-------------------------------------------------------------------------
*/
@@ -47,6 +47,9 @@ public class CallableStmtTest extends TestCase
stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getNumeric (numeric) " +
"RETURNS numeric AS ' DECLARE inString alias for $1; " +
"begin return 42; end; ' LANGUAGE 'plpgsql';");
+ stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getNumericWithoutArg() " +
+ "RETURNS numeric AS ' " +
+ "begin return 42; end; ' LANGUAGE 'plpgsql';");
stmt.close ();
}
@@ -57,6 +60,7 @@ public class CallableStmtTest extends TestCase
stmt.execute ("drop FUNCTION testspg__getDouble (float);");
stmt.execute ("drop FUNCTION testspg__getInt (int);");
stmt.execute ("drop FUNCTION testspg__getNumeric (numeric);");
+ stmt.execute ("drop FUNCTION testspg__getNumericWithoutArg ();");
TestUtil.closeDB(con);
}
@@ -73,8 +77,7 @@ public class CallableStmtTest extends TestCase
call.setDouble (2, (double)3.04);
call.registerOutParameter (1, Types.DOUBLE);
call.execute ();
- double result = call.getDouble (1);
- assertTrue ("correct return from getString ()", result == 42.42);
+ assertEquals(42.42, call.getDouble(1), 0.00001);
}
public void testGetInt () throws Throwable
@@ -83,8 +86,7 @@ public class CallableStmtTest extends TestCase
call.setInt (2, 4);
call.registerOutParameter (1, Types.INTEGER);
call.execute ();
- int result = call.getInt (1);
- assertTrue ("correct return from getString ()", result == 42);
+ assertEquals(42, call.getInt(1));
}
public void testGetNumeric () throws Throwable
@@ -93,9 +95,15 @@ public class CallableStmtTest extends TestCase
call.setBigDecimal (2, new java.math.BigDecimal(4));
call.registerOutParameter (1, Types.NUMERIC);
call.execute ();
- java.math.BigDecimal result = call.getBigDecimal (1);
- assertTrue ("correct return from getString ()",
- result.equals (new java.math.BigDecimal(42)));
+ assertEquals(new java.math.BigDecimal(42), call.getBigDecimal(1));
+ }
+
+ public void testGetNumericWithoutArg () throws Throwable
+ {
+ CallableStatement call = con.prepareCall (func + pkgName + "getNumericWithoutArg () }");
+ call.registerOutParameter (1, Types.NUMERIC);
+ call.execute ();
+ assertEquals(new java.math.BigDecimal(42), call.getBigDecimal(1));
}
public void testGetString () throws Throwable
@@ -104,8 +112,7 @@ public class CallableStmtTest extends TestCase
call.setString (2, "foo");
call.registerOutParameter (1, Types.VARCHAR);
call.execute ();
- String result = call.getString (1);
- assertTrue ("correct return from getString ()", result.equals ("bob"));
+ assertEquals("bob", call.getString(1));
}
diff --git a/org/postgresql/test/jdbc2/StatementTest.java b/org/postgresql/test/jdbc2/StatementTest.java
index d1f9f243..54995d8b 100644
--- a/org/postgresql/test/jdbc2/StatementTest.java
+++ b/org/postgresql/test/jdbc2/StatementTest.java
@@ -3,12 +3,13 @@
* Copyright (c) 2004, PostgreSQL Global Development Group
*
* IDENTIFICATION
-* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v 1.8 2004/11/07 22:17:00 jurka Exp $
+* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v 1.9 2004/11/09 08:55:11 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresql.test.jdbc2;
+import org.postgresql.jdbc2.AbstractJdbc2Connection;
import org.postgresql.test.TestUtil;
import junit.framework.*;
import java.sql.*;
@@ -32,13 +33,14 @@ public class StatementTest extends TestCase
TestUtil.createTempTable(con, "test_statement",
"i int");
TestUtil.createTempTable(con, "escapetest",
- "ts timestamp, d date, t time");
+ "ts timestamp, d date, t time, \")\" varchar(5), \"\"\"){a}'\" text ");
}
protected void tearDown() throws Exception
{
super.tearDown();
TestUtil.dropTable( con, "test_statement" );
+ TestUtil.dropTable( con, "escapetest" );
con.close();
}
@@ -102,6 +104,7 @@ public class StatementTest extends TestCase
count = stmt.executeUpdate("CREATE TEMP TABLE another_table (a int)");
assertEquals(0, count);
}
+
public void testEscapeProcessing() throws SQLException
{
Statement stmt = con.createStatement();
@@ -116,14 +119,177 @@ public class StatementTest extends TestCase
count = stmt.executeUpdate("insert into escapetest (t) values ({t '00:00:00'})");
assertEquals(1, count);
-
ResultSet rs = stmt.executeQuery( "select {fn version()} as version" );
assertTrue(rs.next());
+
+ // check nested and multiple escaped functions
+ rs = stmt.executeQuery( "select {fn version()} as version, {fn log({fn log(3.0)})} as log" );
+ assertTrue(rs.next());
+ assertEquals(Math.log(Math.log(3)), rs.getDouble(2), 0.00001);
+ stmt.executeUpdate("UPDATE escapetest SET \")\" = 'a', \"\"\"){a}'\" = 'b'");
+
+ // check "difficult" values
+ rs = stmt.executeQuery("select {fn concat(')',escapetest.\")\")} as concat" +
+ ", {fn concat('{','}')} " +
+ ", {fn concat('\\'','\"')} " +
+ ", {fn concat(\"\"\"){a}'\", '''}''')} " +
+ " FROM escapetest");
+ assertTrue(rs.next());
+ assertEquals(")a", rs.getString(1));
+ assertEquals("{}", rs.getString(2));
+ assertEquals("'\"", rs.getString(3));
+ assertEquals("b'}'", rs.getString(4));
+
count = stmt.executeUpdate( "create temp table b (i int)" );
assertEquals(0, count);
rs = stmt.executeQuery( "select * from test_statement as a {oj left outer join b on (a.i=b.i)} ");
assertTrue(!rs.next());
}
+
+
+ public void testPreparedFunction() throws SQLException
+ {
+ PreparedStatement pstmt = con.prepareStatement("SELECT {fn concat('a', ?)}");
+ pstmt.setInt(1, 5);
+ ResultSet rs = pstmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("a5", rs.getString(1));
+ }
+
+ public void testNumericFunctions() throws SQLException
+ {
+ Statement stmt = con.createStatement();
+
+ ResultSet rs = stmt.executeQuery("select {fn abs(-2.3)} as abs ");
+ assertTrue(rs.next());
+ assertEquals(2.3f, rs.getFloat(1), 0.00001);
+
+ rs = stmt.executeQuery("select {fn acos(-0.6)} as acos ");
+ assertTrue(rs.next());
+ assertEquals(Math.acos(-0.6), rs.getDouble(1), 0.00001);
+
+ rs = stmt.executeQuery("select {fn asin(-0.6)} as asin ");
+ assertTrue(rs.next());
+ assertEquals(Math.asin(-0.6), rs.getDouble(1), 0.00001);
+
+ rs = stmt.executeQuery("select {fn atan(-0.6)} as atan ");
+ assertTrue(rs.next());
+ assertEquals(Math.atan(-0.6), rs.getDouble(1), 0.00001);
+
+ rs = stmt.executeQuery("select {fn atan2(-2.3,7)} as atan2 ");
+ assertTrue(rs.next());
+ assertEquals(Math.atan2(-2.3,7), rs.getDouble(1), 0.00001);
+
+ rs = stmt.executeQuery("select {fn ceiling(-2.3)} as ceiling ");
+ assertTrue(rs.next());
+ assertEquals(-2, rs.getDouble(1), 0.00001);
+
+ rs = stmt.executeQuery("select {fn cos(-2.3)} as cos, {fn cot(-2.3)} as cot ");
+ assertTrue(rs.next());
+ assertEquals(Math.cos(-2.3), rs.getDouble(1), 0.00001);
+ assertEquals(1/Math.tan(-2.3), rs.getDouble(2), 0.00001);
+
+ rs = stmt.executeQuery("select {fn degrees({fn pi()})} as degrees ");
+ assertTrue(rs.next());
+ assertEquals(180, rs.getDouble(1), 0.00001);
+
+ rs = stmt.executeQuery("select {fn exp(-2.3)}, {fn floor(-2.3)}," +
+ " {fn log(2.3)},{fn log10(2.3)},{fn mod(3,2)}");
+ assertTrue(rs.next());
+ assertEquals(Math.exp(-2.3), rs.getDouble(1), 0.00001);
+ assertEquals(-3, rs.getDouble(2), 0.00001);
+ assertEquals(Math.log(2.3), rs.getDouble(3), 0.00001);
+ assertEquals(Math.log(2.3)/Math.log(10), rs.getDouble(4), 0.00001);
+ assertEquals(1, rs.getDouble(5), 0.00001);
+
+ rs = stmt.executeQuery("select {fn pi()}, {fn power(7,-2.3)}," +
+ " {fn radians(-180)},{fn rand(-2.3)},{fn round(3.1294,2)}");
+ assertTrue(rs.next());
+ assertEquals(Math.PI, rs.getDouble(1), 0.00001);
+ assertEquals(Math.pow(7,-2.3), rs.getDouble(2), 0.00001);
+ assertEquals(-Math.PI, rs.getDouble(3), 0.00001);
+ assertEquals(0.622468634332748, rs.getDouble(4), 0.00001);
+ assertEquals(3.13, rs.getDouble(5), 0.00001);
+
+ rs = stmt.executeQuery("select {fn sign(-2.3)}, {fn sin(-2.3)}," +
+ " {fn sqrt(2.3)},{fn tan(-2.3)},{fn truncate(3.1294,2)}");
+ assertTrue(rs.next());
+ assertEquals(-1, rs.getInt(1));
+ assertEquals(Math.sin(-2.3), rs.getDouble(2), 0.00001);
+ assertEquals(Math.sqrt(2.3), rs.getDouble(3), 0.00001);
+ assertEquals(Math.tan(-2.3), rs.getDouble(4), 0.00001);
+ assertEquals(3.12, rs.getDouble(5), 0.00001);
+ }
+
+ public void testStringFunctions() throws SQLException
+ {
+ Statement stmt = con.createStatement();
+ ResultSet rs = stmt.executeQuery("select {fn ascii(' test')},{fn char(32)}" +
+ ",{fn concat('ab','cd')}" +
+ ",{fn lcase('aBcD')},{fn left('1234',2)},{fn length('123 ')}" +
+ ",{fn locate('bc','abc')},{fn locate('bc','abc',3)}");
+ assertTrue(rs.next());
+ assertEquals(32,rs.getInt(1));
+ assertEquals(" ",rs.getString(2));
+ assertEquals("abcd",rs.getString(3));
+ assertEquals("abcd",rs.getString(4));
+ assertEquals("12",rs.getString(5));
+ assertEquals(3,rs.getInt(6));
+ assertEquals(2,rs.getInt(7));
+ assertEquals(0,rs.getInt(8));
+
+ if (TestUtil.haveMinimumServerVersion(con, "7.3")) {
+ rs = stmt.executeQuery("SELECT {fn insert('abcdef',3,2,'xxxx')}" +
+ ",{fn replace('abcdbc','bc','x')}");
+ assertTrue(rs.next());
+ assertEquals("abxxxxef",rs.getString(1));
+ assertEquals("axdx",rs.getString(2));
+ }
+
+ rs = stmt.executeQuery("select {fn ltrim(' ab')},{fn repeat('ab',2)}" +
+ ",{fn right('abcde',2)},{fn rtrim('ab ')}" +
+ ",{fn space(3)},{fn substring('abcd',2,2)}" +
+ ",{fn ucase('aBcD')}");
+ assertTrue(rs.next());
+ assertEquals("ab",rs.getString(1));
+ assertEquals("abab",rs.getString(2));
+ assertEquals("de",rs.getString(3));
+ assertEquals("ab",rs.getString(4));
+ assertEquals(" ",rs.getString(5));
+ assertEquals("bc",rs.getString(6));
+ assertEquals("ABCD",rs.getString(7));
+ }
+
+ public void testDateFunctions() throws SQLException
+ {
+ Statement stmt = con.createStatement();
+ ResultSet rs = stmt.executeQuery("select {fn curdate()},{fn curtime()}" +
+ ",{fn dayname({fn now()})}, {fn dayofmonth({fn now()})}" +
+ ",{fn dayofweek({fn now()})},{fn dayofyear({fn now()})}" +
+ ",{fn hour({fn now()})},{fn minute({fn now()})}" +
+ ",{fn month({fn now()})}" +
+ ",{fn monthname({fn now()})},{fn quarter({fn now()})}" +
+ ",{fn second({fn now()})},{fn week({fn now()})}" +
+ ",{fn year({fn now()})} ");
+ assertTrue(rs.next());
+ }
+
+ public void testSystemFunctions() throws SQLException
+ {
+ Statement stmt = con.createStatement();
+ ResultSet rs = stmt.executeQuery("select {fn ifnull(null,'2')}" +
+ ",{fn user()} ");
+ assertTrue(rs.next());
+ assertEquals("2",rs.getString(1));
+ assertEquals(TestUtil.getUser(),rs.getString(2));
+
+ if (TestUtil.haveMinimumServerVersion(con, "7.3")) {
+ rs = stmt.executeQuery("select {fn database()} ");
+ assertTrue(rs.next());
+ assertEquals(TestUtil.getDatabase(),rs.getString(1));
+ }
+ }
+
}
[parent: 1b36346cb5fd]