◷ 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.