Connect to sql server database from webdynpro with jdbc 2.0 driver

[ronnie_kohring]

After a bit of looking around I finally got a connection working to my backend sql server database. The procedure to get it working is already available, but I had to do a lot of searching around to find it. So, I decided I might as well write the steps down for future reference. Note, I am no expert in java or web dynpro, so if there is a better way to access the database, feel free to leave a comment. This, however, should provide a way for new webdynpro programmers to get access to their databases.

Create SQL server 2000 JDBC 2.0 driver in j2ee engine

• Download SQL Server 2000 Driver for JDBC Service Pack 3: (http://www.microsoft.com/downloads/details.aspx?familyid=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en) and install it on j2ee server.

• Open Visual administrator, /usr/sap/<instance name>/JC00/j2ee/admin/go.bat

• Navigate to server > services > JDBC connector > drivers in runtime tab

• Click create new driver and specify a name, eg “SQL Server”)

• Point to 3 .jar files which was installed by the MS jdbc driver before. The files are: msbase.jar, mssqlserver.jar and msutil.jar

• The driver installation is now completed

Create datasource

• Click DataSources in the same path as above (server > services > JDBC connector)

• Click “new driver or datasource”.

• Fillout the following properties:

o 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.

Use the datasource from Web Dynpro

• From code the datasource can now be used by the following code snippet:

try {

InitialContext initialContext = new InitialContext();

DataSource dataSource = (DataSource)initialContext.lookup("jdbc/MyAlias");

java.sql.Connection connection = dataSource.getConnection();

}

catch (SQLException e) {

// handle exception

}

catch (NamingException e) {

// handle exception

}

That’s it. The data in the database can now be consumed by regular java objects, like

java.sql.Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery("select * from Region");

[Former Member]

Hi Ronnie,

Thanks for this detailed and informative document. I was very useful for me. I just want to add one more information to this. After following all your steps , I got a version conflict with sql server error. So I selected JDBC 1.0(with XAsupport) in place of 2.0 and it worked.

[Former Member]

Hi All,

Thanks for this wonderful thread. It was really helpful.

I did all the above mentioned steps.

When I tried executing the DataSource from WebDynpro it is throwing an

error:

com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.dbpool.exceptions.BaseResourceException: SQLException thrown by the physical connection: com.sap.sql.log.OpenSQLException: JDBC driver not supported for MS_SQL_SERVER database.

Any idea guys... Kavita Sardesai did you come across the same error?

Regards,

Nirmal

[Former Member]

Hello,

I have Problems with

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

I have installed the MSSQL Server.

I don't know the databaseName.

Here only stands the Server Name and the Service Name.

Patrick

[Former Member]

I have the same Problem,

where do I put the Source Code.

[Former Member]

Thanks for your explanation!

But where do I put the actual code for retrieving the data? (I've started a Bean-managed EJB)

[Former Member]

Hi Ronnie,

Firstly I would like to tell you that u have mentioned all the steps very very clearly and i followed them to create mydatasource.

But have a problem now:

1. Created a datasource named "<b>MyDatasource</b>", following all the steps as you gave above.

2. Have used <b>Database = Northwind. This db consists of a table named "Employees"</b>.

3. When I use statements as:

<b>Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("Select * from Employees");</b>

I get an error as:

<b>SQL statement is "SELECT "XMLVALUE" FROM "BC_DDDBTABLERT" WHERE "NAME" = ?"</b>

Dont know what is the error here?

4. Was going through the tables in my database and found that there is a table named <b>BC_DDDBTABLERT</b> with <b>XMLVALUE</b> as one of its attribute (column).

Want to ask why is my query going to this table, wherein I have explicitly mentioned table as Employees.

Have confirm the execution of this query using Query Analyzer too.

Awaiting Reply.

Please help me solve this error.

Thanks in Advance,

Uday

[Former Member]

Check this topic:

You might find your solution at the and of the thread.