[manifold-sql] Code doesn't compile when using :: to cast a parameter or call function on parameter.

[oldshensheep]

Describe the bug
Code doesn't compile when using :: to cast a parameter.

error: method execute in interface Fragment_7963949495587867082 cannot be applied to given types;
              "[.sql/] insert into table_name(data) values(?::jsonb) ".execute(ctx,
                                                                                                  ^
  required: SqlChangeCtx
  found:    SqlChangeCtx,String
  reason: actual and formal argument lists differ in length

To Reproduce
Steps to reproduce the behavior:
table_name(data) is jsonb in postgresql

            DB.addSqlChange(ctx -> {
              "[.sql/] insert into table_name(data) values(?::jsonb) ".execute(ctx,"string");
            });

            DB.addSqlChange(ctx -> {
              "[.sql/] insert into table_name(data) values(to_hex(?)) ".execute(ctx,"string");
            });

Expected behavior
The code should work like the example below.

    Class.forName("org.postgresql.Driver");
    Connection conn = DriverManager.getConnection(
        "jdbc:postgresql://localhost:5432/postgres");
    String insertSQL = "INSERT INTO table_name(data) VALUES (?::jsonb)";
    try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
      pstmt.setString(1, "{}");
      pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }

Screenshots
If applicable, add screenshots to help explain your problem (drag/drop them here).

Desktop (please complete the following information):

• OS Type & Version:
• Java/JDK version: 21
• IDE version (IntelliJ IDEA or Android Studio): IntelliJ IDEA
• Manifold version: 2024.1.14
• Manifold IntelliJ plugin version: 2024.1.0

Additional context
Add any other context about the problem here.

Stack trace
Please include a stack trace if applicable

[rsmckinney]

Hi @oldshensheep. Manifold uses named parameters prefixed with : where you would otherwise use JDBC ?.

 DB.addSqlChange(ctx -> {
              "[.sql/] insert into table_name(data) values(to_hex(:data)) ".execute(ctx,"string");
            });

DB.addSqlChange(ctx -> {
              "[.sql/] insert into table_name(data) values(:data::jsonb) ".execute(ctx,"string");
            });

See the Query docs for this.

[oldshensheep]

I just tried, still the same error.
If I change the database schema, change the type of table_name(data) from jsonb to text and remove the ::jsonb from code, then no error occurred.

[oldshensheep]

Or if I remove the ::jsonb from SQL, the code compiles, but fails at runtime.

Caused by: org.postgresql.util.PSQLException: ERROR: column "data" is of type jsonb but expression is of type character varying

Is this an expected error? JDBC standard can't map jsonb so it fallback to string.

[rsmckinney]

Ah, yes there appears to be an issue with the json/jsonb postgresql data type. Will have a deeper look. Thanks.

[rsmckinney]

Fix available with release 2024.1.15. Thanks for reporting this!

[oldshensheep]

using :: to cast a parameter works now but call function on parameter failed with the same error

[rsmckinney]

If your app defines more than one dbconfig, you probably need to qualify your sql command. Instead of “[.sql/]…” try ”[.sql:DB/]….

[oldshensheep]

Ah, I see the manifold plugin error  ERROR: function to_hex(unknown) is not unique Hint: Could not choose a best candidate function. You might need to add explicit type casts. Position: 87
I change the to_hex(?) to to_hex(?::int) , the code can be compiled again.