Import from SQL table(not axapta) to Axapta tables

Hi, guys

I need to import data from SQL table which is populated by other process(outside of axapta application) to Axapta tables.
How to do that?
This table could be on the same server where Axapta db is.

thank you

Hi,
Personally, I would like to write a job in Axapta.
-Read data from external database using class ODBC. Here is the sample code:
static void ODBCRead(Args _args)
{
LoginProperty LP = new LoginProperty();
OdbcConnection myConnection;
Statement myStatement;
ResultSet myResult;
;

LP.setDSN("Northwind");

myConnection = new OdbcConnection(LP);

myStatement = myConnection.createStatement();
myResult = myStatement.executeQuery("SELECT * FROM Customers");

while (myResult.next())
{
info(strfmt("%1 %2", myResult.getString(1), myResult.getString(2)));
}
}

-insert data to Axapta and include all the logic that may have.

Hope this can help.

thank you for your response, i have used what you suggested, it worked.

But using the same objects is it possible to call stored procedure, instead of ExecuteQuery or ExecuteUpdate?

I need also create another process, which after table in Axapta is populated from ImportTable(not Axapta table), update some fields in ImportTable.
I would rather do it in stored proc, using "Update ImportTable select ....from Axapta table", it would be much more effective, that looping again thru records.
Do u know the way to call stored proc?
thank you.

Hi
I tried using Store Proc in Axapta sometimes ago, but I did not get too far as Axapta doesn't support developer to call SProc in standard.

You may want to look at the ADO connection class (try "find" method on AOT to get the class name) and find out how to call a SP.

Regards,

Hi,

You can execute a stored proc from Axapta like -

Connection Con = new Connection();
Statement Stmt = Con.createStatement();
ResultSet R = Stmt.executeQuery('<STORED_PROCEDURE_NAME>');

while (R.next())
{
         print R.getString(1);
}

I have used stored proc from Axapta in the past to import data from another SQL database into Axapta. Overall it works ok. But there are quite a few drawbacks using this route.

Regards,

Harish Mohanbabu

Guys can any body Reply me for, how to insert a record in Sql Server from Ax4.0 with out using Axapta Table.

Thanks in advance!

sudvaep

Hi Sudvaep,

I guess you want to write to external SQL tables from Ax 4.0.  I would write a stored procedure in SQL and call that from Ax.  For example

----------------------------------------------------------------

....................................

sqlStat = strfmt("%1 %2", <Name of stored procedure>, curext());

stmt.execute(sqlStat);

----------------------------------------------------------------

In the above string, I am formatting the stored procedure name along with an input parameter.  I like using stored procedure as I find it an elegant option.  But there is no reason why a SQL statement can't be used instead.

Hope this helps,