Querying and Updating Data in Progress OpenEdge Compared with SQL

Progress OpenEdge allows you quickly building database-driven applications, but the Progress ABL (4GL) language uses own approach to query and modify the data.

Assume we have a table defined using the following SQL CREATE TABLE statement:

    CREATE TABLE cities
    (
       city VARCHAR(50),
       country VARCHAR(50),
       continent VARCHAR(50)
    );

To insert a row into this table, we can use SQL INSERT statement:

    INSERT INTO cities VALUES ('Boston', 'United States', 'North America');

In Progress ABL, we firstly need to use the CREATE statement that inserts a new row with default values into a table, and then use ASSIGN statement or = operator to set the column values:

    /* Inserts a new row with default values set */
    CREATE cities.
 
    city = "Boston".
    country = "United States".
    continent = "North America".

Inserting multiple rows using SQL:

   INSERT INTO cities VALUES ('London', 'United Kingdom', 'Europe');
   INSERT INTO cities VALUES ('Paris', 'France', 'Europe');

Inserting multiple rows using Progress ABL:

    CREATE cities.
    city = "London".
    country = "United Kingdom".
    continent = "Europe".
 
    CREATE cities.
    /* ASSIGN is similar to = operator and it allows specifying multiple values */
    ASSIGN city = "Paris" country = "France" continent = "Europe".

Note the following about Progress ABL:

• Once you inserted a new row (CREATE statement), ABL automatically creates a record buffer that has the same name as the table name.
  
  In other words, this is a structure (record) that holds all columns of the new row. You can set the column values using qualified name cities.city or just city if there are no name conflicts with other variables or record buffers.

• Column assignments are not a continuation of CREATE statement, you can put them everywhere in the code (inside IF statement i.e.), Progress will automatically update the row in the database when the record buffer goes out of scope.

• In our example we have 2 CREATE statements. When the second statement is executed, Progress automatically updates the first row with the changes made in the record buffer.

To delete all rows from a table you can use SQL DELETE or TRUNCATE statements:

    -- DELETE allows specifying WHERE conditions and it is logged
    DELETE FROM cities;
 
    -- TRUNCATE deletes all rows and cannot be rolled back
    TRUNCATE TABLE cities;

In Progress ABL, you can use FOR EACH loop and DELETE statement to delete rows:

   /* Delete all rows in cities table */
   FOR EACH cities:
     DELETE cities.
   END.

To update rows in a table you can use SQL UPDATE statement:

    UPDATE cities
     SET city = 'Nice'
     WHERE country = 'France';

In Progress ABL, you can use FOR EACH to iterate over rows and perform the update, or FIND FIRST to update a single row based on a search condition:

   FOR EACH cities WHERE country = "France":
     city = "Nice".
   END.
 
   FIND FIRST cities WHERE country = "France".
   city = "Nice".

To retrieve rows from a table you can use SQL SELECT statement:

    SELECT * FROM cities;

In Progress ABL, you can use FOR EACH to retrieve rows, or FIND FIRST to select a single row based on a search condition:

   FOR EACH cities:
     DISPLAY cities.
   END.
 
   FIND FIRST cities WHERE country = "France".
   DISPLAY cities.

• Progress OpenEdge White Papers, Articles and Reference