v2.4.2

typo in binding.gyp preventing apple silicon compilation

1. #214

v2.4.1

1. Node v17 binaries included.

msnodesqlv8 git(master) node --version
v17.0.1

dates.js
{
_: [],
k: 'linux',
t: [
'bcp', 'promises',
'json', 'connection-pool',
'pause', 'concurrent',
'multiple-errors', 'geography',
'tvp', 'warnings.js',
'compoundqueries.js', 'querycancel.js',
'txn.js', 'datatypes.js',
'params.js', 'query.js',
'querytimeout.js', 'connect.js',
'bulk.js', 'sproc.js',
'prepared.js', 'userbind.js',
'dates.js'
]
}
use conn_str Driver={ODBC Driver 17 for SQL Server}; Server=192.168.1.102;UID=linux; PWD=linux; Database=node

bcp
ms = 59
✔ bcp employee (835ms)
✔ bcp 7 column mixed table (477ms)
✔ bcp expect error null in non null column (353ms)
✔ bcp expect error duplicate primary key (92ms)
✔ bcp recovery from error. (99ms)
✔ bcp hierarchyid binary (153ms)
✔ bcp small binary (118ms)
✔ bcp bit bit (99ms)
✔ bcp uniqueidentifier (89ms)
✔ bcp smallint (81ms)
✔ bcp tinyint (105ms)
✔ bcp real with null (122ms)
✔ bcp real (107ms)
✔ bcp bigint with nulls (112ms)
✔ bcp bigint (85ms)
✔ bcp time (112ms)
✔ bcp numeric (114ms)
✔ bcp varchar(max) (10k chars) (763ms)
✔ bcp datetimeoffset datetimeoffset - mix with nulls (190ms)
✔ bcp datetimeoffset datetimeoffset (165ms)
✔ bcp binary binary - mix with nulls (139ms)
✔ bcp binary binary (204ms)
✔ bcp bit bit - mix with nulls (158ms)
✔ bcp timestamp timestamp - mix with nulls (163ms)
✔ bcp timestamp timestamp - no null (126ms)
✔ bcp varchar varchar with nulls (159ms)
✔ bcp varchar varchar (177ms)
✔ bcp int, int column - with nulls (158ms)
✔ bcp int, int column (141ms)

promises
✔ adhoc proc promise: open call close (95ms)
✔ promises for table insert select rows (2012ms)
✔ using promises to open, query, close pool (47ms)
✔ adhoc promise: open select close
✔ query aggregator: insert 1 valid 1, ivalid table (38ms)
✔ query aggregator: drop, create, insert, select, drop
✔ query aggregator: 4 inserts, 2 updates, 2 updates, update all (44ms)
✔ query aggregator: insert into invalid table
✔ bcp employee (415ms)

json
✔ use proc to insert a JSON array and bulk parse on server (186ms)
✔ use proc to insert a JSON based complex object (180ms)

connection-pool
✔ using promises to open, query, close pool (201ms)
✔ submit 10 queries with errors (no callback) to pool of 4 (69ms)
✔ submit error queries with callback for results (64ms)
✔ submit queries with callback for results (2597ms)
✔ submit 1000 short queries to pool of 4 - expect concurrent queries and fast completion (1743ms)
✔ open pool size 4 - submit queries on parked connections (5610ms)
✔ open pool size 4 - leave inactive so connections closed and parked (4537ms)
✔ submit 7 queries to pool of 4 connections - pause 1 and resume when only item left (3106ms)
✔ submit 7 queries to pool of 4 connections - cancel 2 waiting on pool, expect 4 + 1 concurrent (2067ms)
✔ submit 7 queries to pool of 4 connections - pause 1 and resume whilst waiting on queue (2081ms)
✔ submit 4 queries to pool of 2 connections - expect 2 x queue 2 x concurrent queries (2061ms)
✔ submit 4 queries to pool of 4 connections - expect 4 x concurrent queries (4061ms)
✔ open and close a pool with 2 connections without error

