Handling large data upload to mySQL

[bsouthuk]

Hi

Wonder if someone can help with me this.

I have an analysis application developed in Livecode that my customers are using. A problem they are experiencing is the 'not responding' and fading out of livecode when uploading large sets of data to my mySQL database. For example large amounts of data within a field containing over 100,000 lines of text needs to be uploaded to 1 field within my table in mySQL which can take some amount of time, depending on connection etc.

This is not a problem, however many users think the system has crashed - even though a 'please wait' stack opens. Users start clicking, then livecode app fades and can crash.

Is there a way where the user can keep pressing/clicking etc without the APP fading then creashing?

I am using the following code to upload to mySQL server:

Code: Select all

on mouseUp
 
//CONNECT TO DATABASE

-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID

-- set up the connection parameters - edit these to suit your database
put "xxxxxxxx" into tDatabaseAddress
put "xxxxxxxx" into tDatabaseName
put "xxxxxxxx" into tDatabaseUser
put "xxxxxxxx" into tDatabasePassword

-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into gConnectionID
//answer info "Proposal Generated." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
end if

//ADD TO DATABASE


-- check the global connection ID to make sure we have a database connection
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if

-- edit these variables to match your database & table
-- this assumes a table called Table1 with 3 fields
put field "Ref" into tRef
replace "Ref: " with empty in tRef

put "UPDATE Analyser Login SET RentalM1=" & "'" & (field "Rental M1") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL
put "UPDATE Analyser Login SET CallDataM1=" & "'" & (field "CallData M1") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL2

put "UPDATE Analyser Login SET RentalM2=" & "'" & (field "Rental M2") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL3
put "UPDATE Analyser Login SET CallDataM2=" & "'" & (field "CallData M2") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL4

put "UPDATE Analyser Login SET RentalM3=" & "'" & (field "Rental M3") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL5
put "UPDATE Analyser Login SET CallDataM3=" & "'" & (field "CallData M3") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL6
-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
//put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL

-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL 
revExecuteSQL gConnectionID, tSQL2
revExecuteSQL gConnectionID, tSQL3
revExecuteSQL gConnectionID, tSQL4
revExecuteSQL gConnectionID, tSQL5
revExecuteSQL gConnectionID, tSQL6


-- check the result and display the data or an error message
if the result is a number then
   answer info "Files Uploaded"
   put "Files Uploaded" into field "Status" of group "AnalyseMaster"
          
else
   answer error "There was a problem updating the record to the database:" & cr & the result
   exit to top
end if

//DISCONNECT FROM DATABASE

global gConnectionID

-- if we have a connection, close it and clear the global connection ID
if gConnectionID is a number then
revCloseDatabase gConnectionID
put empty into gConnectionID
end if

end mouseUp

Thank you in advance.

Daniel

[bangkok]

Is there a way where the user can keep pressing/clicking etc without the APP fading then creashing?

Yes. Disable the button when your script begins. And enable it at the end.

Code: Select all

on mouseup
disable me

..........
enable me
on mouseup

Other method : show/hide a large empty and opaque field that will cover all your card and its objects. It allows you to display a large text "wait...." and it prevent any action from the user.

[bsouthuk]

Hi

Thank you for you reply, but this does not stop the app from 'not responding' and fading but just disables the button.

When a large set of data is being uploaded to mySQL the hanging cursor appears after about 5 seconds then users are able to click anywhere on the app. The APP will then fade which leads the user to believe the APP has crashed.

Can we stop this from happening?

[bangkok]

I thought you just wanted to prevent the user to do anything else but wait.

What you want to do is to refresh the display ?

Put within your loop :

Code: Select all

wait with messages

Furthermore : do you execute each INSERT ? Or do you group several INSERT into one query ? You can considerably reduce time by using this technique.

[bsouthuk]

Hi - I'M actually performing 'UPDATE'. I use the following script:

Code: Select all

on mouseUp

//CONNECT TO DATABASE

-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID

-- set up the connection parameters - edit these to suit your DATABASE
put "xxxxxxxx" into tDatabaseAddress
put "xxxxxxxx" into tDatabaseName
put "xxxxxxxx" into tDatabaseUser
put "xxxxxxxx" into tDatabasePassword

