The SQL Connector service enables you to connect to SQL based database servers to both read and write information that can be used in your applications.
sql.execute() function in triggers.sql.select().The SQL Connector service contains the information required to establish a service connection to your database.
The SQL driver supports multiple formats, specifically URL and ADO format.
# example in ADO format
server=tartabit.database.windows.net;user id=<myuser>;password=<mypass>;port=1433;database=tartabit;
# example un URL format
sqlserver://<myuser>:<mypass>@tartabit.database.windows.net?database=tartabit
Execute a statement that does not return rows
// insert record into "mytable" with a simple string.
var rslt = sql.execute('myservice', 'INSERT INTO mytable(Ts,imei,temp) VALUES("2023/03/05 12:33:52", "3250125125122", 25.2')
// rslt.rowsAffected tells you how many rows were affected by the statement.
// insert using a parameterized string
var params = { ts: "2023/03/05 12:33:52", imei: "3250125125122", temp: 25.2}
sql.execute('myservice', 'INSERT INTO mytable(Ts,imei,temp) VALUES( @ts, @imei, @temp)')
Query values from the database. The return will be an object with a field rows that contains an array of javascript objects where the key is the column name.
// insert record into "mytable" with a simple string.
var rslt = sql.select('myservice', 'SELECT * FROM mytable WHERE imei="3250125125122"')
// if there is an error sql.select() will throw an exception, if there are no rows an object will be returned and you must check rslt.count to see if there are results.
rslt.rows.foreach(function (item) {
// the results are formatted as an array of javascript objects with the field names matching the column names.
log.trace( 'found imei ' + item.imei + 'with temp ' +item.temp)
})
The following events are considered billable: