Agenda

• Journalling

• More Embedded SQL

Journalling

Journal

• Controlling object used to record changes to database objects

Journal Entry

• Record of change to a database object

Journal Receiver

• Holds the Journal Entry.

• A Journal can have more than one journal receiver.

Journal

JournalReceiver

JournalReceiver

Journal Entries

JournalEntries

JournalEntries

JournalEntries

*FILE*FILE

Creating a Collection with a Data Dictionary

• Automatically creates a journal to log changes to the database files created in the collection

Setting up the Journal

Creating a Journal Receiver

CRTJRNRCV

• Fill in the Journal Receiver name, the

library where it will be stored

Creating a Journal

CRTJRN

• Fill in the name of the journal and the library that it is stored in.

• Attach the journal receivers by listing them in the Journal Receiver Fields

Start Journal Physical File

STRJRNPF

• Used to attach physical files to the Journal Receivers.

• Record images parameter defines whether the before/after image of the file will be stored or just the after.

Using the Journal

Commitment Control

Commit

• Used to finalize the database change.

• Happens automatically when a program is ended normally.

Rollback

• Uses the journal entries to take the database back to the state that it was in after the last commit.

More Embedded SQL

Dynamic SQL

• Used to build SQL statements as strings and then execute the string

• Remember Lab 2 – we had to hard code the library to delete instead of using a parameter?– Solution to the problem is Dynamic SQL

Steps to create a dynamic SQL Statement

• Build the character string containing your SQL code

• Use the prepare statement to create the dynamic SQL statementEg. Prepare x from :SqlString

Creates the dynamic sql statement called x. SqlString contains an SQL statement

• Use the execute statement

Statements that can be executed dynamically

Alter TableCallComment OnCommitCreate AliasCreate CollectionCreate Distinct TypeCreate FunctionCreate IndexCreate ProcedureCreate TableCreate ViewDelete

DropFree LocatorGrantInsertLabel OnLock TableRenameRevokeRollbackSet PathSet TransactionUpdate

TESTCOLL

What do we use when we want to retrieve more than one record at

a time?CURSORS!!

Why cursors?

SELECT INTO can only retrieve 1 record at a time

What is a Cursor?

• A table created at run time to hold data

Steps to using cursors

• Declare the cursor– Defines what the cursor will look like

• Open the cursor– Loads the cursor with data

• Fetch (ie read) rows from the cursor– Reads a record from the cursor

• (optionally) update or delete the most recently fetched row

• Close the cursor– If applicable, writes changes to the database– Clears the cursor

Patsearch