Here's an interesting one:
Windows 10, SQL Server 2017 Express.
Regional Format: English (United States)
- WW6.21 App works flawlessly, creating and consuming sessions.
Change Regional Format to English (Australia) :
- Connectivity Error 8114: Error converting data type varchar to datetime.
The issue is in wwSession :: Save() where there are 2 TTOC() constructs:
THIS.oSql.Execute("Execute sp_ww_IsSession ?loData.SessionId,'" +; TTOC(DATETIME() - THIS.nSessionTimeout) + "',?@llIsSession") ... ... THIS.oSQL.Execute(; "UPDATE wwSession SET " + ; "SessionId=?loData.SessionId," + ; "UserId=?loData.UserId," + ; "FirstOn=?loData.FirstOn," + ; "LastOn=?loData.LastOn," +; "Vars=?loData.Vars,"+; "Browser=?loData.Browser,"+; "IP=?loData.IP,"+; "Hits=?loData.Hits " + ; " WHERE SessionId=?loData.SessionId AND LastOn > '" + TTOC(DATETIME() - THIS.nSessionTimeOut) + "'")
I'm thinking it may already be remedied using TimeToCStrict() as used in other wwSession functions, but if not, can I recommend a parameter instead... not just so the query is cached for efficiency, but also because parameters work the same against different SQL date/datetime field types and other databases like Oracle:
llIsSession = 0 && Bit result ltCompare=DATETIME() - THIS.nSessionTimeout THIS.oSql.Execute("Execute sp_ww_IsSession ?loData.SessionId,?ltCompare,?@llIsSession") ... ... THIS.oSQL.Execute(; "UPDATE wwSession SET " + ; "SessionId=?loData.SessionId," + ; "UserId=?loData.UserId," + ; "FirstOn=?loData.FirstOn," + ; "LastOn=?loData.LastOn," +; "Vars=?loData.Vars,"+; "Browser=?loData.Browser,"+; "IP=?loData.IP,"+; "Hits=?loData.Hits " + ; " WHERE SessionId=?loData.SessionId AND LastOn > ?ltCompare")
Making these changes appears to eliminate the issue.
TimeToCFixed()
got moved into this recently. I think this hasn't released yet though.
Funny that this has come up twice now in the last 2 weeks and never in the last 20 years 😃
+++ Rick ---
Rick, re timing: having briefly reviewed how creatively MSSQL internals can behave, I suspect it may well represent a recent algorithm enhancement or version; Regional settings have been an absolute requirement for our WW apps since SET SYSFORMAT was a boy and this error wasn't affecting my own PC with SSCE2014.
Did you try running with using TimeToCStrict()
for these commands? The strict syntax should be properly translated by FoxPro.
The big issue with this SQL session is that I don't use it myself. Most other code I end up touching one way or the other, but that particular piece is very old and never gets touched. Recently Richard Kaye found a few small issues that are all addressed in the next update, including the TimeToCStrict()
conversions.
Thanks for the feedback,
+++ Rick ---