Executing SQL directly from X++

The X++ language supports a number of ways to execute native SQL against any SQL data source. Below are two samples for retrieving data as well as manipulating data. Please be aware, that SQL can be really powerful, and it should be used as such.

Example #1: Retrieve data:

Since this example uses a Connecion class, data is retrieved from the database where Axapta is currently connected.

void Sample_1(void)

{
Connection Con = new Connection();
Statement Stmt = Con.createStatement();
ResultSet R =Stmt.executeQuery(‘SELECT VALUE FROM SQLSYSTEMVARIABLES’);

while ( R.next() )
{
print R.getString(1);
}
}

Example #2: Manipulating data (deletion):

void Sample_2(void)
{
str sql;
Connection conn;
SqlStatementExecutePermission permission;
;

sql = ‘delete from custTable’;
permission = new SqlStatementExecutePermission(sql);
conn = new Connection();
permission = new SqlStatementExecutePermission(sql);
permission.assert();
conn.createStatement().executeUpdate(sql);
// the permissions needs to be reverted back to original condition.
CodeAccessPermission::revertAssert();
}

Happing Daxing J Enjoy the power of SQL using x++

Advertisements

10 Responses to Executing SQL directly from X++

  1. Hatice says:

    Hello,

    I have test the second exemple in a job, but AX send me an error message, can you please help me ?

    The job :

    static void Job15(Args _args)
    {
    str sql;
    Connection conn;
    SqlStatementExecutePermission permission;
    ;

    sql = ‘selet * from custTable’;
    permission = new SqlStatementExecutePermission(sql);
    conn = new Connection();
    permission = new SqlStatementExecutePermission(sql);
    permission.assert();
    conn.createStatement().executeUpdate(sql);
    // the permissions needs to be reverted back to original condition.
    CodeAccessPermission::revertAssert();

    }

    The error message :

    Request for the permission of type ‘SqlStatementExecutePermission’ failed.
    (S)\Classes\SqlStatementExecutePermission\demand
    (S)\Classes\Statement\executeUpdate
    (C)\Jobs\Job15 – line 13

    Thanks by advance.

    • Amir Nazim says:

      Hi

      Normally the issue is with the authentication method on the database. Please make sure that you are using the right user credentials from the code.
      i can help you out with some links or related threads that might helps you
      forum where same issue was discussing…. http://dynamicsuser.net/forums/t/355.aspx
      MSDN helps that define the class and examples as well … http://msdn.microsoft.com/en-us/library/aa639808.aspx

      secondly i am seeing a problem in the code as well
      sql = ‘selet * from custTable’;… this must be ‘select’ instead of ‘selet’

      Hope that works for you
      Thanks
      Amir

      • Amir Nazim says:

        Below two steps will also solve the problem..
        1- create new class and put that code in the main method “static void main(Args args){}

        2- make sure that the property of the class “RunOn” is set to “server: this step is too IMPORTANT since the connection can only happen on the SERVER
        and it will fail if you run it in client.

        I hope this helps.

  2. Candice says:

    Hi Amir,

    Is there any way to insert data using SQL directly from X++?

    Thank you.

    • Amir Nazim says:

      yes it is possible, and quite same, you need to build the connection and use statement.executeUpdate(_sql); to do the insert update delete operations. check below example.

      #public static int executeCreateUpdateDelete(str _sql)
      #{
      # OdbcConnection odbcConnection;
      # Statement statement;
      # SqlStatementExecutePermission perm;
      # int result;
      # ;
      #
      # odbcConnection = IncidentDBManager::createConnection();
      #
      # if (odbcConnection)
      # {
      # //Assert permission for executing the sql string.
      # perm = new SqlStatementExecutePermission(_sql);
      # perm.assert();
      #
      # //Prepare the sql statement.
      # statement = odbcConnection.createStatement();
      # result = statement.executeUpdate(_sql);
      # }
      #
      # return result;
      #}

  3. Pingback: Executing SQL directly from X++ « Amir’s Microsoft Dynamics AX space | blogs.bojensen.eu

  4. Stephen says:

    I have a class that executes direct SQL and runs fine from a menu item. How can I use this as a data source for a from or report?

    • Amir Nazim says:

      direct SQL can not be used as datasource, you can fill a tmp table with the direct SQL that can be used as form or report datasource

  5. khan says:

    wow aamiR!

  6. vicky says:

    Hi Amir,
    Can sql update statements be rolled back as we have ttsbegin and ttscommit in AX.

    My code works perfect when there is no failure ,but in case of failure the update statement to sql does not roll backs.(I have kept this in ttsbegin and ttscommit.)

    Also I am fetching data from multiple tables ,so do I need to write 3 times the below code.
    //Assert permission for executing the sql string.
    perm = new SqlStatementExecutePermission(sqltable1);
    perm.assert();

    http://dynamicsuser.net/forums/t/79210.aspx

    Thanks,
    Vikas Mehta

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

%d bloggers like this: