Hi everyone,
Today I want to talk about executing SQL statements in X++ on both the AX database and external databases. This is something probably every AX developer will have to do at some point.
You’ll want to do this for many reasons; to execute stored procedures, to improve performance, to get data from an external database, and so on.
I will provide samples for two classes:
– Connection (Execute SQL statement on current AX database)
– ODBCConnection (Execute SQL statement on external database)
I will not cover the ADO connection (CCADOConnection class), because it doesn’t work when you run it on server (or in batch), and I don’t like that. If you do, try to convince me ;-).
Executing direct SQL on the current AX database
When you execute a SQL statement, there are two options:
– either you did a select and you expect a result to be returned
– or you did insert/update/delete and you don’t expect a result.
The first sample is for a SQL statement that returns a result:
public static server void main(Args _args)
{
Connection connection;
Statement statement;
str query;
Resultset resultSet;
;
// create connection object
connection = new Connection();
// create statement
statement = connection.createStatement();
// Set the SQL statement
query = 'select name from CustTable';
// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();
// when the query returns result,
// loop all results for processing
//BP Deviation documented
resultSet = statement.executeQuery(query);
while(resultSet.next())
{
// do something with the result
info(resultSet.getString(1));
}
// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}
Note: this is a main method, put it in a class. Also note that it has to run on server.
Now if you do an update/delete/insert, you will want to do something like this:
public static server void main(Args _args)
{
Connection connection;
Statement statement;
str query;
;
// create connection object
connection = new Connection();
// create statement
statement = connection.createStatement();
// Set the SQL statement
query = "insert into CustTable (AccountNum, Name, RecId) values ('demo', 'demo', 2)";
// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();
//BP Deviation documented
statement.executeUpdate(query);
// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}
You can find more info about the executeQuery() and executeUpdate() methods on msdn:
Statement Class
Executing direct SQL on an external database using ODBC
Again, we have to differentiate between queries that return a result and those that don’t.
The following code sample retrieves records from an external database and processes the result:
public static server void main(Args _args)
{
Statement statement;
str query;
Resultset resultSet;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
;
loginProperty = new LoginProperty();
loginProperty.setDSN('YOURDSN');
odbcConnection = new OdbcConnection(loginProperty);
// Create new Statement instance
statement =odbcConnection.CreateStatement();
// Set the SQL statement
query = 'select name from CustTable';
// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();
// when the query returns result,
// loop all results for processing by handler
//BP Deviation documented
resultSet = statement.executeQuery(query);
while(resultSet.next())
{
// do something with the result
info(resultSet.getString(1));
}
// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}
As you can see, the code is pretty similar. The main difference is that we are using ODBC classes, including the LoginProperty class.
In this example, I use a DSN (Data Source Name) that I configured on the AOS server. The DSN contains a reference to the server and database you want to connect to, and also what user credentials should be used to connect to the database. This is a lot safer than storing them in AX.
If you don’t know how to create a DSN, there are plenty of tutorials on the web.
To update/delete/update, the code is more or less the same:
public static server void main(Args _args)
{
Statement statement;
str query;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
;
loginProperty = new LoginProperty();
loginProperty.setDSN('YOURDSN');
odbcConnection = new OdbcConnection(loginProperty);
// Create new Statement instance
statement =odbcConnection.CreateStatement();
// Set the SQL statement
query = "insert into CustTable (AccountNum, Name, RecId) values ('demo', 'demo', 2)";
// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();
// when the query returns result,
// loop all results for processing by handler
//BP Deviation documented
statement.executeUpdate(query);
// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}
If you feel that something is missing in these examples, just ask.
Related
Michael
on August 19, 2010 at 14:14
Just one comment to the executeUpdate() Method of statement class.
There seems to be (still) a bug in this method – when using it with DML (insert/update/delete) commands. The result values normally should deliver the number of affected rows. (updated, inserted, deleted)
I always get 0 as result value even if records where affected.
Any ideas how to get this number.
According to documentation in MSDN executeUpdate should return the updated row count.
Victor
on October 21, 2010 at 05:04
Hi Michael,
I’ve got the same issue with the executeUpdate() method always returning 0, however many rows have been updated. If you ever happen to have more info on this, I would greatly appreciate if you could share it with me. Thanks!
Jan
on November 16, 2010 at 14:19
HI,
Tried to use a File DSN, but no connection was made. Anything spesial to do when using File DSN?
Regards,
Jan
Klaas Deforche
on November 16, 2010 at 16:04
Hi Jan,
Sorry, I haven’t used a File DSN yet, so I can’t help you with that. I always use a System DSN; are you able to get it working with that?
Tommy Skaue
on June 8, 2011 at 12:51
I had to wrap my code with ttsbegin and ttscommit in order for this to work.
sanjana
on March 21, 2012 at 13:53
How to debug the statement.executeQuery(query) method.
KrMee
on October 25, 2012 at 11:07
Hello Klaas,
I’ve used your example-code to test a ODBCConnection in Dynamics AX 2012.
I always received the error:
“ODBC operation failed.
Unable to log on to the database.
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
Object ‘OdbcConnection’ could not be created
You do not have access to the database specified”
I found a solution on:
http://dynamics-ax-live.blogspot.be/2009/09/error-data-source-name-not-found-and-no.html
after adding the DSN to the 32bit version, it works.
Thanks!!
Ashish
on July 18, 2013 at 15:42
Hi Klaas,
I am trying to execute the stored procedure from Ax. When we run the stored procedure from SQL Server it returns expected results in my case 3990 records inserted in Ax table but when I run the stored procedure from AX and running the Ax Service with same credentials it returns far fewer records (just 130 records). I am using Dynamics AX 4.0 version SP1.
I am calling the view from another database in my stored procedure and have dbo rights on both databases.
Thanks,
Ashish
Klaas Deforche
on July 23, 2013 at 23:22
Hi Ashish,
That shouldn’t be happening :).
It could be executing on a different database maybe, but I’m sure you checked that. Also, you are checking the number of records on SQL db right (just to make sure they aren’t simply in an other company in AX).
If I were you, I would simplify everything in order to find the problem. Eg, instead of executing the stored procedure, I’d run a simple query on one DB to make sure the DB is correct, then execute a simple stored procedure, and then move on to a more complex scenario including an other database.
I hope you find a solution to your problem.
Regards,
Klaas.
Ashish
on July 24, 2013 at 00:40
Hi Klaas,
Thanks for reponse. I am still not sure what was happening with connection class and why it was timing out.I changed the approach and now using the ADO.net approach provided by florian –
http://floditt.blogspot.com/2011/08/using-adonet-with-x.html
With this approach I can set the timeout that I set 20 mins for my case and now stored procedure is running fine and inserted all records.
is there any way to set the timeout with current connection class in dynamics Ax 4.0?
I like your blogs very much and it is very helpful.
Regards,
Ashish
Klaas Deforche
on July 24, 2013 at 19:13
Hi Ashish,
I’m not sure if there is a way to do it with the connection class, I’ve never had a problem with timeouts like you have. I searched for a bit but I couldn’t really find a way.
I’m glad you got it to work though. I have faith in Florian’s code, he knows what he’s doing :). ADO might actually be the better choice too anyway, thanks for posting the link.
Regards,
Klaas.
Sheheryar
on October 23, 2017 at 07:57
My question: https://community.dynamics.com/ax/f/33/p/254386/712467
zohaib
on October 20, 2018 at 13:51
i am connecting external data base by using your code . “Login failed for user —” error is showing
zohaib
on October 20, 2018 at 13:53
by using your code i am connecting external db from ax2012 but following error is appearing “Login failed for user —-“
ishu
on October 29, 2019 at 12:31
What if, we want to insert multiple record in external database?
For eg.
there are 3 records in AX DB then
while select * from xyzTable
{
insert into xDB.Ytable values (V1,v2,v3)
}
Is it the right way of doing it?
Or
Do we some other option in AX?
Klaas Deforche
on October 29, 2019 at 13:41
Hi Ishu,
You can use ISNERT INTO SELECT for that:
https://www.w3schools.com/sql/sql_insert_into_select.asp
You can add a linked server between the AX and External DB so you can join tables from both databases together.
Then you can write one sql statement and execute it. It will be much faster than doing it one by one in a loop.
You can also write a stored procedure on your databas and call that stored procedure instead of writing the sql code in AX.
Ofcourse I’m not saying all of this is best practice, just that you can :).
Hope this hlps
Klaas.
Trackbacks
on April 19, 2024 at 01:54