Art Of Creation – Dynamics AX Blog

Linked server sql statements

July 23, 2010 at 18:29 (no comments)

Hi all, hope you are well.

Some time ago, I talked about executing direct sql statements, and now I want to share some sql statements that I used to manage linked server connections.

What follows are 4 sql statements that allow you to add en remove linked servers on a database at runtime.

Some assumptions:
- There is a str variable named “query” that will contain the query
- there is a parm method on the class that return the sql server (“server” or “server\instance”)
- there is a parm method that returns a username
- there is a parm method that returns a password

Check if linked server exist
First check if the linked server doesn’t exist yet, or you will get an error when you try to add one that already exists.

query = strfmt("select top 1 * from sys.servers where name = '%1'", this.parmServer());

Add linked server
When the linked server doesn’t exist, add it.

query = strfmt("EXEC sp_addLinkedServer @server = '%1', @srvproduct=N'SQL Server'", this.parmServer());

Add linked server login
Optionally, you can add a login that will be used to connect to the linked server.

query = strfmt("sp_addlinkedsrvlogin @rmtsrvname = '%1' ,@useself = FALSE, @locallogin = NULL, @rmtuser = '%2', @rmtpassword = '%3'",
this.parmServer(),
this.parmUsername(),
this.parmPassword());

Remove linked server
Optionally, you can remove the linked server.

query = strfmt("EXEC sp_dropserver '%1', 'droplogins'", this.parmServer());
Dynamics AX 2009 RU5 backwards compatibility problem

July 22, 2010 at 14:43 (3 comments)

Hi all!

There seems to be a problem with the recently released RU5 for Dynamics AX 2009 SP1.
Dynamics AX clients are supposed to be backwards compatible, so you can always use the latest client, even if you want to connect to an application with a lower version (e.g. AX 2009 RU4 client to AX 2009 RU3 application).

But when I tried to connect to an AX 2009 RU3 application (5.0.1500.1313) after I updated my client to RU5 (5.0.1500.2985), it didn’t work.
Dynamics AX only shows the first record in the forms when you use that combination, as you can see in the screenshot below.

Roll up 5

When you connect using a RU4 client (or RU3), there is no problem at all.

Roll up 4

You can uninstall a hotfix rollup from the Add/Remove software screen in the windows control panel if you want to.

I prefer to duplicate the client folder each time I upgrade it so I have all client versions available.
Just fire up bin\ax32.exe for the version you want and you good to go :-).
Forms Tutorial_Resources and SysImageResources

June 28, 2010 at 19:10 (3 comments)

On multiple occasions, you can add an icon to an object. Like for example on menus. You can add an image by setting the NormalImage property to a path, or you can use the NormalResource property.

The only problem with the NormalResource property is that it asks for an integer. But how do you know what integer the icon has that you want to use?

You can use this form:

Depending on what AX version you are using, the name of the form is different:
Dynamics AX 4: form Tutorial_Resources
Dynamics AX 2009: form SysImageResources

For some reason, Microsoft changed the name of that form in AX 2009, and because I started working with AX version 4.0, I always forget the name of that new form :-).

Update: in AX 2009, you can also open this form in the AX menu; click Tools – Development Tools – Embedded resources.
Executing direct SQL statements

June 21, 2010 at 18:35 (no comments)

Hi everyone,

Today I want to talk about executing SQL statements in X++ on both the AX database and external databases. This is something probably every AX developer will have to do at some point.

You’ll want to do this for many reasons; to execute stored procedures, to improve performance, to get data from an external database, and so on.

I will provide samples for two classes:
- Connection (Execute SQL statement on current AX database)
- ODBCConnection (Execute SQL statement on external database)

I will not cover the ADO connection (CCADOConnection class), because it doesn’t work when you run it on server (or in batch), and I don’t like that. If you do, try to convince me ;-).

Executing direct SQL on the current AX database

When you execute a SQL statement, there are two options:
- either you did a select and you expect a result to be returned
- or you did insert/update/delete and you don’t expect a result.

The first sample is for a SQL statement that returns a result:

