Connect to sql server database from webdynpro with jdbc 2.0 driver
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
}
Thats 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");