[jira] [Comment Edited] (CALCITE-1164) Use setObject(int, Object, int) when binding parameters

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1164?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15209491#comment-15209491 ] 

Julian Hyde edited comment on CALCITE-1164 at 3/24/16 12:33 AM:
----------------------------------------------------------------

When preparing a statement, the SQL validator figures out the type of each parameter. For instance, in {{UPDATE EMP SET name = ? WHERE empno = ?}}, it can figure out that the parameters have types VARCHAR and INTEGER. If the validator can't figure out a type, prepare fails. After prepare has succeeded, and even before you have assigned values to any parameters, you can ask the type, by calling {{PreparedStatement.getParameterMetadata().getParameterType( i )}}.

Suppose that parameter 2 is of type {{java.sql.Types.INT}}. It's still OK to call {{setObject(2, "123")}}, or, for that matter, {{setString(2, "123")}}, because JDBC supports implicit conversion from Java String to SQL INTEGER. If you called {{setString(2, "invalid number")}} you would get a runtime error from the JDBC client.

In other words, SQL parameters are strongly typed, but arguments are implicitly coerced to the parameter type for quite a few (source, target) type combinations.


was (Author: julianhyde):
When preparing a statement, the SQL validator figures out the type of each parameter. If it can't figure out a type, prepare fails. After prepare has succeeded, you can ask the type, by calling {{PreparedStatement.getParameterMetadata().getParameterType(i)}}.

Suppose that parameter 1 is of type {{java.sql.Types.INT}}. It's still OK to call {{setObject(1, "123")}}, or, for that matter, {{setString(1, "123")}}, because JDBC supports implicit conversion from Java String to SQL INTEGER.

In other words, SQL parameters are strongly typed, but arguments are implicitly coerced to the parameter type.

> Use setObject(int, Object, int) when binding parameters
> -------------------------------------------------------
>
>                 Key: CALCITE-1164
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1164
>             Project: Calcite
>          Issue Type: Improvement
>          Components: avatica
>            Reporter: Josh Elser
>            Priority: Minor
>             Fix For: 1.8.0
>
>
> Trying to capture some discussion from a recent pull request: https://github.com/apache/calcite/pull/209#issuecomment-195025402
> In a few places (such as https://github.com/apache/calcite/blob/master/avatica/server/src/main/java/org/apache/calcite/avatica/jdbc/JdbcMeta.java#L795-L800), we perform:
> {code}
> TypedValue o = parameterValues.get(i);
> preparedStatement.setObject(i + 1, o.toJdbc(calendar));
> {code}
> Vladimir stated that this is ambiguous (stored procedures differing by argument list and differentiating between the actual type when the value is null) and would be remedied by passing along the desired type when setting the object.
> We may also have to invoke setNull explicitly? This is unclear to me.
> h5. Reasons why "explicit sql type" is important
> h6. Calling the proper function
> Consider database has two functions that differ in type of argument only.
> For instance {{compute(varchar)}}, {{compute(numeric)}}, and {{compute(user_defined_struct)}}
> Which one should be executed if calling with just {{preparedStatement.setObject(i, null)}}?
> There is not enough information for the database to choose between varchar and numeric function.
> h6. Performance
> Execution plan depends on the types of bind parameters. For instance, in PostgreSQL, you must tell all the datatypes of the bind variables right in {{PREPARE}} message.
> That basically means, if you flip between datatypes, you have to use different prepared statement IDs.
> If just {{String val = ...; ps.setObject(1, val)}} is used, then for non-null it can result in {{String}} execution plan, while for null it can flip to unknown.
> Same for batched statement execution. PostgreSQL just cannot handle datatype flips right in the middle of the batch. It is handled in the pgjdbc driver, so it cuts batch in several sub batches, so it becomes less efficient.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)