Insert data from external database via ODBC

I want to get the data from Sql,

i am created the ODBC Connection and created the Class for Batch Job and writing the One Method to insert in to table

and call this Method in Run Method of class.

Runbase batch is executed and data is comming perfectly.

After run the batch job if i insert the New value in SQL Data Base that value is not Coming.

i am writing this code.

public void insertSGCO_WB1_ProductIN()

{

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

SGCO_WB1_ProductIN _SGCO_WB1_ProductIN;

str sql, criteria;

SqlStatementExecutePermission perm;

;

// Set the information on the ODBC.

loginProperty = new LoginProperty();

loginProperty.setDSN(“ODBC Data Source”);

//Create a connection to external database.

odbcConnection = new OdbcConnection(loginProperty);

if (odbcConnection)

{

sql = “SELECT * FROM SGCO_WB1_TestTableIN”;

perm = new SqlStatementExecutePermission(sql);

perm.assert();

statement = odbcConnection.createStatement();

resultSet = statement.executeQuery(sql);

while (resultSet.next())

{

_SGCO_WB1_ProductIN.clear();

_SGCO_WB1_ProductIN.TRANSACTIONNO = resultSet.getInt(1);

_SGCO_WB1_ProductIN.GrossWeight = resultSet.getReal(2);

_SGCO_WB1_ProductIN.TareWeight = resultSet.getReal(3);

_SGCO_WB1_ProductIN.DATEENTRY = resultSet.getDateTime(4);

_SGCO_WB1_ProductIN.insert();

}

}

}

I am Created a Index in the Table for TransactionNO with Allow Duplicates NO.

If i Run the Batch job suppose 10 records are there it is inserted in the Table.

After i am Inserting the New record in the SQL It is not Coming.

Please Give me the Solution For this

You’re trying to insert existing records again, therefore your code will fail with duplicate key exception. (If there is no other bug.)

Please get used to using debugger to identify problems in your code.

By the way, why are you using RunBaseBatch in AX 2012 instead of the SysOperation framework?

You Understand my Question?

My problem is After running the Batch job i am open the Sql Server and Insert the New Records it is not inserted in to Ax table.

Tell me about how to use the Sysoperation Framework?

Yes, I do. But it seems that you don’t understand my answer. Let me explain it slowly:

Let’s say you have a single record with if transaction number 1. When you run your logic for the first time, it will create a record in AX DB with TRANSACTIONNO = 1.

If you run the batch again, it will again read the same record from the external DB and it will try to insert another to SGCO_WB1_ProductIN with TRANSACTIONNO = 1.

Assuming that there is a unique index on the field, inserting multiple records with the same value isn’t allowed and AX will throw an exception and the whole process will stop, therefore it won’t import any new records.

You should stop importing the same records again. Either delete imported records from the external database, or set a flag on them and import only records without this flag.

Regarding SysOperation framework, you’ll find a plenty of information on internet.