Options

Late Insert of Data coming via ODBC

BettinaBettina Member Posts: 22
Hello,

we have a solution where data from an external program will be inserted to the Navision Database via C/ODBC. Sometimes it takes up to 2 hours until we see the data really in the database table. The external program has a log, so that we can see the real time of sending data to Navision. How can we achieve, that the data are inserted immediately?
We use Native Database, Commit Cache is activated. C/ODBC is configured without Commit Cache.
Thanks for evey note!

Bettina

Comments

  • Options
    vaprogvaprog Member Posts: 1,130
    Hi Bettina,

    does ODBC committ transactions immedeately?
    does the ODBC connection access the same database you expect the data to appear in? (-> any replication / synchronisation involved)
    Is the data written to some staging table or directly to the table you expect the data to appear? (-> how and when does the staging occure?)

    ODBC connections work directly on the database. So, any changes are immediate.
  • Options
    BettinaBettina Member Posts: 22
    Hi,

    firstable, I'm shure, that ODBC access the correct database. Sometimes the data are immediately in the database, but sometimes it takes hours. And if I close the external program (ODBC-User will be logged out) the data appear in the database table immediately. The table is a new one only for collecting the data from the external program.
    What do you mean with "does ODBC committ transactions immedeately"? Is there a special command which should be used in the external program, I think it's written in Visual Basic?
    Additional Info: We have the problems since an update to NAV 2009. With 4.0 it was working without these problems. The only thing we changed in configuration is the increased database server cache.
  • Options
    vaprogvaprog Member Posts: 1,130
    Hi Bettina,

    I do not know very much about ODBC programming, but yes, ODBC supports/requires transactions with some ODBC data sources. In many programs accessing generic ODBC data sources there is an option whether you want it to do an implicit commit after every command or you want to give an explicit commit. So I guess this is an configuration option of ODBC itself.

    You (or the programmer of the application) might want to search documentation for more accurate information. As a start, this may help
    http://msdn.microsoft.com/de-de/library/xafb2yhc.aspx
  • Options
    GoMaDGoMaD Member Posts: 313
    Had the same issue today, a very slow insert into a table of the native NAV 2009 (upgraded from a 3.60 version).

    Uninstalled the ODBC driver of NAV 2009 and reinstalled the old NAV 5.0 ODBC driver (not even the 5.1 ODBC driver) and it work again liked a charm.
    Now, let's see what we can see.
    ...
    Everybody on-line.
    ...
    Looking good!
  • Options
    BettinaBettina Member Posts: 22
    Interesting, GoMaD. You can use the ODBC driver from Version 5 with 2009-Server?
    Actually we have no more problems. I have deactivated the "Commit Cache" of the ODBC driver (thought, the customer had done this, but he didn't) and now it works. I will further watch it ...
  • Options
    GoMaDGoMaD Member Posts: 313
    yes, you can use the 5.00 odbc driver on a nav 2009 native server, it still works at the customer.
    Now, let's see what we can see.
    ...
    Everybody on-line.
    ...
    Looking good!
Sign In or Register to comment.