On 03-Dec-2013 13:15 -0800, J Franz wrote:
Trying to debug a SQL statement (normally executed in a C# client
using OLEDB connection), but I copied into STRSQL and also tried
SQL INSERT statement, and over a hundred columns - receive a msg
SQL0420 about invalid characters in CAST argument, but no mention
of which column. Joblog has same generic msg.
INSERT using constant VALUES or a sub-select? Any explicit CAST or
data type scalar casting functions used? Given the STRSQL could be
used, we can infer there are no CAST(? to ...) parameter marker processing.
I am sure I must have noticed that the -420 sqlcode condition lacked
any preceding CPF5035 to diagnose the column name and the particulars of
the mapping\casting issue. But the solution always must have been
pretty straightforward for me, such that I had never looked into why
there is not more information given. I only have v5r3, and see the same
error in a test... with no logged CPF5035; no access to trace, or debug
to see if a mapping error was logged but /swallowed/ by the SQL.
No matter, the error is almost always the result of the inability of
the SQL to [implicitly] convert a character string into a numeric data
type. Could be as simple as the wrong decimal separator, or it could be
that the string representation of the number has other editing; e.g.
thousands separator, or a leading or trailing sign other than + for
positive or - for negative. Oddly, I was thinking trailing signs were
not supported... but a quick test showed they were acceptable. Examine
the columns that are of a numeric type, for which the corresponding
inserted value is a character string.
A simple example:
create table qtemp/dec51 (d dec(5, 1))
insert into qtemp/dec5 values('1333,3') /* DecPnt(*period) */
; -- sql0420
insert into qtemp/dec5
select '1333.3CR' from qsys2/qsqptabl
; -- sql0420
If the target column's data type had been DATE, then the SQL0180
likely would have a prior cpf5035 rc17 at least for the VALUES(). Of
course the SLQ0180 also has a replacement variable for the Host Variable
I was thinking SQLCA data structure like I use in SQLRPGLE might
show what I need, but cannot see how to specify & view in either
STRSQL screen or iNav Run SQL Statements.
SQLCA is for programming, and those ad hoc SQL processors likely will
provide little about the SQLCA; the latter may have a SQL procedure that
can be called to create a result set with some SQLCA info, but I am not
aware of one. Easy enough to pass the statement into your own compiled
program that either outputs the various SQLCA details [e.g. in
messaging], or makes them available in debug. Or to avoid compiling a
program to process the request, use the REXX interpreted language; use
the SAY statement to output the various SQLCA variables that are
automatically declared for REXX SQL.