ClassNotFoundException: com.sap.portals.jdbc.sqlserver.SQLServerDriver

[reza_saket]

Hello,

(Running NW04SP14)

I am trying to use the jdbc class in my code as follows:

Class.forName("com.sap.portals.jdbc.sqlserver.SQLServerDriver");

I am getting a ClassNotFoundException.

Could you please tell me what do I need to do to import this class for all my portal components?

Do I need to physically include a jar file, if so where can I find it?

Should I include a sharing reference in my code?

Thanks.

[detlev_beutner]

Hi,

have the answers been of any value for you? If yes, please consider rewarding points for helpful answers! Thanks in avance!

Best regards

Detlev

[reza_saket]

Hi,

Thanks for all the valuable hints.

The issue has been resolved by first using the microsoft jdbc driver and attaching them to the PAR file. The second solution was to uploaded and change the JDBC driver to "com.sap.nwmss.jdbc.sqlserver.SQLServerDriver".

The last question that I have is how to obtain the DataSource that have been setup using Visual Admin.

I have created a DataSource "fics" that I am trying to access in a JSPDynPage as follows:

......

InitialContext ctx = new InitialContext();

DataSource dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/fics");

......

I keep getting the following error:

Path to object does not exist at java:comp, the whole lookup name is java:comp/env/jdbc/fics.#

What is the proper way to access this DataSource and do I need to include anything in my XML files in the PAR? I went trough al the notes for DataSource setup, but still keep getting this error.

Thanks

[detlev_beutner]

Hi Reze,

try

ctx.lookup("jdbc/fics");

And I already asked you explicitely to reward points for people taking the time to help you

Regards

Detlev

[Former Member]

Hi Detlev,

I'm asking to you about a problem with connection that since many days I'm not able to solve. I hope in your help regarding this.

I have to connect to Oracle DB from an EJB, I used two solution but without success:

<b>----

---

1. JDBC Connection from a Bean

-

---

</b>

Connection con = null;

Statement stmt = null;

ResultSet rset = null;

String connectionURLThin = "jdbc:oracle:thin:@liposv01:1527:PCD";

String driverClass = "oracle.jdbc.driver.OracleDriver";

String userID = "sappcddb";

String userPassword = "1qaz2wsx";

String queryString = "select ordine_modello_sq.nextval from dual";

int risultato = 0;

