support sqlite 3.41.0 by avoiding double quotes for values

[Pyrolistical]

In standard sql single quotes are for values and double quotes are for identities like column names. Although sqlite does support double quote values this seems to have changed in 3.41.0.

I have sqlite 3.41.0 installed and when this extension uses it, I get the following error when I try to open a database:

[12:12:12 AM][vscode-sqlite][ERROR] Failed to open database '...\database.db': Parse error near line 4: no such column: table
  aster                                 WHERE (type="table" OR type="view")     
                                      error here ---^

I was able to track down the query:

vscode-sqlite/src/sqlite/schema.ts

Lines 46 to 50 in c09e348

	const tablesQuery = `SELECT name, type FROM sqlite_master
	WHERE (type="table" OR type="view")
	AND name <> 'sqlite_sequence'
	AND name <> 'sqlite_stat1'
	ORDER BY type ASC, name ASC;`;

If one tries to run that query on an empty sqlite database in the cli, we get a similar error

>sqlite3 
SQLite version 3.41.0 2023-02-21 18:09:37
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT name, type FROM sqlite_master
   ...> WHERE (type="table" OR type="view")
   ...> AND name <> 'sqlite_sequence'
   ...> AND name <> 'sqlite_stat1'
   ...> ORDER BY type ASC, name ASC;
Parse error: no such column: table
  SELECT name, type FROM sqlite_master WHERE (type="table" OR type="view") AND n
                                     error here ---^
sqlite>

The workaround is to pin the sqlite client to the one included with the extension. Set the vscode setting
sqlite.sqlite3 to the correct client in .vscode\extensions\alexcvzz.vscode-sqlite-0.14.1\bin

[DowneyX]

getting same issue. thanks for the work around.

[GreLeBr]

Unless I am misunderstanding it, it is not quite a fix, you just going back to the old sqlite version that the extension uses by default.
I am having the same issue when using latest sqlite: sqlite-tools-win32-x86-3410200 , sqlite-tools-win32-x86-3410100, sqlite-tools-win32-x86-3410000.
I don't know how many versions of sqlite there are before that but I could get it to work with sqlite-tools-win32-x86-3400000.

[Pyrolistical]

@GreLeBr correct, its a workaround to restore functionality at the time of the extenion's release but does not fix the underlying issue. however the actual fix should be pretty simple. just replace double quotes with single quotes

[rod2ik]

Hi,

Thanks for this awesome extension !

Unfortunately, I am having the same error on Linux (Manjaro), with latest sqlite3 version installed : v. 3.42.0

I am afraid the (grateful) workaround does not work on Linux, unfortunately..

Fixing the extension path to /usr/bin/sqlite3 (my correct path) does not solve the error.

Could you please fix this ? Thankxx by advance

[smargoli2]

Hey @Pyrolistical ,

Can you please provide more detail on what I actually need to change?

Is it this setting of the extension? What value am I putting there?
I tried copying the path to the exe from the extension but the database is not opening. (It's not showing the error either.)

[Pyrolistical]

@smargoli2 That should be correct

[smargoli2]

Thanks, the full path that I used is "C:\Users\Sora Teichman.vscode\extensions\alexcvzz.vscode-sqlite-0.14.1\bin\sqlite-v3.26.0-win32-x86.exe", the database is not opening though.

[Pyrolistical]

@smargoli2 you are missing a slash before .vscode

[smargoli2]

It is working, I was just looking in the wrong place :) Thank you!

[kdcllc]

I am running into this issue on Linux box as well. I changed the path to point to binary of the extension and still getting the error

[novojitdas]

Hey @Pyrolistical ,

Can you please provide more detail on what I actually need to change?

Is it this setting of the extension? What value am I putting there? I tried copying the path to the exe from the extension but the database is not opening. (It's not showing the error either.)

I also solved my problem following this. thanks @smargoli2 and @Pyrolistical . you guys saved me from stress.

[jmforsythe]

A different fix for this: I went to C:\Users<me>.vscode\extensions\alexcvzz.vscode-sqlite-0.14.1\dist\extension.js
At line 1, column 29338 you want to replace
type=\"table"\ OR type=\"view\" with type='table' OR type='view'

[Pyrolistical]

type=\"table"\ OR type=\"view\" with type='table' OR type='view'

@jmforsythe make a PR 😃

[jmforsythe]

#241 Already exists, my fix just does what it does after the plugin is installed.

[b01x]

The following process worked for me on Windows 10
From the windows command line I can run the following command and it runs sqlite without any issues, but this does not work in VSCODE
C:\Users\Joel.vscode\extensions\alexcvzz.vscode-sqlite-0.14.1\bin\sqlite-v3.26.0-win32-x86.exe

In VSCODE, I changed the slashes from back slashes to forward slashes in the sqlite extension configuration and it worked. I tested the following line in the VSCODE terminal to verify it runs.
C:/Users/Joel/.vscode/extensions/alexcvzz.vscode-sqlite-0.14.1/bin/sqlite-v3.26.0-win32-x86.exe

[bartek1a1b]

Where can I modify path in vscode? Where Can I found it?

[houd1ni]

A different fix for this: I went to C:\Users.vscode\extensions\alexcvzz.vscode-sqlite-0.14.1\dist\extension.js At line 1, column 29338 you want to replace type=\"table"\ OR type=\"view\" with type='table' OR type='view'

This definitely works! Thanks. Maybe make a fork of this estension ?

[LeTuChiBao]

In SQlite Extention setting custom . SQLite3 command or executable path. => sqlite.sqlite3 it's will work oke