cancel
Showing results for 
Search instead for 
Did you mean: 

"String was not recognized as a valid DateTime" reading a DATETIMEOFFSET

Former Member
6,404

When we first implemented SQL Anywhere support in our product, we used to get System.FormatException errors when we read a DATETIMEOFFSET from our database tables. As part of the work around to that problem, we added the following lines to our database schema update script at the very end:

SET OPTION date_order          = 'mdy'
go
SET OPTION PUBLIC.preserve_source_format =
go
SET OPTION "PUBLIC"."date_order"='mdy'
go
SET OPTION "PUBLIC"."preserve_source_format"='On'
go
SET OPTION PUBLIC.timestamp_format =
go
SET OPTION PUBLIC.timestamp_with_time_zone_format = 'YYYY-MM-DD HH:NN:SS.SSS+HH:NN'
go


This has been working fine for us. Until last week, when we upgrade to EBF 3967. Now, we get the following stack trace when we try to read a DATETIMEOFFSET from one of our tables:


> 2014-09-15 09:14:08,765 [PubSubBufferMemory::RunSecondaryOutput] ERROR PubSubBufferMemory::PubSubBufferMemory - PubSubBufferMemory::RunSecondaryOutput failed while transitioning from Running to Running: System.Exception: Secondary buffer has failed. Persistent node connection rejected.
> ---> LPRCore.CarSystem.DataAccessException: A database error occurred while trying to retrieve the versions to transmit in GetMessage. 
> ---> System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.
> ---> System.FormatException: String was not recognized as a valid DateTime.
>   at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader)
>   at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteDbDataReader(CommandBehavior behavior)
>   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
>   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
>   --- End of inner exception stack trace ---
>   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
>   at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
>   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
>   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<t>.GetEnumerator()
>   at LPRCore.CarSystem.UserDataAccessor.<queryuserversions>d__1a.MoveNext() in c:\\ElsagTFS\\EOC4\\CarSystem\\LPRCore Plugin CarSystem\\UserDataAccessor.cs:line 605
>   at LPRCore.CarSystem.MultilistEnumerator`2.<getenumerator>d__5.MoveNext() in c:\\ElsagTFS\\EOC4\\CarSystem\\LPRCore Plugin CarSystem\\MultilistEnumerator.cs:line 24
>   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
>   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
>   at LPRCore.Devices.PubSubBufferSA.<getmessages>d__7c.MoveNext() in c:\\ElsagTFS\\EOC4\\CarSystem\\LPRCore Plugin CarSystem\\PubSubBufferSA.cs:line 1106
>   --- End of inner exception stack trace ---
>   at LPRCore.Devices.PubSubBufferSA.<getmessages>d__7c.MoveNext() in c:\\ElsagTFS\\EOC4\\CarSystem\\LPRCore Plugin CarSystem\\PubSubBufferSA.cs:line 1109
>   at LPRCore.Devices.PubSubBufferMemory.RunSecondaryOutput(Object subscription) in c:\\ElsagTFS\\EOC4\\LPRCore\\LPRCore Plugin Com\\PubSubBufferMemory.cs:line 214
>   --- End of inner exception stack trace ---

The code in question is an Entity Framework query that retrieves one row from one table in the database. Here's the CREATE TABLE command for the table in question:

CREATE TABLE "DBA"."Sites" (
    "SiteId" UNIQUEIDENTIFIER NOT NULL,
    "SiteName" VARCHAR(80) NOT NULL,
    "SiteTypeId" INTEGER NOT NULL,
    "DomainId" INTEGER NOT NULL,
    "Latitude" DOUBLE NULL,
    "Longitude" DOUBLE NULL,
    "ParentId" UNIQUEIDENTIFIER NULL,
    "CreatedDate" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    "ModifyDate" TIMESTAMP WITH TIME ZONE NULL,
    "Subscriber" UNIQUEIDENTIFIER NULL,
    "FromToVersion" BIGINT NOT NULL,
    "FromVersion" BIGINT NULL,
    "InstanceId" UNIQUEIDENTIFIER NOT NULL,
    "UpdateLogic" INTEGER NOT NULL DEFAULT 0,
    "Active" BIT NOT NULL DEFAULT 1,
    "Visible" BIT NOT NULL DEFAULT 1,
    "TargetVersion" VARCHAR(100) NULL,
    "LPRType" SMALLINT NULL,
    "Enabled" BIT NULL,
    "CameraID" CHAR(5) NULL,
    "Address" VARCHAR(50) NULL,
    "Facing" SMALLINT NULL,
    "ReadTimeKind" SMALLINT NULL,
    "CurrentVersion" VARCHAR(100) NULL,
    "WatchMode" INTEGER NULL,
    "WatchInfo" VARCHAR(32767 CHAR) NULL,
    "ImageWidth" INTEGER NULL,
    "ImageHeight" INTEGER NULL,
    PRIMARY KEY ( "SiteId" ASC )
) IN "system";


The EF query:

 return context.Sites.Where( site => site.FromVersion == null ).Where( site => site.ID == xxxxx);


We currently do not have the ability to download more recent updates; 3967 is the last EBF that we downloaded before the switch to SAP's download site. I'm unclear as to why we don't have the necessary privileges to download from SAP's site.

In any event, I've re-run the section of the script that I pasted into this question & the error still occurs. Can someone help me figure out how I can get this to work with this EBF? If we can't get it to work, we'll have to rollback the upgrade to 3967.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

It turns out that another developer here upgraded the project to use EBF 4155 without my knowing it. He had asked me for the latest build I had, and I gave him 3967, which was the latest I had. I didn't know that I was running the wrong DLLs on my local workstation until we discussed this problem today.

Once I removed the SQL Anywhere DLLs for EBF 3967 from the GAC, everything worked properly.

I am getting the EBF installer from him & I will install it on my workstation, and this problem won't happen again.

Answers (1)

Answers (1)

VolkerBarth
Contributor

FWIW, here are two fixes w.r.t. DATETIMEOFFSET in newer v12.0.1 EBFs (well, fixed apparently in builts .3968 and .3971 (so a "near miss", as they say):

Can you make sure that the following option value does apply to connections taken from the pool (in case you are using pooled connections)?

SET OPTION PUBLIC.timestamp_with_time_zone_format = 'YYYY-MM-DD HH:NN:SS.SSSSSS+HH:NN'
Former Member
0 Kudos

If you look at the OP, that is the last statement executed in our upgrade script. That option's setting is never changed again, at least not by our software. Doing it this way has worked fine for every release we've used before this. Are you saying I need to set that public option after retrieving a connection from the pool?

Breck_Carter
Participant

No, he is saying "check your assumptions"... i.e., check the assumption that each connection in the pool is actually using that setting, and has not issued its own SET TEMPORARY OPTION statement to change it. Various client application development environments are notorious for messing around with option settings... and then there's always the possibility of a rogue login procedure inside the database. Do not rely on a static inspection of your code... insert some debugging logic that displays the option from inside a connection after it has been established.

Chances are it's not the issue... BUT a database option setting is often no guarantee of the value used by a connection.

VolkerBarth
Contributor
0 Kudos

Yep, that's what I was trying to say, thanks, Breck:)

Former Member
0 Kudos

This problem keeps rearing its ugly head lately. I created a new database and it's happening again.

I execute this command:

SELECT * FROM sa_conn_options() WHERE OptionName LIKE 'timestamp%'


And I get four (4) rows back.:

34,477,'timestamp_format','Controls format for TIMESTAMP values',YYYY-MM-DD HH:NN:SS.SSS
34,478,'timestamp_with_time_zone_format','Controls format for TIMESTAMP WITH TIME ZONE values',YYYY-MM-DD HH:NN:SS.SSS+HH:NN
33,477,'timestamp_format','Controls format for TIMESTAMP values',YYYY-MM-DD HH:NN:SS.SSS
33,478,'timestamp_with_time_zone_format','Controls format for TIMESTAMP WITH TIME ZONE values',YYYY-MM-DD HH:NN:SS.SSS+HH:NN


It turns out I had 2 Interactive SQL sessions open. When I closed one, the number of rows returned reduced to 2.

I think that the entry for timestamp_format may be causing the issue. We have the following statement in our script:

SET OPTION PUBLIC.timestamp_format =


Yet the value returned is not null or the empty string. I've even run that command in the Interactive SQL session that I was using to query the value and it didn't change. What can I do to fix this?

Breck_Carter
Participant
0 Kudos

> I get four (4) rows back

Try calling sa_conn_options ( -1 )... i.e., "If connidparm is less than zero, option values for the current connection are returned."

Breck_Carter
Participant

> Yet the value returned is not null or the empty string.

The Help says timestamp_format "Can be set for an individual connection or for the PUBLIC group. Tak...

However, the sentence "Takes effect immediately" requires translation from whatever language it is written in since it may not mean the same thing as the English sentence "Takes effect immediately".

I used to think I knew how SET OPTION worked, but I don't (and perhaps I never did)... here's what I do instead...

(1) If I want to change an option setting for all future connections, I use SET OPTION PUBLIC.

(2) If I want to change an option for a connection that has already started, I use SET TEMPORARY OPTION. This may be what you want to do.

(3) Whatever the case, I test the code to see if it has the desired effect. It often doesn't.

Also... check to see if the following builtin stored procedure is being called for every connection made by ... since it can have profound effects on application behavior:

ALTER PROCEDURE "dbo"."sp_tsql_environment"()
begin
  if db_property('IQStore') = 'Off' then
    -- SQL Anywhere datastore
    set temporary option close_on_endtrans = 'OFF'
  end if;
  set temporary option ansinull = 'OFF';
  set temporary option tsql_variables = 'ON';
  set temporary option ansi_blanks = 'ON';
  set temporary option chained = 'OFF';
  set temporary option quoted_identifier = 'OFF';
  set temporary option allow_nulls_by_default = 'OFF';
  set temporary option on_tsql_error = 'CONTINUE';
  set temporary option isolation_level = '1';
  set temporary option date_format = 'YYYY-MM-DD';
  set temporary option timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSS';
  set temporary option time_format = 'HH:NN:SS.SSS';
  set temporary option date_order = 'MDY';
  set temporary option escape_character = 'OFF'
end