• 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

graceful way to avoid hardcoding as400 library name in query.

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I've just started a new job and this is the first time I've worked with code that queries AS/400 libraries and files. In some of the existing applications, the sql statements hardcode the library name: ie:



In others, the library name (UTLIB in the above example) have been extracted to a properties file, which is more flexible but makes for ugly code:



From a testing standpoint, the former is a nightmare. There are actually two code bases of an entire app here: one that hardcodes the test library everywhere and then there's the "production" code base. Scary!

So, my question is this: is there a way to set up a DataSource that already knows the library? This way the above code simply becomes:



I could have a TEST DataSource and a PROD DataSource and flip the switch as needed. Elegant, yeah?

Any help would be appreciated, from a verbose answer to simply a nudge in the right direction.

Cheers,

Ted
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ted,
Welcome to JavaRanch!

You need to have the schema/library name in the query. A datasource wouldn't know the schema name. (Too bad, that would be a nice feature!)

I prefer the second query with the schema in a property file. It isn't that ugly. And you can make it a little nicer by introducing a getUsrsTable() method. This works well if you only have a few tables. One method per table and it is easier to read.
 
Ted J Schrader
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, Jeanne! You've probably saved me hours of fruitless Google research.

Let me reiterate my understanding of your getUsrsTable() suggestion:

Instead of:


I could do something like this:



Interesting suggestion! And, yes, it's not that ugly. I'll probably go this route. Of course, getting the hardcoded SQL out of the JSPs, that's a different matter altogether!

Thanks again.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ted,
Yes the rewritten query is correct.

It is good to avoid all code in the JSP, not just SQL. Try doing the query in a servlet and having it forward to a JSP.
 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Okay, I haven't tried this yet (I'm writing it here for future reference for myself as well) but it looks like the library can be set in the JDBC URL.

Check out the following:

IBM Toolbox for Java: JDBC FAQ

IBM Virtual Innovation Center for Hardware
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ted,
Thanks for the links! It looks like a very database/platform specific solution, but if you know you are using AS/400 that may be ok.
 
Ted J Schrader
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jeanne,

I'm hoping you would go into a bit more detail in regards to this solution being very DB/platform specific. I fail to see how I could get tied down, at least from a web-app perspective.

Here's a snippet from some sandbox code that is working for me:



I think going this route, instead of having to concatenate generated table names in my SQL, is a move towards better design. I could simply change the connection URL in a Struts or other conn-pooling-framework config file if I change libraries or even DB vendors. It would be easier to externalise most of my SQL statements to a file, if I should want to do that, because they would be final Strings, for the most part.

Help me understand your point of view. Thanks.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ted,
You are completely right! In trying to explain my point of view I realized it contained a fallacy Putting the library name in the URL should be fine.
reply
    Bookmark Topic Watch Topic
  • New Topic