Database

◷ Reading Time: 3 minutes

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

By default, MSSQL is available in FlexRule Designer. You can also install the preferred database extensions (e.g. MySQL, PostgreSQL).

database

Creates a connection refers to a database

 database (type, cnn)

• type (String): Specifies the type of database (Mandatory)
  MsSQL and Oracle are available by default. You can also use extenstions to make other databases available such as PostgreSQL, MySQL.
• 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.