pause
✔ resume query having never paused (306ms)
✔ pause a closed query
✔ pause a paused query that is about to close
✔ pause a large query and cancel without resume (109ms)
✔ pause a large query to only get 10 rows then submit new query whilst other paused (first killed) (336ms)
✔ pause a large query to only get 10 rows (218ms)
✔ queries can start off paused (200ms)
✔ run a large query (123ms)
✔ pause a large query every 100 rows (798ms)
✔ pause a large query every 100 rows - submit new query (895ms)
✔ close connection with paused query pending a resume (1005ms)
✔ pause a large query and cancel without resume - submit new query (194ms)

concurrent
✔ open connections in sequence and prove distinct connection objects created
✔ check for blocked calls to api with event emission (2012ms)
✔ check for blocked calls to api (2016ms)
✔ check for blocked calls to api with nested query (4032ms)
✔ open connections simultaneously and prove distinct connection objects created
✔ make sure two concurrent connections each have unique spid

multiple errors
✔ non trusted invalid user (60ms)
✔ select then use print statement capture print
✔ use print statement capture print
✔ callback multiple errors
✔ event based multiple errors

geography
✔ use tvp to insert geography LINES using pm (632ms)
✔ show a geography .Net error is reported back from driver (131ms)
✔ use tvp to insert geography LINESTRING, POINT and POLYGON using pm in 1 call (164ms)
✔ insert lines from json coordinates (95ms)
✔ insert points from json coordinates (164ms)
✔ insert a polygon from json coordinates (79ms)
✔ insert an array of geography lines (112ms)
✔ insert an array of geography points (122ms)
✔ prepare a geography point statement for repeat invocations (141ms)

tvp
✔ use tvp simple test type insert test extended ascii (279ms)
✔ use tvp simple test type insert test long string 8 * 1024 (129ms)
✔ call tvp proc with local table (179ms)
✔ call tvp proc from proc (2136ms)
✔ use tvp to select from table type complex object Employee type (638ms)
✔ employee use tm to get a table value type representing table and create that user table type (340ms)
✔ use tvp simple test type insert test using pm (215ms)
✔ non dbo schema use tvp simple test type select test (136ms)
✔ use tvp simple test type select test (154ms)
✔ use tvp simple test type insert test (131ms)

warnings
✔ TEST THREE - Prepared Query - JOIN HINT WARNING (51ms)
✔ TEST ONE - Query - JOIN HINT WARNING
✔ TEST TWO - Query - NULL ELIMNATED WARNING
✔ TEST FIVE - Stord Proc - JOIN HINT WARNING (57ms)
✔ print raises warning not error
✔ TEST FOUR - Prepared Query - NULL ELIMNATED WARNING

compoundqueries
✔ check row count emission is as expected for compound queries 1 insert
✔ test 001 - batched query: SELECT....; INSERT ....; SELECT....; (82ms)
✔ check row count emission is as expected for compound queries 3 inserts, update all
✔ check row count emission is as expected for compound queries 4 inserts, 2 updates, 2 updates, update all
✔ test 003 - batched query: SELECT....; SELECT (with no results) ....; SELECT....; (44ms)
✔ test 004 - batched query: SELECT....; INSERT (invalid...should fail) ....; SELECT....; (42ms)

querycancel
✔ nested cancel - expect Operation canceled on both
✔ cancel single waitfor using notifier - expect Operation canceled
✔ cancel single query and submit new query to prove connection still valid
✔ cancel single query from notifier using tmp connection - expect Operation canceled
✔ cancel single waitfor - expect Operation canceled
✔ cancel single waitfor on non polling query - expect cancel error and query to complete (3011ms)
✔ cancel single query - expect Operation canceled
✔ waitfor delay 20 and delayed cancel- expect Operation canceled (106ms)
✔ cancel single query and cancel again - expect Operation canceled and error
✔ 2 x cancel - expect Operation canceled on both
✔ pause a large query and cancel check done (132ms)
✔ cancel a prepared call that waits
✔ cancel a call to proc that waits for delay of input param. (86ms)

