Search code examples
microsoft-dynamicsdynamics-ax-2012x++dynamics-ax-2012-r3

Create stored procedure from x++

Got myself into trouble today trying to create a stored procedure from ax.

Here is a simple example: 

 static void testProcedureCreation(Args _args)
 {
     MyParamsTable myParams;
     SqlStatementExecutePermission perm;

     str sqlStatement;

     LogInProperty Lp = new LogInProperty();
     OdbcConnection myConnection;
     Statement myStatement;
     ResultSet myResult;
     str temp;
     ;

     select myParams;


     LP.setServer(myParams.Server);
     LP.setDatabase(myParams.Database);
     //Lp.setUsername("sa");
     //Lp.setPassword("sa");

      sqlStatement = @"create procedure testproc
                as begin

                print 'a'

                end";
     //sqlStatement = strFmt(sqlStatement, myStr);
     info(sqlStatement);
     perm = new SqlStatementExecutePermission(sqlStatement);

     perm.assert();

     try
     {
         myConnection = new OdbcConnection(LP);
     }
     catch
     {
        info("Check username/password.");
        return;
     }



     myStatement = myConnection.createStatement();
     myResult = myStatement.executeQuery(sqlStatement);

     while (myResult.next())
     {
        temp = myResult.getString(1);
        info(temp);

        if (strScan(temp, 'Error', 1, strLen(temp)) > 0)
            throw error(temp);
    }

    myStatement.close();

    CodeAccessPermission::revertAssert();
}

To be honest, in my real example I am using BCP and some string concat with a lot of | ' and "".

Anyway, here is what I got:

enter image description here

For a couple of hours I kept changing and retrying a lot of things and, a good thought came into my mind.

"Let's try with a much easier example and check the results!"

OK, no luck, the results were the same, as you can see in the pic above.

But for no reason, I tried to : 

exec testproc

in my ssms instance and to my surprise, it worked. My small procedure was there.

It would be so nice if someone could explain this behavior and maybe what should be the correct approach.

Solution

  • This Q/A should provide an answer. How to get the results of a direct SQL call to a stored procedure?

    executeQuery vs executeUpdate