Accessing SQL Defined Tables › Adding and Replacing Data on SQL Tables

Adding and Replacing Data on SQL Tables

Introduction

In addition to creating new CA IDMS/DB tables, CA Culprit can also add and replace data on existing SQL tables. Many of the coding techniques discussed earlier in this chapter also apply to adding and replacing data.

Updating SQL tables is a two step process:

1. Validating the output data buffer layout against the existing table definition,
2. Inserting new data.

• During the Compile Phase of CA Culprit, an SQL Prepare/Describe is executed for SELECT * FROM table-name. This returns an SQL Definition Area (SQLDA) defining all columns in the table. CA Culprit uses this information to validate the edit parameters coded by the user.
  • Info-column numbers are used to sequence the edit parameters coded by the user.
  • For each SQL column, the SQL data type must match the CA Culprit data specification. Field names are not considered.
  • An edit parameter with data specification FB SZ=4 must follow every SQL column that may contain null values. This numeric field represents the null indicator for the previous SQL column. During the output phase, the value of this field must be either zero (indicating not null) or minus one (indicating a null value).
• During the Output Phase, CA Culprit begins by issuing the SQL command DELETE FROM table-name if TYPE=REPLACE was coded. Next, use the highly efficient bulk insert technique to load data into the SQL table. All SQL processing is handled automatically by CA Culprit.