CODEWITHSUNDEEP
javapostgresqljdbcplpgsql
Andres Olarte
Andres Olarte

Reputation: 4380

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

I need to pass an array of bytea to a stored procedure (the declared type if bytea[]).

The data get's passed to my stored procedure, however, I cannot get the individual bytea from the bytea[]. The array_lower and array_upper functions report a dimension of one, but tumb_data[1] always returns NULL.

If I print the whole bytea[] object from my stored proc, I get something that starts with "{{-1,-40,-1,-32,0,16,74,70,73,70,0,1,1", so I know data is making it to the procedure.

This is some example code that shows the 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 this is the stored procedure:

CREATE OR REPLACE FUNCTION test_funct(a_bytes bytea[])
  RETURNS boolean AS
$BODY$DECLARE
    v_bytes bytea;
BEGIN
FOR i IN array_lower(a_bytes,1) .. array_upper(a_bytes,1) LOOP 
        v_bytes:= a_bytes[i];
        IF (v_bytes IS NULL) THEN
            RAISE EXCEPTION 'error';
        END IF; 
    END LOOP;

    RETURN true;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Finally, here's the log output when I run my 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 FROM pg_catalog.pg_type WHERE 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 FROM pg_catalog.pg_type t, pg_catalog.pg_type e WHERE t.oid = $1 and 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 idea how to pass this to my stored procedure?

Upvotes: 2

Views: 2403

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324445

I suspect that your array has two dimensions; instead of a one dimensional (linear) array of bytea, you have a two-dimensional array of bytea. Indexing such an array's first dimension will produce an empty result. Note that PostgreSQL arrays of any dimensions still have the basic array data type, there is no bytea[][] etc. Use array_ndims(anyarray) to get the array dimensions.

Observe one dimensional:

WITH t1(ba) AS (VALUES(ARRAY[ '\x0a', '\x0c', '\x0e' ] :: bytea[]))
SELECT ba, ba[1], ba[1][1] FROM t1;
            ba             |  ba  | ba 
---------------------------+------+----
 {"\\x0a","\\x0c","\\x0e"} | \x0a | 
(1 row)

vs two-dimensional:

WITH t1(ba) AS (VALUES(ARRAY[[ '\x0a', '\x0c', '\x0e' ]] :: bytea[]))
SELECT ba, ba[1], ba[1][1] FROM t1;

             ba              | ba |  ba  
-----------------------------+----+------
 {{"\\x0a","\\x0c","\\x0e"}} |    | \x0a
(1 row)

Since you have not shown the full value or your SQL, it's hard to say more.

If you suspect a JDBC driver problem it would help if you could perhaps provide a self-contained compileable test case that demonstrated the problem, including SQL script to set up a test database.

Please show the actual SQL that gets run including the parameters passed. You can get this from the PostgreSQL log files with log_statement enabled.

Upvotes: 2

Related Questions