Late Insert of Data coming via ODBC

Bettina

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

vaprog

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.

Bettina

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.

vaprog

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

GoMaD

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.

Bettina

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 ...

GoMaD

yes, you can use the 5.00 odbc driver on a nav 2009 native server, it still works at the customer.