DB table for status codes / text?

[Daryl Brown]

Hello,

Is there a database table that translates status codes into their text equivalent?

E.g., something that maps a "1900" to "ENDED_OK - ended normally", "1800" to "ENDED_NOT_OK - Aborted", etc.

I know these codes are spelled out on the System Return Codes page in the documentation, but it would be a lot easier to use this info if it can be integrated in a SQL query.

Thanks in advance!

-- Daryl

[Michael A. Lowry]

It's a feature, not a bug.

AAKE v21: MSGTX table empty

[Christoph Rekers]

Hi @Daryl Brown,

I think you're looking for UC_ZUTYP:

Cheers

Christoph  

------------------------------
----------------------------------------------------------------
Automic AE Consultant and Trainer since 2000
----------------------------------------------------------------
------------------------------

[Michael A. Lowry]

UC_ZUTYP defines the status codes associated with each condition. For example, the condition ANY_OK corresponds to status codes 1900–1999.

One can select from among these conditions when defining Dependencies for workflow tasks.

Conditions in workflow task dependencies

See System Return Codes for Executable Objects.

[Daryl Brown]

Thanks Christoph and Michael...I might be able to get away with using that table.

As Michael pointed out, though, that's a table for grouping actual codes together under new name...  It looks like if I'm dealing with a particular AH_STATUS value, I could use this table to decode some of the more common ones (e.g., 1800, 1820, 1900), but not every possible one (e.g., there's no single entry for a 1301, other than the ANY_ACTIVE = 1300-1799).

I'm familiar with the System Return Codes for Executable Objects page...  I take it there isn't a DB table that contains the same mapping you see under the 'System Return Codes and Assigned Task States During Execution' section?  (Are those verbose status values all hardcoded into the product rather than the database?)

[Christoph Rekers]

@Daryl Brown,

You could put these into a VARA...

------------------------------
----------------------------------------------------------------
Automic AE Consultant and Trainer since 2000
----------------------------------------------------------------
------------------------------

[Michael A. Lowry]

It's probably easier to extract the messages from the uc.msl file and then insert them into a new table with the same structure as MSGTX.

Here's a command that will match AE messages in the uc.msl file and output tab-separated values:

cat uc.msl | gawk 'match($0, /^([0-9]{8})([EDF])([ACDEIQW])(.*)$/, a) {print a[1] "\t" a[2] "\t" a[3] "\t" a[4]}'

The columns are:

1. Message number
2. Language
3. Message type
4. Message text

If you want to print just a subset of messages, change the capture groups in the regex.

• Only English messages: change [EDF] to just E.
• Only warning messages: [ACDEIQW] to just W.

From tab-separated values, you can readily generate the SQL statements required to create and populate the table.

Update: I just noticed that the (.*) capture group is excluding lines with diacritics. Moreover, using regular expressions is probably the wrong approach when we know each line is divided into columns by character position. If anyone knows a straightforward way to insert separator characters at specific columns, that would probably be more reliable.

[Christoph Rekers]

...or with just AE Script ;-)

:SET &HND#=PREP_PROCESS_FILE(WIN01@SRV-A,"C:\Automic\v21\automationengine\bin\uc.msl", _
: "00001???ED*","COL=LENGTH","LENGTH_TAB='4,4,2,100'","UC_LOGIN=LOGIN.ALL.WINDOWS")
:PROCESS &HND#
:SET &retcode#=GET_PROCESS_LINE(&HND#,2)
:SET &status#=GET_PROCESS_LINE(&HND#,4)
:PRINT &retcode# => &status#
:ENDPROCESS
:CLOSE_PROCESS &HND#

------------------------------
----------------------------------------------------------------
Automic AE Consultant and Trainer since 2000
----------------------------------------------------------------
------------------------------

[Philipp Elmer]

No need to PREP_PROCESS_<ANYTHING> to access the codes in AE-Script, they are available built-in in two ways:

1. Prefix with two hashtags, ie:

:PRINT ##1800

2. Or use the function "GET_MSG_TXT", ie:

:SET &MSG# = GET_MSG_TXT(1800)
:PRINT &MSG#

GET_MSG_TXT lets you also fill in the "message-inserts", i.e.

