is this sql the right way to do an update

[richard2007]

converting my app to SQL, i have lots of update scripts to switch from dbf to sql so i want to make sure before i go ahead and start the work that i am on the right track and have the most efficient way of doing this.

All updates are run in the afterupdate event of a dialog or grid.

this is the type of script i am changing

		'set progress values
		dim tbl as p 
		tbl = table.open("[PathAlias.ADB_Path]\progress.dbf")
		'Find the record that has to be edited
			query.filter = "caseref = " +quote(session.caseref)
			query.order = ""
			query.options = ""
			qry = tbl.query_create()
			nrecs = qry.records_get()
			if nrecs > 0 then 
			tbl.change_begin()
				if totmths1 > 35 then
					tbl.comp_res1 = "Completed"
				else
					tbl.comp_res1 = "Not Completed"
				end if
			end if
			tbl.change_end(.t.)
		qry.close()
		tbl.close()

this is what i have converted it to - this partuclar one needs an if, then, else but not all of them do

	dim cn as sql::Connection
	flagresult = cn.open("::Name::MMLocal")
	dim args as sql::Arguments
	args.set("caseref",session.caseref)
	args.set("COMP_RES1","Completed")
	args.set("COMP_RES1NC","Not Completed")
		if totmths1>35 then
			dim sql_update as c 
			sql_update = "UPDATE progress SET COMP_RES1 = :COMP_RES1 WHERE caseref = :caseref"
			
			dim flag as l 
			flag = cn.Execute(sql_update,args)
			cn.close()
		else
			dim sql_update as c 
			sql_update = "UPDATE progress SET COMP_RES1 = :COMP_RES1NC WHERE caseref = :caseref"			
			dim flag as l 
			flag = cn.Execute(sql_update,args)
			cn.close()		
		end if

do i need to keep opening and closing the connection if this is run from a grid/dialog?
is there a way i can improve this for ease or efficiency?

[glenschild]

Re: is this sql the right way to do an update

Hi Richard

All looks good. Personally I try and open the connection at the last possible moment and close as soon as done. Probably would not make any noticeable difference in a short script.

[GGandhi]

Re: is this sql the right way to do an update

hello
just a little suggestion:
you will need to check if the connection was successful or not at the connection level, and only proceed when connected.

dim cn as sql::Connection
flagresult = cn.open("::Name::MMLocal")

if flagresult = .t.
......rest of your code
else
your_function_name = "alert('error in connecting to database!');"
end if

that will avoid any connection problems and to trap that.

[GGandhi]

Re: is this sql the right way to do an update

args.set("COMP_RES1","Completed")
args.set("COMP_RES1NC","Not Completed")
if totmths1>35 then
dim sql_update as c
sql_update = "UPDATE progress SET COMP_RES1 = :COMP_RES1 WHERE caseref = :caseref"

dim flag as l
flag = cn.Execute(sql_update,args)
cn.close()
else
dim sql_update as c
sql_update = "UPDATE progress SET COMP_RES1 = :COMP_RES1NC WHERE caseref = :caseref"
dim flag as l
flag = cn.Execute(sql_update,args)
cn.close()
end if

also it may be possible to cut down some of the code
comp_res1 = if(totmths > 35, "Completed", "Not Completed")
then all you will need is one update statement.
i did not test this yet, i will test this later today.
let you know.

[richard2007]

Re: is this sql the right way to do an update

thanks for the help, this seems to work

args.add("COMP_RES1",iif(totmths1>35,"Completed","Not Completed"))

[lgrupido]

Re: is this sql the right way to do an update

Actually, since this code fires when the data is updated - the SQL way to do this would be in an update trigger on the table.

It will be a little faster, and it will fire even if you update the data from outside the grid.