c/al

Encrypt/Decrypt strings in C/AL

8 Comments

Following can be used, if you need a encryption/decryption function in NAV. In this quite simple sample there is shown the usage of the TripleDES encryption algorithm. The base are the cryptograhic function in namespace System.Security.Cryptography of the .net framework.

// local variablesTripleDESEncDotNetSystem.Security.Cryptography.TripleDES.'mscorlib, Version=4.0.0.0, culture=neutral, PublicKeyToken=b77a5c561934e089'cStreamDotNetSystem.Security.Cryptography.CryptoStream.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'cModeDotNetSystem.Security.Cryptography.CryptoStreamMode.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'cEncDotNetSystem.Security.Cryptography.ICryptoTransform.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'TripleDESDecDotNetSystem.Security.Cryptography.TripleDES.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'cDecDotNetSystem.Security.Cryptography.ICryptoTransform.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'cKeyDotNetSystem.Byte.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'initVectorDotNetSystem.Byte.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'fStreamDotNetSystem.IO.FileStream.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'fModeDotNetSystem.IO.FileMode.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'sWriterDotNetSystem.IO.StreamWriter.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'sReaderDotNetSystem.IO.StreamReader.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'sDataText 1024FileNameText 1024fileFile// the codesData := 'Here is some data to encrypt.';FileName := 'c:\temp\CText.txt';// encrypt partfMode := fMode.OpenOrCreate;fStream := fStream.FileStream(FileName,fMode);TripleDESEnc := TripleDESEnc.Create('TripleDES');// create encryptor and internally a random key and a random IVcEnc := TripleDESEnc.CreateEncryptor;cMode := cMode.Write;cStream := cStream.CryptoStream(fStream,cEnc,cMode);sWriter := sWriter.StreamWriter(cStream);sWriter.WriteLine(sData);sWriter.Close;cStream.Close;fStream.Close;file.OPEN(FileName);file.READ(sData);file.CLOSE;// display encrypted stringMESSAGE('Encrypted: ' + sData);// decryptfMode := fMode.OpenOrCreate;fStream := fStream.FileStream(FileName, fMode);TripleDESDec := TripleDESDec.Create;// reuse the key/iv-pair, created abovecDec := TripleDESDec.CreateDecryptor(TripleDESEnc.Key,TripleDESEnc.IV);cMode := cMode.Read;cStream := cStream.CryptoStream(fStream,cDec,cMode);sReader := sReader.StreamReader(cStream);sData := sReader.ReadLine;sReader.Close;cStream.Close;fStream.Close;MESSAGE('Decrypted: ' + sData);

In the sample the same Key/IV pair was used for encryption and decryption, automatically created by method CreateEncryptor. To use your own Key/IV pair use method CreateEncryptor(byte[] key, byte[] iv) or set the Key and IV property of the encryptor/decryptor.

...CreateByteArray(cKey, 'abcdefghijklmnoqprstuvwx'); // length: 24CreateByteArray(initVector, 'abcdefgh'); // length: 8TripleDESEnc.Key(cKey);TripleDESEnc.IV(initVector);cEnc := TripleDESEnc.CreateEncryptor;...CreateByteArray(VAR ByteArray : DotNet "System.Array";InputString : Text)// local variables// idxInteger// ArrayTypeDotNetSystem.Type.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'// ByteValueByte// StrLengthIntegerStrLength := strlen(InputString);ArrayType := ArrayType.GetType('System.Byte',FALSE);ByteArray := ByteArray.CreateInstance(ArrayType,StrLength);FOR idx := 1 TO StrLength DO BEGIN  ByteValue := InputString[idx];  ByteArray.SetValue(ByteValue,idx - 1);END;

Links:
http://msdn.microsoft.com/de-de/library/z565ef9x(v=vs.110).aspx

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

Leave a comment

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

// local variablesADOConnection  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.ConnectionADOCommand  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.CommandADORecSet  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.RecordsetconnString  Text 1024activeConnection  VariantviewName  Text 100// the code// replace the server ip and the ext. databasename with your valuesconnString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600';CREATE(ADOConnection);ADOConnection.ConnectionString(connString);ADOConnection.Open;// convert ADO Connection to VariantactiveConnection := 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

Error, when creating an instance of a .net class

Leave a comment

Following error can occur, when creating an instance of a .net class in C/AL:

Reasons:

  • If the assembly is used on client side (property RunOnClient=True), then the assembly was not copied to the local Add-ins Folder of the RTC Client. To copy the assembly into this folder you need in most cases Admin rights. So, check that first.
  • If the assembly is used on server side (property RunOnClient=False), then there might be a version conflict between the assembly file located in the Add-ins Folder of the Nav Service and the used version in the C/AL Code. That can easy occur after installing Updates. So update the code and restart the nav service.
  • Missing System permission: That means, that the assembly was copied from the web or from a computer outside the local network to a target computer/server with windows server OS. Then, the assembly is automatically blocked by the system.

Executing external programs

7 Comments

Sometimes it’s needed to add functionality not delivered by NAV. One way is to use .Net assemblies or automations. Another scenario can be to run an external program in C/AL. One way is to use the quite old SHELL Command or class WshShell of the “Windows Scripting Host” (automation). In both cases, when running an external program, a security warning is displayed … not so good for automatic execution (batch).

Class System.Diagnostics.Process from .Net assembly System.dll
To avoid that and to have also a much prettier solution, use the class System.Diagnostics.Process from the .Net Framework (search for assembly System.dll). For usage in RTC Client set the property RunOnClient to true, for usage on the server only like job queues set the value to false.

Sample: creating a file using the dir command in a dos shell

// variable process | dotnet | System.Diagnostics.Process (assembly System.dll)
process := process.Process;
process.StartInfo.UseShellExecute := FALSE;
process.StartInfo.FileName := ‘cmd.exe’;
process.StartInfo.Arguments := ‘/c dir > c:\temp\dir.txt’;
process.StartInfo.CreateNoWindow := TRUE;
process.Start();
CLEAR(process);

sample: extract a rar file

process := process.Process;
process.StartInfo.UseShellExecute := FALSE;
process.StartInfo.FileName := ‘”C:\Program Files (x86)\WinRAR\unrar.exe”‘;
process.StartInfo.Arguments := ‘e -y -o+ c:\temp\archive.rar c:\temp’;
process.StartInfo.CreateNoWindow := TRUE;
process.Start();
CLEAR(process);

Stop pending processes:
Use methods WaitForExit or Kill to stop pending processes.

Links:
http://msdn.microsoft.com/en-us/library/system.diagnostics.process%28v=vs.110%29.aspx
http://msdn.microsoft.com/en-us/library/dd355282.aspx
http://msdn.microsoft.com/en-us/library/system.diagnostics.process.waitforexit(v=vs.110).aspx

cheers

Convert date string containing a month text value to a date value

1 Comment

Assuming you want to enter date values in the RTC client like 15-MAR-14. that won’t work. date values are only accepted with syntax like 15.03, 15.03.14, etc. But it can be that users prefer the above syntax. so then you can use a text field in your page and convert internally the value into a date value. Following function converts that kind of date string to a date value.

// local variables// dateString | Text// dateValue  | DatedateString := '15-MAR-14';dateValue := ConvertDateString(dateString);MESSAGE(FORMAT(dateValue,0,'<day,2>.<month,2>.<year4>'));ConvertDateString(dateString : Text[9]) : Date// local variables// dateString | Text// dayValue   | Integer// months | Text// monthValue | Integer// yearValue  | Integer// dateValue  | Date// mPos   | Integer// monthText  | Textif strpos(dateString,'-') > 0 then begin  dateString := CONVERTSTR(dateString,'-',',');  EVALUATE(dayValue,SELECTSTR(1,dateString));  months := 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec';  monthText := lowercase(SELECTSTR(2,dateString));  if strlen(monthText) <> 3 thenerror('Invalid month expression: ' + monthText);  mPos := STRPOS(months,monthText);  if mPos >0 then beginmonthValue := (mPos + 3) / 4;EVALUATE(yearValue,SELECTSTR(3,dateString));IF yearValue > 14 THEN  yearValue := yearValue + 1900ELSE  yearValue := yearValue + 2000;dateValue := DMY2DATE(dayValue,monthValue,yearValue);exit(dateValue);  end elseerror('No valid month given: ' + monthText);end;exit(0D);

