My First SQLite Project

You can share your experience with HMG. Share with some screenshots/project details so that others will also be benefited.

Moderator: Rathinagiri

User avatar
Rathinagiri
Posts: 5181
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 141 times
Been thanked: 131 times
Contact:

Re: My First SQLite Project

Post by Rathinagiri » Wed Sep 23, 2009 9:49 am

Hi,

I think this would help you.

http://sqlite.org/lang_transaction.html

Once you give "Begin transaction" query and send the first insert/update query, the locks are obtained.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
mol
Posts: 2876
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 135 times
Been thanked: 66 times
Contact:

Post by mol » Wed Sep 23, 2009 12:13 pm

I thought about situation like this:

Client must edit record of database and this operation lasts a few minutes.
In the same time other operators must modify other records.
I can't block whole table for indeterminable time.

User avatar
Rathinagiri
Posts: 5181
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 141 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Wed Sep 23, 2009 12:50 pm

It won't happen. Because, you can not prolong a lock/query as you like.

'Begin transaction' doesn't lock the database/table. But, the insert/update/delete queries would. Once the query is over, the lock is released. This is my understanding.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
mol
Posts: 2876
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 135 times
Been thanked: 66 times
Contact:

Post by mol » Wed Sep 23, 2009 5:13 pm

I must think about it and try - at friday I will have a few time. I'll write about my experiences. Many thanks Rathi.

User avatar
mol
Posts: 2876
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 135 times
Been thanked: 66 times
Contact:

Post by mol » Thu Sep 24, 2009 7:03 pm

I thought about another one table where I would put ID of edited records and remove it after transaction. But - when program crashes, or is not closed normally, ID of record will still exist in the table...
This is my first project with SQL database. Prior I've used sometimes SQL interactively - rather for some reports...
My thinking is still based on DBF file with record locking & unlocking...

User avatar
mol
Posts: 2876
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 135 times
Been thanked: 66 times
Contact:

Post by mol » Thu Sep 24, 2009 8:30 pm

I found something like:

Code: Select all

SELECT * FROM TABLE FOR UPDATE
but... it generates error :(

User avatar
Rathinagiri
Posts: 5181
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 141 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Fri Sep 25, 2009 3:38 am

Can you please provide a sample?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
mol
Posts: 2876
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 135 times
Been thanked: 66 times
Contact:

Post by mol » Fri Sep 25, 2009 5:42 am

I've modified your sample called "SQLITE1" by adding two buttons and two functions - for viewing record and locking record and second for modifying record.

PS.

I've tried "SELECT.... LOCK IN SHARE MODE" but it also causes error. :(
Attachments
sqlite_MOL.zip
(10.53 KiB) Downloaded 229 times

User avatar
Rathinagiri
Posts: 5181
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 141 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Fri Sep 25, 2009 6:19 am

Hi,

IMHO, in SQL, you don't have any Lock in shared mode command.

However, running first the command "Begin Transaction" would give you the control over the necessary locks. From "Begin Transaction" until giving "End Transaction/Commit/Roll Back" command, your lock will be in place.

The syntax of begin transaction and its features, from SQLite Doc:

Image

Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, you are guaranteed that no other thread or process will be able to read or write the database until the transaction is complete.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
mol
Posts: 2876
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 135 times
Been thanked: 66 times
Contact:

Post by mol » Fri Sep 25, 2009 11:05 am

I found something like this:
http://www.praetoriate.com/oracle_tips_for_update.htm

but it's for Oracle 9i and don't wanne work with our sample with Harbour and sqlite.

I've tried to lock record in my modified function:

Code: Select all

function ChangeRecord
	local aTemp := {space(40)}
	local aResult := {}
	local cRecID := "Giri10"
	
	// Wait for input record #id for change
	cRecID := InputBox("ID of record to edit: ", "Input window", cRecID )
	cRecID := rtrim(cRecID)
	miscsql(dbo,"")
	
	aResult := sql(dbo, "Select  text2 from new where text = '" + cRecID +"';")
	
	if len(aResult)>0
		// lock record by transaction which write non-changed record
		
		if !MOL_Sql(dbo, "BEGIN TRANSACTION; UPDATE new SET text2= '" +aResult[1,1] +"' WHERE text= '" + cRecID +"';")
			MsgStop("Record " +cRecID +" locked by another user! Try later!")
			MOL_SQL(dbo, "ROLLBACK;")
			return .f.
		else
			
			aTemp := InputWindow("Put your value for TEXT2", {"New value:"}, {  aResult[1,1] }, {40})
			if !empty(aTemp[1])
				cQstr := "ROLLBACK; BEGIN TRANSACTION; UPDATE new set text2= '" +aTemp[1] +"' where text='" +cRecID + "'; "
			    if !miscsql(dbo,cQstr)
					msginfo("Error during writing!")
				else
					msgbox("TEXT2 saved OK")
					RefreshTable()
				endif
			else
				mol_sql(dbo,"ROLLBACK;")
			endif
		endif
	endif
	miscsql(dbo,"COMMIT;")
return

but - first statement of "BEGIN TRANSACTION" locks whole table, not single row :(

I have no idea how to make record locking and without it, sqlite is useless in network environment for me!

Post Reply