Recent

Author Topic: SQLite3Connection Access violation  (Read 8247 times)

chrnobel

  • Sr. Member
  • ****
  • Posts: 271
SQLite3Connection Access violation
« on: November 20, 2011, 11:45:08 pm »
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

  • Hero Member
  • *****
  • Posts: 8141
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: SQLite3Connection Access violation
« Reply #1 on: November 21, 2011, 12:02:56 am »
for SQLite, the database name is the path to the file, what have you given there?

chrnobel

  • Sr. Member
  • ****
  • Posts: 271
Re: SQLite3Connection Access violation
« Reply #2 on: November 21, 2011, 12:14:09 am »
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

  • Sr. Member
  • ****
  • Posts: 271
Re: SQLite3Connection Access violation
« Reply #3 on: November 21, 2011, 01:00:12 am »
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

  • Sr. Member
  • ****
  • Posts: 271
Re: SQLite3Connection Access violation
« Reply #4 on: November 21, 2011, 02:26:01 am »
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.sqlite

Leledumbo

  • Hero Member
  • *****
  • Posts: 8141
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: SQLite3Connection Access violation
« Reply #5 on: November 21, 2011, 03:14:06 am »
Quote
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.sqlite
AFAIK ~ isn't interpreted by the OS, but each program does the expansion. That's why you need absolute path instead.

chrnobel

  • Sr. Member
  • ****
  • Posts: 271
Re: SQLite3Connection Access violation
« Reply #6 on: November 21, 2011, 05:01:51 pm »
Quote
AFAIK ~ 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

  • Guest
Re: SQLite3Connection Access violation
« Reply #7 on: November 22, 2011, 07:28:04 am »
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.

http://wiki.freepascal.org/SQLdb_Tutorial1#Editing_data_using_the_grid

Another 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

  • Sr. Member
  • ****
  • Posts: 271
Re: SQLite3Connection Access violation
« Reply #8 on: November 22, 2011, 11:55:38 am »
Quote
http://wiki.freepascal.org/SQLdb_Tutorial1#Editing_data_using_the_grid
Tnx.
Quote
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.
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

  • Sr. Member
  • ****
  • Posts: 271
Re: SQLite3Connection Access violation
« Reply #9 on: November 22, 2011, 12:42:42 pm »
Quote
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.
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

  • Guest
Re: SQLite3Connection Access violation
« Reply #10 on: November 22, 2011, 02:24:49 pm »
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?
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

  • Guest
Re: SQLite3Connection Access violation
« Reply #11 on: November 22, 2011, 02:27:12 pm »
Actually 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

  • Sr. Member
  • ****
  • Posts: 271
Re: SQLite3Connection Access violation
« Reply #12 on: November 22, 2011, 02:50:30 pm »
Are 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.