-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into gConnectionID
//answer info "Proposal Generated." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
end if

//ADD TO DATABASE


-- check the global connection ID to make sure we have a database connection
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if

-- edit these variables to match your database & table
-- this assumes a table called Table1 with 3 fields
put field "Ref" into tRef
replace "Ref: " with empty in tRef

put "UPDATE Analyser Login SET RentalM1=" & "'" & (field "Rental M1") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL
put "UPDATE Analyser Login SET CallDataM1=" & "'" & (field "CallData M1") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL2

put "UPDATE Analyser Login SET RentalM2=" & "'" & (field "Rental M2") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL3
put "UPDATE Analyser Login SET CallDataM2=" & "'" & (field "CallData M2") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL4

put "UPDATE Analyser Login SET RentalM3=" & "'" & (field "Rental M3") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL5
put "UPDATE Analyser Login SET CallDataM3=" & "'" & (field "CallData M3") & "'" & "WHERE ID=" & "'" & (tRef) & "'" into tSQL6
-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
//put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL

-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL 
revExecuteSQL gConnectionID, tSQL2
revExecuteSQL gConnectionID, tSQL3
revExecuteSQL gConnectionID, tSQL4
revExecuteSQL gConnectionID, tSQL5
revExecuteSQL gConnectionID, tSQL6


-- check the result and display the data or an error message
if the result is a number then
   answer info "Files Uploaded"
   put "Files Uploaded" into field "Status" of group "AnalyseMaster"
          
else
   answer error "There was a problem updating the record to the database:" & cr & the result
   exit to top
end if

//DISCONNECT FROM DATABASE

global gConnectionID

-- if we have a connection, close it and clear the global connection ID
if gConnectionID is a number then
revCloseDatabase gConnectionID
put empty into gConnectionID
end if

end mouseUp

So basically does 1 revExecuteSQL at a time. There is no loop?

[bangkok]

So basically does 1 revExecuteSQL at a time. There is not loop?

I don't understand the way you build your UPDATE queries... For instance what field "CallData M3" and field "Rental M3" contain ? 100 000 lines of text ? What's the point to put such a huge quantity of data into just one 1 column and 1 row in a database ?

If not, then why your 6 queries would take so long time to execute ?

I suppose you can't give us the content of one of those 6 queries. But you could at least check their length :

Code: Select all

answer the length of tSQL4

In a nutshell : 6 "normal" UPDATE queries should run a in the blink of an eye, even if your server is very slow and very "remote".

If you have like 100 000 lines of data to update, then you should perhaps review your database structure and use a loop.

Such a loop would allow you to process a "batch" of update queries, refresh the display (with "wait with messages") and then process the next batch etc.

[bsouthuk]

I completely understand, I will use a loop - thank you for your help!

[phaworth]

This doesn't address your Livecode issue but here's a few things to think about on the SQL side which should help.

You should have a BEGIN and END around your SQL statements. That will cut down on the total amount of time it takes for mySQL to process things and it also makes sure that the database will be in a consistent state should anything go wrong with one of your SQL statements.

The table name, Analyser Login, has a space in it and should have double quotes round it. Kinda surprised the SQL works at all without them actually.

It looks like you could use one UPDATE statement instead of the several you have now since they all update the same table and have the same WHERE clause. Each statement you send to mySQL incurs its own overhead so putting all the updates in one statement would speed things up. If you go that route, you wouldn't need the BEGIN/END mentioned above since they are implicit for single statements.

It looks like you started to use the :1,:2,:3 notation then decided not to? I would strongly recommend doing that. It will protect against SQL injection attacks and make sure that any characters in your data that should be escaped (like single quote) will be processed correctly. Plus it will make your code more readable. I like to use an array for that purpose so I don't have to declare lots of local variables.

Taking the last two things together, your UPDATE statement would be:

UPDATE "Analyser Login" SET RentalM1=:1, CallDataM1=:2,RentalM2=:3,CallDataM2=:4,RentalM3=:5,CallDataM3=:6 WHERE ID=:7

Then put the values for RentalM1 into tArray[1], the data for CallDataM1 into tArray[2], etc and make one call to revExecuteSQL passing the name of the array as the third parameter (don't forget to put it in quotes).

Hope that helps,

Pete