How to force a handler to finish it's job before proceeding with something else

LiveCode is the premier environment for creating multi-platform solutions for all major operating systems - Windows, Mac OS X, Linux, the Web, Server environments and Mobile platforms. Brand new to LiveCode? Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

How to force a handler to finish it's job before proceeding with something else

Post by sphere » Mon Apr 08, 2019 12:28 pm

Hi,

i have a handler which only has to run one time at the first startup of the app.
This handler downloads data via PHP and then stores it locally in an sqlite db via a repeat loop.
So if i call this handler in the preOpenstack folder or the openStack folder like:

on preOpenstack
if blablabla does not exist then
downloadstuff
end if (you get the idea)
anotherhandler
got to card"nowme"
end preOpenstack

It seems like the handler downloadstuff is not ended yet or anotherhandler is already running.
I tried to put wait for messages in between the handlers and put a Return 1 at the end of the handler (its not a function) but i'm not sure if it works.
Send to me in a tick or similar seems also not to be doing it.

Any usefull tip about how to do this?

thanks!

jmburnod
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 2718
Joined: Sat Dec 22, 2007 5:35 pm
Location: Genève
Contact:

Re: How to force a handler to finish it's job before proceeding with something else

Post by jmburnod » Mon Apr 08, 2019 1:15 pm

Hi,
What is the script of "downloadstuff" message ?
Best
Jean-Marc
https://alternatic.ch

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: How to force a handler to finish it's job before proceeding with something else

Post by sphere » Mon Apr 08, 2019 1:23 pm

Hi Jean-Marc,

