Forum > Databases
[SOLVED] Is SQLite case sensitive on Linux
Gizmo:
I did say it was hero help, that he gave me!! :-)
Gizmo:
OK, so, the plot thicken.
I managed to compile libsqlite3.so.0 on Linux by downloading the SQLite source (https://sqlite.org/2021/sqlite-amalgamation-3350500.zip) and executing :
gcc -shared -o libsqlite3.so.0 -fPIC sqlite3.c
I then copied that so.0 file to my project folder, and adjusted the dlopen path to the literal location (because using just the filename makes it load the Linux distribution version) so that it used the most recent version instead of my installed version (which is 3.22.0 2018-01-22 ).
LibHandle := dlopen('/home/me/Documents/Projects/QH/libsqlite3.so.0', RTLD_LAZY);
I then run the steps that execute the SQL code, and no error :-) Hooray
But, there is now no result, either! :-( Hoorooo :-(
So, this confirms the error message was related to the version being used. The problem now is a) ensuring my program load the so file I ship with it, and not the users inbuilt version. Is the best way to do that : LibHandle := dlopen('./libsqlite3.so.0', RTLD_LAZY); ? Is LoadLibrary a better choice than dlopen (I use LoadLibrary for Windows now, so thinking of trying to use it for both Linux and OSX too)
And b) working out why I now do not get any data returned by the SQL.
dseligo:
Or, I could rewrite SQL so row_number() is not used.
Gizmo:
That would be super helpful. I hope it is indeed row_number causing the problem though. It seems plausible for sure but it is tricky to confirm. I wondered if we could use COUNT instead of ROW_NUMBER? But having tried that in Linux I still get the same error. It is complaining at one of the opening brackets. I'm not sure if it is the one that immediately follows row_number or one of the other ones.
I've had a go at using SQLite in Linux natively to see if the error is any more meaningful. As the screenshot shows, the table is created OK. But then row_number and count both produce the same error (this is using the native version of SQLite to my Linux system, v3.22.0 2018-01-22) but it does not tell me which bracket is the problem one
So I headed over to https://www.eversql.com/sql-syntax-check-validator/ and pasted the SQL. Please note that this SQL works flawlessly on Windows! :
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT ROW_NUMBER() OVER ( ORDER BY FileName ) rownum, *FROM ( SELECT a.FileName, a.FilePath AS FilePathA, a.FileHash AS FileHashA, b.FilePath AS FilePathB, b.FileHash AS FileHashB FROM TBL_COMPARE_TWO_FOLDERS a LEFT JOIN TBL_COMPARE_TWO_FOLDERS b ON a.FileName = b.FileName AND b.FilePath = :FilePathB WHERE a.FilePath = :FilePathA UNION SELECT c.FileName, d.FilePath AS FilePathA, d.FileHash AS FileHashA, c.FilePath AS FilePathB, c.FileHash AS FileHashB FROM TBL_COMPARE_TWO_FOLDERS c LEFT JOIN TBL_COMPARE_TWO_FOLDERS d ON c.FileName = d.FileName AND d.FilePath = :FilePathA WHERE c.FilePath = :FilePathB )
and it says :
You have an error in your SQL syntax; it seems the error is around: '* from ( select a.FileName, a.FilePath as FilePathA, a.F' at line 6
So there must be something afoot that SQLite on Windows is happy with but SQLite on Linux and OSX do not like. But I am just not good enough with SQLite to work out what. As Martin suggests, I have also asked on SO (https://stackoverflow.com/questions/67433489/sqlite-syntax-error-you-have-an-error-in-your-sql-syntax-it-seems-the-error-i) - I'll no doubt get flamed on there but hey ho. I am desperate here.
If we can simplify it, even if it has a small performance hit, but will work on the SQLite versions listed that should enable greater reach of the users, to be able to use the program.
Because we will have the same problem for OSX too. I have Big Sur (latest OSX) and it has SQLite version 3.32.3 2020-06-18
Gizmo:
OK, this just gets weirder, and weirder.
On the Windows platform, I tried the new version of my program using the old version of SQLite that I used before all this great new SQL that dseligo has added. Up until the other day, when I compiled new SQLIte DLL's to v3.35.5.0, I had used former version 3.21.0.0. So I tried using the v3.21.0.0 DLL's with the new code. And on Windows, it all still works perfectly.
So I don't know how that is possible, given that row_number was added in v3.25.x, but it seems to work just fine even when using v3.21.0.0 DLLs.
The problem is, that suggests to me that the problem on Linux etc is indeed syntactical, and not related to the use or row_number, and Windows is somehow not complaining about it but Linux etc is. So now I am even more confused.
UPDATE AS BELOW : IT MUST BE VERSION RELATED
Navigation
[0] Message Index
[#] Next page
[*] Previous page