Dynamics User Group

Hi,

I wrote a job with following code with following code:

====================================

 ResultSet rs;

 DictTable  dictTable  = new DictTable(tableNum(custTable));
    Connection connection = new Connection();
    Statement  statement  = connection.createStatement();

   str sql=  "select * from CustTable"; 

 rs = statement.executeQuery(sql);
 while (rs.next() )
 {
    info(rs.getString(2));
 }

==========

Statement : rs=statement.executeQuery(sql)-gives me an error:

Request for the permission of type 'SqlStatementExecutePermission' failed.
(S) \Classes\SqlStatementExecutePermission\demand
(S) \Classes\Statement\executeQuery
(C) \Jobs\CSA_AutoPadTest - line 20

=========================

I can not find class: SqlStatementExecutePermission.

I am connected as admin. How can i grant permission for this type of actions?

thank you

Hi,

Perhaps you might want to check the authentication method on the database. Please make sure that you are using the right user credentials from the code.

The class that you mentioned is available under system documentation -> classes in AOT.

Hope this helps,

Hi,

If you haven't managed to fix the problem yet, please do a search for SQLStatementExecutePermission in the Ax 4.0 developer's guide.  There are some code snippets there which you might want to give a try.

Regards,

Hi, Harish.

What Dev guide are you reffering to? the online one on MSDN?

Or is there a dev guide other than that?

I tried to use the code provided here

http://msdn2.microsoft.com/en-us/library/aa639808.aspx

but still unable to figure it out.

Actually, my task is a little harder. I have a separate SQL database on the same SQL server that I need to get data from.

Is there a good example how this can be achieved?

Thanks

Actually, I got it working a split second after I wrote the post :)

But still, what I do now is specify the database i want to connect to, the user that owns the table and the table name, like this:

select * from AXDBtest.dbo.sTestTable

 using the Connection Class, not the ODBCconnection.

Any better suggestions? (it's my first hour working with the new DAX 4.0, hurray)

Hi

try like this

Userconnection connection;

Statement  st;

Result set rt;

str sql;

sql = " select * from custtable";

connection = new userconnection();

st = connection.createstatement();

rt =  st.executequery(sql);

while(rt.next())

{

 print rt.getstring(2);

}

Here is the link.its work fine in my end

http://msdn2.microsoft.com/en-us/library/aa639808.aspx

 Hi,

If you are looking to use different DataBase than the Axapta's one, this is different story.

What Microsoft Provides in their MSDN is how to connect to the AOT database.

any how I check the code and it work pretty fine with me.

creating new class and I put the code as main method.

<

server static void main(Args args)
{
    Connection con = new Connection();
    Statement stmt = con.createStatement();
    ResultSet r;
    str sql;
    SqlStatementExecutePermission perm;
    ;
 
    sql = strfmt('
 
  
  
 

 
  Normal
  0
  
  
  
  
  false
  false
  false
  
  EN-US
  X-NONE
  AR-SA
  
   
   
   
   
   
   
   
   
   
   
   
   
  
  
   
   
   
   
   
   
   
   
   
   
   
  

 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
 




 /* Style Definitions */
 table.MsoNormalTable
	{mso-style-name:"Table Normal";
	mso-tstyle-rowband-size:0;
	mso-tstyle-colband-size:0;
	mso-style-noshow:yes;
	mso-style-priority:99;
	mso-style-qformat:yes;
	mso-style-parent:"";
	mso-padding-alt:0in 5.4pt 0in 5.4pt;
	mso-para-margin-top:0in;
	mso-para-margin-right:0in;
	mso-para-margin-bottom:10.0pt;
	mso-para-margin-left:0in;
	line-height:115%;
	mso-pagination:widow-orphan;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";
	mso-ascii-font-family:Calibri;
	mso-ascii-theme-font:minor-latin;
	mso-hansi-font-family:Calibri;
	mso-hansi-theme-font:minor-latin;}

'select top 10 * from custTrans');
 
    // Set code access permission to help protect the use of
    // Statement.executeUpdate.
    perm = new SqlStatementExecutePermission(sql);
    perm.assert();
 
    try
    {
        r = stmt.executeQuery(sql);
        while (r.next())
        {
            print r.getString(1);
            pause;
        }
    }
    catch (exception::Error)
    {
        print "An error occured in the query.";
        pause;
    }
    // Code access permission scope ends here.
    CodeAccessPermission::revertAssert();
}

>

 .

 hope this can help you

    connection con = new connection();
    statement sta;
    SqlStatementExecutePermission _perm;
    str command;

     command= "sql script here";

        _perm = new SqlStatementExecutePermission(command);
        _perm.assert();

        sta = con.createStatement();
        sta.executeUpdate(command);

        CodeAccessPermission::revertAssert(); 

Hi,

I found it. 

there are two conditions you have to make sure of them so the code will work.

by the way all what the guys repied is correct code, but.

you have to:

1- create new class and but that code in the main method"

Normal 0 false false false EN-US X-NONE AR-SA /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

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 is the way.

please try it and feed us back.