Re: How to get "System Name" for columns in a cursor when using SQL-CLI

Thank you so much gentlemen!! What a wonderful group of dedicated and
helpful individuals you all are :)

One thing that is not clear in my "cry for help" is that I need a solution
that works dynamically for* all SQL cursors* at *runtime* in the* entire
system*.

@Mark

Your solution ..

CREATE TABLE tablename (
longColumnName FOR ShortName ...;
...
);

is actually the problem. If you look at my examples (and leave out the ID
column) mine and yours code are identical (or at least similar)...

CREATE OR REPLACE TABLE mytable (
Active_Job_Name FOR MYACTJOB char(26)
) RCDFMT mytableR;

and the problem is to get your "ShortName" from SQL-CLI.

so @Vern your idea by creating yet another table, will unfortunately only
lead to lots of copy of data into temporary tables.

@Rob - The idea by query the syscolumns and get the "system column name"
could perhaps work since:
SQL_DESC_BASE_COLUMN
SQL_DESC_BASE_SCHEMA
SQL_DESC_BASE_TABLE

are accessible from "SQLColAttribute". However IMHO this is more a hack
than a solution - It will lead to huge overhead to translate each column
name this what for all cursors in the system.

@Mark

Back to your QSYS2.GENERATE_SQL - this is the feature that has been used to
convert the database in the first place - giving me the headache.

Gentlemen:

The problem is in noxDb project:
https://github.com/sitemule/noxDB

Perhaps a use case will help ( I'll use @Mark - your SQL table as example)
:

**free
Ctl-Opt BndDir('NOXDB') dftactgrp(*NO) ACTGRP('QILE');
/include qrpgleRef,noxdb
Dcl-S pRows Pointer;

pRows = json_sqlResultSet(
'select * from tablename'
1:
JSON_ALLROWS:
JSON_META + JSON_FIELDS + JSON_SYSTEM_NAMES
);


BEFORE conversion of the database to SQL it returned

{
rows: [
{
"shortname" : "Some value"
}
]
}

NOW after the conversion of the database to SQL:

{
rows: [
{
"longcolumnname" : "Some value"
}
]
}

... and I have no clue where to get the "shortname" in the dynamic cursor
with SQL-CLI since the DSPFFD shows the same on the file before and the
table after. The only difference is that the file type is now TB and not PF
in the SYSTABLES.

More examples are found here:

https://github.com/sitemule/noxDB/blob/master/examples/

perhaps this is the most clean:
https://github.com/sitemule/noxDB/blob/master/examples/JSONSQL06B.SQL-resultset-object.rpgle


Again - Thank you so much for all your help and support !! and more help
will be welcome :)






On Wed, Aug 4, 2021 at 6:10 PM Mark Waterbury <
mark.s.waterbury@xxxxxxxxxxxxx> wrote:

Niels,

I think you are asking about the SQL-generated "short column name" aka.
"System name", vs. the long column name?

In DDS, we could use ALIAS to define a long fieldname as an "alias" for
the short names.

In Db2i SQL, you can do the same thing like this:

CREATE TABLE tablename (
longColumnName FOR ShortName ...;
...
);

Hope that helps?

All the best,

Mark S. Waterbury

On Wednesday, August 4, 2021, 08:52:55 AM EDT, Niels Liisberg <

nli@xxxxxxxxxxxxxxxxx> wrote:





I am in a situation where the DDS database is now converted into a SQL
database so each table is now created like this:

CREATE OR REPLACE TABLE mytable (
id FOR COLUMN TFROWID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
PRIMARY KEY,
Active_Job_Name FOR MYACTJOB char(26)
) RCDFMT mytableR;

However all the access to the new tables need to know the original column
name so the code will not have to be rewritten.

The question is - how do I get the "FOR" system name ( here MYACTJOB) ?

The "SQLDescribeCol" gives the new SQL name, so instead I was trying to use
the "SQLColAttribute" function but I don't find the option among the
features listed:

SQL_DESC_AUTO_INCREMENT INTEGER
SQL_DESC_BASE_COLUMN
SQL_DESC_BASE_SCHEMA
SQL_DESC_BASE_TABLE
SQL_DESC_COLUMN_CCSID
SQL_DESC_COUNT
SQL_DESC_DISPLAY_SIZE
SQL_DESC_LABEL
SQL_DESC_LENGTH
SQL_DESC_NAME
SQL_DESC_NULLABLE
SQL_DESC_PRECISION
SQL_DESC_SCALE

Perhaps I am using the wrong function?

Any help will be appreciated. Thank you.
--
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
--
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