RE: ACS table creation using column headings in place of column names

... no other language needed!, This could all be done on AS/400 in RPG - using embedded SQL, if you are using (Opaen Source) ArdGate.


<Coy>

Not really. ACS is using jt400.jar (a.k.a. IBM Toolbox for Java) to retrieve the data using the JDBC driver (yes ODBC and JDBC are both based on the SQL99 CLI interface but they are two separate and distinct technologies.) Behind the scenes when you select that Option pane checkbox, ACS is probably turning on the JDBC driver property "extended metadata" so that it can retrieve the column label and use that as the header. You could write your own Java program or Groovy script to do the same and also create the new table in MS SQL using the JTDS JDBC driver with field/column names based on the IBM i column labels with spaces converted to underscores. But that requires you writing code in a language you may or may not be familiar with. You might best be served by just retrieving he necessary metadata yourself and creating an MS SQL table that you can then just execute an insert against so that the names of the fields are irrelevant and only the types are relevant. Does that makes sense?

Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of a4g atl
Sent: 2017 October 27 12:58
To: midrange-l@xxxxxxxxxxxx
Subject: ACS table creation using column headings in place of column names
Importance: Low

I have a need to copy data via the ODBC drivers (any) from the IBM i to the PC/MS SQL. The output file column names should be the text description (underscores replace blanks).

Using ACS I am displaying data on the screen and it displays the column's text description and not the column name. This is a ODBC setup option in the OTHER tab.

Is there a way to use the new ACS ODBC connection in place of the iAccess'
ODBC?

If there is, I may be able to create tables in MS SQL using the column text and not the column name.

An alternative would be to use the ALIAS names but not all files have the ALIAS name defined.

TIA

Darryl Freinkel.
--