Re: Weird embedded SQL problem

Just to close this out …

I had a chance to speak to Sue Romano (IBM’s Ms. SQL) and she has told me that IBM intend to add an option to the pre-compiler to allow it to work the way it used to i.e. an indicator passed as a host variable for a char(1) field will result in the column being populated with ‘0’ or ‘1’ as appropriate.

That is great news and I think Sue for her responsiveness.

During this investigation, I learnt that the SQL pre-compiler doesn’t do anywhere near as much checking as I thought. For example column lengths are not taken into account. This surprised me since I don’t seem to ever be able to get an embedded SQL right first time. “You can’t use that here” type errors are my constant companion.

The table in question was a simple event log. I was never expecting an insert to fail - but because SQL doesn’t have default error handling like RPG does, I had some basic logic in place to check the SQLCODE. Sadly it didn’t do enough to let me know we had a problem. We had been running the code on V7.6 for some time before a forced re-compile caused the error to surface. Yes the change in behaviour was documented in 7.5 memos but foolishly (as we were going to 7.6) I had not read those, just the 7.6 ones. Reminder to self - if skipping releases do NOT skip reading.

Can’t help but wonder how many other people have similar SQL but with no SQLCODE checking. Like me they are probably missing many records!


Jon Paris