Request Error on input parameters

[snekxer]

--EDIT-- See the tedious version on a comment below

When writing and executing a query, a SELECT statement without any parameters (i.e., 'SELECT * FROM table', or 'SELECT column1, column2 FROM table') works without issue. If I modify this statement to have a WHERE clause, with or without a parameter, it timesout on 'RequestError: Failed to cancel request in 5000ms'. The code:

const sql = require('mssql');
let users = {};
const config = {
    server: 'server',
    authentication: {
        type: 'default',
        options: {
            userName: 'user',
            password: 'password'  
        }}
};
sql.connect(config).then(pool => {
    let query = 'SELECT column FROM table WHERE Term = @term';
    return pool.request()
       .input('term', sql.Int, 1)
       .query(query)
}).then(result =>{
    users = result;
    sql.close();
    console.log(users);
}).catch(err =>{
    console.log(err);
});

The full error stack:

RequestError: Failed to cancel request in 5000ms
    at Request.userCallback (C:\Users\v\WebstormProjects\project\node_modules\mssql\lib\tedious\request.js:429:19)
    at Request.callback (C:\Users\v\WebstormProjects\project\node_modules\tedious\lib\request.js:56:14)
    at Connection.socketError (C:\Users\v\WebstormProjects\project\node_modules\tedious\lib\connection.js:2416:20)
    at Connection.dispatchEvent (C:\Users\v\WebstormProjects\project\node_modules\tedious\lib\connection.js:1274:15)
    at Connection.cancelTimeout (C:\Users\v\WebstormProjects\project\node_modules\tedious\lib\connection.js:1198:10)
    at Timeout._onTimeout (C:\Users\v\WebstormProjects\project\node_modules\tedious\lib\connection.js:1162:14)
    at listOnTimeout (internal/timers.js:531:17)
    at processTimers (internal/timers.js:475:7) {
  code: 'ETIMEOUT',
  originalError: ConnectionError: Failed to cancel request in 5000ms
      at ConnectionError (C:\Users\v\WebstormProjects\project\node_modules\tedious\lib\errors.js:13:12)
      at Connection.cancelTimeout (C:\Users\v\WebstormProjects\project\node_modules\tedious\lib\connection.js:1198:67)
      at Timeout._onTimeout (C:\Users\v\WebstormProjects\project\node_modules\tedious\lib\connection.js:1162:14)
      at listOnTimeout (internal/timers.js:531:17)
      at processTimers (internal/timers.js:475:7) {
    message: 'Failed to cancel request in 5000ms',
    code: 'ETIMEOUT'
  },
  name: 'RequestError',
  number: 'ETIMEOUT',
  state: undefined,
  precedingErrors: []
}

[arthurschreiber]

It looks like you're not using tedious directly, but you're using mssql. Please open an issue in https://github.com/tediousjs/node-mssql, or try reproducing the issue with just tedious. 🙇

[snekxer]

Whoops, my bad, I have both open :P sorry and thanks

[snekxer]

I actually do have the same code on tedious, as I was doing testing with both libraries. Same code, issue, etc. Here's the code:

const Connection = require('tedious').Connection;
const config = {
    server: 'server',
    authentication: {
        type: 'default',
        options: {
            userName: 'user', 
            password: 'password'
        }
    },
    options: {
        // If you are on Microsoft Azure, you need encryption:
        encrypt: true,
        database: 'db'  //update me
    }
};
const Request = require('tedious').Request;
const TYPES = require('tedious').TYPES;

const connection = new Connection(config);
connection.on('connect', connected);
connection.on('errorMessage', infoError);
connection.on('end', end);

const table = [];

function connected(err) {
    if (err) {
        console.log(err);
        process.exit(1);
    }

    console.log('connected');

    exec("SELECT colum1, colum2 FROM table WHERE Term=@term;");
    //console.log(table)
}


function exec(sql) {
    let request = new Request(sql, statementComplete);
    request.on('columnMetadata', columnMetadata);
    request.on('row', rows);
    request.on('done', requestDone);
    request.addParameter('term', TYPES.Int, 1);

    connection.execSql(request);
}

function requestDone(rowCount, more) {
    //console.log(rowCount + ' rows');
}

function statementComplete(err, rowCount) {
    if (err) {
        console.log('Statement failed: ' + err);
    } else {
        console.log(rowCount + ' rows');
    }
}

function end() {
    console.log('Connection closed');
    process.exit(0);
}

