locked
UploadOnly from SqlCe client to Oracle server not working RRS feed

  • Question

  • Hi,

    I have taken the Sync Services for ADO.NET 1.0 examples and built a console app that uses a local database cache as the client and Oracle XE as the server.

    I have the initial snapshot and downloading of incremental changes coming from Oracle to the SqlCe database working fine but now I cannot get the UploadOnly mode of the SyncAgent working.  I have turned on SyncTrace and see that the usual SyncSession parameters are being set fine coming up from the client, but I am not getting the column parameters set for uploaded inserts or updates.  I have added SyncParameters to the Configuration of the SampleSyncAgent like so:

     

    SampleServerSyncProvider server = this.RemoteProvider as SampleServerSyncProvider;

     

    this.Configuration.SyncParameters.Add( new SyncParameter( "UPDCUSTOMERID", server.oldCustomerId ) );

     

    this.Configuration.SyncParameters.Add( new SyncParameter( "UPDCUSTOMERNAME", server.nuCustomerName ) );

     

    this.Configuration.SyncParameters.Add( new SyncParameter( "UPDSALESPERSON", server.nuSalesPerson ) );

     

    this.Configuration.SyncParameters.Add( new SyncParameter( "UPDCUSTOMERTYPE", server.nuCustomerType ) );

     

    this.Configuration.SyncParameters.Add( new SyncParameter( "UPDSALESNOTE", server.nuSalesNote ) );

    The parameter names correspond to the column input parameters of my PL/SQL procedure that is the UpdateCommand of my SampleServerSyncProvider.  I have tried: column mappings in the SampleServerSyncProvider, a schema for creating the client table, creating properties of the SampleServerSyncProvider and setting them in the SampleServerSyncProvider_ApplyingChanges event handler, and a few other things - all with no success.

    How can I set values for the column input parameters for the Insert, Update, & DeleteCommands of the SampleServerSyncProvider?  It certainly isn't working automagically as it does for SQL Server.

    I can provide the other portions of code, but it's not anything more than a port from SQL Server T-SQL into PL/SQL stored procs.

    I am using the following Sync assemblies in my solution:

    Microsoft. Synchronization 2.0.0.0
    Microsoft.Synchronization.Data 1.0.0.0
    Microsoft.Synchronization.Data.Server 1.0.0.0
    Microsoft.Synchronization.Data.SqlServerCe 1.0.0.0


    Thank You for any helpful reply,
    Peter G.

    • Moved by Max Wang_1983 Thursday, April 21, 2011 10:31 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Saturday, November 7, 2009 10:41 AM

