RapidIdentity Product Guide: New UI

Database Adapter Actions

Create a database table definition.

Property

Value

Description

table*

text, expression, variable

the name of the table

schema

text, expression, variable

the name of the schema that table is in

catalog

text, expression, variable

the name of the catalog the table is in

key*

text, expression, variable

comma separated list of primary key columns format for each column: [alias=]name

keygen

choice (assigned, native), text, expression, variable

the primary key generation algorithm - one of 'assigned','native' (default: 'assigned')(ignored if multiple key columns specified)

columns*

text, expression, variable

comma separated list of other columns format for each column: [alias=]name[;child-table;join-col[;parent-join-col]]

returnVariable

expression, variable

name of the variable to be assigned to the return value

Example

# With auto-increment
tableDef = createTableDefinition("senators", "id", "native", 
    "lastname,firstname,title,email")
# Without auto-increment
tableDef = createTableDefinition("senators", "email", "assigned",     
    "lastname,firstname,title,email") 

Delete Record from the database.

Property

Value

Description

dbConnection*

expression, variable

the database connection

table*

text, expression, variable

the database table

key*

text, expression, variable

the primary key of the Record to delete - also accepts a Record that contains the primary key

returnVariable

expression, variable

name of the variable to be assigned to the return value

Example

tableDef = createTableDefinition("usr", "id"," native", 
    "fname,lname,status")