txn
✔ setup for tests
✔ begin a transaction and use streaming with error on constraint to trigger rollback detection (49ms)
✔ begin a transaction and rollback on violation and insert valid (69ms)
✔ begin a transaction and add two rows no constraint violation, commit and check (50ms)
✔ begin a transaction and rollback with no query
✔ begin a transaction and rollback with no query and no callback
✔ begin a transaction and commit
✔ begin a transaction and rollback
✔ begin a transaction and then query with an error
✔ begin a transaction and commit (with no async support)

datatypes
✔ test 023a - fetch large varbinary in chunks 'varbinary(max)', fetch as binary (1418ms)
✔ write / read an image column (107ms)
✔ test 001 - verify functionality of data type 'smalldatetime', fetch as date (65ms)
✔ test 002 - verify functionality of data type 'datetime', fetch as date (48ms)
✔ test 003_a - insert valid data into time(7) via TSQL, fetch as date (48ms)
✔ test 003_b - insert valid data into time(0) via TSQL, fetch as date (51ms)
✔ test 004_a - insert valid data into datetime2(7) via TSQL, fetch as date (43ms)
✔ test 004_b - insert valid data into datetime2(0) via TSQL, fetch as date (48ms)
✔ test 005_a - insert valid data into datetimeoffset(7) via TSQL, fetch as date (45ms)
✔ test 005_b - insert valid data into datetimeoffset(0) via TSQL, fetch as date (45ms)
✔ test 006_a - insert valid data into datetimeoffset(7) via TSQL, fetch as date UTC (46ms)
✔ test 007 - insert valid data into date via TSQL, fetch as date (47ms)
✔ test 008 - insert null into varchar(max) via TSQL, fetch as text (47ms)
✔ test 008_bndryCheck_VC - insert 2048 char string into varchar(max) via TSQL, fetch as text (51ms)
✔ test 008_bndryCheck_NVC - insert 2049 char string into nvarchar(max) via TSQL, fetch as text (53ms)
✔ test 009 - verify functionality of data type 'guid', fetch as...

v2.4.0

1. electron v14/v15 binaries included for Linux, Windows and Mac

promises

see promises.ts under samples/typescript for some example code of how to use these promise methods.

Some promises have been added to the API for a more modern async approach. They are all collected under 'object.promises.promise' (pool.promises.open(), sql.promises.query(..), sql.promises.callProc(..), connection.promises.query(..) etc)

see index.js for definitions

export interface AggregatorPromises {
    query(sql: string, params?: any[], options?: QueryAggregatorOptions): Promise<QueryAggregatorResults>
    callProc(name: string, params?: any, options?: QueryAggregatorOptions): Promise<QueryAggregatorResults>
}
   
interface SqlClientPromises  {
    query(conn_str: string, sql: string, params?: any[], options?: QueryAggregatorOptions): Promise<QueryAggregatorResults>
    callProc(conn_str: string, name: string, params?: any, options?: QueryAggregatorOptions): Promise<QueryAggregatorResults>
    open(conn_str: string): Promise<Connection>
}

export interface PoolPromises extends AggregatorPromises {
    open(): Promise<Pool>
    close(): Promise<any>
}

interface ConnectionPromises extends AggregatorPromises {
    prepare(sql: string): Promise<PreparedStatement>
    getTable(name: string): Promise<BulkTableMgr>
    close(): Promise<any>
    cancel(name: string): Promise<any>
}

export interface BulkTableMgrPromises
{
    select(cols: any[]): Promise<any[]>
    insert(rows: any[]): Promise<any>
    delete(rows: any[]): Promise<any>
    update(rows: any[]): Promise<any>
}

