.net Components with batch class in ax2009

Finance and Operations/AX Developer Forum
1

Hi. I’m looking for a solution about this case.

OLE DB component is used for batch class to connect with oracle database and it has changed as ‘called from’/‘server’/‘client’, but it doesn’t work properly. I have already changed ‘server static’ instead of ‘public’ and property of class was changed, too(‘called from’ → ‘server’). I think that AX2009 batch class doesn’t support .net component. Right?

Please let me know about this problem.

Code is like this.

public void SelectQuerySample(KICKR_IFTarget _IFTarget = KICKR_IFTarget::KISDB)
{

System.Exception e;
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand cmdSelect;
System.Data.OleDb.OleDbDataReader reader;
InteropPermission perm;

str exceptionStr;
str ConnectionString;
str sSQL;

KICKR_Test KICKR_Test;

int iCnt, i;

str dd;
perm = new InteropPermission(InteropKind::ClrInterop);
perm.assert();
try {

ConnectionString = strfmt(“Provider=OraOLEDB.Oracle.1;Persist Security info =false;Data Source=%1;User ID=%2;Password=%3”,oracl_backup,test1,test1);

objConn = new System.Data.OleDb.OleDbConnection(ConnectionString);

objConn.Open();
cmdSelect = objConn.CreateCommand();
//Job range →
sSQL = "SELECT AAA ";
sSQL += " FROM ta_item_pic where rownum < 100 ";

cmdSelect.set_CommandText(sSQL);
reader = cmdSelect.ExecuteReader();

while (reader.Read())
{
KICKR_Test.ItemId = reader.get_Item(‘AAA’);
KICKR_Test.insert();
}
//Job range ←
objConn.Close();

}
catch(Exception::CLRError)
{
CodeAccessPermission::revertAssert();

perm = new InteropPermission(InteropKind::ClrInterop);
if (perm == null)
{
return;
}
perm.assert();

e = ClrInterop::getLastException();

CodeAccessPermission::revertAssert();

while( e )
{
exceptionStr += e.get_Message();
e = e.get_InnerException();
}
info(exceptionStr);
} catch {
throw error(“An Exception has occurred”);
}
CodeAccessPermission::revertAssert();
}

2

No, you’re wrong. .NET Interop is supported in batch.

First of all, does your code work if you run it on server without batch? If not, you see that the problem is not related to batches.

Also, please give us a more detailed description than “it doesn’t work”.

3

Hi. Martin. Thank you for your reply.
If you’re right, nothing is better than this.

Let me explain about background. Our AX2009 system needs to connect with Oracle Database and version of it is quite old.(Oracle 8i)
Therefore OLE DB is used for this case.

Well. In my case, I utilized batch class and it’s property is ‘class’. I defined ‘server static’ for this method.

When I utiliized like ‘public’ then I saw this message like below.

Error Message (03:19:33 pm) Request for the permission of type ‘InteropPermission’ failed.
(S)\Classes\InteropPermission\demand
(S)\Classes\CLRObject\get_Message
(S)\Classes\Test\SelectQuerySample - line 71
(S)\Classes\Batch_Server_Test12\run - line 8
(S)\Classes\BatchRun\runJobStatic - line 62

Error Message (03:19:33 pm) Clr Interop Marshal: Unsupported type.
Error Message (03:19:33 pm) CLR object cannot be marshaled to Microsoft Dynamics anytype.

When I declared ‘server static’, there were these message.

Error Infolog for Job Batch_Server_Test13 (5638833028)\Infolog for Task Batch_Server_Test13 (5638835426) Request for the permission of type ‘InteropPermission’ failed.
(S)\Classes\InteropPermission\demand
(S)\Classes\CLRObject\get_Message
(S)\Classes\Test\SelectQuerySample - line 71
(S)\Classes\Batch_Server_Test13\run - line 10
(S)\Classes\BatchRun\runJobStatic - line 62

Error Infolog for Job Batch_Server_Test13 (5638833028)\Infolog for Task Batch_Server_Test13 (5638835426) Clr Interop Marshal: Unsupported type.
Error Infolog for Job Batch_Server_Test13 (5638833028)\Infolog for TaskBatch_Server_Test13 (5638835426) CLR object cannot be marshaled to Microsoft Dynamics anytype.

Please show me the way for this case.
Thank you .

4

It has nothing to do with the “public” keyword; Code Access Security is enforced when running on server (regardless if it’s in batch or not). Add the following piece of code before any call using .NET Interop:

new InteropPermission(InteropKind::ClrInterop).assert();

Seriously, if you try to do everything at once, such as trying to run in batch something that doesn’t run at all, you end up exactly where you are - stuck without any idea what’s going on. Start with a simple piece, test it and add more stuff only if it works.

5

Hi. Martin.

I appreciate your quick answer. I already used ‘InteropPermission’ like previous post.

perm = new InteropPermission(InteropKind::ClrInterop);
perm.assert();

CodeAccessPermission::revertAssert();

Actually interfacing job workes which is in previous post. Data from oracle database can be shown in AX system.

