SQLite3Connection Access violation lazarus / 16-Databases ---------------------------------------------------------------------- chrnobel · 2011-11-20 22:45 Hi. Basically very simple, a form with just a SQLite3Connection, enter the name of the database, and then try to connect it gives an Access violation. I can use just a Sqlite3Dataset and a DataSource, but this does not work with BLOB fields, and there is no way to manipulate the SQL sentences. And Zeos, which I have used previous with MySQL is not an option, as it corrupts images stored in a BLOB. Any suggestions? ---------------------------------------------------------------------- Leledumbo · 2011-11-20 23:02 for SQLite, the database name is the path to the file, what have you given there? ---------------------------------------------------------------------- chrnobel · 2011-11-20 23:14 The name of the database, in my test called test1.sqlite. Trying just to enter the path: ~/udvikling/projekter/lazprojects/sqlite_picture still gives an Access violation (and makes no sense as the database then is not specified) and full path plus name: ~/udvikling/projekter/lazprojects/sqlite_picture/test1.sqlite makes no difference - access violation ---------------------------------------------------------------------- chrnobel · 2011-11-21 00:00 Did some further experimenting. Removed SQLite3dataset from Lazarus, and now I do not get the access violation anymore. I can now state a database name (if the field is empty, trying to connect gives an error that databasename is not filled), but nothing happens - actually I can fill in anything, but not the full name including the path, where I get an 'unable to open database file' As said if just entering the databasename (having the database in the same directory) gives no errors when connecting, but then when trying to test a SQL sentence from the Query states that the table I try to make a select from does not exist. So are there any spooky options that have to be set for the connection? (And why does the SQLite3Connection have fields for Hostname and user for a SQLite database??) ---------------------------------------------------------------------- chrnobel · 2011-11-21 01:26 Even more experimenting. The problem is that the path have to be absolute, so this: ~/udvikling/projekter/lazprojects/sqlite_picture/test1.sqlite does not work. It has to be like this: /home/username/udvikling/projekter/lazprojects/sqlite_picture/test1.sq lite ---------------------------------------------------------------------- Leledumbo · 2011-11-21 02:14 QuoteThe problem is that the path have to be absolute, so this: ~/udvikling/projekter/lazprojects/sqlite_picture/test1.sqlite does not work. It has to be like this: /home/username/udvikling/projekter/lazprojects/sqlite_picture/test1.sq liteAFAIK ~ isn't interpreted by the OS, but each program does the expansion. That's why you need absolute path instead. ---------------------------------------------------------------------- chrnobel · 2011-11-21 16:01 QuoteAFAIK ~ isn't interpreted by the OS, but each program does the expansion. That's why you need absolute path instead.Seems like, but that is to overcome, as long as one is aware. However played a little more with a simple project: Dropped on a form, very basic: TSQLite3Connection TSQLTransaction TSQLQuery TDatasource TDBNavigator TDBGrid The grid is now populated with the values from the database, no problem. However if I try to add another (using the navigator) record, it is added, but only in memory, so refreshing the database, and the new record is gone. So are there any properties that have to be set (doing exactly the same with Zeos worked out of the box, but as said, Zeos unfortunately corrupts BLOBs, leaving that as a no-goer)? Another thing, it seems like the above solution also locks the database - any suggestions? Tnx Christian ---------------------------------------------------------------------- Lacak2 · 2011-11-22 06:28 Quote from: chrnobel on November 21, 2011, 05:01:51 pmHowever if I try to add another (using the navigator) record, it is added, but only in memory, so refreshing the database, and the new record is gone. http://wiki.freepascal.org/SQLdb_Tutorial1#Editing_data_using_the_grid Quote from: chrnobel on November 21, 2011, 05:01:51 pmAnother thing, it seems like the above solution also locks the database - any suggestions? It is how TSQLiteConection works for now. In general in SQLite only one process can access database file until releases all prepared statements and commits. ---------------------------------------------------------------------- chrnobel · 2011-11-22 10:55 Quotehttp://wiki.freepascal.org/SQLdb_Tutorial1#Editing_data_using_the _gridTnx. QuoteIt is how TSQLiteConection works for now. In general in SQLite only one process can access database file until releases all prepared statements and commits.Yes, I understand that SQLite locks the entire database when writing, but it should be possible to have the application to cache what is read (the result in the grid), and then only locking the database when the actual update is done? ---------------------------------------------------------------------- chrnobel · 2011-11-22 11:42 QuoteIt is how TSQLiteConection works for now. In general in SQLite only one process can access database file until releases all prepared statements and commits. Actually I am also facing another problem, I am not able to update any records within the same program, as the database is locked. ---------------------------------------------------------------------- Lacak2 · 2011-11-22 13:24 Quote from: chrnobel on November 22, 2011, 11:55:38 amYes, I understand that SQLite locks the entire database when writing, but it should be possible to have the application to cache what is read (the result in the grid), and then only locking the database when the actual update is done? Good idea, but AFAIK once select statement is prepared and data are read and cashed, this statement is not "unprepared", so file remains locked also after select completes. I am not sure, but IMO you must at least explicitly COMMIT transaction to release lock. But as I wrote I doubt, that it will be sufficient ... there is missing functionality in TSQLiteConnection, which will finalize (or reset) all prepared statements ... I have plans to test it From SQLite docs: "A statement finishes when its prepared statement is reset or finalized." http://www.sqlite.org/lang_transaction.html ---------------------------------------------------------------------- Lacak2 · 2011-11-22 13:27 Quote from: chrnobel on November 22, 2011, 12:42:42 pmActually I am also facing another problem, I am not able to update any records within the same program, as the database is locked. Are you sure, that you do not run two processes (for example Lazarus and your application) at same time ? ---------------------------------------------------------------------- chrnobel · 2011-11-22 13:50 Quote from: Lacak2 on November 22, 2011, 02:27:12 pmAre you sure, that you do not run two processes (for example Lazarus and your application) at same time ? I fell in a pitfall here, yes, as I was testing my project by just compiling from the IDE. Maybe Lazarus should look at how it is done in MSEGUI as it works much better there - including gridupdate.