Returns the column name of the column at a given position in a result set cursor.

Syntax

---

COLUMNNAME(<CursorName>,<Position>)

<CursorName> =

Previously-opened result set cursor

<Position> = 1-based column position

Returns

---

VARCHAR

Usage

---

The COLUMNNAME function returns the column name for the column at a given position in a result set cursor. If the result set cursor has not been opened yet by using the OPEN statement, then calling this function will result in an error.

Combined with the COLUMNCOUNT function, this function is useful for dynamically iterating over the result set columns, optionally fetching, inserting, or updating them.

Examples

---

-- This procedure returns a semicolon-delimited
-- string containing the column names for a given table

CREATE FUNCTION ColumnNames(IN TableName VARCHAR COLLATE ANSI_CI)
RETURNS VARCHAR COLLATE ANSI_CI
BEGIN
   DECLARE ResultCursor SENSITIVE CURSOR FOR Stmt;
   DECLARE I INTEGER;
   DECLARE ResultColumnCount INTEGER;
   DECLARE Result VARCHAR DEFAULT '';

   PREPARE Stmt FROM 'TABLE '+QUOTEDSTR(TableName,'"');

   OPEN ResultCursor;

   SET I=1;
   SET ResultColumnCount=COLUMNCOUNT(ResultCursor);

   WHILE I <= ResultColumnCount  DO
      IF Result <> '' THEN
         SET Result=Result+';';
      END IF;
      SET Result=Result+COLUMNNAME(ResultCursor,I);
      SET I=I+1;
   END WHILE;

   CLOSE ResultCursor;

   RETURN Result;

END

SQL 2003 Standard Deviations

---

This function deviates from the SQL 2003 standard in the following ways: