How to connect other Database ?

Christian_BuehlChristian_Buehl Member Posts: 145
edited 2004-03-27 in Navision Financials
I'm looking for a solution to write Data from Navision into a different database. A connection via ODBC (DAO or ADO) would be preferred.

It's not about reading or writing Navision via ODBC from outside. I want to write data into a SQL-Database (MySQL, MAXDB) directly after a user has pushed a button in Navision.

Is there anyone who can show me a tipp how to do this?

Comments

  • pduckpduck Member Posts: 147
    mmmh using automation objects may be possible for example ADO ? but i haven't any experience with that ...
  • Christian_BuehlChristian_Buehl Member Posts: 145
    :D It took some sweat on my old brain, but now it works with ADODB.

    To help others here the code, here used with a simple Access Database.
    Other Databases should work with just a different connection string

    Example for ADO-Access on external ODBC-Database
    ADOConn 	Automation   'Microsoft ActiveX Data Objects 2.7 Library'.Connection
    ADOrs   	Automation   'Microsoft ActiveX Data Objects 2.7 Library'.Recordset
    ConnStr     	Text        200
    AnzCount    	Text        30
    OpenMethod  	Integer
    LockMethod  	Integer
    Name        	Text        200
    Str         	Text        200
    
    
    // Test ODBC-Connector
    OpenMethod := 2; // 1=adOpenKeyset; 2=adOpenDynamic; 3= adOpenStatic
    LockMethod := 3; // 1=dLockreadonly; 2=adLockPessimistic; 3=adLockOptimistic; 4=adLockBatchOptimistic
    CREATE(ADOconn);
    ConnStr := 'PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=D:\TestDB.mdb';
    ADOconn.Open(ConnStr);
    CREATE(ADOrs);
    
    ADOrs.Open('Select Name,Street from T1',ADOconn,OpenMethod,LockMethod);
    ADOrs.MoveFirst;
    Name := ADOrs.Fields.Item('Name').Value;
    Str := ADOrs.Fields.Item('Street').Value;
    // MESSAGE('Name %1 - Street %2', Name, Str);
    
    ADOrs.MoveNext;
    Name := ADOrs.Fields.Item('Name').Value;
    Str := ADOrs.Fields.Item('Street').Value;
    // MESSAGE('Name %1 - Strasse %2', Name, Str);
    // Update current data
    ADOrs.Update('Street','Testroad');
    ADOrs.Update('Name','Anyone');
    
    
    // Insert new Data
    ADOrs.AddNew;
    ADOrs.Update('Strasse','New Street Text');
    ADOrs.Update('Name','New name');
    
    ADOrs.Close;
    ADOconn.Close;
    CLEAR(ADOrs);
    CLEAR(ADOconn);
    
  • son_tgsson_tgs Member Posts: 1
    Hi,

    I tried to use your code to connect to a MySQL database using the MySQL ODBC driver.

    It works fine when im reading data from the MySQL database, but i can not write data back with your eksample. It returns some errors in danish and translated into english "One or more error occured by a OLE DB action on several steps"

    Any help is welcome :wink:

    Best Regards

    Thomas Son.
  • Christian_BuehlChristian_Buehl Member Posts: 145
    This is an excerpt of an working connection to mysql via ADO.
    For more use look on proper delimiters and correct quoting of special characters like \ or ' .
    Hope this helps
    OpenMethod := 1; // 1=adOpenKeyset; 2=adOpenDynamic; 3= adOpenStatic
    LockMethod := 3; // 1=dLockreadonly; 2=adLockPessimistic; 3=adLockOptimistic; 4=adLockBatchOptimistic
    CREATE(ADOconn);
    ConnStr :=  'Driver={MySQL ODBC 3.51 Driver}; SERVER=webtest; Database=db; User=anyone; Password=xxxx; Option=3';;
    ADOconn.Open(ConnStr);
    CREATE(ADOrs);
    SQLstring := STRSUBSTNO('Select UID,username from db.users where username = "%1"',AccessID);
    ADOrs.Open(SQLstring,ADOconn,OpenMethod,LockMethod);
    IF ADOrs.EOF OR ADOrs.BOF THEN BEGIN  // insert nötig
      SQLstring := 'INSERT INTO db.users SET username="%1",usergroup="%2", adr="%3" ';
      SQLstring := STRSUBSTNO(SQLstring, User, TGroupNum, address)
      ADOconn.Execute(SQLstring);
      ADOrs.UpdateBatch;
      ADOrs.Requery;
      ADOrs.MoveFirst;
      TUID := ADOrs.Fields.Item('UID').Value;
    END ELSE BEGIN
      SQLstring := 'Update db.users set username="%1", address="%2", ';
      SQLstring := STRSUBSTNO(SQLstring, User, address ;
      ADOconn.Execute(SQLstring);
      ADOrs.UpdateBatch;
      ADOrs.Requery;
      ADOrs.MoveFirst;
      TUID := ADOrs.Fields.Item('UID').Value;
    END;
    
    ADOrs.Close;   CLEAR(ADOrs);
    ADOconn.Close; CLEAR(ADOconn);
    
  • edited 2016-02-12
    hi Christian_Buehl, your code work fine for me but only in Classic Client NAV, when try to connect in RTC NAV give the erro "The Microsoft Jet database engine can not open the file xxx.mdb. He was already opened exclusively by another user or you need permission to view its data"

    Please can you help me to solve this problem?!

    :D It took some sweat on my old brain, but now it works with ADODB.

    To help others here the code, here used with a simple Access Database.
    Other Databases should work with just a different connection string

    Example for ADO-Access on external ODBC-Database
    ADOConn 	Automation   'Microsoft ActiveX Data Objects 2.7 Library'.Connection
    ADOrs   	Automation   'Microsoft ActiveX Data Objects 2.7 Library'.Recordset
    ConnStr     	Text        200
    AnzCount    	Text        30
    OpenMethod  	Integer
    LockMethod  	Integer
    Name        	Text        200
    Str         	Text        200
    
    
    // Test ODBC-Connector
    OpenMethod := 2; // 1=adOpenKeyset; 2=adOpenDynamic; 3= adOpenStatic
    LockMethod := 3; // 1=dLockreadonly; 2=adLockPessimistic; 3=adLockOptimistic; 4=adLockBatchOptimistic
    CREATE(ADOconn);
    ConnStr := 'PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=D:\TestDB.mdb';
    ADOconn.Open(ConnStr);
    CREATE(ADOrs);
    
    ADOrs.Open('Select Name,Street from T1',ADOconn,OpenMethod,LockMethod);
    ADOrs.MoveFirst;
    Name := ADOrs.Fields.Item('Name').Value;
    Str := ADOrs.Fields.Item('Street').Value;
    // MESSAGE('Name %1 - Street %2', Name, Str);
    
    ADOrs.MoveNext;
    Name := ADOrs.Fields.Item('Name').Value;
    Str := ADOrs.Fields.Item('Street').Value;
    // MESSAGE('Name %1 - Strasse %2', Name, Str);
    // Update current data
    ADOrs.Update('Street','Testroad');
    ADOrs.Update('Name','Anyone');
    
    
    // Insert new Data
    ADOrs.AddNew;
    ADOrs.Update('Strasse','New Street Text');
    ADOrs.Update('Name','New name');
    
    ADOrs.Close;
    ADOconn.Close;
    CLEAR(ADOrs);
    CLEAR(ADOconn);
    

Sign In or Register to comment.