[
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)
