Re: Mapping SQL UDT to java class

I don't have time at the moment to test this, but I'd try Types.OTHER.

Candidly I don't expect this to work as we don't implement SQLData

I'd love to see this implemented in the driver though.

If you are really stuck you can try https://github.com/impossibl/pgjdbc-ng

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 12 November 2015 at 12:10, Shen, Ning (NSHEN) <NSHEN(at)arinc(dot)com> wrote:

> Hi
>
>
>
> We have some user defined data types in our database. For instance, ICAO
> type is defined as follow:
>
>
>
> CREATE TYPE "ICAO" AS
>
> ("Type" smallint,
>
> "Addr" bytea);
>
>
>
> In my java client side, I have:
>
>
>
> *public class ICAO implements SQLData* {
>
>
>
> @Override
>
> public void readSQL(SQLInput aStream, String aTypeName)
>
> {
>
> typeName = aTypeName;
>
>
>
> try {
>
> icaoType = aStream.readShort();
>
> icaoAddr = aStream.readBytes();
>
> }
>
> catch (SQLException e) {
>
> e.printStackTrace();
>
> }
>
> }
>
>
>
> @Override
>
> public void writeSQL(SQLOutput aStream)
>
> {
>
> try {
>
> aStream.writeShort(icaoType);
>
> aStream.writeBytes(icaoAddr);
>
> }
>
> catch (SQLException e) {
>
> e.printStackTrace();
>
> }
>
> }
>
>
>
> @Override
>
> public String getSQLTypeName()
>
> {
>
> return typeName;
>
> }
>
>
>
> public void setType(short aType)
>
> {
>
> icaoType = aType;
>
> }
>
>
>
> public void setAddr(String aHexAddr)
>
> {
>
> icaoAddr = numToBytes(hexToInt(aHexAddr));
>
> }
>
>
>
> private short icaoType;
>
> private byte[] icaoAddr;
>
> private String typeName = "ICAO";
>
>
>
>
>
> // ******* unit test driver
>
> public static void main(String[] args)
>
> {
>
> final String AVLC_SELECT = "SELECT \"RSSI\",
> \"SymbolCount\", \"ReedSolErr\", \"Quality\", \"FlagCount\",
> \"LowConfidence\", \"BrokenMsg\", \"BadCRCCount\", \"AVLC\".\"DateTime\",
> \"TimeStamp\", \"LineNum\", \"SourceFiles\".\"Station\", \"AVLC\".\"Src\",
> \"Dest\", \"AVLC\".\"Msg\" AS \"AVLCMsg\", \"SQP\".\"Msg\" AS \"SQPMsg\",
> \"AVLC\".\"RadioAddr\", \"FileName\", \"LogType\", \"SentRecv\", \"AG\",
> \"CR\", \"P\", \"F\", \"Type\", \"NS\", \"NR\", \"Score\", \"Delay\",
> \"SREJPairs\", \"XIDType\", \"Information\", \"XIDInfo\", \"BlockID\",
> \"Label\", \"Address\", \"MSN\", \"Text\", \"Agency\", \"Flight\",
> Stations.\"RadioNum\", Stations.\"Freq\", COALESCE(Stations.\"Char\", '?')
> \"Char\", \"UpDownOther\"(\"AVLC\".\"RadioAddr\", \"AVLC\".\"Src\",
> \"Dest\"), \"RFLength\"(\"AVLC\".\"Msg\"), \"ULReportInd\".\"DateTime\"
> \"ULReportIndDateTime\", \"CSMADecisionTime\", \"TM1\", \"TM2\", \"TM3\",
> \"p\""
>
> + "FROM
> \"AVLC\""
>
> + "NATURAL
> JOIN \"SourceFiles\""
>
> + "LEFT OUTER
> JOIN \"SQP\" USING(\"RadioAddr\", \"SQPFileNum\", \"SQPLineNum\")"
>
> + "LEFT OUTER
> JOIN \"ULReportInd\" USING(\"RadioAddr\", \"ULReportIndFileNum\",
> \"ULReportIndLineNum\")"
>
> + "LEFT OUTER
> JOIN \"ARINC618\" USING(\"FileNum\", \"LineNum\")"
>
> + "LEFT OUTER
> JOIN Stations USING(\"Station\", \"RadioAddr\")";
>
> final String AVLC_AC_WHERE = "WHERE (\"Dest\"
> = ? OR \"Src\" = ?) AND \"AVLC\".\"DateTime\" BETWEEN ? AND ?";
>
> final String STATIONS = "WITH Stations AS"
>
> + "("
>
> + "SELECT
> DISTINCT \"Station\", \"RadioAddr\", \"RadioNum\", \"Freq\", \"Char\""
>
> + "FROM
> \"Radios\""
>
> + "NATURAL
> JOIN \"SourceFiles\""
>
> + "LEFT OUTER
> JOIN \"InstChar\" USING(\"Freq\")"
>
> + ")";
>
>
>
> String query = STATIONS + AVLC_SELECT +
> AVLC_AC_WHERE;
>
>
>
> ICAO clnpIcao = new ICAO();
>
> clnpIcao.setType((short)1);
>
> clnpIcao.setAddr("400AE7");
>
>
>
> Connection pgConn = (new
> VdlPgDBConnection("Field20151015")).getConnection();
>
>
>
> try {
>
> PreparedStatement stm =
> pgConn.prepareStatement(query);
>
>
>
> Map<String, Class<?>> newMap = pgConn.getTypeMap();
>
> if (newMap == null)
>
> {
>
> newMap = new
> java.util.HashMap<String, Class<?>>();
>
> }
>
> newMap.put("public.ICAO",
> ICAO.class);
>
> pgConn.setTypeMap(newMap);
>
>
>
> Timestamp st =
> Timestamp.valueOf("2015-10-15 10:00:00.0");
>
> Timestamp et =
> Timestamp.valueOf("2015-10-15 12:00:00.0");
>
>
>
> stm.setObject(1, clnpIcao);
>
> stm.setObject(2, clnpIcao);
>
> stm.setTimestamp(3,st);
>
> stm.setTimestamp(4,st);
>
>
>
> ResultSet result =
> stm.executeQuery();
>
>
>
> List<List<Object>> table = new
> ArrayList<List<Object>>();
>
> List<Object> row = new
> ArrayList<Object>();
>
> Object colData;
>
>
>
> while (result.next())
>
> {
>
> for (int i =
> 1; i <= result.getMetaData().getColumnCount(); i++)
>
> {
>
>
> colData = result.getObject(i);
>
>
> if (colData != null)
>
>
> row.add(colData);
>
>
> else
>
>
> row.add("<null>");
>
> }
>
>
>
>
> System.out.print("\n row = " + row);
>
>
>
> table.add(row);
>
> }
>
>
>
> }
>
> catch (SQLException e)
>
> {
>
> e.printStackTrace();
>
> }
>
> } // ******* end of main()
>
> } // end of ICAO class
>
>
>
>
>
> The error message I got is:
>
>
>
> “*Can't infer the SQL type to use for an instance of vdlmenu.ICAO. Use
> setObject() with an explicit Types value to specify the type to use. *”
>
>
>
> However, for the overloaded setObject((int parameterIndex, Object x,
> SQLType targetSqlType)) and setObject(int parameterIndex, Object x, int
> targetSqlType),
>
> What is the value for the 3rd parameter?
>
>
>
> Thanks
>
>
>
> Ning
>