UPDATE RE: AS400JDBCDriver does not return Column Label (i.e.iSeries Column Header)? -- WDSCI-L

I found a reference to my problem in an iSeries
support forum:

http://www-912.ibm.com/j_dir/JTOpen.nsf/2bd4742db2e03c7b862568230070dbd3/05d8393b0a07369886256f8c004edf89?OpenDocument

It turns out that to get the column label you have to
set properties while requesting the connection from
the JDBCDriver.  I've listed the code changes I made
to retrieve the "extended metadata" below.  The only
bad thing is that it will only work for OS/400 v5r2 or
higher.  That kinda stinks.

-------------------------------------------

// Load the IBM Toolbox for Java JDBC driver.
AS400JDBCDriver jdbcDriver = new AS400JDBCDriver();

Properties jdbcProps = new Properties();
jdbcProps.setProperty("extended metadata", "true");

DriverManager.registerDriver(jdbcDriver);

AS400 sysObj = new AS400(system, "myUserName",
"myPassword");

connection = jdbcDriver.connect(sysObj, jdbcProps,
null);

--------------------------------------------

If anyone has any other ideas about how to retrieve
the column labels in a way that is supported back to
at least v5r1 I'd like to hear about it.  As it is the
above solution won't really work for me.  Thanks for
the help so far, though.

- Jason

--- Jason Palmatier <cobraconn@xxxxxxxxx> wrote:

> Thanks for the reply Sarah!
> 
> I read somewhere that that the DB2 JDBC driver was
> deprecated (actually it was at
>
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0007044.htm).
>  Is this true in all cases or is there a new version
> of the DB2 JDBC driver that can be downloaded?  A
> co-worker tried creating a table from DDS members
> but
> the JT Open driver still returns COLUMN_NAME for
> resultSetMetaData.getColumnLabel(colIndex).  The
> code
> used to create the table (as a physical file) is
> below:
> 
> A          R TEST2R                    TEXT('Test
> DDS-created colhdg')  
> A*                                                  
>  
>                  
> A            FIELD1         1         
> COLHDG('Field'
> 'One')            
> A            FIELD2        10         
> COLHDG('Field'
> 'Two')            
> A            FIELD3          Z        
> COLHDG('Timestamp' 'field')      
> 
> Does this look correct or are there other steps we
> need to take to set the column headings?  Thanks
> again
> for the reply.
> 
> - Jason 
> 
> 
> --- SPoger@xxxxxxxxxxxxxxxxxx wrote:
> 
> > You may want to use the DB2 JDBC driver for the
> > As/400. I have noticed
> > that when I use the JT Open or jt400 driver, I
> only
> > get the column
> > descriptions on tables created from DDS members. 
> > 
> > When I use the DB2 driver, I get the column
> > descriptions for tables
> > created via SQL. ( ie CREATE TABLE syntax.) 
> > 
> > I have never been able to get the column
> desciptions
> > from both types of
> > tables from a single driver.
> > 
> > -Sarah 
> > 
> >  
> > 
> > > -----Original Message-----
> > > From: Jason Palmatier
> [mailto:cobraconn@xxxxxxxxx]
> > 
> > > Sent: Thursday, January 20, 2005 1:31 PM
> > > To: wdsci-l@xxxxxxxxxxxx
> > > Subject: [WDSCI-L] AS400JDBCDriver does not
> return
> > Column 
> > > Label (i.e.iSeries Column Header)?
> > > 
> > > 
> > >    I have created a sample table on a v5r2
> iSeries
> > and
> > > used the LABEL ON statement to set the column
> > header
> > > (i.e. Column Label to Java's ResultSet object)
> to
> > a
> > > human readable string.  If I query the MetaData
> on
> > the
> > > iSeries from STRSQL the labels for each column
> > appear
> > > to be set correctly. 
> > >    However, if I pull the table contents into a
> > > ResultSet via the JT Open 4.6 AS400JDBCDriver
> and
> > then
> > > get the MetaData for the result set I still end
> up
> > > with the COLUMN_NAME when I do 
> > > resultSetMetaData.getColumnLabel(colIndex).  The
> > label added 
> > > via LABEL ON does not seem to be honored by the
> > JDBC Driver.  
> > > Has anyone run into this before, and if so, is
> > there a way to 
> > > force the use of the column header so it will be
> > returned via the
> > > ResultSetMetaData.getColumnLabel(colIndex) call?
> 
> > I've
> > > placed the table creation SQL statements and the
> > code
> > > used to connect and retrieve the table data
> below:
> > > 
> > > DROP TABLE sampleTable;
> > > 
> > > CREATE TABLE QGPL/sampleTable
> > > (
> > >    COLUMN1    char( 20) not null with default ,
> > >    COLUMN2    char( 20) not null with default ,
> > >    COLUMN3    char( 20) not null with default 
> > > );
> > > 
> > > LABEL ON COLUMN sampleTable
> > > (
> > >    COLUMN1    is 'Column One'
> > >    COLUMN2    is 'Column Two'
> > >    COLUMN3    is 'Column Three'
> > > );
> > > 
> > > I then connect to the iSeries and pull the data
> as
> > > follows:
> > > 
> > > ----------------------------------------------
> > > 
> > > DriverManager.registerDriver(new 
> > > com.ibm.as400.access.AS400JDBCDriver());
> > > 
> > > Connection connection =
> > DriverManager.getConnection 
> > > ("jdbc:as400://" + system);
> > > 
> > > DatabaseMetaData dmd = connection.getMetaData();
> > > 
> > > Statement select = connection.createStatement();
> > > 
> > > ResultSet rs = select.executeQuery ("SELECT *
> FROM
> > > QGPL" + dmd.getCatalogSeparator() +
> > "sampleTable");
> > > 
> > > ResultSetMetaData rsmd = rs.getMetaData();
> > > 
> > > for(int j=1; j<=rsmd.getColumnCount(); j++)
> > > {
> > >    System.out.println(rsmd.getColumnLabel(j));
> > > }
> > > 
> > > ----------------------------------------------
> > > 
> > > When printing the column labels I expect to get:
> > > 
> > > Column One
> > > Column Two 
> > > Column Three
> > > 
> > > but instead get:
> > > 
> > > COLUMN1
> > > COLUMN2
> > > COLUMN3
> > > 
> > > Is there any way to fix this?
> > > 
> > > - Jason
> > > 
> > > 
> > > 
> > >           
> > > __________________________________ 
> > > Do you Yahoo!? 
> > > Meet the all-new My Yahoo! - Try it today! 
> > > http://my.yahoo.com 
> > >  
> > > 
> > > -- 
> > > This is the Websphere Development Studio Client
> > for iSeries  
> > > (WDSCI-L) mailing list To post a message email: 
> > > WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe,
> or
> > change 
> > > list options,
> > > visit:
> > http://lists.midrange.com/mailman/listinfo/wdsci-l
> > > or email: WDSCI-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review
> the
> > archives
> > > at http://archive.midrange.com/wdsci-l.
> > > 
> > 
> > -- 
> > This is the Websphere Development Studio Client
> for
> > iSeries  (WDSCI-L) mailing list
> 
=== message truncated ===


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com