Quantcast

passing array as parameters to PrepareStatement or callable statement.[setObject() or setArray()]

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

passing array as parameters to PrepareStatement or callable statement.[setObject() or setArray()]

Assad Jarrahian
Hello all,
  my apologies if this question has been asked before. I looked
through the archives could not find the relevant info (if I overlooked
something, sorry).



int[] intarray = new int[1];
getAssociatedLMs.setObject(1, intarray, java.sql.Types.ARRAY);
//getAssociatedLM's is a callable statement ("{call getLMs(?,?) } ");

so I get a compilation error saying
org.postgresql.util.PSQLException: Cannot cast an instance of [I to
type Types.ARRAY
        at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1637)
        at org.postgresql.jdbc3.AbstractJdbc3Statement.setObject(AbstractJdbc3Statement.java:1435)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1652)
        at    //the setObjectLineAbove


IF I change it to type Integer[]
org.postgresql.util.PSQLException: Cannot cast an instance of
[Ljava.lang.Integer; to type Types.ARRAY
        at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1637)
......


I notice  Dave Cramer mentioned something about this could be an
easier process. But what is the process now? (aka, am I completely
off?)
Much thanks in advance.

Assad

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: passing array as parameters to PrepareStatement or callable

Kris Jurka


On Sat, 12 Nov 2005, Assad Jarrahian wrote:

> Hello all,
>  my apologies if this question has been asked before. I looked
> through the archives could not find the relevant info (if I overlooked
> something, sorry).
>
> int[] intarray = new int[1];
> getAssociatedLMs.setObject(1, intarray, java.sql.Types.ARRAY);
> //getAssociatedLM's is a callable statement ("{call getLMs(?,?) } ");
>
> so I get a compilation error saying
> org.postgresql.util.PSQLException: Cannot cast an instance of [I to
> type Types.ARRAY

Currently the driver only supports passing a specific implementation of
java.sql.Array to setObject (and setArray).  Other people have posted
helper classes to transform some specific java array types to
java.sql.Array, but these have not been generalized and added to the
driver.

Kris Jurka

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

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: passing array as parameters to PrepareStatement or callable statement.[setObject() or setArray()]

Assad Jarrahian
Hi Kris and all,
  So I did a quick google search and found out all I need in my new
class that implements Array is getBaseType() and toString() [I am not
sure this is true since it is not working! Either that or I am doing
something wrong]

Code below

public class IDArray implements Array{
        protected int[] myArray;

        public IDArray(int[] temp ){
          ....
         }
        public int getBaseType() throws SQLException {
                return Types.INTEGER;
        }
      public String toString(){
                String temp = new String();
                temp+="{";
                for (int i=0;i<arraySize; ++i){
                        temp += myArray[i];
                                if (i!=arraySize-1)
                                        temp +=",";
                }
                temp+="}";
                return temp;
        }
}


When I call it in my java code using
        IDArray LM_IDs = new IDArray(new int[]{3,4,5});
        getAssociatedLMs.setObject(1, LM_IDs, java.sql.Types.ARRAY);

I get an exception thrown (I cant figure this one out).
-1 Unknown type _null. [errorCode  message]

Any pointers help, would be much appreciated. Thanks.

-assad


On 11/13/05, Kris Jurka <[hidden email]> wrote:

>
>
> On Sat, 12 Nov 2005, Assad Jarrahian wrote:
>
> > Hello all,
> >  my apologies if this question has been asked before. I looked
> > through the archives could not find the relevant info (if I overlooked
> > something, sorry).
> >
> > int[] intarray = new int[1];
> > getAssociatedLMs.setObject(1, intarray, java.sql.Types.ARRAY);
> > //getAssociatedLM's is a callable statement ("{call getLMs(?,?) } ");
> >
> > so I get a compilation error saying
> > org.postgresql.util.PSQLException: Cannot cast an instance of [I to
> > type Types.ARRAY
>
> Currently the driver only supports passing a specific implementation of
> java.sql.Array to setObject (and setArray).  Other people have posted
> helper classes to transform some specific java array types to
> java.sql.Array, but these have not been generalized and added to the
> driver.
>
> Kris Jurka
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: passing array as parameters to PrepareStatement or callable statement.[setObject() or setArray()]

Dave Cramer-8
This is from a previous post.

Dave
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;
       }
     };

On 13-Nov-05, at 6:56 PM, Assad Jarrahian wrote:

