older sqlite3 versions support (no -csv switch)

[mik977]

I have an older sqlite3 (3.3.6) version installed on a machine. This version does not support the -csv switch, therefore the spawn fails.

All other switches are supported, i.e.

-column -html -line -list -separator

The same can be achieved with list + separator ... I think -csv is only a shortcut for that...

-list -separator ','

ist the same as

-csv

I checked!

What do you think?

[WebReflection]

I don't have your version so it's hard for me to verify what I do would work for your case.

Anyway, in the early message you sent me via email you wrote about .mode csv and I wonder if this command would be supporter:

).concat('.mode csv', '-cmd') instead of ).concat('-csv')

above passes all tests and bench, etc etc ... while sending a mode at initialization time causes many problems to the normal, initial flow ...

I will test that and double check things are really the same (but if you could test above code would be great)

[WebReflection]

tested right now, the test with new lines and disturbing queries fails using -list -separator ','

try changing with this ).concat(',', '-separator', '-list') and you'll see it.

So, list + separator is not as reliable as csv apparently, or maybe I need to specify the line too ? (nope, line ain't working neither)

[mik977]

Hi, I tested on the command line and didn't actually change the code. But from the above post I think you might have missed a ' ... so instead of

.concat(',', '-separator', '-list')

should be

.concat("','", '-separator', '-list')

note the comma enclosed in apostrophes...

In the -help they specify the separator must be enclosed in apostropes.

haven't tried but could work.

[mik977]

The .mode csv works as well but it should be executed as a query somewhere...

db.query('.mode csv')

[WebReflection]

hey @mik977 here the thing ... I cannot just put return self.query('.mode csv') at object creation time because that causes itself a "non readyness" of the object since every query, every operation, is performed asynchronously.

This means all tests could fail and if these do, I would not compromise dblite for a version of sqlite3 that has nothing to do with 99.9999999% of the use cases, you know what I mean?

If there is a tested way to work at startup time, and I will test with quoted separator as you suggested ASAP, I will do the change without a glitch. Until my worst test on reliability fails, I am not planning to make anything not secure anymore because of this way-too-old sqlite3 version.

Thanks for your understanding, coming back soon on this.

[WebReflection]

P.S. of course for your very specific use case .. you can be the one to ensure that db.query(".mode csv") call every time you create an instance ... if you say that works, then that means dblite is already good enough. An early crash would be a more valid feature request, or filed bug.

[mik977]

Oh, i see. I've run the make test and many test cases fail... it is not just the -csv. I guess I will have to upgrade sqlite. Thanks.

[mik977]

Sorry for bothering you again.

Are the err callback supposed to work on insert and update queries too?

i.e. should this work:

db.query('insert into table1 (:id,:aaa)', params, function(err){ console.log(err); });

?

I am NOT getting the error in the callback. The error is reported in the logs as before.

Is the callback function signature OK for an insert query?

I also noticed that giving a make test, 2 of them fail:

• new lines and disturbing queries (always fails)
• dual arguments with error behaviour (sometimes, not always - very weird)

I installed the most recent sqlite3 verson 3.8.1 - built from source

I can send you the test logs if you like.

Can you check?

On 07. 11. 2013 07:04, Andrea Giammarchi wrote:

P.S. of course for your very specific use case .. you can be the one to ensure that call every time you create an instance ... if you say that works, then that means dblite is already good enough. An early crash would be a more valid feature request, or filed bug.

— Reply to this email directly or view it on GitHub https://github.com/WebReflection/dblite/issues/18#issuecomment-27939772.

[WebReflection]

you need a signature of two arguments ... this was necessary for the transition between old way to handle errors and the new one, with double arguments. You have only one argument in there, that won't work for now.

[mik977]

Hi, I've also tried with 2 arguments, but the callback is called with the first arg as null ... meaning everithing is OK. And the error is reported to the logs.

Something must be wrong because the "dual arguments with error behaviour" test sometimes passes and sometimes fails. I've run the tests several times consequently. The tests pass, then a few times they would fail, and then they pass again. The "new lines and disturbing queries" test always fails. Is this OK?

[WebReflection]

no, I have green always everywhere ... which version of node and which version of sqlite3 are you running?

[mik977]

2 consecutive runs:

1.st RUN

[linkng01@vps dblite]$ make test npm test

dblite@0.4.1 test /home/linkng01/domains/dev.link-go.si/node/test/dblite node test/.test.js

/tmp/dblite.test.sqlite

main

OK passes: 1, fails: 0, errors: 0

create table if not exists

OK passes: 1, fails: 0, errors: 0

100 sequential inserts 100 records in 2.755 seconds

OK passes: 1, fails: 0, errors: 0

1 transaction with 100 inserts 200 records in 0.042 seconds

OK passes: 1, fails: 0, errors: 0

auto escape

OK passes: 1, fails: 0, errors: 0

auto field fetched 201 rows as objects in 0.003 seconds

OK passes: 1, fails: 0, errors: 0

auto parsing field fetched 201 rows as normalized objects in 0.002 seconds

OK passes: 1, fails: 0, errors: 0

many selects at once 255 different selects in 0.057 seconds

OK passes: 1, fails: 0, errors: 0

db.query() arguments [ [ '1' ] ] [ [ '2' ] ] [ { id: 1 } ] [ { id: 2 } ]

OK passes: 5, fails: 0, errors: 0

utf-8 ÂĽ ¡ ÂŁ ¡ ⏠¡ $ ¡ ¢ ¡ ⥠¡ ⢠¡ ⣠¡ ⤠¡ ⼠¡ ⌠¡ ⧠¡ ⨠¡ ⊠¡ ⪠¡ ⍠¡ ⭠¡ ⎠¡ ⯠¡ âš

OK passes: 1, fails: 0, errors: 0

new lines and disturbing queries

1. asynchronous test #1: unknown

FAILURE passes: 0, fails: 1, errors: 0

erease file

OK passes: 1, fails: 0, errors: 0

does not create :memory: file

OK passes: 1, fails: 0, errors: 0

cannot close twice

OK passes: 1, fails: 0, errors: 0

-header flag

OK passes: 2, fails: 0, errors: 0

-header flag with fields too

OK passes: 2, fails: 0, errors: 0

runtime headers closing

OK passes: 2, fails: 0, errors: 0

runtime headers with fields too

OK passes: 2, fails: 0, errors: 0

single count on header closing

OK passes: 1, fails: 0, errors: 0

null value test

OK passes: 2, fails: 0, errors: 0

right order of events

OK passes: 2, fails: 0, errors: 0

combined fields and headers

OK passes: 1, fails: 0, errors: 0

notifies inserts or other operations too

OK passes: 2, fails: 0, errors: 0

automagic serialization

OK passes: 1, fails: 0, errors: 0

lastRowID with headers too

OK passes: 1, fails: 0, errors: 0

dual arguments with error behavior Error: near line 1: near "CAUSING": syntax error

1. asynchronous test #1: there is an error
2. asynchronous test #1: there is no data

FAILURE passes: 0, fails: 2, errors: 0

dual arguments with data behavior OK passes: 2, fails: 0, errors: 0

---

FAILURE 3 Failures

npm ERR! weird error 1 npm ERR! not ok code 0 make: *\ [test] Error 1

2.nd RUN

[linkng01@vps dblite]$ make test npm test

dblite@0.4.1 test /home/linkng01/domains/dev.link-go.si/node/test/dblite node test/.test.js

/tmp/dblite.test.sqlite

main

OK passes: 1, fails: 0, errors: 0

create table if not exists

OK passes: 1, fails: 0, errors: 0

100 sequential inserts 100 records in 2.876 seconds

OK passes: 1, fails: 0, errors: 0

1 transaction with 100 inserts 200 records in 0.042 seconds

OK passes: 1, fails: 0, errors: 0

auto escape

OK passes: 1, fails: 0, errors: 0

auto field fetched 201 rows as objects in 0.004 seconds

OK passes: 1, fails: 0, errors: 0

auto parsing field fetched 201 rows as normalized objects in 0.001 seconds

OK passes: 1, fails: 0, errors: 0

many selects at once 255 different selects in 0.051 seconds

OK passes: 1, fails: 0, errors: 0

db.query() arguments [ [ '1' ] ] [ [ '2' ] ] [ { id: 1 } ] [ { id: 2 } ]

OK passes: 5, fails: 0, errors: 0

utf-8 ÂĽ ¡ ÂŁ ¡ ⏠¡ $ ¡ ¢ ¡ ⥠¡ ⢠¡ ⣠¡ ⤠¡ ⼠¡ ⌠¡ ⧠¡ ⨠¡ ⊠¡ ⪠¡ ⍠¡ ⭠¡ ⎠¡ ⯠¡ âš

OK passes: 1, fails: 0, errors: 0

new lines and disturbing queries

1. asynchronous test #1: unknown

FAILURE passes: 0, fails: 1, errors: 0

erease file

OK passes: 1, fails: 0, errors: 0

does not create :memory: file

OK passes: 1, fails: 0, errors: 0

cannot close twice

OK passes: 1, fails: 0, errors: 0

-header flag

OK passes: 2, fails: 0, errors: 0

-header flag with fields too

OK passes: 2, fails: 0, errors: 0

runtime headers closing

OK passes: 2, fails: 0, errors: 0

runtime headers with fields too

OK passes: 2, fails: 0, errors: 0

single count on header closing

OK passes: 1, fails: 0, errors: 0

null value test

OK passes: 2, fails: 0, errors: 0

right order of events

OK passes: 2, fails: 0, errors: 0

combined fields and headers

OK passes: 1, fails: 0, errors: 0

notifies inserts or other operations too

OK passes: 2, fails: 0, errors: 0

automagic serialization

OK passes: 1, fails: 0, errors: 0

lastRowID with headers too

OK passes: 1, fails: 0, errors: 0

dual arguments with error behavior

OK passes: 2, fails: 0, errors: 0

dual arguments with data behavior OK passes: 2, fails: 0, errors: 0

---

FAILURE 1 Failures

npm ERR! weird error 1 npm ERR! not ok code 0 make: *\ [test] Error 1

[mik977]

Latest versions.

node: 0.10.21 sqlite3: 3.8.1 (built today)

[WebReflection]

that CAUSING error ... is actually the expected error. Not sure what's going on with the other one. I've run these tests in every OS except Windows ... maybe it's a new line problem in windows. Are you in windows?

[mik977]

Sorry, haven't noticed your last post. No I do everything on Linux. The second error is not always there, as you can see from the above post. It looks like it is some time(async)-related issue. Did you test with those node and sqlite3 versions? The fact is, in my application I can't get the callback call on error when there is an INSERT sql error...

[WebReflection]

so, you should never have untested SQL queries in your system, regardless dblite … an insert that might throw an error is a very bad sign for many reasons.

Anyway, if you don't use a double variable signature, the error is not sent.

As example, you cannot do

db.query("INSERT INTO table VALUES (?)", [123], function(err){});

You need to specify both err and result for now function(err, res){}, because of the transition between one variable signature and two.

I am unable to reproduce an inconsistent error in all platforms I could test: including Raspberry Pi, Allwinner 10 and 20, Windows, Mac, Ubuntu

If you have a very specific version of Linux maybe I can try in there … sorry but it is hard for me to help in this way :-/

[mik977]

OK, no problem. For now I switched to node-sqlite3 and all is fine. I'll keep an eye on dblite for the future because It looked like the most elegant solution. Thank you very much for your time. I really appreciated you help. It might be something about my system. Unfortunately I am stuck with a not so recent CentOS 5.5 for now.

Miha

On 12.11.2013 19:16, Andrea Giammarchi wrote:

so, you should never have untested SQL queries in your system, regardless dblite … an insert that might throw an error is a very bad sign for many reasons.

Anyway, if you don't use a double variable signature, the error is not sent.

As example, you cannot do

|db.query("INSERT INTO table VALUES (?)", [123], function(err){});|

You need to specify both err and result for now, because of the transition between one variable signature and two.

I am unable to reproduce an inconsistent error in all platforms I could test: including Raspberry Pi, Allwinner 10 and 20, Windows, Mac, Ubuntu

If you have a very specific version of Linux maybe I can try in there … sorry but it is hard for me to help in this way :-/

— Reply to this email directly or view it on GitHub https://github.com/WebReflection/dblite/issues/18#issuecomment-28318372.

Miha Kralj LiNK d.o.o. Nova Gorica Partizanska ulica 49 5000 Nova Gorica Slovenija