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