Reference Manual
   CHAPTER 9. SQL Statements     

SET statement

Function 

To assign a value to a SQL variable.

Syntax 

SET identifier = expression

Permissions 

None.

Side effects 

None.

See also 

CREATE VARIABLE statement

DROP VARIABLE statement

Expressions

Description 

The SET statement assigns a new value to a variable that was previously created using the CREATE VARIABLE statement.

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

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

Variables are necessary for creating large text or binary objects for INSERT or UPDATE statements from Embedded SQL programs, because Embedded SQL host variables are limited to 32,767 bytes.

Standards and compatibility 

• SQL/92     Persistent stored module feature.

• Sybase     Not supported. In Adaptive Server Enterprise, variables are assigned using the SELECT statement with no table, a Transact-SQL syntax that is also supported by Adaptive Server Anywhere. The SET statement is used to set database options in 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[5001];
EXEC SQL END DECLARE SECTION;

EXEC SQL CREATE VARIABLE hold_text LONG VARCHAR;
EXEC SQL SET hold_text = '';
for(;;) {
   /* read some data into buffer ... */
   size = fread( buffer, 1, 5000, fp );
   if( size <= 0 ) break;
   
   /* buffer must be null-terminated */
   buffer[size] = '\0';
   /* add data to blob using concatenation */
   EXEC SQL SET hold_text = hold_text || :buffer;
}
EXEC SQL INSERT INTO some_table VALUES ( 1, hold_text );
EXEC SQL DROP VARIABLE hold_text;

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

EXEC SQL BEGIN DECLARE SECTION;
DECL_BINARY( 5000 ) buffer;
EXEC SQL END DECLARE SECTION;
EXEC SQL CREATE VARIABLE hold_blob LONG BINARY;
EXEC SQL SET hold_blob = '';
for(;;) {
   /* read some data into buffer ... */
   size = fread( &(buffer.array), 1, 5000, fp );
   if( size <= 0 ) break;
   buffer.len = size;

   /* 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;