Customer Report

### Defining and Running an ABL Procedure Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Demonstrates how to define a simple procedure named 'greet' that accepts an input parameter and displays a message, followed by an example of running the procedure. ```ABL PROCEDURE greet: DEFINE INPUT PARAMETER cName AS CHARACTER NO-UNDO. DISPLAY "Hello, " + cName. END PROCEDURE. RUN greet("Bob"). ``` -------------------------------- ### ABL Program Structure Example Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Explains a basic ABL program structure, showing a simple "Hello, World!" output using DISPLAY and a FRAME. Mentions comments and case-insensitivity. ```ABL /* HelloWorld.p */ DISPLAY "Hello, World!" WITH FRAME f1 TITLE "Welcome". ``` -------------------------------- ### ABL DO General Loop Example Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Provides an example of a general-purpose loop in ABL using DO with a counter variable to iterate a fixed number of times and display the counter. ```ABL DO i = 1 TO 5: DISPLAY i. END. ``` -------------------------------- ### ABL FOR EACH Database Loop Example Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Illustrates iterating through database records (customer) using FOR EACH with a WHERE clause and NO-LOCK for read-only access, displaying selected fields. ```ABL FOR EACH customer NO-LOCK WHERE customer.country = "USA": DISPLAY customer.name customer.balance. END. ``` -------------------------------- ### Commenting Code Example (Best Practice) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Provides an example of adding a comment to explain the purpose of a code block that iterates through customer records to update balances. ```ABL /* Updates customer balance to zero if negative */ FOR EACH customer EXCLUSIVE-LOCK WHERE customer.balance < 0: customer.balance = 0. END. ``` -------------------------------- ### ABL Output and Frame Definition Example Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Explains how to define a FRAME to structure output and then use DISPLAY within a FOR EACH loop to show database fields within that frame. Also shows using MESSAGE for simple alerts. ```ABL DEFINE FRAME f1 customer.name LABEL "Name" customer.balance LABEL "Balance" WITH 10 DOWN TITLE "Customers" CENTERED. FOR EACH customer NO-LOCK: DISPLAY customer.name customer.balance WITH FRAME f1. END. MESSAGE "Query complete." VIEW-AS ALERT-BOX. ``` -------------------------------- ### Generating HTML with ABL SpeedScript Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Shows an example of an HTML file ('CustomerList.w') embedding ABL SpeedScript code within ` ``` -------------------------------- ### Defining an ABL Class (Customer) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Provides an example of defining a basic ABL class named 'Customer' with public properties (some with private setters), a constructor to initialize properties, and a method 'GetBalance' that retrieves data from a database table. ```ABL /* Customer.cls */ CLASS Customer: DEFINE PUBLIC PROPERTY CustNum AS INTEGER NO-UNDO GET. PRIVATE SET. DEFINE PUBLIC PROPERTY Name AS CHARACTER NO-UNDO GET. SET. CONSTRUCTOR PUBLIC Customer (INPUT iCustNum AS INTEGER, INPUT cName AS CHARACTER): CustNum = iCustNum. Name = cName. END CONSTRUCTOR. METHOD PUBLIC DECIMAL GetBalance(): FIND customer WHERE customer.cust-num = CustNum NO-LOCK NO-ERROR. IF AVAILABLE customer THEN RETURN customer.balance. RETURN 0.0. END METHOD. END CLASS. ``` -------------------------------- ### Checking File Existence (Best Practice) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Provides an example of using the `FILE-INFO` handle to check if a file exists before attempting to access it, preventing runtime errors. ```ABL FILE-INFO:FILE-NAME = "customers.csv". IF FILE-INFO:FULL-PATHNAME = ? THEN MESSAGE "File not found." VIEW-AS ALERT-BOX. ``` -------------------------------- ### Defining and Using an ABL Function Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Shows how to define a function named 'addNumbers' that takes two integer parameters and returns their sum as an integer, along with an example of calling the function and displaying its result. ```ABL FUNCTION addNumbers RETURNS INTEGER (iNum1 AS INTEGER, iNum2 AS INTEGER): RETURN iNum1 + iNum2. END FUNCTION. DISPLAY addNumbers(5, 3). /* Outputs 8 */ ``` -------------------------------- ### Setup and Parameter Definition - Progress 4GL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/tt911AlarmTrigger.p.doc.md This section initializes the program environment by importing a time zone conversion library, including the temp-table definition file, saving and setting the session date format to 'mdy', and defining the input temp-table and output logical parameters. ```Progress 4GL USING TimeZoneConvert.* FROM ASSEMBLY. /*------------------------------------------------------------------------------ Purpose: Parameters: Notes: ------------------------------------------------------------------------------*/ {tt911AlarmTrigger.i} define var l_cdateformat as char no-undo. assign l_cdateformat = session:date-format. assign session:date-format = "mdy". DEFINE INPUT PARAMETER TABLE FOR tt911AlarmTrigger. DEFINE OUTPUT PARAMETER opResult AS logical NO-UNDO. ``` -------------------------------- ### Defining Reusable Code in ABL Include File (.i) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Example of an ABL include file (common.i) defining a global variable (cLogFile) and a reusable procedure (logMessage) for writing messages to a log file. ```ABL /* common.i */ DEFINE VARIABLE cLogFile AS CHARACTER NO-UNDO INITIAL "app.log". PROCEDURE logMessage: DEFINE INPUT PARAMETER cMsg AS CHARACTER NO-UNDO. OUTPUT TO VALUE(cLogFile) APPEND. PUT cMsg SKIP. OUTPUT CLOSE. END PROCEDURE. ``` -------------------------------- ### ABL IF-THEN-ELSE Conditional Example Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Shows a basic conditional statement in ABL using IF-THEN-ELSE to check a variable's value and display different output. ```ABL DEFINE VARIABLE iAge AS INTEGER NO-UNDO INITIAL 20. IF iAge >= 18 THEN DISPLAY "Adult". ELSE DISPLAY "Minor". ``` -------------------------------- ### Prefix Pattern Matching with BEGINS in Progress ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Demonstrates using the `BEGINS` operator in Progress ABL to find records where a character field starts with a specific string, similar to SQL's `LIKE 'prefix%'`. ```abl FOR EACH customers NO-LOCK WHERE customers.name BEGINS "J": DISPLAY customers.name customers.city. END. ``` -------------------------------- ### Duplicating Logging Logic in ABL (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL example illustrates the anti-pattern of duplicating the same logging logic across multiple procedures (`main1.p` and `main2.p`). This leads to code redundancy and increased maintenance effort. ```ABL /* main1.p */ OUTPUT TO "app.log" APPEND. PUT "Starting" SKIP. OUTPUT CLOSE. /* main2.p */ OUTPUT TO "app.log" APPEND. PUT "Starting" SKIP. OUTPUT CLOSE. ``` -------------------------------- ### Pattern Matching with LIKE in SQL (PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Shows how to use the `LIKE` operator with the '%' wildcard in SQL to find records where a specific column value matches a pattern, such as names starting with 'J'. ```sql SELECT name, city FROM customers WHERE name LIKE 'J%'; ``` -------------------------------- ### Handling Customer Not Found Error ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Provides an example of using the NO-ERROR option with FIND and checking the AVAILABLE attribute to gracefully handle cases where a record is not found, preventing program interruption. This is a standard error handling technique. ```ABL FIND customer WHERE customer.cust-num = 999 NO-ERROR. IF NOT AVAILABLE customer THEN MESSAGE "Customer not found." VIEW-AS ALERT-BOX. ``` -------------------------------- ### Convert Call Time to Property Local Time (ABL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/tt911AlarmTrigger.p.doc.md Converts the call start time (BWcallStartTime) to the local time of the identified property (company) using either a timezone mapping or a time difference parameter. ```ABL SET ldPropertyTime = BWcallStartTime ``` ```ABL FIND ServerTZMapping where property matches opiCompany_num ``` ```ABL IF found ``` ```ABL SET l_cPropertyZone = TZoffset ``` ```ABL CONVERT ldPropertyTime to property time using l_cPropertyZone ``` ```ABL FIND ext_company_num where key = "TIME-DIFF" and company matches opiCompany_num ``` ```ABL IF found ``` ```ABL ADJUST ldPropertyTime by time difference in hours ``` -------------------------------- ### Incorrectly Handling FIND Error ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Example of a common mistake: performing a FIND operation without NO-ERROR and checking AVAILABLE. If the record is not found, this will cause a runtime error. The fix is to use NO-ERROR and check AVAILABLE. ```ABL FIND customer WHERE customer.cust-num = 999. DISPLAY customer.name. ``` -------------------------------- ### Incorrectly Using EXCLUSIVE-LOCK for Read ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Example of a common mistake: using EXCLUSIVE-LOCK for a read-only query. This causes unnecessary contention and performance degradation. The fix is to use NO-LOCK for read operations. ```ABL FOR EACH customer EXCLUSIVE-LOCK: /* Avoid for read-only */ DISPLAY customer.name. END. ``` -------------------------------- ### Convert UTC Call Time to Property Local Time (Progress ABL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/tt911AlarmTrigger.p.doc.md Converts the call start time (`BWcallStartTime`) from UTC to the property’s local time. Uses `ServerTZMapping` and the `TimeZoneConvert` library if available. Falls back to a time difference (`ext_company_num.int_val`) if the conversion fails. Logs the converted time (`ldPropertyTime`) for auditing. ```Progress ABL ldPropertyTime = tt911AlarmTrigger.BWcallStartTime. find first ServerTZMapping where ServerTZMapping.propertyid = opiCompany_num no-lock no-error. if available ServerTZMapping then do: Assign l_cPropertyZone = ServerTZMapping.TZoffset . utcdatetime = TimeZoneConvert.TimeZoneConversion:UTCToProperty(INPUT ldPropertyTime, INPUT int(l_cPropertyZone), INPUT "d:\\sdd64\\config\\SddTimeZone.xml") . cresult = string(utcdatetime). if cresult ne "" and cresult ne ? then do: ldPropertyTime = datetime(cresult). end. end. if not avail ServerTZMapping or cresult eq "" or cresult eq ? or ldPropertyTime eq ? then do: find first ext_company_num where ext_company_num.company_num eq opiCompany_num and ext_company_num.Field_name eq "TIME-DIFF" no-error. if avail ext_company_num then do: if ldPropertyTime = ? then ldPropertyTime = tt911AlarmTrigger.BWcallStartTime. assign ldPropertyTime = add-interval(ldPropertyTime,ext_company_num.int_val,"hours") . end. end. output to value("..\\logs\\DLL-911AlarmTrigger-" + string(month(today)) + "-" + string(day(today)) + "-" + string(year(today)) + ".log" ) append. put unformatted "Converted time (UTC to Property): " string(today) " " string(time,"hh:mm:ss") "," opiCompany_num "," BWcallStartTime "," BWdialedDigits "," BWuserId "," tt911AlarmTrigger.BWuserName "," BWuserExtension "," BWuserPhone "," BWenterpriseId "," BWgroupId "," BWgroupName "," BWgroupAddress "," BWclidNumber "," BWclidName "," BWsrcIpAdd "," string(ldPropertyTime) skip. output close. ``` -------------------------------- ### Instantiating and Using an ABL Class Object Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Demonstrates how to declare a variable of the 'Customer' class type, create a new instance using the constructor, access its properties and methods, and explicitly delete the object. ```ABL /* UseCustomer.p */ DEFINE VARIABLE oCustomer AS Customer NO-UNDO. oCustomer = NEW Customer(100, "Acme Sports"). DISPLAY oCustomer:Name oCustomer:GetBalance(). DELETE OBJECT oCustomer. ``` -------------------------------- ### Backing Up File (Best Practice) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Shows how to programmatically create a backup of a file using the `COPY-LOB` statement before modifying the original file. ```ABL COPY-LOB FROM FILE "data.txt" TO FILE "data.bak". ``` -------------------------------- ### Defining Dynamic File Path (Best Practice) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Shows how to define a variable in an include file (`config.i`) to store a file path, promoting portability by avoiding hardcoded paths. ```ABL /* config.i */ DEFINE VARIABLE cDataDir AS CHARACTER NO-UNDO INITIAL "C:/data/". ``` -------------------------------- ### Running ABL Procedure Persistently Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL code demonstrates running the `utilities.p` procedure persistently using `PERSISTENT SET hUtil`. This keeps the procedure in memory, allowing subsequent calls to `formatCurrency` via the handle `hUtil` to be more efficient. ```ABL /* main.p */ RUN utilities.p PERSISTENT SET hUtil. RUN formatCurrency IN hUtil (1234.56, OUTPUT cResult). DISPLAY cResult. /* Outputs: 1,234.56 */ ``` -------------------------------- ### Import, Update, and Report Customers - ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL program demonstrates a full workflow: importing customer data from a CSV file into a temp-table, updating the database based on the imported data, and generating a report of customers with a balance over 1000. It utilizes include files for configuration and common procedures, handles file existence checks, includes basic error handling during import, and uses procedures to structure the logic. ```abl /* CustomerImportAndReport.p */ /* Purpose: Import customers from CSV, update database, and generate report */ {config.i} /* Includes cDataDir, cDbName */ {common.i} /* Includes logMessage procedure */ /* Define temp-table for import */ DEFINE TEMP-TABLE ttCustomer FIELD custNum AS INTEGER FIELD custName AS CHARACTER FIELD balance AS DECIMAL. /* Import data */ PROCEDURE importCustomers: DEFINE INPUT PARAMETER cFile AS CHARACTER NO-UNDO. FILE-INFO:FILE-NAME = cFile. IF FILE-INFO:FULL-PATHNAME = ? THEN DO: RUN logMessage("File " + cFile + " not found"). RETURN. END. INPUT FROM VALUE(cFile). REPEAT: CREATE ttCustomer. IMPORT DELIMITER "," ttCustomer NO-ERROR. IF ERROR-STATUS:ERROR THEN DO: RUN logMessage("Invalid data at line " + STRING(INPUT LINE-NUMBER)). NEXT. END. END. INPUT CLOSE. END PROCEDURE. /* Update database */ PROCEDURE updateDatabase: DO TRANSACTION: FOR EACH ttCustomer: FIND customer WHERE customer.cust-num = ttCustomer.custNum NO-ERROR. IF NOT AVAILABLE customer THEN CREATE customer. ASSIGN customer.cust-num = ttCustomer.custNum customer.name = ttCustomer.custName customer.balance = ttCustomer.balance. END. END. RUN logMessage("Updated " + STRING(RECID(customer)) + " records"). END PROCEDURE. /* Generate report */ DEFINE FRAME fReport customer.cust-num LABEL "ID" customer.name LABEL "Name" customer.balance LABEL "Balance" WITH 10 DOWN TITLE "Customer Report" CENTERED. FOR EACH customer NO-LOCK WHERE customer.balance > 1000: DISPLAY customer.cust-num customer.name customer.balance WITH FRAME fReport. END. /* Main block */ RUN importCustomers(cDataDir + "customers.csv"). RUN updateDatabase. ``` -------------------------------- ### ABL Database Querying with FIND Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Shows how to find a specific record (customer) using FIND FIRST with a WHERE clause, NO-LOCK, and NO-ERROR. It includes checking if the record was found using AVAILABLE. ```ABL FIND FIRST customer WHERE customer.cust-num = 100 NO-LOCK NO-ERROR. IF AVAILABLE customer THEN DISPLAY customer.name. ELSE DISPLAY "Customer not found". ``` -------------------------------- ### Using Dynamic File Path (Best Practice) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Illustrates how to use the dynamically defined file path variable (`cDataDir`) with the `VALUE` function to construct the full file path for input. ```ABL INPUT FROM VALUE(cDataDir + "customers.csv"). ``` -------------------------------- ### Using ABL Include File in Main Procedure (.p) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Demonstrates how to include the common.i file using the {} syntax and then call the logMessage procedure defined within it from a main ABL procedure (main.p). ```ABL /* main.p */ {common.i} RUN logMessage("Application started"). ``` -------------------------------- ### Displaying Data with Labels (Progress ABL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Shows how to use the `LABEL` keyword within a `DISPLAY` statement in Progress ABL to provide custom headings for fields when outputting data from a `FOR EACH` loop. ```abl FOR EACH customers NO-LOCK WHERE customers.balance > 1000: DISPLAY customers.name LABEL "Customer Name" customers.city LABEL "Customer City". END. ``` -------------------------------- ### Assigning Data to Variables for Display (Progress ABL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Illustrates an alternative in Progress ABL for programmatic use, where data from a `FOR EACH` loop is assigned to defined variables before being displayed. ```abl FOR EACH customers NO-LOCK WHERE customers.balance > 1000: DEFINE VARIABLE customerName AS CHARACTER NO-UNDO. DEFINE VARIABLE customerCity AS CHARACTER NO-UNDO. ASSIGN customerName = customers.name customerCity = customers.city. DISPLAY customerName customerCity. END. ``` -------------------------------- ### Checking File Existence using FILE-INFO in ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Demonstrates how to use the `FILE-INFO` handle by setting its `FILE-NAME` property and checking if the `FULL-PATHNAME` property is unknown (`?`) to determine if the file exists, displaying an alert box if it does not. ```ABL FILE-INFO:FILE-NAME = "data.txt". IF FILE-INFO:FULL-PATHNAME = ? THEN MESSAGE "File not found." VIEW-AS ALERT-BOX. ``` -------------------------------- ### Handling Transactions in SQL (PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Illustrates how to use `BEGIN`, `COMMIT`, and implicit rollback in SQL to ensure atomicity for multiple update operations within a transaction block. ```sql BEGIN; UPDATE customers SET balance = balance - 100 WHERE customer_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 101; COMMIT; ``` -------------------------------- ### Querying Data with Index in ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Demonstrates using a FOR EACH loop with a WHERE clause aligned with a database index (customer.cust-num) for performance optimization in ABL. Uses NO-LOCK for read-only access. ```ABL FOR EACH customer NO-LOCK WHERE customer.cust-num = 100: DISPLAY customer.name. END. ``` -------------------------------- ### General Pattern Matching with MATCHES in Progress ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Shows how to use the `MATCHES` operator in Progress ABL for more complex pattern matching using wildcards, similar to SQL's `LIKE` with various wildcards. ```abl FOR EACH customers NO-LOCK WHERE customers.name MATCHES "J*": DISPLAY customers.name customers.city. END. ``` -------------------------------- ### Importing Data from a CSV File in ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Illustrates the process of reading data from a 'customers.csv' file into a temporary table using `INPUT FROM` and `IMPORT`, handling potential errors, and then transferring the data from the temp-table to a database table. ```ABL DEFINE TEMP-TABLE ttCustomer FIELD custNum AS INTEGER FIELD custName AS CHARACTER FIELD balance AS DECIMAL. INPUT FROM "customers.csv". REPEAT: CREATE ttCustomer. IMPORT DELIMITER "," ttCustomer NO-ERROR. IF ERROR-STATUS:ERROR THEN NEXT. END. INPUT CLOSE. FOR EACH ttCustomer: CREATE customer. ASSIGN customer.cust-num = ttCustomer.custNum customer.name = ttCustomer.custName customer.balance = ttCustomer.balance. END. ``` -------------------------------- ### Closing Input Stream (Best Practice) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Demonstrates the correct way to close an input stream using `INPUT CLOSE` after reading data from a file, preventing resource leaks. ```ABL INPUT FROM "data.txt". REPEAT: IMPORT cLine. END. INPUT CLOSE. ``` -------------------------------- ### Centralizing Database Configuration in ABL Include File Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL include file (`config.i`) centralizes database connection details by defining a variable for the database name and performing the `CONNECT` operation. Using an include file prevents hardcoding connection strings in multiple places. ```ABL /* config.i */ DEFINE VARIABLE cDbName AS CHARACTER NO-UNDO INITIAL "sports2000". CONNECT VALUE(cDbName) -H "localhost" -S 1234. ``` -------------------------------- ### Joining Tables in SQL (PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Demonstrates how to combine data from the 'customers' and 'orders' tables using the `JOIN` clause in SQL. It filters results for customers located in 'Boston'. ```sql SELECT c.name, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE c.city = 'Boston'; ``` -------------------------------- ### Defining Currency Formatting Procedure in ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL procedure (`utilities.p`) contains a `formatCurrency` procedure that takes a DECIMAL amount and outputs a formatted CHARACTER string. It encapsulates a specific utility function for reuse. ```ABL /* utilities.p */ PROCEDURE formatCurrency: DEFINE INPUT PARAMETER dAmount AS DECIMAL NO-UNDO. DEFINE OUTPUT PARAMETER cFormatted AS CHARACTER NO-UNDO. cFormatted = STRING(dAmount, ">>>,>>9.99"). END PROCEDURE. ``` -------------------------------- ### Hardcoding File Path (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Illustrates the anti-pattern of hardcoding an absolute file path, which makes the code non-portable across different environments. ```ABL INPUT FROM "C:/data/customers.csv". ``` -------------------------------- ### Adding Data with CREATE and ASSIGN (Progress ABL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md In ABL, you create a new record using the `CREATE` statement and then assign values to its fields. Alternatively, you can use direct assignments. ```abl CREATE customers. ASSIGN customers.name = "John Doe" customers.city = "London" customers.balance = 500.00. ``` ```abl CREATE customers. customers.name = "John Doe". customers.city = "London". customers.balance = 500.00. ``` -------------------------------- ### Querying Customer by Number using Index ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Shows how to query a specific customer record using the cust-num field, which is typically indexed, to leverage database indexes for optimized performance. Writing WHERE clauses that align with indexes is a key best practice. ```ABL FOR EACH customer NO-LOCK WHERE customer.cust-num = 100: DISPLAY customer.name. END. ``` -------------------------------- ### Using ABL Include File in Main Procedure Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL procedure (`main.p`) includes the `common.i` file to access the shared `logMessage` procedure. It demonstrates how to incorporate reusable code defined in an include file into a main program. ```ABL /* main.p */ {common.i} RUN logMessage("Starting application"). ``` -------------------------------- ### Selecting Data with Aliases (SQL PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Demonstrates using `AS` to alias column names and table names (`c`) in a `SELECT` statement to improve readability. Filters records based on a balance condition. ```sql SELECT c.name AS customer_name, c.city AS customer_city FROM customers c WHERE c.balance > 1000; ``` -------------------------------- ### Direct Database Import (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Demonstrates the risky practice of importing data directly into a database table without prior validation, which can lead to data corruption. Best practice is to use temp-tables for staging. ```ABL IMPORT customer. ``` -------------------------------- ### Simulating Nested Queries with Nested Loops in Progress ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Demonstrates how to achieve the functionality of a nested query in Progress ABL by using nested `FOR EACH` loops and a flag variable to check for related records. ```abl FOR EACH customers NO-LOCK: DEFINE VARIABLE hasOrder AS LOGICAL NO-UNDO. hasOrder = FALSE. FOR EACH orders NO-LOCK WHERE orders.customer_id = customers.customer_id AND orders.order_date > DATE("01/01/2024"): hasOrder = TRUE. LEAVE. END. IF hasOrder THEN DISPLAY customers.name customers.city. END. ``` -------------------------------- ### Logging ABL Import Results Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL snippet demonstrates logging the results of an import operation to a file (`import.log`). It records the number of records processed (using `RECID` on the temp-table) and the timestamp for auditing purposes. ```ABL OUTPUT TO "import.log" APPEND. PUT "Imported " + STRING(RECID(ttCustomer)) + " at " + STRING(NOW) SKIP. OUTPUT CLOSE. ``` -------------------------------- ### Importing CSV Data into ABL Temp-Table Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL procedure (`import-customers.p`) demonstrates importing data from a CSV file (`customers.csv`) into a temporary table (`ttCustomer`) using the `IMPORT` statement. It then iterates through the temp-table to create records in a database table (`customer`). ```ABL /* import-customers.p */ DEFINE TEMP-TABLE ttCustomer FIELD custNum AS INTEGER FIELD custName AS CHARACTER FIELD balance AS DECIMAL. INPUT FROM "customers.csv". REPEAT: CREATE ttCustomer. IMPORT DELIMITER "," ttCustomer. END. INPUT CLOSE. FOR EACH ttCustomer: CREATE customer. ASSIGN customer.cust-num = ttCustomer.custNum customer.name = ttCustomer.custName customer.balance = ttCustomer.balance. END. ``` -------------------------------- ### Creating Table: PostgreSQL SQL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Defines a new permanent table named 'customers' in a PostgreSQL database. It specifies four columns: 'customer_id' as an auto-incrementing primary key, 'name' and 'city' as character strings with specified lengths, and 'balance' as a numeric type with precision. This statement is part of Data Definition Language (DDL). ```SQL CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100), city VARCHAR(50), balance NUMERIC(10, 2) ); ``` -------------------------------- ### Defining Common Variables and Procedures in ABL Include File Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL include file (`common.i`) defines a global variable `cLogFile` for the log path and a procedure `logMessage` to write messages to this file. It serves as a central place for shared utility code. ```ABL /* common.i */ DEFINE VARIABLE cLogFile AS CHARACTER NO-UNDO INITIAL "app.log". PROCEDURE logMessage: DEFINE INPUT PARAMETER cMsg AS CHARACTER NO-UNDO. OUTPUT TO VALUE(cLogFile) APPEND. PUT cMsg SKIP. OUTPUT CLOSE. END PROCEDURE. ``` -------------------------------- ### Customer Data Processing and Web Report Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md This ABL program defines a `CustomerManager` class to import customer data from a CSV file and update the database. It then generates a simple HTML web report listing customers with a balance over 1000. It includes basic file I/O, database transactions, and SpeedScript output. ```abl /* CustomerManager.p */ {config.i} /* Defines cDataDir = "C:/data/" */ /* Class for customer operations */ CLASS CustomerManager: DEFINE PRIVATE TEMP-TABLE ttCustomer FIELD custNum AS INTEGER FIELD custName AS CHARACTER FIELD balance AS DECIMAL. METHOD PUBLIC VOID ImportCustomers(INPUT cFile AS CHARACTER): FILE-INFO:FILE-NAME = cFile. IF FILE-INFO:FULL-PATHNAME = ? THEN RETURN ERROR "File not found". INPUT FROM VALUE(cFile). REPEAT: CREATE ttCustomer. IMPORT DELIMITER "," ttCustomer NO-ERROR. IF ERROR-STATUS:ERROR THEN NEXT. END. INPUT CLOSE. END METHOD. METHOD PUBLIC VOID UpdateDatabase(): DO TRANSACTION: FOR EACH ttCustomer: FIND customer WHERE customer.cust-num = ttCustomer.custNum NO-ERROR. IF NOT AVAILABLE customer THEN CREATE customer. ASSIGN customer.cust-num = ttCustomer.custNum customer.name = ttCustomer.custName customer.balance = ttCustomer.balance. END. END. END METHOD. END CLASS. /* Web report */ OUTPUT TO "WEB". {&OUT} "Customer Report". {&OUT} "". FOR EACH customer NO-LOCK WHERE customer.balance > 1000: {&OUT} "". END. {&OUT} "

