Cannot find symbol NUMERIC

[ELIELDO MARTINS]

I have a problem with an integration test. I created an ALIAS for a function, but it gives a compilation error stating that it cannot find the numeric symbol. Does anyone use ALIAS with this data type?

CREATE ALIAS my_stored_proc AS '

  numeric envia() {

    return 1;

}

';

[Evgenij Ryazanov]

Hello!

The valid syntax is

CREATE ALIAS MY_STORED_PROC AS '
BigDecimal envia() {
    return BigDecimal.ONE;
}
';

because SQL NUMERIC data type is mapped to BigDecimal data type in Java.

[ELIELDO MARTINS]

My code is the following:

CREATE ALIAS my_stored_proc AS '
  import java.math.BigDecimal;
  @CODE
  BigDecimal send() {
    return BigDecimal.ONE;
}
';
----------------

        // call the send routine to csmp
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withFunctionName("my_stored_proc");

        SqlParameterSource paramMap = new MapSqlParameterSource()
                .addValue("id", 1);

        BigDecimal result = jdbcCall.executeFunction(BigDecimal.class, paramMap);

-----------------
But, it is returning "null"!

Can you imagine why?

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/92fde29d-e26e-4816-b752-d7e568b971c1n%40googlegroups.com.

[Evgenij Ryazanov]

With JDBC, you can call your function either with PreparedStatement or with CallableStatement:

try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) {

Statement s = c.createStatement();

s.execute("CREATE ALIAS my_stored_proc AS 'BigDecimal send() { return BigDecimal.ONE; }'");

// With PreparedStatement

PreparedStatement ps = c.prepareStatement("CALL my_stored_proc()");

ResultSet rs = ps.executeQuery();

rs.next();

System.out.println(rs.getBigDecimal(1));

// With CallableStatement

CallableStatement cs = c.prepareCall("{? = CALL my_stored_proc()}");

cs.registerOutParameter(1, Types.NUMERIC);

cs.execute();

System.out.println(cs.getBigDecimal(1));

}

In case of callable statement you should register the output parameter only, because your function doesn't have any input parameters. It is also possible to use it without registration of output parameter, in that case result can be read from it in the same way as from prepared statement.

You use a wrapper over JDBC, it has a different API, but situation in the same. Your function doesn't have any input parameters, it means you shouldn't try to pass them here, hypothetically SimpleJdbcCall can be confused by that unexpected parameter. You can try to run it without parameters. But I never used that library, so I can't be sure.

[ELIELDO MARTINS]

The tip works exactly !!!

Thanks for your attention.

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/927ea4e9-60d0-41ad-b186-95202b3bdcfan%40googlegroups.com.