How to revert a sql update in ttsbegin and ttscommit: AX 2012

Finance and Operations/AX Developer Forum
1

Hi guys,

I am pulling data from sql to Ax and once the data is created in one of tables in AX I need to update the sql tables …so I wrote the following code.

ttsbegin;

if(odbcConnection)
{ AXtable.field1 = resultset.getstring(0);

Options.insert();

sqlupdate = “UPDATE [dbo].[sqltable] SET [Is record created] = 1”;

sqlupdateoption = odbcConnection.createStatement();

sqlupdateoption.executeUpdate(sqlupdate);

}

ttscommit;

Everything is fine when a record is created in AX but in case there is an error the AX piece rolls back but the sql update statement does not .(is there anything other then ttscommit which I need to specify.

Please share some idea on this issue.

Thanks,

2

Probably the easiest way is committing changes to the external database only after everything succeeded in AX. Note that OdbcConnection class has ttsBegin() and ttsCommit() methods.

3

May be you can use odbcTransaction class to roll back the transaction.

OdbcTransaction transaction = null;

try
{
transaction = odbcConnection.BeginTransaction();
…
…
…
transaction.Commit();
}

catch(ExceptIon::Error)
{

try
{
// Attempt to roll back the transaction.
transaction.Rollback();
}
catch
{
// Do nothing here; transaction is not active.
}
}

Regards,

Raghav.

4

Hi,

Just Update the External Table once all internal transactions are done.

Regards

Pawan

5

Thanks a lot for your suggestions .

I ran up into a new issue .

Error :Multiple calls to CodeAccessPermission.Assert

I am running the class Run on server also the main class is server static void main but then too i am getting the error .below is my code.

static void odbcconnall(Args _args)

{

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statementproduct,statementoption,statementprdrel;

ResultSet resultset,resultsetproduct,resultsetprdoptionrel;

str sqloption,sqlupdate, criteria,sqlproduct,sqlproductrelation;

SqlStatementExecutePermission perm;

Table1 Table1;

Table2 Table2;

Table3 Table3;

;

// Set the information on the ODBC.

loginProperty = new LoginProperty();

loginProperty.setDSN(“csc”);

loginProperty.setDatabase(“cs”);

//Create a connection to external database.

odbcConnection = new OdbcConnection(loginProperty);

ttsBegin;

if (odbcConnection)

{

sqloption = “SELECT * FROM Table1”;

sqlproduct = “SELECT * FROM Tabl2”;

sqlproductrelation = “SELECT * FROM Table3”;

//Assert permission for executing the sql string.

perm = new SqlStatementExecutePermission(sqloption);

perm.assert();

//Prepare the sql statement.

statementoption = odbcConnection.createStatement();

resultSet = statementoption.executeQuery(sqloption);

// statement.executeUpdate(sql);

//product

perm = new SqlStatementExecutePermission(sqlproduct);

perm.assert();

statementproduct = odbcConnection.createStatement();

resultsetproduct = statementproduct.executeQuery(sqlproduct);

//productoptionrelation

perm = new SqlStatementExecutePermission(sqlproductrelation);

perm.assert();

statementprdrel = odbcConnection.createStatement();

resultsetprdoptionrel = statementprdrel.executeQuery(sqlproductrelation);

Table1.clear();

while (resultSet.next())

{

Table1.CompanyCode = resultSet.getString(1);

Table1.CharacteristicName = resultSet.getString(2);

Table1.CharacteristicDescription = resultset.getString(3);

Table1.DeletionIndicator = resultset.getString(4);

Table1.SystemCode = resultset.getString(5);

Table1.DateCreation = resultset.getDate(6);

// Table1.Time = resultset.getString(7);

Table1.IsOptionCreated = resultset.getBoolean(8);

Table1.Owner = resultset.getstring(9);

if(table1.validateWrite())

{

table1.insert();

sqlupdate = “UPDATE [dbo].[table2] SET [Is tab2 created] = 9”;

perm = new SqlStatementExecutePermission(sqlupdate);

// perm.assert();

sqlupdateoption = odbcConnection.createStatement();

sqlupdateoption.executeQuery(sqlupdate);

}

}

Table2.clear();

while(resultsetproduct.next())

{

Table2.Product = resultsetproduct.getString(1);

Table2.SalesOrganization = resultsetproduct.getString(2);

}

resultSet.close(); statementoption.close();

}

else

{

error(“Failed to log on to the database through ODBC.”);

}

ttsCommit;

}

the main issue is during the SQL update row .

I am using perm.assert () multiple times but if i remove that then I get "‘SqlStatementExecutePermission’ failed."

There are three tables from where I am getting the data but after getting the data I need to update the sql for each record i create in AX .

Please let me know what changes do I need to do in the code ?

I have also tried permissionSet = new Set(Types::VarString); but I dont know if this works for sql ?

Thanks a ton.

6

The error message should be clear enough. The solution is CodeAccessPermission::assertMultiple().

7

Hi Martin,

This too doesn’t wok.

Is there any thing else also which I need to specify ?

however ,if i do revertaccess() and then again give the commands of login and odbc statements then it works.

Please suggest

8

“Doesn’t work” is nothing I can help with. Without knowing what you did and what’s the problem, I also can’t tell you what “else you need to specify”.

I don’t believe that “CodeAccessPermission::assertMultiple() doesn’t work”. More likely, you don’t use it correctly, but since you didn’t explain what you did, nobody can give you any advice.

Usually people will simply ignore your question if you don’t provide enough information. If you need help, you need to do your part first.

9

My Bad…

Below is the code which I have used …have also tried running the code at different levels i.e. have used this code at different levels…( CodeAccessPermission::assertMultiple(permissionSet):wink:

public void run()

{

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statementproduct,statementoption,statementprdrel,sqlupdateoption;

ResultSet resultset,resultsetproduct,resultsetprdoptionrel;

str sqloption,sqlupdate, criteria,sqlproduct,sqlproductrelation;

SqlStatementExecutePermission perm,perm1;

Options Options;

PwcProduct products;

ProductOptionRelation productOptionRelation;

Set permissionSet;

;

//Create a connection to external database.

odbcConnection = new OdbcConnection(loginProperty);

ttsBegin;

// odbcConnection.ttsbegin();

if (odbcConnection)

{

sqloption = “SELECT * FROM Options”;

sqlupdate = “UPDATE [dbo].[Options] SET [Is Option created] = 9”;

permissionSet = new Set(Types::Class);

permissionSet.add(new SqlStatementExecutePermission(sqloption));

permissionSet.add(new SqlStatementExecutePermission(sqlupdate));

CodeAccessPermission::assertMultiple(permissionSet);

//Prepare the sql statement.

statementoption = odbcConnection.createStatement();

resultSet = statementoption.executeQuery(sqloption);

ttsbegin;

Options.clear();

while (resultSet.next())

{

Options.CompanyCode = resultSet.getString(1);

Options.CharacteristicName = resultSet.getString(2);

Options.CharacteristicDescription = resultset.getString(3);

Options.DeletionIndicator = resultset.getString(4);

Options.SystemCode = resultset.getString(5);

Options.DateCreation = resultset.getDate(6);

Options.IsOptionCreated = resultset.getBoolean(8);

Options.Owner = resultset.getstring(9);

if(Options.validateWrite())

{

Options.insert();

sqlupdateoption = odbcConnection.createStatement();

sqlupdateoption.executeQuery(sqlupdate);

}

}

ttsCommit;

// odbcConnection.ttscommit();

resultSet.close(); statementoption.close();

}

else

{

error(“Failed to log on to the database through ODBC.”);

}

ttsCommit;

}

10

You don’t make me happy, because I real hate working with this API. You have no type control either in SQL nor in X++, you have to write SQL statements manually etc. etc. There are much better APIs in .NET.

But I guess I can’t avoid it after starting replying to this thread, even if the topic changed completely from How to revert a sql update in ttsbegin and ttscommit. All right, let me refresh how such things were done years ago…

Before actually changing your code, let me repost it. I’ve just modified it to a form that everybody can easily copy and paste, I removed unused variables, adjusted indentation etc.

LoginProperty       loginProperty;
OdbcConnection      odbcConnection;
Statement           statementOption, sqlUpdateOption;
ResultSet           resultSet;
str                 sqlOption, sqlUpdate;
Options             options;
Set                 permissionSet;

//Create a connection to external database.
odbcConnection = new OdbcConnection(loginProperty);

ttsBegin;

if (odbcConnection)
{
    sqloption = "SELECT * FROM Options";
    sqlupdate = "UPDATE [dbo].[Options] SET [Is Option created] = 9";
    permissionSet = new Set(Types::Class);

    permissionSet.add(new SqlStatementExecutePermission(sqloption));
    permissionSet.add(new SqlStatementExecutePermission(sqlupdate));

    CodeAccessPermission::assertMultiple(permissionSet);

    //Prepare the sql statement.
    statementoption = odbcConnection.createStatement();
    resultSet = statementoption.executeQuery(sqloption);

    ttsbegin;

    options.clear();

    while (resultSet.next())
    {
        options.CompanyCode                     = resultSet.getString(1);
        options.CharacteristicName              = resultSet.getString(2);
        options.CharacteristicDescription       = resultset.getString(3);
        options.DeletionIndicator               = resultset.getString(4);
        options.SystemCode                      = resultset.getString(5);
        options.DateCreation                    = resultset.getDate(6);
        options.IsOptionCreated                 = resultset.getBoolean(8);
        options.Owner                           = resultset.getstring(9);

        if (options.validateWrite())
        {
            options.insert();

            sqlupdateoption = odbcConnection.createStatement();
            sqlupdateoption.executeQuery(sqlupdate);
        }
    }

    ttsCommit;

    resultSet.close();
    statementoption.close();
}
else
{
    error("Failed to log on to the database through ODBC.");
}

ttsCommit;

Now I have to go and prepare a test table to be able to test the code…

11

This is how I rewrote your code to work in my system. I have a simpler table in external database and I removed the dependency to Options table in AX.

LoginProperty       loginProperty;
OdbcConnection      odbcConnection;
Statement           statementOption, sqlUpdateOption;
ResultSet           resultSet;
str                 sqlOption, sqlUpdate;
Set                 permissionSet;
str                 id;

loginProperty = new LoginProperty();
loginProperty.setServer('.');
loginProperty.setDatabase("TestDB");

//Create a connection to external database.
odbcConnection = new OdbcConnection(loginProperty);

sqloption = "SELECT * FROM Options";
sqlupdate = "UPDATE Options SET Completed = 1";
permissionSet = new Set(Types::Class);

permissionSet.add(new SqlStatementExecutePermission(sqloption));
permissionSet.add(new SqlStatementExecutePermission(sqlupdate));

CodeAccessPermission::assertMultiple(permissionSet);

//Prepare the sql statement.
statementoption = odbcConnection.createStatement();
resultSet = statementoption.executeQuery(sqloption);

while (resultSet.next())
{
    id = resultSet.getString(1);

    sqlupdateoption = odbcConnection.createStatement();
    sqlupdateoption.executeUpdate(sqlupdate);
}

resultSet.close();
statementoption.close();

And my result is: there is no error about code access permissions (it does throw another error, but let’s address just one problem at time). Can you confirm again that the error about “Multiple calls to CodeAccessPermission.Assert” still occurs in your case and that you’re using the code you posted here?

12

Hi Martin,

Sry for the late reply…was working on different requirement…

Your solution worked and it did allowed multiple calls to statements.Thanks a lot [:)]

However,

I run in to the below error while updating that particular record in the while loop .

SQL error description: [Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command

If I keep run the sql update command outside the while loop it works…but i need to update it simultaneously since i will have to update that particular record in sql also.

13

Are you using the same code? If not, please show us your code. Also, how many records do you update? I tried it with multiple records, but only with a few (three or so).