Skip to Content
0
Sep 21, 2005 at 09:29 AM

Connect to sql server database from webdynpro with jdbc 2.0 driver

58 Views

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");