Query with parameters with specific type

Ignition
, ,
1

Hello, I am trying to make a query but some attributes in my database have specific types which I have done before. So when I make my query, an error appears:

org.postgresql.util.PSQLException: ERROR: the “competence” column is of type “Competences” but the expression is of type variant character
Note: You must rewrite the expression or apply a type transformation to it.

I understand what the problem is but I don’t know how to fix it because the parameters only take strings, integers or something like that. And I have to take a type that I created directly in my database, what should I do?

Thank you very much in advance!

2

This page should help you with this.

For the example here should look something like this i’d guess.

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

image

3

As Victor shows, you will have to organize your SQL to construct the composite type from scalar parameters, as JDBC only natively supports scalar types in value parameters.

The PostgreSQL JDBC driver does have extensions to construct java objects compatible with arbitrary DB types, but brand-specific extensions are not exposed in Ignition at all. (And if they were, would only be accessible in gateway scope.)

Consider not making DB tables with exotic types.