Forum > Databases
[SOLVED] Is SQLite case sensitive on Linux
Gizmo:
Hi folks
I've had some utterly hero help from dseligo her in the forums. He has helped me so much with lots of SQL for SQLite which I use with my application.
But, I keep hitting snags when I take working, valid SQL code from Windows that runs just fine, to Linux and OSX. And I have a feeling it is case sensitivity, as some Googling suggests that it is sensitive to table names. I keep getting errors like :
"raised exception class 'ESQLDatabaseError' with message:
SQLite3Connection1 : near "(": syntax error"
But, I've gone through and checked the CREATE statement with all my other references to the tables, and the case seems to be consistant. So I'm not certain it is case sensitivty. But as it works fine on Windows, I am assuming it must be.
Q1) Is SQLite case sensitive on Linux?
Q2) Is SQLite case sensitive on OSX?
Q3) Is there a way to simply disable SQL case sensitivity within my Lazarus project? Or a way to ensure that when its compiled, the resulting Linux combiled binary contains SQL that is in the appropriate case?
Q4) If Q3 is "No", is there a way to automatically correct\format large SQL statements to the correct formatting\case?
Thanks
lucamar:
I'm not entirely sure, having used it but little, but AFAIK SQLite is not case sensitive, except with respect to the database name itself, which depends on the underlying file system.
One thing that could be happening instead is that you have got different versions with slightly "different" (read: "buggy" or "patched") sintaxes. It would help troubleshooting to see the statements giving errors and in which version they work (the Windows one) and which don't (the Linux and Mac OS ones).
dseligo:
--- Quote from: lucamar on May 06, 2021, 07:10:35 pm ---One thing that could be happening instead is that you have got different versions with slightly "different" (read: "buggy" or "patched") sintaxes. It would help troubleshooting to see the statements giving errors and in which version they work (the Windows one) and which don't (the Linux and Mac OS ones).
--- End quote ---
I think that too, I asked him to check SQlite version on OSX and Linux (in PM).
I think the problem is in use of row_number() window function which was added in 3.25.0 version of SQlite.
Gizmo:
OK, so on Windows we are using SQLite v3.35.5.0 valid as of April 2021 (replacing former version 3.21.0.0)
On Linux Mint, I have version 3.22.0 2018-01-22. On Apple OSX Big Sur (latest OSX) I have version 3.32.3 2020-06-18. So seemingly Apple and Linux LTS systems are significantly behind v3.35.5.0
So, given dseligo's remark above, this might explain the problem! I just need to try and find a v3.35.5.0 debian package, or compile it from source I suppose. And then try.
Problem next, though (assuming that does work) is how I ensure my users, who will not all have bang up to date bleeding edge Linux distributions, can use the application. But I guess that's up to them. Stay current or not...we can't change the world can we. But then I too am one of those who is sticking with what I like and know works (Linux Mint v19)
UPDATE: https://sqlite.org/2021/sqlite-tools-linux-x86-3350500.zip has a pre-compiled Linux binary, v3.35.5.0! So I hoped I could just ship that, but I need the SO library file, not the complete binary, called libsqlite3.so.0. I will probably have to compile from source.
The code, for reference, is :
--- Code: Pascal [+][-]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";}};} ---// COMPARE TWO FOLDERS tab update grid routineprocedure TfrmSQLiteDBases.UpdateGridCOMPARETWOFOLDERSTAB(Sender: TObject); begin try sqlCOMPARETWOFOLDERS.Close; //sqlCOMPARETWOFOLDERS.SQL.Text := 'SELECT * FROM TBL_COMPARE_TWO_FOLDERS'; //DS (original) sqlCOMPARETWOFOLDERS.SQL.Text:= //DS (new) '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)'; sqlCOMPARETWOFOLDERS.ParamByName('FilePathA').AsString:=FFilePathA; //DS (new) sqlCOMPARETWOFOLDERS.ParamByName('FilePathB').AsString:=FFilePathB; //DS (new) SQLite3Connection1.Connected := True; SQLTransaction1.Active := True; sqlCOMPARETWOFOLDERS.Open; [b]// ERROR IS THROWN HERE[/b] frmDisplayGrid3.dbGridC2F.Options := frmDisplayGrid3.dbGridC2F.Options + [dgAutoSizeColumns]; except on E: EDatabaseError do begin MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0); end; end;end;[/pascal]
lucamar:
Quite a statement, that! :o
If the error is in the SQL, might not you might find better help in the SQLite forum?
Just saying :-[
Navigation
[0] Message Index
[#] Next page