File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes graceful way to avoid hardcoding as400 library name in query. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "graceful way to avoid hardcoding as400 library name in query." Watch "graceful way to avoid hardcoding as400 library name in query." New topic
Author

graceful way to avoid hardcoding as400 library name in query.

Ted J Schrader
Greenhorn

Joined: Jan 12, 2005
Posts: 2
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
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Ted J Schrader
Greenhorn

Joined: Jan 12, 2005
Posts: 2
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
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

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.
Ted J Schrader
Greenhorn

Joined: Oct 10, 2001
Posts: 28
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
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

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

Joined: Oct 10, 2001
Posts: 28
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
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: graceful way to avoid hardcoding as400 library name in query.