Web Connection
Sessionid conversion error if region changed
Gravatar is a globally recognized avatar based on your email address. Sessionid conversion error if region changed
  RVBoy
  All
  Jul 15, 2020 @ 01:56am

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.

Gravatar is a globally recognized avatar based on your email address. re: Sessionid conversion error if region changed
  Rick Strahl
  RVBoy
  Jul 15, 2020 @ 05:33am

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 ---

Gravatar is a globally recognized avatar based on your email address. re: Sessionid conversion error if region changed
  RVBoy
  Rick Strahl
  Jul 15, 2020 @ 01:01pm

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.

Gravatar is a globally recognized avatar based on your email address. re: Sessionid conversion error if region changed
  Rick Strahl
  RVBoy
  Jul 15, 2020 @ 03:29pm

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 ---

Gravatar is a globally recognized avatar based on your email address. re: Sessionid conversion error if region changed
  RVBoy
  Rick Strahl
  Jul 15, 2020 @ 04:12pm

Rick, agreed: over 15 years running the same WW code with NZ or UK region definitely enabled, and now suddenly an issue. It has to be at the MSSQL end.

I fixed it using the parameterized query posted earlier that doesn't need timetoCStrict() and lets MSSQL cache the query for efficiency.

© 1996-2020