SQL problem when executed in an Ajax Callback

[ids-dave]

Hello

I have a SQL Update statement that works when I run it from SQL Manager, but it won't execute when used in an Ajax Callback. I need toconcatenate a date and time field together and use the result for downstream calculations.


the update statement is.....

UPDATE mytable SET [mydateandtime] = CAST(CONVERT(NVARCHAR(19), CONVERT(VARCHAR(12), CAST([mydateonly] AS date)) + ' ' + CONVERT(VARCHAR(12), CAST([mytimeonly] AS time)), 101) AS datetime)

I put a different Update statement in the Callback function to make sure there wasn't some other problem. I also removed all other fields and the 'where' clause to simplify things while trying to resolve this. The function works correctly when updating other fields.

I've also tried a wide range of things like using ['] vs ["] for the space between the date and time, upper and lower case combinations for the field types, removed the spaces after the commas and stuff like that. Any of these tweaks works in SQL Mgr but not when the same statement is used in a Callback

SQL field definitions are

mydateandtime datetime 2011-08-15 11:22:33.000 example of expected results
mydateonly date 2011-08-15 sample input
mytimeonly time(0) 11:22:33 sample input

I also tried different combinations of time set as time(0) and time(7) and the datetime as datetime2(7)

I am stumped as to why it works in SQL Mgr, but not from the Ajax Callback. I appreciate and insight or guidance

Thanks! Dave

[Davidk]

Re: SQL problem when executed in an Ajax Callback

Could you post the full code in your ajax callback? If not the full code then at least "exactly" the update statement. What's the error?

Also, I would next try to simplify the update statement. I would try to do the Cast and Convert date/time work in xbasic to see if that works.

[ids-dave]

Re: SQL problem when executed in an Ajax Callback

Hi David, thank you for looking at this, here's the code


function clockout as c (e as p)
'debug(1)
DIM cn as SQL::Connection
dim flagResult as l
flagResult = cn.open("::Name::starhr_master")
if flagResult = .f. then
clockout = "alert('Error message # mico900001a - could not connect to the database - contact support');"
end
end if
cn.PortableSQLEnabled = .t.
DIM args as sql::arguments
if a5_eval_valid_expression("=Now()",local_variables()) then
args.add("newsystemdateout",convert_type(a5_eval_expression("=Now()",local_variables()),"T"))
end If
if a5_eval_valid_expression("=Date()",local_variables()) then
args.add("newpunchoutdate",convert_type(a5_eval_expression("=Date()",local_variables()),"D"))
end If
if a5_eval_valid_expression("=Time()",local_variables()) then
args.add("newpunchouttime",convert_type(a5_eval_expression("=Time()",local_variables()),"Y"))
end If
if a5_eval_valid_expression("=Date()",local_variables()) then
args.add("newdateout",convert_type(a5_eval_expression("=Date()",local_variables()),"D"))
end If
if a5_eval_valid_expression("=Time()",local_variables()) then
args.add("newtimeout",convert_type(a5_eval_expression("=Time()",local_variables()),"Y"))
end If

args.add("New_MachineID","website")
args.add("newreqstatus","(pending)")
args.add("m_tcid",e._currentRowDataNew.TC_ID,"N")
args.add("newhoursworked","12.34")

dim sqlUpdateStatement as c
sqlUpdateStatement = <<%sql%
UPDATE Timecards SET [System Date Out] = CAST(CONVERT(NVARCHAR(19),CONVERT(VARCHAR(12),CAST([Punch In Date] AS date))+' '+CONVERT(VARCHAR(12),CAST([Punch In Time] AS time)),101) AS datetime) WHERE TC_ID = :m_tcid
%sql%
dim flag as l
flag = cn.Execute(sqlUpdateStatement,args)
clockout = "{grid.Object}.refresh()"
end function

I am not getting any error messages. I also turned on "Show Update Commands" from the update Properties and the SQL code is not being shown when running the grid from working preview. It behaves like it being completely ignored (at one point, I put in an alert message near just before the "end function" just to make sure the function was being executed, which it is)

I have used other variations of the UPDATE Timecards statement against other fields and it works successfully

Please let me know if you need anything else, thanks much, Dave

[Davidk]

Re: SQL problem when executed in an Ajax Callback

Hey Dave,

I can't see how your code would work as it is... but my SQL isn't that great so I just don't understand some things.

I looks like you have a lot more arguments being set than used.
There's so much going in the update statement.

I simplified stuff a lot and this is working with MySQL (not sure if that's what you're using)
Fields are defined as DateTime, Date, and Time

I put in error checking and display on the execute statement so that may help if you're still having problems.

You can do some error checking in the args. set but maybe using IsDate and IsTime instead of what you're doing now.

Here's another issue that I just read about. Depending on where your server is, you could be getting wrong dates and times. Now() etc. use the Server date and time, not the client. If your server is close by then it's not an issue for you, unless your user is in Hong Kong and then the values may be wrong for what you're doing.
I just read about this and haven't looked into it further... just thought I'd mention it.

Change your table name back to yours... field names, connection etc. whatever else I've changed for me to run it here, and it should run for you.

function clockout as c (e as p)
debug(1)
DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("::Name::yourdatabase","youraccount","yourpassword")

if flagResult = .f. then 
	clockout = "alert('Error message # mico900001a - could not connect to the database - contact support');"
	end 
end if

cn.PortableSQLEnabled = .t.

DIM args as sql::arguments
'if a5_eval_valid_expression("=Now()",local_variables()) then 
'	args.add("newsystemdateout",convert_type(a5_eval_expression("=Now()",local_variables()),"T"))
'end If

'if a5_eval_valid_expression("=Date()",local_variables()) then 
'	args.add("newpunchoutdate",convert_type(a5_eval_expression("=Date()",local_variables()),"D"))
'end If

'if a5_eval_valid_expression("=Time()",local_variables()) then 
'	args.add("newpunchouttime",convert_type(a5_eval_expression("=Time()",local_variables()),"Y"))
'end If

'if a5_eval_valid_expression("=Date()",local_variables()) then 
'	args.add("newdateout",convert_type(a5_eval_expression("=Date()",local_variables()),"D"))
'end If

'if a5_eval_valid_expression("=Time()",local_variables()) then 
'	args.add("newtimeout",convert_type(a5_eval_expression("=Time()",local_variables()),"Y"))
'end If

Dim newdatetime as T
args.add("newsystemdateout",Now())
args.add("newpunchoutdate",Date())
args.add("newpunchouttime",Time())


'args.add("New_MachineID","website")
'args.add("newreqstatus","(pending)")

args.add("ProdId",e._currentRowDataNew.ProdId)

'args.add("newhoursworked","12.34")

dim sqlUpdateStatement as c
sqlUpdateStatement = "UPDATE tblProducts SET mydateandtime = :newsystemdateout, mydateonly = :newpunchoutdate, mytimeonly = :newpunchouttime WHERE ProdId = :ProdId"
'sqlUpdateStatement = <<%sql%
'UPDATE Timecards SET [System Date Out] = CAST(CONVERT(NVARCHAR(19),CONVERT(VARCHAR(12),CAST([Punch In Date] AS date))+' '+CONVERT(VARCHAR(12),CAST([Punch In Time] AS time)),101) AS datetime) W'HERE TC_ID = :m_tcid
'%sql%
dim flag as l
flag = cn.Execute(sqlUpdateStatement,args)
if flag = .f. then 
	'there was an error - close the connection and exit
	cn.Close()
	dim msg as c 
	msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	clockout = jscmd
	exit function 
end if 
	
clockout = "{grid.Object}.refresh()"

end function

[kingcarol]

Re: SQL problem when executed in an Ajax Callback

To get an error message about why the execute of the query did not work, I think you need to use your flag variable to capture the error after your
flag = cn.Execute(sqlUpdateStatement,args)

Here's an example that Selwyn gave me once:
if flag = .f. then
'there was an error - give a message, close the connection, and exit
dim msg as c
msg = "Could not execute query. Please contact your program administrator for help. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
cn.Close()
clockout = jscmd
exit function
end if

[ids-dave]

Re: SQL problem when executed in an Ajax Callback

Hey David and Carol

Thank you for your feedback. I tried several other variations of using the SQL CAST function from within Alpha and finally gave up. Performing the "Cast" function as an expression in my SQL view works perfectly in my app so all if well in that regard. This is a large app and while I use sql views extensively to reduce overhead and create table joins, I was planning on doing all data manipulations and logic within Alpha. I want to keep everything we build consistent so it is easier to maintain. But it is what it is....

As for the other items pointed out
Error Messages - I used a several different methods of displaying the error, including Selwyn's and none of them revealed what was happening. Right or wrong, I excluded them when sharing the code on the message board because it was really really sloppy

Multiple Time Zones - this is gonna be fun as my customer base grows. Right now the server is a godaddy VPS and resides in Arizona. All my customers are in the Eastern time zone and I have the server set to EDT. I know I will be adding code to adjust the date/time on all transactions as soon as I get a customer in a different zone

Thanks again and have a great Labor Day weekend
Dave