aspose file tools*
The moose likes Oracle/OAS and the fly likes searching for column name Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "searching for column name" Watch "searching for column name" New topic
Author

searching for column name

R Singh
Greenhorn

Joined: Mar 07, 2002
Posts: 4
I have an XML schema having elements to be matched with database table columns. I am using jdbc to find table and column information from a given databse.
How do I search tables/columns to dynamically match the element names with corresponding column names in tables. I understand that if it was a select command, one could use soundex function.
Thanks in advance. Please help ASAP.


Ramanand
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

At a high level, because I can't remember this kind of code at the top of my head,
But JDBC, as you said gives you the column names in the table. And using the XML parser classes you can get a list of the elements.
Since you said that the elements in the XML documents match the field names, why not just use the .equals() method of the String class.
Again I am simplifying things and your particular arrangement might not make it possible to do this the easy way.
Hope that helps
Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
R Singh
Greenhorn

Joined: Mar 07, 2002
Posts: 4
Thanks for a quick response. Let me restate my requirement.
I can get the element name from XML schema. However, I do need to find a matching column name in one of the tables in the database. For example, if the element name (from xml schema) is LastName, I need to find a column of a table with similar names (lname, last_name, etc.) Just like soundex may work in a select or update statement.
Any help will be greatly appreciated.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

OK, then with that it makes it a bit harder.
But if you are using Java1.4 you could use the Regular Expressions classes. Although Lastname - LName might make it tough to come up with just the right expression.
Now the best solution is if the database and XML matched exactly. can there be changes to either side?
Or do you know this information in advanced. Like you know which tables you want to get the XML data into. If so, then use XSL to transform the XML document to match the data scheme, and then you will have exact matches.
If you try to rely on Soundex, and keep the same unknowns and uncertainty, you can never guarantee that the matches you get are correct.
Mark
R Singh
Greenhorn

Joined: Mar 07, 2002
Posts: 4
Mark,
The situation I am facing with gives me only the knowledge of XML schema. The tool (I am working on) is supposed to scavange the client database and find the matching columns from all the tables it can get. Subsequently, it needs to form a query and execute it to get relevant data.
Thus, I will have no prior knowledge of tables or their column names.
I am using JDK1.4. However, as you rightly pointed out, the regular expression will also have tough time in matching "LastName" to "lname", for example. And, soundex search does not seem to always work properly either.
Any other alternative you can think of?
Regards,
- ramanand
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Punt?
I'd say you are doomed. The reason is that there are too many variables, and it will never work in all cases.
You have to make decisions on what you will allow and won't allow, meaning you cannot say "Work in all cases" because you will fail.
Now if you say, LastName and how Soundex works, or taking Last and Name and using Regex is the definition of a field match, then you can get away with that.
The only other alternative is to have the user tell which fields match up where.
Mark
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: searching for column name