to use the standard functionality of date validation and conversion it’s needed to change function MakeDateText in codeunit 1:

MakeDateText(VAR DateText : Text[250]) : Integer// additional local variables// mPos | Integer// monthText | Text | 10// monthValue | Integer// Text Constant: Text022 | jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,decPosition := 1;Length := STRLEN(DateText);ReadCharacter(' ',DateText,Position,Length); // begin changesIF STRPOS(DateText,'-') > 0 THEN BEGIN  DateText := CONVERTSTR(DateText,'-',',');  monthText := lowercase(SELECTSTR(2,DateText));  if strlen(monthText) <> 3 thenerror('Invalid month expression: ' + monthText);  mPos := STRPOS(Text022,monthText);  IF (mPos > 0) THEN BEGINmonthValue := (mPos + 3) / 4;// optional: add a leading 0, if needed; simple use format(monthValue) should also workmonthText := PADSTR('',2 - STRLEN(FORMAT(monthValue)),'0') + FORMAT(monthValue);DateText := SELECTSTR(1,DateText) + '-' + monthText + '-' + SELECTSTR(3,DateText);  END ELSEerror('No valid month given: ' + monthText);END;// end changes IF NOT FindText(PartOfText,DateText,Position,Length) THEN...

cheers

Process decimal values with different regional settings

Leave a comment

When importing/processing decimal values, the decimal separator is . or , according to the regional setting. Assuming you want to import decimal values with US Standard regional setting, then the separator is a point (.).

To process such decimal values, you can work with the NAV Command GLOBALLANGUAGE. That is the in NAV at runtime used language.

impValue := 0.25;impValStr := format(impValue);// check for GER and FR languageif (GLOBALLANGUAGE = 1031) OR (GLOBALLANGUAGE = 1036) then  impValStr := Convertstr(impValStr,'.'','); // replaces the point by a comma

Additional you can use the Command WINDOWSLANGUAGE. This command returns the regional system setting (the language of the OS). In common with that command you can handle every case.

for language codes follow
http://technet.microsoft.com/en-us/library/dd346950.aspx

links:
http://msdn.microsoft.com/en-us/library/dd338772.aspx
http://msdn.microsoft.com/en-us/library/dd301095.aspx

You cannot create an Automation object ‘name of autom.’ on Microsoft Dynamics NAV Server. You must create it on a client computer.

2 Comments

This is an error message, when trying to create an instance of an automation on a nav server vs. >= 2013. It’s only possible to create the automation instance as client instance. Creating automation instances on nav server is allowed till nav 2009.

solution:
use create(<name_of_autom.>,false,true) instead of create(<name_of_autom.>).

remarks:
you need a installed version of the automation on each client computer.

links:
create fct.: http://msdn.microsoft.com/en-us/library/dd355255(v=nav.70).aspx
Usage of automations in nav 2013: http://msdn.microsoft.com/en-us/library/ee909565(v=nav.70).aspx
Usage of automations in nav 2009: http://msdn.microsoft.com/en-us/library/ee909565(v=nav.60).aspx

Sql Statements in C/AL – Part 1 : Select

2 Comments

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

Date Functions

1 Comment

Date functions are quite often used and needed. Here are some samples:

  1. Calculate the 1st of current month:
    EndDate := CALCDATE('-CM');
  2. Calculate last day of current month:
    EndDate := CALCDATE('CM');
  3. Calculate the end date of a defined period (e.g.3 months) starting with the 1st of the current month; get the last day of the end month:
    EndDate := CALCDATE('-CM+3M-1D');EndDate := CALCDATE('CM+2M');
  4. Get start and end date of a period with given day values and variable month value:
    startDay, endDay, month, prevMonth, currYear | integer valuesmonth := 10  // sample valueif month > 1 then prevMonth := month - 1 else prevMonth := 12;currYear := DATE2DMY(TODAY,3);  // get current yearstartDate := DMY2DATE(startDay, month - 1, currYear);endDate := DMY2DATE(endDay, month, currYear);

Be aware that the calc formulars are language specific. For german language e.g. use LM instead of CM, 1T instead of 1D.