Connecting to SQL via EJB

[Former Member]

Hi all,

I need to access a SQL server from my EJB program. I have to connect to the SQL and retrieve data.This is my requirement. As of now, I am connecting to the SQL using the following code in my bean.

/********Code begins*******/

//To connect to SQL

try {

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

Statement stmt = null;

Connection connection = null;

System.out.println("SQL Web Service");

try {

connection =

DriverManager.getConnection(

"jdbc:microsoft:sqlserver://<Hostname>:<port>",

"<Username>",

"<pwd>");

stmt = connection.createStatement();

} catch (SQLException e1) {

e1.printStackTrace();

}

// To retrieve the data from DB

ResultSet rs = null;

String return_string = null;

String query = "select * from <tablename>";

try {

rs = stmt.executeQuery(query);

while (rs.next()) {

return_string = rs.getString(1);

}

} catch (SQLException e2) {

e2.printStackTrace();

}

try {

connection.close();

} catch (SQLException e3) {

e3.printStackTrace();

}

return return_string;

/*****Code ends*******/

This is what I've done now. But it doesn't work!! When I deploy the EAR and run , I get the following exception.

com.sap.engine.services.ejb.exceptions.BaseRemoteException

Can someone help me with the right way of connecting to SQL server from EJB. I need to expose this as a web service later.

Thanks & Regards,

M.Subathra

[Vlado · Accepted solution]

Hi M.Subathra,

The standard J2EE way of connecting to a DataBase is via a JDBC DataSource object. Using the DriverManager directly is not recommended in J2EE scenarios. For more information please refer to the SAP NetWeaver online help:

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/82/fdbf2085f65f43a71e755fc904478d/frameset.htm">Getting a Connection to the Database</a>

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/64/8590109a86f145958fb22dab86d58d/frameset.htm">Declaring Resource References</a>

Hope it helps!

-Vladimir

[Former Member · Accepted solution]

Hi Vladimir,

Thanks for the reply. Let me try it out. But then, the web service works fine in NW04.

I tried using NW04s SP10. The error is as follows:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket

What could be the problem?

Thanks & regards,

M.Subathra

[Vlado · Accepted solution]

Hi M.Subathra,

So, you are still connecting via a DriverManager, right? As I already mentioned, please try the DataSource solution outlined above.

[Former Member]

Hi,

Solved the issue. I had to make a host file entry for the SQL server in the WAS!! That was why it threw 'Error establishing socket' exception.

Regards,

M.Subathra

[Former Member]

Hi,

One Issue I could guess,

>This DB Driver is available in the application server where you deploy the EAR.

>Also where is the exact location of Exception Raised. possibly paste the Stack Trace here.

regards

Vivek Nidhi

[Former Member]

Hi,

The stack trace says "java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver .SQLServerDriver". I could figure out that I need to add the sql jar files somewhere. I tried adding the jar files in Java build Path of the EJB project, but in vain.

>This DB Driver is available in the application server where you deploy the EAR.

--> What does this mean? I would like to add that, if I run the same code from a standalone java program, I get the expected result. (I added the jar files in external jars).

Can someone help me as to what has to be done?

Thanks & regards,

M.Subathra

[Former Member]

Hi,

Just an additional info - I am using NW04s SP7. Will that be a problem cos the same code works well in NW04.

Any ideas?

Thanks & regards,

M.Subathra