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

[<SPoger@xxxxxxxxxxxxxxxxxx>]

Jason- Your DDS looks fine. It's the same syntax as my system.

Here is the Java code I use to get the column descriptions:

DatabaseMetaData dbmeta = con.getMetaData();
ResultSet colrs = dbmeta.getColumns(null, libname_str, tablename_str,
null);
while( colrs.next()){
                                
                String col_name =   colrs.getString("COLUMN_NAME"); 
                String col_remarks =  colrs.getString("REMARKS"));
                short col_datatype = colrs.getShort("DATA_TYPE");
int col_size = colrs.getInt("COLUMN_SIZE");
int col_digits= colrs.getInt("DECIMAL_DIGITS");
int col_ordpos = colrs.getInt("ORDINAL_POSITION");

        }


I didn't have to change the JDBC connection properties for this to work.
What I did is probably overkill for most applications, but I needed to
get the other info about the column anyhow.

I have tested 2 DB2 drivers. The one that ships with the Windows version
of DB2. This version I have only used for testing inside WebSphere
Studio. The other one is the one built into OS/400. The driver class
name is "com.ibm.db2.jdbc.app.DB2Driver" The connection url is
"jdbc:db2:*LOCAL"  I have used this in production when the Java
application/webapp is running on the iSeries.  

The DB2 driver that is built into OS/400 is not depreciated. In fact its
what IBM recommends using when both the Java app and the database are on
the same physical iSeries machine. The url that you were looking at was
specific to using JDBC from a Java applet. I have not used JDBC from an
applet due to security/network issues. My JDBC has always run in
production in either a servlet or a Java application.


Hope this helps,
Sarah


> -----Original Message-----
> From: Jason Palmatier [mailto:cobraconn@xxxxxxxxx] 
> Sent: Thursday, January 20, 2005 5:15 PM
> To: Websphere Development Studio Client for iSeries
> Subject: RE: [WDSCI-L] AS400JDBCDriver does not return Column 
> Label(i.e.iSeries Column Header)?
> 
> 
> 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?top
ic=/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
> > 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.
> > 
> 
> 
> 
>               
> __________________________________ 
> Do you Yahoo!? 
> The all-new My Yahoo! - Get yours free! 
> 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.
>