views:

149

answers:

3

I have a java-application using JDBC for database interaction. I want to do a search based on a series of regular-expressions, however the application should be generic, we do not know if the database-engine will by mysql,oracle,sql server etc, but we are pretty sure it's gonna be either mysql or oracle.

Will regular-expressions limit my application to a specific database or can i use them without worrying about compatibility?

+4  A: 

Well, using regular expressions will limit you to DBMS that support them :-). That said, at least Oracle, MySQL, PostgreSQL and MS-SQL support some sort of regexp, so it should not be a problem in principle. You might still run into compatibility problems, of course.

Your best bet probably is to confine the use of regular expressions to some defined parts of the application, such as a few stored procedures or one module in your app that generates the SQL queries.

That is good practice anyway, and will make later changes doable.

sleske
+2  A: 

While in theory I believe both MySQL and Oracle are meant to support POSIX ERE, MySQL uses REGEXP where Oracle uses REGEXP_LIKE, and regular-expressions.info notes quirks with Oracle's implementation (there are likely similar ones for MySQL).

So, you probably can't use this.

Matthew Flaschen
+1  A: 

Yes, using non-standard SQL features like regular expression search will limit your application to a specific database.

Like slekse suggested, a good solution is to confine your use of non-standard SQL features to one specific module. Then you'll only need to change that module if you change DBMS.

Steve McLeod