INSERT statement

Creates a new database record, displays the initial values for the fields in the record, prompts for values of those fields, and assigns those values to the record.

The INSERT statement is a combination of the following statements:

CREATE — Creates an empty record buffer

DISPLAY — Moves the record from the record buffer into the screen buffer and displays the contents of the buffer on the screen

PROMPT-FOR — Accepts input from the user, and puts that input into the screen buffer

ASSIGN — Moves data from the screen buffer into the record buffer

Note: Does not apply to SpeedScript programming.

Data movement

CREATE — Creates an empty record buffer

DISPLAY — Moves the contents of the record buffer to the screen buffer and displays the screen buffer

PROMPT-FOR — Accepts input from the user into the screen buffer

ASSIGN — Moves the contents of the screen buffer to the record buffer

Syntax


INSERT `record` [ EXCEPT `field` ... ] [ USING { ROWID ( `nrow` ) \| RECID ( `nrec` ) } ] [ `frame-phrase` ] [ NO-ERROR ]

record

The name of the record you want to add to a database file. The AVM creates one record buffer for every file you use in a procedure. This buffer is used to hold a single record from the file associated with the buffer. Use the DEFINE BUFFER statement to create additional buffers, if necessary. The CREATE part of the INSERT statement creates an empty record buffer for the file in which you are inserting a record.

To insert a record in a table defined for multiple databases, you must qualify the record’s table name with the database name. See the Record phrase reference entry for more information.

EXCEPT field

Inserts all fields except those listed in the EXCEPT phrase.

USING { ROWID ( nrow ) | RECID ( nrec ) }

Allows you to insert a record in an RMS relative file (for backward compatibility only) using a specific record number, where nrow is the ROWID relative record number of the record you want to insert and nrec is the RECID relative record number of the record you want to insert.

frame-phrase

Specifies the overall layout and processing properties of a frame. For more information on frame-phrase, see the Frame phrase reference entry.

NO-ERROR

Suppresses ABL errors or error messages that would otherwise occur and diverts them to the ERROR-STATUS system handle. If an error occurs, the action of the statement is not done and execution continues with the next statement. If the statement fails, any persistent side-effects of the statement are backed out. If the statement includes an expression that contains other executable elements, like methods, the work performed by these elements may or may not be done, depending on the order the AVM resolves the expression elements and the occurrence of the error.

To check for errors after a statement that uses the NO-ERROR option:

Check the ERROR-STATUS:ERROR attribute to see if the AVM raised the ERROR condition.

Check if the ERROR-STATUS:NUM-MESSAGES attribute is greater than zero to see if the AVM generated error messages. ABL handle methods used in a block without a CATCH end block treat errors as warnings and do not raise ERROR, do not set the ERROR-STATUS:ERROR attribute, but do add messages to the ERROR-STATUS system handle. Therefore, this test is the better test for code using handle methods without CATCH end blocks. ABL handle methods used in a block with a CATCH end block raise ERROR and add messages to the error object generated by the AVM. In this case, the AVM does not update the ERROR-STATUS system handle.

Use ERROR-STATUS:GET-MESSAGE( message-num ) to retrieve a particular message, where message-num is 1 for the first message.

If the statement does not include the NO-ERROR option, you can use a CATCH end block to handle errors raised by the statement.

Some other important usage notes on the NO-ERROR option:

NO-ERROR does not suppress errors that raise the STOP or QUIT condition.

A CATCH statement, which introduces a CATCH end block, is analogous to a NO-ERROR option in that it also suppresses errors, but it does so for an entire block of code. It is different in that the error messages are contained in a class-based error object (generated by the AVM or explicitly thrown), as opposed to the ERROR-STATUS system handle. Also, if errors raised in the block are not handled by a compatible CATCH block, ON ERROR phrase, or UNDO statement, then the error is not suppressed, but handled with the default error processing for that block type.

When a statement contains the NO-ERROR option and resides in a block with a CATCH end block, the NO-ERROR option takes precedence over the CATCH block. That is, an error raised on the statement with the NO-ERROR option will not be handled by a compatible CATCH end block. The error is redirected to the ERROR-STATUS system handle as normal.

If an error object is thrown to a statement that includes the NO-ERROR option, then the information and messages in the error object will be used to set the ERROR-STATUS system handle. This interoperability feature is important for those integrating code that uses the traditional NO-ERROR technique with the newer, structured error handling that features error objects and CATCH end blocks.

Example

In this procedure the user adds a new Order record. After the user adds a new Order record, the procedure creates OrderLines for that record. The procedure uses the CREATE statement to create OrderLines rather than the INSERT statement. When you use the INSERT statement, the PROMPT-FOR and ASSIGN parts of the INSERT let you put data into all the fields of the record being inserted. In the case of OrderLines, this procedure only lets you add information into a few of the OrderLine fields. Use CREATE together with UPDATE to single out the OrderLine fields.

r-insrt.p
REPEAT: INSERT Order WITH 1 COLUMN. REPEAT: CREATE OrderLine. OrderLine.OrderNum = Order.OrderNum. UPDATE OrderLine.LineNum OrderLine.ItemNum OrderLine.Qty OrderLine.Price. /* Verify the ItemNum by finding an Item with that number */ FIND Item OF OrderLine. END. END.

r-insrt.p

REPEAT: 
  INSERT Order WITH 1 COLUMN. 
  REPEAT: 
    CREATE OrderLine. 
    OrderLine.OrderNum = Order.OrderNum. 
    UPDATE OrderLine.LineNum OrderLine.ItemNum OrderLine.Qty  
      OrderLine.Price. 
    /* Verify the ItemNum by finding an Item with that number */ 
    FIND Item OF OrderLine. 
  END. 
END.

Notes

If an error occurs during the INSERT statement, the AVM retries the data entry part of the statement and processes the error associated with the block that contains the statement. (For example, an error might occur when the user enters a duplicate index value for a unique index.)

Any frame characteristics described by an INSERT statement contribute to the frame definition. When ABL compiles a procedure, it uses a top-to-bottom pass of the procedure to design all the frames required by that procedure, including those referenced by INSERT statements, and adds field and related format attributes as it goes through the procedure.

If you receive input from a device other than the terminal, and the number of characters read by the INSERT statement for a particular field or variable exceeds the display format for that field or variable, the AVM returns an error. However, if you set a logical field that has a format of y/n and the data file contains a value of yes or no, the AVM converts that value to y or n.

If you use a single qualified identifier with the INSERT statement, the compiler first interprets the reference as dbname.filename. If the compiler cannot resolve the reference as dbname.filename, it tries to resolve it as filename.fieldname.

When inserting fields, you must use filenames that are different from field names to avoid ambiguous references. See the Record phrase reference entry for more information.

The INSERT statement causes any related database CREATE triggers to execute. All CREATE triggers execute after the record is actually created. If a CREATE trigger fails (or executes a RETURN statement with the ERROR option), the record creation is undone.