#GPPT Parsing Returned SQL Data into Rows and Columns


David Meego - Click for blog homepageWhen writing customizations using GP Power Tools as your development tool, there may be times where you need to return a SQL query as a data set to your Trigger Setup script or Runtime Execute Setup script.

It is possible to display a data set to the end user in a SQL Results window using the MBS_SQL_Results helper function. If you have “Goto” actions defined, the MBS_SQL_Results_Goto helper function will display the data and enable the SQL Goto functionality.

But what if we don’t want the data displayed to the user and just need to use the data in your code.

The MBS_SQL_Check_Exists helper function can be used to return the results of a SQL query to as a data set. One of the parameters for this helper function specifies whether data is to be returned. If this parameter is set to false, the helper function can just be used to identify the existence or non-existence of data matching the SQL Query.

If the Return Data parameter is set to true, the full data set of the query is returned as a text field. Each row of the data set is terminated with a carriage return character, char(13), and each column on a row is separated by a tab character, char(9).

If only one field is returned (single row of single column), then it is fairly easy to obtain the data using the substring(MBS_Text_Field, 1, X) function where X is the maximum length of datatype. If desired, the trim() function could also be used to remove leading and trailing spaces.

But what happens if you have multiple rows of multiple columns? How can you parse the data set returned as a text field back into something usable.

Demonstration Project

To show how to parse the data set I will use a demonstration project that you should find useful. The project shows how to create a SQL Query to return customer data with a Parameter List to allow the selection of a range of customers. The query can be has a couple of SQL Gotos defined which allow the selected customer to be opened in Customer Maintenance or for the list of selected customers to be displayed. The SQL Query has been published so it can also be run by users from the SQL Executer window. It can also be called from Runtime Execute as a published script using a helper function to display the SQL Results with Gotos to the user. Finally, you can start the trigger which will add the SQL Query as a menu from the Customer Maintenance window.

In addition to all of the above, the CUSTOMERS2 Runtime Execute Script is an example showing how to parse SQL Data.

The CUSTOMERS SQL Query used is shown below. Note that it is using Dexterity Technical Names in braces, these are converted to SQL Physical Names when the script is executed. Also the parameter placeholders will be replaced when the script is executed

select CUSTNMBR as [Customer ID], {Customer Name}, {Contact Person show}, ADDRESS1, {Country Code}
from {RM_Customer_MSTR}
where {Customer Number} >= ''/*%01%*/ and {Customer Number} <= ''/*%T01%*/

Here is the same script after the conversion of the Dexterity names and the insertion of parameters after selecting the range of customers: AARONFIT0001 to ATMORERE0001.

select CUSTNMBR as [Customer ID], CUSTNAME, CNTCPRSN as [Contact Person], ADDRESS1, CCode
from RM00101
where CUSTNMBR >= 'AARONFIT0001' and CUSTNMBR <= 'ATMORERE0001'

Here is the resulting data set as returned to the MBS_Text_Field text variable by the MBS_SQL_Check_Exists helper function with the parameters: true {Return Data}, false {Show Names}.

AARONFIT0001	Aaron Fitz Electrical	Bob Fitz	One Microsoft Way	
ADAMPARK0001	Adam Park Resort	Roberta Masouras	Suite 9876	
ADVANCED0001	Advanced Paper Co.	Manoj Monat	456 19th Street S.	
ADVANCED0002	Advanced Tech Satellite System	Grant Lasko	8765 66 Ave.	
ALTONMAN0001	Alton Manufacturing	Jennifer Rossini	P.O. Box 3343	
AMERICAN0001	American Science Museum	Andrew MacWilliams	789 North Carlton Place	
AMERICAN0002	American Electrical Contractor	Sue Almassy-Wicker	3456 North Calumet Avenue	
ASSOCIAT0001	Associated Insurance Company	Dmitry Rodin	321 Garden Mall	
ASTORSUI0001	Astor Suites	Business Office	987 West Alaska Ave	
ATMORERE0001	Atmore Retirement Center	Jane Donato	567 W 7th St.	

10 of 10 Rows of 5 Columns Returned.

Example Code

Quick Terminology Note: Row and Columns at the SQL level become Lines and Fields at the Dexterity level.

My first attempt at parsing the data used the Field_ParseText() command to retrieve the lines of up to 255 characters as this command will automatically split data at a carriage return character. This method worked to split the lines, but did not going to work for the next step because the lines returned had been stripped of the tab characters separating the fields.

The next approach was to use the pos() function to locate carriage return characters, char(13) and the substring() function to return a string variable of the line. The min() function was also used in case the length of the line exceeds 255 characters so that we will only return the first 255 characters. Note: Make sure that the SQL Query used will not return more than 255 characters per row.

Now that we have broken the data set into lines, we can use the RW_Token() report writer function to split the line into fields. RW_Token() was added by David Musgrave (me) to version 7.0 of Dynamics GP along with a suite of other RW functions to make it easier for consultants and developers to write reports with Report Writer.

RW_Token() can be used to split a separated list into individual values using any specified separation character. Using the tab character, char(9), with RW_Token() we can retrieve the fields from the line. In our example below we are using a for loop to get the first four fields and assign them to variables. The resulting data is then displayed to the user with a warning statement in “text” mode. Note: Don’t return too many records as each record requires a click to dismiss the warning dialog.

local text MBS_Text_Field;
local integer MBS_Status;

local string l_line;
local string l_field;
local integer l_pos, l_old_pos;
local long l_row;
local integer l_column;
local string l_ID, l_Name, l_Contact, l_Address;

call with name "MBS_Script_Load_SQL" in dictionary 5261, 
	"CUSTOMERS", MBS_Text_Field;

call with name "MBS_SQL_Check_Exists" in dictionary 5261, 
	MBS_Text_Field, true {Return Data}, false {Show Names}, MBS_Status;
case MBS_Status 
	in [OKAY] 
{		warning MBS_Text_Field;
}		l_row = 0;
		l_pos = 1;
		repeat
			l_old_pos = l_pos;
			l_pos = pos(MBS_Text_Field, char(13), l_old_pos);
			if l_pos &amp;gt; 0 then
				l_line = substring(MBS_Text_Field, l_old_pos, min(255, l_pos-l_old_pos));
				l_pos = l_pos + 1;
				if not empty(l_line) then
					increment l_row;
					clear l_ID, l_Name, l_Contact, l_Address;
					for l_column = 1 to 4 do
						l_field = trim(RW_Token(l_line, char(9), l_column));
						case l_column
							in [1]
								l_ID = l_field;
							in [2]
								l_Name = l_field;
							in [3]
								l_Contact = l_field;
							in [4]
								l_Address = l_field;
							else
						end case;
					end for;
					warning text("Record: " + str(l_row) + char(13) + "ID: " + l_ID + char(13) 
								+ "Name: " + l_Name + char(13) + "Contact: " + l_Contact + char(13) 
								+ "Address: " + l_Address + char(13));
				end if;
			end if;
		until l_pos = 0 or empty(l_line);

	in [MISSING] 
		warning MBS_Text_Field;
	else 
		warning MBS_Text_Field;
end case;

You could just display the retrieved data into window fields if desired rather than temporarily placing them into variables. Also, if the window fields are an array, you could simplify the code further using a formula to calculate the array index. For example: l_index = 4 * (l_row – 1) + l_column;

Below is additional code examples that can be used if you need to parse the returned data from a string into another datatype. Note that the local variable declarations will need to be copied to the top of your script.

{

{ Example Code for other Datatypes }

local integer l_Integer;
l_Integer = integer(value(l_field));

local currency l_Currency;
l_Currency = currency(value(l_field));

local boolean l_Boolean;
l_Boolean = (upper(l_field) = TRUE_STRING); { When stored as TRUE or FALSE }
l_Boolean = (value(l_field) <> 0); { When stored as 0 or 1 }

local integer l_year, l_month, l_day;
local date l_Date;
l_year = integer(value(substring(l_field, 1, 4)));
l_month = integer(value(substring(l_field, 6, 2)));
l_day = integer(value(substring(l_field, 9, 2)));
l_Date = mkdate(l_month, l_day, l_year); { Where Date in 'YYYY/MM/DD' format }

local integer l_hour, l_minute, l_second, l_pos;
local time l_Time;
l_pos = pos(l_field, CH_COLON, 1); { Find colon in Datetime string }
l_hour = integer(value(substring(l_field, l_pos-2, 2)));
l_minute = integer(value(substring(l_field, l_pos+1, 2)));
l_second = integer(value(substring(l_field, l_pos+4, 2)));
l_Time = mktime(l_hour, l_minute, l_second); { Where Time in 'HH:MM:SS' format }

}

Below is the configuration file with the example CUSTOMERS project. Feel free to install it and play with the different ways the data can be retrieved and displayed.

Hope you find this example useful.

David

This article was originally posted on http://www.winthropdc.com/blog.

One thought on “#GPPT Parsing Returned SQL Data into Rows and Columns

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.