• 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

Generic SQL and Websphere

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I've searched the forums for this and I can't seem to find an answer. I'm probably not asking the question correctly, so I'll try to explain it here. I put this in the Websphere forum because it seemed the most appropriate. Please feel free to move this to another forum if appropriate.

For the purposes of this question, assume that:
- we have a single server with three websphere server instances, running on ports 9080, 9081, and 9082. Those represent test, QA, and production.
- we have three different databases representing test, QA, and production.
- for each environment, such as QA, the tables are not all located in the same qualifier. For example, the QA environment there might be QA1.employee, QA2.account, etc...
- we are using Websphere 6.1 on a Windows server.

Now, when you write an SQL statement in your code, you want to be generic and say "Select * from employee", except that in one environment, it might be "TEST1.employee" and in another environment, it might be "QA1.employee".

How do you set up a datasource in Websphere so that you can write generic SQL? Currently, we have code that substitutes the proper qualifier based on looking up the host name you are running on. This isn't a great approach because if the application is moved to a different host, we have to recompile, retest, and redistribute. Granted its not a huge change, but it's still a change.

If anyone has a link they can point me to, that'd be fantastic.

Thanks in advance.




 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a similar setup. I just configure Websphere to refer to whichever database is appropriate for the environment, and I just write code like "select * from table". I don't use qualifiers in the SQL. Why is it that your setup requires the qualifiers in your SQL?
 
Kev Adams
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Unfortunately, our setup is a bit strange. It's best explained by an example...

Let's say that we have a table called "account". On our production machine, it's in the "finance" qualifier. So, that makes it something like "select * from finance.account".

Now, on our development machine, that table is in a qualifier called "financet", with the ending "T" meaning it's a test qualifier. Now, the code turns into "select * from financet.account"

So, I can't just say "select * from account" and point websphere to the right database. Or, can I? I'm a novice at this, so that's why I was asking the question.

I was thinking that there must be a way to configure this, even if you have multiple qualifiers. So, if you have a single database, and the "account" table exists 3 times in the "finance", "financeqa" and "financeprod" qualifiers, can't you configure websphere to default that qualifier for a given data source? Or, if not, is there another way?
 
Climb the rope! CLIMB THE ROPE! You too tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic