• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

searching for column name

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Creativity is allowing yourself to make mistakes; art is knowing which ones to keep. Keep this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic