Re: (How to) Make composite PGObject with Text? (Was: (How to) Make SQLData of UUID?)

See my response inline

On Mon, 22 Jul 2019 at 20:45, Alexander Myodov <[email protected]> wrote:

> *TLDR, primary question:* when I create a custom subclass of PGObject to
> match a composite type containing the Text/varchar data; how do I properly
> deserialize these text fields in void PGObject::setValue(String s) and
> how do I properly serialize them in String PGObject::getValue()?
>
> *More explanation (for the readers and even for myself):*
>
> Well, seems like was my primary mistake in original email was trying to
> use the SQLData/typemap approach; seems it is still not supported by
> org.postgresql driver. And the primary way of using the PostgreSQL's
> composite types in Java is to make a matching PGObject subclass, and bind
> it through PGConnection::addDataType.
>
I'd be curious why and how the SQLData/typemap is better ?

>
> Alas, there is rather little documentation on actually doing that; and the
> existing github code gives some hints, but insufficiently. I actually
> managed to map the type created like CREATE TYPE MYFUNC_RETURN_TYPE AS (key
> UUID) to a Java class. But then faced even a more basic issue: how to
> bind a type containing the text strings (like Text or varchar)?
>
We would love for you to contribute to the documentation

>
> Imagine I have a different type CREATE TYPE MYTYPE2 AS (a TEXT, b TEXT).
> To use it in Java, both ways (receiving in the ResultSets, and setting in
> the prepared statements), I’ll need to write the matching Java class,
> something like this:
>
> public abstract class MyType2 extends PGobject implements Serializable,
> Cloneable {
>     public String a;
>     public String b;
>
>     // Not all the necessary methods are shown
>
>     public MyType2() { setType(""); }
>
>     public MyType2(String a, String b) {
>         this();
>         this.a = a;
>         this.b = b;
>     }
>
>     @Override
>     public void setValue(String s) throws SQLException {
>         // This method will parse
>         // the record literal from PostgreSQL
>         // and set the data
>     }
>
>     @Override public String getValue() {
>         // This method will convert
>         // the data into the PostgreSQL-compatible
>         // record literal
>     }
> }
>
> The problems are: 1. how to parse (setValue)  the serialized literal with
> this type properly? 2. And more importantly, how to serialize it (getValue)
> properly for PostgreSQL?
> Both questions assume that the string may be as wild as it happens - any
> Unicode, any single or double quotes, any combinations of backslash
> characters.
>
> are you sure you need to serialize it properly? The driver does encode
strings that it sends to the backend.
https://github.com/pgjdbc/pgjdbc/blob/d0453b7582975438eb889082cffcfd8ebe6e3e4d/pgjdbc/src/main/java/org/postgresql/core/v3/SimpleParameterList.java#L386


> *1.* Actually, I seem to have enough luck parsing the literal inside the void setValue(String
> s).
>
> The incoming string will be something like ("ABC","DEF"); with some weird
> handling of NULLs.
> Create a new PGTokenizer that cuts the leading and the trailing brackets,
> and that assumes the items are split with comma:
> PGtokenizer t = new PGtokenizer(PGtokenizer.removePara(s), ',');
> Then get both tokens:
> String aRaw = t.getToken(0), bRaw = t.getToken(1);
> Interestingly, there may be no getToken(1), if b is NULL. Therefore, if
> t.getSize() == 1 (rather than 2), I should consider b = null, but that’s
> a different story.
> Then I’ll check both aRaw and bRaw. If they are empty strings, the
> according field will be null, too.
> And finally, I should remove the leading and trailing double quotes if
> they exist; and then,.. I manually deal with the backslashes and
> double-quotes as they income doubled!?
> a = PGtokenizer.remove(aRaw, "\"", "\"")
>     // replace <""> to <">
>     .replaceAll("\"\"", "\"")
>     // replace <\\> to <\>
>     .replaceAll("\\\\\\\\", "\\\\");
>
> Is that right way to deal with it, so low-level? But there seems no helper
> de-escaping functions that I could use and be safe. But don’t I miss
> something else to be de-escaped?..
>
> *2.* More problem is with the serializing the data in the String
> getValue().
> I’ll have to create the string like ("Value of a","Value of b"); if some
> of them e.g. a is NULL, the string will look something like (,"Value of
> b").
> Well, okay; I should add leading/trailing brackets and split them with
> comma; and if a or b is null, put an empty string instead of it.
> But how to properly escape the string itself?
> The best I found is String.format("\"%s\"", Utils.escapeLiteral(null,
> str, true)) – but it doesn’t seem to handle various combinations of
> single or double quotes.
>
> Any idea how to escape the strings properly then?
>

If you send such a string into setString does it fail ? (I'm genuinely
curious)

Dave

>
> пн, 22 июл. 2019 г. в 00:58, Alexander Myodov <[email protected]>:
>
>> Hello!
>>
>> I seem to miss something obvious maybe, but I cannot find a way to use
>> UUID as SQLData; for example, to make a proper JDBC handling of custom
>> PostgreSQL type containing an UUID.
>>
>> Imagine I made a custom type returned from some PL/PgSQL function:
>>
>> CREATE TYPE MYFUNC_RETURN_TYPE AS
>>
>> (
>>
>>   key UUID
>>
>> );
>>
>>
>> Now, to handle it in JDBC, I need to make a custom type like MyfuncReturnType implements
>> SQLData, and then add it to the type map of the connection. In
>> MyfuncReturnType, I’ll need to implement void writeSQL(SQLOutput stream)
>> method, and at some point I’ll need to do something like this: to call
>> either
>>
>> SQLOutput::writeObject(SQLData x)
>>
>> or
>>
>> SQLOutput::writeObject(Object x, SQLType targetSqlType),
>>
>> passing my UUID in somehow.
>>
>>
>> But UUID doesn’t satisfy SQLData interface in any way; and if I use the
>> second method, I do not have a proper SQLType for UUID anywhere.
>>
>>
>> I previously used the pgjdbc-ng driver, and they had a collection of
>> postgresql-specific SQLType's; but that driver had other problems, so I
>> decided to switch to the mainstream JDBC driver, and faced this lack.
>>
>>
>> Any hints please?
>>
>> --
>> Alex Myodov
>>
>
>
> --
> Alex Myodov
>