RE: SQL 22023 but data is valid

Vern,

Neither the precompile nor the final compilation listing references any
HVR* variables. I think this is a runtime issue.

Working through debug doesn't show anything odd. It's always a numeric
value that causes the error, and can be positive or negative. Running
the problem statement through STRSQL always succeeds. It isn't the same
rows each time, either. Running the same CSV file multiple times have
different rows affected. But it is always the same value positions 14 or
16.

The only thing I haven't done yet is examine the sqlinsert variable in
hex mode. Possibly a minus sign or decimal point is coming through a
different hex value? The CSV file is in the IFS (copied in through FTP)
with has a code page 437, and read using the IFS APIs.

Here is the code that creates and executes the insert:
// sqlinsertstub is: insert into schema/table (field list) values(
sqlinsert = sqlinsertstub;
for i = 1 to numvalues;
// Convert single quotes for SQL benefit.
if %scan(SQLQUOTE : csvarray(i)) > 0;
csvarray(i) = formatsqlquote(csvarray(i));
endif;
if i > 1;
sqlinsert += ',';
endif;
// Format insert string based on data type.
%occur(csvarrayinfo) = i;
select;
when datatype = 'CHAR' or datatype = 'VARCHAR';
if %len(%trimr(csvarray(i))) > length;
sqlinsert += SQLQUOTE + %subst(csvarray(i):1:length) +
SQLQUOTE;
else;
sqlinsert += SQLQUOTE + %trimr(csvarray(i)) + SQLQUOTE;
endif;
when datatype = 'DATE';
// Test date against SQL standard date formats.
dateformat = getdateformat(csvarray(i));
if dateformat <> DATEERROR and dateformat <> *blanks;
sqlinsert += 'date(' + SQLQUOTE + %trim(csvarray(i)) +
SQLQUOTE + ')';
else;
sqlinsert += 'date(''0001-01-01'')'; // Soft error.
endif;
when datatype = 'NUMERIC';
sqlinsert += 'cast(' + %trim(csvarray(i)) +
' as numeric(' + %char(length) + ',' + %char(scale) + '))';
endsl;
endfor;
sqlinsert += ')';
exec sql execute immediate :sqlinsert;

For the execute immediate, the compiler substitutes:
//**** sql execute immediate :sqlinsert;
/END-FREE
C Z-ADD 8 SQLER6
C CALL SQLROUTE
C PARM SQLCA
C PARM SQLINSERT
/FREE


Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Thursday, December 20, 2007 08:41
To: Midrange Systems Technical Discussion
Subject: RE: SQL 22023 but data is valid

Loyd

Check the output of the precompiler - the final listing - and see
what the data types for the various HVRxxxx variable. Walk through,
in debug, the part of the code that assigns all those in the insert
and see if any have "interesting" values. Also, are you using any
host variables with the same name in different contexts? I've seen
some apparently odd things that end up being explainable once you look
at them.

Hope this gets you further down the path.
Vern