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