Re: SQL issue with CCSID 65535

The best workaround, in my mind, would be to handle this situation without
changing the job environment. It requires a little bit more code in your
program, but should be nice and safe to use in various client
environments. Assuming you're using RPG:

ctl-opt dftactgrp(*no);
dcl-s c_column_name char(10);
dcl-s c_name char(10);
dcl-s c_schema char(10);
dcl-s column_name varucs2(128) ccsid(1200);
dcl-s name varucs2(128) ccsid(1200);
dcl-s name_start_position int(10);
dcl-s schema varucs2(128) ccsid(1200);
exec sql
with data as
(select coalesce(name_type, ' ') as name_type,
coalesce(name, ' ') as name,
coalesce(schema, ' ') as schema,
coalesce(column_name, ' ') as column_name,
name_start_position
from table(qsys2.parse_statement('select ds1.monthno +
from divsales ds1 +
where ds1.monthno = 1 +
order by ds1.divno',
'*SYS','*PERIOD','*APOSTSQL'
)
)
)
select name, schema, column_name, name_start_position
into :name, :schema, :column_name, :name_start_position
from data
where name_start_position < (select min(name_start_position)
from data
where name_type = 'TABLE')
order by name_start_position;
c_name = name;
c_schema = schema;
c_column_name = column_name;
*inlr = *on;
return;

The program, as written, only accesses the first row returned by
parse_statement but I'm confident you are familiar with how to retrieve all
rows given your original program. I tried to remain as faithful as I could
to your original and basically:

Declared character variables to match what you wanted via casting
(prefixing the names with c_)
Declared ucs2 variable length variables to match what's
returned/documented by parse_statement
Retrieved the parse_statement columns into the ucs2 variables
Set the character variables to the values of the ucs2 variables

So when the program runs:
c_name = 'DIVSALES'
c_schema = ' '
c_column_name = 'MONTHNO'
name_start_position = 12

If you really wanted to play it safe you could define the c_* variables
with CCSID(37). Otherwise the RPG runtime will just go with the default
job CCSID when the job CCSID is 65535. Note that it is a very conscious
decision by database to NOT use the default job CCSID in this type of
situation. 65535 means 65535!

I would be hesitant to change the job CCSID to a non-65535 value. While
this is safe for the majority of user application environments, majority
does not equal all... For instance let's assume your program is
interactive in nature and you present a panel/screen/whatever. If the user
happens to have also set an attention key handler (SETATNPGM) and you've
changed the job CCSID from 65535 to 37, guess what CCSID is active when the
attention program gets activated? No error to trap, just a user
action/request.

Bruce





On Sat, Dec 14, 2019 at 3:56 PM Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

All is true and I think, IRC, that the OP said this program would be
running on other systems - maybe it's a software vendor's product.

So I had responded that he could save the existing job CCSID, set the
job CCSID to some non-HEX CCSID (65535), then restore the saved one. He
was concerned that something would happen to the job before the CCSID
could be changed back, I responded that there are exit and other error
that might take care of that concern.

Anyway, this is my attempt to bring the thread back to mid-stream. :)

Vern

On 12/14/2019 2:37 PM, David Gibbs via RPG400-L wrote:

On Sat, Dec 14, 2019 at 8:41 AM <smith5646midrange@xxxxxxxxx> wrote:

Care to elaborate on that answer?

CCSID 65535 means no translation ... characters in other CCSIDs will
not be translated into the target CCSID.

If you are working with US english data, you should use CCSID 37 so it
can be translated to and from 1200.

In the multi-national, unicode enabled, world ... it's probably a good
idea to have your jobs running in the appropriate CCSID for your
locale.

david

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-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

--
Thanks and Regards,
Bruce
931-505-1915