Finance | Project Operations, Human Resources, ...

How can we use select statement in X++ code directly to replace use connect query sql statement?

(0) Share

Report

Posted on by Liya Cheng

         // create connection object
        Connection connection = new Connection();

        // create statement
        Statement statement = connection.createStatement();
        
        // Set the SQL statement
        str query = 'select dbo.SysGetUserIdFromContextInfo()';

        // assert SQL statement execute permission
        new SqlStatementExecutePermission(query).assert();

        // when the query returns result,
        // loop all results for processing
        //BP Deviation documented
        ResultSet resultSet = statement.executeQuery(query);

        while(resultSet.next())
        {
            userId = resultSet.getString(1);
        }

I have the same question (0)

All responses (16)

Answers (0)

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

Hi Apple,

could you let us know what you are trying to achieve? Normally you should use only x++ select statements, and not direct SQL statements.

If you let us know your business requirement, we might be able to provide assistance. Just by looking at your SQL Statement, it's not possible to understand what business problem you are trying to solve.

Was this reply helpful? Yes No
Martin Dráb 236,570 Most Valuable Professional on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

What are you trying to achieve by calling SysGetUserIdFromContextInfo?

Also, I see you've attached tags "Financial Management" and "Management Reporter", but you haven't haven't explained how your code is related to them. Could you do it now, please?

Was this reply helpful? Yes No
Liya Cheng on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

Hi Nikolaos

Now, we want to get the userId from the above code. but in our test case, sometimes, it will get the wrong userId. and i never seen the way to use select dbo.SysGetUserIdFromContextInfo(), i don't know what is the "dbo.SysGetUserIdFromContextInfo()" , i only know select table, but the "dbo.SysGetUserIdFromContextInfo()"is not a table. so i want to know maybe we can have a way to replace the way to get userId from the same dbo.SysGetUserIdFromContextInfo() to ensure it will get the correct userId.

Was this reply helpful? Yes No
Martin Dráb 236,570 Most Valuable Professional on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

Why don't you simply call curUserId()?

What does it have to do with Management Reporter?

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

UserId of what? Could you please share the business requirement that you're trying to solve. Thanks! Which user id do you want to get, and what do you want to use it for?

In x++ you can get the user id of the current session by calling curUserId() function.

Was this reply helpful? Yes No
Liya Cheng on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

i want to select it to get userId, but i don't know why the test use calling SysGetUserIdFromContextInfo to get (PS: This is created by other people, i only check it.)? i don't know what is it. is a class or method? i never seen

Was this reply helpful? Yes No
Liya Cheng on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

the test case use curuserId to get userId, and also need calling dbo.SysGetUserIdFromContextInfo() to get userId

Was this reply helpful? Yes No
Liya Cheng on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

the expected userIdd is admin, but sometime it will return AOSUser, this is flaky issue.

Was this reply helpful? Yes No
Martin Dráb 236,570 Most Valuable Professional on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

It's not AX application object at all. It's a function defined in SQL Server.

I'm afrad we can't tell you why the other people decided to use it and whether it's a bug or not, because you told us absolutely nothing about where the code is used. You should ask the other people.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: How can we use select statement in X++ code directly to replace use connect query sql statement?

Hi Apple,

you still didn't tell us what are you expecting to get.

You said that you want "UserId".. But UserId of what?

As we explained, you get current user id by curUserId() function.

Could you please verify if this is what you need?

However the calls to the database are not done by the current user, they are done by the AOS. Users don't even have access to the database.

So whoever implemented the original code did not know what they were doing.

Was this reply helpful? Yes No