export interface PreparedPromises {
    free(): Promise<any>
    query(params?: any[], options?: QueryAggregatorOptions) : Promise<QueryAggregatorResults>
}

for example using the connection pool using promises.

connection pool

async function pool() {
    try {
        const connStr: string = getConnection()
        const size = 4
        const options: PoolOptions = {
            connectionString: connStr,
            ceiling: size
        }
        const pool: Pool = new sql.Pool(options)
        await pool.promises.open()
        const all = Array(size * 2).fill(0).map((_, i) => pool.promises.query(`select ${i} as i, @@SPID as spid`))
        const promised: QueryAggregatorResults[] = await Promise.all(all)
        const res = promised.map(r => r.first[0].spid)
        await pool.promises.close()
        console.log(`pool spids ${res.join(', ')}`)
    } catch (e) {
        console.log(e)
    }
}

query

async function adhocQuery() {
    try {
        const connStr: string = getConnection()
        const res: QueryAggregatorResults = await sql.promises.query(connStr, 'select @@SPID as spid')
        console.log(`ashoc spid ${res.first[0].spid}`)
    } catch (e) {
        console.log(e)
    }
}

async function openSelectClose() {
    try {
        const connStr: string = getConnection()
        const conn: Connection = await sql.promises.open(connStr)
        const res: QueryAggregatorResults = await conn.promises.query('select @@SPID as spid')
        console.log(JSON.stringify(res, null, 4))
        await conn.promises.close()
    } catch (e) {
        console.log(e)
    }
}

procedure

use a promise to open connection, call a proc and close all from one promise - or call from a connection. Note all results are aggregated i.e. you are returned a result containing all queries etc

async function adhocProc() {
    try {
        const connStr: string = getConnection()
        const proc = new ProcTest(connStr, sampleProc)
        await proc.create()    
        const msg = 'hello world'
        const res: QueryAggregatorResults = await sql.promises.callProc(connStr, sampleProc.name, {
            param: msg
        })        
        await proc.drop()
        console.log(`adhocProc returns ${res.returns} from param '${msg}''`)
    } catch (e) {
        console.log(e)
    }
}

async function proc() {
    try {
        const connStr: string = getConnection()
        const proc = new ProcTest(connStr, sampleProc)
        await proc.create()    
        const conn: Connection = await sql.promises.open(connStr)
        const promises: ConnectionPromises = conn.promises
        const msg = 'hello world'
        const res: QueryAggregatorResults = await promises.callProc(sampleProc.name, {
            param: msg
        })
       
        console.log(`proc returns ${res.returns} from param '${msg}''`)
        await proc.drop()
        await promises.close()
    } catch (e) {
        console.log(e)
    }
}

table manager

use a promise to fetch a table and insert rows to it.

async function table() {
      try {
        const connStr: string = getConnection()
        const connection = await sql.promises.open(connStr)
        const tm: BulkTableTest = new BulkTableTest(connection, sampleTableDef)
        const table: BulkTableMgr = await tm.create()
        const vec: SampleRecord[] = getInsertVec(10)
        console.log(`table = ${tm.createTableSql}`)
        await table.promises.insert(vec)
        const read = await connection.promises.query(tm.selectSql)
        console.log(`table ${read.first.length} rows from ${tm.tableName}`)
        console.log(JSON.stringify(read.first, null, 4))
        await tm.drop()
        await connection.promises.close()
       } catch (e) {
        console.log(e)
      }
}

v2.2.0

1. electron v13 binaries

2. BCP allows fast insert speed from client to a designated table. This is achieved via allocating fixed positions in memory binding each column on that table and re-populating/sending each row to the server. It is in effect a memory copy from the client to the table.

a 16 column Employee table mixed with binary, varchar, date, int and decimal can insert over 50k rows in 3 seconds (vs 25 seconds using non bcp) over a network, smaller tables speeds can be over 100k a second.

