Skip site navigation (1) Skip section navigation (2)

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

From: Vaclav Tvrdik <tvrdik(at)i3(dot)cz>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Question about passing array of a complex type from jdbc to plpgsql
Date: 2011-07-04 09:40:54
Message-ID: 4E118AA6.1080804@i3.cz (view raw or flat)
Thread:
Lists: 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[]
);




Responses

pgsql-jdbc by date

Next:From: Johann 'Myrkraverk' OskarssonDate: 2011-07-04 22:02:38
Subject: Re: Possible oversight in org.postgresql.xa.PGXAConnection.commitPrepared(Xid xid)
Previous:From: Heikki LinnakangasDate: 2011-06-30 12:00:30
Subject: Re: Possible oversight in org.postgresql.xa.PGXAConnection.commitPrepared(Xid xid)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group