Database

◷ Reading Time: 3 minutes

At the expression level as well as Flow level the data can be retrieved from a database.

database

Creates a connection reference to a database

 database (type, cnn)
  • type (String): Specifies the type of database: MsAccess, MsSql, Oracle, Odbc (Mandatory)
  • cnn (String): Provides connection string to the database (Mandatory)
Example:
db:= database('mssql','Data Source=.\\SqlExpress;Initial Catalog=Car-Insurance;User ID=sa;Password=123;MultipleActiveResultSets=True')

Result: It connects to the given database

query

Executes a SELECT query against a database reference.

db .query (query, args)
  • db (Variable): Variable that connected the database (Mandatory)
  • query (String): Database query to exact data (Mandatory)
  • args (Dictionary): Arguments mentioned in the query (Optional. But it is mandatory only if there are any arguments mentioned in the query)
Example: 
db.query('select * from HighRiskCars where id in @ids', {ids:[1,2,3]})

Result:
[
     {
         "Id" : 1,
         "Made" : "Hyundai",
         "Model" : "IX35"
     },
     {
         "Id" : 2,
         "Made" : "BMW",
         "Model" : "E63"
     },
     {
         "Id" : 3,
         "Made" : "Hyundai",
         "Model" : "IX35"
     }
 ]

It allws to query with array of values as parameter.
Example:
db.query('select * from HighRiskCars where id=@id', {id:2})
Result:
[
     {
         "Id" : 2,
         "Made" : "BMW",
         "Model" : "E63"
     }
 ]

It allws to query with an specific ID of record
Example: db.query('select * from HighRiskCars')

Result:
[
     {
         "Id" : 1,
         "Made" : "Hyundai",
         "Model" : "IX35"
     },
     {
         "Id" : 2,
         "Made" : "BMW",
         "Model" : "E63"
     },
     {
         "Id" : 3,
         "Made" : "Hyundai",
         "Model" : "IX35"
     },
     {
         "Id" : 4,
         "Made" : "BMW",
         "Model" : "E63"
     }
 ]

It allws to query with no parameters.

queryExecute

Executes one or more INSERT, UPDATE, and DELETE queries against a database reference.

db .queryExecute (query, args)
  • db (Variable): Variable that connected the database (Mandatory)
  • query (String): Database query (Mandatory)
  • args (Dictionary): Arguments mentioned in the query (Optional. But it is mandatory only if there are any arguments mentioned in the query)
Example: db.queryExecute("INSERT INTO customers (fname, lname) VALUES (@fname, @lname)", {fname:'Alex', lname:'Pell'})
Result: Insert items to a given table.

queryScalar

Executes a query against a database reference and returns a value.

db .queryScalar (query, args)

Returns: The value from the first column of the first row of the query.

  • db (Variable): Variable that connected the database (Mandatory)
  • query (String): Database query (Mandatory)
  • args (Dictionary): Arguments mentioned in the query (Optional. But it is mandatory only if there are any arguments mentioned in the query)
Example: db.queryScalar("INSERT INTO customers (id, fname, lname, age) VALUES (@id, @fname, @lname, @age)", {id: 5, fname:'Alex', lname:'Pell', age:21})
Result: Insert items to a given table.
Updated on January 5, 2021

Was this article helpful?

Related Articles