Postgres Pro
Downloads
Home   >   mailing lists

Thread: prepared statement using postgres array

prepared statement using postgres array

From
"Christian Kindler"
Date:
Hello

I have read a lot but did not find any solution for inserting a array of floats into pg database via jdbc and prepared
statements. 

Can anyone please help me out?

Thanks
Christian
--
cu
Chris

Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

Re: prepared statement using postgres array

From
Dave Cramer
Date:
Christian,


Look through our test code, essentially you have to implement the
java.sql.Array interface on an object which contains an array of
floats and pass that in.

Dave
On 30-Nov-07, at 9:07 AM, Christian Kindler wrote:

> Hello
>
> I have read a lot but did not find any solution for inserting a
> array of floats into pg database via jdbc and prepared statements.
>
> Can anyone please help me out?
>
> Thanks
> Christian
> --
> cu
> Chris
>
> Psssst! Schon vom neuen GMX MultiMessenger gehört?
> Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend

Re: prepared statement using postgres array

From
"Christian Kindler"
Date:
Hi Dave

> you have to implement the java.sql.Array

hmmm ... i have tried to find a sample implementation of the interface, but no success.

I am sure this is a ugly solution but I figures out a workaround:

INSERT INTO Quote(fi_id, market_id, "date", "time",
                  "open", high, low, "close", vol, oi,
                  bid, bidvol, ask, askvol,
                  last,
                  sysstatus,
                  buyers,  buyersvol, sellers, sellersvol,
                  additional)
           VALUES(?, ?, ?, ?,
                  ?, ?, ?, ?, ?, ?,
                  ?, ?, ?, ?,
                  ?,
                  ?,
                  (select string_to_array(?, ',')::float[]),
                  (select string_to_array(?, ',')::float[]),
                  (select string_to_array(?, ',')::float[]),
                  (select string_to_array(?, ',')::float[]),
                  (select string_to_array(?, ',')::float[])
                 );

 ... as is said not pretty but it works for the moment ...

Thanks!
Christian

PS But it still will be cool if anybody could provide me a sample snipped implementing java.sql.Array


--
cu
Chris

Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser

Re: prepared statement using postgres array

From
Dave Cramer
Date:
Christian,

I'm doing this from memory so you may have to adjust.

you really only need to implement toString on the interface.
and the getBaseType.

the method has to output "{e0,e1,e2...}"

Where eN is the element of the array

Dave
On 30-Nov-07, at 12:43 PM, Christian Kindler wrote:

> Hi Dave
>
>> you have to implement the java.sql.Array
>
> hmmm ... i have tried to find a sample implementation of the
> interface, but no success.
>
> I am sure this is a ugly solution but I figures out a workaround:
>
> INSERT INTO Quote(fi_id, market_id, "date", "time",
>                  "open", high, low, "close", vol, oi,
>                  bid, bidvol, ask, askvol,
>                  last,
>                  sysstatus,
>                  buyers,  buyersvol, sellers, sellersvol,
>                  additional)
>           VALUES(?, ?, ?, ?,
>                  ?, ?, ?, ?, ?, ?,
>                  ?, ?, ?, ?,
>                  ?,
>                  ?,
>                  (select string_to_array(?, ',')::float[]),
>                  (select string_to_array(?, ',')::float[]),
>                  (select string_to_array(?, ',')::float[]),
>                  (select string_to_array(?, ',')::float[]),
>                  (select string_to_array(?, ',')::float[])
>                 );
>
> ... as is said not pretty but it works for the moment ...
>
> Thanks!
> Christian
>
> PS But it still will be cool if anybody could provide me a sample
> snipped implementing java.sql.Array
>
>
> --
> cu
> Chris
>
> Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
> Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

Re: prepared statement using postgres array

From
Kris Jurka
Date:

On Fri, 30 Nov 2007, Christian Kindler wrote:

> I have read a lot but did not find any solution for inserting a array of
> floats into pg database via jdbc and prepared statements.
>

Just now I've implemented the JDBC4 method Connection.createArrayOf, so if
you're using JDK1.6 and don't mind using a development driver that's
probably the best way to go.

http://ejurka.com/pgsql/jars/arr/

Kris Jurka

Re: prepared statement using postgres array

From
"Christian Kindler"
Date:
Kris,

thanks a lot I'll use the develop driver - it works great for me!!

> you're using JDK1.6 and don't mind using a development driver that's
> probably the best way to go.
>
> http://ejurka.com/pgsql/jars/arr

Christian
--
cu
Chris

Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser