Repository: tediousjs/node-mssql (js)

Issue:
Unable to bulk insert when there is an nvarchar(max) column in the table
<!--
Please use this issue template, which will help package maintainers effectively triage your issue.
Issues that do not conform to this template or do not provide useful information cannot be addressed until the required information is provided.
-->

<!-- Brief description of problem -->
**Invalid column type from bcp client for colid 1.**
Unable to bulk insert data to a table with a column having nvarchar(max) field.
I tried https://github.com/tediousjs/tedious/issues/679 , this fix , but it is also not working , im attaching a sample code to reproduce the same behaviour
### Expected behaviour:
Need to bulk insert the data into the db
<!-- What did you expect to happen? -->

### Actual behaviour:

<!-- What actually happened? Please include *relevant* SQL and schema where possible -->
```
(node:38764) UnhandledPromiseRejectionWarning: RequestError: Invalid column type from bcp client for colid 1.
    at handleError (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/mssql/lib/tedious.js:426:15)
    at Connection.emit (events.js:193:13)
    at Parser.tokenStreamParser.on.token (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/tedious/lib/connection.js:716:12)
    at Parser.emit (events.js:193:13)
    at Parser.parser.on.token (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
    at Parser.emit (events.js:193:13)
    at addChunk (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/readable-stream/lib/_stream_readable.js:297:12)
    at readableAddChunk (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/readable-stream/lib/_stream_readable.js:279:11)
    at Parser.Readable.push (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/readable-stream/lib/_stream_readable.js:240:10)
    at Parser.Transform.push (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/readable-stream/lib/_stream_transform.js:139:32)
```
### Configuration:

#test.js
```
var sql = require("mssql");

function testTable() {
    let table = new sql.Table('temp1');
    table.create = false;
    table.columns.add('id', sql.NVarChar, {
        length: 'max'
    });
    return table;
}
module.exports = testTable;
```
#demo.js 
```
var sql = require("mssql");
let tempTable = require("./test")();

async function testDb() {
    config = {
//put db config here
    }

    let db = await new sql.ConnectionPool(config).connect();
    const transaction = db.transaction()
    await new Promise(resolve => transaction.begin(resolve));
    const request = new sql.Request(transaction);

    tempTable.rows.add.apply(tempTable.rows, ["123123132"]);
    tempTable.rows.add.apply(tempTable.rows, ["213123123123"]);
    tempTable.rows.add.apply(tempTable.rows, ["23423423"]);
    tempTable.rows.add.apply(tempTable.rows, ["234234234234"]);
    console.log("bulk adding")
    console.log(tempTable)

    let response = await request.bulk(tempTable);
    console.log(response)
    await transaction.commit();
}

testDb();
```


### Software versions

  * NodeJS: 11.13.0
  * node-mssql: 5.1.0
SQL Server Enterprise Edition
Engine version: 14.00.3049.1.v1
--





Pull request:
Fixes #891 - Bulk Insert for NVARCHAR(MAX) Fields.
What this does:
Fixes #891

Adds the ability to set length for an NVARCHAR(MAX) column, which was breaking while bulk insertion.


Patch:
diff --git a/lib/table.js b/lib/table.js
index 99f92ef..748a831 100644
--- a/lib/table.js
+++ b/lib/table.js
@@ -32,6 +32,9 @@ function Table (name) {
       column.name = name
       column.nullable = options.nullable
       column.primary = options.primary
+      if (options.hasOwnProperty('length')) {
+        column.length = options.length
+      }
 
       return this.push(column)
     }


Test patch:
diff --git a/test/cleanup.sql b/test/cleanup.sql
index 7778b07..b588f6a 100644
--- a/test/cleanup.sql
+++ b/test/cleanup.sql
@@ -25,6 +25,17 @@ if exists (select * from sys.tables where name = 'tran_test')
 if exists (select * from sys.tables where name = 'bulk_table')
 	exec('drop table [dbo].[bulk_table]')
 
+if exists (select * from sys.tables where name = 'bulk_table2')
+	exec('drop table [dbo].[bulk_table2]')
+
+if exists (select * from sys.tables where name = 'bulk_table3')
+	exec('drop table [dbo].[bulk_table3]')
+
+if exists (select * from sys.tables where name = 'bulk_table4')
+	exec('drop table [dbo].[bulk_table4]')
+
+if exists (select * from sys.tables where name = 'bulk_table5')
+	exec('drop table [dbo].[bulk_table5]')
 
 if exists (select * from sys.tables where name = 'streaming')
 	exec('drop table [dbo].[streaming]')
diff --git a/test/common/tests.js b/test/common/tests.js
index dc45fc6..f4ab335 100644
--- a/test/common/tests.js
+++ b/test/common/tests.js
@@ -469,11 +469,35 @@ module.exports = (sql, driver) => {
       }).catch(done)
     },
 
