× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.