This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

searching for column name

 
R Singh
Greenhorn
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mark Spritzler
ranger
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
R Singh
Greenhorn
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic