Thread: [PATCH] Ability to PreparedStatement.setObject(#, int[])

[PATCH] Ability to PreparedStatement.setObject(#, int[])

From
Doug Fields
Date:
Hello,

(Find Unified Context Diff below against 7.2 shipping sources)

Currently there is no way to set an int[] object to an INTEGER[] in
Postgres with JDBC, unless you create a string and do it. However, reading
p266-267 of JDBC API Tutorial and Reference, 2nd Ed by White, Fisher,
Cattell, Hamilton, Hapner, indicates that we should be able to setObject it
and have it work.

The current 7.2 drivers do not support this functionality, that of calling
setObject() with an array. Instead, the driver tries to use object
serialization to store it.

In theory, any base type or String array should be converted to the
appropriate array format and stored in an array type in the database. I
have only patched it to recognize an int[] array to demonstrate what should
be done. If people wish, I can extend my patch to all base array types
other than byte (which is already handled).

Cheers,

Doug


diff -ru5 org-orig/postgresql/jdbc2/PreparedStatement.java
org/postgresql/jdbc2/PreparedStatement.java
--- org-orig/postgresql/jdbc2/PreparedStatement.java    Tue Jan 15 02:37:33
2002
+++ org/postgresql/jdbc2/PreparedStatement.java Wed Feb 20 20:34:32 2002
@@ -754,11 +754,29 @@
                         setTimestamp(parameterIndex, (Timestamp)x);
                 else if (x instanceof Boolean)
                         setBoolean(parameterIndex,
((Boolean)x).booleanValue());
                 else if (x instanceof PGobject)
                         setString(parameterIndex, ((PGobject)x).getValue());
-               else
+               else if (x instanceof int[]) {
+                       /* Turn into a string and use setString instead.
+                        * This should be used for other array types as well.
+                        * See p266-267 of JDBC API Tutorial and Reference,
+                        * 2nd Ed by White, Fisher, Cattell, Hamilton, Hapner
+                        * for why this method should be done this way.
+                        * Doug Fields <dfields-pg-jdbc@pexicom.com>
+                        * Feb 20, 2002 */
+                       StringBuffer sb = new StringBuffer();
+                       int[] y = (int[])x; // Ease of use
+                       sb.append('{');
+                       for (int i = 0; i < y.length; i++) {
+                               if (i > 0)
+                                       sb.append(',');
+                               sb.append(y[i]);
+                       }
+                       sb.append('}');
+                       setString(parameterIndex, sb.toString());
+               } else
                         // Try to store java object in database
                         setSerialize(parameterIndex,
connection.storeObject(x), x.getClass().getName() );
         }

         /*


Re: [PATCHES] [PATCH] Ability to PreparedStatement.setObject(#, int[])

From
Bruce Momjian
Date:
This has been saved for the 7.3 release:

    http://candle.pha.pa.us/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Doug Fields wrote:
> Hello,
>
> (Find Unified Context Diff below against 7.2 shipping sources)
>
> Currently there is no way to set an int[] object to an INTEGER[] in
> Postgres with JDBC, unless you create a string and do it. However, reading
> p266-267 of JDBC API Tutorial and Reference, 2nd Ed by White, Fisher,
> Cattell, Hamilton, Hapner, indicates that we should be able to setObject it
> and have it work.
>
> The current 7.2 drivers do not support this functionality, that of calling
> setObject() with an array. Instead, the driver tries to use object
> serialization to store it.
>
> In theory, any base type or String array should be converted to the
> appropriate array format and stored in an array type in the database. I
> have only patched it to recognize an int[] array to demonstrate what should
> be done. If people wish, I can extend my patch to all base array types
> other than byte (which is already handled).
>
> Cheers,
>
> Doug
>
>
> diff -ru5 org-orig/postgresql/jdbc2/PreparedStatement.java
> org/postgresql/jdbc2/PreparedStatement.java
> --- org-orig/postgresql/jdbc2/PreparedStatement.java    Tue Jan 15 02:37:33
> 2002
> +++ org/postgresql/jdbc2/PreparedStatement.java Wed Feb 20 20:34:32 2002
> @@ -754,11 +754,29 @@
>                          setTimestamp(parameterIndex, (Timestamp)x);
>                  else if (x instanceof Boolean)
>                          setBoolean(parameterIndex,
> ((Boolean)x).booleanValue());
>                  else if (x instanceof PGobject)
>                          setString(parameterIndex, ((PGobject)x).getValue());
> -               else
> +               else if (x instanceof int[]) {
> +                       /* Turn into a string and use setString instead.
> +                        * This should be used for other array types as well.
> +                        * See p266-267 of JDBC API Tutorial and Reference,
> +                        * 2nd Ed by White, Fisher, Cattell, Hamilton, Hapner
> +                        * for why this method should be done this way.
> +                        * Doug Fields <dfields-pg-jdbc@pexicom.com>
> +                        * Feb 20, 2002 */
> +                       StringBuffer sb = new StringBuffer();
> +                       int[] y = (int[])x; // Ease of use
> +                       sb.append('{');
> +                       for (int i = 0; i < y.length; i++) {
> +                               if (i > 0)
> +                                       sb.append(',');
> +                               sb.append(y[i]);
> +                       }
> +                       sb.append('}');
> +                       setString(parameterIndex, sb.toString());
> +               } else
>                          // Try to store java object in database
>                          setSerialize(parameterIndex,
> connection.storeObject(x), x.getClass().getName() );
>          }
>
>          /*
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [PATCHES] [PATCH] Ability to PreparedStatement.setObject(#, int[])

From
Bruce Momjian
Date:
Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Doug Fields wrote:
> Hello,
>
> (Find Unified Context Diff below against 7.2 shipping sources)
>
> Currently there is no way to set an int[] object to an INTEGER[] in
> Postgres with JDBC, unless you create a string and do it. However, reading
> p266-267 of JDBC API Tutorial and Reference, 2nd Ed by White, Fisher,
> Cattell, Hamilton, Hapner, indicates that we should be able to setObject it
> and have it work.
>
> The current 7.2 drivers do not support this functionality, that of calling
> setObject() with an array. Instead, the driver tries to use object
> serialization to store it.
>
> In theory, any base type or String array should be converted to the
> appropriate array format and stored in an array type in the database. I
> have only patched it to recognize an int[] array to demonstrate what should
> be done. If people wish, I can extend my patch to all base array types
> other than byte (which is already handled).
>
> Cheers,
>
> Doug
>
>
> diff -ru5 org-orig/postgresql/jdbc2/PreparedStatement.java
> org/postgresql/jdbc2/PreparedStatement.java
> --- org-orig/postgresql/jdbc2/PreparedStatement.java    Tue Jan 15 02:37:33
> 2002
> +++ org/postgresql/jdbc2/PreparedStatement.java Wed Feb 20 20:34:32 2002
> @@ -754,11 +754,29 @@
>                          setTimestamp(parameterIndex, (Timestamp)x);
>                  else if (x instanceof Boolean)
>                          setBoolean(parameterIndex,
> ((Boolean)x).booleanValue());
>                  else if (x instanceof PGobject)
>                          setString(parameterIndex, ((PGobject)x).getValue());
> -               else
> +               else if (x instanceof int[]) {
> +                       /* Turn into a string and use setString instead.
> +                        * This should be used for other array types as well.
> +                        * See p266-267 of JDBC API Tutorial and Reference,
> +                        * 2nd Ed by White, Fisher, Cattell, Hamilton, Hapner
> +                        * for why this method should be done this way.
> +                        * Doug Fields <dfields-pg-jdbc@pexicom.com>
> +                        * Feb 20, 2002 */
> +                       StringBuffer sb = new StringBuffer();
> +                       int[] y = (int[])x; // Ease of use
> +                       sb.append('{');
> +                       for (int i = 0; i < y.length; i++) {
> +                               if (i > 0)
> +                                       sb.append(',');
> +                               sb.append(y[i]);
> +                       }
> +                       sb.append('}');
> +                       setString(parameterIndex, sb.toString());
> +               } else
>                          // Try to store java object in database
>                          setSerialize(parameterIndex,
> connection.storeObject(x), x.getClass().getName() );
>          }
>
>          /*
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026