SQL Precompiler error SQL0206

[gwilburn]

I'm receiving this error... yet the program compiles (not sure if it works yet)

SQL0206 10 3984 Position 56 Column or global variable PGMNAME not found

The SQL Insert statement it's referencing is in a subprocedure. The PGMNAME field is defined in the D-specs of the main program as part of the SDS.

Exec Sql Insert into OEBOXCBX(bxridc, bxcom#, bxord#, bxrel#,
                    bxbid#, bxbox#, bxprt#, bxqty#, bxent#, bxsfx#, bxpckr)
Values('BX', :inCom#, :inOrd#, :inRel#, :xd.sscc, :wkbox#,
                    :xd.part, :xd.qty, psent#, pssfx#, pgmname);

I am really trying to use SQL more for IO... but stuff like this can cause a person to regress to native IO.

[Viking]

On most of your variables that you're using on the VALUES part of your SQL, you have a colon in front of them as you should. Try a colon in front of those last three variables too, like :PSENT#, :PSSFX#, :PGMNAME.

[gwilburn]

On most of your variables that you're using on the VALUES part of your SQL, you have a colon in front of them as you should. Try a colon in front of those last three variables too, like :PSENT#, :PSSFX#, :PGMNAME.

Just call me Homer.... Homer Simpson, DOH!
(I should change my avatar)

[wegrace]

Sometimes it just takes a second pair of eyes.

[gwilburn]

So I'm fairly new to using SQL to insert rows... The statement above did not insert rows. Stupid question: Do I need to explicitly populate each and every field in the db file? Or is this possibly an issue with field types (i.e. populating a zoned 5,0 field with an unsigned integer)?

[Vectorspace]

It depends.

You don't need to specify every field if you are specifying a column list to insert into, which you are. However, any columns that you don't specify must either allow null, or have a default value to use in place of null.

The field types do not need to be identical, however they must be compatible. E.g. if your unsigned integer has a value of 15, that can be put into a zoned 5,0 because 15 is less than 5 digits. But if the unsigned integer has a value of 12345678, then that is too big for zoned 5,0 so the statement will fail.

If the insert statement is failing, it should be providing error information. Run it in debug, and check the value of SQLSTT, SQLCOD, and SQLERM. Compare against the documentation: https://www.ibm.com/support/knowledg...alakickoff.htm
Note that SQLCOD can be transformed into an iSeries Message ID (e.g. SQL Code -311 is message ID SQL0311, SQL Code 20365 is message ID SQ20365), and SQLERM is the substitution values for that message. If the error related to a particular column or host variable name, that name should be in SQLERM

[gwilburn]

Great information! Not sure what you mean by "or have a default value to use in place of null"
This is a DDS described file created many years ago... pretty sure that no fields allow null. However, when I use RPG to write records characters default to blank, zoned default to zero, etc.

Unfortunately this had to be running today... so I went back to native i/o to write the records. I will likely revisit this at a later when i have time to debug.

[B.Hauser]

So I'm fairly new to using SQL to insert rows... The statement above did not insert rows. Stupid question: Do I need to explicitly populate each and every field in the db file? Or is this possibly an issue with field types (i.e. populating a zoned 5,0 field with an unsigned integer)?

Two Questions
1. What exactly is the SQLCODE immediately after the INSERT Statement?
2. Did you compile your program with the COMMIT option with set *NONE (or did you just use the defautl value)?

I assume your tables/files are not registered in an journal. If so, an insert, update or delete performed under commitment control will not work.
The default option for COMMIT in the CRTSQLRPGI command is *CHG, i.e. all INSERT, UPDATE and DELETE statement are executed under commitment control.
To get rows inserted, updated or deleted in Tables/Files that are not registered in an journal, you either have to set the compile option COMMIT to *NONE or (even better) include an SQL SET OPTION Statement in your program (before all other SQL Commands in your source) with COMMIT = *NONE (EXEC SQL SET OPTION COMMIT=*NONE).

Birgitta

[Vectorspace]

When you define an SQL Table, you can specify that a column should allow null, or use a default value in place of null. When you do an SQL insert and do not specify all the columns, the unspecified columns get null or that default value. I'm afraid I don't know what happens with DDS files in this situation

But Birgitta to the rescue! Her idea about commitment control is the most likely cause.
Birgitta has shown you two methods to disable commitment control for the whole program. To add to that, you can also disable it for an individual SQL statement by ending it with "WITH *NC" in case there are other SQL statements in the program that do need commitment control.

[Vectorspace]

Typo - the statement should be ended with "WITH NC" (not *NC)

[wegrace]

Regarding what VectorSpace noted about transforming an sqlCode into an as400 message id, I created the code below many years ago based on an IBM manual.

// The method that follows for deriving the SQL Error Id came from page 1 of
// the IBM Manual, 'DB2 Universal Database for iSeries SQL Messages and Codes'

if (sqlCode <> *ZERO);

Msg.Id = 'SQ' + %trim(%editw(%abs(sqlCode) :' 0 '));

if %subst(Msg.Id :3 :1) = '0';

%subst(Msg.Id :3 :1) = 'L';
endif;

The text for the resulting Message Id can be found in message file qSqlMsg.

Walt

[gwilburn]

I make it a practice to have the following in all my SQLRPGLE programs (nearly all of the db files are DDS)
Exec Sql Set Option Commit = *none;

I will definitely try the WITH NC - I'm assuming that is only needed on statements doing an UPDATE INSERT or DELETE.

[Vectorspace]

I make it a practice to have the following in all my SQLRPGLE programs (nearly all of the db files are DDS)
Exec Sql Set Option Commit = *none;

I will definitely try the WITH NC - I'm assuming that is only needed on statements doing an UPDATE INSERT or DELETE.

We have that same practice.

It's not always true that WITH NC is only for UPDATE, INSERT. or DELETE. Some of the commit options will affect an SQL Cursor position. I'm afraid I don't know more than that though, I am not well versed in this.

[B.Hauser]

Regarding what VectorSpace noted about transforming an sqlCode into an as400 message id, I created the code below many years ago based on an IBM manual.

// The method that follows for deriving the SQL Error Id came from page 1 of
// the IBM Manual, 'DB2 Universal Database for iSeries SQL Messages and Codes'

if (sqlCode <> *ZERO);

Msg.Id = 'SQ' + %trim(%editw(%abs(sqlCode) :' 0 '));

if %subst(Msg.Id :3 :1) = '0';

%subst(Msg.Id :3 :1) = 'L';
endif;

The text for the resulting Message Id can be found in message file qSqlMsg.

Walt

There are easier methods for determining the Message-Id and Message Text.
Both Information can be directly determined by executing a GET DIAGNOSTICS Statement immediately after the SQL-Statement in case.

Exec SQL Get Diagnostics Condition 1
          :YourMessageId = DB2_MESSAGE_ID,
          :YourMessageText = MESSAGE_TEXT;

Birgitta

[wegrace]

Thanks for the info Birgitta!

I wasn't aware of that 20 years ago when I wrote that code.

How do you get the detailed message information which appears in the qSqlMsg message file?

Walt