send a misspelled query doesn't return error string

[risingphoenix]

Hi

if I send a query like this:

db.query('select jhgfkjhg * from users',function(err,data) {
    console.log(err);
    console.log(data);
});

err is null and data is an array of 0 elements.

even if a set a global listener db.on('error'... the error inside the closure is an array of 0 elements.

if I send a query like this:

db.query('select jhgfkjhg * from users',null,{id:Number},function(err,data) {
    console.log(err);
    console.log(data);
});

returns:

Uncaught node.js Error 
TypeError: Cannot call method 'forEach' of undefined
    at Socket.eval (... \node_modules\dblite\build\dblite.node.js:296:25 ...)

I run all my code under node-webkit

I do something wrong?
Thanks for your code

[WebReflection]

This is what I have in console

> var db = require('dblite')(':memory:');
undefined
> db.query('create table users (id INTEGER PRIMARY KEY)');
{ domain: null,
  _events: {},
  _maxListeners: 10,
  close: [Function],
  lastRowID: [Function],
  plain: [Function],
  query: [Function] }
> db.query('select jhgfkjhg * from users', function(err, data){console.log(err);console.log(data);});
{ domain: null,
  _events: {},
  _maxListeners: 10,
  close: [Function],
  lastRowID: [Function],
  plain: [Function],
  query: [Function] }
> [Error: Error: near line 2: near "from": syntax error
]
null

So everything works for me. I need to know which version of node.js and sqlite3 you are using in order to guess more.

Last, but not least, dblite works that whatever you don't need, should not be used.
As example, your last operation would expect an array of properties, not null … you don't have properties in the query, don't pass them or pass an empty Array instead of null.

Query error a part, this would be fine
db.query('select jhgfkjhg * from users',{id:Number},callback);

as well as this one
db.query('select jhgfkjhg * from users',[],{id:Number},callback);

Have a look at the list of overloads in the README.md page ;-)

[risingphoenix]

Thanx for the reply

My OS il Win 7, Node version is 0.10.22 and the SQLite is the the windows command-line 3.8.2.
Currently I am using your module inside node-webkit version 0.8.1... maybe that is the problem.

The same error happens if I change my the params with an empty array.
The error disappears only if I change the fields argument to null or an empty array.

If I run your last command db.query('select jhgfkjhg * from users', function(err, data){console.log(err);console.log(data);}) the result is always:
null and []

Tomorrow I'll do more tests...
Many Thanks

[WebReflection]

OK .. so, do not send arguments you don't need. Latest example is good, use that and forget the null arguments since it is not supported. Database speaking, dblite assumes you know what you are doing ;-)

That being said, it looks like node-webkit does not write to the stderr so dblite is unable to catch that.

can you try with both on('error') and a query such:

dblite(':memory:')
  .on('error', function(){
    console.log(arguments);
  })
  .query('qwoidj qwid jojdoqiw dj', function() {
    console.log(arguments);
  })
;

and tell me if anything at all happens? If not, I am not sure there is anything I can do :-(

[risingphoenix]

The only console.log that fires is the second and logs {"0":null}

I've tried to debug your code (only for good purpose ;) ) and the spawn goes only into the .stdout -> 'data' event and in the buffer there's only the SUPER_SECRET value
--- e+xntPsFGc51zZxkhlJSPuYsyjtwlSpoculblolr7es5Y633YLGZetH0eZbrPpL/QdoKBJCoxRYzxfb2ve4A1w==---

[WebReflection]

I am sorry, the second one should have been

dblite(':memory:')
  .on('error', function(){
    console.log(arguments);
  })
  .query('qwoidj qwid jojdoqiw dj', function(err, data) {
    console.log(arguments);
  })
;

with both arguments defined since arty is used in order to understand how to deal with the on error listener.

In any case it looks like there's nothing in the stderr … I will try to simulate this on a windows machine and with this node-webkit … if you could test latest snippet and same operation in regular windows node to compare results, I would really appreciate that. Thanks!

[risingphoenix]

Hello WebReflection
I have done quite a bit of testing.
Simply using the last script without passing through node-webkit, the result is always the same.

So I have tried a simply script on spawn and sqlite3

var test = function() {
  var EOL = require('os').EOL;
  var spawn = require('child_process').spawn;
  var child = spawn('sqlite3.exe');

  child.stdout.on('data', function(buf) {
    console.log('[OUT] '+buf);
  });
  child.stderr.on('data', function(buf) {
    console.log('[ERR] '+buf);
  });
  child.on('close', function(code) {
    console.log('[END] code', code);
  });

  child.stdin.write('select ciao;'+EOL);
  child.stdin.write('.quit'+EOL);
};

test();

And I have noticed that if I comment the last .quit the stderr data event doesn't fire up.
So i have tried to execute multiple command trying to force the stderr to drain the buffer like this:

 setTimeout(function() {
    child.stdin.write('select ciao;'+EOL+'select "secret";'+EOL);
  },1);
  setTimeout(function() {
    child.stdin.write('select ciao;'+EOL+'select "secret";'+EOL);
  },100);
  setTimeout(function() {
    child.stdin.write('select ciao;'+EOL+'select "secret";'+EOL);
  },300);
  setTimeout(function() {
    child.stdin.write('.quit'+EOL);
  }, 1000);

and the strange thing is that at the end of the script the log is:

[ERR] Error: near line 1: no such column: ciao
Error: near line 3: no such column: ciao
Error: near line 5: no such column: ciao

As if the commands were all chained in a single script.

Swapping the sqlite3.exe with the wmci (that opens an interactive shell, too) and launching a wrong command, all works correctly, here's the console log

[OUT] wmic:root\cli>
[ERR] cpuasd - Alias non trovato.
[OUT] wmic:root\cli>
[ERR] cpuasd - Alias non trovato.
[OUT] wmic:root\cli>
[ERR] cpuasd - Alias non trovato.
[OUT] wmic:root\cli>

The only difference I see is the "propt" of the shell.

I hope I've given other elements to figure out how to solve the problem...

[sangregoriopaolo]

The same thing happens to me too using dblite inside atom-shell :( Any advice?

[sangregoriopaolo]

Just for providing additional info, trying debugging the issue seems that both ondata and onerror are called, but the ondata is called before so the "wasError" variable has not been set yet, and once the onerror is then called, the $callback has been unset

[WebReflection]

@sangregoriopaolo do you have a basic example I can work on? I am still unable to reproduce the problem, not so easy to fix it. Also exact version of node and sqlite + platform would be great.

Thanks and apologies for any inconvenience.

[sangregoriopaolo]

You should not apologize for something you publish for free! :) Sure I'd be happy to help.

> sqlite3 --version
3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace

The same thing happens with the latest binary from sqlite3 website too.
I'm running OSX Yosemite 10.10.1

What I basically experienced trying to debug your code, is that it seems that the stderr is somehow buffered, or anyway comes up after the response from sqlite comes on the stdout.
The order your callbacks got called are first the "on data", and then the "on error" which does not find any callback because it has already been unset by the "on data" method.

Use case:

var dblite = require('dblite');
dblite(':memory:')
  .on('error', function(){
    console.log("Error" + JSON.stringify(arguments) );
  })
  .query('qwoidj qwid jojdoqiw dj', function() {
    console.log("Result" + JSON.stringify(arguments));
  })
;

Executing this returns:

Result{}
Error{"0":"Error: near line 1: near \"qwoidj\": syntax error\n"}

As you can see, result got displayed before.
If I can help in any other way please tell me! :)
I tried to debug the issue myself, but seems like sqlite populates stdout and stderr in this order, so maybe there is no thing we can do to fix it.