However auto bach job doesn’t work. This is a problem. Even though error messages were shown like above, ‘IntoropPermission’ doesn’t work.

Thank you.

6

I don’t know what “Actually interfacing job workes” means and I don’t see it mentioned before. Please explain from the beginning what works and what doesn’t and tell us what code you have on line 71 (where the exception is thrown).

Also reducing your code to the minimal case still showing the error is often the best strategy.

7

I’m sorry to make you confusing. I reduced codes and it is like below.

And ‘Actually interfacing job workes’ means that when I ran a job(interfacingOLEDB), it worked.
But it is run by batch class, I saw error message like above.

static void interfacingOLEDB(Args _args)
{
Test Test;
;

Test = new Test;
Test.Sample();
}

class Test
{
}

public void Sample()
{
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand cmdSelect;
System.Data.OleDb.OleDbDataReader reader;
InteropPermission perm;
str ConnectionString;
str strSQL;
AAA AAA;
;

perm = new InteropPermission(InteropKind::ClrInterop);
perm.assert();

try {

ConnectionString = strfmt(“Provider=OraOLEDB.Oracle.1;Persist Security info =false;
Data Source=%1;User ID=%2;Password=%3”,oracl_backup,test1,test1);

objConn = new System.Data.OleDb.OleDbConnection(ConnectionString);

objConn.Open();
cmdSelect = objConn.CreateCommand();

strSQL = “SELECT AAA FROM oracleTable”;

cmdSelect.set_CommandText(strSQL);
reader = cmdSelect.ExecuteReader();

while (reader.Read())
{
AAA.ItemId = reader.get_Item(‘AAA’);
AAA.insert();
}

objConn.Close();

CodeAccessPermission::revertAssert();

}
catch
{
CodeAccessPermission::revertAssert();
}
}

8

Job runs on client. Please run your code on server (but not in batch) first, try a batch only if your code works without batch.

Running you code on client means that it runs in a completely different process on (typically) completely different machine, so it’s not very informative test.

And what’s the failing line of code?

9

Hi. Martin. I changed job to run on server like below. But debugger went to the catch line when debugger approached to the ‘objConn.Open();’ line. There is no exception message. Thank you.

static void interfacingOLEDB(Args _args)
{
;
Test::Sample();
}

class Test
{
}

server static void Sample()
{
System.Exception e;
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand cmdSelect;
System.Data.OleDb.OleDbDataReader reader;
InteropPermission perm;
str ConnectionString;
str strSQL;
AAA AAA;

str exceptionStr;
;

perm = new InteropPermission(InteropKind::ClrInterop);
perm.assert();

try {

ConnectionString = strfmt(“Provider=OraOLEDB.Oracle.1;Persist Security info =false;Data Source=%1;User ID=%2;Password=%3”,‘datasource’,‘id’,‘password’);

objConn = new System.Data.OleDb.OleDbConnection(ConnectionString);

objConn.Open();
cmdSelect = objConn.CreateCommand();

strSQL = “SELECT XF_PLU as AAA FROM itemmas where rownum < 100”;

cmdSelect.set_CommandText(strSQL);
reader = cmdSelect.ExecuteReader();

while (reader.Read())
{
AAA.ItemId = reader.get_Item(‘AAA’);
AAA.insert();
}

objConn.Close();

CodeAccessPermission::revertAssert();

}
catch
{
while( e )
{
exceptionStr += e.get_Message();
e = e.get_InnerException();
}
info(exceptionStr);

CodeAccessPermission::revertAssert();
}
}

10

Your exception handling is wrong.

First of all, you never write anything to the e variable, so you can’t expect to get anything from it. You likely wanted to assign CLRInterop::getLastException() there.

And you should run this handler for CLR exceptions only (catch(Exception::CLRError)), not for all exceptions.

11

Thank you, Martin.
As you said, I changed exception handler and there are error message.

Error Message (04:55:37 pm) Clr Interop Marshal: Unsupported type.
Error Message (04:55:37 pm) CLR object cannot be marshaled to Microsoft Dynamics anytype.

It is shown when debugger is in ‘objConn.Open()’ lines.

12

Great, we just prove that the problem has nothing to do with batch (as I expected from the beginning).

Can you please remove all subsequent code and verify that objConn = new System.Data.OleDb.OleDbConnection(ConnectionString); (without calling Open) works and it starts failing as soon as you add Open().

13

Hi. Martin.

Thank you for your help. I asked to IT team in customer company and permission for AOS was not enough to run this job.

I appreciate your help.

Have a nice day!

14

Hi. Martin.

Thank you for your help. I want to let you know what I did after all.

I checked connection between AOS server and Oracle database with system administrator. After oracle database is upgraded from 8 to 11g, OLE db doesn’t make any trouble. Even batch job didn’t make any problem. It is natural as you said.

Actually I wanted to utilize ODBC connection, so I installed and tested from ODAC 10 version to ODAC 12 version. However there was a problem. Suddenly AOS is stopped when record set returns string value. If there is time to study about it, I would like to check it, but I am running out of time for due date, so I decided to utilize OLE DB.

Also I want to help you someday if I have a chance.

Thank you. Martin.