Get data from Oracle to Navision 4.0

Comments

• Options

  dmitrip Member Posts: 44

  2005-08-09

  I never work with Oracle relational database (only with Oracle Financial Analyzer). But I suppose that you can use SQL Server DTS to download data from Oracle DB to SQL Server DB.
  Latter you can use:
  1. Excel - to verify data plus Navision dataport to upload data into Navision.
  2. Upload data directly on SQL Server.
  
  All master data can be uploaded to corresponding tables directly (i.e. customer, vendor, etc.). Open balances are uploaded to corresponding journals and are posted from there.
  
  As far as I know Microsoft suggest a special transition program (what does it include???) from Oracle to Navision. Please check their site for more info.
  
  Dmitri.

  0
• Options

  wonmo Member Posts: 139

  2005-08-09

  Just use ADO. We've done this to connect to an Oracle database and as long as you design your tables and code well, it's very, very fast.

  0
• Options

  Angelo Member Posts: 180

  2005-08-10

  Hello,Wonmo
  
  May I know the code using ADO to connect to Oracle database system?
  
  regards,
  Angelo

  0
• Options

  rossi Member Posts: 36

  2005-08-10

  Hi!
  
  Here is an example how to use ADO to retrieve data from external datasources, in this case a MS SQL server. This should work on most databases, just change the connection string accordingly..
  
  Examples of connection strings at http://www.connectionstrings.com/
  
  To test this codeunit, just run the codeunit from object explorer.
  
  Enjoy!
  
  
  OBJECT Codeunit 80006 ADO Usage
  {
  OBJECT-PROPERTIES
  {
  Dato=10.08.05;
  Tidspunkt=11:05:13;
  Endret=Ja;
  Versjonsoversikt=;
  }
  PROPERTIES
  {
  OnRun=BEGIN
  IF ISCLEAR(aADO_Con) THEN
  CREATE(aADO_Con);
  IF ISCLEAR(aADO_Rs) THEN
  CREATE(aADO_Rs);
  
  aADO_Con.Open('Provider=SQLOLEDB;Data source=LocalHost;database=Northwind;User Id=sa;Password=;');
  aADO_Rs.Open('select * from Customers',aADO_Con);
  
  aADO_Rs.MoveFirst;
  WHILE NOT aADO_Rs.EOF DO
  BEGIN
  
  // Do something with the record
  MESSAGE('%1',aADO_Rs.Fields.Item(0).Value);
  
  aADO_Rs.MoveNext();
  END;
  END;
  
  }
  CODE
  {
  VAR
  aADO_Con@1000000000 : Automation "{EF53050B-882E-4776-B643-EDA472E8E3F2} 2.7:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.7 Library'.Connection";
  aADO_Rs@1000000002 : Automation "{EF53050B-882E-4776-B643-EDA472E8E3F2} 2.7:{00000535-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.7 Library'.Recordset";
  }
  }

  0
• Options

  wonmo Member Posts: 139

  2005-08-10

  The above code for SQL Server is pretty much what you want to do - establish the connection and then populate a Recordset object with the data that you want. However with Oracle you will need to acquire the ADO data provider. DO NOT USE ODBC as this is very, very slow. Also. be aware that the SQL syntax will be slightly different than with SQL Server. As an example of selecting records based on date range in Oracle:
  
  ...WHERE CREATED_DATE >= TO_DATE(''06/20/05'', ''MM/DD/YY'')...

  0