There is no possibility to read data from an external database with standard c/al. Best way to do that till NAV version 2009 is using ADO. With Nav vs. 2013 upwards you can use .Net classes.
The following code can be used to read data from a given, external database.
// local variablesADOConnection Automation'Microsoft ActiveX Data Objects 2.8 Library'.ConnectionADOCommand Automation'Microsoft ActiveX Data Objects 2.8 Library'.CommandADOParameter Automation'Microsoft ActiveX Data Objects 2.8 Library'.ParameterADORecSet Automation'Microsoft ActiveX Data Objects 2.8 Library'.RecordsetadoField Automation'Microsoft ActiveX Data Objects 2.8 Library'.FieldconnString Text1024activeConnection VariantsalesLineStr Text1024idx IntegeradoValue VariantadoType Integer// the code// replace the server ip and the ext. databasename with your valuesReadFromExternalDatabase()connString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600';CREATE(ADOConnection);ADOConnection.ConnectionString(connString);ADOConnection.Open;// convert ADO Connection to VariantactiveConnection := ADOConnection;CREATE(ADOCommand);ADOCommand.ActiveConnection := activeConnection;ADOCommand.CommandType := 1; // type is sql statement// sample statement: read first record from table sales lineADOCommand.CommandText := 'select top 1 * from [CRONUS AG$Sales Line]';ADOCommand.CommandTimeout := 100;CREATE(ADORecSet);ADORecSet := ADOCommand.Execute;ADORecSet.MoveFirst; // goto first recordFOR idx := 1 TO ADORecSet.Fields.Count - 1 DO BEGIN adoField := ADORecSet.Fields.Item(idx); adoType := adoField.Type; IF adoType 131 THEN // ado type numeric must be convertedadoValue := adoField.Value ELSEadoValue := GetFieldValue(adoField); IF (adoField.Type 13) THEN // ado type 13 = Unknown// gets the value with index idx of the resulting recordsalesLineStr := salesLineStr + FORMAT(adoValue); salesLineStr := salesLineStr + ';';END;MESSAGE(salesLineStr);ADORecSet.Close;ADOConnection.Close;CLEARALL;GetFieldValue(adoField : 'Microsoft ActiveX Data Objects 2.8 Library'.Field) : Text// local variables// ADOStream Automation'Microsoft ActiveX Data Objects 2.8 Library'.Stream// RetVal TextCREATE(ADOStream);ADOStream.Open;ADOStream.WriteText(adoField.Value);ADOStream.Position:= 0;RetVal:= ADOStream.ReadText;ADOStream.Close;exit(RetVal);
helpful links:
http://www.w3schools.com/asp/ado_ref_connection.asp
http://www.w3schools.com/asp/ado_ref_command.asp
http://www.w3schools.com/asp/prop_comm_commandtype.asp#commandtypeenum
Great blog post. Small correction : in the “GetFieldValue” function I think you need to Create the ADOStream, before open it up.
Keep on writing.
LikeLike
thx
LikeLike