RE: SQL Update different on v7r3? on behalf linked_User

Hi Thomas

@Linked_User
A name in a For Loop is only valid within this loop.
Any name (variable, cursor etc.) must be unique within the same source. This is documented
May be this was not checked in Release V5R4, got compiled and worked (accidentally). In a later release uniqueness is checked and now compilation fails.

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!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Thomas Garvey
Sent: Mittwoch, 9. Januar 2019 17:09
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Update different on v7r3?

Yes, I was already using %trim and it worked fine on v5r4.
I've tried making the variables VARYING and the eval statement that creates the SQL statement fails now whether I use %trim or not.

I think I'm going to have to NOT use SQL Update and read each member of the source member and use RPG to replace the string value.

While I have you, Birgitta, may I ask another SQL question?
I know you're the SQL expert on this forum.

I have another SQL statement (in a create procedure command that fails on v7r3 but works fine on v5r4...

For Linked_User as
C1 cursor for
select XTUSER from BZXT
where XTTMPL = UserID
and XTID = 'XT'
Do
/* update their authorities to match this Template change... */
call ZX2_Authority(
Linked_User.XTUSER,
ProductCode ,
AuthSetting ,
CallingProgram ,
UserID);
End For;

The code is fine, but later in the same procedure I re-use the same block of code.
On v5r4 the RUNSQLSTM works fine. On v7r3 it fails saying the Linked_User reference must be unique.

I know how to fix this one but, do you know of a document that lists the differences I will be running into in all my SQL work that is being moved to v7r3?

Best Regards,

Thomas Garvey


On 1/9/2019 12:21 AM, Birgitta Hauser wrote:

Are you sure the replacement is 'Tom' and not 'Tom ' (because you used a fixed length variable)?
Try to put a TRIM around 'Tom'.

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!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Thomas
Garvey
Sent: Mittwoch, 9. Januar 2019 00:22
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Update different on v7r3?

No, it doesn't fail, but it does not do the replace in the same way.
That is, it does not disregard the lengths of the old and new values when doing the replace.

So, for example, if I have
/Drop Procedure Library/ProcedureName;// /in the source member.
Executing the update with replace so that /Library/ might be replaced with Tom will result in...
/Drop Procedure Tom /ProcedureName;/

While on v5r4 the same SQL execution results in...
/Drop Procedure Tom/ProcedureName;/

When a RUNSQLSTM is executed against the one with the embedded blanks will fail.

I'm open to suggestions.

Best Regards,

Thomas Garvey

On 1/8/2019 4:43 PM, Vernon Hamberg wrote:

Hi Thomas

It's not a 7.3 change - it was done sometime after v5r4 - the thing
now is, SQL assumes that literal values are VARCHAR, hence the
mismatch message.

Does it cause the UPDATE to fail? I would not think so, it's just a
nuisance, I hope.

HTH
Vern

On 1/8/2019 3:56 PM, Thomas Garvey wrote:

Hi,

I've got a program that is used to replace a particular value found
in source members with another value.
It uses the SQL update command, as follows...
update QSQLSRC set srcdta=replace(srcdta,'oldvalue','newvalue')

This works fine on an old v5r4 box and the oldvalue and newvalue can
have different lengths with no complaints from SQL.
On a v7r3 box I get an SQL7939 message, saying 'Data conversion
required on INSERT or UPDATE'.
The reason code is 3 (Reason 3 is that 'the INSERT or UPDATE value
is a variable length string and column SRCDTA is not').

I can't see any difference in the files between the systems. In
fact, the file on v7r3 is just a transferred copy of the file from v5r4.

What am I doing wrong, or what am I ignorant of here?

Best Regards,


Thomas Garvey

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx 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@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com