RE: SQL Update different on v7r3?

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