revCommitDatabse but no start transaction
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 108
- Joined: Sun Aug 23, 2009 7:48 am
- Contact:
revCommitDatabse but no start transaction
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?
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
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
Re: revCommitDatabse but no start transaction
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
-
- Posts: 108
- Joined: Sun Aug 23, 2009 7:48 am
- Contact:
Re: revCommitDatabse but no start transaction
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.
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
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
Re: revCommitDatabse but no start transaction
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
-
- Posts: 108
- Joined: Sun Aug 23, 2009 7:48 am
- Contact:
Re: revCommitDatabse but no start transaction
I know that but it still doesn't tell me what it does when I have no revStartDatabaseTransction...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.
___________________________________
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
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
Re: revCommitDatabse but no start transaction
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...hliljegren wrote: So, my normal procedure is to:If you just build a long SQL string you need to ensure that you roll your own code to prevent SQL injection.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;"
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!
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
-
- Posts: 108
- Joined: Sun Aug 23, 2009 7:48 am
- Contact:
Re: revCommitDatabse but no start transaction
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.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!
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.
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
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
Re: revCommitDatabse but no start transaction
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.
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w