"COUNT field incorrect or syntax error" when string reaches a certain length during an insert? about node-sqlserver-v8 HOT 17 CLOSED

What happens if you use sql.WLongVarChar(value) .....

from node-sqlserver-v8.

That works perfectly, thanks!

from node-sqlserver-v8.

I was getting invalid precision type HY104 (msnodesql v0.1.46) I've since updated to v0.2.10 and now I'm getting this new error message 07002. How does one use the "WLongVarChar" in the above example?

Thanks,
Jeff

from node-sqlserver-v8.

if you are using a very large string as a parameter

sql.query(conn_str, q, [sql.WLongVarChar(value)], function (err, results) { if(err){ console.log(err); } });

should force the driver to use the correct type .... please provide the example you are trying if this does not work correctly.

from node-sqlserver-v8.

Thanks for explaining working great now. 👍

from node-sqlserver-v8.

Update: I think I might have found a possible cause of this issue.... it would appear that it may not be counting the whitespace/newline characters properly to determine if the type should be a WLongVarChar

I have been able to test this by preforming a count on the sting I'm passing in if it is under 2000 including whitespace then no problem, how ever if the character count is like 1800 + 300 whitespaces then it will error until the character count goes over 2000 then it will start working again.....

In my case I'm storing some html and js into the database for dynamic offline content those naturally have lots of white space from the editor..... and I have a base sql update/insert function so for now I'm checking if length is over 2000 during my query builder and parameterizing process

// data: {uuid: 'unique id', col1Name: 'string', col2Name: 'string', col3Name: 'string'};
// dataProps: ['uuid', 'col1Name', 'col2name'] // is an array of filtered propNames to update

var updateQuery = "UPDATE [dbo].[" + tableName + "] SET ";
var updateQueryPrams = [];
for (var i = 0; dataProps.length > i; i++) {
if (dataProps[i] != "uuid") { // exclude the uuid
updateQuery += dataProps[i] + "= ?,";
if (data[dataProps[i]] && data[dataProps[i]].length > 2000) {
updateQueryPrams.push(sql.WLongVarChar(data[dataProps[i]]));
} else {
updateQueryPrams.push(data[dataProps[i]]);
}
}
updateQuery += "lastUpdated = CURRENT_TIMESTAMP";
updateQuery += " where uuid= ?";
updateQueryPrams.push(data.uuid);

sql.query(connStr[database], updateQuery, updateQueryPrams, function (err) {........

Thanks,
Jeff

from node-sqlserver-v8.

I've had this problem when calling a stored procedure. No spaces in the text being stored, but if the text has between 2048 and 8192 characters long then the driver wouth throw an error and go into a completely non-responsive state.

I made sure that my input variables to the stored proc (TEXT) are cast like this:
sql.engine.Text(data.json, data.json.length)

Giving it the correct length right at the beginning makes it work

from node-sqlserver-v8.

I am having the same issue. This is how I'm calling my stored procedure

const sql = require("mssql/msnodesqlv8")

module.exports = (req, res, next) => {
    var request = new sql.Request(connection)
    request.input("id", sql.Int, req.params.id)
    request.input("page_content", req.body.page_content)
    request.execute("editpage", function(err, rs){
        if (err) console.log(err)
        next()
    })
}

I tried adding:

request.input("page_content", sql.WLongVarChar(65000), req.body.page_content)

and:

request.query("UPDATE pages SET page_content = @page_content, WHERE id = @id", [sql.WLongVarChar(65000)], function(err, rs){
    if (err) console.log(err)
    next()
  })

and I am getting "sql.WLongVarChar is not a function" error. Any adjustments I can make to get this to work? It appears the problem (like Jeff mentions above) happens only between 2000 and 8000 characters or so.

from node-sqlserver-v8.

from node-sqlserver-v8.

Awesome. Appreciate your help.

from node-sqlserver-v8.

I have the same problem, is there a fix coming?

from node-sqlserver-v8.

from node-sqlserver-v8.

this has been fixed please test on 0.6.3

https://github.com/TimelordUK/node-sqlserver-v8/releases/tag/0.6.3

from node-sqlserver-v8.

I believe this is now fixed so will close. Please reopen if still an issue.

from node-sqlserver-v8.

I have the same problem. on 0.6.6...

from node-sqlserver-v8.

from node-sqlserver-v8.

from node-sqlserver-v8.