|
Sergio,
Good finding and your point is right. Here what matters is that if for
some reason the length of your parameter changes upon a file's record
definition, you would only have to recompile the program.
The problem here is that it seems like the precompiler is not reading the
file record's definitions up to where it is validating the prototypes of
the UpdFile procedure.
But I think this noise is more about the "test" DS presence.
Javier.
El mié, 28 dic 2022 a las 12:36, Sergio Luis Puentes-Valladares (<
spuentes@xxxxxxxxx>) escribió:
SQL Precompiler SQL0312 and SQL5011 message enhancementTechnology
*News*
*Abstract*
Description of the SQL0312 and SQL5011 reason code support
*Content*
You are in: IBM i Technology Updates
<https://www.ibm.com/support/pages/node/1119129/> > Db2 for i -
Updates <https://www.ibm.com/support/pages/node/1116645/> > Db2 for ilanguages
Functional Enhancements <https://www.ibm.com/support/pages/node/1115697>
SQLPrecompiler SQL0312 and SQL5011 message enhancement
One of the pain points for embedded SQL development within source
has been the fact that when the precompiler gives an error messagethat
indicating that a host variable is not valid for use within SQL, it does
not give any indication why. There are numerous reasons why a host
variable or host structure may not be usable within SQL and the reason
the precompiler is generating the message is often not obvious.the
Support was added to indicate a reason code on the SQL0312 message and
SQL5011 message. This support is available for all ILE precompilers.IBM i
In IBM i 7.2 and 7.3, the precompiler listing shows the English text “
Reason: <reason code description>” at the end of the message text. In
7.4, the message text is updated toChar(10);
[image: image.png]
According to what IBM indicates, Error SQL0312 is very ambiguous, so in
Releases 7.2, 7.3 and 7.4
the reason (reason) was added in this case 1
*1- No declaration for the variable exists, the declaration is not within
the current scope, or*
*the variable does not have an equivalent SQL Data*
**free
ctl-opt dftactgrp(*no);
exec sql set option commit = *none;
dcl-f tblstr disk usage(*input) keyed;
dcl-s tmp_count zoned(5:0) template;
dcl-ds test;
STRNUM;
STRNAM;
end-ds;
dcl-s message char(50);
dcl-s count like(tmp_count);
dcl-pr UpdFile like(tmp_count);
* *n like(STRNUM); *Change the like(Name Variable) by its
definition, example *STRUM CHAR(10); STRNAM CHAR(30);*
* *n like(STRNAM);
*
end-pr;
read RTBLSTR;
if not %eof;
message = '1-' + %char(STRNUM) + %trim(STRNAM);
dsply message ' ';
count = UpdFile(STRNUM:STRNAM);
message = 'select rows: ' + %char(count);
dsply message ' ';
endif;
*inlr = *on;
dcl-proc UpdFile;
dcl-pi *n like(tmp_count);
* pSTRNUM like(STRNUM); * Change the like(Name Variable) by its
definition, example P*STRUM CHAR(10); PSTRNAM CHAR(30);*
* pSTRNAM like(STRNAM);*
end-pi;
dcl-s count like(tmp_count) inz(0);
message = '4-' + %char(pSTRNUM) + %trim(pSTRNAM);
dsply message ' ';
exec sql
select count(*)
into :count
from tblstr
where STRNUM = :pSTRNUM and STRNAM = :pSTRNAM
;
return count;
end-proc;
This is my program and it works perfect
**free
ctl-opt dftactgrp(*no);
exec sql set option commit = *none;
dcl-f Orderfile disk usage(*input) keyed;
dcl-s tmp_count zoned(5:0) template;
dcl-ds test;
OrderNbr;
Customer;
end-ds;
dcl-s message char(50);
dcl-s count like(tmp_count);
*dcl-pr UpdFile like(tmp_count); pOrderNbr Char(10); pCustomer
Char(10);end-pr;*
read OrderFile;
if not %eof;
message = '1-' + %trim(OrderNbr) + '-' + %trim(Customer);
dsply message ' ';
count = UpdFile(OrderNbr:Customer);
message = 'select rows: ' + %char(count);
dsply message ' ';
endif;
*inlr = *on;
dcl-proc UpdFile;
* dcl-pi *n like(tmp_count); pOrderNbr Char(10); pCustomer
end-pi;*Char(10)
dcl-s count like(tmp_count) inz(0);
message = '4-' + %trim(pOrderNbr) + '-' + %trim(pCustomer);
dsply message ' ';
exec sql
select count(*)
into :count
from Orderfile
where ORDERNBR = :pOrderNbr and CUSTOMER = :pCustomer
;
return count;
end-proc;
*Note*
*Change the use like(Variable) to the variable definition example
num(10)*to
*File : OrderFile*
[image: image.png]
*Debug Program*
[image: image.png]
[image: image.png]
[image: image.png]
*Debug Result*
[image: 5f5666c1-2b26-4492-a1a3-29e66e84f990.png]
Rewards
Sergio L Puentes Valladares
Developer SR IBM i RPG/COBOL
El mié, 28 dic 2022 a las 13:48, Maria Lucia Stoppa (<mlstoppa@xxxxxxxxx
)
escribió:
These are the first 5 fields of TBLSTRchar(35).
A UNIQUE
A R RTBLSTR TEXT('Store Information')
A STRNUM R REFFLD(LOCATNP050 JDAREF )
A COLHDG('Store Number')
A EDTCDE(Z)
A STRNAM R REFFLD(CHRCTRA030 JDAREF )
A COLHDG('Store Name')
A STADD1 R REFFLD(ADDRESA035 JDAREF )
A COLHDG('Address Line 1')
A STADD2 R REFFLD(ADDRESA035 JDAREF )
A COLHDG('Address Line 2')
A STADD3 R REFFLD(ADDRESA035 JDAREF )
A COLHDG('Address Line 3')
As you can see, It's defined using DDS .
LOCATNP050 is packed(5:0), CHRCTRA030 is char(30), ADDRESA035 is
DS 'test' is needed because in the programs being converted from fixed
nofree format there are lots of KLISTs, which I am trying to convert intoDS
to use as %kds, and I think this may cause other problems: when a DS
contains a variable defined with the same name of a database field and
variablestype/length, the compiler takes only the length from the originaldatabase
field, not the type, so the compilation list shows 2 different
procedurewith the same name and length but different type (packed and zoned). I
think this is another critical point.
Anyway, even after removing the DS 'test' the problem with the
canstill persists.define
The only way I found to have the program compiling and working is to
a template record with prefixes, and use those prefixed fields as the
'like' argument everywhere in the source code.
Lucia
Il giorno mer 28 dic 2022 alle ore 16:55 Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx> ha scritto:
I will try your code as is. Can you give me your TBLSTR table'sstructure?
No contents, no problem, but just to replicate this with moreprecision. I
would populate it with some data. Also if it is a DDS PF or an SQLtable.
Why would you want structure "test" for? If you are not using it,
OPTIONyoumlstoppa@xxxxxxxxx
comment that out and try again?
Javier.
El mié, 28 dic 2022 a las 9:29, Maria Lucia Stoppa (<
the)
escribió:
This is my code:
**free
ctl-opt dftactgrp(*no);
exec sql set option commit = *none;
dcl-f tblstr disk usage(*input) keyed;
dcl-s tmp_count zoned(5:0) template;
dcl-ds test;
STRNUM;
STRNAM;
end-ds;
dcl-s message char(50);
dcl-s count like(tmp_count);
dcl-pr UpdFile like(tmp_count);
*n like(STRNUM);
*n like(STRNAM);
end-pr;
read RTBLSTR;
if not %eof;
message = '1-' + %char(STRNUM) + %trim(STRNAM);
dsply message ' ';
count = UpdFile(STRNUM:STRNAM);
message = 'select rows: ' + %char(count);
dsply message ' ';
endif;
*inlr = *on;
dcl-proc UpdFile;
dcl-pi *n like(tmp_count);
pSTRNUM like(STRNUM);
pSTRNAM like(STRNAM);
end-pi;
dcl-s count like(tmp_count) inz(0);
message = '4-' + %char(pSTRNUM) + %trim(pSTRNAM);
dsply message ' ';
exec sql
select count(*)
into :count
from tblstr
where STRNUM = :pSTRNUM and STRNAM = :pSTRNAM
;
return count;
end-proc;
When the SQL statement inside UpdFile procedure is commented, then
compiles and works (returning 0 of course).
When the SQL statement is active, then this is the error I got:
DIAGNOSTIC MESSAGES
3 exec sql set option commit = *none;
000300 12/28/22
44 where STRNUM = :pSTRNUM and STRNAM = :pSTRNAM
004400 12/28/22
5770ST1 V7R3M0 160422 Create SQL ILE RPG Object
TEST0009NO 12/28/22 07:24:46 Page 3
DIAGNOSTIC MESSAGES
MSG ID SEV RECORD TEXT
SQL5066 0 3 Precompile option COMMIT changed by SET
notstatement.
SQL0312 30 44 Position 21 Variable PSTRNUM not defined or
exists,usable.
Reason: No declaration for the variable
orthe
declaration is not within the current scope,
notthe
type.variable does not have an equivalent SQL data
SQL0312 30 44 Position 43 Variable PSTRNAM not defined or
exists,usable.
Reason: No declaration for the variable
orthe
declaration is not within the current scope,
<the
type.variable does not have an equivalent SQL data
Message Summary
Total Info Warning Error Severe Terminal
3 1 0 0 2 0
30 level severity errors found in source
49 Source records processed
Il giorno mer 28 dic 2022 alle ore 16:21 Raul Alberto Jager Weiler
haven'tprocedure.raul.jager@xxxxxxxxx> ha scritto:
I think SQL is global and the parameters are local to the
mlstoppa@xxxxxxxxx
El mié, 28 de dic. de 2022 08:19, Maria Lucia Stoppa <
escribió:
Hi all,
I am struggling with a subfield definition issue which I
executed.scope,packedunderstood
yet, and it's driving me crazy!
In a program, an update keyed physical file is defined with a
procedure,(5,
0)
key field.
The file definition is global, like this:
dcl-f tblstr disk usage(*input) keyed;
The program passes that key field plus a second field to a
x_STRNAMand
the procedure uses those fields in an embedded SQL, like this:
dcl-proc UpdFile;
dcl-pi *n zoned(5:0);
x_STRNUM like(STRNUM) value;
x_STRNAM like(STRNAM) value;
end-pi;
dcl-s count zoned(5:0) inz(0);
message = '4-' + %char(x_STRNUM) + %trim(x_STRNAM);
dsply message ' ';
exec sql
select count(*)
into :count
from tblstr
where STRNUM = :x_STRNUM and STRNAM = :x_STRNAM
;
return count;
end-proc;
Well, the program doesn't compile, because both x_STRNUM and
exists,are
not usable by the SQL statement:
Position 21 Variable X_STRNUM not defined or not usable.
Reason: No declaration for the variable
the
declaration is not within the current
ordata
the
variable does not have an equivalent SQL
andtype.
The same for x_STRNAM.
When the SQL 'where' clause is commented, the program compiles
it
works, meaning the 'dsply' operation code is correctly
fixedthepassed
I tried different options, like adding value to the parameters
can'tand
casting the rpg variables used within the SQL statement, but I
getdefine
rid of the issue.
The idea I get from all the tries is that file's fields used to
the
input parameters of the procedure are not recognized by SQL, so
variables inside the procedure are for some reason unusable.
What am I missing? Does any of you have any idea about it?
Any hint or suggestion is greatly appreciated as converting
subscriptionRPG
mailingprograms to free format includes many of these situation.
Thanks!
Lucia
--
Maria Lucia Stoppa
mlstoppa@xxxxxxxxx
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any
mailingmailingmailingrelatedaffiliate
questions.
Help support midrange.com by shopping at amazon.com with our
link: https://amazon.midrange.com--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelistrelated
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
questions.
Help support midrange.com by shopping at amazon.com with our
link: https://amazon.midrange.com
--
Maria Lucia Stoppa
mlstoppa@xxxxxxxxx
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelistrelated
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
questions.
Help support midrange.com by shopping at amazon.com with our
link: https://amazon.midrange.com--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelistrelated
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
questions.
Help support midrange.com by shopping at amazon.com with our
relatedlistlink: https://amazon.midrange.com
--
Maria Lucia Stoppa
mlstoppa@xxxxxxxxx
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
listquestions.--
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx--
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.