[+]
[+]
[+]
[-]
  
  
 [+]
 [+]
 [+]
  
  
  
 [+]
 [+]
 [+]
 [-]
   
   
   
   
   
   
   
   
   
  [+]
   
   
   
   
 [+]
 [+]
 [+]
  
 [+]
  
Updated on 8/14/2019
Workflow Documentation
SQL on External Databases
Direct link to topic in this publication:

PREVIOUS  |  NEXT

SQL on External Databases

Quick Links


In this section, we provide instruction, methods, and code samples to demonstrate how you can connect to a database and execute SQL operations. We describe how you can create a datasource parameter map and provide the syntax necessary for the database object to access the database. We also explain how to define custom parameters using Workflow setup tools and then how to use those custom parameters to establish a connection to the Microsoft SQL Server  ® database and the Oracle ® database. 

In this first section, we focus on creating a datasource parameter map and defining custom datasource parameters using setup tools. 

Database Drivers

[Back to top]

You are required to provide a database driver class. The following drivers are available:

  • oracle.jdbc.OracleDriver
  • net.sourceforge.jtds.jdbc.Driver
  • org.postgresql.Driver
  • org.h2.Driver

Contact Workflow Development if you intend to use a driver that isn't listed above.

Datasource

[Back to top]

There are two ways to establish a database connection in your scripts. You can:

  • Provide a datasource parameter map in a script.
  • Define datasource parameters as custom parameters in Setup > System Settings > Organization Settings

See the following code samples for the syntax. 


Create a Datasource Parameter Map 

[Back to top]

Use the following code sample to create a datasource parameter map. Line 11 provides the syntax for the database object to access the database.

//Set the parameters
def params = [ 
  	"url" : "jdbc:oracle:thin:127:0:0:1:1521:mydatabase", 
  	"username" : "myAdminUser",
  	"password": "Password123",
  	"driverClassName": "oracle.jdbc.OracleDriver",
    "jdbcInterceptors": "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
	]

//Use the db object to access the database
def db = resp.dbConnect(params);
  
  


Define Custom Datasource Parameters 

[Back to top]

You can also define datasource parameters as custom parameters in Workflow. To define the parameters:

  1. Login to Workflow as an administrator.
  2. Go to Setup > System Settings > Organization Settings and select the Custom Parameters tab.
  3. Select a name for your datasource, such as datasource.dsg.
  4. Click the New Param button.
  5. Create each of the following custom datasource parameters for the Microsoft ® SQL Server Database, ensuring the prefix matches the name you select for the datasource.

 LabelValue Type
datasource.[DATASOURCE_NAME].urljdbc:jtds:sqlserver://[DB-SERVER-IP]/[DATABASE-NAME];instance=[DATABASE-INSTANCE]Text
datasource.[DATASOURCE_NAME].password Your DB passwordPassword
datasource.[DATASOURCE_NAME].usernameYour DB usernamePassword
datasource.[DATASOURCE_NAME].driverClassName net.sourceforge.jtds.jdbc.DriverText


Your list of custom parameters should resemble the following:



You could also create the following custom datasource parameters to establish a connection with the Oracle ® Database. Ensure that the prefix matches the name you select for the datasource.

 LabelValueType
datasource.[DATASOURCE_NAME].urljdbc:oracle:thin:@[DB-SERVER-IP]:[DB-PORT]:[DATABASE-NAME] Text
datasource.[DATASOURCE_NAME].password Your DB passwordPassword 
datasource.[DATASOURCE_NAME].usernameYour DB usernamePassword
datasource.[DATASOURCE_NAME].driverClassName oracle.jdbc.OracleDriver Text


Your list of custom parameters should resemble the following:


Workflow only supports the following parameters: usernamepasswordURL, and driverClassName

After you create your custom parameters, such as those described in step 5 above, you can invoke the database by calling the following method:

db = resp.dbConnect ('datasource.mydb'); 


Now that you've created your database object, you are able to use it in a script to execute various types of SQL operations against the database. See Database Queries for detailed information about using SQL operations.


Related Articles