hi list!
i think the reason is the string representation of the
java.sql.Array being created/sent which is (as far
as i understood it) being created in
AbstractJdbc4Connection.appendArray(StringBuffer sb, Object elements, char delim).
this function recusrively calls itself if
o.getClass().isArray() is true, and appends
all array values as a subarray.
for normal multidimensional arrays i think this is correct,
but in case of a bytea[] i'm not sure. my best guess is,
that if the Oid of the parameter is BYTEA or BYTEA_ARRAY,
the value should be hex-encoded and not treated as a nested array.
what is being sent to the server looks like this:
String representation of array:
{{"118","-110","-61","-83","53","64","-69","-128","60","2","11","58","-18","102","-51","-120","-121","18","50","52","-22","12","110","113","67","-64","-83","-41","63","-12","49","-19"},{"63","-60","-52","-2","116","88","112","-30","-64","-39","-97","113","-13","15","-16","101","108","-115","-19","-44","28","-63","-41","-45","-45","118","-80","-37","-26","-123","-30","-13"},{"-117","91","-99","-80","-63","61","-78","66","86","-56","41","-86","54","74","-87","12","109","46","-70","49","-117","-110","50","-92","-85","-109","19","-71","84","-45","85","95"}}
but according to johto in the #postgresql irc channel,
postgres expects an array of bytea to look like so:
String representation of array:
{"\x7692c3ad3540bb803c020b3aee66cd8887123234ea0c6e7143c0add73ff431ed","\x3fc4ccfe745870e2c0d99f71f30ff0656c8dedd41cc1d7d3d376b0dbe685e2f3","\x8b5b9db0c13db24256c829aa364aa90c6d2eba318b9232a4ab9313b954d3555f"}
i have attached a small program demonstrating the problem and
also showing, that passing an array of encoded strings works as
expected.
my question is:
* should the jdbc driver hex-encode the bytea values inside a bytea[]
before sending them to the server
OR
* should the server "know" that the multidimensional array being
passed is actually a bytea[] and not a byte[][].
--
rgds, armin walland