Convert ux date control value to sql date format

[frankbicknell]

I am wondering if I am missing something because I can't believe that it is this cumbersome to convert a ux date control into a format that can be used in a sql update method in an ajax callback.

What I have had to do in the ajaxcallback.

get the value of the date control.
convert it to a date with the ctod() function.
convert it to a slq date with the sql_date() function, which returns a character value like {2015-06-10}.
lastly I have to remove the curly brackets.
then it can be used in the sql update method.

Does that seem reasonable or am i just getting lazy?

[GGandhi]

Re: Convert ux date control value to sql date format

why do you need all of those, when the (ux) dialog is bound to that table, alpha is supposed to do that for you, is that not correct?
even if you were to update another unrelated table like log then still alpha can update, without those translation, am i not correct?

[frankbicknell]

Re: Convert ux date control value to sql date format

This ux is just a little app that I have created to set the report options for my invoices. I do not have it bound to a table. I am just using some controls and setting some report options, saving it to disk and viewing it, etc and I need to update a couple of fields in the table. I made this ux way back when they were new and I did not know much about them.

As I am typing this I am thinking that maybe I should just bind it to the table after all.
Thanks for pointing out the obvious, I will give it a try.

[Pat Bremkamp]

Re: Convert ux date control value to sql date format

Here's what I do

if e.dataSubmitted.CORRECTION_DATE != "" then
vTempD = convert_type(e.dataSubmitted.CORRECTION_DATE,"D")
vTempC = dtoc(vTempD,"12-")
vSQLtxt = vSQLtxt + "CORRECTION_DATE = '"+vTempC+"', "
end if

Pat

[frankbicknell]

Re: Convert ux date control value to sql date format

Thanks for the idea Pat.
This simplified it for me a little but I still think I am going to bind the ux to a table.
cSqlDate = substr(sql_date(ctod(e._currentRowDataNew.IDATE)),2,10)

[GGandhi]

Re: Convert ux date control value to sql date format

i am still wondering. if you just assign the argument to the e.dataSubmitted.IDATE without converting
does it not update the sql table properly?
(or you need this conversion for your filter for the report?)

[frankbicknell]

Re: Convert ux date control value to sql date format

Without the conversion I get a mySql error stating that the date is in the wrong format. Surprised me too. I am using a ux control set up as a date.

[GGandhi]

Re: Convert ux date control value to sql date format

i did a test using mysql just like you have and i had no problem.
the table and dialog are not bound together, the i used insert function ( will be no difference for update ) using ajax callback.
did you use arguments?

[GGandhi]

Re: Convert ux date control value to sql date format

if result then
		sqlInsert = "INSERT INTO frank1 (name, date) values (:newName, :newDate)"
		args.set("newName", name)
		args.set("newDate", date1)
		result = cn.Execute(sqlInsert,args)
		if result then
			xb = "alert('success')";
		end if

http://screencast.com/t/cvmzMvMj0Mx

code and result

[frankbicknell]

Re: Convert ux date control value to sql date format

If I use the value without converting I get the error from mysql.

this is the update statement.

				myargs.set("maIdate",cSqlDate)
					cErrorMsgNo = "101"
					
					
					OK = cn.open("::Name::connection_string")
					if OK
						sqlStatement = <<%sql%
						update table set idate = :maIdate, hard_copy = :maInvFileName where id= :maInvID %sql%
						OK = cn.execute(sqlStatement,myargs)
					end if

[GGandhi]

Re: Convert ux date control value to sql date format

dim name as c
	dim date1 as d
	name = e.dataSubmitted.name
	date1 = e.dataSubmitted.date
	
	dim cn as sql::Connection
	dim args as sql::Arguments
	dim result as l
	dim sqlInsert as c
	
	result = cn.Open("::Name::local_mySql")
	if result then
		sqlInsert = "INSERT INTO frank1 (name, date) values (:newName, :newDate)"
		args.set("newName", name)
		args.set("newDate", date1)
		result = cn.Execute(sqlInsert,args)
		if result then
			xb = "alert('success')";
		end if
		cn.close()
	end if

i did not convert to a sql date just as it came from the dialog.

[GGandhi]

Re: Convert ux date control value to sql date format

i just did an update works without any problem. do not convert the date just use it as it comes.

[frankbicknell]

Re: Convert ux date control value to sql date format

Thank you for your help Govindan I am going to have to figure out why this is not working for me. It did seem like a lot of work to have to convert a date in a ux for sql.

[peteconway]

Re: Convert ux date control value to sql date format

you know what the format is - just use a function and then submit it.

function timestampAdd as c (e as p)
debug(1)
dim ts as c
ts = time("yyyy-MM-dd 0h:0m:0s", now())
e._set.Person_AddedOn.value = ts
end function

Cheers Pete.

[GGandhi]

Re: Convert ux date control value to sql date format

Thank you for your help Govindan I am going to have to figure out why this is not working for me. It did seem like a lot of work to have to convert a date in a ux for sql.

what is the data type in MySQL table, and what is the data type in the ux and what is the data entered on data entry.
if they are all the same then there should be no problem.