Question about passing array of a complex type from jdbc to plpgsql

Поиск
Список
Период
Сортировка
От Vaclav Tvrdik
Тема Question about passing array of a complex type from jdbc to plpgsql
Дата
Msg-id 4E118AA6.1080804@i3.cz
обсуждение исходный текст
Ответы Re: Question about passing array of a complex type from jdbc to plpgsql  (Maciek Sakrejda <msakrejda@truviso.com>)
Список 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 по дате отправления:

Предыдущее
От: Heikki Linnakangas
Дата:
Сообщение: Re: Possible oversight in org.postgresql.xa.PGXAConnection.commitPrepared(Xid xid)
Следующее
От: "Johann 'Myrkraverk' Oskarsson"
Дата:
Сообщение: Re: Possible oversight in org.postgresql.xa.PGXAConnection.commitPrepared(Xid xid)