JSP for mySQL to Oracle

I have a small program that is written using JSP using mySQL at the backend (development) and I am thinking to move it to Oracle (production). There are a few conversion issues and I appreciate your input and suggestions:

1. In mySQL there is auto_increment function but not in Oracle 9i. In my JSP sql (that uses mySQL) I could have just use

insert into t_emp (id, name) value ('', 'Joe Black');

In oracle I uses sequence and the sql looks like:

insert into t_emp values (emp_seq.nextval, 'Joe Black');

How could I use just 1 type of sql for both databases?

2. If I use NOW() in the DATETIME in mySQL in some INSERT statement, what should I replace with in Oracle? sysdate seems to be doing the equivalent.

My real questions is, using JSP, how could I program in such a way that my program is independent of the database at the backend?

Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Tuan_Jean.

I guess is not a way that you could do that, unless that you uses the DB as a parameter and put it on the the code. Different DBs uses different sentences or functions so you have to take care of them. is like the AS on the FROM statement you can't use it in Oracle but you must use it in mySQL.

If you have a way to Know the DB uses just make a switch on the code and send the proper statement to the DB.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tuan_JeanAuthor Commented:
Thank you.
Hi Tuan_Jean.
Did you solve the problem as I recomended you?
If you did I wonder why you assigned me a C grade instead an A grade.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Tuan_JeanAuthor Commented:

I understand the method you recommended are working but didn't use it as this makes the code very complex as each sql statement will have to had a switch.

However, I am not sure how the Grade C comes into the picture. I am happy to assign grade A. How do I do it?
HI Tuan_Jean.

Actually there is a way that you could do it without a switch, if you place a session variable with the DB used onces you start the application you could have a constants file with all your querys for the different DataBase and use them as needed compounding them at runtime on the JSP, somethin like this.

public class Constants

    public Constants()

    public static final String [] ORACLE = {"ORACLE SQL 1","ORACLE SQL 2"};
    public static final String [] mySQL = {"mySQL SQL 1","mySQL SQL 2"};
    public String get(String s, int n) {
          String p;
             // You can make a switch here if you have more than 2 DBs
                p = this.ORACLE[n];
                p = this.mySQL[n];
          return p;

<%@ page import="Constants" %>
session.putValue("DB", "ORACLE"); // Obviously you must put this in the login jsp
Constants c = new Constants();
String sql = c.get((String) session.getAttribute("DB"),0);
<title>Documento sin t&iacute;tulo</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">


About the A grade I thisk you shoud just post a comment to the sdministrators saying that you want the Grade changed for this question from C to A.

Tuan_JeanAuthor Commented:
Javier. Thank you again.
Tuan_JeanAuthor Commented:
Dear Administrator, I am very please with the answer Javier posted. Could you please change the grade from C to A. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.