Export (0) Print
Expand All

Using Host Variables

SQL Server 2000

  New Information - SQL Server 2000 SP3.

You can manage input and output for Embedded SQL statements by using host variables. Host variables are standard C-program variables that are declared in an Embedded SQL declare section by using the BEGIN DECLARE SECTION and END DECLARE SECTION statements.

Use host variables when the number of items and their data types are known at compile time. You can use host variables in static SQL statements to specify input values or to receive output values. You can also use host variables together with parameter markers in dynamic SQL statements to specify input values or to receive the output of a dynamically prepared cursor.

Security Note  Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information, see Validating User Input.

When a host variable name is used in an Embedded SQL statement, the variable name begins with a colon (:). This colon enables the compiler to distinguish between host variables, and tables or columns that might have the same name.

The following example is of a C program that uses host variables. The program prompts the user for an author's last name and stores the entered value in the host variable szLastName. The program then retrieves the author's first name from the pubs database and stores the result in the host variable szFirstName.

#include <stdio.h>

int main ()
{
   EXEC SQL BEGIN DECLARE SECTION;
   char szLastName[30];
   char szFirstName[30];
   EXEC SQL END DECLARE SECTION;

   EXEC SQL CONNECT TO gizmo.pubs USER $integrated;

   printf("Type author's last name: ");
   fgets(stdin, 30, szLastName);

   EXEC SQL SELECT au_fname INTO :szFirstName
      FROM authors WHERE au_lname = :szLastName;

   printf("Author's first name is %s.", szFirstName);
   return (0);
}

Security Note  The preceding example uses the $integrated keyword to enable Windows Authentication. When possible, use this authentication method. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 cryptoAPI.

Show:
© 2014 Microsoft