AW: SQL0180 on SQL insert

The date format used in the SQL precompiler can be influenced as others
already pointed out by setting the date format in either the Compile command
or the SET OPTION statement.
But nevertheless only a single format can be taken for all date variables.
The problem is RPG or the hostvariables defined by the precompiler in RPG
because it always converts into character including all date formats in DDS.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Thomas Garvey
Gesendet: Tuesday, 10.7 2012 17:04
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL0180 on SQL insert

Yes, but that's done by the SQL precompiler. I have no control over that.
At least, if I do, I don't know how to change it.

I made the changes you suggested (specify each individual field and the
related null indicator, rather than use an externally described data
structure) on my SQL insert statement. It works. So, thanks very much for
that suggestion. After all, that's what I was trying to do... Get it
working.

I have to suggest though that this sure seems like a failure of the SQL
precompiler. It should not be generating the SQL DS to have date fields
with formats not related to the fields being inserted.

Thanks again for everyone's help.

Tom Garvey


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Tuesday, July 10, 2012 9:32 AM
To: 'Midrange Systems Technical Discussion'
Subject: AW: SQL0180 on SQL insert

Didn't I see SQL-Variables in your example formatted as *MDY/?

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Thomas Garvey
Gesendet: Tuesday, 10.7 2012 16:05
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL0180 on SQL insert

Birgitta,

If SQL "cannot handle character representations with 2 digit years (except
the current job's date format)", which of my date's is set up as a two digit
year? One is an *ISO and the others are *USA. Nowhere am I using *MDY (or
*YMD, or *DMY). Are you saying that RPG is forcing a date to be represented
as a character representation, which happens to be a two digit year? If so,
where is that done? How do I see that? All the compile references show
them as 10 characters.

Thanks

Tom Garvey

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Tuesday, July 10, 2012 12:15 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL0180 on SQL insert

Birgitta,

You are right, of course. Somehow in all the coming and goings of messages I
erased the original post and had forgotten how Thomas was inserting his
data. I'm not very sure if I have any (SQL) programs inserting the whole
record at a time (opposed as doing it as a list of columns) but, at the same
time, all my dates are defined as *ISO.

I'm pretty sure you "hit the nail on the head" regarding Thomas's problem.
Thanks for your help.

Best Regards,

Luis Rodriguez
IBM Certified Systems Expert ? eServer i5 iSeries