revCommitDatabse but no start transaction

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
hliljegren
Posts: 108
Joined: Sun Aug 23, 2009 7:48 am
Contact:

revCommitDatabse but no start transaction

Post by hliljegren » Thu Sep 08, 2016 3:47 pm

Can someone explain how revCommitDatabase works? As far as I can see there is no way to start the transaction. And according to another post here revCommitDatabase saves all changes since the last revCommitDatabase. But if that is true, should I then call revCommitDatabase do all my inserts, updates etc and then call revCommitDatabase again? And again if that is true how long will LiveCode wait before auto-commiting to the DB? But If I really want a single transaction, how do I start it? Currently I'm building a big transaction with "BEGIN ... COMMIT" But that makes it hard to use variable binding.

So can someone please enlighten me?
___________________________________
MacBook Pro, 15" 2.6GHz i7 Mac OS X 10.10.4
iMac 27", 3.2 GHz Quad i7, Mac OS 10.10.4
LiveCode 7.0.6 or 8.0dp4

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: revCommitDatabse but no start transaction

Post by MaxV » Wed Sep 14, 2016 2:34 pm

I prefer this way:

Code: Select all

put "BEGIN TRANSACTION ; " into tSQL
put "INSERT ... ; " after tSQL
put "UPDATE ... ; " after tSQL
...
put "COMMIT; " after tSQL
revExecuteSQL connID,tSQL
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

hliljegren
Posts: 108
Joined: Sun Aug 23, 2009 7:48 am
Contact:

Re: revCommitDatabse but no start transaction

Post by hliljegren » Wed Sep 14, 2016 2:37 pm

Well that's how I usually do it also, but as there is a revCommitDatabase I thought that someone could explain what it actually does...

Hmm, there is a difference between your solution and my ordinary solution. And that is that I call revExecuteSQL for each item otherwise I can't secure the input.

So, my normal procedure is to:

revExecuteSQL dbID, "BEGIN TRANSACTION;"
revExecuteSQL myID, "insert into mytable(id, name, date) values (:1, :2, :3)", "tId", "tName", "tSeconds"
...
revExecuteSQL dbID, "COMMIT;"

If you just build a long SQL string you need to ensure that you roll your own code to prevent SQL injection.
Last edited by hliljegren on Wed Sep 14, 2016 2:43 pm, edited 1 time in total.
___________________________________
MacBook Pro, 15" 2.6GHz i7 Mac OS X 10.10.4
iMac 27", 3.2 GHz Quad i7, Mac OS 10.10.4
LiveCode 7.0.6 or 8.0dp4

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: revCommitDatabse but no start transaction

Post by MaxV » Wed Sep 14, 2016 2:38 pm

Not all databases support the revCommitDatabase command. The command is currently supported by Oracle, PostgreSQL and SQLite databases only. Calling the revCommitDatabase command on any other type of database connection will have no affect.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

hliljegren
Posts: 108
Joined: Sun Aug 23, 2009 7:48 am
Contact:

Re: revCommitDatabse but no start transaction

Post by hliljegren » Wed Sep 14, 2016 2:44 pm

MaxV wrote:Not all databases support the revCommitDatabase command. The command is currently supported by Oracle, PostgreSQL and SQLite databases only. Calling the revCommitDatabase command on any other type of database connection will have no affect.
I know that but it still doesn't tell me what it does when I have no revStartDatabaseTransction...
___________________________________
MacBook Pro, 15" 2.6GHz i7 Mac OS X 10.10.4
iMac 27", 3.2 GHz Quad i7, Mac OS 10.10.4
LiveCode 7.0.6 or 8.0dp4

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: revCommitDatabse but no start transaction

Post by MaxV » Wed Sep 14, 2016 3:20 pm

hliljegren wrote: So, my normal procedure is to:

Code: Select all

revExecuteSQL dbID, "BEGIN TRANSACTION;"
revExecuteSQL myID, "insert into mytable(id, name, date) values (:1, :2, :3)", "tId", "tName", "tSeconds"
...
revExecuteSQL dbID, "COMMIT;"
If you just build a long SQL string you need to ensure that you roll your own code to prevent SQL injection.
No, bad way! Your way have very bad side effects in a multi user enviroment. You load the database of commands, and it waits a commit...
The Transaction idea is to compact command in a unique command, this way you risk too much uncompleted tasks, overloading or database and so on...
Try to limit as much a s possible the revExecuteSQL calls, and your program will super speed up! :D
SQL injections can't be totally prevented, you can use the *b binary method but it isn't 100% safe.
There is no way in any programming language to be 100% defended against SQL injections.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

hliljegren
Posts: 108
Joined: Sun Aug 23, 2009 7:48 am
Contact:

Re: revCommitDatabse but no start transaction

Post by hliljegren » Wed Sep 14, 2016 9:25 pm

MaxV wrote: No, bad way! Your way have very bad side effects in a multi user enviroment. You load the database of commands, and it waits a commit...
The Transaction idea is to compact command in a unique command, this way you risk too much uncompleted tasks, overloading or database and so on...
Try to limit as much a s possible the revExecuteSQL calls, and your program will super speed up! :D
SQL injections can't be totally prevented, you can use the *b binary method but it isn't 100% safe.
There is no way in any programming language to be 100% defended against SQL injections.
Well, I'm only using it for SQLite so the multi user doesn't apply. Have you tried it? For me leaving out the the begin and commit is the things that makes the difference. Not the number of calls to revExecuteSQL! Leaving begin and commit out increases the time by several magnitudes but multiple calls to revExecuteSQL doesn't. At least for me.

Also did a quick test against MariaDB (MySQL) with the same result!
___________________________________
MacBook Pro, 15" 2.6GHz i7 Mac OS X 10.10.4
iMac 27", 3.2 GHz Quad i7, Mac OS 10.10.4
LiveCode 7.0.6 or 8.0dp4

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: revCommitDatabse but no start transaction

Post by MaxV » Thu Sep 15, 2016 10:32 am

Yes, you are right, begin transaction speed up the database, because it analyzes all requests until commit and then decide how to do what you require in the fastest way.
However keep in mind that your way is very risky.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Post Reply

Return to “Databases”