SEO

> The previous discussion does detail the remaining steps needed to get
> something ready to be committed: proper quoting, error checking, test
> cases, ... If you have the time and skill to work on these that would
> be appreciated.
>


Kris (and folks), I did actually find some justification for the time to
work on this. The attached patch should follow on from our last state in
Feb. Essentially the only thing I added was error checking of the
arguments to 'executeUpdate's columnNames. The checking is done in a new
Utils.needsQuoted(String in) (see javadoc) (feel free to rename this
method).

I opted to use the Quoting mechanism I already had in executeUpdate for
now, since the string validation (no 0x00 && no nested quotes) is being
done in needsQuoted (in the same loop that validates quotes and scans
for whitespace).

Let me know in detail what else needs to be implemented in terms of
error checking or methods. I did not add any unit-tests (that will be a
learning curve; seeking volunteers).

Questions:

-is whitespace the sole determinator for needing quoting? And other chars?

-is it fine to leave the string un-quoted if it contains no ws, vs
always quoting it (my feeling is yes).

-is '"' the only legal quoting chars? (I cant remember for having
dabbled with too many non-spec databases)

-my needsQuoted method throws if the identifier contains nested quotes
(foo"bar or "foo"bar"); is there a legal quote-escaping mechanism
similar to apostrophe doubling? eg: how or would one pass foo"bar (I
imagine quotes are never allowed in identifiers but don't have an SQL
spec handy)

Ken

PS - Kris, I recall you said the backslashes in the patch were
troublesome; did you find any fix for your patch tool aside from
translating them to '/'? If not I will translate them from hereto forth.

# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: C:\dev\java\proj\pgjdbc\pgjdbc
# This patch can be applied using context Tools: Patch action on respective folder.
# It uses platform neutral UTF-8 encoding and \n newlines.
# Above lines and this line are ignored by the patching process.
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 2\AbstractJdbc2Statement.java Base (1.104)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 2\AbstractJdbc2Statement.java Locally Modified (Based On 1.104)
***************
*** 286,291 ****
--- 286,318 ----
}

