Writing Passthrough SQL or Range Where Clauses

Writing Passthrough SQL or Range Where Clauses
==============================================

When creating code for Passthrough SQL statements or Range Where clauses, it is important to ensure that this SQL code is compatible with all regional settings and sort orders. It is also important to ensure that when field values are passed SQL that they handled correctly.

Below are some guidelines to follow:


1) Whenever a date value is passed to SQL, use the sqlDate() global function to ensure that the date is in YYYYMMDD format. If you use str() the date format used will depend on the regional settings and may be either MM/DD/YYYY or DD/MM/YYYY. SQL does not accept DD/MM/YYYY format and this make cause intermittent errors which occur after the 12th day of each month.

2) Whenever a string value is passed to SQL, use SQL_FormatStrings() global function to ensure that strings are wrapped in single quotes and that if a single quote exists in the string that a second single quote is added to stop the premature termination of the string.

3) Whenever a range is selected between a minimum and maximum values on a string field, the maximum value used by Dexterity may not be the correct maximum value for SQL depending on the Sort Order and collation of the SQL server. Using the Dexterity fill command fills the field with ASCII 255 for the length of the string. However, depending on the sort order ASCI 255 ( ) will not be the highest value and the where clause will return no results. Below is an example which uses the system 9600 command to obtain the maximum character value for a system's sort order and then uses this to build a where clause using this value.


local string l_MaxChar;
local integer l_Result,l_Length;

system 9600, table SOP_HDR_WORK, l_MaxChar, l_Result;

if empty('End Location Code') or filled('End Location Code') then
fill 'End Location Code';
l_Length = length('End Location Code');
clear 'End Location Code';
'End Location Code' = pad('End Location Code', TRAILING, l_MaxChar, l_Length);
end if;

range table SOP_HDR_WORK where physicalname('Location Code' of table SOP_HDR_WORK) + CH_SPACE + CH_GREATERTHAN + CH_EQUAL + CH_SPACE
+ SQL_FormatStrings('Start Location Code') + CH_SPACE + SQL_AND + CH_SPACE
+ physicalname('Location Code' of table SOP_HDR_WORK) + CH_SPACE + CH_LESSTHAN + CH_EQUAL + CH_SPACE
+ SQL_FormatStrings('End Location Code');


David Musgrave [MSFT]
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions
http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.