SQL Metadata info

[HMiller]

Hi,
I would like to know if there is a way to retrieve metadata info (column name, type , precision.... ) from a static SQL in order to use them inside a RPGLE program.
Column names are not related to SYSNAMES or any other DB2 column definition.
I attach an example where all columns are not coming from a Db2 table.

select 'Potato' SkuCode, 100.00 Price , 2.50 Quantity , 100.00 * 2.50 Total from dummy;

Best regards,
Giovanni


Screenshot.png

[B.Hauser]

Create an SQL View based on the SELECT-Statement and then retrieve the column information from SysColumns.

[HMiller]

Thank you.
that's an option but unfortunately I cannot use it in many cases due to performance issues.
In RpgLe programs I had to use variables in Sql JOIN statement and not in Sql Where condition for performance reasons.
In a static view J cannot use that option or I have to create the Sql view on the fly each time I run the program.
Here is an example

select mabyear,mabweek, mabfabb,
mabdcod_padre,mabdcod_pcl,
mabforn,forn.aadsog,
mabcatg,art.aadsog,
customer,
id_edisev,id_bcdet
from pronw00t w
left join prdla00t d on w.id_prdla= d.id
left join ansog01l forn on forn.aacazi = :http_stdin.azienda and
forn.aacsog = mabforn
left join ansog01l art on art.aacazi = :http_stdin.azienda and
art.aacsog = mabcatg
where mabdcod = :http_stdin.articolo

Giovanni

[B.Hauser]

Not sure what you really want!
... but you can create and use SQL Global Variables within Views. You only have to set them before executing the view.
... and a view is never a performance killer (because it is just a saved SELECT statement) the performance killer are the syntax you use in your SELECT statement, i.e. you have multiple ways writing a SELECT statement which return the same result, but in some cases the optimizer can use an index and in other cases not. The second performance killer is missing access paths (indexes or keyed logical files) without the right access path table scans must be performed.

I assume what you want is to write a completely dynamic SQL and then handle the data over a cursor.
If the columns in your SELECT statement change, then you have to deal with a SQL descriptor area (SQLDA), which returns pointers to your column data and also information about the column type and data length.
https://www.ibm.com/docs/en/i/7.4?topic=programming-coding-sql-statements-in-ile-rpg-applications

Documentation is not very good.

[HMiller]

Hi B.Hauser,

thank you for your reply.
I'm implementing a RPGLE service program that generates a JSON file to be handled by a web application . The web application needs to know how to format each single column in a grid( equivalent to IBM I subfile).
This is the actual implementation, which is really ugly because for each column I have to define Name, Label, size,Type.
I would like to get all these info from SQL METADATA

// Ag-GRID Column Headings
http_stdout.columnHeadings(1).name = 'Anno';
http_stdout.columnHeadings(1).label = 'Anno';
http_stdout.columnHeadings(1).size = 100;
http_stdout.columnHeadings(1).type = 'Integer';
http_stdout.columnHeadings(2).name = 'Settimana';
http_stdout.columnHeadings(2).label = 'Settimana';
http_stdout.columnHeadings(2).size = 100;
http_stdout.columnHeadings(2).type = 'Integer';
http_stdout.columnHeadings(3).name = 'Qta';
http_stdout.columnHeadings(3).label = 'Quantita';
http_stdout.columnHeadings(3).size = 100;
http_stdout.columnHeadings(3).type = 'Decimal(3)';
....
exec sql declare crsr cursor for
select mabyear anno ,mabweek settimana, mabfabb qta
from pronw00t w;

I will try to create a VIEW in qtemp library and then get info from Syscolumns and see if this way is affecting performances.
I will have a look at dynamic SQL as well .