function infoError(info) {
    console.log(info.number + ' : ' + info.message);
}

function columnMetadata(columnsMetadata) {
    columnsMetadata.forEach(function(column) {
        //console.log(column.colName);
    });
}


function rows(columns) {
    let row = [];
    columns.forEach(function(column) {
        let value;
        if (column.value === null) {
            value = 'NULL';
        } else {
            value = column.value;
        }
        row.push(value);
    });
    console.log(row);
}

[snekxer]

For clarification, it is the same issue, this is just the pure tedious version. The error:
Statement failed: ConnectionError: Failed to cancel request in 5000ms

[IanChokS]

Hi @snekxer,

If I modify this statement to have a WHERE clause, with or without a parameter, it timesout on 'RequestError: Failed to cancel request in 5000ms'

I've tried running your script using a where clause as well, but mine retrieved data back fine.

I suspect that your parameter in the sql statement is wrong, or might be something wrong with your config.

[snekxer]

@IanChokS , if you see the code I posted second (in the comment), the WHERE clause is included. I have executed that same SQL query in the DB to make sure it works, and it does, so I can verify it is not a query issue. For the config, if I just modify the query and not touch the config, it goes through as well.
Is there's anything else I should include in the config for parameters to work?

[IanChokS]

Hmm interesting 🤔

The query I used was

and all seems to be working. Is this what you meant by 'WHERE clause is included'?

What tedious version are you using?

[snekxer]

I'm using v6.13.0.

Yes, that same query fails for me, and also the parametrized version. See the "addParameter" in the exec function:

function connected(err) {
    if (err) {
        console.log(err);
        process.exit(1);
    }
    console.log('connected');
    exec("SELECT colum1, colum2 FROM table WHERE Term=@term;");
}

function exec(sql) {
    let request = new Request(sql, statementComplete);
    request.addParameter('term', TYPES.Int, 1);
    request.on('columnMetadata', columnMetadata);
    request.on('row', rows);
    connection.execSql(request);
}

[MichaelSun90]

Hi @snekxer, the behavior seems reproducible by using your script and the query with a parameter. Currently try to dig into this, see why the driver behaves like this.

[IanChokS]

Here's my setup:

So I'm getting data back. Am I missing something in my setup? @MichaelSun90 how did you reproduce the error

[snekxer]

Nope, that's the exact same thing I have, it just doesn't work for me 🤷‍♂ I can give you more info on my setup if that's helpful:

Win10 v10.0.18362 Build 18362
Node.js v12.14.0
SQL Server v13.0.1711.0

[IanChokS]

Oh you're using Node 2.x.x. That's quite old. Tedious supports Node v 6.x and up, and soon we'll be dropping support for Node 6.x as well. I think you're getting issues because your Node version is too old.

Do you have an environment where you can use the same test but with the Node version 8, 10, or 12? I think it will work then.

[snekxer]

@IanChokS Copy/pasted badly, it's v12.14.0. Just edited the comment.

[IanChokS]

Oh lol. Hmmm that's interesting 🤔 I'm not sure why yours is failing to be honest. Can you create a new testing environment that uses the latest tedious version?

[snekxer]

@IanChokS So another weird thing.... I was pretty sure I was using the latest version of tedious, I had quite literally just installed it from npm; your comment made me double check. When I do npm install tedious, I get that the version installed is 8.0.1. But when I do npm tedious -v I get v6.13.6. I tried this in a clean environment to make sure that it wasn't a dependency on another package, but no; I get the same answers in a clean build. So I want to say that I'm using the latest version, but honestly I don't know which answer to trust.

[MichaelSun90]

Hi @snekxer, I figured out why you are having this issue. Can you help me check a file in your repo under "\lib\data-types" called "nvarchar.js"? There should be a function called "writeParameterData: function writeParameterData(buffer, parameter, options, cb)". Do you have the cb function as a parameter for that function? That is the cause of why I got the error that you showed us.

Is that is the case, I think you may not run with the latest Tedious set up on your side. This change happened a while ago, and I just checked it is in the latest version 8.0.1

I think you may have two versions of Tedious on your machine. One is an older version of Tedious without the previously mentioned changes installed by using NPM install from a command prompt globally. The second one is your cloned tedious lastest repo which has the changes. when you "const Connection = require('tedious').Connection;", by default it may go and fetch the tedious installed globally which is an older version of tedious that hence the error.

Try change "const Connection = require('tedious').Connection;" to "const Connection = require('./lib/connection');" if you already run "npm run build" within the repo loaction. See if that resolve the issue.

