Loading a client file into BLOB with RTC Client

For some time have been looking for a solution on how to upload a file into BLOB with RTC Client.  The built in functions, UPLOAD and UPLOADINTOSTREAM both force an Open Dialog unless you first copy the file to a temporary path.  I already had the file name and wanted to skip that part.

I always stopped on the fact that I was unable to move binary data with code from the client layer to the server layer.  Then today, I finally got an idea on how to solve this.  I convert the client file to a base64 string, transfer that string to the server layer and save as a file.  Then I create a binary server file based on the base64 server file.  That file is identical to the client file and ready to be imported into BLOB on the server side.
[code htmlscript=”false”]IF ISSERVICETIER THEN BEGIN
Document.ADDTEXT(
ClientConvert.ToBase64String(
ClientFile.ReadAllBytes(ImageFileName)));
ServerBase64FileName := ThreeTireMgt.ServerTempFileName(”,”);
ServerFileStream.WRITEMODE(TRUE);
ServerFileStream.CREATE(ServerBase64FileName);
ServerFileStream.CREATEOUTSTREAM(OutStr);
Document.WRITE(OutStr);
ServerFileStream.CLOSE;

ServerDocumentFileName := ThreeTireMgt.ServerTempFileName(”,”);
ServerDocumentFile.WriteAllBytes(
ServerDocumentFileName,
ServerConvert.FromBase64String(
ServerBase64File.ReadAllText(ServerBase64FileName)));

ServerFileStream.OPEN(ServerDocumentFileName);
ServerFileStream.CREATEINSTREAM(InStr);
Image.CREATEOUTSTREAM(OutStr);
COPYSTREAM(OutStr,InStr);
ServerFileStream.CLOSE;
ServerBase64File.Delete(ServerBase64FileName);
ServerDocumentFile.Delete(ServerDocumentFileName);
END ELSE BEGIN
Image.IMPORT(ImageFileName,FALSE);
END;[/code]
This should support files upto 1.5GB in size.

ImageTest

Blocking Log

Today I needed to see who was blocking another user and why.  I created a few objects to monitor the Session table and log the information.

I use ADO to connect to the database server to get the current SQL statement for each session to be able to see what is beeing locked.

Objects attached.

Blocking Log

My asp.net website using NAV Web Service

I am building an asp.net website that communicates with NAV via Web Services.  One of the issues I had to solve was the authentication between the web and the NAV Web Services.

You can ether use NTLM authentication or the current user.  If you will be using NTLM you will need code similar to this in you website.
[code htmlscript=”false” land=”vb”]Dim NAVPunch1 As New WebService.NAVPunch
Dim User As New System.Net.NetworkCredential
User.Domain = "Dynamics.is"
User.UserName = "Gunnar"
User.Password = "<password>"
NAVPunch1.Credentials = User
NAVPunch1.Url = "http://Dynamics.is:7047/DynamicsNAV/WS/" & _
"Dynamics/Codeunit/NAVPunch"[/code]
This also means that you have to store the username, domain and password in you web site code. You will also need to enable NTLM authentication in your CustomSettings.xml
[code htmlscript=”false” lang=”vb”]<add key="WebServicesUseNTLMAuthentication" value="true"></add>[/code]
The other way is to use the credentials of the user running the web. In that case the code would be similar to this:
[code htmlscript=”false” lang=”vb”]NAVPunch1.UseDefaultCredentials = True
NAVPunch1.Url = "http://Dynamics.is:7047/DynamicsNAV/WS/" & _
"Dynamics/Codeunit/NAVPunch"[/code]
And no changes to CustomSettings.xml are required.  The authentication will be handled with IIS.  You will need to go into Internet Information Services (IIS) Manager.  Go into Application Pools and add a new application pool

Select a name that fits you web site and then go to Advanced Settings…

and update the Idendity.

Then go and select this Application Pool for the web site.

The final step is to make sure that the user you select in your code or in the application pool has access to NAV Web Services.  That is done with the standard authentication methods in Dynamics NAV.

WSDL Code Generator

On several occasions I have needed to create a code to communicate with Soap Web Services. The manual labor in creating the functions and XML Ports is something that I would like to be rid of. On that node I created a Batch that does most of the job for me.

Execute this and you will be asked to save the “Web Service Objects.txt” file to your computer and from there you will be able to import the file and continue the work.

You will get XMP Ports for every method, both the request and response.  You will also get a Codeunit with a function for every method and the necessary functions to handle the web service communication.

What is left is for you to connect your data to the functions and the XML Ports.

