Re: RPG SQL insert into

I'm leapfrogging Chris' reply back to the OP - the statement has a problem in the "host variable" - you can't make a host variable of a constant by putting a colon ahead of it - so :'ddddd' is completely invalid.

I have to believe others already noticed this. So it goes, eh?

HTH
Vern

On 10/5/2015 10:00 AM, Hiebert, Chris wrote:

I have two problems:
The first is that the following does not work
exec sql insert into FILEP (FILEKEY, FILESEQ#, FILEDATA)
values('Test 1', 3, :'ddddd');

The second is that I do not know how to find the problem.

Using the STRSQL is good if you are not actually trying to test a host variable.
i.e. The SQL syntax where a colon is followed by an RPG Variable name ":rpgvarname".

If you are only testing the syntax of the statement, and understand that all host variables need to be replaced with a static values, then using STRSQL is great.

But half the time it's the Host Variables that are mucking up the statement.

An easy way to check their syntax is to write another [very] simple SQL-RPGLE program that only contains the SQL statement and variables. Then muddle your way through the compiler errors or SQL runtime errors.

If your program doesn't trap the SQL errors from the SQL Communications Area [SQLCA] you can still get at the information by running the program in Debug.

Then you can view the job log to see the last SQL error. Viewing the error in this way is sometimes simpler because the system retrieves the Message ID from the SQL message file [QSYS/QSQLMSGF].

You can also get the same information from the SQLCODE, SQLSTATE and SQLERR variables.

You can determine the message id by using the SQLCODE variable.

EVALR APIMsgID = %Editw(Pr_SqlCode:' 0 '); //<-- 10 Character edit word "bbbbb0bbbb".
If %SUBST(APIMsgID:3:1) = *BLANK;
%SUBST(APIMsgID:1:3)='SQL';
ELSE;
%SUBST(APIMsgID:1:2) ='SQ';
ENDIF;

Then you can use this message id and the SQLERR variable to correctly view the message from the message file. This would be the same information that is displayed in the job log.


I find that most of the time the definition of the host variable is the problem. Make sure the data type matches and that should correct the issue.

When dealing with dates, sometimes it is simpler to define the host variable as a 10 character field and passing a formatted string instead of the actual date.

There were some compiler issues when dealing with dates. This date issue may have been fixed in V7R1, but I had seen the error a lot in V5R4.
Sometimes the pre-compiler doesn't correctly parse the date formats listed inside the program with the HSPEC or the EXEC SQL SET OPTION DATFMT.
In the case where the date is in a data structure and your pre-compiler gives an error about the ds not being defined, I found that specifying the date format on the compile command solved the issue.
Specifying the date format using DATFMT and DATSEP on the CRTSQLRPGI create command solves some date issues.

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.