...

• Note that the JDBC Driver for SQL Server® has to be downloaded separately as it does not ship with JS7.
• The example makes use of the SQL Server® database server running for hostname sqlserversrv and port 1433.
• Note that use of the above parameters with the JDBC URL is required: sendStringParametersAsUnicode=false;selectMethod=cursor
• For Windows environments, access to SQL Server can be configured to use integrated security, i.e. to use the account that the JOC Cockpit Windows Service is operated for. 
  • Specify empty values for the <property name="hibernate.connection.username"> and <property name="hibernate.connection.password"> elements.
  • Add to the JDBC URL: integratedSecurity=true
  • The account used for the JOC Cockpit Windows Service has to be configured with SQL Server® to allow access to the database.
  • The JDBC Driver distribution usually ships with a library for authentication purposes with a name such as sql_jdbc.dll or  mssql-jdbc_auth-9.2.1.x64.dll or similar. This library should be added to a location that is specified with the Windows PATH environment variable for the JOC Cockpit Windows Service or simply stored to the C:\Windows\System32 directory or to the bin directory of the Java JDK/JRE in use.

Database Setup

In a first step users have to create a database schema and account for JS7. The database schema has to support the Unicode character set.

Setup for MySQL

Database

For the character encoding of the database UTF8 has to be specified. Similarly a collation sequence should be specified that includes UTF8 support.

# add database
CREATE DATABASE js7 CHARACTER SET utf8 COLLATE utf8_general_ci;

Account

# add account
CREATE USER 'jobscheduler'@'%' IDENTIFIED BY 'jobscheduler';

Permissions

The following permissions are required for the schema:

• Owner account
  • Tables, Views, Functions, Stored Procedures: CREATE, DROP, ALTER
• Run-time account
  • Tables, Views: SELECT, INSERT, UPDATE, DELETE
  • Functions, Stored Procedures: EXECUTE
    
    

# grant all permissions
GRANT ALL ON js7.* TO 'jobscheduler'@'%';

grant individual permissions
GRANT CREATE, DROP, ALTER, EXECUTE, SELECT, UPDATE, INSERT, DELETE on js7.* to 'jobscheduler'@'%';

Setup for Oracle

Database

When creating the database then consider to use a UTF based character encoding and collating sequence, for example:

# set up database
CREATE DATBASE JS7
...
CHARACTER SET AL32UTF8;

# consider linguistic sorting for Unicode
ALTER SESSION SET NLS_COMP='LINUGUISTIC';

Accounts

Frequent practice includes to set up two accounts: an owner account that owns the schema and objects and a run-time account that is granted permissions to access objects of the owner schema. Alternatively a single account can be used that owns the schema.

# add owner account
CREATE USER JS7_OWNER IDENTIFIED BY JS7_OWNER;
GRANT CONNECT, RESOURCE TO JS7_OWNER;

# add run-tine account
CREATE USER JS7_USER IDENTIFIED BY JS7_USER;
GRANT CONNECT, RESOURCE TO JS7_USER;

Permissions

The following permissions are required for JS7:

• Owner Account
  • Tables, Views, Sequences: CREATE, DROP
  • Procedures: EXECUTE
• Run-time Account
  • Tables, Views: SELECT, INSERT, UPDATE, DELETE
  • Sequences: SELECT
• If a single account is used then all above permissions have to be assigned.

There are number of ways how to assign permissions, e.g. by use of roles. One option is to create the objects in the owner schema and to generate the respective GRANT commands like this:

# generate SQL statement to grant permissions to run-time account
SELECT DISTINCT
       CASE
            WHEN object_type = 'SEQUENCE' THEN 'GRANT SELECT'
            WHEN object_type IN ('TABLE', 'VIEW') THEN 'GRANT SELECT,INSERT,UPDATE,DELETE'
       END
       || ' ON JS7_OWNER.'|| object_name || ' TO JS7_USER;' 
  FROM dba_objects 
 WHERE object_type IN ('TABLE','VIEW','SEQUENCE') and owner='JS7_OWNER';

Connection Pool

JS7 makes use of a connection pool to allow a larger number of JOC Cockpit user sessions to share database connections from the pool.

...