Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using metadata to get COLUMN_SIZE

Status
Not open for further replies.
adrianjohnson

adrianjohnson

Programmer
May 16, 2002
145
GB
I've got some code which displays a list of database tables in a tree, and when a node is selected, the fields are listed underneath the table name.

I'd like to add the data type and size to the list of fields, so that it would read: field type size

Here's my code:

Code: 
// Get all the columns for the current table
ResultSet columnNames = metadata.getColumns(null, null, tableName, null);

// Add nodes for the columns as children of the table node
while(columnNames.next())
{
  colName = columnNames.getString("COLUMN_NAME");
  colType = columnNames.getString("DATA_TYPE");
  colSize = columnNames.getInt("COLUMN_SIZE");
  colData = colName + " " + colType + " " + colSize;
  tableNode.add(new DefaultMutableTreeNode(colData));
}

Variables colName, colType and colData are declared as strings, whereas colSize is an int.

When I run the code I get a "Column not found." If I remove all references to colSize (and COLUMN_SIZE) then it works and I get a list of tables.

Where am I going wrong?

(If I need to provide further code, let me know).

Thanks,

Adrian Johnson

I'm using Eclipse 3.1, with JRE 5.0 as my default, but also have 1.4.2 installed.

Adrian Johnson
Assystance - i.t. solutions
 
Sort by date Sort by votes
I knew an Adrian Johnson at Warwick Uni. You're not him are you?

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy
 
Upvote 0 Downvote
... and your code looks okay to me. What JDBC Driver are you using?

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy
 
Upvote 0 Downvote
And can you post the output from a "desc table_name" in your db too ...

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software
 
Upvote 0 Downvote
JDBC driver:

Code: 
sun.jdbc.odbc.JdbcOdbcDriver

Sorry, sedj, I didn't understand your comment. I've just tried this project in VB.NET, so my head's spinning.


Thanks,

Adrian

Adrian Johnson
Assystance - i.t. solutions
 
Upvote 0 Downvote
Post the table structure (ie the names of the table's fields - in many db's its "desc table_name").

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software
 
Upvote 0 Downvote
This is the SQL to create the first table in the list:

Code: 
CREATE TABLE A_BEXT
(
COMP_NO Varchar 8,
PSTK Varchar 25,
LOWER Double 8,
SELL_PR Double 8,
CUST_REF Varchar 25,
UPPER Double 8,
SH_CODE Varchar 10
);

There are loads of tables, some with 000's of records; and there are two databases (I'm working on a project to try and sort/merge the data - this is the first stage).

I'm not sure if the code above is what you need/asked for, but hope it helps.

Thanks,

Adrian

Adrian Johnson
Assystance - i.t. solutions
 
Upvote 0 Downvote
Well there is no field in that table called "COLUMN_NAME" (or any of the three field names you posted in your Java code) - so of course it won't be able to find a field called "COLUMN_NAME".

If you did something like :

resultSet.getString("COMP_NO");

then it should work.





--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software
 
Upvote 0 Downvote
It retrieves the column name ok (and the type, although that is returned as a number) - it's the size it doesn't get, even though in Ivor Horton's book it suggests using COLUMN_SIZE to get the size of the column.


Adrian Johnson
Assystance - i.t. solutions
 
Upvote 0 Downvote
sedj, the metadata resultset should contain these fields. There will be a record for each field of the specified table. Check out the getColumns method of the java.sql.DatabaseMetaData class.

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy
 
Upvote 0 Downvote
Sorry, wrong end of stick ... I thought you were talking about a straight ResultSet-from-query type set :_)

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software
 
Upvote 0 Downvote
No probs.

Any ideas why my code doesn't work?? (Or is there another way to get data types and sizes of fields in tables?)

Thanks,

Adrian

Adrian Johnson
Assystance - i.t. solutions
 
Upvote 0 Downvote
Adrian, is there any other JDBC Driver you could use? The Sun ODBC-JDBC Bridge is really only for experimental use (Sun's own admission).

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy
 
Upvote 0 Downvote
I tried some code against a MySQL database and it works fine.
Code: 
...
DatabaseMetaData md = con.getMetaData();
      ResultSet rs = md.getColumns(null, null, "RESULT", null);
      while (rs.next()){
        System.out.println("Name=" + rs.getString("COLUMN_NAME") 
                           + " Type Name=" + rs.getString("TYPE_NAME") 
                           + " Size=" + rs.getInt("COLUMN_SIZE"));
      }
...

This gave me
Code: 
Name=RESULT_ID Type Name=bigint Size=20
Name=SCHEDULER_RUN_DATE Type Name=date Size=10
Name=SCHEDULER_RUN_TIME Type Name=time Size=8
... etc

which is correct for the database I tried.




Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy
 
Upvote 0 Downvote
Tim,

I'm unsure about other JDBC drivers (are there any?) as I thought Sun did it all, and IBM did other stuff.

I'm connecting to a dBASE database, an Informix one, and Access too!! Basically, the project is to read the dBASE and Infomix databases, get the schema and data out and put it into an Access DB for another firm to put into their DB. They said Access is the best DB for them to work with.

I thought my initial problem was getting the size of the fields in the DB, but if there's another way I'm all ears!

Thanks,

Adrian

Adrian Johnson
Assystance - i.t. solutions
 
Upvote 0 Downvote
Just some stupid thoguhts:

1.- Make sure you don't have a zero instead of a capital "o". I know it sounds bad, but I got stuck with a thing like that for days one time.

2.- Try getting the fields by number instead of by column name, i.e. getString(1)

Cheers,
Dian
 
Upvote 0 Downvote
Basically, you're better off accessing the database through a native JDBC driver than by going over a JDBC-ODBC bridge. (Many DB vendors provide their own JDBC Drivers, which are usually recommended). I think there are some JDBC drivers for MS Access but I don't know of any free ones.

To go with Dian's suspicions ... Have you tried completely deleting the offending line of code and retyping it carefully? You could may have a non-visible control code in there, or something.

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy
 
Upvote 0 Downvote
I comment out the code, and try different things. I also copied and pasted the code into NetBeans.

Do you think a re-type is in order?

Adrian Johnson
Assystance - i.t. solutions
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Makumbi256
  • Locked
  • Question
how can i convert this code to javascript
Replies
1
Views
379
xwb
xwb
hendrikbez
  • Locked
  • Question
Add currency to as value
Replies
1
Views
357
Rick C. Hodgin
Rick C. Hodgin
Rienes
  • Locked
  • Question
How to learn Java
Replies
3
Views
420
Rienes
Rienes
R-MacDonald
  • Locked
  • Question
How to order Software on Java?
Replies
3
Views
534
JacquelineReeser
JacquelineReeser
varocho
  • Locked
  • Question
Need to have gradle build file reference database-connection info within 'application.yml' file
Replies
1
Views
396
Diancecht
Diancecht

Part and Inventory Search

Sponsor

Back
Top