> Hi Kris and all,
>   So I did a quick google search and found out all I need in my new
> class that implements Array is getBaseType() and toString() [I am not
> sure this is true since it is not working! Either that or I am doing
> something wrong]
>
> Code below
>
> public class IDArray implements Array{
>         protected int[] myArray;
>
>         public IDArray(int[] temp ){
>           ....
>          }
> public int getBaseType() throws SQLException {
> return Types.INTEGER;
> }
>       public String toString(){
> String temp = new String();
> temp+="{";
> for (int i=0;i<arraySize; ++i){
> temp += myArray[i];
> if (i!=arraySize-1)
> temp +=",";
> }
> temp+="}";
> return temp;
> }
> }
>
>
> When I call it in my java code using
> IDArray LM_IDs = new IDArray(new int[]{3,4,5});
> getAssociatedLMs.setObject(1, LM_IDs, java.sql.Types.ARRAY);
>
> I get an exception thrown (I cant figure this one out).
> -1 Unknown type _null. [errorCode  message]
>
> Any pointers help, would be much appreciated. Thanks.
>
> -assad
>
>
> On 11/13/05, Kris Jurka <[hidden email]> wrote:
>>
>>
>> On Sat, 12 Nov 2005, Assad Jarrahian wrote:
>>
>>> Hello all,
>>>  my apologies if this question has been asked before. I looked
>>> through the archives could not find the relevant info (if I  
>>> overlooked
>>> something, sorry).
>>>
>>> int[] intarray = new int[1];
>>> getAssociatedLMs.setObject(1, intarray, java.sql.Types.ARRAY);
>>> //getAssociatedLM's is a callable statement ("{call getLMs(?,?) }  
>>> ");
>>>
>>> so I get a compilation error saying
>>> org.postgresql.util.PSQLException: Cannot cast an instance of [I to
>>> type Types.ARRAY
>>
>> Currently the driver only supports passing a specific  
>> implementation of
>> java.sql.Array to setObject (and setArray).  Other people have posted
>> helper classes to transform some specific java array types to
>> java.sql.Array, but these have not been generalized and added to the
>> driver.
>>
>> Kris Jurka
>>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: passing array as parameters to PrepareStatement or callable statement.[setObject() or setArray()]

jojopaderes
Is the Array class used in the method refers to java.sql.Array? If
yes, I think it's not possible to use that to create an object since
it is an interface.

On 11/14/05, Dave Cramer <[hidden email]> wrote:

> This is from a previous post.
>
> Dave
> 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;
>        }
>      };
>
> On 13-Nov-05, at 6:56 PM, Assad Jarrahian wrote:
>
> > Hi Kris and all,
> >   So I did a quick google search and found out all I need in my new
> > class that implements Array is getBaseType() and toString() [I am not
> > sure this is true since it is not working! Either that or I am doing
> > something wrong]
> >
> > Code below
> >
> > public class IDArray implements Array{
> >         protected int[] myArray;
> >
> >         public IDArray(int[] temp ){
> >           ....
> >          }
> >       public int getBaseType() throws SQLException {
> >               return Types.INTEGER;
> >       }
> >       public  String toString(){
> >               String temp = new String();
> >               temp+="{";
> >               for (int i=0;i<arraySize; ++i){
> >                       temp += myArray[i];
> >                               if (i!=arraySize-1)
> >                                       temp +=",";
> >               }
> >               temp+="}";
> >               return temp;
> >       }
> > }
> >
> >
> > When I call it in my java code using
> >       IDArray LM_IDs = new IDArray(new int[]{3,4,5});
> >       getAssociatedLMs.setObject(1, LM_IDs, java.sql.Types.ARRAY);
> >
> > I get an exception thrown (I cant figure this one out).
> > -1 Unknown type _null. [errorCode  message]
> >
> > Any pointers help, would be much appreciated. Thanks.
> >
> > -assad
> >
> >
> > On 11/13/05, Kris Jurka <[hidden email]> wrote:
> >>
> >>
> >> On Sat, 12 Nov 2005, Assad Jarrahian wrote:
> >>
> >>> Hello all,
> >>>  my apologies if this question has been asked before. I looked
> >>> through the archives could not find the relevant info (if I
> >>> overlooked
> >>> something, sorry).
> >>>
> >>> int[] intarray = new int[1];
> >>> getAssociatedLMs.setObject(1, intarray, java.sql.Types.ARRAY);
> >>> //getAssociatedLM's is a callable statement ("{call getLMs(?,?) }
> >>> ");
> >>>
> >>> so I get a compilation error saying
> >>> org.postgresql.util.PSQLException: Cannot cast an instance of [I to
> >>> type Types.ARRAY
> >>
> >> Currently the driver only supports passing a specific
> >> implementation of
> >> java.sql.Array to setObject (and setArray).  Other people have posted
> >> helper classes to transform some specific java array types to
> >> java.sql.Array, but these have not been generalized and added to the
> >> driver.
> >>
> >> Kris Jurka
> >>
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to [hidden email] so that your
>        message can get through to the mailing list cleanly
>


--
You can make a difference!
http://gawadkalinga.org
http://www.childrenshour.org.ph
http://www.handsonmla.org

http://groups.yahoo.com/group/pinoyjug
http://jojopaderes.blogsome.com
http://jojopaderes.multiply.com

"In preparing for battle I have always found that plans are useless,
but planning is indispensable." - Eisenhower

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: passing array as parameters to PrepareStatement or callable

Oliver Jowett
Jojo Paderes wrote:
> Is the Array class used in the method refers to java.sql.Array? If
> yes, I think it's not possible to use that to create an object since
> it is an interface.

Yes it's java.sql.Array, but the code is creating an instance of an
anonymous inner class that implements Array, which is fine:

>>     Array a = new Array() {
>>       public String getBaseTypeName() {return "int4";}
[...]

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: passing array as parameters to PrepareStatement or callable statement.[setObject() or setArray()]

jojopaderes
Ah, didn't notice that. Thanks for the clarification :-)

On 11/21/05, Oliver Jowett <[hidden email]> wrote:

> Jojo Paderes wrote:
> > Is the Array class used in the method refers to java.sql.Array? If
> > yes, I think it's not possible to use that to create an object since
> > it is an interface.
>
> Yes it's java.sql.Array, but the code is creating an instance of an
> anonymous inner class that implements Array, which is fine:
>
> >>     Array a = new Array() {
> >>       public String getBaseTypeName() {return "int4";}
> [...]
>
> -O
>


--
You can make the world a better place for humanity!
http://gawadkalinga.org
http://www.childrenshour.org.ph
http://www.handsonmla.org

http://groups.yahoo.com/group/pinoyjug
http://jojopaderes.blogsome.com
http://jojopaderes.multiply.com

"In preparing for battle I have always found that plans are useless,
but planning is indispensable." - Eisenhower

---------------------------(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

Loading...