Sql Statements in C/AL – Part 2 : Create View

The following code can be used to create a sql view in a given, external database.

// local variables
ADOConnection	  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
ADOCommand	  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command
ADORecSet	  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
connString	  Text 1024
activeConnection  Variant
viewName	  Text 100

// the code
// replace the server ip and the ext. databasename with your values
connString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600';
CREATE(ADOConnection);
ADOConnection.ConnectionString(connString);
ADOConnection.Open;
// convert ADO Connection to Variant
activeConnection := ADOConnection;

viewName := 'CRONUS$Item View';
CREATE(ADOCommand);
ADOCommand.ActiveConnection := activeConnection;
ADOCommand.CommandType := 1;
ADOCommand.CommandText :=
  'SELECT * FROM INFORMATION_SCHEMA.TABLES where ' +
  '(TABLE_TYPE = ''VIEW'') and (TABLE_NAME =''' + viewName + ''')';
ADORecSet := ADOCommand.Execute;
IF ADORecSet.EOF = TRUE THEN BEGIN
  ADOCommand.CommandText := 'CREATE VIEW [' + viewName + ']' +
    'AS SELECT No_, Description FROM dbo.[CRONUS$Item]' +
    'WHERE No_ like ''100%''';
  ADOCommand.Execute;
  message('view created');
END ELSE
  MESSAGE('view already exists');

ADORecSet.Close;
ADOConnection.Close;
CLEARALL;

follow also posting “Sql Statements in C/AL – Part 1 : Select”.

cheers

Sql Statements in C/AL – Part 1 : Select

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 variables
ADOConnection	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Connection	
ADOCommand	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Command	
ADOParameter	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Parameter	
ADORecSet	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Recordset	
adoField	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Field	
connString	  Text		1024
activeConnection  Variant		
salesLineStr	  Text		1024
idx		  Integer		
adoValue	  Variant		
adoType		  Integer		

// the code
// replace the server ip and the ext. databasename with your values
ReadFromExternalDatabase()
connString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600';
CREATE(ADOConnection);
ADOConnection.ConnectionString(connString);
ADOConnection.Open;
// convert ADO Connection to Variant
activeConnection := ADOConnection;

CREATE(ADOCommand);
ADOCommand.ActiveConnection := activeConnection;
ADOCommand.CommandType := 1; // type is sql statement
// sample statement: read first record from table sales line
ADOCommand.CommandText := 'select top 1 * from [CRONUS AG$Sales Line]';
ADOCommand.CommandTimeout := 100;

CREATE(ADORecSet);
ADORecSet := ADOCommand.Execute;
ADORecSet.MoveFirst;  // goto first record
FOR 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 converted
    adoValue := adoField.Value
  ELSE
    adoValue := GetFieldValue(adoField);
  IF (adoField.Type  13) THEN  // ado type 13 = Unknown
    // gets the value with index idx of the resulting record
    salesLineStr := 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	  Text		
CREATE(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