Thanks for your great library, that I'm actually using anyway :)

[WebReflection]

I a not sure why sqlite returns regular output on errors but it's kinda hard to find a way to work around this. If the problem is "bad queries are badly handled" it might end up as a wontfix simply because bad queries, being queries not a user but a developer input, should never happen (and users should never write them :P )

Will try to dig more on it thought, thanks for the help

[sangregoriopaolo]

Exactly! :) Actually I'm using it anyway for that reason. Thanks again for this beatiful library!

[rooflack]

Hi,
sorry for interrupting. I'm jumping into this thread just to let you know that I had exactly the same issue on Windows (W7), using the official SQLite3 CLI, and somehow found a workaround (which I don't really like but at least allows me to keep on developing in the meantime).
After pulling out a lot of hair, and crying as loud as I could, which I finally realized didn't help, I found this thread, which put me on the right way, hence this post.
So here it what happens:

• The CLI tool for SQLite3 (3.8.7.4 2014-12-09) flushes stdout after every query but does not explicitly flush stderr at any time.
• On Linux, it's fine because stderr is unbuffered, so every time an error is raised, stderr is written to and dblite is able to detect it.
• On Windows, on the other hand, stderr is fully buffered if redirected to anything else than the console. This means that if you run the CLI tool manually everything seems fine, but as soon as you pipe stderr (as dblite does), nothing is written until the process exits.

To go back to sangregoriopaolo's example, if you run it on Windows, the "error" line is actually printed out only when the sqlite3 child process ends. If you set ignoreErrors=false, for instance, you never get any error message (or get them only at random times, totally out of context, depending on the way the buffer is flushed.
So the workaround I'm using now is that I compiled my own version of the sqlite3 binary, using a modified shell.c where I added a few calls to fflush(stderr) (got the idea from this other thread although I'm not directly using the same code).
I don't know how OSX works regarding buffered output, but I thought this sounded similar enough to post this here.
As I don't think there is anything to do on dblite's side, it may be worth submitting this to the SQLite team, as I doubt this inconsistency between OSes is meant by design (I'm very new to both Node and SQLite and may have overlooked something, so I'll let you make this call, WebReflection).

Thanks a lot for this very good lib!

[WebReflection]

thank you @rooflack and I am on Linux indeed, hence probably my inability to reproduce problems.

it looks like the effort to work-around might be non reliable or too weird/compromising anyway, but the way I read your comment feels like much more could break and I don't like it so I'll try to figure out how/what to do anyway and see if I can fix All The Things !!! 💃

stay tuned

[rooflack]

Thanks @WebReflection for your (very) quick reply.
I think indeed that the only safe way to fix this is to have the SQLite guys integrate a new option in the CLI tool for output buffer management (allowing one to force buffered or unbuffered output via a command line option, falling back to the system's default if not provided), and to handle this on dblite's side with a version selector (.withSQLite-style).
But again my point of view may be biased by my newbieness to this environment.

Happy new programming year!