« »

search

Art Of Creation – Dynamics AX Blog

 The everyday life of a Dynamics AX developer

  • Pages

    • AX links
    • Contact
  • Tags

    2009 2012 AIF AOS Application Batch Best practices bookmarks CLR Compiler Configuration Database Datasource Data Types Debug Decisions Dynamics AX Editorscripts Error Event Log Events Forms Fun Google Inheritance Job link Nice-to-haves Odd code Patterns Performance RPC security SQL String synchronize SysOperation System.Diagnostics System.IO TechDays Technical conference Tools Try Catch WinAPI X++
  • Recent Comments

    • Klaas Deforche on AX Technical Conference 2011: Session Recordings (Now available)
    • Nick Peterson on AX Technical Conference 2011: Session Recordings (Now available)
    • Call a BOF service with a caller record | UnitedTechnos on AX2012: SysOperation part 2: SysOperationServiceController
    • Klaas Deforche on AX Technical Conference 2011: Session Recordings (Now available)
    • Robert on AX Technical Conference 2011: Session Recordings (Now available)
    • How to handle errors in a transaction scope | Sharp Dynamics Magazine on Try Catch and transactions
    • Klaas Deforche on AX2012: SysOperation part 1: Data Contracts and Service Operations
    • Prashanth on AX2012: SysOperation part 1: Data Contracts and Service Operations
    • SysOperationFramework : Field display order - Kenny Saelen’s Microsoft Dynamics AX Blog - AX Technical Blogs - Microsoft Dynamics Community on AX2012: SysOperation introduction
    • Axilicious » SysOperationFramework : Field display order on AX2012: SysOperation introduction
  • Who's Online

    • 0 Members.
    • 2 Guests.
  • Archives

    • January 2012 (2)
    • December 2011 (1)
    • November 2011 (3)
    • August 2011 (7)
    • April 2011 (1)
    • March 2011 (1)
    • February 2011 (1)
    • January 2011 (2)
    • November 2010 (1)
    • October 2010 (5)
    • August 2010 (2)
    • July 2010 (2)
    • June 2010 (3)
    • May 2010 (4)
    • April 2010 (13)
    • March 2010 (1)
    • February 2010 (8)
    • January 2010 (5)
    • December 2009 (3)
    • November 2009 (2)
    • October 2009 (7)
    • September 2009 (1)
    • August 2009 (3)
    • June 2009 (2)
    • May 2009 (2)
    • April 2009 (7)
  • Ax Dev Twitter

    • Microsoft Dynamics Salary Survey 2012 http://t.co/whcaqA97
    • Session Recordings Technical Conference Nice available here: http://t.co/yyOsKrt0
    • RT @Jvan_veldhuizen: @toincrease #DataMigration in more detail. #MSDynAx #2012 can perform --> 22000+ records per minute, 360+ record ...
    • @Jvan_veldhuizen bedankt om te volgen, ik kijk al uit naar al je blogposts over data migratie :)
    • @fawad343 that's for answering my question a few days back, Thx :)
    • So tell me, AX professionals, how often are you allowed to work from home?
    • RT @msdynamicsax: AX Technical Conference 2011 (Nice, France) wrap-up: http://t.co/vGg7Cn1h #msdynax #erp #AX2012
    • New blog post: AX Technical Conference 2011 Day 1: Information Source http://t.co/RLa5Q2rO
    • New blog post: Data migration: Importing the InventItemGroupForm Table http://t.co/giFAJ4u1
    • New blog post: See you at Technical Conference in Nice (Nov 14-16) http://t.co/moyBB2vZ
  • X++ Performance tips

    February 6, 2011 at 14:21  (3 comments)

    Hi everyone.

    I want to do an other post about performance. But not so much about exotic SQL stuff or hidden settings, but simple X++ performance tips.
    These are the first things I do and look for when there is a performance problem, because they are easy and obvious things to fix.

    Tip 1: Measure execution time of your code
    Measuring is knowing. Before you start changing code, make sure you have a set of data you can keep reusing for your tests. Measure the performance of your code on that data after each change in code so you know the impact of your changes.

    One way to do this is by using the Winapi::getTickCount() (or WinApiServer::getTickCount() if your code runs on server) method.

    static void KlForTickCountSample(Args _args)
    {
        int ticks;
        ;

        // get the tickcount before the process starts
        ticks = winapi::getTickCount();
       
        // start the process
        sleep(2000); // simulate 2 seconds of processing
       
        // compare tickcount
        ticks = winapi::getTickCount() - ticks;
       
        // display result
        info(strfmt('Number of ticks: %1', ticks));
    }

    Tip 2: limit the number of loops
    A LOT of time goes into loops. If you have a performance problem, start looking for loops. Code can run really fast, but it can get slow when it is executed too many time, eg, in a loop.

    Tip 3: avoid ‘if’ in ‘while select’
    When there is a ‘if’ in a ‘while select’, see if you can rewrite it a a where statement in your select. Don’t be affraid use a join either. Consider the following example:

    static void KlForIfInLoop(Args _args)
    {
        VendTable vendTable;
        ;

        // usually slower
        while select vendTable
        {
            if(vendTable.VendGroup == 'VG1')
            {
                info(vendTable.AccountNum);
            }
        }

        // usually faster
        while select vendTable
        where vendTable.VendGroup == 'VG1'
        {
            info(vendTable.AccountNum);
        }
    }

    Tip 4: avoid double use of table methods
    Using table methods a lot can get really slow if you do it wrong.

    Consider the following example:

    static void klForTableMethodsSlow(Args _args)
    {
        SalesLine salesLine;
        InventDim inventDim;
        ;
       
        // select a salesline
        select firstonly salesLine;
       
        inventDim.InventColorId = salesLine.inventDim().InventColorId;
        inventDim.InventSizeId  = salesLine.inventDim().InventSizeId;
        inventDim.inventBatchId = salesLine.inventDim().inventBatchId;
    }

    This example code looks nice, but there’s a problem.
    The salesLine.inventDim() method contains the following:

    InventDim inventDim(boolean  _forUpdate = false)
    {
        return InventDim::find(this.InventDimId, _forUpdate);
    }

    This means that the invendDim record is read three times from the database.
    It is better to declare the inventDim record locally and only retrieve it once:

    static void klForTableMethodsFast(Args _args)
    {
        SalesLine salesLine;
        InventDim inventDim;
        InventDim inventDimLoc;
        ;
       
        // select a salesline
        select firstonly salesLine;
       
        inventDimLoc = salesLine.inventDim();
       
        inventDim.InventColorId = inventDimLoc.InventColorId;
        inventDim.InventSizeId  = inventDimLoc.InventSizeId;
        inventDim.inventBatchId = inventDimLoc.inventBatchId;
    }

    Tip 5: Don’t put too much code on tables
    Code on tables is usually fast, but things can get slow if you use it to much.
    Say you have a table with an InventDimId field. If you have 5 methods that need the InventDim record, because you don’t have a classDeclaration method on your table, you need to call this function 5 times, once in every method:

    InventDim::find(this.inventDim)

    .
    When you put these methods on a class, you could optimise it by fetching the record only once and storing it in the classDeclaration, or better, passing it as a parameter to your methods.

    An other example is fetching parameters from parameter tables, eg InventParameters::find(). On a table, you have to fetch it each time you call a method. In a class, you would probably optimize your code to only fetch the parameter record once.

    Tip 6: Use the fastest code
    For some tasks, there is ‘special code’ that is faster than the code you would normally write.
    For example:

    // slower
    while select forupdate custTable
    where custTable.custGroup == 'TST'
    {
        custTable.delete();
    }

    // faster
    delete_from custTable
    where custTable.custGroup == 'TST';

    The same applies to update_recordset for updating records.

    Also, when adding values to the end of a container

    cont += "a value";

    is faster than

    cont = conins(cont, conlen(cont), "a value");

    Tip 6: Every optimization counts
    Remember that every optimization you do to you code counts, even if it’s a little one. Small performance tweaks can have a huge effect once you process large quantities of data. So don’t be lazy, and optimize :-).

  • News from the Dynamics AX Technical Conference 2011

    January 20, 2011 at 11:29  (4 comments)

    Many of you are at the technical conference right now, but if you’re not (like me unfortunately), you’re probably readling blogs to see what’s new in AX2012 (aka AX6).

    Two of my colleagues are there, and they are keeping up to their promise of blogging about the interesting stuff that is being unveiled (thanks guys!).

    Check out their blogs:
    - Tom Van Dyck
    - Kenny Saelen (or follow on twitter)

    Also check the #DAXCONF tag on twitter for tweets.

    Warning: it could be overwhelming ;-)

  • Happy 2011

    January 1, 2011 at 00:00  (no comments)

    Happy new year to everyone!
    I wish you all a lots of successful Dynamics AX implementations in 2011 ;-).

    happy 2011
    (Image © jscreationzs)

  • Decisions Fall 2010 session content available next week

    November 2, 2010 at 11:03  (1 comment)

    For everyone that missed (part of) the AX day of Decisions Fall 2010 yesterday, the sessions will be available on demand somewhere next week.

    Keep an eye on their website.

  • Tomorrow Decisions Virtual Conference – AX Day

    October 31, 2010 at 19:35  (no comments)

    Tomorrow the 1st of November is the start of Decisions Fall 2010, a 4 day, free online virtual conference about AX, GP, NAV and CRM.

    Check out what sessions are available for AX here: Dynamics AX program
    You can still register (for free) for this event, so what are you waiting for :-).

    Thank to Kenny for letting me know about this event.

  • Using the UserConnection class to create a new transaction scope

    October 20, 2010 at 18:30  (2 comments)

    Hi All!

    Here’s a trick I learned from a colleague:
    With the class UserConnection, you can make sure a transaction isn’t rolled back even if the transaction it is in is rolled back.

    Consider the following example:

    static void KlForRecordNotInserted(Args _args)
    {
        CustTable custTable;
        ;
       
        ttsbegin;
       
        custTable.clear();
        custTable.initValue();
        custTable.AccountNum = "000010";
        custTable.Name = "My Test Customer";
        custTable.insert();
       
        throw error("An error that causes a rollback");
        ttscommit;
    }

    The record will not be inserted, because an error is thrown that causes an implicit ttsabort (so the tranaction is rolled back), right?

    Well, you can counter this behavior by using the UserConnection class:

    static void KlForRecordInserted(Args _args)
    {
        UserConnection userConnection;
        CustTable custTable;
        ;
       
        ttsbegin;
       
        userConnection = new userConnection();
       
        custTable.clear();
        custTable.initValue();
        custTable.AccountNum = "000020";
        custTable.Name = "My Test Customer";
        custTable.setConnection(userConnection); // set userconnection
        custTable.insert();
       
        throw error("An error that causes a rollback");
        ttscommit;
    }

    You will see that the CustTable record will be inserted in the example above.

    This can be useful if you really want a record to be inserted, for example when doing logging (like in batch processes, or when debugging).
    When you don’t use a UserConnection, your logging will be rolled back together with other transaction.

  • Dynamics AX Performance Optimization

    October 17, 2010 at 22:30  (3 comments)

    The Belgian Dynamics Community organized a ‘Connection Day’ about Performance Optimization on the 1st of October.
    They have put the session content online here .

    If you are looking for ways to solve performance issues, be sure to check it out.

    Also, I found this nice PowerPoint presentation titled ‘AX Troubleshooting Tips’ while searching for ways to trace long running queries.
    Download it here.

  • See you at Convergence 2010, The Hague

    October 16, 2010 at 23:42  (1 comment)

    Hi all!

    Together with some of my collegues at RealDolmen, I will be attending Convergence 2010 in The Hague (Den Haag), The Netherlands, on Oktober 21st.

    You can expect a blog post about Convergence next week.
    Update: The content of Convergence wasn’t technical enough to be posted on this blog.

    See you then (or there).

  • Batch multithreading

    October 3, 2010 at 13:57  (5 comments)

    Do you have batches that run for hours? After reading this post (and doing some coding), they might just run in minutes.
    How? By creating multiple tasks in your batch job at runtime. Read on :-).

    Introduction

    Normally, when you schedule your RunBaseBatch class as a batch job, it will create one task.
    You can check this under Basic – Periodic – Batch Job when you have scheduled a batch job.
    In a diagram, it looks like this:

    This batch task will execute everything on one batch session on one batch server.

    But in AX 2009, your batch group can be added to multiple AOS server, and an AOS server can process multiple tasks simultaneously. So how can we use this to speed up our batch job?

    We do it by splitting up our task in multiple smaller ones, which are then distributed over multiple batch sessions (threads) and over multiple AOS servers.

    Like this:

    Example

    In this example, I will loop all my customers, and simulate a process that runs for 3 seconds by using the sleep() function.

    Note: download the .xpo file below.

    Single thread


    First, let’s create a batch job like we normally would.
    For this, I create 2 classes:

    1. KlForUpdateCustomers: A class that contains my business logic I want to execute
    2. KlForUpdateCustomersSingleTheadBatch: A class that extends RunBaseBatch and makes calls to KlForUpdateCustomers

    KlForUpdateCustomers
    This class contains this in its run method (see xpo file for other methods):

    public void run()
    {
        ;
        // simulate a process that takes 3 seconds
        sleep(3000);
        info(strfmt('updated customer %1', this.parmCustTable().AccountNum));
    }

    KlForUpdateCustomersSingleTheadBatch
    This class is our batch class so it extends RunBaseBatch. It has a queryRun, and has this run method:

    public void run()
    {
        CustTable custTable;
        ;

        while(queryRun.next())
        {
            custTable = queryRun.get(tablenum(CustTable));

            KlForUpdateCustomers::newcustTable(custTable).run();
        }
    }

    It loops all customers, and makes a call to the class we created earlier for each customer. To keep it simple, I did not add exception handling to this example.

    When we run this in batch, a single batch task is created in the batch job that runs for about 3 minutes (according to the batch job screen).

    Multithread


    To rewrite this batch job to use multiple tasks, we will need 3 classes:

    1. KlForUpdateCustomers: the business logic class we created earlier and we can reuse
    2. KlForUpdateCustomersMultiThreadBatch: The class that represents the batch job and will create batch tasks
    3. KlForUpdateCustomersMultiThreadTask: The class that represents one batch task and makes calls to KlForUpdateCustomers

    KlForUpdateCustomers
    This class is the same as before and processes one customer (CustTable record).

    KlForUpdateCustomersMultiThreadTask
    This class extends RunBaseBatch and represents one of the many batch tasks we will be creating. In this example it processes one CustTable record, so a task will be created for each CustTable record.

    The run method looks like this:

    public void run()
    {
        ;
        KlForUpdateCustomers::newcustTable(this.parmCustTable()).run();
    }

    KlForUpdateCustomersMultiThreadBatch
    This is our batch job class, and this is where the real magic happens. This class will create multiple instances of KlForUpdateCustomersMultiThreadTask and add them as a task to our job at run time.

    This is how the run method looks:

    public void run()
    {
        BatchHeader                         batchHeader;
        KlForUpdateCustomersMultiThreadTask klForUpdateCustomersMultiThreadTask;
        CustTable                           custTable;
        ;

        while(queryRun.next())
        {
            custTable = queryRun.get(tablenum(CustTable));

            if(this.isInBatch())
            {
                // when in batch
                // create multiple tasks
                if(!batchHeader)
                {
                    batchHeader = BatchHeader::construct(this.parmCurrentBatch().BatchJobId);
                }

                // create a new instance of the batch task class
                klForUpdateCustomersMultiThreadTask = KlForUpdateCustomersMultiThreadTask::newcustTable(custTable.data());

                // add tasks to the batch header
                batchHeader.addRuntimeTask(klForUpdateCustomersMultiThreadTask, this.parmCurrentBatch().RecId);
            }
            else
            {
                // when not in batch
                KlForUpdateCustomers::newcustTable(custTable).run();
            }
        }

        if(batchHeader)
        {
            // save the batchheader with added tasks
            batchHeader.save();
        }
    }

    As you can see, for each custTable record, we create a new task. When the batch job doesn’t run in batch, we process it as we otherwise would using one session.

    In the batch tasks screen (Basic – Inquiries – Batch Job – (select you batch job) – View Tasks), you can clearly see what happens.

    First, the status is waiting, and one task has been created.

    Then, when the batch job is executing, we can see that multiple tasks have been added to our batch job.

    We can also see that it processes 8 tasks at the same time! This is bacause the maximum number of batch threads is set to 8 on the batch server schedule tab of the Server configuration screen (Administration – Setup – Server configuration).

    Finally, we can see that the job has ended, and that all runtime tasks have been moved to the batch job history:

    You can view the history and the log of this batch job:

    Notice how the executing time has gone from 3 minutes to 1 minute according to the batch job screen.

    Alternatively, you could use a queryrun in you batch tasks class too. You could for example create a batch group per customer group if that makes more sense to you. The ‘per record’ approach is just an example, just do what makes the most sense in your situation. Sometimes it is better if the runtime tasks have a bit more to do, to counter the overload of creating the tasks.

    Conclusion


    This method has helped me a lot while dealing with performance issues. Be aware though, you could have problems with concurrency and heavy load (that I haven’t discussed here). But if you do it right, it will result in a huge performance boost.

    Spread the word :-).

    Download the XPO file by clicking here.

    Update 2011/02/11:
    There were two errors in the example:
    This line:

    batchHeader = BatchHeader::construct(this.parmCurrentBatch().RecId);

    Has been replaced with:

    batchHeader = BatchHeader::construct(this.parmCurrentBatch().BatchJobId);

    And this line:

    klForUpdateCustomersMultiThreadTask = KlForUpdateCustomersMultiThreadTask::newcustTable(custTable);

    Has been replaced with:

    klForUpdateCustomersMultiThreadTask = KlForUpdateCustomersMultiThreadTask::newcustTable(custTable.data());

    Todo on my part: update the XPO.

  • Cannot edit a record in Translation table (DEL_PBATranslateConsistOf). Access Denied

    August 29, 2010 at 13:54  (2 comments)

    When deleting a company account in Dynamics AX 2009, you can get the following errors:

    User ‘Admin’ is not authorized to delete a record in table ‘DEL_PBATRANSLATECONSISTOF). Request den[ied].
    Cannot edit a record in Translation table (DEL_PBATranslateConsistOf). Access Denied: You do not have sufficient authorization to modify data in database.

    This week was the n-th time someone has come to me with this problem, so it’s time to blog about it :-).

    The error you receive is odd, because user in question has enough rights to edit any table (administrator).
    This error occurs when you don’t have the Product Builder (PBA) license key enabled.

    There are two possible solutions to this problem:

    • Either activate the license key for Product Builder under Administration – Setup – System – License information (if you have the PBA license key), and after synchronizing the Data Dictionary, you should be able to delete the company account
    • Or if you don’t have the PBA license key, go to the AOT – Data Dictionary – Tables – DEL_PBATranslateConsistOf, and clear the security key property.
  • Trivial: by in group by is optional

    August 4, 2010 at 18:11  (4 comments)

    Here is something I did not know: apparently, the ‘by’ in ‘group by’ is optional.

    The following job compiles just fine, and outputs the correct result:

    static void KlForGroupBy(Args _args)
    {
        InventTable inventTable;
        ;
       
        while select inventTable
        group by ItemGroupId
        {
            info(inventTable.ItemGroupId);
        }
       
        info('------');
       
        while select inventTable
        group ItemGroupId // without by
        {
            info(inventTable.ItemGroupId);
        }
    }

    It looks funny though :-).

  • 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  (17 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). (Edit: Read comment #4 by Florian Dittgen)

    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 screenshot

    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’re 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  (5 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.

  • « Previous Page

    Next Page »

     

Wordpress // Photon theme // Copyright © Klaas Deforche