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

["Francis Chuang (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-1164?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Francis Chuang updated CALCITE-1164:
------------------------------------
    Fix Version/s:     (was: avatica-1.12.0)
                   avatica-1.13.0

> 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: avatica-1.13.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
(v7.6.3#76005)