cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Lookup Not working:

Senthilprakash1
Participant
0 Kudos

Dear All,

DATABASE: MMSQL

Please Help, I Tried to do a JDBC Lookup.

by exactly doing as told in this weblog

/people/william.li/blog/2007/03/30/using-jdbc-connection-pool-in-xi-message-mapping

STEPS:

1. Created a Java Code for Executing the lookup, following is the code..!

-


code----


package blabla.shine;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class Z2VDP {

public String getcircleid(String dataSource, String sqlStmt) {

String value = "Okay";

Connection conn = null;

Context ctx = null;

DataSource ds = null;

Statement stmt = null;

ResultSet rst = null;

try {

ctx = new InitialContext();

if (ctx == null) {

value = "Error - No Context";

return value;

}

ds = (DataSource) ctx.lookup(dataSource);

if (ds == null) {

value = "Error - No dataSource: " + dataSource;

return value;

}

}

catch (Exception e) {

value = e.getMessage();

return value;

}

try {

conn = ds.getConnection();

if (conn != null) {

stmt = conn.createStatement();

rst = stmt.executeQuery(sqlStmt);

if (rst.next()) {

value = rst.getString(1);

}

conn.close();

}

}

catch (Exception e) {

value = e.getMessage();

}

finally {

if (rst != null) {

try {

rst.close();

}

catch (Exception e) {

value = e.getMessage();

}

}

if (stmt != null) {

try {

stmt.close();

}

catch (Exception e) {

value = e.getMessage();

}

}

if (conn != null) {

try {

conn.close();

}

catch (Exception e) {

value = e.getMessage();

}

}

}

return value;

}

}

-


END CODE----


STEP2.Created Driver for MSSQL DB the 3 Jar fiels I imported in Visual Admin.

STEP3.Created DataSource ,Created Alias, 1st Used JDBC 1.X (no XA Support)

Specified the URL and the Paccess everything perfect.

STEP4. Did DB initialization test in visual Admin, was working perfectly.

Step 5. Created a user Defined Function in the Mapping for calling t he JAVA Code.

Step6. Compiled the Java Code and imported it in to Message Archives Successfully.

Step 7. When I tried to Test the Message Mapping in IR I am getting this error,

Error: com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector.exceptions.BaseResourceException: Cannot open resource(Managed Connection) with LocalTransaction support or add a second one in the same transaction, for component.

Step 8. I changed the DataSource to 2.0 XA Support connection.

Used Connection Pool Type, and specified the Drivers for the Datasource Properly.

Did Test DB Connection and wit too worked fine.

Step9. But when I tried to do the Message mapping testing I got the same error,

Error: com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector.exceptions.BaseResourceException: Cannot open resource(Managed Connection) with LocalTransaction support or add a second one in the same transaction, for component

Please Help me out, I have ran out of options , please help me out anyone pelase. Ii am trying to Rectify the problem, but donno where i am going wrong.

And if i need to change the code, could u please give me the code as i am very new to XI and Java.

Regards,

Prakash

Accepted Solutions (1)

Accepted Solutions (1)

GabrielSagaya
Active Contributor
0 Kudos
Senthilprakash1
Participant
0 Kudos

Dear Gabril,

yes i have checked the URL everything as i have told DB initialisation Test in the Visual Admin is also working Very fine.

Only this is i am getting the above mentioned error when ever i try to execte the Message Mapping.--> Test in the IR.

ERROR

Error: com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector.exceptions.BaseResourceException: Cannot open resource(Managed Connection) with LocalTransaction support or add a second one in the same transaction, for component "webContainer/applications/sap.com/TestDB/TestDB" in application "sap.com/TestDB".

Regards,

prakash

Edited by: senthilprakash selvaraj on Jun 12, 2008 5:35 PM

GabrielSagaya
Active Contributor
0 Kudos

Hi

Only thing to remember is when you are doing login with SQL Server , the main thing is to check the SQLCONNECTION PROPERTY. That's the one which causes problem.

check with the database name too

http://www.codeguru.com/forum/archive/index.php/t-235161.html

http://p2p.wrox.com/topic.asp?TOPIC_ID=55974

Senthilprakash1
Participant
0 Kudos

Dear Gabril,

Yes from the Visual Admin ia m able to test the connection in DB Initialisation TAB, I executed a querry in visual Admin and it successfully retried the value from the database after connection to it.

The problem is when i try to Test the Message Mapping, in there its thrwoing the above mentioned error.

Please Advice

Regards,

Prakash

Former Member
0 Kudos

Hi Prakash,

Did you solve the problem ?. I'm developing a new scenario and I have exactly the same problem. I guess is a bug in the SP.

Thanks.

Regards,

Yuvan.

Answers (1)

Answers (1)

nisarkhan_n
Active Contributor
0 Kudos

can u put the DB conenction pool parameters you have entered? how did u test to check your DB connection pool is working fine is it from test tab from the Connectore service?

how did u upload the jar file in the connector service?

Senthilprakash1
Participant
0 Kudos

Dear Nisar,

Yes i tested the BD Connection from DB Initialisation Tab in Visual Admin. I gave a sample SQL Querry and Executed it. It Connected to the Database and Feteched the required Value from the Database also.

Following is the connection Parameters i used for Data Source JDBC 2.0 XA Support

-


Connection Parameters for Data Source----


*Main tab > DataSource Name: fx “MyDatasource”

o Main tab > Add Alias: fx “MyAlias”

o Main tab > Driver Name: SQL Server

o Main tab > JDBC version: 2.0 (with XA support)

o Main tab > Object factory: com.microsoft.jdbcx.sqlserver.SQLServerDataSourceFactory

o Main tab > DataSource Type: ConnectionPoolDataSource

o Main tab > CPDS classname: com.microsoft.jdbcx.sqlserver.SQLServerDataSource

o Additional tab > add property “serverName = <your server>, fx localhost”

o Additional tab > add property “databaseName = <your db>, fx Northwind”

o Additional tab > add property “portNumber = <port>, fx 1433”

o Additional tab > add property “user = <sql database user>, fx sa”

o Additional tab > add property “password = <password>”

• Save and restart J2EE. The datasource is now ready to use.*

-


END DAta Source----


I loaded the the required .jar fiels for MS JDBC in JDBC Connector in visual Admin as discribed in this weblog

/people/william.li/blog/2007/03/30/using-jdbc-connection-pool-in-xi-message-mapping

Please Help

Regards

Prakash

Edited by: senthilprakash selvaraj on Jun 13, 2008 6:15 AM

Former Member
0 Kudos

Hi Senthil

what is the value you are passing for ds

is it ds = jdbc/MyAlias/SQL server or somthing else.

Thanks

Gaurav