You can also check whether you are using a globally installed Tedious by hovering your cursor on top of the ‘tedious’ from the line "const Connection = require('tedious').Connection" if you are using vscode as an editor or some other advanced editor. The path of the project that you are using will appear below your cursor. The default path is usually: ""C:/Users//AppData/Local/Microsoft/TypeScript/3.7/node_modules/@types/tedious/index""

Hope this helps.

[snekxer]

@MichaelSun90 yes, that function exists indeed. Definitely helps, thanks!

As I was telling Ian above and as you noted, it does seem like I have two versions of tedious, but I don't understand how this came to be. npm says it installed 8.0.1 (and since the repo gives npm as the installation method this seems as intended), but when checking the version it says 6.13.6. I'll clone the repo and go from there, but I am curious on the mismatch by npm, and the unfortunate consequence that the instruction to install from npm would not give the version currently in the repo.

I might add all of this is local, not global. I just checked my project's node modules to check and even the README notes the version as 8.0.1. I tried requiring the file's path as you suggested but this does not work, which with the above observation makes sense.

[IanChokS]

When I run npm tedious -v on the latest release I also get 6.12.0, so I don't think that's the issue. I don't know what is causing your problem 😢 You should be able to use const Connection = require('tedious').Connection; as well, so switching to const Connection = require('./lib/connection'); might not be the ideal permanent fix

[snekxer]

Yeah, apparently npm tedious -v gives you the version of npm, not tedious. Package-lock also mentions 8.0.1. From what Michael described, I would think that I have 8.0.1 installed but with some old code? Because I do have that function with cb.

[IanChokS]

Hmm try deleting your package-lock.json and run npm i again

[snekxer]

Done, still 8.0.1

[IanChokS]

Did the error still persist?

[snekxer]

Yep. That's why I'm saying, it seems I have 8.0.1 with old code. Which I have no clue how it happened, but at this point I just want to resolve it. Is there any alternative to installing from npm?

[IanChokS]

You can try to do git clone git@github.com:tediousjs/tedious.git

Then cd tedious -> npm i -> npm run build -> npm run test-all (To see if any current tests fail)

[snekxer]

Ha, I was just writing that I'd clone it already but wasn't sure where to go from there. Thanks, I'll do that and check that it solves it

[IanChokS]

After you run npm run build, you can try to do const Connection = require('./lib/connection'); as Michael suggested

If that still doesn't work, try deleting C:/Users//AppData/Local/Microsoft/TypeScript/3.7/node_modules/@types/tedious/index and then run npm i tedious again

[snekxer]

Cloned, built and all, still getting the same error.

I don't have TypeScript on Local; checked LocalLow and Roaming too just in case and nothing there either.

@MichaelSun90 , you mentioned earlier:

There should be a function called "writeParameterData: function writeParameterData(buffer, parameter, options, cb)". Do you have the cb function as a parameter for that function? That is the cause of why I got the error that you showed us.
Is that is the case, I think you may not run with the latest Tedious set up on your side. This change happened a while ago, and I just checked it is in the latest version 8.0.1

Does this mean that the current version should NOT have the cb parameter? If so, what I just cloned from GitHub still has it. I checked the corresponding file on the repo here and it also has it.

[MichaelSun90]

Hi @snekxer,
The lastest Tedious 8.0.1 should have the cb functions. For checking versions of node module, instead of using "npm -v tedious" which actually returns the version of npm, you can try "npm list tedious" which should return the version for Tedious.

[IanChokS]

I don't have TypeScript on Local;

typescript is in the devDependencies of package.json, so running npm i should download TypeScript and put it in the Node Modules folder... 🤔

[MichaelSun90]

Just to clear things up, @snekxer, are you currently try to run the test script that you provided in your previous message with a cloned repo or tedious installed by running "npm install tedious"?

[snekxer]

@MichaelSun90 got it, just wanted to clarify. The cb has been included in everything I've checked so that wasn't an issue before. I am currently running on a cloned repo, and before was running on the npm install. Both show the same error.

@IanChokS TypeScript doesn't show up in my node modules either, in both the repo clone and npm install.

[IanChokS]

Remove node_modules and package-lock.json and run npm i again.

---

npm/npm#17282

I fell into this trap as well, eventually to find out I had the NODE_ENV set to production in my current shell session. Closing my shell session and starting a new one did the trick.

