Now Particularly Misnamed

    mssql-functions

    1.6.3 • Public • Published

    mssql-functions

    A simple wrapper for tedious (Node TDS module for connecting to SQL Server databases). All functions are Promises.

    Installation

    From npm.

    npm install mssql-functions
    

    Or from git.

    npm install roboulbricht/mssql-functions
    

    class TDatabase

    Function: constructor(config)

    Property: types

    Returns tedious TYPES.

    Function: connect()

    Establishing the connection to the database.

    var Database = require('mssql-functions');
    
    var connection_string = {
      authentication: {
        type: "default",
        options: {
          userName: "***",
          password: "***",
        }
      },
      server: "***",
      options: {
        database: "***",
        instanceName: "***"
      }
    }
    
    var db = new Database(connection_string);
    db.connect()
        .then(function() {
            console.log('connected');
        })
        .catch(function(error) {
            console.log(error.message);
        });

    Function: disconnect()

    Close the connection to the database.

    Function: query(sql, params, config)

    Execute the query which returns the result table.

    • sql {String} The SQL statement to be executed.
    • params {Array[]} An array of arrays containing the parameter definitions.
    • config {Object}
      • columns {true, false} Returns object with columns and rows.
    var t1 = await db.query('select * from #pokus');
    console.log(t1);
    var t2 = await db.query('select * from #pokus where id=@id', [
      ['id', db.types.Int, 2]
    ]);
    console.log(t2);
    var t3 = await db.query('select * from #pokus', undefined, {columns: true});
    console.log(t3);

    Function: queryInt(sql, id)

    Execute the query which returns the result table with one integer parameter. Always use @id as parameter in SQL.

    • sql {String} The SQL statement to be executed.
    • id {Int} An integer number.
    var t = await db.queryInt('select * from #pokus where id=@id', 2);

    Function: queryLM(sql, params, rowfunc)

    Execute the query in low memory mode.

    • sql {String} The SQL statement to be executed.
    • params {Array[]} An array of arrays containing the parameter definitions.
    • rowfunc {Function(row)} Callback
    var t = await db.queryLM('select * from uzivatelia', undefined, (row) => {
      console.log(row);
    });

    Function: execute(sql, params)

    Execute the query without returning the result table. Good for insert queries.

    • sql {String} The SQL statement to be executed.
    • params {Array[]} An array of arrays containing the parameter definitions.
    await db.execute('insert into #pokus(id,name) values (1, \'one\')');
    await db.execute('insert into #pokus(id,name) values (@id, @name)', [
      ['id', db.types.Int, 2],
      ['name', db.types.VarChar, 'two']
    ]);

    Function: executeInt(sql, id)

    Execute the query without returning the result table with one integer parameter. Always use @id as parameter in SQL.

    • sql {String} The SQL statement to be executed.
    • id {Int} An integer number.
    db.executeInt('delete from #pokus where id=@id', 1);

    Function: executeBatch(sql)

    Execute the query without returning the result table. There is no param support, but it is the only way to create temporary tables. See the original documentation.

    var Database = require('mssql-functions');
    
    var connection_string = {
      authentication: {
        type: "default",
        options: {
          userName: "***",
          password: "***",
        }
      },
      server: "***",
      options: {
        database: "***",
        instanceName: "***"
      }
    }
    
    db.connect()
      .then(async function() {
        console.log('connected');
        try {
        await db.beginTransaction();
        console.log('in transaction');
    
        await db.executeBatch('create table #pokus(id int, name varchar(255))');
        await db.execute('insert into #pokus(id,name) values (1, \'one\')');
        await db.execute('insert into #pokus(id,name) values (2, \'two\')');
        var t = await db.query('select * from #pokus');
        console.log(t);
    
        await db.commitTransaction();
        console.log('commit');
        } catch(error) {
          await db.rollbackTransaction();
          console.log('error in transaction', error.message);
        }
      })
      .catch(function(err) {
        console.log('error', err.message);
      });

    Function: bulkLoad(table, options, columns, data)

    Bulk load data.

    var Database = require('mssql-functions');
    
    var connection_string = {
      userName: "***",
      password: "***",
      server: "***",
      options: {
        database: "***",
        instanceName: "***"
      }
    }
    
    db.connect()
      .then(async () => {
        console.log('connected');
        await db.executeInt('delete from osoby where firma_id=@id', 9999);
    
        var options = { keepNulls: true };
        var columns = [
          ['firma_id', db.types.Int, { nullable: true }],
          ['meno', db.types.NVarChar, { length: 50, nullable: true }],
          ['priezvisko', db.types.NVarChar, { length: 50, nullable: true }]
        ];
        var data = [
          { firma_id: 9999, meno: 'M1', priezvisko: 'P1' },
          { firma_id: 9999, meno: 'M2', priezvisko: 'P2' }
        ];
        let res = await db.bulkLoad('osoby', options, columns, data);
    
        db.disconnect();
      })
      .catch(function(err) {
        console.log('error', err.message);
      });

    Function: identity()

    Return the last identity from previous function execute.

    Function: batchsql(commands)

    Batch execute.

    var Database = require('mssql-functions');
    
    var connection_string = {
      authentication: {
        type: "default",
        options: {
          userName: "***",
          password: "***",
        }
      },
      server: "***",
      options: {
        database: "***",
        instanceName: "***"
      }
    }
    
    var db = new Database(connection_string);
    db.connect()
        .then(function() {
          console.log('connected');
          var cmd = [
            'insert into externe_logovanie(projekt, datum, log, level) values (\'batch1\', getdate(), \'1\', 1)',
            'error sql',
            'insert into externe_logovanie(projekt, datum, log, level) values (\'batch1\', getdate(), \'3\', 1)'
          ];
          db.batchsql(cmd)
            .then(function(res) {
              console.log('batch result', res);
            })
            .catch(function(err) {
              console.log('error', err.message);
            });
        })
        .catch(function(error) {
            console.log(error.message);
        });

    Function: beginTransaction()

    Begin the transaction.

    var Database = require('mssql-functions');
    
    var connection_string = {
      authentication: {
        type: "default",
        options: {
          userName: "***",
          password: "***",
        }
      },
      server: "***",
      options: {
        database: "***",
        instanceName: "***"
      }
    }
    
    db.connect()
      .then(async function() {
        console.log('connected');
        try {
        await db.beginTransaction();
        console.log('in transaction');
    
        await db.execute('insert into #pokus(id,name) values (1, \'one\')');
    
        await db.commitTransaction();
        console.log('commit');
        } catch(error) {
          await db.rollbackTransaction();
          console.log('error in transaction', error.message);
        }
      })
      .catch(function(err) {
        console.log('error', err.message);
      });

    Function: commitTransaction()

    Commit the transaction.

    Function: rollbackTransaction()

    Rollback the transaction.

    todo

    • Write examples

    Keywords

    none

    Install

    npm i mssql-functions

    DownloadsWeekly Downloads

    15

    Version

    1.6.3

    License

    MIT

    Unpacked Size

    21.7 kB

    Total Files

    4

    Last publish

    Collaborators

    • roboulbricht