Re: strsql or iNav sql - how to see sqlca detail for errors

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
iNav.
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 number.

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.