Skip to end of metadata
Go to start of metadata

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

  • No labels

1 Comment

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


    Open the Server Administration

    http://6nhgl1s:50100/irj/portal


    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


    Open the Server Administration

    http://portalname:50100/irj/portal


    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