Question about passing array of a complex type from jdbc to plpgsql - Mailing list pgsql-jdbc
| From | Vaclav Tvrdik |
|---|---|
| Subject | Question about passing array of a complex type from jdbc to plpgsql |
| Date | |
| Msg-id | 4E118AA6.1080804@i3.cz Whole thread Raw |
| Responses |
Re: Question about passing array of a complex type from jdbc
to plpgsql
|
| List | pgsql-jdbc |
Hello,
I have a question about passing about passing array of complex type into plpgsql function. We have function with following signature:
create or replace function PARSE_BTS_WALK_TRACK( p_user BTS_WALK_TRACK.USER_ID%type, -- integer
p_client_imei BTS_WALK_TRACK.IMEI%type, -- varchar
p_btwt_recs BTWT_RECORD[],
out po_create_complex_cnt integer,
out po_rslt FCALL_RESULT) as
$BODY$
.....
$BODY$
language 'plpgsql' volatile security definer;
Types definitions are bellow and the point is in BTWT_RECORD which itself contains array of other type.
It is possible (and correctly working) to execute this function from psql with a command:
select PARSE_BTS_WALK_TRACK( 1, 'imei', ARRAY[('127.0.0.1', 80, 1111111, 230, 2, 1, 11, 230, 2, 1, 111, 14.0, 50.0, null, 1,ARRAY[(14.0, 54.0, null, null, null, null, null, null, 'TEST')::GPS_RECORD])::BTWT_RECORD]);
First we have tried to call this method using JDBC Array and Struct types, but postgresql jdbc driver doesn't implement Struct yet so we weren't able to get thinks work (Connection.createStruct is not implemented and with custom Struct implementations are problems too).
So we are using PGObject to push the data to the database and we have problem with correct syntax, mainly for complex usages. We could not find any info about it. Finally we have been able to call the function from jdbc by passing a text representation of the array of the nested type GPS_RECORD. Working approach seams to be like (the real working code we use is embraced in our framework and thus is quite long to be posted here):
Connection conn = ... from some pool ...
CallableStatement statement = connection.prepareCall("PARSE_BTS_WALK_TRACK(?, ?, ?, ?)");
... register some input params
String[] params ...
params[0] = "(85.160.201.65, 58193, 1203099449, 230, 2, 3142, 203293913, 230, 2, 1137, 203343914, 0.0, 0.0,,,{\"(\"\\\\\",\"1\\\\\",\"\\\\\",\"1\\\\\",\"1\\\\\",\"1\\\\\",\"1\\\\\",\"1\\\\\",\"A\\\\\")\"})";
params[1] = next record definition here
....
....
PGobject[] objs = new PGObject[params.length];
for (int i = 0; i < params.length;i++) {
final PGobject obj = new PGobject();
obj.setType("BTWT_RECORD");
obj.setValue(params[a]);
objs[i] = obj;
}
Object value = statement.getConnection().createArrayOf("BTWT_RECORD", objs);
statement..setObject(1, value, Types.ARRAY);
... register some output params
statement.execute();
So we have two basic questions:
1) is it supported passing such complex structures (array of types) from jdbc without using a text representation for the data (I mean purely using Java classes Array, PgObject or similar) ?
2) If not we are OK with using "mixed solution" (part of a passed data is text representaion), but rules for escaping are unclear to us (we found above syntax by try/error method, mainly assymetric backslashing of nested values looks weird) and we could not find some description how to do it. Can someone point us to description or clear how the rules for text representation of nested types are ? In near time we are going to pass even more complex data between jdbc and postgres, so try/error approach is not best one for us:-).
Thanks in advance for any help
Vaclav TVRDIK
Types definition used in function are following:
create type FCALL_RESULT as (
STATUS varchar(5), -- OK, WARN, ERR, NOTICE
ERROR_CODE int, -- 0 = OK
ERROR_DESC varchar(1024),
PARAMS varchar(1024) -- if needed, csv list
);
create type GPS_RECORD as (
GPS_LATITUDE float8,
GPS_LONGTITUDE float8,
GPS_ALTITUDE float8,
GPS_VELOCITY float8,
GPS_TIME integer,
GPS_ALTITUDE_ACC float8,
GPS_ACC float8,
GPS_HEADING float8,
GPS_SOURCE varchar
);
create type BTWT_RECORD as (
IP_ADDRESS varchar,
PORT integer,
CLIENT_TIME integer, -- sec from epoch
CELL_OUT_MCC integer,
CELL_OUT_MNC integer,
CELL_OUT_LAC integer,
CELL_OUT_CELLID integer,
CELL_IN_MCC integer,
CELL_IN_MNC integer,
CELL_IN_LAC integer,
CELL_IN_CELLID integer,
GPS_LATITUDE float8,
GPS_LONGTITUDE float8,
GPS_ALTITUDE float8,
LOG_SEQNO integer,
GEO_INFO GPS_RECORD[]
);
pgsql-jdbc by date: