Thread: PreparedStatement.setXXX

PreparedStatement.setXXX

From
"Roberta Campo"
Date:
We're moving from Postgresql7.4 to v.8.0.3,
with some problems rising from the strictness of the new driver
postgresql-8.0-311.jdbc3.jar.

We used to call a function f(integer[]) using
PreparedStatement.setString('{...}')
but now the types are not compatible.


How should we fix that ?

A. Using the setArray method :
This requires the implementation of the interface java.sql.Array :
- which methods are required in this case ?
- is there any available implementation of this class ? :)

B. Using the setObject(colnumber, String, type ) method :
This should require less implementation than the Array, shouldn't it ?
- how to specify the type int[] ?

C. Using different drivers / protocol versions / ...

 Thanks,
Roberta



Re: PreparedStatement.setXXX

From
Dave Cramer
Date:
Roberta,

You can force version 2 protocol with protocolVersion=2

or you can implement the Array interface.

For putting information into the db, I would imagine all you need to implement is everything up to getResultSet()

Dave
On 18-Jul-05, at 7:55 AM, Roberta Campo wrote:

We're moving from Postgresql7.4 to v.8.0.3,
with some problems rising from the strictness of the new driver
postgresql-8.0-311.jdbc3.jar.

We used to call a function f(integer[]) using
PreparedStatement.setString('{...}')
but now the types are not compatible.


How should we fix that ?

A. Using the setArray method :
This requires the implementation of the interface java.sql.Array :
- which methods are required in this case ?
- is there any available implementation of this class ? :)

B. Using the setObject(colnumber, String, type ) method :
This should require less implementation than the Array, shouldn't it ?
- how to specify the type int[] ?

C. Using different drivers / protocol versions / ...

 Thanks,
Roberta



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





Dave Cramer
www.postgresintl.com
ICQ #14675561
ph (519 939 0336 )

Re: PreparedStatement.setXXX

From
sumit shah
Date:
Roberta:

Here is my utility code that implements java.sql.Array:


  public static Array convertStringToPgSqlArray(final String[] s) throws Exception {
    if(s == null || s.length < 1) return null;
    Array a = new Array() {
      public String getBaseTypeName() {return "text";}
      public int getBaseType()  {return 0;}
      public Object getArray()  {return null;}
      public Object getArray(Map<String, Class<?>> map)  {return null;}
      public Object getArray(long index, int count)  {return null;}
      public Object getArray(long index, int count, Map<String, Class<?>> map)  {return null;}
      public ResultSet getResultSet()  {return null;}
      public ResultSet getResultSet(Map<String, Class<?>> map)  {return null;}
      public ResultSet getResultSet(long index, int count)  {return null;}
      public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) {return null;}

      public String toString() {
        String p = "{";
        if(s.length == 0) {
        } else {
          for(int i = 0; i < s.length - 1; i++) p += s[i] + ",";
          p += "'" + s[s.length - 1] + "'";
        }
        p+="}";
        return p;
      }
    };
    return a;
  }



public static Array convertIntegerToPgSqlArray(final int[] p) {
    if(p == null || p.length < 1) return null;
    Array a = new Array() {
      public String getBaseTypeName() {return "int4";}
      public int getBaseType()  {return 0;}
      public Object getArray()  {return null;}
      public Object getArray(Map<String, Class<?>> map)  {return null;}
      public Object getArray(long index, int count)  {return null;}
      public Object getArray(long index, int count, Map<String, Class<?>> map)  {return null;}
      public ResultSet getResultSet()  {return null;}
      public ResultSet getResultSet(Map<String, Class<?>> map)  {return null;}
      public ResultSet getResultSet(long index, int count)  {return null;}
      public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) {return null;}

      public String toString() {
        String fp = "{";
        if(p.length == 0) {
        } else {
          for(int i = 0; i < p.length - 1; i++) fp += p[i] + ",";
          fp += p[p.length - 1];
        }
        fp+="}";
        return fp;
      }
    };
    return a;
  }



On Jul 18, 2005, at 6:52 AM, Dave Cramer wrote:

Roberta,

You can force version 2 protocol with protocolVersion=2

or you can implement the Array interface.

For putting information into the db, I would imagine all you need to implement is everything up to getResultSet()

Dave
On 18-Jul-05, at 7:55 AM, Roberta Campo wrote:

We're moving from Postgresql7.4 to v.8.0.3,
with some problems rising from the strictness of the new driver
postgresql-8.0-311.jdbc3.jar.

We used to call a function f(integer[]) using
PreparedStatement.setString('{...}')
but now the types are not compatible.


How should we fix that ?

A. Using the setArray method :
This requires the implementation of the interface java.sql.Array :
- which methods are required in this case ?
- is there any available implementation of this class ? :)

B. Using the setObject(colnumber, String, type ) method :
This should require less implementation than the Array, shouldn't it ?
- how to specify the type int[] ?

C. Using different drivers / protocol versions / ...

 Thanks,
Roberta



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





Dave Cramer
www.postgresintl.com
ICQ #14675561
ph (519 939 0336 )


Re: PreparedStatement.setXXX

From
Oliver Jowett
Date:
Roberta Campo wrote:

> A. Using the setArray method :
> This requires the implementation of the interface java.sql.Array :
> - which methods are required in this case ?
> - is there any available implementation of this class ? :)

Someone else replied to this with an implementation..

The driver's Array support is pretty bad currently. It will only work if
the Array implementation implements:

 - getBaseTypeName() returns the underlying postgresql type name ("int")
 - toString() returns a correctly-formatted array string ("{1,2,3}")

Proper support for arbitrary Array implementations (and bare arrays via
setObject) has been on my todo list for ages but it's no longer a high
priority for our application so I doubt I'll work on it any time soon :/

> B. Using the setObject(colnumber, String, type ) method :
> This should require less implementation than the Array, shouldn't it ?
> - how to specify the type int[] ?

JDBC has no mechanism to specify a particular array type via a Types.*
constant unfortunately :(

> C. Using different drivers / protocol versions / ...

Using protocol version 2 as Dave Cramer suggests is a short-term fix but
you don't want to be using that in the longer term..

-O