I had added NODE_ENV=production in my environment variables, hence wasn't installing any devDependencies into node_modules. Removed it and is working fine now.

" I also had the NODE_ENV set to production and for this reason it did not work for me to delete the package-lock.json file, and node_modules folder with npm install command. It worked just fine. Thanks"

[MichaelSun90]

@snekxer , if you are trying to run the script against the installed tedious, then the steps should be:

1. open a cmd prompt anywhere, run "npm install tedious" - this should install 8.0.1, you can check it by run "npm list tedious"
2. then run your script exactly what you paste above. Keeping the line "const Connection = require('tedious').Connection;" the same. This should works.

If you are trying to run the script against a cloned repo:

1. like Ian said, after clone the repo, run "npm i", "npm run build" which should create a lib folder in your repo.
   
2. then modified the line "const Connection = require('./lib/connection')" just for testing .

Maybe you can try both see if either of this method behave differently.

I am pretty sure that the issue is coming from the cb function.
"connection.ts" has function: "makeRequest" which called a function called "getdata" which will eventually called a function "__writeParameterData" under “rpcrequest-payload.ts” which involve the cb function. if the cb is not called then the program will hang there and cause a time out.

[snekxer]

@IanChokS Deleted node_modules and package-lock.json, then npm i, still same error. With the comments you included, I also checked my NODE_ENV; it's undefined but I put it to development just in case, and still same error.

@MichaelSun90 Yes, I tried both and the method behaves the same in both of them. Same error. I can connect just fine in both, the problem comes only with the parameter. If I exclude the parameter there is no issue at all (again, in both).

[IanChokS]

[MichaelSun90]

Hi @snekxer, what editor are you using for debugging? Is VScode an option for you, since that is the only one I am familiar with. If you are using it, or you want to try it, then maybe debug the code a bit may help. Or maybe you can share your current set up with us, so we can dig into why this is happening to you.

I would set breakpoints at :

connection.js under lib/src folder : "payload.getData" under "makeRequest(request, packetType, payload)"

rpcrequest-payload.ts lib/src folder : getData, _writeParameterData, and writeParameterData under
_writeParameterData

The writeParameterData will be datatype specific:
Next depends on the datatype of your parameter, it will go to corresponding datatype's writeParameterData.
For example:

int.js under lib\data-types\int.js: writeParameterData: function writeParameterData(buffer, parameter, _options, cb)

This cb is the reason why the query hangs and timeout.

[arthurschreiber]

@snekxer @MichaelSun90 @IanChokS

Sorry for not chiming in earlier, but I think you've been all mislead by the writeParameterData stuff. 😓 Sure, if the code for your version of tedious is somehow messed up and callback parameters don't get called because some files are from one version while other files from another, things would obviously not work.

But as you mentioned a few times, you installed via npm, so the version that was installed should have code that is "internally" consistent, and I doubt the problem stems from a mixup there.

What I think is more interesting is looking at your database. I think you mentioned you're connecting to a local database? Can you connect via SQL Server Management Studio or another tool just fine? If you run the same database query via SQL Server Management Studio, how long does it take for a response to come back?

tedious has default values for request timeout handling, if a request has not finished after 15 seconds it will time out, and the timeout will cause an attempt to be made to cancel the query by sending a cancellation notice to the server. If that cancellation is not acknowledged, the error you are getting is emitted. This means the query is sent to the database, but not all data is returned after 15 seconds, so the query is cancelled, but the cancellation is not acknowledged either, so an error is emitted.

Do you see the connected log output when you execute your script? Could you paste all of the log output maybe?

If you change the query to just some super basic thing like SELECT 1, do you see the same problem?

[snekxer]

Hi @arthurschreiber ; yes, I can connect and execute anything with SQL Server Management Studio without issue. The query takes about 1 second. In fact any query I do, with or without the WHERE clause, works fine and takes 1 sec.

I was just about to re run it (I've been out and only just now am touching my computer in like a week) to get the whole output but apparently now it works??? I have not idea what is going on 😕 I've changed nothing but it works now? Tested on the dev environment and the app and no issue now. No idea what happened but thank you anyway!

The issue was only if I did SELECT * FROM table WHERE column=something. So if I did a SELECT * FROM table, there was no issue.

[IanChokS]

I guess a computer restart was all that was needed 😄
I'm glad you were able to figure it out! 🙌

[snekxer]

@IanChokS the best part is that i didn't even restart it, it was in sleep mode this whole time. Ah, computers :)