JDBC Connection Setup
Go to Visual administrator (j2ee admin) -> Server -> Services -> JDBC Connector -> tab strip Runtime -> Drivers -> SYSTEM_DRIVER -> sap.com.portal.jdbcdrivers
MS SQL Server 2000
Driver files |
msbase.jar, msutil.jar, mssqlserver.jar |
Connection URL |
jdbc:sqlserver://<host_name>:<port>;DatabaseName=<db_name> |
Driver name |
com.microsoft.jdbc.sqlserver.SQLServerDriver |
MS SQL Server 2005
Driver file |
sqljdbc.jar |
Connection URL |
jdbc:sqlserver://<host_name>:<port>;database=<db_name> |
Driver name |
com.microsoft.sqlserver.jdbc.SQLServerDriver |
Oracle
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
Driver file |
n/a |
Connection URL |
jdbc:oracle:<drivertype>:@<database> |
Driver name |
oracle.jdbc.driver.OracleDriver |
Example |
jdbc:oracle:thin:@myhost:1521:orcl |
MaxDB
Driver file |
|
Driver |
com.sap.dbtech.jdbc.DriverSapDB |
Connection URL |
jdbc:sapdb://<server>:<Port>/<database> |
Example |
jdbc:sapdb://ilgm76/mydb |
MySQL
http://dev.mysql.com/downloads/connector/j/3.1.html
Note a Version 5.0 does exists but only tested on 3.1
http://dev.mysql.com/downloads/connector/j/5.0.
Driver file |
mysql-connector-java-3.1.14-bin.jar |
Driver |
com.mysql.jdbc.Driver |
Connection URL |
jdbc:mysql://<host_name>:<port>/<database> |
Example |
jdbc:mysql://ilgm76:3306/mydb |
CSV / Excel
A CSV-JDBC driver recommended by multiple users: http://www.hxtt.com (Driver)
Driver file |
|
Driver |
com.hxtt.sql.text.TextDriver |
Connection URL |
jdbc:text:<file path> |
Example |
jdbc:text:/C:/EP_Installables/test.xls |
Another one (that was not yet successfully setup) Relique Driver
Driver file |
|
Driver |
org.relique.jdbc.csv.CsvDriver |
Connection URL |
jdbc:relique:csv:c:csv, suggested alternative: jdbc:csv:csvdir?_CSV_Header=true |
Comment to the Relique drive:
The problem is that the driver is not fully implemented (missing a lot of functions, i.e. getTables). All you have to do is to implement a few functions (getTables and getColumns in CsvDatabaseMetadata class - you have to create a result set with names of csv files as a tables (in getTables) and the result set with names of columns - parse the first line of csv file). In class CsvStatement you have to change function execute (call executeQuery and save the result) and getResultSet (return the saved result from there).
1 Comment
Unknown User (r4gib8t)
How to setup JDBC & BI JDBC Connection in SAP Portal and MS SQL 2000
MS SQL 2000 Server Settings
MS SQL 2000
Apply Patch 3a
Install Microsoft SQL Server 2000 Driver for JDBC
Msbase.jar
Mssqlserver.jar
Msutil.jar
MS SQL Enterprise Manager->Microsoft SQL ServersàSQL Server GroupàLocal(Windows NT)à Right Click à Security Tab
Select options(Authentication): SQL Server and Windows
SAP Netweaver Settings for JDBC settings
JDBC connection allows to see the only Stored Procedures in the SQL Database
While if you want to see the table & run queries on Views please create the BI JDBC connection , BI JDBC doesn't allow the stored procedures.
http://6nhgl1s:50100/irj/portalOpen the Server Administration
System AdministrationàSystem ConfigurationàBrowseàPortal ContentàVisual ComposeràRight Click Select NewàSystem from PAR
Select : com.sap.portal.systems.jdbc
Property CategoryàSelect Connection Properties
Connection URL à jdbc:sap:sqlserver://<sqlserverip>:1433;DatabaseName=SurveyDB1
Driver Class Nameà com.sap.portals.jdbc.sqlserver.SQLServerDriver
Validate Connection à Yes (to check the connection problem in Log Viewer)
Property CategoryàSelect User Management
User Mapping Typeàadmin, user
Displayà select System Aliases
Give the system a name: SurveryDB1
Click addàclick Save
Go to User Administration Tab àIdentity Management
Select User : Administrator
Double click to modify
Click on Modify Tabàscroll to last tab "User Mapping"
Select System from list (SurveyDB1)
Mapped User: sa
Mapped Password: <password for sql user sa>
System AdministrationàSystem ConfigurationàBrowseàPortal ContentàVisual ComposeràSurveyDB1 ( the system created earlier)
Displayà Connection Tests
Test the connection
If error check the error in the Log Viewer.
SAP Netweaver Settings for BI JDBC settings
http://portalname:50100/irj/portalOpen the Server Administration
System AdministrationàSystem ConfigurationàBrowseàPortal ContentàVisual ComposeràRight Click Select NewàSystem from PAR
Select : com.sap.portal.systems.bi
Property CategoryàSelect Connection Properties
Connection URL à jdbc:microsoft:sqlserver://<<sqlserverip>:1433;DatabaseName=VC_TA
Driver Class Nameà com.microsoft.jdbc.sqlserver.SQLServerDriver
Validate Connection à Yes (to check the connection problem in Log Viewer)
Property CategoryàSelect User Management
User Mapping Typeàadmin, user
Displayà select System Aliases
Give the system a name: SurveryDB2
Click addàclick Save
Go to User Administration Tab àIdentity Management
Select User : Administrator
Double click to modify
Click on Modify Tabàscroll to last tab "User Mapping"
Select System from list (SurveyDB2)
Mapped User: sa
Mapped Password: <password for sql user sa>
System AdministrationàSystem ConfigurationàBrowseàPortal ContentàVisual ComposeràSurveyDB1 ( the system created earlier)
Displayà Connection Tests
Test the connection
If error check the error in the Log Viewer.
Visual Admin Settings for JDBC connection settings
Visual Admin Link : C:\usr\sap\J2E\JC01\j2ee\admin\go.bat
Log Viewer : C:\usr\sap\J2E\JC01\j2ee\admin\logviewer-standalone\logviewer.bat
J2EàServeràServicesàJDBC Connector
Run Time àResourcesà Driversà click on Create New Driver icon on top
DB Driver : com.microsoft.jdbc.sqlserver
Choose a jar file, select yes for adding more jar files 2 times.
Include 3 jar files from the earlier installed jdbc driver from ms.
Msbase.jar
Mssqlserver.jar
Msutil.jar
Go to Connection Container service
Runtimeà ConnectorsàConnector 1.0
Expand sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc
Select SDK_JDBC
Click on Clone icon above
Name new one as SDK_MSSQL_JDBC
Go to Resource Adapter Tab à Loader Referencesà Add Library:com.microsoft.jdbc.sqlserver
Go to Managed Connection Factoryà Properties tab
Driver Name : Value : com.sap.portals.jdbc.sqlserver.SQLServerDriver
URL jdbc:sap:sqlserver://<sqlserverip>:1433;DatabaseName=SurveyDB1
Username : Administrator
Password : <password>
Save
Deploy service-> JDBC Connectorà sap.com/com.sap.jdo
Stop Application
Start Application