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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s