This is not fully tested, so any updates would be appreciated.

Import WSDL (Updated 2012-12-08)

Kill Idle Connections

To save concurrent uses in NAV it might be a good idea to kill idle connections automatically.

This can be done by looping through the Session Virtual Table and check the Idle Time field. I like to add the field “Maximum Idle Time” to the User Setup table and compare that field to the Idle Time in the Session table before deciding to kill the session.

The use running this batch must have permission to kill sessions on the SQL Server.
[code htmlscript=”false”]IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
‘Driver={SQL Server Native Client 10.0};’ +
‘Server=<ServerName>;’ +
‘Database=<DatabaseName>;’ +
‘Trusted_Connection=yes;’);
ADOConnection.CommandTimeout(0);

Session – OnAfterGetRecord()
IF "Application Name" <>
‘Microsoft Dynamics NAV Classic client’
THEN
CurrReport.SKIP;
BackSlashPos := STRPOS("User ID",’\’);
IF NOT
UserSetup.GET(
UPPERCASE(COPYSTR("User ID",BackSlashPos + 1)))
THEN
CurrReport.SKIP;
IF FORMAT(UserSetup."Maximum Idle Time") = ” THEN
CurrReport.SKIP;
Idle := "Idle Time";
MaxIdle := (UserSetup."Maximum Idle Time" – 000000T);
IF Idle < MaxIdle THEN
CurrReport.SKIP;

ADOConnection.Execute(STRSUBSTNO(‘KILL %1’,"Connection ID"));

Session – OnPostDataItem()
ADOConnection.Close;
CLEAR(ADOConnection);[/code]

Name DataType Subtype Length
UserSetup Record User Setup
ADOConnection Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Connection
MaxIdle Decimal
Idle Decimal
BackSlashPos Integer

This can be added as a Job Queue Batch.

See here to check for required permission to use the kill statement.

Import a text to RTC Note in the Record Link Table

In the process of importing data from MSSQL into NAV I needed to import a comment text. I wanted to import the text into the Note BLOB field in the Record Link table. It was not obvious how to do this but after some work I successfully imported all my comments.

I first start to create the record link with the following code
[code htmlscript=”false”]IF Note <> ” THEN BEGIN
LinkID := DestTbl.ADDLINK(”,DestTbl.TABLECAPTION);
RecordLink.GET(LinkID);
RecordLink.Type := RecordLink.Type::Note;
RecordLink.Note.CREATEOUTSTREAM(OutStr);
OutStr.WRITE(Text2NoteText(Note));
RecordLink.MODIFY;
END;[/code]
After my first round I found out that I needed to include the length of the Note as a Char in the beginning of the note. Then I noticed that my Icelandic characters where not correctly imported and after some study found out that the content of the BLOB needs to be UFT-8 encoded. This led me to create the Text2NoteText function.
[code htmlscript=”false”]Text2NoteText(NoteToImport : Text[1024]) NAVNode : Text[1024]
NAVNode := UTF8Encode(NoteToImport);
NoteLength := STRLEN(NAVNode);
IF NoteLength <= 255 THEN BEGIN
Char1 := NoteLength;
NAVNode := FORMAT(Char1) + NAVNode;
Char2 := 1;
END ELSE BEGIN
Char1 := 128 + (NoteLength – 256) MOD 128;
Char2 := 2 + (NoteLength – 256) DIV 128;
NAVNode := FORMAT(Char1) + FORMAT(Char2) + NAVNode;
END;[/code]
And the UFT8Encode function
[code htmlscript=”false”]UTF8Encode(String2Encode : Text[1024]) EncodedString : Text[1024]
MakeVars;
String2Encode := CONVERTSTR(String2Encode,NavStr,AsciiStr);
FOR Index := 1 TO STRLEN(String2Encode) DO BEGIN
Char2 := String2Encode[Index];
IF Char2 <= 127 THEN
EncodedString := EncodedString + FORMAT(Char2)
ELSE IF Char2 <= 192 THEN BEGIN
Char1 := 194;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END ELSE BEGIN
Char1 := 195;
Char2 := Char2 – 64;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END;
END;[/code]
The MakeVars function creates the NavStr and AsciiStr that I need for the conversion. This is similar to function InitCharTables in Codeunit 424 except I am not using Excel to convert the strings.

Attached is a codeunit with these functions.

RTCNoteTool

 

Importing data from MS SQL via CSV File

I have a database with a number of tables that I need to import into NAV in order to create a NAV solution to replace the old outdated solution.

