How to Create Sql Connection Through X++ in Ax 2012

Description:- The X++ language supports a number of ways to execute native SQL against any SQL data source. The Connection class establishes a current database session that you can use to execute SQL statements and return results. In the following example, the create Statement method initializes the Statement object. The Statement. Execute Query method executes an SQL statement and then stores the retrieved data in the Result Set object. The Result Set class provides access to a table of data generated by executing a Statement. Here we will Pass Store Procedure in Query and get Data from Database.

Create Job and Copy this Code and Create Store Procedure in your Database and pass in the query.

Store Procedure:-

Create procedure [dbo].[SP_GetNumber]
as
begin
declare @ID nvarchar(10)
set @ID = (select Max(NUMBER_) from AIFDEMOTABLE)
if @ID is null set @ID = 'Num-000000'
declare @i int
set @i = right(@ID,6) + 1
select 'Num-' + right('000000' + convert(nvarchar(10),@i),6) as Number
end

Create Job:-

static void ODBCConnection(Args _args)
{
    LoginProperty   myLoginProperty;
    DictTable  _dictTable;
    ODBCConnection  odbcConnection;
    Resultset       resultSet, resultSetCount;
    Statement       statement, statement2;
    SqlStatementExecutePermission   sqlpermission;
    str CompanyId,Query;

    myLoginProperty = new LoginProperty();
    myLoginProperty.setServer(SysSQLSystemInfo::construct().getLoginServer());
    myLoginProperty.setDatabase(SysSQLSystemInfo::construct().getloginDatabase());
    odbcConnection = new OdbcConnection(myLoginProperty);
    if (odbcConnection)
    {
        Query="exec SP_GetNumber";
        //Assert permission for executing the sql string.
        sqlpermission = new SqlStatementExecutePermission(Query);
        sqlpermission.assert();
            // Create new Statement instance
        statement =odbcConnection.createStatement();
        resultSet =statement.executeQuery(Query);
        while(resultSet.next())
        {
            info(resultSet.getString(1));
        }
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

Author : Umesh Patel

Related Posts