Thread: examples of SQL Arrays and jdbc?
first, apologies for that blank message i just accidentally sent to the
list.
i'm looking for some decent examples of using the SQL array type with JDBC.
i have a number of arrays of 12 integers which need to come in and out. my
reference right now is the Sun _JDBC API Tutorial and Reference_, and it's
leaving me thinking that the Array/JDBC API is incredibly badly thought
out, e.g. i can extract an int [] easily but i have to convert it to an
Integer [] in order to write it back out. it can't really be this bad, can
it?
thanks,
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security
On Thursday, February 6, 2003, at 06:18 PM, Richard Welty wrote:
> first, apologies for that blank message i just accidentally sent to the
> list.
>
> i'm looking for some decent examples of using the SQL array type with
> JDBC.
> i have a number of arrays of 12 integers which need to come in and
> out. my
> reference right now is the Sun _JDBC API Tutorial and Reference_, and
> it's
> leaving me thinking that the Array/JDBC API is incredibly badly thought
I couldn't agree more. JDBC Array support sucks.
> out, e.g. i can extract an int [] easily but i have to convert it to an
> Integer [] in order to write it back out. it can't really be this bad,
> can
> it?
Postgres is cool in that you can do a stmt.setString(...) (if using
prepared statements) for any datatype, including arrays.
Postgres' string form of an array is (in its simplest form):
{1, 2, 3, N}
or
{"a", "b", "c", "N"}
So you can convert your int[] into a String in the above form and just
do:
stmt.setString(3, Helper.arrayToPostgresString(myIntArray));
And if you're creating INSERT/UPDATE statements yourself:
create table foo (bar int[]);
insert into foo (bar) values ('{"1","2","3"}');
I got fancy and stole Postgres' java.sql.Array implementation and added
a little factory method to it, so I can do things like this:
java.sql.Array array = MyPostgresArray.create(new int[] { 1, 2, 3 });
stmt.setArray(3, array);
or
java.sql.Array array = MyPostgresArray.create(new int[] { 1, 2, 3 });
String sql = "insert into foo (bar) values (" + array.toString() + ")";
I know this class works great w/ Postgres 7.2.x. I haven't tested it
with 7.3. It's attached in case you find it useful. Note that this
class doesn't support multidimensional arrays.
eric
Attachment
On Thu, 6 Feb 2003 19:11:15 -0500 "Eric B. Ridge" <ebr@tcdi.com> wrote:
> On Thursday, February 6, 2003, at 06:18 PM, Richard Welty wrote:
> > out, e.g. i can extract an int [] easily but i have to convert it to an
> > Integer [] in order to write it back out. it can't really be this bad,
> > can it?
> Postgres is cool in that you can do a stmt.setString(...) (if using
> prepared statements) for any datatype, including arrays.
> Postgres' string form of an array is (in its simplest form):
> {1, 2, 3, N}
> or
> {"a", "b", "c", "N"}
> So you can convert your int[] into a String in the above form and just
> do:
> stmt.setString(3, Helper.arrayToPostgresString(myIntArray));
ah, ok, thanks. my path is now clear.
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security
Yes, in general any postgres data type can be set/get using
setString/getString
This may change in the future, it is really an artifact of the wire
protocol
Be warned!
Dave
On Thu, 2003-02-06 at 19:11, Eric B.Ridge wrote:
> On Thursday, February 6, 2003, at 06:18 PM, Richard Welty wrote:
>
> > first, apologies for that blank message i just accidentally sent to the
> > list.
> >
> > i'm looking for some decent examples of using the SQL array type with
> > JDBC.
> > i have a number of arrays of 12 integers which need to come in and
> > out. my
> > reference right now is the Sun _JDBC API Tutorial and Reference_, and
> > it's
> > leaving me thinking that the Array/JDBC API is incredibly badly thought
>
> I couldn't agree more. JDBC Array support sucks.
>
> > out, e.g. i can extract an int [] easily but i have to convert it to an
> > Integer [] in order to write it back out. it can't really be this bad,
> > can
> > it?
>
> Postgres is cool in that you can do a stmt.setString(...) (if using
> prepared statements) for any datatype, including arrays.
>
> Postgres' string form of an array is (in its simplest form):
> {1, 2, 3, N}
> or
> {"a", "b", "c", "N"}
>
> So you can convert your int[] into a String in the above form and just
> do:
> stmt.setString(3, Helper.arrayToPostgresString(myIntArray));
>
> And if you're creating INSERT/UPDATE statements yourself:
>
> create table foo (bar int[]);
> insert into foo (bar) values ('{"1","2","3"}');
>
> I got fancy and stole Postgres' java.sql.Array implementation and added
> a little factory method to it, so I can do things like this:
> java.sql.Array array = MyPostgresArray.create(new int[] { 1, 2, 3 });
> stmt.setArray(3, array);
> or
> java.sql.Array array = MyPostgresArray.create(new int[] { 1, 2, 3 });
> String sql = "insert into foo (bar) values (" + array.toString() + ")";
>
> I know this class works great w/ Postgres 7.2.x. I haven't tested it
> with 7.3. It's attached in case you find it useful. Note that this
> class doesn't support multidimensional arrays.
>
> eric
>
>
> ______________________________________________________________________
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
Dave Cramer <Dave@micro-automation.net>
On 06 Feb 2003 20:27:19 -0500 Dave Cramer <Dave@micro-automation.net> wrote:
> Yes, in general any postgres data type can be set/get using
> setString/getString
>
> This may change in the future, it is really an artifact of the wire
> protocol
>
> Be warned!
taking this advice to heart, i switched my code to write jdbc int arrays
from using strings, per eric's posting, to using Integer arrays, saving
them with setObject, per page 266-267 of the sun jdbc book (second
edition). their example shows an array of strings:
String [] departments = {"accounting", "personal", "marketing"};
PreparedStatement pstmt = con.prepareStatement(
"UPDATE AJAX_LTD SET DEPTS = ? WHERE ID = 0045");
pstmt.setObject( 1, departments);
my code differs in that the object is an Integer [],
Debug.println( "starting arrays, field_index: " + field_index);
statement.setObject( ++field_index,
intArrayToInteger( target_percentages));
Debug.println( "first array done, field_index: " + field_index);
where intArrayToInteger appears to work correctly, and is as follows:
public static Integer [] intArrayToInteger( int [] ints){
Integer [] IntArray = new Integer [ints.length];
for( int i = 0; i < ints.length; i++){
IntArray[i] = new Integer( ints[i]);
}
return IntArray;
}
i'm getting the following error:
starting arrays, field_index: 7
SQLException: The table for [Ljava.lang.Integer;
is not in the database. Contact the DBA, as the
database is in an inconsistent state.
i'd previously seen this when i mistakenly tried to pass int [] arrays to
setObject. am i missing something here? perhaps some Objects work and not
others?
environment (which i inadvertantly left out of my note earlier today on my
problems with set methods on my UPDATE statement):
RedHat 7.3
postgresql installed from rpm, 7.2.1-5
pgjdbc2.jar driver from website late october of last year
jdk 1.4.1_01
thanks in advance,
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security
Richard,
The current implementation of setObject() doesn't support arrays.
thanks,
--Barry
Richard Welty wrote:
> On 06 Feb 2003 20:27:19 -0500 Dave Cramer <Dave@micro-automation.net> wrote:
>
>
>>Yes, in general any postgres data type can be set/get using
>>setString/getString
>>
>>This may change in the future, it is really an artifact of the wire
>>protocol
>>
>>Be warned!
>
>
> taking this advice to heart, i switched my code to write jdbc int arrays
> from using strings, per eric's posting, to using Integer arrays, saving
> them with setObject, per page 266-267 of the sun jdbc book (second
> edition). their example shows an array of strings:
>
> String [] departments = {"accounting", "personal", "marketing"};
> PreparedStatement pstmt = con.prepareStatement(
> "UPDATE AJAX_LTD SET DEPTS = ? WHERE ID = 0045");
> pstmt.setObject( 1, departments);
>
> my code differs in that the object is an Integer [],
>
> Debug.println( "starting arrays, field_index: " + field_index);
> statement.setObject( ++field_index,
> intArrayToInteger( target_percentages));
> Debug.println( "first array done, field_index: " + field_index);
>
> where intArrayToInteger appears to work correctly, and is as follows:
>
> public static Integer [] intArrayToInteger( int [] ints){
> Integer [] IntArray = new Integer [ints.length];
>
> for( int i = 0; i < ints.length; i++){
> IntArray[i] = new Integer( ints[i]);
> }
> return IntArray;
> }
>
> i'm getting the following error:
>
> starting arrays, field_index: 7
> SQLException: The table for [Ljava.lang.Integer;
> is not in the database. Contact the DBA, as the
> database is in an inconsistent state.
>
> i'd previously seen this when i mistakenly tried to pass int [] arrays to
> setObject. am i missing something here? perhaps some Objects work and not
> others?
>
> environment (which i inadvertantly left out of my note earlier today on my
> problems with set methods on my UPDATE statement):
>
> RedHat 7.3
> postgresql installed from rpm, 7.2.1-5
> pgjdbc2.jar driver from website late october of last year
> jdk 1.4.1_01
>
> thanks in advance,
> richard
> --
> Richard Welty rwelty@averillpark.net
> Averill Park Networking 518-573-7592
> Unix, Linux, IP Network Engineering, Security
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>