a 7 column table as shown inserts 100k rows in 3.5 seconds over a network - and that includes selecting 100 back to verify insert.

   √ bcp employee (1144ms)
    √ bcp 7 column mixed table  (3753ms)
    √ bcp expect error null in non null column (441ms)
    √ bcp expect error duplicate primary key (68ms)
    √ bcp recovery from error. (73ms)
    √ bcp hierarchyid binary (165ms)
    √ bcp small binary (105ms)
    √ bcp bit bit (1379ms)
    √ bcp uniqueidentifier (98ms)
    √ bcp smallint (97ms)
    √ bcp tinyint (90ms)
    √ bcp real with null (90ms)
    √ bcp real (89ms)
    √ bcp bigint with nulls (82ms)
    √ bcp bigint (100ms)
    √ bcp time (99ms)
    √ bcp numeric (103ms)
    √ bcp varchar(max) (10k chars) (1806ms)
    √ bcp datetimeoffset datetimeoffset - mix with nulls (145ms)
    √ bcp datetimeoffset datetimeoffset (239ms)
    √ bcp binary binary - mix with nulls (197ms)
    √ bcp binary binary (203ms)
    √ bcp bit bit - mix with nulls (101ms)
    √ bcp timestamp timestamp - mix with nulls (119ms)
    √ bcp timestamp timestamp - no null (127ms)
    √ bcp varchar varchar with nulls (172ms)
    √ bcp varchar varchar (139ms)
    √ bcp int, int column - with nulls (108ms)
    √ bcp int, int column (125ms)


  29 passing (12s)

 test('bcp 7 column mixed table ', testDone => {
    async function test () {
      function getNumeric (i) {
        const v = Math.sqrt(i + 1)
        return Math.round(v * 1e6) / 1e6
      }
      const bcp = new BcpEntry({
        tableName: 'test_table_7_bcp',
        columns: [
          {
            name: 'id',
            type: 'INT PRIMARY KEY'
          },
          {
            name: 's1',
            type: 'VARCHAR (255) NULL'
          },
          {
            name: 's2',
            type: 'VARCHAR (100) NULL'
          },
          {
            name: 'i1',
            type: 'int null'
          },
          {
            name: 'i2',
            type: 'int NULL'
          },
          {
            name: 'n1',
            type: 'numeric(18,6) NULL'
          },
          {
            name: 'n2',
            type: 'numeric(18,6) NULL'
          }]
      }, i => {
        return {
          id: i,
          s1: i % 2 === 0 ? null : `column1${i}`,
          s2: `testing${i + 1}2Data`,
          i1: i * 5,
          i2: i * 9,
          n1: getNumeric(i),
          n2: getNumeric(i)
        }
      }, (actual, expected) => {
        assert.deepStrictEqual(actual.length, expected.length)
        for (let i = 0; i < actual.length; ++i) {
          const lhs = actual[i]
          const rhs = expected[i]
          assert.deepStrictEqual(lhs.id, rhs.id)
          assert.deepStrictEqual(lhs.s1, rhs.s1)
          assert.deepStrictEqual(lhs.s2, rhs.s2)
          assert.deepStrictEqual(lhs.i1, rhs.i1)
          assert.deepStrictEqual(lhs.i2, rhs.i2)
          assert(Math.abs(lhs.n1 - rhs.n1) < 1e-5)
          assert(Math.abs(lhs.n2 - rhs.n2) < 1e-5)
        }
      })
      return await bcp.runner(100000)
    }
    test().then((e) => {
      testDone(e)
    })
  })

    // see bcp.js unit tests - bind to a table
    async create () {
      const promisedQuery = util.promisify(theConnection.query)
      const tm = theConnection.tableMgr()
      const promisedGetTable = util.promisify(tm.getTable)
      await promisedQuery(this.dropTableSql)
      await promisedQuery(this.createTableSql)
      const table = await promisedGetTable(this.tableName)
      return table
    }
  }
 // set the flag to turn on bcp and send rows to server using fast memory copy.
        theConnection.setUseUTC(false)
        const table = await helper.create()
        table.setUseBcp(true)
        const promisedInsert = util.promisify(table.insertRows)
        const promisedQuery = util.promisify(theConnection.query)

