SqlClient

Blended Admin supports the following relational databases

  • Sqlite
  • MySql
  • MariaDb
  • Postgres
  • Oracle
  • SqlServer
  • DB2
  • Odbc

To make a query to a database, first you need to create a instance of SqlClient passing the provider and connection string. You can do it in the following way:

  • Sqlite
    //using connectionString
    var sqlClient = new  SqlClient({provider:'Sqlite',connectionString:'Data Source = database.db;'});
      
    //using properties
    var sqlClient = new  SqlClient({provider:'Sqlite', dataSource:'database.db'});
    
  • MySql
    //using connectionString
    var sqlClient = new  SqlClient({provider:'MySql',connectionString:'SERVER=ServerUrl;DATABASE=DatabaseName;UID=UserId;PASSWORD=Password;'});
      
    //using properties
    var sqlClient = new  SqlClient({
                          provider:'MySql',
                          server:'ServerUrl',
                          database:'DatabaseName',
                          user:'UserId',
                          password:'Passowrd'});
                            
    //using connectionUrl
    var sqlClient = new  SqlClient({connectionUrl:'mysql://UserId:Password@ServerUrl/DatabaseName'});
    
  • MariaDb
    //using connectionString
    var sqlClient = new  SqlClient({provider:'MariaDb',connectionString:'SERVER=ServerId;DATABASE=DatabaseName;UID=UserId;PASSWORD=Password;'});
      
    //using properties
    var sqlClient = new  SqlClient({
                          provider:'MariaDb',
                          server:'ServerUrl',
                          database:'DatabaseName',
                          user:'UserId',
                          password:'Passowrd'});
                            
    //using connectionUrl
    var sqlClient = new  SqlClient({connectionUrl:'mariadb://UserId:Password@ServerUrl/DatabaseName'});
    
  • Postgres
    //using connectionString
    var sqlClient = new  SqlClient({provider:'Postgres',connectionString:'SERVER=ServerId;DATABASE=DatabaseName;UID=UserId;PASSWORD=Password;'});
      
    //using properties
    var sqlClient = new  SqlClient({
                          provider:'Postgres',
                          server:'ServerUrl',
                          database:'DatabaseName',
                          user:'UserId',
                          password:'Passowrd'});
                            
    //using connectionUrl
    var sqlClient = new  SqlClient({connectionUrl:'postgres://UserId:Password@ServerUrl/DatabaseName'});
    
  • Oracle
    //using connectionString
    var sqlClient = new  SqlClient({provider:'Oracle',connectionString:'SERVER=ServerId;DATABASE=DatabaseName;UID=UserId;PASSWORD=Password;'});
      
    //using properties
    var sqlClient = new  SqlClient({
                          provider:'Oracle',
                          server:'ServerUrl',
                          database:'DatabaseName',
                          user:'UserId',
                          password:'Passowrd'});
                            
    //using connectionUrl
    var sqlClient = new  SqlClient({connectionUrl:'oracle://UserId:Password@ServerUrl/DatabaseName'});
    
  • SqlServer
    //using connectionString
    var sqlClient = new  SqlClient({provider:'SqlServer',connectionString:'SERVER=ServerId;DATABASE=DatabaseName;UID=UserId;PASSWORD=Password;'});
      
    //using properties
    var sqlClient = new  SqlClient({
                          provider:'SqlServer',
                          server:'ServerUrl',
                          database:'DatabaseName',
                          user:'UserId',
                          password:'Passowrd'});
                            
    //using connectionUrl
    var sqlClient = new  SqlClient({connectionUrl:'sqlserver://UserId:Password@ServerUrl/DatabaseName'});
    
  • DB2
    //using connectionString
    var sqlClient = new  SqlClient({provider:'DB2',connectionString:'SERVER=ServerId;DATABASE=DatabaseName;UID=UserId;PASSWORD=Password;'});
      
    //using properties
    var sqlClient = new  SqlClient({
                          provider:'DB2',
                          server:'ServerUrl',
                          database:'DatabaseName',
                          user:'UserId',
                          password:'Passowrd'});
                            
    //using connectionUrl
    var sqlClient = new  SqlClient({connectionUrl:'db2://UserId:Password@ServerUrl/DatabaseName'});
    
  • Odbc
    //using connectionString
    var sqlClient = new  SqlClient({provider:'Odbc',connectionString:'Driver={MySQL ODBC 5.1 Driver};SERVER=ServerUrl;DATABASE=DatabaseName;UID=UserId;PASSWORD=Password;'});
      
    //using properties
    var sqlClient = new  SqlClient({
                          provider:'Odbc',
                          driver:'{MySQL ODBC 5.1 Driver}',
                          server:'ServerUrl',
                          database:'DatabaseName',
                          user:'UserId',
                          password:'Password'});
                            
    //using connectionUrl
    var sqlClient = new  SqlClient({connectionUrl:'mysql://UserId:Password@ServerUrl/DatabaseName'});
    

