Re: (How to) Make composite PGObject with Text? (Was: (How to) MakeSQLData of UUID?) - Mailing list pgsql-jdbc

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.

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)?

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.

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?

пн, 22 июл. 2019 г. в 00:58, Alexander Myodov <amyodov@gmail.com>:

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