Search This Blog

Friday, September 16, 2011

Execute External DB Query using X++


static void krishh_ExecuteExternalDBQuery()
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;
    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("dsnName");
    loginProperty.setDatabase("databaseName");
    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);
    if (odbcConnection)
    {
        sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";
        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();
        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);
        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }
        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

2 comments:

  1. Hi
    How did you solve the login property?
    When I try the same I am met with an error: ODBC connection failed. Unable to logon to the database.

    ReplyDelete
    Replies
    1. Hi Erik,
      you can use like below create connectionString and define the dsnName,UID,PWD for the databse of that DSN and pass that to the loginProperty class.

      ConnectionString=strfmt("DSN=%1;UID=%2;PWD=%3",DSN,UserName,Password);

      loginProperty = new LoginProperty(); Loginproperty.setOther(ConnectionString);

      Delete

Thanks for visiting my blog,
I will reply for your comment within 48 hours.

Thanks,
krishna.