Creating an array of bytea in Java to pass to a Postgresql stored procedure -

Creating an array of bytea in Java to pass to a Postgresql stored procedure -

i need pass array of bytea stored procedure (the declared type if bytea[]).

the info get's passed stored procedure, however, cannot individual bytea bytea[]. array_lower , array_upper functions study dimension of one, tumb_data[1] returns null.

if print whole bytea[] object stored proc, starts "{{-1,-40,-1,-32,0,16,74,70,73,70,0,1,1", know info making procedure.

this illustration code shows problem:

class.forname("org.postgresql.driver"); string url = "jdbc:postgresql://localhost/test"; connection c = drivermanager.getconnection(url,"andres","xxx"); list<byte[]> thumbdata=new arraylist<byte[]>(); thumbdata.add("test #1".getbytes()); thumbdata.add("test #1".getbytes()); object[] thumbs=thumbdata.toarray(); array thumbarray=c.createarrayof("bytea", thumbs); preparedstatement stmt=c.preparestatement("select test_funct(?)"); stmt.setarray(1,thumbarray); stmt.execute();

and stored procedure:

create or replace function test_funct(a_bytes bytea[]) returns boolean $body$declare v_bytes bytea; begin in array_lower(a_bytes,1) .. array_upper(a_bytes,1) loop v_bytes:= a_bytes[i]; if (v_bytes null) raise exception 'error'; end if; end loop; homecoming true; end$body$ language plpgsql volatile cost 100;

finally, here's log output when run test program:

2013-02-17 21:03:52 est log: execute <unnamed>: set extra_float_digits = 3 2013-02-17 21:03:52 est log: execute <unnamed>: select oid pg_catalog.pg_type typname = $1 2013-02-17 21:03:52 est detail: parameters: $1 = '_bytea' 2013-02-17 21:03:52 est log: execute <unnamed>: select e.typdelim pg_catalog.pg_type t, pg_catalog.pg_type e t.oid = $1 , t.typelem = e.oid 2013-02-17 21:03:52 est detail: parameters: $1 = '1001' 2013-02-17 21:03:52 est log: execute <unnamed>: select test_funct($1) 2013-02-17 21:03:52 est detail: parameters: $1 = '{{"\\x3834","\\x313031","\\x313135","\\x313136","\\x3332","\\x3335","\\x3439"},{"\\x3834","\\x313031","\\x313135","\\x313136","\\x3332","\\x3335","\\x3439"}}' 2013-02-17 21:03:52 est error: error 2013-02-17 21:03:52 est statement: select test_funct($1) 2013-02-17 21:03:52 est log: unexpected eof on client connection

any thought how pass stored procedure?

i suspect array has 2 dimensions; instead of 1 dimensional (linear) array of bytea, have two-dimensional array of bytea. indexing such array's first dimension produce empty result. note postgresql arrays of dimensions still have basic array info type, there no bytea[][] etc. utilize array_ndims(anyarray) array dimensions.

observe 1 dimensional:

with t1(ba) (values(array[ '\x0a', '\x0c', '\x0e' ] :: bytea[])) select ba, ba[1], ba[1][1] t1; ba | ba | ba ---------------------------+------+---- {"\\x0a","\\x0c","\\x0e"} | \x0a | (1 row)

vs two-dimensional:

with t1(ba) (values(array[[ '\x0a', '\x0c', '\x0e' ]] :: bytea[])) select ba, ba[1], ba[1][1] t1; ba | ba | ba -----------------------------+----+------ {{"\\x0a","\\x0c","\\x0e"}} | | \x0a (1 row)

since have not shown total value or sql, it's hard more.

if suspect jdbc driver problem help if perhaps provide self-contained compileable test case demonstrated problem, including sql script set test database.

please show actual sql gets run including parameters passed. can postgresql log files log_statement enabled.

java postgresql jdbc plpgsql