Prepared Statement,NVarChar and sql.MAX about node-mssql HOT 6 CLOSED

I use sql.NVarChar('max') without issue

from node-mssql.

According to SQL server error messages, MAX length is not supported in prepared statements.

The size (65535) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).
The size (65535) given to the type 'nvarchar' exceeds the maximum allowed for any data type (8000).
The size (65535) given to the type 'varbinary' exceeds the maximum allowed for any data type (8000).

@rossipedia, you should not use sql.NVarChar('max'), it only create NVARCHAR(4000), you should use sql.NVarChar(sql.MAX).

from node-mssql.

My mistake, it was a bug in type declaration. It's fixed now.

from node-mssql.

@patriksimek thanks for the tip! we've since moved to matching parameter types to the columns, but that's good to know for future reference

from node-mssql.

Not sure if it's this exact bug, but I'm having a problem related to this - using SQL Server 2012 and node-mssql 1.3.0. I'm trying to pass empty string to a prepared statement using sql.NVarChar(sql.MAX) and it fails on execute()

Expected result: [ { MyString: '' } ]
Actual result: execute() failed

Problem only seems occur with the magical combination of sql.MAX and str === ''. As long as there is at least 1 character in the string, or if I use sql.NVarChar(4000) it works as expected.

var ps = new sql.PreparedStatement(connection);

ps.input('str', sql.NVarChar(sql.MAX));

ps.prepare('SELECT @str AS MyString', function (err) {
    if (err) {
        console.log('prepare() failed');
    }

    ps.execute({
        str: ''
    }, function (err, recordset) {
        if (err) {
            console.log('execute() failed');
        }

        console.dir(recordset);
    });
});

from node-mssql.

Thanks for the report, it's fixed and will be part of the 2.0 release.

from node-mssql.