Re: LC Server - MySQL - update about 2000 rows/records of a table

2017-12-30 Thread Sannyasin Brahmanathaswami via use-livecode 
Ditto what Mike said: 

" On mysql..  can't you prebuild your multi-command transaction in a variable"

I periodically do this kind of thing. assuming the transaction is exactly the 
same… one way is to build a single SQL query from your data.

Assume you need something like this, getting and SQL query into a string, from 
raw data with LC's text processing tools is easy:

INSERT INTO `mytable` (item,quantity,amount)
VALUES
('Oranges',3,5.00)
('Apples',10,6.00)
#etc..
#(no problem doing 2000 of these)
;

and then do the insert as a single query/transaction and let the dBase handle 
it.
BR




On 12/29/17, 3:05 PM, "use-livecode on behalf of Mike Bonner via use-livecode" 
 wrote:


(with a beginning and ending to the transaction) and then use revexecutesql
with a single call for the one big transaction?

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: LC Server - MySQL - update about 2000 rows/records of a table

2017-12-30 Thread Mark Schonewille via use-livecode 
The shell function can do this. I don't know if there could be a limit 
to the length of the shell command though.


Working example:

on mouseUp
   put shortfilepath("C:\Program Files\MySQL\MySQL Server 
5.6\bin\mysql.exe") into myFilePath

   -- the following line contains 2 insert commands
   put "insert into testtable (name) values ('Anne'); insert into 
testtable (name) values ('Belinda')" into mySql
   put myFilePath && "-uUSERNAME -pPASSWORD test -e" && quote & mySql & 
quote into myQuery

   get shell(myQuery)
end mouseUp

Kind regards,

Mark Schonewille
http://economy-x-talk.com
https://www.facebook.com/marksch

Buy the most extensive book on the
LiveCode language:
http://livecodebeginner.economy-x-talk.com

Op 29-Dec-17 om 21:53 schreef Matthias Rebbe via use-livecode:

Hi,

i need to update regularly about 2000 rows/records of a MySQL table with 
Livecode Server.

What is better?
Executing the 2000 update commands in one take using revexecuteSQL
or should i execute one update command after each other within a repeat loop?

What do you think?

Regards,

Matthias




___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: LC Server - MySQL - update about 2000 rows/records of a table

2017-12-29 Thread Dr. Hawkins via use-livecode 
On Fri, Dec 29, 2017 at 5:05 PM, Mike Bonner via use-livecode
 wrote:
> On mysql..  can't you prebuild your multi-command transaction in a variable
> (with a beginning and ending to the transaction) and then use revexecutesql
> with a single call for the one big transaction?

Not when I was trying a couple of years ago.

I tried both with and without including the BEGIN/END, which gets
added by LiveCode under some circumstances, and there's a thread in
the archives from it (err, do we keep those?  google my email,
livecode, and mySQL to get a start on one of the parasitic sites).

Apparently, the issue is (was?) livecode, and not mySQL.

As the only reason I had to use mySQL was that my web host provided
it, I switched to postgresql

-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: LC Server - MySQL - update about 2000 rows/records of a table

2017-12-29 Thread Matthias Rebbe via use-livecode 
Thanks to all.

As i wrote already, i am updating now one row after the other using a repeat 
loop. MySQL  is running on the same server as Livecode Server is. So it takes 
only one seconds or so to update all 2276 records.

Matthias


> Am 30.12.2017 um 02:05 schrieb Mike Bonner via use-livecode 
> >:
> 
> On mysql..  can't you prebuild your multi-command transaction in a variable
> (with a beginning and ending to the transaction) and then use revexecutesql
> with a single call for the one big transaction?
> 
> 
> On Fri, Dec 29, 2017 at 5:59 PM, Dr. Hawkins via use-livecode <
> use-livecode@lists.runrev.com > wrote:
> 
>> On Fri, Dec 29, 2017 at 12:53 PM, Matthias Rebbe via use-livecode
>> > wrote:
>> 
>>> Executing the 2000 update commands in one take using revexecuteSQL
>>> or should i execute one update command after each other within a repeat
>> loop?
>> 
>> Unless something has changed, you can't submit multiple mySQL commands
>> at once from LiveCode, whether you immediately execute them or not.
>> If the server is not local, you may find 2000 consecutive latencies to
>> be brutally long (as in minutes, not seconds)
>> 
>> You can, however, with postgreSQL and SQLite.  In fact, it's a couple
>> of insertions  done as a singe transaction with SQLite every time I
>> open a client, and a similar one to the posts server when I create a
>> new one.
>> 
>> 
>> --
>> Dr. Richard E. Hawkins, Esq.
>> (702) 508-8462
>> 
>> ___
>> use-livecode mailing list
>> use-livecode@lists.runrev.com 
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com 
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

Matthias Rebbe
Tel +49 5741 31
‌https://matthiasrebbe.eu ‌
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: LC Server - MySQL - update about 2000 rows/records of a table

2017-12-29 Thread Mike Bonner via use-livecode 
On mysql..  can't you prebuild your multi-command transaction in a variable
(with a beginning and ending to the transaction) and then use revexecutesql
with a single call for the one big transaction?


On Fri, Dec 29, 2017 at 5:59 PM, Dr. Hawkins via use-livecode <
use-livecode@lists.runrev.com> wrote:

> On Fri, Dec 29, 2017 at 12:53 PM, Matthias Rebbe via use-livecode
>  wrote:
>
> > Executing the 2000 update commands in one take using revexecuteSQL
> > or should i execute one update command after each other within a repeat
> loop?
>
> Unless something has changed, you can't submit multiple mySQL commands
> at once from LiveCode, whether you immediately execute them or not.
> If the server is not local, you may find 2000 consecutive latencies to
> be brutally long (as in minutes, not seconds)
>
> You can, however, with postgreSQL and SQLite.  In fact, it's a couple
> of insertions  done as a singe transaction with SQLite every time I
> open a client, and a similar one to the posts server when I create a
> new one.
>
>
> --
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
>
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: LC Server - MySQL - update about 2000 rows/records of a table

2017-12-29 Thread Dr. Hawkins via use-livecode 
On Fri, Dec 29, 2017 at 12:53 PM, Matthias Rebbe via use-livecode
 wrote:

> Executing the 2000 update commands in one take using revexecuteSQL
> or should i execute one update command after each other within a repeat loop?

Unless something has changed, you can't submit multiple mySQL commands
at once from LiveCode, whether you immediately execute them or not.
If the server is not local, you may find 2000 consecutive latencies to
be brutally long (as in minutes, not seconds)

You can, however, with postgreSQL and SQLite.  In fact, it's a couple
of insertions  done as a singe transaction with SQLite every time I
open a client, and a similar one to the posts server when I create a
new one.


-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: LC Server - MySQL - update about 2000 rows/records of a table

2017-12-29 Thread Matthias Rebbe via use-livecode 
Thanks Rick for sharing your thoughts.

I am just updating only one field in each record and i am not locking the 
record.
The complete update when doing one after one in a repeat loop does not take 
more than 1 second.
So i think i will go that way.

Thanks again.

Regards,
Matthias 

> Am 29.12.2017 um 23:19 schrieb Rick Harrison via use-livecode 
> >:
> 
> Hi Matthias,
> 
> Are there going to be users on your system at the time of the update?
> Are you locking the record or records before your update, and then
> unlocking them?
> 
> You should do a test with the repeat loop to see how long the process takes.
> If it is depriving your users of the database for too long you may want to
> think of a way which will impact their use the least.  Perhaps picking a
> time of day when your system is getting the least amount of traffic
> would be best.
> 
> Hope this helps.
> 
> Rick
> 
>> On Dec 29, 2017, at 3:53 PM, Matthias Rebbe via use-livecode 
>> > wrote:
>> 
>> Hi,
>> 
>> i need to update regularly about 2000 rows/records of a MySQL table with 
>> Livecode Server.
>> 
>> What is better?
>> Executing the 2000 update commands in one take using revexecuteSQL
>> or should i execute one update command after each other within a repeat loop?
>> 
>> What do you think?
>> 
>> Regards,
>> 
>> Matthias 
>> 
>> 
>> ___
>> use-livecode mailing list
>> use-livecode@lists.runrev.com 
>> Please visit this url to subscribe, unsubscribe and manage your subscription 
>> preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com 
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: LC Server - MySQL - update about 2000 rows/records of a table

2017-12-29 Thread Rick Harrison via use-livecode 
Hi Matthias,

Are there going to be users on your system at the time of the update?
Are you locking the record or records before your update, and then
unlocking them?

You should do a test with the repeat loop to see how long the process takes.
If it is depriving your users of the database for too long you may want to
think of a way which will impact their use the least.  Perhaps picking a
time of day when your system is getting the least amount of traffic
would be best.

Hope this helps.

Rick

> On Dec 29, 2017, at 3:53 PM, Matthias Rebbe via use-livecode 
>  wrote:
> 
> Hi,
> 
> i need to update regularly about 2000 rows/records of a MySQL table with 
> Livecode Server.
> 
> What is better?
> Executing the 2000 update commands in one take using revexecuteSQL
> or should i execute one update command after each other within a repeat loop?
> 
> What do you think?
> 
> Regards,
> 
> Matthias 
> 
> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode