How to connect other Database ?

Christian_Buehl

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?

pduck

mmmh using automation objects may be possible for example ADO ? but i haven't any experience with that ...

Christian_Buehl

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_tgs

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

Best Regards

Thomas Son.

Christian_Buehl

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);

freire.victormanuel@gmail.com

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?!

Christian_Buehl wrote: »

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);