Run a query

When your instance of SqlClient is initialized, you can run a query in few ways.

To run a query without any prameter, you can simply pass it as the first argument of the query function.

  var rows = sqlClient.query('select * from employees');
  console.log(rows);   // [{Id:1, Name:'John'},{Id:2, Name:'Mike'},...]

Running a query with parameters is very similar, you just have to define them as a object.

  // first version
  var rows = sqlClient.query('select * from employees where Id=@Id', {Id:1});
  console.log(rows);   // [{Id:1, Name:'John'}]
 
  // second version
  var rows = sqlClient.query({
    sql:'select * from employees where Id=@Id', 
    parameters:{Id:1}
  });
  console.log(rows);   // [{Id:1, Name:'John'}]

It’s worth to mention that when you run a select query that returns big amount of data, it can be slow as the query function reads and returns all the rows.

Run a cursor

To process bigger set of data, can be more efficient using a cursor. Let’s take a look at few examples below.

You can use hasNext and next methods to iterate over all records.

 var cursor = sqlClient.cursor('select * from employees');
 while(cursor.hasNext())
 {
   var item = cursor.next();
   console.log(item);
 }
 cursor.close();
 // {Id:1, Name:'John'}
 // {Id:2, Name:'Mike'}

The same can be achieved using each method.

var cursor = sqlClient.cursor('select * from employees');
cursor.each(function(item) {
 console.log(item);
});
cursor.close();
// {Id:1, Name:'John'}
// {Id:2, Name:'Mike'}

It’s also pissible to read only the first item.

var cursor = sqlClient.cursor('select * from employees where Id=@Id', {Id:1});
var item = cursor.first();
cursor.close();
console.log(item); // [{Id:1, Name:'John'}]

Multiline quries

Use grave accent to write multilines queries

 var rows = sqlClient.query(`select * 
 from employees`);
 console.log(rows);   // [{Id:1, Name:'John'},{Id:2, Name:'Mike'},...]

Connect and End

When a query or cursor is run, the client connects to the database automaticaly, and the connection is close when the script is finished. Behaviour can be controlled with connect() and end() methods.

  sqlClient.connect();
  
  sqlClient.query(...);
  
  sqlClient.end();

Transactions

To run several queries in a transaction, first you need to connect to a database and then start transaction. After running all queries, commit can commit the transaction.

  sqlClient.connect();
  sqlClient.transaction();
  
  sqlClient.query('INSERT ...');
  sqlClient.query('UPDATE ...');
  
  sqlClient.commit();

Transaction can also be rollback at any point.

  sqlClient.connect();
  sqlClient.transaction();
  
  sqlClient.query('INSERT ...');
  sqlClient.query('UPDATE ...');
  
  sqlClient.rollback();

Error handling

In case of any error, the client throws error. You have to use try-catch statement to handle them.

  try {
    var sqlClient = new  SqlClient({provider:'MySql',connectionString:'Server=blabla;Database=blabla;UID=blabla;PASSWORD=blabla;'});
    sqlClient.connect();
  } catch(err) {
    console.log(err); // Cannot connect to the database......
  }