hi, I am using Navision Financials 2.06 with a navision database. Is it possible to make a direct connection with an oracla database? Is there additional software required? I have to fill up an oracle database connected to a web-site. thanks Tom Delchambre Tom.delchambre@vandemoortele.com
hi, sorry, I have to connect to an MS SQL server. Can i do it thrue ODBC? Or is there a easier/faster way?
You can use Automation. Step 1 - Setup a couple of globals: Name DataType Subtype ADOconn Automation ‘Microsoft ActiveX Data Objects 2.6 Library’.Connection ADOrecset Automation ‘Microsoft ActiveX Data Objects 2.6 Library’.Recordset Step 2 - Open the database with something like SqlString := STRSUBSTNO(‘provider=%1;driver={SQL Server};server=%3;uid=%4;pwd=%5;database=%6’,PRO,DSN,SRV,UID,PWD,DB); ADOconn.Open(SqlString); where PRO=provider (e.g. MSDASQL) SRV=server name UID=username PWD=password DB =database (e.g. Northwind) Step 3 - Load the recordset with something like: SqlString := STRSUBSTNO(‘SELECT * FROM Products WHERE (SupplierID = %1)’,SuppID); ADOrecset.Open(SqlString,ADOconn,3,3); MESSAGE(’%1 records retrieved.’,ADOrecset.RecordCount); Step 4 - Position yourself in the recordset with something like: IF ADOrecset.RecordCount <> 0 THEN ADOrecset.MoveFirst ELSE EXIT; There are also ADOrecset.AbsolutePosition/MoveNext/MoveLast etc. which may come in handy later. Step 5 - Get the data from the recordset using something like: (a) Data := ADOrecset.GetRows(1,Bookmark,‘ProductID’); OR (b) RecString := ADOrecset.GetString(2,1,’|’); (a) returns each field as a variant so you’ll have to convert it to whatever you want to use in Navision and you have to bookmark the record first because otherwise it moves onto the next record after each GetRows. (b) returns the whole record as a string with “|” between each field so you’ll have to split it up yourself. I’m sure somebody will come up with a more sophisticated way of handling things but this works okay for me. No warranty express or implied . Cheers, John