locked
Sync SQL2005<->PostgreSQL RRS feed

  • Question

  • Is it possible?

    How to do it?

    Thank you.
    Regards,
    Oleg.
    • Moved by Hengzhe Li Friday, April 22, 2011 7:32 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Sunday, January 6, 2008 3:49 PM

Answers

  • Hi

     

    In theory it should work. I assume you can use ADO.NET to access PostgreSQL using .NET OleDB provider. You could do the same thing with DbServerSyncProvider.

     

    It may be good idea to check out the demo on synchronizing with Oracle.

     

    Thanks

    Wednesday, January 9, 2008 8:33 AM

All replies

  • Hi

     

    In theory it should work. I assume you can use ADO.NET to access PostgreSQL using .NET OleDB provider. You could do the same thing with DbServerSyncProvider.

     

    It may be good idea to check out the demo on synchronizing with Oracle.

     

    Thanks

    Wednesday, January 9, 2008 8:33 AM
  •  

    Hi Rafik,

    thank you for the response.

     

    Yet 2 questions.

    1. I want to synchronize mssql->postgersql and source db has daily 10 000 - 300 000 records to sync. Firstly should be synched 10 000 000 records.

    Record is near 400 bytes.

    How do you think is it possible to do using SyncServices?

     

    2. Can I use PGSQL .NET provider but not OLEDB provider?

     

    Regards,

    Oleg.

     

    Wednesday, January 9, 2008 8:39 AM
  • Hi Oleg,

     

    1- That's where batching comes into play. I've a batching demo sample on my web site. Check it out.

    2- Of course you could use it.That's even better! I did not know that there is a .NET data access provider for PGSQL. Good to know though.

     

    Thanks

    Thursday, January 10, 2008 3:11 AM
  • Hi Rafik,

     

    1. thanx, I will research batching demo

    2. Yes, it exists and looks not bad :-).

    I am going to remake sample for the Oracle sync to PostgreSql sync. It is useful for teh learning.

    It was done, but unfortunately  I have 2 issues:

          2.1. on my x64 server i have a problem with CE engine, I will investigate what is it . I've wrote message to this forum.

    if (false == File.Exists(dbPathTextBox.Text))

    {

    SqlCeEngine clientEngine = new SqlCeEngine(connString); //<-- here

    clientEngine.CreateDatabase();

    clientEngine.Dispose();

    }

     

    exp = {"An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)"}

     

    I changed project to x86 and it is working

     

          2.2. when I start Synchronize() I receive error

                    SyncStatistics syncStats = syncAgent.Synchronize(); // <---- here

                    // Update the UI
                    _progressForm.EnableClose();
                    _progressForm = null;
                    buttonRefreshOrders_Click(null, null);                          
                }
                //exp.Message = "Unable to cast object of type 'Npgsql.NpgsqlParameter' to type 'System.Data.Common.DbParameter'."

     

    How can I solve this?...

     

    thank you.

    Regards,

    Oleg

     

    Thursday, January 10, 2008 7:43 AM
  •  Rafik Robeal wrote:

    Hi Oleg,

     

    1- That's where batching comes into play. I've a batching demo sample on my web site. Check it out.

    2- Of course you could use it.That's even better! I did not know that there is a .NET data access provider for PGSQL. Good to know though.

     

    Thanks



    The link to your web site its broken.

    I'm interesting in a sample whit PGSQL using Microsoft Synchronization Services...
    Friday, January 25, 2008 10:14 AM
  •  

    Hi,

    Rafik's site is

    www.syncguru.com

     

     

    I am trying to do sample for PGSQL, butnow have one problem:

     

    Syn Services require select or exec command with parameter (!) for retrieving anchor.

    I cannot solve this.

     

    Regards,

    Oleg.

     

     

    Friday, January 25, 2008 11:27 AM
  • try this http://npgsql.projects.postgresql.org/

    Code Snippet

    using Npgsql;
    using NpgsqlTypes;
    ...


     NpgsqlCommand anchorCmd = new NpgsqlCommand();
    anchorCmd.CommandType = CommandType.Text;
    anchorCmd.CommandText =
    " SELECT MAX(idx) FROM (SELECT MAX(track_insert) AS idx FROM orders UNION SELECT (track_update) AS idx FROM orders) AS idx ;";
    NpgsqlParameter param = new NpgsqlParameter(SyncSession.SyncNewReceivedAnchor, NpgsqlDbType.Timestamp, 20);
    param.Direction = ParameterDirection.Output;
    anchorCmd.Parameters.Clear();
    anchorCmd.Parameters.Add(param);

    serverSyncProvider.SelectNewAnchorCommand = anchorCmd;
    anchorCmd.Connection = serverConnection;
    ...


    Or do you have other solution?
    Please post it.
    thanks in advance.

    Edit:
    postgres has probably problem with output parameter.

    "...Npgsql "simulates" output parameter by parsing the first result set from the execution of a query and translating it to output parameters value.... "
    example from http://npgsql.projects.postgresql.org/docs/manual/UserManual.html
            NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");
    conn.Open();

    // Send a query to backend.
    NpgsqlCommand command = new NpgsqlCommand("select * from tablea where column1 = 2", conn);

    // Now declare an output parameter to receive the first column of the tablea.

    NpgsqlParameter firstColumn = new NpgsqlParameter("firstcolumn", NpgsqlDbType.Integer);
    firstColumn.Direction = ParameterDirection.Output;

    command.Parameters.Add(firstColumn);

    try
    {
    command.ExecuteNonQuery();

    // Now, the firstcolumn parameter will have the value of the first column of the resultset.
    Console.WriteLine(firstColumn.Value);
    }

    finally
    {
    conn.Close();
    }

    it's working for example from syncguru.com
    Wednesday, April 23, 2008 6:27 PM
  •  Ocherk wrote:

     

    Hi,

    Rafik's site is

    www.syncguru.com

     

     

    I am trying to do sample for PGSQL, butnow have one problem:

     

    Syn Services require select or exec command with parameter (!) for retrieving anchor.

    I cannot solve this.

     

    Regards,

    Oleg.

     

     



    Dear Ochecrk,

    Have you been able to do sync with Postgres? I am learning sync services between Postgres and SQLCE, so can I have a example of how to do sync with Postgres. Thank you very much.

    CT
    Wednesday, October 8, 2008 5:53 AM
  • Hi,

    I am working on this in background... Now I have no soluition but I hope it can be solved in near future.

    I am cooperating with authors of PGSQL OLE DB provider, probably it can be solved via using their provider.

    I.e. to use OleDbCOnnection etc but not native client.

    Their porvider is not free but it is not expensive and much better then exisitng one.

     

    Regards,

    Oleg.

     

     

    Wednesday, October 8, 2008 6:00 AM
  • Are you able now to sync with postgre? I'm trying it but didn't get it to work (PGSQL<->SQLCE)...
    Monday, December 22, 2008 3:21 PM