Hi all
I wanted to loop through a DBF table and insert the records into a MySQL table using a script and not with a form open. Managed to do it using the Genie but I first had to Dim the variables as global to map the fields to the variables in the Genie. Then I changed the variables as shared in script. I just want to know why one cannot see shared variables in the genie while mapping it to fields. Also I am closing the connection after each insert. Can one leave the connection open throughout the loop and then close it?
My Code:
Any pointer would be appreciated.
I wanted to loop through a DBF table and insert the records into a MySQL table using a script and not with a form open. Managed to do it using the Genie but I first had to Dim the variables as global to map the fields to the variables in the Genie. Then I changed the variables as shared in script. I just want to know why one cannot see shared variables in the genie while mapping it to fields. Also I am closing the connection after each insert. Can one leave the connection open throughout the loop and then close it?
My Code:
Code:
dim tbl as p dim shared vDriver as c dim shared vFirstSTartDateTime as t dim shared vStartDate_Grp as d dim shared vCountStartDateTime as n dim shared vLastEndDateTime as t dim shared vTotalTripTime as n dim shared vTotalIdleTime as n dim shared vTotalDrive_Time as n dim shared vTotalDistance as n dim shared vMax_Max_Speed as n dim shared vMaxRpm as n tbl = table.open("satlog_summary") 'debug(1) tbl.fetch_first() while .not. tbl.fetch_eof() 'do something here vDriver = tbl.drivername vFirstSTartDateTime = tbl.first_start_date_time vStartDate_Grp = tbl.first_start_date_time vCountStartDateTime = tbl.count_start_date_time vLastEndDateTime = tbl.last_end_date_time vTotalTripTime = tbl.total_trip_time vTotalIdleTime = tbl.total_idle_time vTotalDrive_Time = tbl.total_drive_time vTotalDistance = tbl.total_total_distance vMax_Max_Speed = tbl.maximum_max_speed vMaxRpm = tbl.maximum_max_rpm '====================================================== 'Insert a new record into a remote SQL database. 'DIM a connection variable DIM cn as SQL::Connection dim flagResult as l flagResult = cn.open("::Name::Tankers_Local") if flagResult = .f. then ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text) end end if 'Specify that we are using Portable SQL syntax cn.PortableSQLEnabled = .t. 'Dim a SQL arguments object, create arguments and set their values DIM args as sql::arguments if a5_eval_valid_expression("=Var->vDriver",local_variables()) then args.add("Drivername",convert_type(a5_eval_expression("=Var->vDriver",local_variables()),"C")) end If if a5_eval_valid_expression("=Var->vStartDate_Grp",local_variables()) then args.add("Start_Date_Grp",convert_type(a5_eval_expression("=Var->vStartDate_Grp",local_variables()),"D")) end If if a5_eval_valid_expression("=Var->vFirstSTartDateTime",local_variables()) then args.add("First_Start_Date_Time",convert_type(a5_eval_expression("=Var->vFirstSTartDateTime",local_variables()),"T")) end If if a5_eval_valid_expression("=Var->vCountStartDateTime",local_variables()) then args.add("Count_Start_Date_Time",convert_type(a5_eval_expression("=Var->vCountStartDateTime",local_variables()),"N")) end If if a5_eval_valid_expression("=Var->vLastEndDateTime",local_variables()) then args.add("Last_End_Date_Time",convert_type(a5_eval_expression("=Var->vLastEndDateTime",local_variables()),"T")) end If if a5_eval_valid_expression("=Var->vTotalTripTime",local_variables()) then args.add("Total_Trip_Time",convert_type(a5_eval_expression("=Var->vTotalTripTime",local_variables()),"N")) end If if a5_eval_valid_expression("=Var->vTotalIdleTime",local_variables()) then args.add("Total_Idle_Time",convert_type(a5_eval_expression("=Var->vTotalIdleTime",local_variables()),"N")) end If if a5_eval_valid_expression("=Var->vTotalDrive_Time",local_variables()) then args.add("Total_Drive_Time",convert_type(a5_eval_expression("=Var->vTotalDrive_Time",local_variables()),"N")) end If if a5_eval_valid_expression("=Var->vTotalDistance",local_variables()) then args.add("Total_Total_Distance",convert_type(a5_eval_expression("=Var->vTotalDistance",local_variables()),"N")) end If if a5_eval_valid_expression("=Var->vMax_Max_Speed",local_variables()) then args.add("Maximum_Max_Speed",convert_type(a5_eval_expression("=Var->vMax_Max_Speed",local_variables()),"N")) end If if a5_eval_valid_expression("=Var->vMaxRpm",local_variables()) then args.add("Maximum_Max_Rpm",convert_type(a5_eval_expression("=Var->vMaxRpm",local_variables()),"N")) end If dim sqlInsertStatement as c sqlInsertStatement = <<%txt% INSERT INTO satlog_summary (Drivername, Start_Date_Grp, First_Start_Date_Time, Count_Start_Date_Time, Last_End_Date_Time, Total_Trip_Time, Total_Idle_Time, Total_Drive_Time, Total_Total_Distance, Maximum_Max_Speed, Maximum_Max_Rpm) VALUES (:Drivername, :Start_Date_Grp, :First_Start_Date_Time, :Count_Start_Date_Time, :Last_End_Date_Time, :Total_Trip_Time, :Total_Idle_Time, :Total_Drive_Time, :Total_Total_Distance, :Maximum_Max_Speed, :Maximum_Max_Rpm) %txt% dim flag as l flag = cn.Execute(sqlInsertStatement,args) if flag = .f. then 'ui_msg_box("Error","Record was not inserted. Error reported was: " + crlf(2) + cn.CallResult.text,UI_STOP_SYMBOL) else if cn.AffectedRows() = 1 then 'USING THIS PART TO INCREMENT A RECORD COUNTER 'ui_msg_box("Notice","Record was created.",UI_INFORMATION_SYMBOL) else 'USING THIS TO UPDATE A LOG FILE 'ui_msg_box("Error","Record was not inserted." ,UI_STOP_SYMBOL) end if end if 'Now, close the connection cn.close() '====================================================== tbl.fetch_next() end while
Comment