Showing posts with label sql query using axapta. Show all posts
Showing posts with label sql query using axapta. Show all posts

Friday, June 25, 2010

sql query in ax

create a connection using connection object, don't need to assign any parameter here, this will help to run query. Create an statement object and assign a query in an execute method of it. Before that you need to create an SQlStatementExecutePermission that will allow to use SQL in a X++, then call assert that declares the calling code to invoke an API that is protected by a permission. after execute query a resultset is generated that you can access via .get(Datatype) method.

example is given below.



Connection con = new Connection();
Statement stmt = con.createStatement();
str sqlPassStr1;
ResultSet sqlres;
SqlStatementExecutePermission sqlStatementExecutePermission;
;

sqlPassStr1 = 'select ACCOUNTNUM, CUSTGROUP from CustTable';

sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlPassStr1);
sqlStatementExecutePermission.assert();

sqlres = stmt.executeQuery( sqlPassStr1);
while(sqlres.next())
{
info(strfmt('%1 %2', sqlres.getString(1), sqlres.getString(2)));
}