|
What happens if you replace:
'select * from tablename'
With
Select ...list each system column name here... from tablename
I'm still wondering if there is some option in compiling, SET OPTION, etc
which can set to using system column names vs the long names.
IDK if bumping this over to the RPG list would help.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Niels Liisberg
Sent: Thursday, August 5, 2021 6:16 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: How to get "System Name" for columns in a cursor when using
SQL-CLI
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.
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,use
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
the "SQLColAttribute" function but I don't find the option among thelist
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
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxxlist
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
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
--
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
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