try {

//check = "start connection";

Class.forName(driverClass).newInstance();

con = DriverManager.getConnection(connectionURLThin, userID, userPassword);

stmt = con.createStatement ();

rset = stmt.executeQuery(queryString);

if (rset.next()) {

risultato = rset.getInt(1);

//prog_ordine_seq = new Integer(risultato);

//check = "Connessione JDBC avvenuta";

}

rset.close();

stmt.close();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (InstantiationException e) {

e.printStackTrace();

} catch (IllegalAccessException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

if (con != null) {

try {

con.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

}

} finally {

if (con != null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

---

<b>I have the error(the server is unix and a classpath is set)</b>

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

-

---

Loader Info -

---

ClassLoader name: [local/OrdineWD]

Parent loader name: [Frame ClassLoader]

References:

common:service:http;service:servlet_jsp

service:ejb

common:service:iiop;service:naming;service:p4;service:ts

service:jmsconnector

library:jsse

library:servlet

common:library:IAIKSecurity;library:activation;library:mail;library:tc_secssl

library:ejb20

library:j2eeca

library:jms

library:opensql

common:library:com.sap.security.api.sda;library:com.sap.security.core.sda;library:security.class;library:webservices_lib;service:adminadapter;service:basicadmin;service:com.sap.security.core.ume.service;service:configuration;service:connector;service:dbpool;service:deploy;service:jmx;service:jmx_notification;service:keystore;service:security;service:userstore

interface:resourcecontext_api

interface:webservices

interface:ejbserialization

sap.com/tc_wddispwda

sap.com/ModelloApplication

sap.com/tc_wdcorecomp

service:webdynpro

service:sld

library:tc_ddicddicservices

library:com.sap.aii.proxy.framework

library:tc_graphicsigs

library:com.sap.mw.jco

library:com.sap.lcr.api.cimclient

library:sapxmltoolkit

library:com.sap.aii.util.rb

library:com.sap.util.monitor.jarm

library:tc_ddicddicruntime

library:com.sap.aii.util.xml

library:tc_colapi

library:com.sap.aii.util.misc

library:tc~cmi

library:tc_colruntime

Resources:

/usr/sap/PCD/JC00/j2ee/cluster/server0/apps/local/OrdineWD/webdynpro/public/lib/ModelloHelper.jar

/usr/sap/PCD/JC00/j2ee/cluster/server0/apps/local/OrdineWD/webdynpro/public/lib/app.jar

/usr/sap/PCD/JC00/j2ee/cluster/server0/apps/local/OrdineWD/webdynpro/public/lib/MyCommandBean.jar

/usr/sap/PCD/JC00/j2ee/cluster/server0/apps/local/OrdineWD/webdynpro/public/lib/ModelloEjb.jar

Loading model: {parent,references,local}

<b>----

---

1. DataSource Connection from sessionBean

-

---

</b>

try {

InitialContext ctx = new InitialContext();

DataSource ds = (DataSource) ctx.lookup("java:comp/env/ejb/ORDINE_MODELLO_POOL");

Connection con = ds.getConnection();

String query = "select ordine_modello_sq.nextval from dual";

Statement stmt = con.createStatement();

try {

ResultSet rs = stmt.executeQuery(query);

try {

while ( rs.next() ) {

//Insert the sequence value

int prog_ordine_int = rs.getInt(1);

prog_ordine_seq = new Integer(prog_ordine_int);

}

} finally {

rs.close();

}

} finally {

stmt.close();

}

}catch (Exception e) {

e.printStackTrace();

}

<b>I have the error finding datasource:</b>

com.sap.engine.services.jndi.persistent.exceptions.NamingException: Exception during lookup operation of object with name ejbContexts/sap.com/ModelloApplication/MySessionBean/java:comp/env/jdbc/ORDINE_MODELLO_POOL, cannot resolve object reference. [Root exception is com.sap.engine.services.connector.exceptions.BaseResourceException: ConnectionFactory "jdbc/ORDINE_MODELLO_POOL" does not exist. Possible reasons: the connector in which ConnectionFactory "jdbc/ORDINE_MODELLO_POOL" is defined is not deployed or not started.]

<b>Thanks in advance for your help.</b>

Vito Palasciano

[detlev_beutner]

Hi Vito,

1.) Mark code with the "Code" button of the SDN forum editor, that makes it much more readable.

2.) In your first case, obviously the classloader has no reference to the driver. Add the driver itself to the project or a reference to the deployed driver, if it is already deployed somehow. Anyhow, this very basic JDBC approach is not really something advised.

3.) You quoted your code with

DataSource ds = (DataSource) ctx.lookup("java:comp/env/<b>ejb</b>/ORDINE_MODELLO_POOL")

?! Anyhow, the exception message sounds different. Even if from an EJB that should work, did you also try the alternative "jdbc/ORDINE_MODELLO_POOL" (or does the exception message in fact come from such an approach? Test both!). Is "ORDINE_MODELLO_POOL" really the name of the datasource (alias)?!

Also check out http://help.sap.com/saphelp_nw04/helpdata/en/82/fdbf2085f65f43a71e755fc904478d/frameset.htm in detail.

Hope it helps

Detlev

[Former Member]

<b>Detlev,

sorry the right line is:</b>

DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/ORDINE_MODELLO_POOL")

<b>But I tested both without success. Regarding ORDINE_MODELLO_POOL:</b>

<b>In data-source-aliases.xml (in EAR project) I have:</b>

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE data-source-aliases SYSTEM "data-source-aliases.dtd">

<data-source-aliases>

<aliases>

<data-source-name>${com.sap.datasource.default}</data-source-name>

<alias>ORDINE_MODELLO_POOL</alias>

</aliases>

</data-source-aliases>

<b>In ejb-jar.xml (in ejb project) I have:</b>

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">

<ejb-jar>

<description>Progetto Ordine Modello</description>

<display-name>OrdineModello</display-name>

<enterprise-beans>

<session>

<ejb-name>ModelloSessionBean</ejb-name>

<home>com.sap.examples.modello.ModelloSessionHome</home>

<remote>com.sap.examples.modello.ModelloSession</remote>

<local-home>com.sap.examples.modello.ModelloSessionLocalHome</local-home>

<local>com.sap.examples.modello.ModelloSessionLocal</local>

<ejb-class>com.sap.examples.modello.ModelloSessionBean</ejb-class>

<session-type>Stateless</session-type>

<transaction-type>Container</transaction-type>

<ejb-local-ref>

<ejb-ref-name>modello/ModelloEntityBean</ejb-ref-name>

<ejb-ref-type>Entity</ejb-ref-type>

<local-home>com.sap.examples.modello.ModelloEntityLocalHome</local-home>

<local>com.sap.examples.modello.ModelloEntityLocal</local>

<ejb-link>ModelloEntityBean</ejb-link>

</ejb-local-ref>

<resource-ref>

<res-ref-name>jdbc/ORDINE_MODELLO_POOL</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

<res-sharing-scope>Shareable</res-sharing-scope>

</resource-ref>

</session>

<entity>

<ejb-name>ModelloEntityBean</ejb-name>

<home>com.sap.examples.modello.ModelloEntityHome</home>

<remote>com.sap.examples.modello.ModelloEntity</remote>

<local-home>com.sap.examples.modello.ModelloEntityLocalHome</local-home>

<local>com.sap.examples.modello.ModelloEntityLocal</local>

<ejb-class>com.sap.examples.modello.ModelloEntityBean</ejb-class>

<persistence-type>Container</persistence-type>

<prim-key-class>java.lang.Integer</prim-key-class>

<reentrant>False</reentrant>

<cmp-version>2.x</cmp-version>

<abstract-schema-name>ModelloEntity</abstract-schema-name>

<cmp-field>

<field-name>desc_ordine</field-name>

</cmp-field>

<cmp-field>

<field-name>data_creaz_ordine</field-name>

</cmp-field>

<cmp-field>

<field-name>prog_ordine</field-name>

</cmp-field>

<cmp-field>

<field-name>utente_ep</field-name>

</cmp-field>

<cmp-field>

<field-name>status</field-name>

</cmp-field>

<cmp-field>

<field-name>cod_titolare</field-name>

</cmp-field>

<cmp-field>

<field-name>data_mod_ordine</field-name>

</cmp-field>

<primkey-field>prog_ordine</primkey-field>

<query>

<query-method>

<method-name>findByStatus</method-name>

<method-params>

<method-param>java.lang.String</method-param>

</method-params>

</query-method>

<ejb-ql>select object(b) from ModelloEntity b where b.status like ?1</ejb-ql>

</query>

</entity>

</enterprise-beans>

</ejb-jar>

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE data-source-aliases SYSTEM "data-source-aliases.dtd">

<data-source-aliases>

<b><aliases>

<data-source-name>${com.sap.datasource.default}</data-source-name>

<alias>ORDINE_MODELLO_POOL</alias>

</aliases></b>

</data-source-aliases>

<b>I hope this coul help you to understand the problem. thanks.</b>

Vito

PS: Code button used

[Former Member]

Detlev,

for the JDBC connection (that I need to retrieve an Oracle sequence value to insert in the table PK) <u>I just import ojdbc14.jar in javaBuildPath of my project</u> (the project should make the JDBC connection to DB in order to retrieve the sequence value and then call the create method of the sessionBean in order to insert the record in DB so the PK is unique).

What I'm missing to do? I really don't understand? Please, help me.

Vito

[detlev_beutner]

Hi Vito,

the buildPath is used at compile time. It has no meaning at all at runtime.

Hope it helps

Detlev

[Former Member]

Detlev, this is clear. But I have the problem yet. I just wanto to do a JDBC connection.

Hovewer, another big problem, I try to retrieve data from a table from a session bean using this method:

private ModelloEntityLocalHome modelHome;

......

public Collection viewAll() {

Collection bonuses = new ArrayList();

try {

Collection active = modelHome.findAll();

ModelloEntityLocal currModelloEntityLocal;

for (Iterator iterator = active.iterator(); iterator.hasNext();) {

currModelloEntityLocal = (ModelloEntityLocal) iterator.next();

if (currModelloEntityLocal!=null){

bonuses.add(getModelBridge(currModelloEntityLocal));

}

}

} catch (FinderException e) {

e.printStackTrace();

}

return bonuses;

}

I have an exception Java null pointer, I think all is right, very stange. My bean are:

- ModelloSession

- ModelloSessionBean

- ModelloSessionHome

- ModelloSessionLocal

- ModelloSessionLocalHome

Another exception (casting exception) in these lines, i haven't this error before:

public MyCommandBean() throws CreateException {

// looks up the session bean and creates the Home interface

try {

InitialContext ctx = new InitialContext();

<b>home = (ModelloSessionLocalHome)ctx.lookup("localejbs/MySessionBean");</b>

modelloSessionLocal = home.create();

} catch (Exception namingException) {

namingException.printStackTrace();

}

}

I have to solve these as soon as possible, I hope in your help. Thanks.

Vito

[Former Member]

hi,

You can add the jar file to run on the server side under

dist>PORTAL-INF>lib. I think this would solve your purpose.

Regards,

Ganesh.N

[Former Member]

Hi Reza,

Check out the following thread. That should solve your problem.

<b>>

Regards

Rajeev.

Do award points for helpful answers in SDN.

[Former Member]

Hi,

we have used the standard com.microsoft.jdbc.sqlserver.SQLServerDriver and

added the Driver classes (jars - msbase.jar,mssqlserver.jar,msutil.jar) on the

JDBC Connector in Visual Administrator.

we created a Datasource working on this Driver, and we used the AliasName,

Created in Visual Administrator, to get the connection in the portal apps.

Regards,

S.Divakar

[Former Member]

Hi,

Download the class finder from the above link. If you specify the exception, it will tell you what is the problem.

http://www.alphaworks.ibm.com/aw.nsf/reqs/jarclassfinder

[reza_saket]

Detlev,

Do you know where is the physical jar for my class:

com.sap.portals.jdbc.sqlserver.SQLServerDriver?

And where would I need to include it on the physical path on the server?

I would prefer to avoid this and use the defined JDBC connection within the Portal, however I could not find a way to do PreparedStatements in my code.

Do you know if it is possible to use the PreparedStatement class with the Portal connection?

[detlev_beutner]

Hi Reza,

> where is the physical jar for my class

Maybe I wasn't clear I don't think that you should grab it anyhow, but if you want it, you'll get it:

\usr\sap\<ID>\<INST>\j2ee\cluster\<NODE>\bin\ext\com.sap.portal.jdbcdrivers\P9sqlserver.jar

> use the defined JDBC connection within the Portal

You can also add your own, for example not based on OpenSQL but on vendor/native SQL, see the link given.

> possible to use the PreparedStatement class

> with the Portal connection

That should even work with the standard DB instance.

Hope it helps

Detlev

[detlev_beutner]

Hi Reza,

> need to physically include a jar file

Theoretically, you could. Practically, you shouldn't.

> include a sharing reference

Maybe that could work, but I won't check this. For this simple reason:

In general, using a J2EE server means that you have a DB connection pool at hand where you can attach your database and then retrieve the Datasource object from the JNDI. That way, you won't have to care about connection pooling etc, and using tools like oject-relational-mappers also is quite easy, for they also expect just the Datasource or even better the JNDI lookup name.

See http://help.sap.com/saphelp_nw04/helpdata/de/bb/69da54d8aedc419d46d9ea074e4d41/frameset.htm for details how to attach your DB to the server.

Hope it helps

Detlev