Working with Relational Databases via SQL

From ADS scripts, the built-in object SQL can be used to connect to relational databases through SQL. This may be used to implement batch importers or update scripts. Two functions are provided right now

• SQL.query for Querying SQL Databases from ADS

• SQL.update for Updating SQL Databases with ADS

Querying SQL Databases from ADS

The following example walks through all rows of the table called country in the database called world and creates one instance of the RDFS class g:Country for each, based on the values of the Code and Name columns.

let conn = {
    url: 'jdbc:mysql://localhost:3306/world', 
    user: 'newuser',
    password: '...'
}
let sql = `
    SELECT Code, Name
    FROM country;
`
let results = SQL.query(conn, sql);
results.forEach((row) => {
    g.createCountry({
        isoCountryCode3: row.Code,
        prefLabel: row.Name
    })
})

Updating SQL Databases with ADS

The following example shows the syntax of SQL update commands. As usual, you may want to use JavaScript string substitution to produce a suitable SQL string from your data.

let conn = {
    url: 'jdbc:mysql://localhost:3306/world', 
    user: 'newuser',
    password: '...'
}
let sql = `
	DROP TABLE country;
`
SQL.update(conn, sql);