The first step was to build identical tables in NAV.  In my case this was done manually, but there should not be difficult to convert a “CREATE TABLE” command into a NAV object text file.

I then got the name of all the tables in the database with

and copied the result to Excel. In Excel I first created the select statement in column C

and then created the sqlcmd function in column G

Then I fill down column C and column G and have all the lines for my command file ready. Note that I use “;” as a decimal separator.

I create a folder for the data and within the folder I create a file called ExtractData.cmd and copy all the lines from column G to that file. Then this command file is executed and that folder fills up with CSV files for each table.

The final step is to import the data with the attached NAV Report.

MSSQL-CSV Import

 

 

Web Service that deliveres BLOB data

I wanted to be able to use the Record Link table in NAV to link to my attachments. Since that part of NAV is not customizable every attachment that I link to NAV needs to be accessible via URL.

So, when I import or scan a file into NAV it can be stored in a BLOB or I can store it in a customized external database. After storing the file I create a URL link to that file and insert that as a new link to any record in NAV. To complete this task I created a NAV Web Service that delivers the file as a base64 string. Here is the part of the code that encodes the BLOB stream to a BigText variable.
[code htmlscript=”false”]TempFile.CREATETEMPFILE;
TempFileName := TempFile.NAME;
TempFile.CLOSE;
TempFile.TEXTMODE(FALSE);
TempFile.CREATE(TempFileName);
TempFile.CREATEOUTSTREAM(OutStr);
DocumentStore.Blob.CREATEINSTREAM(InStr);
COPYSTREAM(OutStr,InStr);
TempFile.CLOSE;

CREATE(XMLDoc);
CREATE(ADOStream);
XMLNode := XMLDoc.createNode(‘element’, ‘ImageFile’, ‘SKYRR Signing’);
XMLNode.dataType := ‘bin.base64’;

ADOStream.Type := 1;
ADOStream.Open();
ADOStream.LoadFromFile(TempFileName);
XMLNode.nodeTypedValue := ADOStream.Read();
ADOStream.Close();

Document.ADDTEXT(XMLNode.text);

CLEAR(ADOStream);
CLEAR(XMLNode);
CLEAR(XMLDoc);
ERASE(TempFileName);[/code]
Then I created a aps.net website that can get the document both from this NAV Web Service and also from the customized database all based on the parameters passed with the URL.

Attached is a part of the NAV code and the website required to deliver the attachment.

WebSite

Attachment NAV Source Code

 

NAV TechDays 2011

Yesterday I flew from Keflavik, Iceland to Amsterdam and then traveled by train to Antwerp.  Here I am @NAV TechDays 2011

What

NAV TechDays is the name of a new conference, organized by mibuso.com.
The goal of this conference is to offer 2 days full of – technical only and highly relevant – sessions, related to Microsoft Dynamics™ NAV.

 

Session topics:

  • Administrating NAV ‘7.0’ with Windows PowerShell 2.0
  • High-Quality Test Automation for NAV Applications
  • Developer Tools
  • Form Transformation
  • Integration (CRM connector, Webservices, Windows Phone 7, …)
  • Mergetool
  • .NET and NAV Interop
  • Partner-ready Software: how to build software for others to maintain
  • Reporting story in NAV ‘7.0’
  • RoleTailored Client Add-ins, Visualization & Charting
  • Tools & tricks that make NAV development easier and more organized
  • Understanding Keys & Indexes with Dynamics NAV & SQL Server

E-Mailing PDF Report with Job Queue

One of my clients asked for a automated report delivery every morning.  Since Job Queue was already running I decided to create a way to have Job Queue print the report to PDF and email it as an attachment.

This example uses the E-Mailer Report no. 50003 to send Report No. 6 to my email address.

First I had to make some changes to codeunit 449, Job Queue Start Codeunit, the OnRun trigger.  I created a local variable with the name JobQueueEntry as a Record 472 and then added to the code.
[code htmlscript=”false”]CASE "Object Type to Run" OF
"Object Type to Run"::Codeunit:
CODEUNIT.RUN("Object ID to Run",Rec);
"Object Type to Run"::Report:
//#01
IF "Parameter String" <> ” THEN BEGIN
JobQueueEntry := Rec;
JobQueueEntry.SETRECFILTER;
REPORT.RUN("Object ID to Run",FALSE,FALSE,JobQueueEntry);
END ELSE
//#01
REPORT.RUN("Object ID to Run",FALSE);
END;[/code]
Attached is report 50003 that does the job.

Any Report E-Mailer