SQL

Structured Query Language (SQL) is a standard language that is used for communication between an app and a database. The AppFramework Sql QML plug-in provides tools to read and write to SQLite, ODBC, PostgreSQL, and MySQL databases, as well as to read DBF and CSV files, via SQLite virtual tables.

For more examples of this functionality, see the SQL samples in ArcGIS AppStudio. For more information and examples, see our Esri Community blog post, Introduction to SQL Beta in AppStudio 2.0.

To use this functionality, you first need to include the following import statement:

1
import ArcGIS.AppFramework.Sql 1.0

SQLite database

The SqlDatabase component is the central component of the Sql QML plug-in, which provides a large selection of tools to create, open, read, and modify SQL databases in an app. The following code sample creates an SQLite database at a set location:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
FileFolder {
  id: fileFolder
  path: "~/ArcGIS/Data/Sql"
}

SqlDatabase {
  id: db
  databaseName: fileFolder.filePath("sample.sqlite")
}

Component.onCompleted: {
  fileFolder.makeFolder();
  db.open();
}

When created, this database is empty, but you can fill and interact with it using the query method. The following code sample replaces the component.onCompleted object in the previous code sample and populates the created SQLite database with a table of values:

1
2
3
4
5
6
7
Component.onCompleted: {
    fileFolder.makeFolder();
    db.open();
    db.query("CREATE TABLE world_cities (name TEXT,subcountry TEXT, country TEXT)");
    db.query("INSERT INTO world_cities VALUES ('Melbourne', 'Victoria', 'Australia')");
    db.query("INSERT INTO world_cities VALUES ('Redlands', 'California', 'United States')");
}

If your query is a select statement, the first relevant row is returned as a JSON object by default. To return all relevant rows, you must loop through all rows in the table. The following code sample returns all rows as text strings in the table called Roads:

1
2
3
4
5
6
7
var query = db.query("SELECT * FROM Roads");
var ok = query.first();
while (ok) {
  console.log(JSON.stringify(query.values));
  ok = query.next();
}
query.finish();

CSV files

Data in CSV files can be queried by creating a linked table in an SQLite database. Only the metadata (for example, the .csv filename) is stored in SQLite, with the content remaining in the CSV file. The CSV file can be either an embedded resource of your app or an external file that can be accessed by your app. All of the options supported by the FileFolder readCsvFile method, except for dataElementAsArray , are also supported here.

The following code sample creates a linked table to a pre-existing CSV file using FileFolder to store a suitable file path to the CSV file. The :memory: value for the databaseName property specifies the in-memory database, in which a list of world cities is saved.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SqlDatabase {
    id: db
    databaseName: ":memory:"
}

FileFolder {
    id: dataFolder
    url: "data"
}

Component.onCompleted: {
    var csvFilePath = dataFolder.filePath("world-cities.csv");
    db.open();
    db.query("CREATE VIRTUAL TABLE world_cities USING CSV(filepath=%1,ignoreInvalidLines=%2,valueType=%3)".arg(csvFilePath).arg("false").arg("text");
}