+    'bulk load with varchar-max field' (name, done) {
+      let t = new sql.Table(name)
+      t.create = true
+      t.columns.add('a', sql.NVarChar, {
+        length: Infinity
+      })
+
+      t.rows.add('JP1016')
+      let req = new TestRequest()
+      req.bulk(t).then(result => {
+        assert.strictEqual(result.rowsAffected, 1)
+
+        req = new sql.Request()
+        req.batch(`select * from ${name}`).then(result => {
+          assert.strictEqual(result.recordset[0].a, 'JP1016')
+          done()
+        }).catch(done)
+      }).catch(done)
+    },
+
     'bulk converts dates' (done) {
       let t = new sql.Table('#bulkconverts')
       t.create = true
-      t.columns.add('a', sql.Int, { nullable: false })
-      t.columns.add('b', sql.DateTime2, { nullable: true })
+      t.columns.add('a', sql.Int, {
+        nullable: false
+      })
+      t.columns.add('b', sql.DateTime2, {
+        nullable: true
+      })
       t.rows.add(1, new Date('2019-03-12T11:06:59.000Z'))
       t.rows.add(2, '2019-03-12T11:06:59.000Z')
       t.rows.add(3, 1552388819000)
@@ -494,6 +518,66 @@ module.exports = (sql, driver) => {
       }).catch(done)
     },
 
+    'bulk insert with length option as string other than max throws' (name, done) {
+      const req = new TestRequest()
+      let table = new sql.Table(name)
+      table.create = true
+      table.columns.add('name', sql.NVarChar, {
+        length: 'random'
+      })
+
+      table.rows.add(table.rows, ['JP1016'])
+      req.bulk(table).then(() => {
+        assert.fail('it should throw error while insertion length with non-supported values')
+        done()
+      }).catch(err => {
+        assert.strictEqual(err.message, "Incorrect syntax near 'random'.")
+        assert.strictEqual(err.code, 'EREQUEST')
+        assert.strictEqual(err.name, 'RequestError')
+        done()
+      })
+    },
+
+    'bulk insert with length option as undefined throws' (name, done) {
+      const req = new TestRequest()
+      let table = new sql.Table(name)
+      table.create = true
+      table.columns.add('name', sql.NVarChar, {
+        length: undefined
+      })
+
+      table.rows.add(table.rows, ['JP1016'])
+      req.bulk(table).then(() => {
+        assert.fail('it should throw error while insertion length with non-supported values')
+        done()
+      }).catch(err => {
+        assert.strictEqual(err.message, 'Invalid column type from bcp client for colid 1.')
+        assert.strictEqual(err.code, 'EREQUEST')
+        assert.strictEqual(err.name, 'RequestError')
+        done()
+      })
+    },
+
+    'bulk insert with length as max' (name, done) {
+      let t = new sql.Table(name)
+      t.create = true
+      t.columns.add('a', sql.NVarChar, {
+        length: 'max'
+      })
+
+      t.rows.add('JP1016')
+      let req = new TestRequest()
+      req.bulk(t).then(result => {
+        assert.strictEqual(result.rowsAffected, 1)
+
+        req = new sql.Request()
+        req.batch(`select * from ${name}`).then(result => {
+          assert.strictEqual(result.recordset[0].a, 'JP1016')
+          done()
+        }).catch(done)
+      }).catch(done)
+    },
+
     'prepared statement' (done) {
       const ps = new TestPreparedStatement()
       ps.input('num', sql.Int)
diff --git a/test/common/unit.js b/test/common/unit.js
index 0090687..28729ab 100644
--- a/test/common/unit.js
+++ b/test/common/unit.js
@@ -197,6 +197,11 @@ describe('Unit', () => {
     t.columns.add('c', sql.TinyInt, { nullable: false, primary: true })
     assert.strictEqual(t.declare(), 'create table [#mytemptable] ([a] int, [b] tinyint null, [c] tinyint not null, constraint PK_mytemptable primary key (a, c))')
 
+    t = new sql.Table('MyTable')
+    t.columns.add('name', sql.NVarChar, {
+      length: Infinity
+    })
+    assert.strictEqual(t.columns[0].length, Infinity)
     return done()
   })
 
diff --git a/test/tedious/tedious.js b/test/tedious/tedious.js
index 5451070..49e7a04 100644
--- a/test/tedious/tedious.js
+++ b/test/tedious/tedious.js
@@ -120,9 +120,12 @@ describe('tedious', () => {
     })
 
     it('bulk load (table)', done => TESTS['bulk load']('bulk_table', done))
+    it('bulk load with varchar-max field (table)', done => TESTS['bulk load with varchar-max field']('bulk_table2', done))
     it('bulk load (temporary table)', done => TESTS['bulk load']('#anohter_bulk_table', done))
     it('bulk converts dates', done => TESTS['bulk converts dates'](done))
-
+    it('bulk insert with length option as undefined throws (table)', done => TESTS['bulk insert with length option as undefined throws']('bulk_table3', done))
+    it('bulk insert with length option as string other than max throws (table)', done => TESTS['bulk insert with length option as string other than max throws']('bulk_table4', done))
+    it('bulk insert with length as max (table)', done => TESTS['bulk insert with length as max']('bulk_table5', done))
     after(done => sql.close(done))
   })