What is difference between standard query and SQL query?

(0) Share

Report

Posted on by Community Member

When I insert data into database with standard query(Ex1), It is correct to insert data.
But When I insert data into database with SQL query(Ex2), local language are broken.
What is difference between both query?
(My local Language is Korean)

Ex1) Table.field = '한글'
Table.Insert();
Field in Table : 한글 (correct)

Ex2) query = insert into Table (field) values ('한글');
excute(query);
Field in Table : ???? (incorrect)

also I using AX2012 R3 CU8, SQL server 2014.

I want to use SQL query to insert no AX database.

If you know about this problem, please tell me how to resolve this problem.

Thank you.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Mea_ 60,284 on at

Like (0)

Report

There are couple of reasons why using direct SQL is a bad idea in AX.

1) It does not use AX business logic.

2) It is almost impossible to find this code. In AX we have wonderful feature "cross references" but you can't build cross references with direct sql.

3) It is easy to do mistakes because there is no compile time check and hard to find it.

Why did you decide to go this way ?

Was this reply helpful? Yes No
Hariharans87 3 on at

Like (0)

Report

You could try below query. Include N before the value means pass string as NVarchar

insert into Table (field) values (N'한글')

It seems you are directly insert records in AX database without knowledge of AX. It is not recommended. Because, you will face recid and datareadid issues.

Let me know your scenario. We will try to give some recommended suggestions.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi,

At some point you need to use SQL queries, those scenarios could be like

1. If you want to get data from external source

2. To call or execute stored procedures etc.

These can be done using 2 methods,

1st if you want to execute SQL statements in current AX Database then go for Connection Class.

2nd if you want to get data from external sources then go for ODBCConnection Class.

Now come to the second part of your question i.e. why use TableBuffer.insert() to insert data.

In AX, database(SQL) operations are embedded in X++. So using the table buffer you can do all the related tasks such as insertion, updation, selection, deletion etc easily. You don't need to write SQL for each of the operations. Another reason can be that, using Table buffer makes process fast and robust. It reduces the code size, and many more.

So if you want to use SQL statements then go for it, its just a matter of choice but yes it is not a best practice and hence not recommended for a developer.

Regards,

Gaurav Singhal

Was this reply helpful? Yes No
Hariharans87 3 on at

Like (0)

Report

I am only thinking about your insert statement, because you may face recid and dataareaid issues. e.g. recid is coming from AX logic.

Refer into this link for how to access the external db and insert data in AX dev.goshoom.net/.../connection-to-external-database

Was this reply helpful? Yes No
Suggested answer

Saju Kanakaraj 605 on at

Like (0)

Report

Hi Leo,

Please try this code.

You need to configure odbc(32 Bit) connection.

public static server void TestSqlinsertAX(Args _args)

{

Statement statement;

str query;

LoginProperty loginProperty;

OdbcConnection connection;

;

loginProperty = new LoginProperty();

loginProperty.setDSN('SQLTest');

connection = new OdbcConnection(loginProperty);

statement = connection.CreateStatement();

query = "insert into SqlTest (id, RecId) values ('Test', 2)";

new SqlStatementExecutePermission(query).assert();

statement.executeUpdate(query);

CodeAccessPermission::revertAssert();

}

Was this reply helpful? Yes No