:SET &MSG2# = GET_MSG_TXT(00000008,"I faked that one")
:PRINT &MSG2#

------------------------------
Philipp Elmer

Become a member!
https://membership.philippelmer.com
------------------------------

[Michael A. Lowry]

Hi @Philipp Elmer. That's very helpful, thanks.

@Daryl Brown's original question though was about looking up messages completely in SQL. I have been looking for the best way to recreate the MSGTX table - empty since version 21. I thought that separating the columns by tab characters would be a good way to prepare the data for import.

The approach below seems to work well. It inserts tabs between the columns of uc.msl, and writes the output to a new file with column names in the first row.

printf "%s\t%s\t%s\t%s\n" "Msg_Idnr" "Msg_Lang" "Msg_Type" "Msg_Text" > ucmsl.tsv
cat uc.msl | sed 's/.\{10\}/&\t/' | sed 's/.\{9\}/&\t/' |  sed 's/.\{8\}/&\t/' >> ucmsl.tsv

This approach preserves punctuation and quoting characters, as well as diacritics found in French & German messages.

Msg_Idnr        Msg_Lang        Msg_Type        Msg_Text
00000000        D       C       |
00000000        E       C        |
00000000        F       C        |
00000001        D       E       NO DATA (Dateiende, ungültiger Schlüssel, keine Meldung usw.)
00000001        E       E       NO DATA (end of file, invalid key, no message...)
00000001        F       E       AUCUNE DONNEE (fin de fichier, clé non valide, aucun message, etc.)
00000002        D       E       VORHANDEN
00000002        E       E       EXISTS
00000002        F       E       EXISTE
00000003        D       E       GEKÜRZT
00000003        E       E       TRUNCATED
00000003        F       E       TRONQUE
00000004        D       I       MEHR DATEN
00000004        E       I       MORE DATA
00000004        F       I       PLUS DE DONNEES
00000005        D       I       LOCK
00000005        E       I       LOCK
00000005        F       I       VERROUILLER
00000006        D       W       DEADLOCK oder Verbindungsverlust zur Datenbank - Rollback Verarbeitung initiiert. Siehe vorherige Meldungen.
00000006        E       W       DEADLOCK or Connection to database lost - Rollback handling initiated. See previous messages.
00000006        F       W       INTERBLOCAGE ou perte de connexion à la base de données - Restauration initiée. Voir messages précédents.
00000007        D       E       Diese Funktion ist noch nicht implementiert.
00000007        E       E       This function has not yet been implemented.
00000007        F       E       Cette fonction n'est pas encore disponible
00000008        D       E       Unerwartete Antwort auf Meldungstyp '&01'.
00000008        E       E       Unexpected response to message type '&01'.
00000008        F       E       Réponse inattendue au message de type '&01'
00000009        D       E       '&01': Zugriff verweigert
00000009        E       E       '&01': Access denied
00000009        F       E       '&01': Accès refusé

[Philipp Elmer]

Hi @Michael A. Lowry 
nice one! Here are a few thoughts to re-create MSGTX
(I've created a video-tutorial for re-creating the MSGTX-table back in 2021, but it's in German and behind our paywall https://membership.philippelmer.com/courses/meldungstexte-in-die-automic-db-laden/ 😬 A 2 Weeks free trial is available, though)

• I wouldn't use the empty MSGTX table -> whenever you create your own tables in the AE-schema, do use a distinct name-pattern for the tables so they are clearly distinguishable from AE tables, and cannot collide with possible future AE tables.
  In my tutorial, I call the table "MSGTX_Custom".
  I use the same table schema (i.e. columns and datatypes).
• The simplest way (for me) to get the uc.msl into the table was by converting it into a SQL-script.
  Basically just:
  • Take the regular expression (........)(.)(.)(.*)
  • And substitute it with
    insert into MSGTX_CUSTOM \(MSGTX_MSG_IDNR,MSGTX_MSGL_SHORT,MSGTX_MSG_TYPE,MSGTX_TEXT\) values \(\1,'\2','\3','\4'\);

I hope that's useful.

Regards,

------------------------------
Philipp Elmer

Become a member!
https://membership.philippelmer.com
------------------------------