/*
+ * Execute a SQL INSERT, UPDATE or DELETE statement. In addition
+ * SQL statements that return nothing such as SQL DDL statements
+ * can be executed
+ *
+ * @param sql a SQL statement
+ * @return either a row count, or 0 for SQL commands
+ * @exception SQLException if a database access error occurs
+ */
+ protected int executeUpdateGetResults(String p_sql) throws SQLException
+ {
+ if (preparedQuery != null)
+ throw new PSQLException(GT.tr("Can''t use query methods that take a query string on a PreparedStatement."),
+ PSQLState.WRONG_OBJECT_TYPE);
+ if( isFunction )
+ {
+ executeWithFlags(p_sql, 0);
+ return 0;
+ }
+ checkClosed();
+ p_sql = replaceProcessing(p_sql);
+ Query simpleQuery = connection.getQueryExecutor().createSimpleQuery(p_ sql);
+ execute(simpleQuery, null, 0);
+ this.lastSimpleQuery = simpleQuery;
+ return getUpdateCount();
+ }
+
+ /*
* Execute a SQL INSERT, UPDATE or DELETE statement. In addition,
* SQL statements that return nothing such as SQL DDL statements can
* be executed.
Index: org/postgresql/jdbc3/AbstractJdbc3Statement.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 3\AbstractJdbc3Statement.java Base (1.21)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 3\AbstractJdbc3Statement.java Locally Modified (Based On 1.21)
***************
*** 19,24 ****
--- 19,27 ----
import org.postgresql.core.QueryExecutor;
import org.postgresql.core.Field;
import org.postgresql.core.BaseConnection;
+ import org.postgresql.core.Utils;
+ import org.postgresql.jdbc2.AbstractJdbc2Connection;
+ import org.postgresql.jdbc2.AbstractJdbc2Statement.Statem entResultHandler;
import org.postgresql.util.GT;

/**
***************
*** 28,33 ****
--- 31,37 ----
*/
public abstract class AbstractJdbc3Statement extends org.postgresql.jdbc2.AbstractJdbc2Statement
{
+
private final int rsHoldability;

public AbstractJdbc3Statement (AbstractJdbc3Connection c, int rsType, int rsConcurrency, int rsHoldability) throws SQLException
***************
*** 106,112 ****
*/
public ResultSet getGeneratedKeys() throws SQLException
{
! return createDriverResultSet(new Field[0], new Vector());
}

/**
--- 110,118 ----
*/
public ResultSet getGeneratedKeys() throws SQLException
{
! return result==null ?
! createDriverResultSet(new Field[0], new Vector())
! : result.getResultSet();
}

/**
***************
*** 135,141 ****
{
if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
return executeUpdate(sql);
!
throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

--- 141,147 ----
{
if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
return executeUpdate(sql);
! //fix me : impl NO_GENERATED_KEYS & RETURN_GENERATED_KEYS
throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

***************
*** 184,198 ****
*/
public int executeUpdate(String sql, String columnNames[]) throws SQLException
{
! if (columnNames.length == 0)
return executeUpdate(sql);
!
! throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

/**
* Executes the given SQL statement, which may return multiple results,
--- 190,236 ----
*/
public int executeUpdate(String sql, String columnNames[]) throws SQLException
{
! //fix me : columnNames only quoted if contain 0x20
! String prefix = sql.substring(0,10).toLowerCase();
! if (columnNames==null || prefix.indexOf("insert")==-1)
! return executeUpdateGetResults(sql);
! if (!(connection instanceof AbstractJdbc2Connection))
! {
! throw new PSQLException(GT.tr("Driver version does not support returning generated keys.")+" "+connection.getClass().getName(), PSQLState.NOT_IMPLEMENTED);
! }
! AbstractJdbc2Connection con = (AbstractJdbc2Connection)connection;
! int args = columnNames.length;
! if (!connection.haveMinimumServerVersion("8.2"))
! throw new PSQLException(GT.tr("Server version does not support returning generated keys.")+" (< "+"8.2"+")", PSQLState.NOT_IMPLEMENTED);
! if (args==0)
return executeUpdate(sql);
! StringBuffer s = new StringBuffer(sql.length()+(args*32));
! s.append(sql);
! s.append('\n');
! s.append("RETURNING");
! s.append(' ');
! boolean needsQuote;
! for (int i=0; i<args; i++)
! {
! String arg = columnNames[i];
! if (arg==null)
! //throw new NullPointerException("executeUpdate: null columnName at index "+i);
! throw new PSQLException(GT.tr("Null value in columnNames"), PSQLState.INVALID_PARAMETER_VALUE);
! if (i!=0)
! s.append(',');
! needsQuote = Utils.needsQuoted(arg);
! if (needsQuote)
! s.append('"');
! s.append(arg);
! if (needsQuote)
! s.append('"');
}
+ return executeUpdateGetResults(s.toString());
+ //throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
+ }

+
+
/**
* Executes the given SQL statement, which may return multiple results,
* and signals the driver that any
Index: org/postgresql/core/Utils.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\core \Utils.java Base (1.6)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\core \Utils.java Locally Modified (Based On 1.6)
***************
*** 146,152 ****
--- 146,186 ----

return sbuf;
}
+ /**
+ * return true if the string contains whitespace and is not already quoted, false otherwise
+ *
+ * @param in
+ * @return true if the string contains whitespace and is not already quoted
+ * @throws java.sql.SQLException if the string contains quotes inside its value
+ * (foo"bar or "foor"bar"), or contains char 0x00.
+ */
+ public static final boolean needsQuoted(String in) throws SQLException
+ {
+ int len = in.length();
+ //quoted and non-empty quotes:
+ boolean already = len>1 && in.charAt(0)=='"' && in.charAt(len-1)=='"';
+ if (already && len==2)
+ throw new PSQLException(GT.tr("Empty quoted value"), PSQLState.INVALID_PARAMETER_VALUE);
+ int end = len-1;
+ for (int i=1; i<end; i++)
+ {//scan for legal
+ char c = in.charAt(i);
+ if (c=='"')
+ throw new PSQLException(GT.tr("Invalid quotes found inside argument"), PSQLState.INVALID_PARAMETER_VALUE);
+ if (c=='\0')
+ throw new PSQLException(GT.tr("Null bytes may not occur in identifiers."), PSQLState.INVALID_PARAMETER_VALUE);
}
+ for (int i=1; i<end; i++)
+ {
+ char c = in.charAt(i);
+ if (Character.isWhitespace(c))
+ return !already;
+ }
+ return false;
+ }
+
+ }


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Version with 4-space instead of tabs..

# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: C:\dev\java\proj\pgjdbc\pgjdbc
# This patch can be applied using context Tools: Patch action on respective folder.
# It uses platform neutral UTF-8 encoding and \n newlines.
# Above lines and this line are ignored by the patching process.
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 2\AbstractJdbc2Statement.java Base (1.104)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 2\AbstractJdbc2Statement.java Locally Modified (Based On 1.104)
***************
*** 286,291 ****
--- 286,318 ----
}

