SQL INDICATOR

What is SQL INDICATOR in embedded SQL?

Associates a host indicator variable with a host data variable in embedded SQL so the program can detect NULL values and data truncation.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INDICATOR: Supported by pre-compilers for Oracle (Pro*C, Pro*COBOL), PostgreSQL (ECPG), IBM Db2, Informix ESQL/C, SQL Server Embedded SQL (obsolete), and most ANSI SQL embedded implementations. Not relevant in interactive SQL clients.

SQL INDICATOR Full Explanation

INDICATOR is a reserved word defined by the SQL standard for embedded SQL. In host-language programs (C, COBOL, PL/SQL, etc.) each column value moved between the database and a host variable can be paired with an indicator variable. The indicator variable stores status information:• 0 – the column value is not NULL and fits in the host variable• -1 – the column value is NULL• >0 – the column value was truncated; the number indicates the original lengthThe keyword INDICATOR explicitly precedes the indicator variable so the pre-compiler can parse the pair unambiguously. Although many implementations allow the shorthand “:host :ind” without the keyword, using INDICATOR keeps code portable across compilers that require it. Indicator variables are normally defined as 2-byte signed integers (SHORT in C, PIC S9(4) COMP in COBOL).INDICATOR is only meaningful inside EXEC SQL … END-EXEC (or BEGIN DECLARE SECTION blocks) and cannot be executed directly by the database engine. It is processed at pre-compile time and removed from the final SQL sent to the server.

SQL INDICATOR Syntax

:host_variable INDICATOR :indicator_variable

SQL INDICATOR Parameters

Example Queries Using SQL INDICATOR

EXEC SQL BEGIN DECLARE SECTION;
  char   name[51];
  short  name_ind;
  int    id;
  short  id_ind;
EXEC SQL END DECLARE SECTION;

/* Fetch a single row into host variables */
EXEC SQL SELECT user_id, full_name
         INTO :id INDICATOR :id_ind,
              :name INDICATOR :name_ind
         FROM   users
         WHERE  user_id = 42;

Expected Output Using SQL INDICATOR

After the SELECT executes, the program variables hold:- id = 42, id_ind = 0 (not NULL)- name = "Alice", name_ind = 0 –or–- If full_name was NULL, name_ind = -1 and the name buffer is untouched
The database itself is not modified

Use Cases with SQL INDICATOR

Reading nullable columns into a C or COBOL application
Detecting string truncation when host buffers are shorter than column size
Passing NULLs from a host program to INSERT or UPDATE statements
Writing portable embedded SQL that compiles across different pre-compilers

Common Mistakes with SQL INDICATOR

Forgetting to declare the indicator variable, causing a pre-compile error
Using the wrong data type (must be signed SMALLINT)
Assuming indicator variables are set automatically without being specified
Misinterpreting positive values (>0) as successful rather than truncated

First Introduced In

SQL-89 (first embedded SQL specification)

Frequently Asked Questions

What does INDICATOR mean in embedded SQL?

It flags a companion variable that tells your program whether a fetched value was NULL or truncated.

Do I always need an indicator variable?

Use one whenever the column can be NULL or when you risk truncation. Otherwise it is optional, but many compilers warn if omitted.

Can I omit the INDICATOR keyword and just write ":var :ind"?

Some compilers allow that shorthand, but including the keyword is safer and portable across all ANSI-compliant pre-compilers.

What values can an indicator variable hold?

-1 for NULL, 0 for a valid non-truncated value, and a positive number for the original length of truncated data.