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 HSQLDB and TO_DATE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "HSQLDB and TO_DATE" Watch "HSQLDB and TO_DATE" New topic
Author

HSQLDB and TO_DATE

Tee Morales
Greenhorn

Joined: Apr 21, 2009
Posts: 3
I am writing some JUnit test cases with HSQLDB.

I found that TO_DATE, TO_CHAR are not found in HSQLDB. Was wondering if anyone had a workaround for this. The team I am on uses HSQLDB as a test case database. But the actual Application will run against Oracle.

Any help will be appreciated.
TIA
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14268
    
  21

TO_DATE etc. are Oracle-specific SQL functions. They're not standard SQL so they don't exist in HSQLDB or other databases. To get around this, don't use those Oracle-specific functions, or use Oracle instead of HSQLDB for testing. If Oracle licenses are a problem, you could maybe use the free Oracle Express Edition for this purpose.


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

What Jesper says.

To make your application portable (you have this requirement, because it has to work on two database brands),
you have to use standard sql in your queries.

You should not need to_date.
If you have a select statement, you can retrieve date values by using ResultSet.getDate() and family.
If you have to use a date in the where clause, you should use PreparedStatement, and bind your date parameters.

Making an app database independent is not trivial. It's hard when you use frameworks that are specialised to do it, let alone without such a framework.
Databases behave differently (subtile - or sometimes not so subtile) even if you only use standard sql statements.
I don't know if it is a good idea to test your application with HSQLDB only, when production runs on Oracle.
I would certainly also include tests with an Oracle database.


OCUP UML fundamental and ITIL foundation
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: HSQLDB and TO_DATE