/*
+ * Execute a SQL INSERT, UPDATE or DELETE statement. In addition
+ * SQL statements that return nothing such as SQL DDL statements
+ * can be executed
+ *
+ * @param sql a SQL statement
+ * @return either a row count, or 0 for SQL commands
+ * @exception SQLException if a database access error occurs
+ */
+ protected int executeUpdateGetResults(String p_sql) throws SQLException
+ {
+ if (preparedQuery != null)
+ throw new PSQLException(GT.tr("Can''t use query methods that take a query string on a PreparedStatement."),
+ PSQLState.WRONG_OBJECT_TYPE);
+ if( isFunction )
+ {
+ executeWithFlags(p_sql, 0);
+ return 0;
+ }
+ checkClosed();
+ p_sql = replaceProcessing(p_sql);
+ Query simpleQuery = connection.getQueryExecutor().createSimpleQuery(p_ sql);
+ execute(simpleQuery, null, 0);
+ this.lastSimpleQuery = simpleQuery;
+ return getUpdateCount();
+ }
+
+ /*
* Execute a SQL INSERT, UPDATE or DELETE statement. In addition,
* SQL statements that return nothing such as SQL DDL statements can
* be executed.
Index: org/postgresql/jdbc3/AbstractJdbc3Statement.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 3\AbstractJdbc3Statement.java Base (1.21)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 3\AbstractJdbc3Statement.java Locally Modified (Based On 1.21)
***************
*** 19,24 ****
--- 19,27 ----
import org.postgresql.core.QueryExecutor;
import org.postgresql.core.Field;
import org.postgresql.core.BaseConnection;
+ import org.postgresql.core.Utils;
+ import org.postgresql.jdbc2.AbstractJdbc2Connection;
+ import org.postgresql.jdbc2.AbstractJdbc2Statement.Statem entResultHandler;
import org.postgresql.util.GT;

/**
***************
*** 28,33 ****
--- 31,37 ----
*/
public abstract class AbstractJdbc3Statement extends org.postgresql.jdbc2.AbstractJdbc2Statement
{
+
private final int rsHoldability;

public AbstractJdbc3Statement (AbstractJdbc3Connection c, int rsType, int rsConcurrency, int rsHoldability) throws SQLException
***************
*** 106,112 ****
*/
public ResultSet getGeneratedKeys() throws SQLException
{
! return createDriverResultSet(new Field[0], new Vector());
}

/**
--- 110,118 ----
*/
public ResultSet getGeneratedKeys() throws SQLException
{
! return result==null ?
! createDriverResultSet(new Field[0], new Vector())
! : result.getResultSet();
}

/**
***************
*** 135,141 ****
{
if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
return executeUpdate(sql);
!
throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

--- 141,147 ----
{
if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
return executeUpdate(sql);
! //fix me : impl NO_GENERATED_KEYS & RETURN_GENERATED_KEYS
throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

***************
*** 184,198 ****
*/
public int executeUpdate(String sql, String columnNames[]) throws SQLException
{
! if (columnNames.length == 0)
return executeUpdate(sql);
!
! throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

/**
* Executes the given SQL statement, which may return multiple results,
--- 190,236 ----
*/
public int executeUpdate(String sql, String columnNames[]) throws SQLException
{
! //fix me : columnNames only quoted if contain 0x20
! String prefix = sql.substring(0,10).toLowerCase();
! if (columnNames==null || prefix.indexOf("insert")==-1)
! return executeUpdateGetResults(sql);
! if (!(connection instanceof AbstractJdbc2Connection))
! {
! throw new PSQLException(GT.tr("Driver version does not support returning generated keys.")+" "+connection.getClass().getName(), PSQLState.NOT_IMPLEMENTED);
! }
! AbstractJdbc2Connection con = (AbstractJdbc2Connection)connection;
! int args = columnNames.length;
! if (!connection.haveMinimumServerVersion("8.2"))
! throw new PSQLException(GT.tr("Server version does not support returning generated keys.")+" (< "+"8.2"+")", PSQLState.NOT_IMPLEMENTED);
! if (args==0)
return executeUpdate(sql);
! StringBuffer s = new StringBuffer(sql.length()+(args*32));
! s.append(sql);
! s.append('\n');
! s.append("RETURNING");
! s.append(' ');
! boolean needsQuote;
! for (int i=0; i<args; i++)
! {
! String arg = columnNames[i];
! if (arg==null)
! //throw new NullPointerException("executeUpdate: null columnName at index "+i);
! throw new PSQLException(GT.tr("Null value in columnNames"), PSQLState.INVALID_PARAMETER_VALUE);
! if (i!=0)
! s.append(',');
! needsQuote = Utils.needsQuoted(arg);
! if (needsQuote)
! s.append('"');
! s.append(arg);
! if (needsQuote)
! s.append('"');
}
+ return executeUpdateGetResults(s.toString());
+ //throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
+ }

+
+
/**
* Executes the given SQL statement, which may return multiple results,
* and signals the driver that any
Index: org/postgresql/core/Utils.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\core \Utils.java Base (1.6)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\core \Utils.java Locally Modified (Based On 1.6)
***************
*** 146,152 ****
--- 146,186 ----

return sbuf;
}
+ /**
+ * return true if the string contains whitespace and is not already quoted, false otherwise
+ *
+ * @param in
+ * @return true if the string contains whitespace and is not already quoted
+ * @throws java.sql.SQLException if the string contains quotes inside its value
+ * (foo"bar or "foor"bar"), or contains char 0x00.
+ */
+ public static final boolean needsQuoted(String in) throws SQLException
+ {
+ int len = in.length();
+ //quoted and non-empty quotes:
+ boolean already = len>1 && in.charAt(0)=='"' && in.charAt(len-1)=='"';
+ if (already && len==2)
+ throw new PSQLException(GT.tr("Empty quoted value"), PSQLState.INVALID_PARAMETER_VALUE);
+ int end = len-1;
+ for (int i=1; i<end; i++)
+ {//scan for legal
+ char c = in.charAt(i);
+ if (c=='"')
+ throw new PSQLException(GT.tr("Invalid quotes found inside argument"), PSQLState.INVALID_PARAMETER_VALUE);
+ if (c=='\0')
+ throw new PSQLException(GT.tr("Null bytes may not occur in identifiers."), PSQLState.INVALID_PARAMETER_VALUE);
}
+ for (int i=1; i<end; i++)
+ {
+ char c = in.charAt(i);
+ if (Character.isWhitespace(c))
+ return !already;
+ }
+ return false;
+ }
+
+ }


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Does anyone have knowledge of how to implement:

Statement.executeUpdate(String sql, int columnIndexes[])

This would be in the context of using the server's RETURNING clause. For
named-identifiers this is straightforward but I do not know how the
'returning' clause could support numbers... hopefully it can.

Something like?:

.... RETURNING [1],[2]

Ken



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Kris, do you what token parsers utils exists in the current JDBC
package? E.g the most tried and true way to get the schema and table
name from:

INSERT INTO foo (col1, col2..) VALUES ..
INSERT INTO foo VALUES ..
INSERT INTO "foo" VALUES ..
INSERT INTO mydb."foo" VALUES ..
etc.

Thanks,
Ken



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Wed, 12 Dec 2007, Ken Johanson wrote:

> Kris, do you what token parsers utils exists in the current JDBC
> package? E.g the most tried and true way to get the schema and table
> name from:
>
> INSERT INTO foo (col1, col2..) VALUES ..

Most of the parsing code in the driver is focused on finding placeholders
and escape sequences and doesn't care what the query is actually doing.
Deriving the base tables of a query happens in only one place, updatable
resultset support. See
org.postgresql.jdbc2.AbstractJdbc2ResultSet#parseQ uery. That said, the
current implementation is terrible and is fooled by many queries. It just
looks for the first " FROM " and takes anything after that as the table
the select is based on. Clearly this doesn't work for SELECT col AS from
FROM tab; or SELECT /* FROM */ * FROM tab; or SELECT (SELECT col FROM tab)
FROM tab2; and many other ways. So I doubt modelling new code on it is a
good idea.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Wed, 5 Dec 2007, Ken Johanson wrote:

> I opted to use the Quoting mechanism I already had in executeUpdate for now,
> since the string validation (no 0x00 && no nested quotes) is being done in
> needsQuoted (in the same loop that validates quotes and scans for
> whitespace).
>
> -is whitespace the sole determinator for needing quoting? And other chars?

Any keywords would need quoting: If you had a column named "user" it must
be quoted.

jurka=# create temp table zz(a int, "user" text);
CREATE TABLE

jurka=# insert into zz values(1,'a') returning a, user, "user";
a | current_user | user
---+--------------+------
1 | jurka | a

> -is it fine to leave the string un-quoted if it contains no ws, vs always
> quoting it (my feeling is yes).

I was thinking about this some more and I think we should quote everything
regardless of whether it needs it or not. This forces the caller to
provide the column in the correct case because it won't be folded any
more, but that's something we're already doing in DatabaseMetaData. If we
don't do this there will be no way for the user to indicate that he has
case-sensitive column names. (Unless of course we implemented
getGeneratedKeys with column names similar to how we might implemented it
for interger column indexes. If we used RETURNING * and only did the
extraction once it got back to the driver, then we have some more
flexibility in handling names.)

> -is '"' the only legal quoting chars? (I cant remember for having dabbled
> with too many non-spec databases)

Yes.

> -my needsQuoted method throws if the identifier contains nested quotes
> (foo"bar or "foo"bar"); is there a legal quote-escaping mechanism similar to
> apostrophe doubling? eg: how or would one pass foo"bar (I imagine quotes are
> never allowed in identifiers but don't have an SQL spec handy)

Nested quotes are legal and escaped just like apostrophe doubling:

create table "abc""def" ( """" int);


> PS - Kris, I recall you said the backslashes in the patch were troublesome;
> did you find any fix for your patch tool aside from translating them to '/'?
> If not I will translate them from hereto forth.
>

I haven't looked at it since then. Let's get another draft or two and
I'll see what needs to be done.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Kris Jurka wrote:

>
> Any keywords would need quoting: If you had a column named "user" it
> must be quoted.
>

Enough said. I will quote all IDs and provide a diff tonight hopefully.



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Kris, please try to apply the attached and let me know what errors if
any you get.

All ids are now quoted in: executeUpdate(String sql, String
columnIndexes[]), and: int executeUpdate(String sql, int
columnIndexes[]) is implemented, however it currently will work ONLY if
the fully qualified table is set in the insert:

INSERT INTO foocatalog.fooschema.tbl .....(quoted or not)

It will support normalizing the not-supplied catalog and schema names --
after I find out how to extract these from the Connection (hopefully
this would not require an additional round trip). Any suggestions on this?

Ken

# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: C:\dev\java\proj\pgjdbc\pgjdbc
# This patch can be applied using context Tools: Patch action on respective folder.
# It uses platform neutral UTF-8 encoding and \n newlines.
# Above lines and this line are ignored by the patching process.
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 2\AbstractJdbc2Statement.java Base (1.104)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 2\AbstractJdbc2Statement.java Locally Modified (Based On 1.104)
***************
*** 286,291 ****
--- 286,318 ----
}

/*
+ * Execute a SQL INSERT, UPDATE or DELETE statement. In addition
+ * SQL statements that return nothing such as SQL DDL statements
+ * can be executed
+ *
+ * @param sql a SQL statement
+ * @return either a row count, or 0 for SQL commands
+ * @exception SQLException if a database access error occurs
+ */
+ protected int executeUpdateGetResults(String p_sql) throws SQLException
+ {
+ if (preparedQuery != null)
+ throw new PSQLException(GT.tr("Can''t use query methods that take a query string on a PreparedStatement."),
+ PSQLState.WRONG_OBJECT_TYPE);
+ if( isFunction )
+ {
+ executeWithFlags(p_sql, 0);
+ return 0;
+ }
+ checkClosed();
+ p_sql = replaceProcessing(p_sql);
+ Query simpleQuery = connection.getQueryExecutor().createSimpleQuery(p_ sql);
+ execute(simpleQuery, null, 0);
+ this.lastSimpleQuery = simpleQuery;
+ return getUpdateCount();
+ }
+
+ /*
* Execute a SQL INSERT, UPDATE or DELETE statement. In addition,
* SQL statements that return nothing such as SQL DDL statements can
* be executed.
Index: org/postgresql/jdbc3/AbstractJdbc3Statement.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 3\AbstractJdbc3Statement.java Base (1.21)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\jdbc 3\AbstractJdbc3Statement.java Locally Modified (Based On 1.21)
***************
*** 11,16 ****
--- 11,17 ----

import java.math.BigDecimal;
import java.sql.*;
+ import java.util.ArrayList;
import java.util.Calendar;
import java.util.Vector;

***************
*** 19,24 ****
--- 20,28 ----
import org.postgresql.core.QueryExecutor;
import org.postgresql.core.Field;
import org.postgresql.core.BaseConnection;
+ import org.postgresql.core.Utils;
+ import org.postgresql.jdbc2.AbstractJdbc2Connection;
+ import org.postgresql.jdbc2.AbstractJdbc2Statement.Statem entResultHandler;
import org.postgresql.util.GT;

/**
***************
*** 28,33 ****
--- 32,38 ----
*/
public abstract class AbstractJdbc3Statement extends org.postgresql.jdbc2.AbstractJdbc2Statement
{
+
private final int rsHoldability;

public AbstractJdbc3Statement (AbstractJdbc3Connection c, int rsType, int rsConcurrency, int rsHoldability) throws SQLException
***************
*** 106,112 ****
*/
public ResultSet getGeneratedKeys() throws SQLException
{
! return createDriverResultSet(new Field[0], new Vector());
}

/**
--- 111,119 ----
*/
public ResultSet getGeneratedKeys() throws SQLException
{
! return result==null ?
! createDriverResultSet(new Field[0], new Vector())
! : result.getResultSet();
}

/**
***************
*** 135,141 ****
{
if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
return executeUpdate(sql);
!
throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

--- 142,148 ----
{
if (autoGeneratedKeys == Statement.NO_GENERATED_KEYS)
return executeUpdate(sql);
! //fix me : impl NO_GENERATED_KEYS & RETURN_GENERATED_KEYS
throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

***************
*** 159,172 ****
*/
public int executeUpdate(String sql, int columnIndexes[]) throws SQLException
{
! if (columnIndexes.length == 0)
return executeUpdate(sql);
!
! throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

/**
--- 166,206 ----
*/
public int executeUpdate(String sql, int columnIndexes[]) throws SQLException
{
! if (columnIndexes==null || columnIndexes.length == 0)
return executeUpdate(sql);
! String prefix = sql.substring(0,10).toLowerCase();
! if (columnIndexes==null || prefix.indexOf("insert")==-1)
! {
! return executeUpdateGetResults(sql);
}
+ int start = Utils.position(sql, "INTO", 0);
+ ArrayList args = Utils.getInsertIds(sql, start);
+ String pgCols =
+ "SELECT column_name "+
+ "FROM information_schema.columns "+
+ "WHERE table_catalog='"+args.get(0)+"' AND table_schema='"+args.get(1)+"' AND table_name='"+args.get(2)+"' "+
+ "ORDER BY ordinal_position";
+ ResultSet rs = null;
+ String[] columnNames = new String[columnIndexes.length];
+ try {
+ rs = this.executeQuery(pgCols);
+ } catch (SQLException ex) {
+ throw new PSQLException(GT.tr("Could not translate column name indexes.")+" "+ex, PSQLState.UNEXPECTED_ERROR);
+ } finally {
+ if (rs!=null) rs.close();
+ }
+ int j=0;
+ try {
+ for (; j<columnNames.length; j++)
+ {
+ rs.absolute(columnIndexes[j]);
+ columnNames[j] = rs.getString(1);
+ }
+ } catch (SQLException ex) {//invalid column index provided
+ throw new PSQLException(GT.tr("Column index out of bounds.")+" "+columnIndexes[j], PSQLState.UNEXPECTED_ERROR);
+ }
+ return executeUpdate(sql, columnNames);
+ }

/**
* Executes the given SQL statement and signals the driver that the
***************
*** 184,198 ****
*/
public int executeUpdate(String sql, String columnNames[]) throws SQLException
{
! if (columnNames.length == 0)
return executeUpdate(sql);
!
! throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
}

/**
* Executes the given SQL statement, which may return multiple results,
--- 221,262 ----
*/
public int executeUpdate(String sql, String columnNames[]) throws SQLException
{
! String prefix = sql.substring(0,10).toLowerCase();
! if (columnNames==null || prefix.indexOf("insert")==-1)
! return executeUpdateGetResults(sql);
! if (!(connection instanceof AbstractJdbc2Connection))
! {
! throw new PSQLException(GT.tr("Driver version does not support returning generated keys.")+" "+connection.getClass().getName(), PSQLState.NOT_IMPLEMENTED);
! }
! AbstractJdbc2Connection con = (AbstractJdbc2Connection)connection;
! int args = columnNames.length;
! if (!connection.haveMinimumServerVersion("8.2"))
! throw new PSQLException(GT.tr("Server version does not support returning generated keys.")+" (< "+"8.2"+")", PSQLState.NOT_IMPLEMENTED);
! if (args==0)
return executeUpdate(sql);
! StringBuffer s = new StringBuffer(sql.length()+(args*32));
! s.append(sql);
! s.append('\n');
! s.append("RETURNING");
! s.append(' ');
! for (int i=0; i<args; i++)
! {
! String arg = columnNames[i];
! if (arg==null)
! //throw new NullPointerException("executeUpdate: null columnName at index "+i);
! throw new PSQLException(GT.tr("Null value in columnNames"), PSQLState.INVALID_PARAMETER_VALUE);
! if (i!=0)
! s.append(',');
! s.append('"');
! s.append(arg);
! s.append('"');
}
+ return executeUpdateGetResults(s.toString());
+ //throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."), PSQLState.NOT_IMPLEMENTED);
+ }

+
+
/**
* Executes the given SQL statement, which may return multiple results,
* and signals the driver that any


Index: org/postgresql/core/Utils.java
*** C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\core \Utils.java Base (1.6)
--- C:\dev\java\proj\pgjdbc\pgjdbc\org\postgresql\core \Utils.java Locally Modified (Based On 1.6)
***************
*** 12,17 ****
--- 12,18 ----
package org.postgresql.core;

import java.sql.SQLException;
+ import java.util.ArrayList;

import org.postgresql.util.GT;
import org.postgresql.util.PSQLException;
***************
*** 146,152 ****
--- 147,286 ----

return sbuf;
}
+ /**
+ * return true if the string contains whitespace and is not already quoted, false otherwise
+ *
+ * @param in
+ * @return true if the string contains whitespace and is not already quoted
+ * @throws java.sql.SQLException if the string contains quotes inside its value
+ * (foo"bar or "foor"bar"), or contains char 0x00.
+ */
+ public static final boolean needsQuoted(String in) throws SQLException
+ {
+ int len = in.length();
+ //quoted and non-empty quotes:
+ boolean already = len>1 && in.charAt(0)=='"' && in.charAt(len-1)=='"';
+ if (already && len==2)
+ throw new PSQLException(GT.tr("Empty quoted value"), PSQLState.INVALID_PARAMETER_VALUE);
+ int end = len-1;
+ for (int i=1; i<end; i++)
+ {//scan for legal
+ char c = in.charAt(i);
+ if (c=='"')
+ throw new PSQLException(GT.tr("Invalid quotes found inside argument"), PSQLState.INVALID_PARAMETER_VALUE);
+ if (c=='\0')
+ throw new PSQLException(GT.tr("Null bytes may not occur in identifiers."), PSQLState.INVALID_PARAMETER_VALUE);
}
+ for (int i=1; i<end; i++)
+ {
+ char c = in.charAt(i);
+ if (Character.isWhitespace(c))
+ return !already;
+ }
+ return false;
+ }
+
+ /**
+ * Return an ArrayList of Strings representing the table identifiers, quoted or not.
+ * Any number of id may exists; no attempt is made to validate the maximum number of IDs.
+ * @param sql INSERT INTO stmt
+ * @param start - index of the INTO keyword, after which the <code>catalog.schema.table</code> identifiers appear
+ * @return ArrayList who first element is the left-most identifiers, and right-most is the table name.
+ * @author Ken Johanon ken2006@onnet.cc
+ */
+ public static ArrayList getInsertIds(String sql, int start)
+ {
+ if (start<0)
+ throw new IllegalArgumentException("getInsertIds: invalid start index: "+start);
+ start += 4;
+ //advance to first alnum
+ for (; start<sql.length(); start++)
+ if (Character.isLetterOrDigit(sql.charAt(start)))
+ break;
+ //advance to first non-quoted, non-alnum
+ ArrayList ar = new ArrayList(4);
+ int end = start;
+ int pos = start;
+ boolean inQuote = sql.charAt(end-1)=='"';
+ for (; end<sql.length(); end++)
+ {
+ char c = sql.charAt(end);
+ if (inQuote)
+ {
+ if (c=='"')
+ {
+ ar.add(sql.substring(pos, end));
+ end++;
+ pos = end+1;
+ inQuote = false;
+ }
+ }
+ else
+ {
+ if (c=='"')
+ {
+ inQuote = true;
+ pos = end+1;
+ }
+ else if (c=='.')
+ {
+ ar.add(sql.substring(pos, end));
+ pos = end+1;
+ }
+ }
+
+ if (c=='(' || (!inQuote && Character.isSpaceChar(c)))
+ {
+ if (pos!=end)
+ ar.add(sql.substring(pos, end));
+ break;
+ }
+ }
+ return ar;
+ }
+
+ /**
+ * Search for and return the location of <code>find</code> in String <code>in</code>, case insensitive.
+ * If in is empty, return -1. If find is empty, return 0.
+ * @param in
+ * @param find - string to find
+ * @param pos - starting position to search
+ * @return int location, or -1 if not found
+ * @author Ken Johanon ken2006@onnet.cc
+ */
+ public static int position(CharSequence in, String find, int pos)
+ {
+ boolean c = in==null || in.length()==0;
+ boolean d = find==null || find.length()==0;
+ if (d || c && d)
+ return 0;
+ if (c)
+ return -1;
+ int a = in.length();
+ int b = find.length();
+ int count = 0;
+ //if (pos>a-b)
+ // return -1;
+ char c1, c2;
+ for (int i=pos; i<a; i++)
+ {
+ c1 = in.charAt(i);
+ c2 = find.charAt(count);
+ if (c1==c2 || c1==Character.toLowerCase(c2) || c1==Character.toUpperCase(c2))
+ count++;
+ else
+ {
+ i -= count;
+ count = 0;
+ }
+ if (count==b)
+ return i-b+1;
+ }
+ return -1;
+ }
+
+ }


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Kris, were you able to apply the last patch I sent? Let me know what
you'd like. I would like to proceed so I can close this project out.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Tue, 18 Dec 2007, Ken Johanson wrote:

> Kris, were you able to apply the last patch I sent? Let me know what
> you'd like. I would like to proceed so I can close this project out.
>

Sorry, I have not had time to look at it and probably won't be able to
until next year. I've got a vacation coming up and people who pay me that
want stuff done before I leave.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings