Talking to the Database

4GL was built for database applications, so SQL is woven right into the language — you write SELECT, INSERT, UPDATE and DELETE directly, and the results land in your variables. This chapter covers the everyday patterns: connecting, reading one row or many, changing data safely with transactions, and handling errors.

The examples use a table called customers with columns id, name, city and balance. Adapt them to your own schema.

Connecting

Open a database at the start of your program:

DATABASE sales_db

From then on, every SQL statement runs against that database. Close it (rarely needed; it closes on exit) with:

CLOSE DATABASE

Exploring a database. Before you write code, it helps to see what is really there. Two command-line tools ship with Aubit 4GL:

adbaccess sales_db                 # interactive SQL prompt
adbschema -d sales_db -t customers # print one table's definition

Always check column names against the real schema rather than guessing.

Reading a single row: SELECT ... INTO

When a query returns at most one row, read it straight into variables:

DEFINE name    CHAR(40)
DEFINE balance DECIMAL(12,2)

SELECT name, balance INTO name, balance
    FROM customers
    WHERE id = 101

IF SQLCA.SQLCODE = 0 THEN
    DISPLAY name CLIPPED, " owes ", balance
ELSE
    DISPLAY "No such customer"
END IF

SQLCA.SQLCODE is 0 on success, 100 when nothing matched, and a negative number on error. Always check it.

Reading many rows: cursors and FOREACH

For a query that returns several rows, declare a cursor and loop with FOREACH, which opens the cursor, fetches each row, and closes it for you:

DEFINE rl_cust RECORD
    id   INTEGER,
    name CHAR(40),
    city CHAR(30)
END RECORD

DECLARE c_cust CURSOR FOR
    SELECT id, name, city
        FROM customers
        WHERE city = "Berlin"
        ORDER BY name

FOREACH c_cust INTO rl_cust.id, rl_cust.name, rl_cust.city
    DISPLAY rl_cust.id, "  ", rl_cust.name CLIPPED
END FOREACH

This is the natural way to fill a DYNAMIC ARRAY for a DISPLAY ARRAY (see chapter 5):

DEFINE a_cust DYNAMIC ARRAY OF RECORD
    id   INTEGER,
    name CHAR(40),
    city CHAR(30)
END RECORD
DEFINE i INTEGER

LET i = 0
FOREACH c_cust INTO rl_cust.id, rl_cust.name, rl_cust.city
    LET i = i + 1
    LET a_cust[i].* = rl_cust.*
END FOREACH

CALL SET_COUNT(i)
DISPLAY ARRAY a_cust TO s_cust.*
    ON ACTION accept EXIT DISPLAY
END DISPLAY

Changing data

-- add a row
INSERT INTO customers (id, name, city, balance)
    VALUES (200, "New Corp", "Munich", 0)

-- change rows
UPDATE customers
    SET balance = balance + 100
    WHERE id = 200

-- remove rows
DELETE FROM customers
    WHERE balance = 0 AND city = "Munich"

After each statement, SQLCA.SQLCODE tells you whether it worked, and SQLCA.SQLERRD[3] holds the number of rows affected.

Transactions: all or nothing

When several changes must succeed or fail together, wrap them in a transaction. BEGIN WORK starts it, COMMIT WORK makes the changes permanent, and ROLLBACK WORK undoes everything since BEGIN WORK.

BEGIN WORK

    UPDATE accounts SET balance = balance - 100 WHERE id = 1
    IF SQLCA.SQLCODE != 0 THEN
        ROLLBACK WORK
        ERROR "Transfer failed"
        RETURN
    END IF

    UPDATE accounts SET balance = balance + 100 WHERE id = 2
    IF SQLCA.SQLCODE != 0 THEN
        ROLLBACK WORK
        ERROR "Transfer failed"
        RETURN
    END IF

COMMIT WORK
MESSAGE "Transfer complete"

Either both updates stick, or neither does. Never leave a transaction half-open.

Prepared statements

When you run the same query many times with different values — or build the SQL text at run time — PREPARE it once and EXECUTE it repeatedly. Use ? as a placeholder for each value.

DEFINE name CHAR(40)

PREPARE p_find FROM "SELECT name FROM customers WHERE id = ?"

EXECUTE p_find USING 101 INTO name
DISPLAY name CLIPPED

EXECUTE p_find USING 102 INTO name
DISPLAY name CLIPPED

FREE p_find        -- release it when done

For a prepared query that returns many rows, declare a cursor over it:

PREPARE p_bycity FROM "SELECT id, name FROM customers WHERE city = ? ORDER BY name"
DECLARE c_bycity CURSOR FOR p_bycity
OPEN c_bycity USING "Berlin"
FETCH c_bycity INTO rl_cust.id, rl_cust.name
-- ... loop with FETCH while SQLCA.SQLCODE = 0 ...
CLOSE c_bycity

Search screens: CONSTRUCT

CONSTRUCT lets the user type search criteria into a form, and builds the matching SQL WHERE clause for you — the basis of every "search" screen. The user can enter ranges, wildcards and comparisons in the fields, and CONSTRUCT translates them.

DEFINE where_clause CHAR(500)

CONSTRUCT where_clause ON customers.name, customers.city
    FROM f_name, f_city

LET sql_text = "SELECT id, name, city FROM customers WHERE ", where_clause CLIPPED
PREPARE p_search FROM sql_text
DECLARE c_search CURSOR FOR p_search
-- ... open and FOREACH as usual ...

Binding a form to a table

In chapter 3 every form was FORMONLY. To bind fields directly to table columns, name the database and bind each field to table.column:

DATABASE sales_db

SCREEN
{
    Customer
    Name:  [f_name              ]
    City:  [f_city              ]
}

ATTRIBUTES
Edit f_name = customers.name;
Edit f_city = customers.city;

This lets the form inherit the column's type and length, and makes CONSTRUCT and DISPLAY/INPUT work naturally with a RECORD LIKE customers.*.

Error handling

Two status sources cover almost everything:

• SQLCA.SQLCODE — the result of the last SQL statement (0 ok, 100 not found, negative = error).
• STATUS — the result of the last non-SQL operation (e.g. file handling).

For repetitive checks, WHENEVER sets a default reaction so you do not write an IF after every statement:

WHENEVER ERROR STOP            -- abort on any SQL error (good while developing)
WHENEVER NOT FOUND CONTINUE    -- "no rows" is not an error, just continue

-- ... your SQL ...

WHENEVER ERROR CONTINUE        -- go back to checking SQLCODE yourself when you need to

A practical habit. During development use WHENEVER ERROR STOP so mistakes surface loudly. In finished code, switch to WHENEVER ERROR CONTINUE and check SQLCA.SQLCODE where it matters, so the user gets a friendly message instead of a crash.

You can now read and write real data. Next, learn the calls that let your program drive the modern client — themes, hiding fields, and notifications: Talking to the VDC Client.