it is a handler like this (i don't have it with me right now):

Code: Select all

on downloadstuff
put URL "https://mywebsite/afolder/getstuff.php" into tMe (the PHP fetches a few columns of the DB)
URLdecode(tMe) into tBetter
TextDecode(tBetter, UTF8) into tMuchBetter
set the itemdel to tab
repeat for x=1 to the number of line of tMuchBetter
put item 1 of tMuchBetter intot tId
put item 2 of tMuchBetter into tThis
put item 3 of tMuchBetter into tThat
(then the part for sqlite to INSERT every item on the same Id row as in the remote DB)
(but i don't have it exactly in mind, but it works)
end repeat
return 1 (Which if i'm correct is for functions)
end downloadstuff

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: How to force a handler to finish it's job before proceeding with something else

Post by Thierry » Mon Apr 08, 2019 1:57 pm

sphere wrote:

Code: Select all

on preOpenstack
if blablabla does not exist then
downloadstuff
end if  (you get the idea)
anotherhandler
got to card"nowme"
end preOpenstack
Any usefull tip about how to do this?
Hi sphere,

a bit shooting in the dark, but you could try this:

Code: Select all

on preOpenstack
    send "preOpenStack_2" to me in 1 tick
end preOpenstack

on preOpenStack_2
   if blablabla does not exist then
      downloadstuff
   end if  (you get the idea)
   anotherhandler
   got to card"nowme"
end preOpenstack_2
Does it change something?

Best,

Thierry
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: How to force a handler to finish it's job before proceeding with something else

Post by sphere » Mon Apr 08, 2019 2:09 pm

Hi Thierry,

thanks i wil try it tonight, i'm somewhere else now.
I thought that this send only worked at functions, but perhaps i'm wrong.

I will report back.

Thanks

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: How to force a handler to finish it's job before proceeding with something else

Post by Thierry » Mon Apr 08, 2019 2:12 pm

I thought that this send only worked at functions.
Certainly not true. I do that very often.
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: How to force a handler to finish it's job before proceeding with something else

Post by sphere » Mon Apr 08, 2019 6:40 pm

Thanks, i thought i tried it before.
But it seems to works just fine now.

Thanks a lot!

Cordialement

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: How to force a handler to finish it's job before proceeding with something else

Post by AxWald » Tue Apr 09, 2019 11:05 am

Hi,

1.) When "PreOpenStack" runs it's very early in the startup process. LC might not yet have its libraries (revliburl, revdatabaselibrary ...) loaded - so you better don't use such stuff here. "OpenCard" of the main card of the main stack runs quite some time later, for instance.

2.)
sphere wrote:
Mon Apr 08, 2019 1:23 pm
repeat for x=1 to the number of line of tMuchBetter
put item 1 of tMuchBetter intot tId
put item 2 of tMuchBetter into tThis
put item 3 of tMuchBetter into tThat
(then the part for sqlite to INSERT every item on the same Id row as in the remote DB)
So basically this is sent to the SQLite:

Code: Select all

INSERT INTO `t_table` SET `id`= 1, `fld1` = 'a', `fld2` = 'A';
INSERT INTO `t_table` SET `id`= 2, `fld1` = 'b', `fld2` = 'B';
INSERT INTO `t_table` SET `id`= 3, `fld1` = 'c', `fld2` = 'C';
...
The problem here is that this is very slow: SQLite will make a transaction for every single INSERT! (Link)
Ways faster is this:

Code: Select all

INSERT INTO `t_table` (`id`, `fld1`, `fld2`) VALUES
(1, 'a', 'A'),
(2, 'b', 'B'),
(3, 'c', 'C'),
...
So you basically have this code in LC:

Code: Select all

function makeSQL tMuchBetter
   set itemdel to tab
   put "INSERT INTO `t_table` (`id`, `fld1`, `fld2`) VALUES " & CR into myVar
   repeat for each line L in tMuchBetter
      put "(" & item 1 of L & ", " & \
            swote(item 2 of L) & ", " & \
            swote(item 3 of L) & ")," & CR after myVar
   end repeat
   delete char -2 to -1 of myVar
   return myVar & ";"
end makeSQL 

function swote what
   return "'" & what & "'"
end swote
Doing it this way is magnitudes faster - after all it's just 1 SQL call ;-)

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: How to force a handler to finish it's job before proceeding with something else

Post by sphere » Tue Apr 09, 2019 6:36 pm

Yes that is somewhat how i handle it, and then it still takes some time, but it is only the first run.
So now i made a copy of my stack and changed some things, and one of them is i will call the script in the OpenCard handler of the first card (Which is used as a splash) and not in the preOpenStack anymore.
This works good and now i have time to get a notice on screen to wait a while only this first start.

So my handler is this:

on muchbetter
local tUrl,tPP,tId,tPl,tPc,tLo,tLa,tSQL,tFields,x,tPA
lock screen
put gHomeUrl & "getmystuff.php" into tUrl
put URL tUrl into tPP
put URLDecode(tPP) into tPA --better
put textDecode(tPA,"UTF-8") into tPP--much better (no weird characters after fetching utf8 from db)
--answer tPP
put "id, yo, man" into tFields
set the itemdel to tab
repeat for each line tLine in tPP
put item 1 of tLine into tId
put item 2 of tLine into tPl
put item 3 of tLine into tPc
--put item 4 of tLine into tFor
--put item 5 of tLine into tLater
put "INSERT INTO mystuff (" & tFields & ") VALUES (:1,:2,:3)" into tSQL #:4,:5
revExecuteSQL gLocalDbID, tSQL, "tId", "tPl", "tPc" # ,"tFor", "tLater"
wait 0 with messages
end repeat
unlock screen
end muchbetter

i'm missing the revexecutesql in your script, please explain how it could work with one SQL call?
It's 6000 somewhat lines to insert

thanks!

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: How to force a handler to finish it's job before proceeding with something else

Post by AxWald » Wed Apr 10, 2019 8:39 am

Hi,
sphere wrote:
Tue Apr 09, 2019 6:36 pm
i'm missing the revexecutesql in your script, please explain how it could work with one SQL call?
OK, at first I streamlined the function to create the VALUES. I works now w/o knowing the number of fields, and it auto-quotes values that are not numeric:

Code: Select all

function makeSQLValues theData
   --  theData must be tab-CR delimited!
   set itemdel to tab
   repeat for each line L in theData
      put "(" after myVar
      repeat for each item I in L
         if I is a number then put I after myVar else  put swote(I) after myVar
         put comma after myVar
      end repeat
      delete last char of myVar
      put ")" & comma & CR after myVar
   end repeat
   delete char -2 to -1 of myVar
   return myVar & ";"
   --  returns a valid VALUES string, text values autoquoted. You may omit the semicolon.
end makeSQLValues 

function swote what
   return "'" & what & "'"
end swote
This is now a nice generic function, suitable to be added to our library. Functions for quoting with " (kwote?) and ' (here: swote) should be there anyways.

But now we fill the database:

Code: Select all

on mouseUp
   --  assuming we already have correctly formatted tab-CR data in "theData"
   --  and are connected to "gLocalDbID"
   --  ...
   put the millisecs into t1
   put "INSERT INTO `myStuff` (`id`,`yo`,`man`) VALUES " & CR into StrSQL
   --  always using accent graves (`) for table and field names saves much trouble!
   put makeSQLValues(theData) into myValues    --  we create the VALUES
   put the number of lines of myValues into myRecordNum    --  we count the records
   revExecuteSQL gLocalDbID, (StrSQL & myValues)    --  here we sent the data to the db
   
   if the result begins with "revdberr" then    --  NEVER EVER FORGET THIS!!!
      set the clipboardData["text"] to StrSQL
      answer error "Your query failed! Reason: " & CR & the Result & CR & CR & \
            "(I have copied the SQL string sent. Paste into a text editor to find the mistake!)" \
            titled "Database error"
   else if the result <> myRecordNum then    --  And check if really all records were inserted!!!
      set the clipboardData["text"] to myValues
      answer warning "Not all records could be handled properly!" & CR & \
            "Inserted: " & the result & " of " & myRecordNum & CR & CR & \
            "(I have copied the VALUES sent. Paste into a text editor to find the mistake!)" \
            titled "Database error"
   else
      answer information myRecordNum & " records sucessfully inserted. Time used: " & \
            round((the millisecs - t1)/1000,1) & " secs." titled "wOOt!"
   end if
end mouseUp
You see, we do 1 (one) database call. And we check the result, so we know what has happened. And if something goes haywire, we have the data at hand, to past & analyze/ correct.

PS: the "lock screen" in your script isn't necessary - you don't change the screen during the script ...

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: How to force a handler to finish it's job before proceeding with something else

Post by sphere » Wed Apr 10, 2019 6:57 pm

Great! Never knew it would be possible with ony one call to the DB, but i know mostly basics to use MYSQL and stuff. I also did the tutorial for the middleware using PDO on the Database section.
I know about the ' (also called tick), same as in mysql.


With KWOTE you mean quote
i don't know swote, i only know QUOTE and DOUBLE QUOTE

I will give this a try and see if i can speed up things up. AS for now it works but it is slow, and even with a warning for the slow start at 1st run, People are less patience these days.

Thanks! i will report back once i tried.

Sphere

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: How to force a handler to finish it's job before proceeding with something else

Post by Klaus » Wed Apr 10, 2019 7:02 pm

sphere wrote:
Wed Apr 10, 2019 6:57 pm
With KWOTE you mean quote
i don't know swote, i only know QUOTE and DOUBLE QUOTE
No, he means SWOTE! 8)
AxWald wrote a little function swote(), scroll down his first script to see it.

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: How to force a handler to finish it's job before proceeding with something else

Post by sphere » Wed Apr 10, 2019 7:21 pm

Entschuldigung Klaus :)

I did not see that, ok that makes it clear :oops:

sphere
Posts: 1145
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: How to force a handler to finish it's job before proceeding with something else

Post by sphere » Thu Apr 11, 2019 8:49 pm

Hi Axwald,

i played with your code and it seems 10 times faster than my code. But...
I have to solve some things.
In my code i got a datatype mismatch, which i have not with your code, maybe due to the quotes.
i have items which begin their naming with 't or 's (which stands actually for Het and Des in Dutch) hat could be issuing the datatypemismatch although it get's perfectly displayed.
By the way the erors are very summier so...

Now i changed this line:

Code: Select all

if I is a number then put I after myVar else  put swote(I) after myVar
to only

Code: Select all

put swote(I) after myVar
It might be good for the ID, but not for the data as 1 of the items on a row is 1234 - 5678,
so four numbers a space a dash a space four numbers, and if i leave the line of code as it was it would not be one whole item i think

Now my code took a big 42.6seconds, enough for people to stop the app, before it proceeds to the next card
With your code (with insert error so i copied the time result before the else) it says it takes 4.3 seconds but, But i'm not convinced yet if really speeds it up, it also waits until proceeding to the next card, and if i look at the actual sqlite file via explorer then you still see it building up in KiloBytes.
The end result is for both the same 152 KB in size. for both it counts 12020 rows, while actually it are 6030 rows somewhat in my mysql db. So i'm not getting that.

The one faster thing is to create only for this data an separate db file and embed i as non-stack file.
Have to test some more.

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: How to force a handler to finish it's job before proceeding with something else

Post by AxWald » Fri Apr 12, 2019 2:22 pm

Hi,

removing the auto-quoting shouldn't be a problem - as long as you're quoting everything (as you do). It doesn't hurt to quote numerics.
When this is done, and when you're running the code as written in the "mouseUp" handler here, you should get an answer dialogue:
6030 records successfully inserted. Time used: 4.3 secs.
Ooops - this is constructed using numbers from your post, and it made me think. 4.3 sec is a looong time for a local SQLite!

I just tested this with some real data, ~240KB, 6457 records. And came upon a few shortcomings:
  1. When manipulating delimited lists, LC has a nasty habit: If the last item in a list is empty, it will be ignored. This most often happens in "repeat for each item" constructs, in our case in the "makeSQLValues" function: If the last item in a line is empty, this line will be an item short in the VALUES list, and SQLite will choke.
    Easy remedy: When constructing data to be worked with in such an environment, be sure the last item in a record is never empty! ID fields are great for such ("id" INTEGER PRIMARY KEY NOT NULL). SELECT them as last item, and you're fine.
    Hard remedy: Change the "makeSQLValues" function to accommodate for this. This is possible, but has a heavy cost in speed. I usually choose to keep the speed. Changing the order of fields in a records costs nothing but occasional debugging, if you forget to do it ...
    .
  2. revExecuteSQL doesn't return "revdberr" - as does "revdb_execute()", the function equivalent I usually use. Since INSERT should return a number, we better test for this.
    .
  3. I should test my code better before posting!
So I made a small stack (attached), and reworked the code. Inserting the 240KB:
6457 records successfully inserted. Time used: 607 millisecs.
This looks OK. For my very old developing machine.
(AMD Athlon II X2 B24 @ 3000MHz, 8GB RAM, LC & Data on HD, Win 10-64 pro, LC 6.7.10)

Now with such INSERTs, it's quite easy to duplicate your data. Maybe it's the reason for:
for both it counts 12020 rows, while actually it are 6030 rows somewhat in my mysql db
To avoid such, I have created an index in my SQLite, for a field that is unique:

Code: Select all

CREATE UNIQUE INDEX "t_Test_ANum" ON "t_test" ("ANum" ASC)
This effectively prohibits duplicates. When trying to INSERT a second time, the db complains:
UNIQUE constraint failed: t_test.ANum
and we get an error dialogue :)

To get rid of such, I'd do a cleanUp in the db before inserting fresh data:

Code: Select all

DELETE FROM `t_test`; VACUUM;
This deletes all data from our table, and compacts the database - you start again with a shiny fresh one! And it doesn't cost much:
Your table has been emptied, and your database has been compacted.
Time used: 206 millisecs.
OK, maybe this helps a bit. Have fun with!
Attachments
INSERTtest.zip
A small test stack, with test data & a SQLite
(42.99 KiB) Downloaded 207 times
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Post Reply

Return to “Getting Started with LiveCode - Experienced Developers”