Database Adapter Actions

Database Adapter Actions

createTableDefinition

Create a database table definition.

# 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.

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.

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.

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.

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.

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.

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.

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

# 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