Help on MYSQL... system is hanging... large file

[garyb]

Guys I needs some advice, I have a MYSQL database on a window 7 environment. The file I am reading is 2m records. I want to navigate the file from record 1 to 2m. Based on criteria do some modifications on the data.

I connect fine however after reading about 10k records the LiveCode system hangs/Freezes/STops. I have tried to read the data in on small batchs but have no luck.

I had a similar issue with MS Access and had to change the locks in the registry. Not sure if this is the same issue.

I am pretty frustrated and after spending 8 hour behind the PC I have now decided to go to the community for help.

Thanks in advance if you can give me some advice.

Gary

Below I have attached
1) The script that i have for reading through the file
2) The script for connecting to the database.


1) Script for reading through the file
on mouseUp
-- check the global connection ID to make sure we have a database connection
global gConnectionID,tTableName , tData, tSQL,sCurrentRecord,sRecordCount,sCursorID,tColumnNames,sRecordAsArray,tColumnName
local t_Value, t_Total_Value, t_KG, t_Total_KG, t_Litre, t_Total_Litre, t_Max_Limit, t_Interval_Count
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if
-- construct the SQL (this selects all the data from the specified table)
put "A01CustomerDeliveryData" into tTableName -- set this to the name of a table in your database
-- put "SELECT * FROM " & tTableName into tSQL
put "SELECT a01customerdeliverydata.DelCustCode FROM a01customerdeliverydata LIMIT 1, 10" into tSQL
put revdb_query(gConnectionID, tSQL) into sCursorID
-- put revdb_recordcount(sCursorID) into sRecordCount
put 9999 into sRecordCount
put 5 into t_Max_Limit
put 1 + t_Max_Limit into t_Interval_count
put 1 into sCurrentRecord
Put 0 into t_Value
Put 0 into t_Litre
Put 0 into t_KG
Put 0 into t_Total_Value
Put 0 into t_Total_Litre
Put 0 into t_Total_KG
Put 0 into field "f_Value"
Put 0 into field "f_Litre"
Put 0 into field "f_KG"
put revdb_columnnames(sCursorID) into tColumnNames
repeat until sCurrentRecord > sRecordCount
Put sCurrentRecord into field "f_Counter"
if t_Interval_Count > t_Max_Limit then
get revdb_closecursor(sCursorID)
put "SELECT a01customerdeliverydata.DelQtyinValue, a01customerdeliverydata.DelQtyinLitres, a01customerdeliverydata.DelQtyinKg FROM a01customerdeliverydata LIMIT " & sCurrentRecord & "," & sCurrentRecord + t_Max_Limit - 1 into tSQL
put revdb_query(gConnectionID, tSQL) into sCursorID
put 1 into t_Interval_Count
end if
put revdb_columnbyname(sCursorID,"DelQtyInValue") into t_Value
put revdb_columnbyname(sCursorID,"DelQtyInLitres") into t_Litre
put revdb_columnbyname(sCursorID,"DelQtyInKG") into t_KG
put t_Value + t_Total_Value into t_Total_Value
put t_Litre + t_Total_Litre into t_Total_Litre
put t_KG+ t_Total_KG into t_Total_KG
add 1 to sCurrentRecord
add 1 to t_Interval_Count
get revdb_movenext(sCursorID)
end repeat
Put t_Total_Value into field "f_Value"
Put t_Total_Litre into field "f_Litre"
Put t_Total_KG into field "f_KG"
get revdb_closecursor(sCursorID)
end mouseUp



2) Connect Script
on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID, tDatabaseAddress, tDatabaseAddress,tDatabaseUser, tDatabasePassword, tResult, tDatabaseName

-- set up the connection parameters - edit these to suit your database
put "localhost" into tDatabaseAddress
put "plascondropanalysisdb" into tDatabaseName
put "root" into tDatabaseUser
put "" 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 "Connected to the database." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
end if
end mouseUp

[Mark]

Hi,

It looks like you want to download 2000 records, make some modifications in LiveCode and then upload those 2000 records to the server again? That's not what MySQL and all those other database engines were made for. The great thing about those database engines is that you can send a few commands, which will then be executed remotely, on the server, without having to download and upload all those data. You're frustrated, because you're trying to do something you shouldn't do in the first place.

What exactly do you want to do and why? Perhaps we can find a better way.

Kind regards,

Mark

[MaxV]

I agree with Mark, your code is strange.
Why don't you opearate directly on MySQL? You could using a query like:

Code: Select all

put "SELECT SUM(DelQtyinValue), SUM(DelQtyinLitres), SUM(DelQtyinKg) FROM a01customerdeliverydata;" into tSQL
 put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords

so tRecords will contains all calculus results. I suggest you to study all MySQL functions and apply directly in the query, obtainting just the result you need as output. Morever, if you indicate the table using FROM, you don't need to indicate it in the SELECT statement.

[tguimond]

If you are seeing the livecode application "hanging", this can happen because livecode sends the redraw messages for the graphical interface through the message pipe. It is possible the application is still running but in such a tight loop that the screen isn't refreshing and so the operating system thinks that it is hung. Try putting some wait with messages in the code while you process the cursor.

wait 0 milliseconds with messages

Tim