1
SyteLine Application Messages in SQL
Last updated on 8/21/01 by Tom Blosser
This document deals with creating, maintaining, and retrieving messages within the T-SQL code base of SyteLine on SQL. It doesn't deal with creating/maintaining messages within the WinStudio Client portion, although some similar constructs are being put in place there. Issues relating to message presentation and handling user prompts from within WinStudio are dealt with in other documents that will be referenced here.
I. Application Messages - an overview
a)Tables
There are three SQL tables that participate in the Application database message system.
ApplicationMessages - Actual messages are stored here. These messages can be straight literal text or can contain substitution keywords (%s or %t). All messages are keyed by a MessageNo. The %s substitution keywords are replaced by text from another message in the ApplicationMessages table. The %s substitution text is mapped via records in the ObjectBuildMessages table. The %t substitution keywords are replaced by literal text passed into a call to GetErrorMessageTextSp. The GetErrorMessageTextSp stored proc is used specifically for constructing messages containing %t substitution keywords. Records in the ApplicationMessages table are maintained via the ErrorMessages.sql script file located in Source Safe project $/ApplicationDB/SQL Scripts.
ObjectMainMessages - All messages in the ApplicationMessages table are referenced (or accessed) via an ObjectName. This table contains the list of defined ObjectNames, each of which references a specific message via the MessageNo value. Records in the ObjectMainMessages table are keyed by 'ObjectName' and 'MessageType'. Therefore several messages can exist for the same ObjectName as long as each has a different MessageType. There are several available MessageTypes, only a few of which are relevant to the SyteLine on SQL project. Relevant "Message Types" are discussed within the specific message descriptions contained in this and other referenced documents. A list of all possible MessageTypes is included at the end of this document. Records in the ObjectMainMessages table are maintained via script files located in the Source Safe project $/ApplicationDB/Messages. Each of the message script files (*.msg) contains all of the ObjectMainMessages definitions that relate to the "system as a whole", to a specific table, to a specific column, or to a specific stored procedure. All SyteLine system base messages and/or other text strings that relate to the system as whole are maintained in file BaseMessages.msg (e.g. ObjectNames 'E=CmdFailed' and '@customer'). All other messages and/or text strings are maintained in table, column, or stored procedure specific files. For instance, file "coitem.msg" contains scripts that define Message Objects for all of the messages related to constraint and trigger processing for table coitem. File "CoCustomerValidSp.msg" contains scripts that define Message Objects used during processing of the CoCustomerValidSp stored procedure. Message files are named and function according to the following formula:
BaseMessages.msg - contains ObjectNames for general Syteline base messages and substitution "Text Aliases". These messages are not specific to a single table or stored procedure. This will account for most of the messages that are not associated with a specific constraint.
<tablename>.msg - where <tablename> is the non-hungarianized table name as it appears in the SyteLine_App SQL database. Messages referenced here are specific to the table named. Most of these will be Foreign Key constraint messages.
<StoredProcName>.msg - where <StoredProcName> is the Hungarianized Stored Procedure name as it is stored in the SyteLine_App database. Messages referenced here are specific to the named stored procedure. Using the appropriate Message Type, up to 18 different messages can be defined for a single Stored Procedure.
ObjectBuildMessages - This table has a child relationship to the ObjectMainMessages table. An ObjectMainMessage record may or may not have child records in this table. Existence of child records depends on whether there are '%s' substitution keywords in the message text referenced by the ObjectMainMessage. There will be one record in ObjectBuildMessages for each occurrence of '%s' in the main message. Records in the ObjectBuildMessages table are keyed by 'ObjectName', 'MessageType', and 'SequenceNo'. Each ObjectBuildMessages record references another message which will be substituted in the appropriate %s placeholder. The 'SequenceNo' determines which %s in the message string is to receive the targeted substitution. Messages are referenced by MessageNo if MessageNo > 1 or by SubObjectName if MessageNo = 1. The SubObjectName is simply an ObjectName in the ObjectMainMessages table with a MessageType of 5. A value should be included in the SubObjectName column for all substitutions based on table or column names (e.g. @customer or @customer.cust_num).
Misc. Notes: - The ObjectBuildMessages table only comes into play for messages assembled using calls to GetErrorMessageSp and GetErrorMessageTextSp. These will be explained later in this document. The SyteLine base messages (e.g. 'E=Exist1') do not use the ObjectBuildMessages table to get their substitution values. TheSyteLine base messages use &1, &2, etc. as substitution placeholders. Substitutions here are taken from other message ObjectNames (e.g. @tablename, @tablename.column) which are passed in as parms to the MsgAppSp stored proc.
b) Maintaining Messages
As mentioned above, every message MUST have at least one entry in the ApplicationMessages table and the ObjectMainMessages table. Records in the ObjectBuildMessages table are optional depending on the requirements of the message. Records between the ApplicationMessages and ObjectMainMessages tables are joined via the MessageNo column. A record for a given MessageNo must exist in the ApplicationMessages table before a record referencing that MessageNo can be inserted into the ObjectMainMessages table. Adding records to each table is detailed below.
ApplicationMessages table - Scripts for maintaining messages in the ApplicationMessages table are found in file ErrorMessages.sql located in Source Safe project $/ApplicationDB/SQL Scripts. The AddApplicationMessageSp can be used to add a message or alter the text of a message if it already exists. The following example is taken from the ErrorMessages.sql file.
EXEC AddApplicationMessageSp
100226 -- MessageNo - Unique Message Number
, 'The %s (%t) entered already exists.' -- MessageText.
Go
ObjectMainMessages and ObjectBuildMessages tables - Scripts for maintaining records in the ObjectMainMessages and ObjectBuildMessages tables are found in *.msg files located in Source Safe project $/ApplicationDB/Messages. Types of *.msg files are discussed above. The AddObjectMainMessageSp and AddObjectBuildMessageSp procedures are used to add records to the ObjectMainMessages and ObjectBuildMessages tables respectively. These procedures will not try to update a record if that record exists. Attempting to add an existing record will simply fail here. To update (or replace) an ObjectMainMessage or ObjectBuildMessage record using these procedures, you must first delete the existing entry in the table. The following example of adding messages for Foreign Key constraint coitemFk1 is taken from the coitem.msg file.
EXEC AddObjectMainMessageSp
'coitemFk1'-- Object Name
, 17-- Message type 17 (FK Constraint message)
, 100005-- Message No for template - The %s, %s combination entered …
, 16-- severity
, 'This message is displayed when Customer specified does not exist.' -- Msg description
, 0-- Table Object Type
GO
EXEC AddObjectBuildMessageSp
'coitemFk1'-- ObjectName
, 17 -- Message type 17 (FK Constraint message)
, 1-- sequence number
, 1-- MessageNo (1=No-op message number)
, '@coitem.cust_num' -- Object name for 'Customer' text.
GO
EXEC AddObjectBuildMessageSp
'coitemFk1'-- ObjectName
, 17-- Message type 17 (FK Constraint message)
, 2-- sequence number
, 1-- MessageNo (1=No-op message number)
, '@coitem.cust_seq' -- Object name for 'Ship To' text
GO
Note: The fourth parm to AddObjectBuildMessageSp is a MessageNo. If MessageNo > 1, then the substitution text will be retrieved at runtime by looking up that MessageNo in the ApplicationMessages table. If MessageNo = 1, then the ObjectName specified in optional parm 5 will be used to do another lookup in the ObjectMainMessages table to retrieve the message text from the ApplicationMessages table. When the substitution text is a table name, a column name, or has an existing ObjectName reference in the ObjectMainMessages table (e.g. '@!BrokerageInvoiceAmount'), then use 1 in parm 4 and the appropriate ObjectName in parm 5. This should be the case in most of our messages. Otherwise, use the appropriate MessageNo designation in parm 4 and omit parm 5.
c) Retrieving Messages
Messages are assembled/retrieved through a few different methods. Retrieved messages are passed back to WinStudio for presentation either through OUTPUT parms on a Stored Procedure or through RaiseErrorSp in a trigger. In the SyteLine on SQL project, most messages will be assembled and retrieved using either the MsgAppSp or MsgAskSp procedures. These procedures are discussed below under a section titled Standard SyteLine Application Messages. If cases arise where a message needs to be retrieved that is not a Standard SyteLine Application message, use either the GetErrorMessageSp or GetErrorMessageTextSp procedures. These procedures are discussed below under a section titled Miscellaneous Messages.
II. SyteLine on SQL Message Classes
There are three basic classes of messages that we will be dealing with in SQL for the SyteLine project. They are
- Constraint Messages (Foreign key, Primary key, Unique, and Check constraints)
- Standard SyteLine Application messages (error, warning, informational)
- Miscellaneous Messages (any other required messages not covered in the first two classes.)
a) Constraint Messages
The messages we associate with various constraints in our SQL application database are retrieved from the system at runtime by the Application Middleware layer. When a constraint condition in SQL has been violated, SQL throws an exception for that constraint name which in turn is caught in the Middleware. These messages must therefore be named according to the Constraint name to which they apply. Any parameters to be included in the message must be referenced and extracted from within the message system using the ObjectBuildMessages table. See "SyteLineForeignKeys.doc" located in the Source Safe Documentation folder for details on creating/maintaining Foreign Key constraint messages. At some point, we will attempt to programmatically generate scripts for creating all other required constraint messages. At present, developers need only be concerned with creating messages for constraints of type "Foreign Key".
b) Standard SyteLine Application Messages
(Note: Please refer to document "\\TOMCAT\SyteLineDev\Documentation\Presenting Messages In SyteLine on SQL" for details on displaying and processing messages that are retrieved in SQL Stored Procedures/Triggers.)
This class should cover most of the non-constraint type of messages we will use in this project.To ease the creation and assembling of SyteLine application messages in SQL, we have created MsgAppSp and MsgAskSp stored procedures which can be used to simulate the lib/msg-app.i calls that would be found in Progress code. (It is assumed that most, if not all, stored procedures represent business logic taken from some piece of progress code.) To speed up the development process, we're proposing that you use the same message that is coded in the equivalent progress code and convert it to MsgAppSp or MsgAskSp calls in the Stored Procedure (e.g. cut, paste, and massage it or simply type it in the new syntax). MsgAppSp and MsgAskSp are described below:
MsgAppSp retrieves a non-conditional prompt message (presented with only an OK button) into a single specified OUTPUT variable. Up to 15 optional string substitution parms may be passed.
EXEC MsgAppSp
@Infobar OUTPUT-- Receives formatted message
, @BaseMsg -- Base message template (e.g. 'E=Exist1')
, @Parm1 -- Message substitution parm. (e.g. Message ObjectName or
literal text
, @Parm2
…
, @Parm15
MsgAskSp retrieves a conditional prompt message (presented with two or more buttons) and returns the message with the buttons stripped off into a PromptMsg OUTPUT variable and the Button specifications into a PromptButtons OUTPUT variable.
EXEC MsgAskSp
@PromptMsg OUTPUT -- Receives formatted message
, @Buttons OUTPUT-- Receives list buttons to be included in message presentation
, @BaseMsg -- Base message template (e.g. 'Q=ExistForIs0NoYes')
, @Parm1 -- Message substitution parm. (e.g. Message ObjectName or
literal text
, @Parm2
…
, @Parm15
Subsequent calls to either will concatenate messages into the first OUTPUT variable. In the case of MsgAskSp, the PromptButtons OUTPUT variable will always be overwritten with the buttons for the latest messages retrieved.
The following compares a lib/msg-app.i call with the equivalent MsgAppSp call.
Msg-app.i
{lib/msg-app.i std-msg '' V=NoCompare '@item.cost-method'
'@:item.cost-method:C'}
{lib/msg-app.i std-msg std-msg I=IsCompareNot0
'@item.cost-type' '@:item.cost-type:S' '@item'}
MsgAppSp
EXEC @Severity = MsgAppSp @Infobar OUTPUT, 'I=NoCompare'
, '@item.cost_method'
, '@:item.cost_method:C'
EXEC @Severity = MsgAppSp @Infobar OUTPUT, 'I=IsCompareNot0'
, '@item.cost_type'
, '@:item.cost_type:S'
, '@item'
Aside from requiring two output parms, the MsgAskSp is called the same way. The following is an example of a call to MsgAskSp to get a conditional action message.
MsgAskSp
EXEC @Severity = MsgAskSp @PromptMsg OUTPUT, @PromptButtons OUTPUT
, 'Q=ExistForIs0NoYes'
, '@item'
, '@item.item'
, @Item
Notes:
-Since the first OUTPUT variable carries it's previous contents with it, we do not need to specify it as an argument twice as is currently done in the progress code (e.g. lib/msg-app.i std-msg std-msg I=IsCompare2 ' …).
-All parms must be separated by a comma.
-All literal parms must be surrounded by single quotes (e.g. 'V=NoCompare', '@item.cost_method'). Literal parms include the base message name as well as all non-variable substitution parms.
-The vertical bar delimiter for buttons has been removed from the Message Names. (e.g. 'Q=ExistForIs0|No|Yes' in progress is 'Q=ExistForIs0NoYes' in SQL.)
-Parms representing actual variable values to be passed into the message should be SQL variable names with NO single quotes. (e.g. @Item - the last parm in the MsgAskSp example above)
-Use the SQL version of the table or column name when referencing that object as opposed to the progress name ('@item.cost-method' in progress becomes '@item.cost_method' in SQL)
-Return value from MsgAppSp or MsgAskSp should always be put into the @Severity
variable used to return the success/failure status. At present, MsgAskSp always returns 0 since it is assumed the actual error status will be determined on the client (WinStudio) when the conditional prompt is processed.
In all but one case, the developer should not have to do anything to create the actual messages and parms for the Standard SyteLine Messages in the ApplicationMessages tables. The base messages have already been populated. Table, column, and list-pair parms (e.g. '@tablename', '@tablename.column', '@:tablename.column:code') will be added to the message tables at some point in the future. Since message values for these parms are not currently in the database, when the messages are presented, the parms themselves will be substituted in the message.
The one case where the developer may have to create entries in the ObjectMainMessages and ApplicationMessages tables relates to data substituted into messages from the Progress sys-text table. The SQL sys_text table will no longer be used for message text of this sort, so the developer will need to create the equivalent text as a "message" in the ObjectMainMessages/ApplicationMessages tables. The ObjectName for this type of substitution text should be a Hungarianized version of the text with no spaces preceded by characters '@!' (e.g. '@!FreightVoucherAmount'.) We'll refer to this type of message as a "Text Alias" since it's ObjectName represents the literal text (in English). This can be illustrated with the following example.
Msg-app.i call in Progress
lib/msg-app.i std-msg std_msg E=MustCompare=
"entry(1, symtext.sys-test.txt[6])", "entry(2, symtext.sys-text.txt[6])"
Note: Records in the sys-text table are keyed by sym_prog, which is selected according to the program name to which they apply. In the case above, the appropriate record is selected by sym_prog = 'po/lcap2'.
MsgAppSp call in SQL
EXEC @Severity = MsgAppSp @Infobar OUTPUT, 'E=MustCompare='
, '@!FreightVoucherAmount'
, '@!FreightInvoiceAmount'
In this example, the text being referenced in the sys-text table is "Freight Voucher Amount" and "Freight Invoice Amount" respectively. Text Alias messages have been created for each in the our ObjectMainMessages/ApplicationMessages tables. The following statements from the ErrorMessages.sql file and the BaseMessages.msg file were used to create the '@!FreightVoucherAmount' Text Alias message object.
-- From ErrorMessages.sql
EXEC AddApplicationMessageSp
102323-- MessageNo
, 'Freight Voucher Amount'-- Message text
GO
-- From BaseMessages.msg
EXEC AddObjectMainMessageSp
'@!FreightVoucherAmount'-- ObjectName
, 5 -- Message Type - 'Sp Step 1'. Use 5 for all Text Alias messages.
, 102323 -- MessageNo
, 0 -- Severity. Use 0 for all "Text Alias" messages.
, 'This is a base message for substitutions.' -- Message description
, 2 -- Object Type - 'stored procedure'. Use 2 for all "Text Alias" msgs.
GO
c) Miscellaneous Messages
At this point, it is not clear exactly where we will encounter the need to create and retrieve messages that don't fall under the first two classes. Currently, there are several cases where messages in Sps and Triggers are being retrieved using GetErrorMessageSp and GetErrorMessageTextSp. These were coded, however, prior to creation of the MsgAppSp and MsgAskSp standards. Most, if not all, of these can probably be converted to MsgAppSp or MsgAskSp calls. However, if the need arises, GetErrorMessageSp and GetErrorMessageTextSp are used to retrieve messages as follows. Note: Both of the samples below were done prior to utilizing MsgAppSp and both can now be converted to use MsgAppSp with a Standard SyteLine Application message.
a) GetErrorMessageSp
Use GetErrorMessageSp to retrieve a message where the referenced message contains either no substitution placeholders or placeholders of type %s. Type %s placeholders are substitution made via child records in the ObjectBuildMessages. No variable substitutions are passed in as parms to the Stored Procedure. The following is a call to retrieve a stored procedure specific message using GetErrorMessageSp.
EXEC @Severity = GetErrorMessageSp
'CustomerValidSp'-- ObjectName. In this case, ObjectName is the Stored Procedure name.
, 8-- MessageType (8 means Data Entered is not valid)
, @Infobar OUTPUT-- Output parm to receive formatted message.
The result is that @Infobar = The Customer entered is not valid.
Notes:
From within the ObjectMainMessages table, ObjectName 'CustomerValidSp' references MessageNo 100001. The text of MessageNo is