Logical System Configuration (.sjdbc)

Configuring JDBC logical systems

In general a JDBC logical system can be configured via AdapLink GUI editor for JDBC Config.sjdbc and it gets stored in AdapLink configuration table yalklsco.

The following are examples of logical system configuration. Please replace with relevant values based upon your specific requirements.

Connecting to MSSQL database via JDBC Type 4 driver

In this example, the logical system name is assumed as LOG_SYS3, the database name is assumed as testDB and the TCP/IP port is assumed as 1433 and IP address is assumed as 127.0.0.1, username is assumed to be user1, and password in encrypted format.

Using Microsoft SQL Server 2005 JDBC Driver:

Parameter	Value
Logical System ID	LOG_SYS3
DB URL	sqlserver://127.0.0.1:1433;database=testdb;integratedSecurity=false;
DB User	user1
DB Password	*****
DB Driver	com.microsoft.sqlserver.jdbc.SQLServerDriver
DBR Key	MSSQL
Use SQL Batch for Insert / Update	[X]
Use StoredProcedure For Update	[X]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras	DB_SCHEMA=dbo
Description

Note

Query parameter DB_SCHEMA was introduced only after the AdapLink 4.4.0; So the respective jar update needs to be applied over a 4.4.0 service install.

Connecting to MySQL database via JDBC Type 4 driver

In this example, the logical system name is assumed as LOG_SYS2.

Parameter	Value
Logical System ID	LOG_SYS2
DB URL	mysql://localhost:3306/db1
DB User	root
DB Password	*****
DB Driver	com.mysql.jdbc.Driver
DBR Key	MYSQL
Use SQL Batch for Insert / Update	[]
Use StoredProcedure For Update	[]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras
Description

Connecting to Oracle database via JDBC Type 4 thin driver

In this example, the logical system name is assumed as LOG_SYS4, the connection is via thin driver, the database name is assumed as JTEST and the TCP/IP port is assumed as 1527 and IP address is assumed as 192.168.0.1, username is assumed to be user1, and password in encrypted format. Please replace with relevant values based upon your specific requirements.

Connection is via thin driver:

Parameter	Value
Logical System ID	LOG_SYS4
DB URL	oracle:thin:@192.168.0.1:1527:JTEST
DB User	user1
DB Password	*****
DB Driver	oracle.jdbc.driver.OracleDriver
DBR Key	ORACLE
Use SQL Batch for Insert / Update	[X]
Use StoredProcedure For Update	[]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras	DB_SCHEMA=SCHEMA_NAME_IN_UPPERCASE
Description

If DB_SCHEMA is not supplied then the db user name is assumed to be the default schema.

Connecting to MS Access database via ODBC driver

The logical system name is assumed as LOG_SYS1 and the data source name is assumed as data_source_name1.

Using JDBC - ODBC Bridge Driver:

Parameter	Value
Logical System ID	LOG_SYS1
DB URL	odbc:data_source_name1
DB User
DB Password
DB Driver	sun.jdbc.odbc.JdbcOdbcDriver
DBR Key	MSAccess
Use SQL Batch for Insert / Update	[]
Use StoredProcedure For Update	[]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras
Description

Using hxtt Access Driver:

Parameter	Value
Logical System ID	LOG_SYS_A
DB URL	access:/c:/TestData.mdb
DB User
DB Password
DB Driver	com.hxtt.sql.access.AccessDriver
DBR Key	MSAccess
Use SQL Batch for Insert / Update	[]
Use StoredProcedure For Update	[]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras
Description

Connecting to remote MS Access database via JDBC Type 3 RMI JDBC objectweb driver

For more details on “RMI JDBC Objectweb” visit, http://rmijdbc.objectweb.org/.

In this example, the logical system name is assumed as LOG_SYS5, the connection is via RMI JDBC ObjectWeb driver, Which in turn connects to a remote MS Access database via ODBC.

Here the remote data source name is assumed as s5, username is assumed to be user1, and password in encrypted format.

Connection is vremote MS Access database via JDBC Type 3 RMI JDBC objectweb driver:

Parameter	Value
Logical System ID	LOG_SYS5
DB URL	rmi://192.168.0.1/jdbc:odbc:s5
DB User	user1
DB Password	*****
DB Driver	org.objectweb.rmijdbc.Driver
DBR Key
Use SQL Batch for Insert / Update	[]
Use StoredProcedure For Update	[]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras
Description

Connecting to remote Progress 9.1 database

In this example, the logical system name is assumed as LOG_PRO91.

Connection to Progress 9.1 database:

Parameter	Value
Logical System ID	LOG_PRO91
DB URL	jdbcprogress:T:hostname:1234:dbname
DB User	root
DB Password	*****
DB Driver	com.progress.sql.jdbc.JdbcProgressDriver
DBR Key	PROGRESS
Use SQL Batch for Insert / Update	[]
Use StoredProcedure For Update	[]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras	DB_SCHEMA=PUB
Description

Connecting to remote Progress 10.1B database using OpenEdge JDBC type 4 Driver

For more details on OpenEdge type 4 driver visit http://www.psdn.com/library/entry!default.jspa?categoryID=254&externalID=839&fromSearchPage=true.

In this example, the logical system name is assumed as LOG_SYS6

Connection to Progress 10.1B database using OpenEdge:

Parameter	Value
Logical System ID	LOG_SYS6
DB URL	datadirect:openedge://localhost:5162;databaseName=mydb1
DB User	user1
DB Password	*****
DB Driver	com.ddtek.jdbc.openedge.OpenEdgeDriver
DBR Key | PROGRESS |
Use SQL Batch for Insert / Update	[]
Use StoredProcedure For Update	[]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras	DB_SCHEMA=PUB
Description

Connecting to remote AS400 using JTOpen

JTOpen is the Open Source version of the IBM Toolbox for Java

For more details visit http://jt400.sourceforge.net/.

In this example, the logical system name is assumed as LOG_AS400

Connection to remote AS400 using JTOpen:

Parameter	Value
Logical System ID	LOG_AS400
DB URL	as400://hostname/libraryName
DB User	user
DB Password	*****
DB Driver	com.ibm.as400.access.AS400JDBCDriver
DBR Key | AS400 |
Use SQL Batch for Insert / Update	[X]
Use StoredProcedure For Update	[]
Auto Commit Only	[X]
Ignore Batch fetch Hint	[]
Extras
Description

Connecting to PostgreSQL database via JDBC Type 4 driver

PostgreSQL JDBC driver download page.

We are using JDBC3 PostgreSQL Driver. The three here implies JDBC 3 specification and not Type 3.

In this example, the logical system name is assumed as LOG_SYS9

Connection to PostgreSQL database via JDBC Type 4 driver:

Parameter	Value
Logical System ID	LOG_SYS9
DB URL	postgresql://localhost:27095/db1
DB User	user1
DB Password	*****
DB Driver	org.postgresql.Driver
DBR Key	POSTGRES
Use SQL Batch for Insert / Update	[]
Use StoredProcedure For Update	[]
Auto Commit Only	[]
Ignore Batch fetch Hint	[]
Extras
Description