Database Adapter Actions

RapidIdentity Product Guides - 2019 Rolling Release

Database Adapter Actions

createTableDefinition

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")

deleteDatabaseRecord

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)

getDatabaseRecord

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")

getDatabaseRecords

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)

openDatabaseConnection

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!)
}

queryDatabaseSQL

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)

saveDatabaseRecord

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)

updateDatabaseSQL

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
])

Complete Database Adapter Example

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

In this section:

RapidIdentity Product Guides - 2019 Rolling Release

Database Adapter Actions

createTableDefinition

Example

deleteDatabaseRecord

Example

getDatabaseRecord

Example

getDatabaseRecords

Example

openDatabaseConnection

Example

queryDatabaseSQL

Example

saveDatabaseRecord

Example

updateDatabaseSQL

Example

Complete Database Adapter Example

Example

Search results