outputDB = openDatabaseConnection(...)
queryExample = createRecord()
setRecordValue(queryExample, "status", "I")
inactiveRecords = getDatabaseRecords(outputDB, "usr", queryExample)
forEach(inactive, inactiveRecords) {
delStatus = deleteDatabaseRecord(outputDB, "usr", inactive)
if(Boolean(delStatus)) {
    log("Deletion Successful!")
} else {
    log("Deletion Failed!")
}
close(outputDB) 

Get a Record from the database.

Property

Value

Description

dbConnection*

expression, variable

the database connection

table*

text, expression, variable

the database table

key*

text, expression, variable

the primary key of the Record - also accepts a Record that contains the primary key

returnVariable

expression, variable

name of the variable to be assigned to the return value

Example

tableDef = createTableDefinition("usr", "email", "assigned", 
    "fname,lname,status,email")
outputDB = openDatabaseConnection(...)
resultRecord = getDatabaseRecord(outputDB, "user", 
    "john.doe@example.com")
resultFName = getRecordFieldValue(resultRecord, "fname")

Get Records from the database.

Property

Value

Description

dbConnection*

expression, variable

the database connection

table*

text, expression, variable

the database table

example

expression, variable

example Record

ignoreCase

boolean, expression, variable

Ignore case when matching example fields (default: false)

maxResults

expression, variable

maximum number of Records to return (default: unlimited)

returnVariable

expression, variable

name of the variable to be assigned to the return value

Example

tableDef = createTableDefinition("usr", "id","native",
    "fname,lname,status")
outputDB = openDatabaseConnection(...)
queryExample = createRecord()
setRecordValue(queryExample, "status", "I")
inactiveRecords = getDatabaseRecords(outputDB, "usr", 
    queryExample)
log("There are " + inactiveRecords.length + " inactive records.")
forEach(inactive,inactiveRecords) {
    delStatus = deleteDatabaseRecord(outputDB,"usr",inactive)
}
close(outputDB)

Open a connection to a database.

Property

Value

Description

jdbcDriverClass*

text, expression, variable

the name of JDBC driver class to use to connect to the database

jdbcURL*

text, expression, variable

the JDBC connection URL

tableDefinitions

text, expression, variable

the definitions of the tables that connection will use

user

text, expression, variable

the user name for authenticating to the database

password

password, string, expression, variable

the user password for authenticating to the database

extraProperties

expression, variable

object or Record containing extra Hibernate properties to customize the connection

returnVariable

expression, variable

name of the variable to be assigned to the return value

Example

tableDef = createTableDefinition("usr", "id", "native", 
    "fname,lname,status")
# MySQL Database example
outputDB = openDatabaseConnection("com.mysql.jdbc.Driver",
    "jdbc:mysql://127.0.0.1/indirect", tableDef, "root", "password")
if(Boolean(outputDB)) {
    log(Database connection successful!)
} else {
    log(Database connection failed!)
}

Get Records from the database using SQL.

Property

Value

Description

dbConnection*

expression, variable

the database connection

sql*

text, expression, variable

the SQL query

params

expression, variable

array of positional parameters to bind to the SQL query

returnVariable

expression, variable

name of the variable to be assigned to the return value

Example

tableDef = createTableDefinition("usr", "id", "native", 
    "fname,lname,status")# MySQL Database example
outputDB = openDatabaseConnection("com.mysql.jdbc.Driver", 
    "jdbc:mysql://127.0.0.1/indirect", tableDef, "root", "password")
if (Boolean(outputDB)) {
    id = "12345"
    sql = "SELECT fname,lname,status FROM usr WHERE id='" + id + "'"
    users = queryDatabaseSQL(outputDB, sql)
    forEach(user, users) {
        log("User: " + user)
    }
} else {
}
close(outputDB)

### RapidIdentity Connect supports parameterized queries. 
    For example, in each iteration of the forEach loop, log 
    the user 's name to be their first and last name 
    separated by a single space. Parameters (params) could 
    be used in the example above as follows.
sql = "SELECT fname,lname,status FROM usr WHERE ID = ?"
users = queryDatabaseSQL(outputDB, sql, [user.fname + " "
    user.lname, user.id
]) 
tableDef = createTableDefinition("usr", "id", "native", 
    "fname,lname,status")
# MySQL Database example
outputDB = openDatabaseConnection("com.mysql.jdbc.Driver", 
    "jdbc:mysql://127.0.0.1/indirect", tableDef, "root", "password")
if (Boolean(outputDB)) {
    id = "12345"
    sql = "SELECT fname,lname,status FROM usr WHERE id='" + id + "'"
    users = queryDatabaseSQL(outputDB, sql)
    forEach(user, users) {
        log("User: " + user)
    }
} else {
}
close(outputDB)

Save a Record to the database.

Property

Value

Description

dbConnection*

expression, variable

the database connection

table*

text, expression, variable

the database table

record*

expression, variable

the Record to save

returnVariable

expression, variable

name of the variable to be assigned to the return value

Example

textInput = openDelimitedTextInput("/root/senators.csv", 
    "fname,lname,status,email")
tableDef = ("usr", "email", "assigned", "fname,lname,status,email")
outputDB = openDatabaseConnection(...)
forEach(senator, textInput) {
    saveResult = saveDatabaseRecord(outputDB, "user", senator)
    if (Boolean(saveResult)) {
        log("Save Successful!")
    } else {
        Log("Save Failed!")
    }
}
close(outputDB)
close(textInput)

Update database using SQL.

Property

Value

Description

dbConnection*

expression, variable

the database connection

sql*

text, expression, variable

the SQL statement

params

expression, variable

array of positional parameters to bind to SQL statement

returnVariable

expression, variable

name of the variable to be assigned to the return value

Example

tableDef = createTableDefinition("usr", "id", "native", 
    "fname,lname,status")# MySQL Database example
outputDB = openDatabaseConnection("com.mysql.jdbc.Driver", 
    "jdbc:mysql://127.0.0.1/indirect", tableDef, "root", "password")
if (Boolean(outputDB)) {
    id = "12345"
    sql = "SELECT fname,lname,status FROM usr WHERE id='" + id + "'"
    users = queryDatabaseSQL(outputDB, sql)
    forEach(user, users) {
        log("User: " + user)
        updateSQL = "UPDATE usr SET name='" + user.fname + " "
        user.lname + "' WHERE ID='" + user.id + "'"
        result = updateDatabaseSQL(outputDB, updateSQL)
        if (result) {
            log("User updated successfully")
        } else {
            log("User not updated")
        }
    }
} else {
}
close(outputDB)

### RapidIdentity Connect supports parameterized queries. 
    For example, in each iteration of the forEach loop, set the 
    user 's id field value to be their first and last name 
    separated by a single space. Parameters (params) could 
    be used in the example above as follows.
updateSQL = "UPDATE usr SET name = ? WHERE ID = ?"
result = updateDatabaseSQL(outputDB, updateSQL, [user.fname + " "
    user.lname, user.id
])

Example

# Input from text file
textInput = openDelimitedTextInput("/root/senators.csv", 
    "LastName,FirstName,Title,Email,Phone,Description")
# Define table and open DB connection(MySQL w / auto - increment)
tableDef = createTableDefinition("senators", "id", "native", 
    "lastname,firstname,title,email,phone")
outputDB = openDatabaseConnection("com.mysql.jdbc.Driver", 
    "jdbc:mysql://server/dbSchema", tableDef, "root", "password")
# Loop through input records
forEach(inputRecord, textInput) {
    # Schema mapping
    renameRecordFields(inputRecord, "LastName,FirstName,Title,Email,Phone", 
        "lastname,firstname,title,email,phone")
    # Check for existance in target
    queryRecord = createRecord()
    queryValue = getRecordFieldValue(inputRecord, "email")
    setRecordValue(queryRecord, "email", queryValue)
    matchingRecords = getDatabaseRecords(outputDB, "senators", 
        queryRecord)
    if (matchingRecords.length == 1) {
        # Match found.Add primary key to current record from source.
        matchingKeyValue = getRecordFieldValue(matchingRecords[0], "id")
        setRecordFieldValue(inputRecord, "id", matchingKeyValue)
    } else {
        # No match found.Nothing more to do.
    }
    # Write(insert / update) record to database
    saveDatabaseRecord(outputDB, "senators", inputRecord)
}
# Close database and file connections
close(outputDB)
close(textInput)# Input from text file