Create stored procedure from x++

Question

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:

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.

Does this help: http://stackoverflow.com/questions/12184152/how-to-get-the-results-of-a-direct-sql-call-to-a-stored-procedure (`executeQuery` vs `executeUpdate`). Not at a dev box, so can't really dig into the question. — Alex Kwitny, Nov 24 '16 at 00:55
Thanks a lot Alex, executeUpdate did the trick very well and i understood the reasons. Can you please repost your comment as an answer so i can accept it? Many thanks to Jan B. Kjeldsen too. I was indeed lost on this :) — Olaru Mircea, Nov 24 '16 at 13:19

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

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

executeQuery vs executeUpdate

edited May 23 '17 at 12:30

Community

1
1

answered Nov 24 '16 at 16:09

Alex Kwitny

11,211
2
49
71

Create stored procedure from x++

1 Answers1