Reference Manual
   CHAPTER 9. SQL Statements     

CREATE VARIABLE statement

Function 

To create a SQL variable.

Syntax 

CREATE VARIABLE identifier data-type

Permissions 

None.

Side effects 

None.

See also 

BEGIN... END statement

SQL Data Types

DROP VARIABLE statement

SET statement

Description 

The CREATE VARIABLE statement creates a new variable of the specified data type. The variable contains the NULL value until it is assigned a different value by the SET VARIABLE statement.

A variable can be used in a SQL statement anywhere a column name is allowed. If column name matches the identifier, Adaptive Server Anywhere checks to see if there is a variable that matches and uses its value.

Variables belong to the current connection, and disappear when you disconnect from the database or when you use the DROP VARIABLE statement. Variables are not visible to other connections. Variables are not affected by COMMIT or ROLLBACK statements.

Variables are useful for creating large text or binary objects for INSERT or UPDATE statements from Embedded SQL programs.

Variables created by CREATE VARIABLE can be used in any SQL statement or in any procedure or trigger.

Local variables in procedures and triggers are declared within a compound statement (see Using compound statements).

Standards and compatibility 

• SQL/92     Vendor extension.

• Sybase     Not supported by Adaptive Server Enterprise.

Example 

• The following code fragment could be used to insert a large text value into the database.

  EXEC SQL BEGIN DECLARE SECTION;
  char buffer[5000];
  EXEC SQL END DECLARE SECTION;
  EXEC SQL CREATE VARIABLE hold_blob LONG VARCHAR;
  EXEC SQL SET hold_blob = '';
  for(;;) {
     /* read some data into buffer ... */
     size = fread( buffer, 1, 5000, fp );
     if( size <= 0 ) break;
     /* add data to blob using concatenation
     Note that concatenation works for binary 
     data too! */
     EXEC SQL SET hold_blob = hold_blob || :buffer;
  }
  EXEC SQL INSERT INTO some_table VALUES ( 1, hold_blob );
  EXEC SQL DROP VARIABLE hold_blob;