This protocol is not part of the ODBC specification and its use therefore depends on using correct ODBC driver. For linux users, this should work out the box as ODBC 17 is the only driver supported and this is one used for BCP. The feature has been tested on Ubuntu, MacOS, Debian and Alpine.

For windows users, older drivers can still be used on all non bcp functions just as before - however presently only ODBC 17 is supported for bcp. Hence you need to have installed ODBC data source "ODBC Driver 17 for SQL Server". No other driver will work and attempts to do so will probably crash the node instance.

v2.1.0

This is a fairly significant release, please test carefully before deploying to production environments.

1. node v16 binaries on all platforms.
2. fix where cancel query may cause node crash due to thread unsafe call
3. incorrect bound types may cause hung query.
4. mix of error / info during query may cause hung query in callback mode
5. improved typescript typings #207
6. improved local date management #205
7. useUTC option on pool
8. boundTable.useMetaType(true) send column types for bound tables rather than driver deducing types from data.
9. utility promise await poolOrConnection.callprocAggregator('sp_proc', [], { timeoutMs: procTimeout, raw: false })

v2.0.15

1. add binary support for node v16
2. #203

stored proc call via a pool or connection

see unit tests for sproc or samples\javascript\procedure.js

// connectionProxy can be a pool or connection
const q = connectionProxy.callproc(spName, o, (err, results, output, more) => { })

javascript git(master) node .\procedure.js
Debugger attached.
asConnection 1000 invocations, elapsed = 1427, res length=1000
pool opened : {
    "floor": 0,
    "ceiling": 5,
    "heartbeatSecs": 20,
    "heartbeatSql": "select @@SPID as spid",
    "inactivityTimeoutSecs": 60,
    "connectionString": "Driver={ODBC Driver 17 for SQL Server};Server=(localdb)\\node;Database=scratch;Trusted_Connection=yes;"
}
asPool [5] 1000 invocations, elapsed = 230, res length=1000
done
Waiting for the debugger to disconnect...

v2.0.14

fix for lower node versions

v2.0.13

enhanced error reporting i.e. sproc number, servername etc

1. #202

v2,0,12

1. #200

  test('use tableMgr bulk insert varchar vector - exactly 4000 chars', testDone => {
    async function runner () {
      const b = repeat('z', 4000)
      const helper = new TypeTableHelper(theConnection, 'NVARCHAR(MAX)')
      const expected = helper.getVec(10, i => b)
      const table = await helper.create()
      const promisedInsert = util.promisify(table.insertRows)
      const promisedSelect = util.promisify(table.selectRows)
      try {
        await promisedInsert(expected)
        const res = await promisedSelect(expected)
        assert.deepStrictEqual(res, expected)
      } catch (e) {
        assert.ifError(e)
      }
    }
    runner().then(() => {
      testDone()
    })
  })

v2.0.11

bulk insertion where vector includes nulls causes unexpected behvior - 'null' txt in db column or node crashes for varbinary

#194

test('use tableMgr bulk insert varbinary vector - with null', testDone => {
    async function runner () {
      const b = Buffer.from('0102030405060708090a', 'hex')
      const helper = new TypeTableHelper(theConnection, 'varbinary(10)')
      const expected = helper.getVec(10, i => i % 2 === 0 ? null : b)
      const table = await helper.create()
      const promisedInsert = util.promisify(table.insertRows)
      const promisedSelect = util.promisify(table.selectRows)
      try {
        await promisedInsert(expected)
        const res = await promisedSelect(expected)
        assert.deepStrictEqual(res, expected)
      } catch (e) {
        assert.ifError(e)
      }
    }
    runner().then(() => {
      testDone()
    })
  })