locked
SyncOrchestrator’s Synchronize() is failing when executing the SelectNewTimestampCommand RRS feed

  • Question

  • Sync Framework’s SyncOrchestrator’s Synchronize() is failing miserably when executing the SelectNewTimestampCommand command with SQL Anywhere 12 databases and OdbcConnection.

    In SQL Anywhere “timestamp” is a date/time data type and cannot be cast to a binary but can be to a bigint.

    In SQL Server “timestamp” is an 8 byte data type.

    OdbcType.Timestamp is “A stream of binary Data (SQL_BINARY).  This maps to a System.Array of Type System.Byte.”

    All SQL examples below are abbreviated – no where clause.  The important stuff is showing.

    All SQL examples result in the same “Exception Error” at the end of the code examples

    All SQL examples work with OdbcDataReader.

    • Example 1

    newCommand.CommandText = "SELECT cast(cast(Max(Last_Modified_at) as bigint) as binary(8)) AS " + DbSyncSession.SyncNewTimestamp + " from SYS.SYSTAB);";

    newCommand.Parameters.Add (DbSyncSession.SyncNewTimestamp, OdbcType.Timestamp).Direction = ParameterDirection.Output;

    newCommand.Parameters[DbSyncSession.SyncNewTimestamp].Size = 8;

    • Example 2

    newCommand.CommandText = "SELECT cast(cast(Max(Last_Modified_at) as bigint) as binary(8)) AS " + DbSyncSession.SyncNewTimestamp + " from SYS.SYSTAB);";

    NO Parameter assignment

    • Example 3

    newCommand.CommandText = "BEGIN DECLARE " + DbSyncSession.SyncNewTimestamp + " binary (8); SELECT cast(cast(Max(Last_Modified_at) as bigint) as binary(8)) as " + DbSyncSession.SyncNewTimestamp + " from SYS.SYSTAB; END;";

    newCommand.Parameters.Add (DbSyncSession.SyncNewTimestamp, OdbcType.Timestamp).Direction = ParameterDirection.Output;

    newCommand.Parameters[DbSyncSession.SyncNewTimestamp].Size = 8;

    • Example 4

    newCommand.CommandText = "SELECT cast(cast(Max(Last_Modified_at) as bigint) as binary(8)) from SYS.SYSTAB;";

    Exception Error:

    Microsoft.Synchronization.Data.DbMetadataSyncException was unhandled

     HResult=-2146233088

     Message=Missing the output value for parameter 'sync_new_timestamp' while executing the SelectNewTimestampCommand command.

     Source=Microsoft.Synchronization

     StackTrace:

           at Microsoft.Synchronization.Data.DbSyncScopeHandler.GetLocalTimestamp(IDbConnection connection, IDbTransaction transaction)

           at Microsoft.Synchronization.Data.DbSyncScopeHandler.ReadScope(IDbConnection connection, IDbTransaction transaction, ReadKnowledgeType readType, ReadForgottenKnowledgeType readFKtype)

    When testing and using the OdbcDataReader the code is:

                       

    Console.Write (data.GetName (0) + " - ");

    Console.WriteLine ( data.GetString (0));

    Results for Examples 1 through 3 are:

                    sync_new_timestamp – 0000000001337BB2

    For Example 4 is:

                    Max(tab.last_modified_at) – 0000000001337BB2

    BTW SelectScopeInfoCommand does not throw an exception so I assume it is working correctly.  It is executed before SelectNewTimestampCommad.

    newCommand.CommandText = "SELECT scope_id, scope_local_id, scope_sync_knowledge, scope_tombstone_cleanup_knowledge, scope_timestamp FROM Sync.Scope_Info WHERE scope_name = '" + SCOPE_NAME + "'";

    So why is SelectNewTimestampCommand failing?

    Monday, December 21, 2015 12:56 PM