Database Connection

Using Dialog

1. In the Data view, right-click DATABASE and click Add Store.

2. In the Add Database Connection Information dialog,

3. Fill out connection information as below and click OK.

• Name: order (ODI name)

• Vendor: mysql

• Database IP Address: oz.ozeform.io

• Database Port number: 3306

• Database name: ozdemodb

• User name: designer

• Password: password

Using DB Connection Pool

db.properties

1. Go to the default working folder (C:\Users\username\Documents\OZRepository\) and create db.properties.

2. Add DB connection information below.

db.properties

# for MySQL, MariaDB
# 'demo' will be the Alias name
demo.vendor=mysql
#demo.serverAddress=oz.ozeform.io
demo.serverAddress=127.0.0.1
demo.portNo=3306
demo.dbName=ozdemodb
demo.user=designer
demo.password=password
demo.maxconns=20
demo.initconns=5
demo.timeout=5
demo.initSqls=
demo.closeSqls=
demo.doConnectionCheck=true
demo.testQueryString=select 1
demo.sessionQuery=
demo.encodecharset=
demo.decodecharset=
demo.fetchrow=0
demo.ignoreQueryError=false

Connecting to database

1. In the Add Database Connection Information dialog,

2. Tick the checkbox Alias at the bottom and type in "demo"

3. Select the db.properties from the file browser.

4. Click OK.

Database Information

1. Once connected to the database, tables are listed in the lower part of the Data view.

2. By right-clicking a table name you can see table field information or data rows (max 100 rows by default).

3. To change the max row count, go to File tab menu, select Options > Environment > General.

Connecting to SQL Server

Get the appropriate JDBC driver for your SQL Server version from Microsoft.

For Designer

1. Put the driver .jar file into OZ Query Designer 9.0/UniversalDriver

2. Open OZ Query Designer 9.0/config/launch.cfg

3. Replace sqljdbc.jar with the downloaded jar and save.

4. Add database alias definition in the db.properties

db.properties

mssql.vendor=USER
mssql.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
mssql.url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=dbname;encrypt=false;
mssql.user=sa
mssql.password=password
mssql.maxconns=20
mssql.initconns=5
mssql.timeout=5
mssql.initSqls=
mssql.closeSqls=
mssql.doConnectionCheck=true
mssql.testQueryString=select 1
mssql.sessionQuery=
mssql.encodecharset=
mssql.decodecharset=
mssql.fetchrow=0
mssql.ignoreQueryError=false
mssql.encrypt=false

For Server

1. Put the downloaded jar file into WEB-INF/lib

2. Restart tomcat for the server

Connecting to Oracle12C

1. Get ojdbc6.jar

2. Put it in C:\Program Files (x86)\Forcs\OZ Report 6.0\OZ Query Designer 6.0\UniversalDriver

3. Delete classes12.jar and classes12.zip from UniversalDriver.

4. Open ..\config\launch.cfg, find "classes.zip", and change it to "ojdbc6.jar".

5. Add database alias definition in the db.properties

db.properties

ora.vendor=oracle
ora.serverAddress=end-point
ora.portNo=1521
ora.sid=ORCL
ora.user=user
ora.password=password
ora.maxconns=20
ora.initconns=5
ora.timeout=5
ora.doConnectionCheck=false
ora.sessionQuery=
ora.doConnectionCheck=
ora.testQueryString=
ora.decodecharset=
ora.encodecharset=
ora.fetchrow=
ora.ignoreQueryError=false

Database Connection (6.0)

Using Dialog

1. In the Data view, right-click DATABASE and click Add Store.

2. In the Add Database Connection Information dialog,

3. Fill out connection information as below and click OK.

• Name: order (ODI name)

• Vendor: user

• Driver Class Name: com.mysql.jdbc.Driver

• Connection URL: jdbc:mysql://127.0.0.1:3306/ozdemodb

• User name: username

• Password: password

Using DB Connection Pool

db.properties

1. Go to the default working folder (C:\Users\username\Documents\OZRepository\) and create db.properties.

2. Add DB connection information below.

db.properties

demo.vendor=USER
demo.driver=com.mysql.jdbc.Driver
demo.url=jdbc:mysql://127.0.0.1:3306/ozdemodb
demo.user=user
demo.password=password
demo.maxconns=20
demo.initconns=5
demo.timeout=5
demo.initSqls=
demo.closeSqls=
demo.doConnectionCheck=true
demo.testQueryString=select 1
demo.sessionQuery=
demo.encodecharset=
demo.decodecharset=
demo.fetchrow=0
demo.ignoreQueryError=false