public static server void main(Args _args)
{
Connection connection;
Statement statement;
str query;
Resultset resultSet;
;

// create connection object
connection = new Connection();

// create statement
statement = connection.createStatement();

// Set the SQL statement
query = 'select name from CustTable';

// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();

// when the query returns result,
// loop all results for processing
//BP Deviation documented
resultSet = statement.executeQuery(query);

while(resultSet.next())
{
// do something with the result
info(resultSet.getString(1));
}

// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}

Note: this is a main method, put it in a class. Also note that it has to run on server.

Now if you do an update/delete/insert, you will want to do something like this:

public static server void main(Args _args)
{
Connection connection;
Statement statement;
str query;
;

// create connection object
connection = new Connection();

// create statement
statement = connection.createStatement();

// Set the SQL statement
query = "insert into CustTable (AccountNum, Name, RecId) values ('demo', 'demo', 2)";

// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();

//BP Deviation documented
statement.executeUpdate(query);

// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}

You can find more info about the executeQuery() and executeUpdate() methods on msdn:
Statement Class

Executing direct SQL on an external database using ODBC

Again, we have to differentiate between queries that return a result and those that don’t.

The following code sample retrieves records from an external database and processes the result:

public static server void main(Args _args)
{
Statement statement;
str query;
Resultset resultSet;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
;

loginProperty = new LoginProperty();
loginProperty.setDSN('YOURDSN');

odbcConnection = new OdbcConnection(loginProperty);

// Create new Statement instance
statement =odbcConnection.CreateStatement();

// Set the SQL statement
query = 'select name from CustTable';

// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();

// when the query returns result,
// loop all results for processing by handler
//BP Deviation documented
resultSet = statement.executeQuery(query);

while(resultSet.next())
{
// do something with the result
info(resultSet.getString(1));
}

// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}

As you can see, the code is pretty similar. The main difference is that we are using ODBC classes, including the LoginProperty class.

In this example, I use a DSN (Data Source Name) that I configured on the AOS server. The DSN contains a reference to the server and database you want to connect to, and also what user credentials should be used to connect to the database. This is a lot safer than storing them in AX.
If you don’t know how to create a DSN, there are plenty of tutorials on the web.

To update/delete/update, the code is more or less the same:

public static server void main(Args _args)
{
Statement statement;
str query;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
;

loginProperty = new LoginProperty();
loginProperty.setDSN('YOURDSN');

odbcConnection = new OdbcConnection(loginProperty);

// Create new Statement instance
statement =odbcConnection.CreateStatement();

// Set the SQL statement
query = "insert into CustTable (AccountNum, Name, RecId) values ('demo', 'demo', 2)";

// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();

// when the query returns result,
// loop all results for processing by handler
//BP Deviation documented
statement.executeUpdate(query);

// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}

If you feel that something is missing in these examples, just ask.
Now part of the Dynamics AX Community

June 2, 2010 at 22:52 (5 comments)

Hi everyone,

Just to announce that this blog is now part of the Dynamics AX Community.

You can read all about it in this warm welcome message on the Microsoft Dynamics AX Community Team blog.

While you are at it, create a profile (if you don’t already have one), because with the newsgroups closing any time now, the Dynamics AX Community is the place to be for all your community needs.
Normalization dilemma

May 29, 2010 at 00:42 (8 comments)

Hi all!

I have a dilemma, and would like your opinions.
The situation is that I want to add fields to an existing table (say InventTable)

What would you prefer:

So the question is, should I add new fields to the table (option one), or should I create a new table with the fields and link to it. (option 2)

Normally, option 1 is the standard way of adding extra fields to a table, but option 2 has a few advantages:
- You don’t need to modify the existing table
- You’ll have less trouble with these fields when you have to upgrade AX
- It will be easier to deploy your modifications in an AX environment that already contains modifications to the table
There are also a few disadvantages I can think of:
- You have to join 2 tables to get the data from the new fields
- You can’t add the new fields to fields groups on the main table (InventTable in this example)
- On forms, you will have to add a new data source, or add display fields
I would certainly go for option 1 when doing customizations for a customer, but would consider option 2 for product development. I’m in doubt. How about you?
Creating a class, the best practice way

May 18, 2010 at 13:40 (no comments)

Hi all!

Some time ago, I talked about the ‘construct’ pattern that is use a lot in AX. Although I use it often, it’s not perfect because it violates some best practices. (Also, you could end up with a large class hierarchy of classes that extend each other, but I’ll discuss that in an other post.)

First off, know that you can change the compiler level and the best practices the compiler checks.
To do this, compile something so the compiler output window pops up. Then click the setup button, and adjust the settings.
I used compiler level 4 and enabled all BP checks for this example.

So, let create a simple class. This class will simply print information of an InventTable record to the screen.

1. ClassDeclaration
When we think about this class a bit, we immediately realize that we need to declare an InventTable record as a member of out class:

/// <summary>
/// Class written to test construct and new... patterns
/// </summary>
/// <remarks>
/// Delete this class when proven a point :)
/// </remarks>
class KLFORTestBP
{
InventTable inventTable;
}

Also note that I added documentation to this method. I will do so for other methods too. When you don’t, you’ll get a BP deviation warning.

2. Parm method
One of the things that are important (or at least I find important), is using parm methods for your class variables. So, let’s create one:

// use parm methods to access class variables
public InventTable parmInventTable(InventTable _inventTable = inventTable)
{
;
inventTable = _inventTable;

return inventTable;
}

3. New method
You should never write code in the new method, or add parameters to it. On the contrary, you should always set this method to protected, and use a static construct() or a static new…() method instead.

// a new method should be protected
// use static new... or construct instead
protected void new()
{
}

4. Run method
The run method will be our method that contains our logic. Here, we print info to the screen

/// <summary>
/// Executes logic on the parameters of this class
/// </summary>
/// <remarks>
/// Just for demo purposes
/// </remarks>
public void run()
{
;
info(this.parmInventTable().NameAlias);
}

Note that we are using the parm method instead of directly accessing the variable.

5. Construct method
Because the new method is now protected, we will need a contruct method to be able to create an instance of this class.

private static KLFORTestBP construct()
{
;
return new KLFORTestBP();
}

When a class doesn’t need parameters, you can leave out the ‘private’, but now, it is private because we plan on creating a new…() method that contains parameters. A construct method shouldn’t contain parameters, and should only be used to create a new instance of your class. It is also the only place in your application where you can call the new() method of your class.

6. New…() method
As said earlier, because we need a parameter (an InventTable record), we use a new…() instead of the construct method to create an instance of our class. I will name it newFromInventTable() based on the parameter it has.
In this method, we call the contruct() method to create an instance (instead of new()), set the parm method from the parameter and return the object.

/// <summary>
/// Creates a new KLFORTestBP object from an InventTable record
/// </summary>
/// <param name="_inventTable">
/// The InventTable used to create the object
/// </param>
/// <returns>
/// New instance of KLFORTestBP with the InventTable parameter set
/// </returns>
/// <remarks>
/// Use a new... method instead of the constuct method because it takes parameters/// A
/// </remarks>
public static KLFORTestBP newFromInventTable(InventTable _inventTable)
{
KLFORTestBP kLFORTestBP;
;

kLFORTestBP = KLFORTestBP::construct();
kLFORTestBP.parmInventTable(_inventTable);

return kLFORTestBP;
}

You could also create an init() method that initializes you class (we don’t need initialization now). The init() method should return a boolean if initialization can go wrong, so you can throw an error.
It would look like this:

public static KLFORTestBP newFromInventTable(InventTable _inventTable)
{
KLFORTestBP kLFORTestBP;
;

kLFORTestBP = KLFORTestBP::construct();
kLFORTestBP.parmInventTable(_inventTable);

if(!kLFORTestBP.init())
{
throw error("Item record has no data"); // todo: create label
}

return kLFORTestBP;
}

Remember to create a label for the text string, or you will get a best practice error.

7. Main method
Now, all we need more is a main method, so this class is runable (by pressing F5 or as a menu item).

server public static void main(Args _args)
{
KLFORTestBP kLFORTestBP;
InventTable inventTable;
;

if(_args && _args.dataset() == tablenum(InventTable))
{
// a button was pressed on a InventTable datasource
inventTable = _args.record();
}
else
{
// for demo, simple select the first one
select firstonly inventTable;
}

// display item information
kLFORTestBP = KLFORTestBP::newFromInventTable(inventTable);
kLFORTestBP.run();
}

Normally, you will get the record from the args object, but for demo purposes, I added the ‘select firstonly’ so you can run the class by pressing F5. As you can see, the new…() method is used to create an instance of the class, and then the run method is called.

There you go, you now have a best practice deviation free class!

8. Using the class
Using the class as a menu item or in code couldn’t be easier.

You can drag/drop the class to the action menu item node in the AOT to create a menu item, and you will be able to use it on any form with the inventTable datasource.

From code, you can simple write this line (assuming there is a variable called inventTable):

KLFORTestBP::newFromInventTable(inventTable).run();

Conclusion:
When writing code that doesn’t contain any best practice deviations (especially when checking them all on compiler level 4), you quickly end up with a lot of methods in your class and wondering if the effort is worth it. It can be a bit painful to get your code best practice deviation free.

However, you’ll also realise that following the best practices has its advantages. Your code will be cleaner, easier to understand, easier to debug because of the parm, constuct and run methods, less subject to change, contain less errors and your class will be used in the way you intended when you wrote it.

More info on MSDN:
Best practices for constructors
AllowDuplicates Yes: Illegal property value

May 17, 2010 at 18:06 (no comments)

When changing the property AllowDuplicates on a table index from No to Yes, the following error can be thrown at you:

Illegal property Value

This is because on your table, the property PrimaryIndex is set to the index you are trying to change.
Microsoft closing AX newsgroups

May 10, 2010 at 10:39 (2 comments)

Microsoft will begin closing its newsgroups, including the Dynamics AX newsgroups, as announced here:
http://www.microsoft.com/communities/newsgroups/default.mspx

It’s a shame, because I visit the AX development group using the web based news reader daily, and I will miss it.

Where will we go now? To the Dynamics Forums?
Split big text files in smaller ones in X++

April 21, 2010 at 18:16 (no comments)

Below is a job I found useful many times, so don’t want to loose it.
Nothing special, but maybe you’ll find it useful too.

It splits an input text file into smaller files.
As there is one comment line for every line of code (almost), I’m sure you’ll figure it out :-).

static void KlForSplitFile(Args _args)
{
#File
AsciiIO inFile, outFile;
container rec;
int cnt = 0, fileNum = 0;
FileName outFileName;
FileIOPermission filePermissionOrig, outFilePermission;
Set permissionSet = new set(types::Class);

// settings begin
// input file
str _inFileName = @"C:\temp\testsplit\testsplit.csv";
// max number of lines
int _maxrec = 20000;
//settings end
;

//
// first loop to create the permission set
//

// assert permission for read
new FileIOPermission(_inFileName, #io_read).assert();

// create new asciiIo for the file we are reading in
inFile = new AsciiIo(_inFileName,#io_read);

// read file to check how many files will be created
while(inFile.read())
{
if(cnt == _maxrec || cnt == 0)
{
// 1 more file
filenum++;
// create filename
outFileName = strfmt('%1_%2.csv',
substr(_inFileName,1,strlen(_inFileName) - 4), fileNum);
// create permission
outFilePermission = new FileIOPermission(outFileName,#io_write);
// add permission to set
permissionset.add(outFilePermission);
// reset line counter
cnt = 0;
}
// 1 more line
cnt++;
}

// also add fileIn permission to set
filePermissionOrig = new FileIOPermission(_inFileName, #io_read);
permissionset.add(filePermissionOrig);
// revert permission assertion
CodeAccessPermission::revertAssert();

// assert permissions for set
CodeAccessPermission::assertMultiple(permissionset);

//
// loop file again and split
//

// reset counters
fileNum = 0;
cnt = 0;

// create new asciiIo for the file we are reading in
inFile = new AsciiIo(_inFileName,#io_read);

// read file to check how many files will be created
// read ahead
rec = inFile.read();
while(rec)
{
if(cnt == _maxrec || cnt == 0)
{
// 1 more file
filenum++;
// create filename
outFileName = strfmt('%1_%2.csv',
substr(_inFileName,1,strlen(_inFileName) - 4), fileNum);
outFile = new AsciiIO(outFileName,#io_write);
// reset line counter
cnt = 0;
}
// 1 more line
cnt++;
// write to split file
outFile.writeExp(rec);
// read next line
rec = inFile.read();
}

// revert permission assertion
CodeAccessPermission::revertAssert();

info('done');
}
AIF: ‘EndElement’ is an invalid XmlNodeType

April 20, 2010 at 11:31 (no comments)

Hi all.

I got this error when developing a C# .NET application that consumed a AIF web service, and couldn’t find a solution online so let me share mine :-).

The problem was that I initialised my queryCriteria wrong:

CriteriaElement ace = new CriteriaElement();
ace.FieldName = "AccountNum";
ace.Value1 = "000001";

I forgot the specify the datasourcename and operation:

CriteriaElement ace = new CriteriaElement();
ace.FieldName = "AccountNum";
ace.Value1 = "000001";
ace.DataSourceName = "VendTable";
ace.Operator = Operator.Equal;
TechDays 2010 Antwerp – Videos

April 19, 2010 at 17:27 (no comments)

Hi all!

I attended TechDays 2010 in Antwerp this month and you could read about it on my blog, and apparently, videos of all sessions are available of channel 9.

Check them out here:
http://channel9.msdn.com/tags/TechDaysBelux/

Really interesting stuff there, so have fun.

Update:
You can also find the videos (and PowerPoint presentations) on the TechDays website.
AIF: HTTP Error 404.3 – Not Found

at 16:34 (no comments)

After installing an AIF web server and deploying services on it, I got an error when I tried to browse my web service http://myhost:82/MicrosoftDynamicsAXAif50/itemservice.svc.
The error was:

HTTP Error 404.3 – Not Found

When I tried to add a web reference the svc in visual studio .NET, I got a similar error:

HTTP Error 404 – Not Found

I solved it by using the method described on the AIF blog:
http://blogs.msdn.com/aif/archive/2008/12/12/http-error-404-3-is-received-when-calling-a-web-service.aspx

Basically, execute the following command on your web server:

ServiceModelReg.exe -i -x

After that, everything worked fine.
X++ editor bug?

April 14, 2010 at 19:02 (9 comments)

Hi All!

There’s this little bug in the X++ editor that I find fairy annoying.
When you paste text at the beginning of a line, the first character get overwritten.

I demonstrate it in the next video:

Is it just me?
AIF: The class method for the specified action %1 could not be found

at 18:13 (no comments)

When you get this error:

The class method for the specified action %1 could not be found

It probably means you have regenerated you classes using the AIF document wizard, or that you have imported the AIF classes without id’s.

You can use this job to fix the class id’s in the AIFAction table:

static void KlForFixAIFActions(Args _args)
{
AIFAction aIFAction;
ClassId classId;

// inner method:
// spit string at delimeter, keep left substring
str lSplit( str _s, str _delimeter )
{
str s = "";
int pos;
int len = strlen( _s );

pos = strfind( _s, _delimeter, 0, len );
s = strdel( _s,pos,len-pos+1);

return s;
}
;

// fix all actions
while select aIFAction
{
// get 'real' classid
classId = classname2id(lSplit(aIFAction.ActionId, '.'));

// check if classid and classname match
if(aIFAction.ClassId != classId)
{
if(classId == 0)
{
// class does not exist
warning(strfmt("Class %1 does not exist", lSplit(aIFAction.ActionId, '.')));
}
else
{
// doesn't match
// update classid
ttsbegin;
aIFAction.selectForUpdate(true);
aIFAction.ClassId = classId;
aIFAction.update();

info(strfmt('Action %1 updated', aIFAction.ActionId));
ttscommit;
}
}
}

info('done');
}
Next Page »