ID	Name	Balance
" customer.cust-num "	" customer.name "	" customer.balance "

". OUTPUT CLOSE. /* Main block */ DEFINE VARIABLE oManager AS CustomerManager NO-UNDO. oManager = NEW CustomerManager(). oManager:ImportCustomers(cDataDir + "customers.csv"). oManager:UpdateDatabase(). DELETE OBJECT oManager. ``` -------------------------------- ### Initialize Variables and Log Entry - Progress ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/tt911AlarmTrigger.p.doc.md Initializes key variables like `opResult` and `opiCompany_num`. It checks for the availability of data in the `tt911AlarmTrigger` temp-table and assigns values from it if available. Finally, it logs detailed call information, including timestamps, user details, and call specifics, to a date-stamped log file for auditing purposes. ```Progress ABL assign opResult = no opiCompany_num = 0. find first tt911AlarmTrigger no-lock no-error. if not avail tt911AlarmTrigger then return. ASSIGN ipBWEnterprise = tt911AlarmTrigger.BWenterpriseId ipBWGroup = tt911AlarmTrigger.BWgroupId . p_origination_ext# = tt911AlarmTrigger.BWuserExtension . output to value("..\logs\DLL-911AlarmTrigger-" + string(month(today)) + "-" + string(day(today)) + "-" + string(year(today)) + ".log" ) append. put unformatted "Entry: " string(today) " " string(time,"hh:mm:ss") "," opiCompany_num "," BWcallStartTime "," BWdialedDigits "," BWuserId "," BWuserName "," BWuserExtension "," BWuserPhone "," BWenterpriseId "," BWgroupId "," BWgroupName "," BWgroupAddress "," BWclidNumber "," BWclidName "," BWsrcIpAdd "," session:date-format skip. output close. ``` -------------------------------- ### Adding Conditional Logic with IF-ELSE in Progress ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Demonstrates how to implement conditional logic within a Progress ABL loop using `IF-ELSE IF-ELSE` statements to categorize data procedurally. ```abl FOR EACH customers NO-LOCK: DEFINE VARIABLE balanceCategory AS CHARACTER NO-UNDO. IF customers.balance > 1000 THEN balanceCategory = "High". ELSE IF customers.balance > 500 THEN balanceCategory = "Medium". ELSE balanceCategory = "Low". DISPLAY customers.name balanceCategory. END. ``` -------------------------------- ### ABL Variable Declaration and Display Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Demonstrates declaring variables of different data types (CHARACTER, INTEGER, DECIMAL) using DEFINE VARIABLE with NO-UNDO and INITIAL value, then displaying them. ```ABL DEFINE VARIABLE cName AS CHARACTER NO-UNDO INITIAL "Alice". DEFINE VARIABLE iAge AS INTEGER NO-UNDO INITIAL 30. DEFINE VARIABLE dSalary AS DECIMAL NO-UNDO INITIAL 50000.50. DISPLAY cName iAge dSalary. ``` -------------------------------- ### Ignoring File Existence Check (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Shows the error of attempting to open a file for input without first verifying its existence, which will result in a runtime error if the file is missing. ```ABL INPUT FROM "data.txt". ``` -------------------------------- ### Adding Data with INSERT (SQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md To add a new record, use the `INSERT INTO` statement. This inserts a new row into the `customers` table with the specified values. ```sql INSERT INTO customers (name, city, balance) VALUES ('John Doe', 'London', 500.00); ``` -------------------------------- ### Handling Database Find Errors in ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Shows how to use NO-ERROR with a FIND statement and check ERROR-STATUS or AVAILABLE to handle cases where a record is not found, displaying a message box if an error occurs or the record is unavailable. ```ABL FIND customer WHERE customer.cust-num = 999 NO-ERROR. IF ERROR-STATUS:ERROR OR NOT AVAILABLE customer THEN MESSAGE "Customer not found." VIEW-AS ALERT-BOX. ELSE DISPLAY customer.name. ``` -------------------------------- ### Joining Tables in Progress ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Shows how to achieve a join-like operation in Progress ABL by nesting `FOR EACH` loops. The comma between loops implicitly links records based on the specified `WHERE` conditions. ```abl FOR EACH customers NO-LOCK WHERE customers.city = "Boston", EACH orders NO-LOCK WHERE orders.customer_id = customers.customer_id: DISPLAY customers.name orders.order_date. END. ``` -------------------------------- ### Querying Customer Data with NO-LOCK ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Demonstrates using the NO-LOCK option for read-only queries on the customer table, filtering by country to improve performance and avoid unnecessary locking. This is a best practice for queries that do not involve updates. ```ABL FOR EACH customer NO-LOCK WHERE customer.country = "USA": DISPLAY customer.name customer.balance. END. ``` -------------------------------- ### Using Nested Queries (Subqueries) in SQL (PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Shows how to use a subquery within the `WHERE` clause in SQL to filter results based on the output of another query, finding customers with recent orders. ```sql SELECT name, city FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE order_date > '2024-01-01' ); ``` -------------------------------- ### Hardcoding Database Connection in ABL (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL snippet shows the anti-pattern of hardcoding database connection details directly in the code. This makes maintenance difficult as connection parameters must be changed in every file where they appear. ```ABL CONNECT "sports2000" -H "localhost" -S 1234. ``` -------------------------------- ### Handling Transactions in Progress ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Demonstrates the use of the `DO TRANSACTION` block in Progress ABL to group database operations. This ensures that all operations succeed or fail together, providing atomicity. ```abl DO TRANSACTION: FIND customers EXCLUSIVE-LOCK WHERE customers.customer_id = 1. customers.balance = customers.balance - 100. FIND accounts EXCLUSIVE-LOCK WHERE accounts.account_id = 101. accounts.balance = accounts.balance + 100. END. ``` -------------------------------- ### Overcomplicated List Iteration (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Shows an overly verbose way to check if a value exists in a list by manually iterating through entries, which is less efficient than using built-in functions. ```ABL DEFINE VARIABLE i AS INTEGER. DO i = 1 TO NUM-ENTRIES(cList): IF ENTRY(i, cList) = cValue THEN ... END. ``` -------------------------------- ### Calling a Progress 4GL Procedure with a Temp-Table Input Parameter Source: https://github.com/tasmidur/progress-abl-doc/blob/main/tt911AlarmTrigger.p.doc.md This snippet demonstrates how a calling program in Progress 4GL defines, populates, and passes a temp-table as an input parameter to another procedure. It shows the syntax for defining the temporary table structure and using the `RUN` statement with the `INPUT TABLE` clause. ```Progress 4GL /* Caller program */ DEFINE TEMP-TABLE tt911AlarmTrigger NO-UNDO FIELD BWcallStartTime AS DATETIME FIELD BWdialedDigits AS CHARACTER FIELD BWuserId AS CHARACTER /* ... other fields ... */. CREATE tt91ldAlarmTrigger. ASSIGN tt911AlarmTrigger.BWcallStartTime = NOW tt911AlarmTrigger.BWdialedDigits = "911" tt911AlarmTrigger.BWuserId = "user123". RUN 911AlarmTrigger.p (INPUT TABLE tt911AlarmTrigger, OUTPUT opResult). ``` -------------------------------- ### Adding Conditional Logic with CASE in SQL (PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Illustrates how to use the `CASE` statement in SQL to add conditional logic directly within a query, categorizing results based on different conditions. ```sql SELECT name, CASE WHEN balance > 1000 THEN 'High' WHEN balance > 500 THEN 'Medium' ELSE 'Low' END AS balance_category FROM customers; ``` -------------------------------- ### Summarizing Data with BREAK BY (Progress ABL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md ABL lacks direct aggregate functions, so you use procedural logic with `BREAK BY`. `BREAK BY` groups records, and `FIRST-OF` and `LAST-OF` help manage group boundaries. ```abl DEFINE VARIABLE customerCount AS INTEGER NO-UNDO. DEFINE VARIABLE totalBalance AS DECIMAL NO-UNDO. FOR EACH customers NO-LOCK BREAK BY customers.city: IF FIRST-OF(customers.city) THEN ASSIGN customerCount = 0 totalBalance = 0. ASSIGN customerCount = customerCount + 1 totalBalance = totalBalance + customers.balance. IF LAST-OF(customers.city) AND customerCount > 5 THEN DISPLAY customers.city customerCount totalBalance. END. ``` -------------------------------- ### Using LOOKUP for List Check (Fix) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Provides the correct and concise way to check if a value exists within a list using the built-in `LOOKUP` function, replacing manual iteration. ```ABL LOOKUP(cValue, cList) ``` -------------------------------- ### Renaming Tables and Columns (SQL PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Demonstrates using the `ALTER TABLE` command in SQL with the `RENAME TO` clause for renaming a table and the `RENAME COLUMN TO` clause for renaming a specific column. ```sql ALTER TABLE customers RENAME TO clients; ALTER TABLE customers RENAME COLUMN city TO town; ``` -------------------------------- ### Sorting Results with FOR EACH and BY (Progress ABL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md ABL uses the `BY` keyword within `FOR EACH` to sort records. Multiple `BY` clauses chain the sort order. ```abl FOR EACH customers NO-LOCK WHERE customers.country = "USA" BY customers.city BY customers.name DESCENDING: DISPLAY customers.name customers.city. END. ``` -------------------------------- ### Retrieving Data with FOR EACH (Progress ABL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md ABL uses a `FOR EACH` loop to iterate over records that match a condition. The `NO-LOCK` keyword ensures read-only access, similar to PostgreSQL's default behavior. ```abl FOR EACH customers NO-LOCK WHERE customers.balance > 1000: DISPLAY customers.name customers.city. END. ``` -------------------------------- ### Calling ABL Procedure Non-Persistently (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL snippet shows the less efficient approach of running an external procedure (`utilities.p`) without the `PERSISTENT` keyword. This causes the procedure to be loaded and unloaded for each call, incurring performance overhead. ```ABL RUN utilities.p. RUN formatCurrency (1234.56, OUTPUT cResult). ``` -------------------------------- ### Dropping a Table (SQL PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Presents the SQL command `DROP TABLE` which permanently deletes a table and all its associated data and structure from the database. ```sql DROP TABLE customers; ``` -------------------------------- ### Updating Customer Balance in Transaction ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Illustrates wrapping an update operation on customer balances within a DO TRANSACTION block to ensure atomicity and control the scope of exclusive locks. Using transactions sparingly and controlling their size is a best practice. ```ABL DO TRANSACTION: FOR EACH customer EXCLUSIVE-LOCK WHERE customer.balance < 0: customer.balance = 0. END. END. ``` -------------------------------- ### Defining Temp-Table: Progress ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Defines a temporary, in-memory table named 'ttCustomers' within a Progress ABL program session. It includes four fields: 'customer_id' as an integer, 'name' and 'city' as character strings with specified display formats, and 'balance' as a decimal with a display format. Temp-tables are session-specific and do not persist data; constraints like primary keys must be handled programmatically. ```Progress ABL DEFINE TEMP-TABLE ttCustomers NO-UNDO FIELD customer_id AS INTEGER FIELD name AS CHARACTER FORMAT "x(100)" FIELD city AS CHARACTER FORMAT "x(50)" FIELD balance AS DECIMAL FORMAT "->>,>>9.99". ``` -------------------------------- ### Retrieving Data with SELECT (SQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md The `SELECT` statement retrieves data from one or more tables. This query selects the `name` and `city` of customers with a balance greater than 1000. ```sql SELECT name, city FROM customers WHERE balance > 1000; ``` -------------------------------- ### Handling Import Errors in ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md This ABL snippet shows how to use the `NO-ERROR` option with the `IMPORT` statement to prevent runtime errors when encountering invalid data. It checks `ERROR-STATUS:ERROR` to detect if an error occurred during the import and displays a message. ```ABL IMPORT DELIMITER "," ttCustomer NO-ERROR. IF ERROR-STATUS:ERROR THEN MESSAGE "Invalid data at line " + STRING(INPUT LINE-NUMBER) VIEW-AS ALERT-BOX. ``` -------------------------------- ### Summarizing Data with Aggregate Functions (SQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Aggregate functions like `COUNT`, `SUM`, etc., summarize data. This groups customers by city, counts them, and sums their balances, showing only cities with more than 5 customers. ```sql SELECT city, COUNT(*) AS customer_count, SUM(balance) AS total_balance FROM customers GROUP BY city HAVING COUNT(*) > 5; ``` -------------------------------- ### Importing with DELIMITER (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Illustrates the mistake of not using NO-ERROR when importing data, which can lead to crashes on invalid input. The code attempts to import a comma-delimited file without error handling. ```ABL IMPORT DELIMITER "," ttCustomer. ``` -------------------------------- ### Not Closing Input Stream (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Highlights the mistake of forgetting to close an input stream after reading, which can lead to resource leaks and potential issues. ```ABL INPUT FROM "data.txt". REPEAT: IMPORT cLine. END. /* Missing INPUT CLOSE */ ``` -------------------------------- ### ABL Database Updating with DO TRANSACTION Source: https://github.com/tasmidur/progress-abl-doc/blob/main/BASIC.md Demonstrates updating database records (customer) within an explicit DO TRANSACTION block. It uses FOR EACH with EXCLUSIVE-LOCK to modify records based on a condition. ```ABL DO TRANSACTION: FOR EACH customer EXCLUSIVE-LOCK WHERE customer.balance < 0: customer.balance = 0. END. END. ``` -------------------------------- ### Defining Variable Without NO-UNDO (Mistake) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Illustrates the mistake of defining a temporary variable without the `NO-UNDO` keyword, which adds unnecessary transaction overhead for non-transactional data. ```ABL DEFINE VARIABLE cTemp AS CHARACTER. ``` -------------------------------- ### Truncating a Table (SQL PostgreSQL) Source: https://github.com/tasmidur/progress-abl-doc/blob/main/Understanding Database Operations_ From SQL to Progress ABL.md Provides the SQL command `TRUNCATE TABLE` which is used to quickly remove all rows from a specified table, resetting identity columns. ```sql TRUNCATE TABLE customers; ``` -------------------------------- ### Incorrectly Using Unbounded Query ABL Source: https://github.com/tasmidur/progress-abl-doc/blob/main/README.md Demonstrates the mistake of performing a query without a WHERE clause, which processes all records in the table. This is inefficient for large tables and should be avoided by adding specific WHERE conditions. ```ABL FOR EACH customer NO-LOCK: DISPLAY customer.name. END. ```