This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Field alias in JDBC.... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Field alias in JDBC...." Watch "Field alias in JDBC...." New topic
Author

Field alias in JDBC....

Daniel Platon
Ranch Hand

Joined: Oct 10, 2005
Posts: 42
Hello all,

I wanted to take advantage of a neat feature of SQL called "field alias". I'm sure you all know you can do something like(example):


to automatically convert the returning field to the date format you want and access the column via the alias name ( p_expected_return_date ). I have tried this using a simple SQL statement and guess what ? JDBC thinks that "TO_DATE(p_expected_return_date" and "'DD.MM.YYYY') as p_expected_return_date" are two different fields, probably due to the " , " character. If I run this query from TOAD for example works fine, and the column is named according to the alias.

Does anyone have any idea how this can be fixed ? It happens on Oracle 10g.

Have a great day,
Dan
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


JDBC thinks that "TO_DATE(p_expected_return_date" and "'DD.MM.YYYY') as p_expected_return_date" are two different fields

Huh? Using column aliases works for me. Aliases after all are applied by the database, not JDBC. Are you sure your SQL is valid?

Also, why are you formatting the date in your database? Would it not make more sense to just return a date and have Java apply the formatting (which keeps your JDBC code database independent)?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Daniel Platon
Ranch Hand

Joined: Oct 10, 2005
Posts: 42
Hello and thanks for the reply,

Unfortunately this query must be as flexible as it can get. I build it dynamically in terms of select columns and conditions, so there is no way to know whether the column is a date, a string or an int. The problem is not with the column alias, but with the damn to_date() function. That's why I think is a JDBC issue.
I will try using a PreparedStatement insted of a Statement, see if that's of any help

Thanks a lot and have a good day.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Unfortunately this query must be as flexible as it can get. I build it dynamically in terms of select columns and conditions, so there is no way to know whether the column is a date, a string or an int

The database will know, since it has to. And if the database knows you can get this information via JDBC. The easiest is to use the getObject method of ResultSet.


The problem is not with the column alias, but with the damn to_date() function. That's why I think is a JDBC issue.

The ResultSet that is returned is just a direct reflection of the results in the database, JDBC is not adding or changing anything. So if you can run your query via Toad, JDBC can handle the result set. Check you are running the query you think you are.
Joan Horta Tosas
Ranch Hand

Joined: Feb 01, 2007
Posts: 59
I think you have a problem using the same name as the parsed column and the alias (you have both p_expected_return_date). Change one of them.
Daniel Platon
Ranch Hand

Joined: Oct 10, 2005
Posts: 42
Hello all,

Problem solved due to the old "looking-at-code-again-and-realizing-how-stupid-you-are" method . There is no problem in using the same name for the alias and the parsed column.

Thanks,
Dan
 
Don't get me started about those stupid light bulbs.
 
subject: Field alias in JDBC....
 
Similar Threads
Inserting date using SQL
Query works in DB, but not with JDBC (MS version)
stuck up in the problem
Date functions
Using Hibernate to do Oracle query with to_date function