Infor M3 #5 -- How to run SQL queries in MAK

How to run SQL queries in MAK

Sometimes we may find difficult to use normal database methods to retrieve data from DB tables for complex logic, sometimes we may end up creating multiple indexes to get the accurate results in a single shot instead of looping the records and filtering them by checking few conditions.

Isn't this process tedious? to make your work easier there is a facility to run SQL queries directly from MAK.

We have two types of SQL query execution in MAK as mentioned below

1. SQL query on a specific table
2. SQL query on multiple tables

Let us see the syntax and the behaviour of them in MAK

1. SQL query on a specific table

Well, this is pretty much as select * from XXXXXX query, which would give us the entire table's data. We execute SQL queries on the session object, which holds the current user's session.

Syntax:

session.OPEN_SQL(query, def, nrOfRecords);

Return type:

Byte[][]

Parameters

• query refers to the SQL query you want to pass i.e., select * from table where conditions
• def refer to the table on which the query details to be saved
• nrOfRecords refers to the maximum number of records you wish to retrieve

This method returns a two-dimensional array of type byte, we should convert byte into a MvxRecord then assign it to the DB Table object then the retrieval process continues normally.

It is obvious that when we handle SQL through code, we must handle SQL exceptions as well otherwise we will end up with dumplog.

All the records retrieved from the session object would be stored into Java Heap space, so we should have a limit in providing the number of records to be retrieved.

Let us take an example to retrieve all items from MITMAS where CHID is "MyUserID"

String userId = "MyUserID";

String query = "select * from MITMAS where MMCHID='"+userId+"'";

String def = "MITMAS";

int nrOfRecords = 1000;

try {

                      for (byte[] record_byte : session.OPEN_SQL(query, def, nrOfRecords)) {

if (record_byte == null)//End of records

break;

MvxRecord MITMAS_record = new MvxRecord(record_byte); // creates a mvxrecord

ITMAS.setRecord(MITMAS_record);//Assigns the record to MITMAS

ITMAS.getITNO(); // get data as usual

}

} catch (Exception e) {

                       //There are no records exists

}

As we can't predict the number of records present in the table we make a null check to make sure the program won't get terminated abnormally

2. SQL query on multiple tables

Well, this method is used to execute either limited columns retrieval or join queries in SQL, which would give us the data based on the columns we have written in the query.

Syntax:

session.SQL(query, nrOfRecords);

Return type:

MvxStrg[]

Parameters

• query refers to the SQL query you want to pass i.e., select column_name from table where conditions
• nrOfRecords refers to the maximum number of records you wish to retrieve

This method returns a single-dimensional array of type MvxString (Stream of characters based on the query), you should divide the MvxString using a delimiter \t to segregate the columns data.

you must know the position of each column in the retrieved MvxString.

It is obvious that when we handle SQL through code, we must handle SQL exceptions as well otherwise we will end up with dumplog.

All the records retrieved from the session object would be stored into Java Heap space, so we must have a limit in providing the number of records to be retrieved.

Let us take an example to retrieve the work centre name from MPDWCT by providing the work centre from  MPDOPE

String query = "select PPPLNM, POOPNO, PPDEPT from MPDWCT, MPDOPE where PPPLGR= POPLGR and POPRNO='ProductXX';

int nrOfRecords = 10;

try {
boolean isFirstRecord = false;

                      for (MvxString record_string : session.SQL(query, nrOfRecords)) {

if (record_string == null)//End of records

break;
if(!isFirstRecord ){

String[] columns = record_string.toString().split("\t");

columns[0]; // PLNM

columns[1]; // OPNO

columns[2]; // DEPT
      }else{
isFirstRecord =true;
}

}

} catch (Exception e) {

                       //There are no records exists

}

As we can't predict the number of records present in the table we make a null check to make sure the program won't get terminated abnormally.

Advantages

• We can avoid creating indexes to get data on those columns which are not in any index
• No more bothering about Fields sequence in any index
• No more looping through records to filter the data on some checks
• We can use aggregate functions to fetch sum, avg, count etc., in session.SQL() method

Disadvantages

Well, they told the great power comes with great responsibility 😉

As said earlier, data of session gets stored in Java Heap memory so we should be very careful while choosing the number of records to be retrieved. If heap size is completely used then there would be a dumplog generated.

That's it, come back here and tell us about the before-and-after. I bet you'll have something to say!

Please let me know your thoughts in the comment section below!

If you enjoyed this post, I'd be very grateful if you'd help it spread by emailing it to a friend, or sharing it on Twitter or LinkedIn.

In our next post let us see about Infor M3 Standard Search feature

Thanks for reading!

--Saiteja Saadi