Scripted SQL

Overview

The Scripted SQL connector is designed to manage users, groups or other entities stored in a relational DBMS.

Installation

Follow these steps to run the stand-alone connector server:

  1. Download the connector server jar file and store it in the connector server directory (any directory).
  2. Download the Scripted SQL connector jar file and store it in the bundles subdirectory.
  3. Start the connector server. For more information, check the page about this topic.

Supported operations

  • Authenticate
  • ResolveUsername
  • Create
  • Delete
  • Update
  • Search
  • Test
  • Schema
  • Sync
  • ScriptOnConnector
  • UpdateAttributeValues

Configuration

Configuration Properties

The following table describes all of the properties that you can specify for the configuration:

Configuration PropertyTypeRequiredDescription
Authenticate ScriptString
Authenticate groovy script
Authenticate Script File NameString
Path of authenticate groovy script file
Name QuotingString
Select whether database column names for this resource should be quoted, and the quoting characters. By default, database column names are not quoted (None). For other selections (Single, Double, Back, or Brackets), column names will appear between single quotes, double quotes, back quotes, or brackets in the SQL generated to access the database.
Delete ScriptString
Delete groovy script
Search ScriptString
Search groovy script
Update Script File NameString
Path of update groovy script file
Create ScriptString
Create groovy script
Auto commitBoolean
With this parameter set to true, each SQL statement is treated as a transaction. It is automatically committed right after it is executed.
Search Script File NameString
Path of search groovy script file
Create Script File NameString
Path of create groovy script file
Clear Text Password To ScriptBoolean
Should password be passed to scripts in clear text?
Delete Script File NameString
Path of delete groovy script file
Schema Script File NameString
Path of schema groovy script file
Reload Script On ExecutionBoolean

By default, scripts are loaded and compiled when a connector instance is created and initialized.
Setting this parameter to true will make the connector load and compile the script every time it is called.

Use only for test/debug purpose since this can have a significant impact on performance.

Test ScriptString
Test groovy script
Test Script File NameString
Path of test groovy script file
Update ScriptString
Update groovy script
Scripting LanguageString
The scripting language; only default value GROOVY is currently supported.
Sync Script File NameString
Path of sync groovy script file
Sync Script
String
Sync groovy script
HostStringXEnter the name of the host where the database is running.
PortStringXEnter the port number the database server is listening on.
UserStringXEnter the name of the mandatory Database user with permission to account table.
User PasswordGuardedString
XEnter a user account that has permission to access accounts table.
DatabaseString
Enter the name of the database on the database server that contains the table.
JDBC DriverStringXSpecify the JDBC Driver class name. Oracle is oracle.jdbc.driver.OracleDriver. MySQL is org.gjt.mm.mysql.Driver. Could be empty if datasource is provided.
JDBC Connection URLStringXSpecify the JDBC Driver Connection URL. Oracle template is jdbc:oracle:thin:@[host]:[port(1521)]:[DB].  MySQL template is jdbc:mysql://[host]:[port(3306)]/[db], for more info, read the JDBC driver documentation. Could be empty if datasource is provided.
Enable writing empty stringBoolean
Select to enable support for writing an empty strings, instead of a NULL value, in character based columns defined as not-null in the table schema. This option does not influence the way strings are written for Oracle based tables. By default empty strings are written as a NULL value.
Rethrow all SQLExceptionsBoolean
If this is not checked, SQL statements which throw SQLExceptions with a 0 ErrorCode will be have the exception caught and suppressed. Check it to have exceptions with 0 ErrorCodes rethrown.
Native TimestampsBoolean
Select to retrieve Timestamp data type of the columns in java.sql.Timestamp format from the database table.
All nativeBoolean
Select to retrieve all data type of the columns in a native format from the database table.
Validate Connection QueryString
There can be specified the check connection alive query. If empty, default implementation will test it using the switch on/off the autocommit. Some select 1 from dummy table could be more efficient.
Datasource PathString
Enter the JDBC Data Source Name/Path to connect to the Oracle server. If specified, connector will only try to connect using Datasource and ignore other resource parameters specified. The example value is: jdbc/SampleDataSourceName
Initial JNDI PropertiesString
Could be empty or enter the JDBC JNDI Initial context factory, context provider in a format: key = value.