Answers

  • Solved this problem after many long hours pouring over the SyncTrace logs, tracing the Oracle server, and trial & error.  You set the parameters for the Insert, Update, & DeleteCommands of the DbServerSyncProvider child class (SampleServerSyncProvider) object by using the column names of the SyncTable with a ':' in front of it.  For example, here's the table I want to sync:

    create table SYNC_CUSTOMERS( CUSTOMERID NVARCHAR2(36) not null primary key, CUSTOMERNAME NVARCHAR2(100) not null, SALESPERSON NVARCHAR2(100) not null, CUSTOMERTYPE NVARCHAR2(100), SALESNOTE NVARCHAR2(1000), INSERTTIMESTAMP TIMESTAMP(6) not null, UPDATETIMESTAMP TIMESTAMP(6) not null, INSERTID NUMBER default 0 not null, UPDATEID NUMBER default 0 not null );

    Next is the PL/SQL stored proc to insert records at the server coming up from the SqlCE client.  The parameters to this stored proc for the table columns must be different from the actual names of the columns.

    PROCEDURE INSERT_SYNC_CUSTOMER

    ( p_CustomerId

     

     

    , p_CustomerName

     

     

    , p_SalesPerson

     

     

    , p_CustomerType

     

     

    , p_SalesNote

     

     

    , sync_originator_id

     

     

    , sync_row_count

     

     

    )

     

     

     

     

     

     

     

     

     

     

     

    OracleCommand insSyncCustomerCmd =

    new OracleCommand( "BEGIN MSF_PROVISIONING_PKG.INSERT_SYNC_CUSTOMER( p_CustomerId => :CUSTOMERID, p_CustomerName => :CUSTOMERNAME, p_SalesPerson => :SALESPERSON, p_CustomerType => :CUSTOMERTYPE, p_SalesNote => :SALESNOTE, sync_originator_id => :sync_originator_id, sync_row_count => :sync_row_count ); END;", serverConn );

    insSyncCustomerCmd.Parameters.Add( ":CUSTOMERID", OracleType.NVarChar, 36 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":CUSTOMERID"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":CUSTOMERNAME", OracleType.NVarChar, 100 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":CUSTOMERNAME"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":SALESPERSON", OracleType.NVarChar, 100 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":SALESPERSON"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":CUSTOMERTYPE", OracleType.NVarChar, 100 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":CUSTOMERTYPE"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":SALESNOTE", OracleType.NVarChar, 1000 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":SALESNOTE"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":" + SyncSession.SyncOriginatorId, OracleType.Number ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters.Add( ":" + SyncSession.SyncRowCount, OracleType.Number ).Direction = ParameterDirection.Output;

    syncCustomersSyncAdapter.InsertCommand = insSyncCustomerCmd;



    Hope this helps someone else,

    Peter

    INSERT INTO sync_customers( CustomerId, CustomerName, SalesPerson, CustomerType, SalesNote, InsertId, UpdateId )

     

     

    VALUES( p_CustomerId, p_CustomerName, p_SalesPerson, p_CustomerType, p_SalesNote, sync_originator_id, sync_originator_id );

    sync_row_count :=

     

    SQL%ROWCOUNT;

     

     

    END INSERT_SYNC_CUSTOMER;

    Finally, the Sync Framework "knows" only the column names, so the SyncParameters that it provides for the table columns are just the column names preceded by a colon (:).  The PL/SQL stored proc's parameters are bound to the SyncParameters for the columns when creating the Insert or Update or Delete commands in the constructor of the SampleServerSyncProvider:

    // Applies Client inserts to the Server.
    BEGIN
    AS
    OUT NUMBER
    IN NUMBER
    IN NVARCHAR2 DEFAULT NULL
    IN NVARCHAR2
    IN NVARCHAR2
    IN NVARCHAR2
    IN NVARCHAR2
    • Marked as answer by Peter G_ Friday, November 13, 2009 6:19 PM
    • Edited by Peter G_ Friday, November 13, 2009 6:23 PM corrected code
    Friday, November 13, 2009 6:18 PM

All replies

  • Solved this problem after many long hours pouring over the SyncTrace logs, tracing the Oracle server, and trial & error.  You set the parameters for the Insert, Update, & DeleteCommands of the DbServerSyncProvider child class (SampleServerSyncProvider) object by using the column names of the SyncTable with a ':' in front of it.  For example, here's the table I want to sync:

    create table SYNC_CUSTOMERS( CUSTOMERID NVARCHAR2(36) not null primary key, CUSTOMERNAME NVARCHAR2(100) not null, SALESPERSON NVARCHAR2(100) not null, CUSTOMERTYPE NVARCHAR2(100), SALESNOTE NVARCHAR2(1000), INSERTTIMESTAMP TIMESTAMP(6) not null, UPDATETIMESTAMP TIMESTAMP(6) not null, INSERTID NUMBER default 0 not null, UPDATEID NUMBER default 0 not null );

    Next is the PL/SQL stored proc to insert records at the server coming up from the SqlCE client.  The parameters to this stored proc for the table columns must be different from the actual names of the columns.

    PROCEDURE INSERT_SYNC_CUSTOMER

    ( p_CustomerId

     

     

    , p_CustomerName

     

     

    , p_SalesPerson

     

     

    , p_CustomerType

     

     

    , p_SalesNote

     

     

    , sync_originator_id

     

     

    , sync_row_count

     

     

    )

     

     

     

     

     

     

     

     

     

     

     

    OracleCommand insSyncCustomerCmd =

    new OracleCommand( "BEGIN MSF_PROVISIONING_PKG.INSERT_SYNC_CUSTOMER( p_CustomerId => :CUSTOMERID, p_CustomerName => :CUSTOMERNAME, p_SalesPerson => :SALESPERSON, p_CustomerType => :CUSTOMERTYPE, p_SalesNote => :SALESNOTE, sync_originator_id => :sync_originator_id, sync_row_count => :sync_row_count ); END;", serverConn );

    insSyncCustomerCmd.Parameters.Add( ":CUSTOMERID", OracleType.NVarChar, 36 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":CUSTOMERID"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":CUSTOMERNAME", OracleType.NVarChar, 100 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":CUSTOMERNAME"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":SALESPERSON", OracleType.NVarChar, 100 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":SALESPERSON"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":CUSTOMERTYPE", OracleType.NVarChar, 100 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":CUSTOMERTYPE"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":SALESNOTE", OracleType.NVarChar, 1000 ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters[":SALESNOTE"].DbType = DbType.String;

    insSyncCustomerCmd.Parameters.Add( ":" + SyncSession.SyncOriginatorId, OracleType.Number ).Direction = ParameterDirection.Input;

    insSyncCustomerCmd.Parameters.Add( ":" + SyncSession.SyncRowCount, OracleType.Number ).Direction = ParameterDirection.Output;

    syncCustomersSyncAdapter.InsertCommand = insSyncCustomerCmd;



    Hope this helps someone else,

    Peter

    INSERT INTO sync_customers( CustomerId, CustomerName, SalesPerson, CustomerType, SalesNote, InsertId, UpdateId )

     

     

    VALUES( p_CustomerId, p_CustomerName, p_SalesPerson, p_CustomerType, p_SalesNote, sync_originator_id, sync_originator_id );

    sync_row_count :=

     

    SQL%ROWCOUNT;

     

     

    END INSERT_SYNC_CUSTOMER;

    Finally, the Sync Framework "knows" only the column names, so the SyncParameters that it provides for the table columns are just the column names preceded by a colon (:).  The PL/SQL stored proc's parameters are bound to the SyncParameters for the columns when creating the Insert or Update or Delete commands in the constructor of the SampleServerSyncProvider:

    // Applies Client inserts to the Server.
    BEGIN
    AS
    OUT NUMBER
    IN NUMBER
    IN NVARCHAR2 DEFAULT NULL
    IN NVARCHAR2
    IN NVARCHAR2
    IN NVARCHAR2
    IN NVARCHAR2
    • Marked as answer by Peter G_ Friday, November 13, 2009 6:19 PM
    • Edited by Peter G_ Friday, November 13, 2009 6:23 PM corrected code
    Friday, November 13, 2009 6:18 PM
  • Hi!
    I am doing a synchronization of Oracle DB's. I have a couple of question, if you are kind to answer. How do you select data (example for SelectChanges, SelectRow etc.). I do it with a cursor but for SelectRow command this doens't work. It says (although the cursor parameter is OUT parameter), that it cannot find a value to set for it before callimg the procedure. For the SelectChanges command everything works ok.

    For the insert command, for me it works this way:
     OracleCommand insCustomerCmd = new OracleCommand();
                insCustomerCmd.CommandType = CommandType.StoredProcedure;
                insCustomerCmd.CommandText = "sp_orders_ApplyInsert";
                insCustomerCmd.Parameters.Add("order_id", OracleType.Number);
                insCustomerCmd.Parameters.Add("order_date", OracleType.Timestamp);
                insCustomerCmd.Parameters.Add(DbSyncSession.SyncRowCount, OracleType.Number).Direction = ParameterDirection.Output;
    
                adapterCustomer.InsertCommand = insCustomerCmd;
    CREATE OR REPLACE procedure sp_orders_applyinsert 
    (                        
        order_id NUMBER,
        order_date TIMESTAMP,
        sync_row_count OUT NUMBER 
    )    
    AS
    nCount NUMBER;
    p_order_id NUMBER;
    p_order_date TIMESTAMP;
    BEGIN
    p_order_id := order_id;
    p_order_date := order_date;
    
    	sync_row_count := 0;
    	
    	select count(*) INTO nCount 
    	from orders_tracking tt 
    	where tt.order_id = p_order_id;
    	
        IF nCount = 0 THEN
        	insert into orders (order_id, order_date) 
                values (p_order_id, p_order_date);
                
            sync_row_count := sql%rowcount;
        ELSE
        	sync_row_count := 0;
        END IF;
        
    END;

    Best regards,

    Costin
    Tuesday, November 24, 2009 9:56 AM
  • Paun,

    I would be surprised if your insert command worked.  I saw other posts that stated that the .Net parameters to the command must have the colon as the first character. For example:

    NOT - insCustomerCmd.Parameters.Add("order_id", OracleType.Number);
    BUT - insCustomerCmd.Parameters.Add(":order_id", OracleType.Number);

    But, if your insert command works that means that it is simpler to bind the .Net sync parameters to the Oracle stored proc parameters.  However, I don't like the declaration of local variables in the stored proc - essentially you are performing the binding of variables inside your stored proc instead of the .Net code.

    To select data for the incremental insert, update, and delete stored procs, I used a SYS_REFCURSOR as the output variable that you open and select. For example:

      PROCEDURE SELECT_INC_INS_SYNC_CUSTOMERS
      ( sync_initialized IN NUMBER
      , sync_last_received_anchor IN OUT VARCHAR2
      , sync_new_received_anchor IN VARCHAR2
      , sync_originator_id IN NUMBER
      , select_sync_customers OUT SYS_REFCURSOR
      ) AS
      BEGIN
        OPEN select_sync_customers
        FOR SELECT CustomerId, CustomerName, SalesPerson, CustomerType, SalesNote
          FROM sync_customers
          WHERE InsertTimestamp > sync_last_received_anchor
          AND InsertTimestamp <= sync_new_received_anchor
          AND InsertId <> sync_originator_id;
      END SELECT_INC_INS_SYNC_CUSTOMERS;

    Here's hoping you get something out of this.
    Peter

    Friday, November 27, 2009 8:41 AM