Solved

JSP for mySQL to Oracle

Posted on 2003-11-24
7
377 Views
Last Modified: 2010-04-01
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.
0
Comment
Question by:Tuan_Jean
  • 4
  • 3
7 Comments
 
LVL 6

Accepted Solution

by:
jarasa earned 40 total points
ID: 9816699
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.

regards

Javier
0
 

Author Comment

by:Tuan_Jean
ID: 9820993
Thank you.
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9823162
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.
regards
Javier.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Tuan_Jean
ID: 9828184
Javier,

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?
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9830715
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.

constants.java
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
          if(s.equals("ORACLE"))
                p = this.ORACLE[n];
          else
                p = this.mySQL[n];
          return p;
          
    }
}

access.jsp
<%@ 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);
......
%>
<html>
<head>
<title>Documento sin t&iacute;tulo</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<%=sql%>
</body>
</html>


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.

regards.
Javier
0
 

Author Comment

by:Tuan_Jean
ID: 9833844
Javier. Thank you again.
0
 

Author Comment

by:Tuan_Jean
ID: 9833850
Dear Administrator, I am very please with the answer Javier posted. Could you please change the grade from C to A. Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
default methods vs abstract class 1 109
dateFashion java challenge 9 132
lessBy10  challenge 15 92
Java Script nested call 3 75
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
A customer recently asked me about anti-malware and the different deployment options available for his business. Daily news about cyberattacks, zero-day vulnerabilities, and companies that suffered a security breach made him wonder if the endpoint a…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now