Update MYSQL field on Button press in a UX Component

[paulyp]

Hi Guys

I am trying to set the data in a MYSQL field to NULL when a button is pressed in an UX Component. I have a button that when pressed fires an Ajax callback that fires an Xbasic function called deleteImage (together with an Actionscript that actually deletes the image). I am trying to base the update on the Primary Key and have tryed everything although I still trying to get my head around SQL and struggling a little. I am trying to set e._state.s_con_cont_id to the primary key in the onDialogExecute using

dim s_con_cont_id as C
e._state.s_con_cont_id = {id_contractor}

then in the Xbasic deleteImage function I have the following code

function deleteImage as c (e as p)
dim namedConnection as C
dim cn as sql::connection
dim args as sql::arguments
dim sqlqy as c
dim con_cont_id as N
con_cont_id = convert_type(e._state.s_con_cont_id, "N")
cn.open("::Name::CCS")
sqlqy = "UPDATE a1contstaf SET a1imagefilename = NULL WHERE a1idconsta = :cont_id)"
args.add("cont_id",con_cont_id)
cn.Execute(sqlqy,args)
cn.close()
end function

a1contstaf is the table, a1imagefilename is the file I am trying to make NULL and a1idconsta is the primary key.

I know its wrong as it doesn't work and would really appreciate any guidance.

Cheers
Paul

[frankbicknell]

Re: Update MYSQL field on Button press in a UX Component

It looks like it should work to me. Have you tried to run the update query from a mySql command line or dbm program?

You can try something like this to see if there is an error in your query or you can look at cn.CallResult.text in the debugger.

ok = cn.Execute(sqlqy,args)
IF .not. OK
	jsToReturn = "\"alert('"+ js_escape(cn.CallResult.text) +"');\""
	deleteImage = jsToReturn
END IF

[Nate Mitchell]

Re: Update MYSQL field on Button press in a UX Component

Paul, as a follow on to this, you can use the following function to see what the SQL statement looks like after the arguments have been passed in.
Place this after your args.add line and look at your statement in debug mode.
As Frank mentioned, copy the fully qualified statement into a database tool and run manually to see if it executes.
This will also show you if your arguments are getting populated correctly.

sql=replace_arguments_in_string(spSQL,args,3)

[TheSmitchell]

Re: Update MYSQL field on Button press in a UX Component

Hi Guys

I am trying to set the data in a MYSQL field to NULL when a button is pressed in an UX Component. I have a button that when pressed fires an Ajax callback that fires an Xbasic function called deleteImage (together with an Actionscript that actually deletes the image). I am trying to base the update on the Primary Key and have tryed everything although I still trying to get my head around SQL and struggling a little. I am trying to set e._state.s_con_cont_id to the primary key in the onDialogExecute using

dim s_con_cont_id as C
e._state.s_con_cont_id = {id_contractor}

then in the Xbasic deleteImage function I have the following code

function deleteImage as c (e as p)
dim namedConnection as C
dim cn as sql::connection
dim args as sql::arguments
dim sqlqy as c
dim con_cont_id as N
con_cont_id = convert_type(e._state.s_con_cont_id, "N")
cn.open("::Name::CCS")
sqlqy = "UPDATE a1contstaf SET a1imagefilename = NULL WHERE a1idconsta = :cont_id)"
args.add("cont_id",con_cont_id)
cn.Execute(sqlqy,args)
cn.close()
end function

a1contstaf is the table, a1imagefilename is the file I am trying to make NULL and a1idconsta is the primary key.

I know its wrong as it doesn't work and would really appreciate any guidance.

Cheers
Paul

You have an error in you SQL statement. Delete the closing parentheses (highlighted in red below).

function deleteImage as c (e as p)
    dim namedConnection as C
    dim cn as sql::connection
    dim args as sql::arguments
    dim sqlqy as c
    dim con_cont_id as N
    con_cont_id = convert_type(e._state.s_con_cont_id, "N")
    cn.open("::Name::CCS")
    [B]sqlqy = "UPDATE a1contstaf SET a1imagefilename = NULL WHERE a1idconsta = :cont_id[COLOR="#FF0000"])[/COLOR]"[/B]
    args.add("cont_id",con_cont_id)
    cn.Execute(sqlqy,args)
    cn.close()
end function

Frank's suggestion to add a check to the return value of cn.execute is a good one, too. If you tried his suggestion, then I can probably assume that you figured out what the problem is by now.

[paulyp]

Re: Update MYSQL field on Button press in a UX Component

Hi Sarah,
thanks for that. Funny how a little symbol can make such a difference !!! If I run it, then it only makes a difference when I submit the record and not after the script is actually run which is what I am looking for (would of thought the record would update to the database on execution). I am renaming an image to a more specific name in case of data loss in the future - I want to easily match user with records. I am using a function in the 'afterDialogValidate' based on one of Selwyn's videos and unless the database record is NULL then the filename reverts back to its GUID allocated image reference.

No image or camera upload image MYSQL data.jpg Image deleted and new image uplaoded MYSQL data.jpg Image Upload Screen.jpg
The code I am using to rename the images is;

if rtc.flagRecordWasSaved
dim sql as c
sql = "select a1idconsta, a1contname, a1firstnam, a1lastname, a1imagefilename from a1contstaf where a1idconsta = :pk"
dim args as sql::Arguments
dim cn as sql::Connection
flag = cn.open("::Name::CCS")
if flag = .f. then
e.javascript = e.javascript + "alert('" + js_escape(cn.CallResult.text) + "');"
end
end if
args.add("pk", convert_type(rtc.primaryTablePrimaryKey,"N"))
dim flag as l
flag = cn.Execute(sql,args)
if flag = .f. then
e.javascript = e.javascript + "alert('" + js_escape(cn.CallResult.text) + "');"
cn.Close()
end
end if
dim rs as sql::ResultSet
rs = cn.ResultSet
dim imageName as c
dim filename as c
imageName = rs.Data("a1contname")+"_"+rs.Data("a1firstnam")+"_"+rs.Data("a1lastname")
filename = rs.Data("a1imagefilename")
cn.FreeResult()
dim filename2 as c
filename2 = file.filename_parse(filename,"dp") +imageName+file.filename_parse(filename,"e")
if file.exists(filename) then
file.rename(filename,filename2)
end if
dim sql2 as c
sql2="update a1contstaf set a1imagefilename = :fn1 where a1idconsta = :pk"
args.Add("fn1",filename2)
flag = cn.Execute(sql2,args)
if flag = .f. then
e.javascript = e.javascript + "alert('" + js_escape(cn.ResultSet.text) + "');"
cn.Close()
end
end if
cn.Close()
end if

Cheers
Paul

[TheSmitchell]

Re: Update MYSQL field on Button press in a UX Component

If I run it, then it only makes a difference when I submit the record and not after the script is actually run which is what I am looking for (would of thought the record would update to the database on execution).

Ignore all the parts about submit. Does your SQL database update when you run it? Meaning, when you run the script and go look at the tables in your database using some other tool like MySQL Workbench, do the records change how you expect? And does the file get renamed on the filesystem like you expect?