[
https://issues.apache.org/jira/browse/TRAFODION-3099?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16526472#comment-16526472
]
ASF GitHub Bot commented on TRAFODION-3099:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1623#discussion_r198899742
--- Diff: core/sql/cli/CliExpExchange.cpp ---
@@ -3804,7 +3893,10 @@ InputOutputExpr::inputValues(atp_struct *atp,
if (!source) {
continue;
}
-
+ if(isOdbc)
--- End diff --
Maybe trafci does not require quotes around string literals in the "using"
clause. Also, I don't think we should allow this kind of syntax in the "using"
clause. We are specifying the value of a parameter that already has a data
type. Specifying a character set at this time does not make sense, that syntax
is meant for SQL queries, not for variables of parameters.
Note that the "using" clause does not accept SQL syntax, only values. So,
execute xx using 3+4 would probably insert '3+4' into the database, not '7'.
> Prepare execute fails to handle N'<string>', _iso88591'<string>',
> _utf8'<string>' for a nchar column
> ----------------------------------------------------------------------------------------------------
>
> Key: TRAFODION-3099
> URL: https://issues.apache.org/jira/browse/TRAFODION-3099
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: shaoyong.li
> Assignee: shaoyong.li
> Priority: Major
>
> As shown below, for a nchar column c1 with a value of N'ABCD' in the table t,
> a direct select from t with predicates c1=N'ABCD', c1='ABCD',
> c1=_iso88591'ABCD', and c1=_utf8'ABCD' all are able to find the match.
> However, with a prepared query xx executed using a parameter ? in sqlci and
> trafci operator have failed.
> From sqlci:
> >>drop table if exists t;
>
> --- SQL operation complete.
> >>create table t (c1 nchar(10));
>
> --- SQL operation complete.
> >>insert into t values (N'ABCD');
>
> --- 1 row(s) inserted.
> >>select * from t;
>
> C1
> --------------------
>
> ABCD
>
> --- 1 row(s) selected.
> >>select * from t where c1=N'ABCD';
>
> C1
> --------------------
>
> ABCD
>
> --- 1 row(s) selected.
> >>select * from t where c1='ABCD';
>
> C1
> --------------------
>
> ABCD
>
> --- 1 row(s) selected.
> >>select * from t where c1=_iso88591'ABCD';
>
> C1
> --------------------
>
> ABCD
>
> --- 1 row(s) selected.
> >>select * from t where c1=_utf8'ABCD';
>
> C1
> --------------------
>
> ABCD
>
> --- 1 row(s) selected.
> >>
> >>prepare xx from select * from t where c1=?;
>
> --- SQL command prepared.
> >>execute xx using N'ABCD';
>
> --- 0 row(s) selected.
> >>execute xx using 'ABCD';
>
> C1
> --------------------
>
> ABCD
>
> --- 1 row(s) selected.
> >>execute xx using _iso88591'ABCD';
>
> C1
> --------------------
>
> ABCD
>
> --- 1 row(s) selected.
> >>execute xx using _utf8'ABCD';
>
> C1
> --------------------
>
> ABCD
>
> --- 1 row(s) selected.
>
> From trafci:
>
> trafci sees even more failures:
>
> $ trafci
>
> Welcome to EsgynDB Command Interface
> Copyright (c) 2015-2018 Esgyn Corporation
>
> User Name: db__root
> Host Name/IP Address: localhost:23400
>
> Connected to EsgynDB Advanced
>
> SQL>obey mytest.sql;
>
> SQL>drop table if exists t;
>
> --- SQL operation complete.
>
> SQL>create table t (c1 nchar(10));
>
> --- SQL operation complete.
>
> SQL>insert into t values (N'ABCD');
>
> --- 1 row(s) inserted.
>
> SQL>select * from t;
>
> C1
> --------------------
> ABCD
>
> --- 1 row(s) selected.
>
> SQL>select * from t where c1=N'ABCD';
>
> C1
> --------------------
> ABCD
>
> --- 1 row(s) selected.
>
> SQL>select * from t where c1='ABCD';
>
> C1
> --------------------
> ABCD
>
> --- 1 row(s) selected.
>
> SQL>select * from t where c1=_iso88591'ABCD';
>
> C1
> --------------------
> ABCD
>
> --- 1 row(s) selected.
>
> SQL>select * from t where c1=_utf8'ABCD';
>
> C1
> --------------------
> ABCD
>
> --- 1 row(s) selected.
>
> SQL>prepare xx from select * from t where c1=?;
>
> --- SQL command prepared.
>
> SQL>execute xx using N'ABCD';
>
> --- 0 row(s) selected.
>
> SQL>execute xx using 'ABCD';
>
> C1
> --------------------
> ABCD
>
> --- 1 row(s) selected.
>
> SQL>execute xx using _iso88591'ABCD';
>
> *** ERROR[29183] Invalid Parameter Value: CHAR input data is longer than the
> length for column: 0
>
> SQL>execute xx using _utf8'ABCD';
>
> *** ERROR[29183] Invalid Parameter Value: CHAR input data is longer than the
> length for column: 0
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
