sql_variables

HyperSQL Variables

BIND Variables

To bind a HS variable name to an Oracle column name in the sql method, you precede the HS variable name with a ":". Since Oracle column names are UPPERCASE, you must use HS variable names that also are UPPERCASE.

EXAMPLE:

/* Insert a new record into an ORACLE table named "TEST" */
str S = "newvar" ;
int I = 30 ;
float F = 20.02 ;
str D = "07-NOV-57" ;

sql ( "insert into test values (:S, :I, :F, :D )" ) ;
``

_

The identities :S, :I, :F, and :D are called BIND variables. BIND variables can be used where ever data values are required in a SELECT, INSERT, or UPDATE statement.

SELECT Variables

SELECT variables are encountered only in SQL "SELECT" commands. When issuing a SELECT statement from the HS sql method, the SELECT variables are automatically mapped to HS variables. HS SELECT variables must be in UPPERCASE since they correspond to the default Oracle uppercase table column names. When a SELECT statement is executed, the HS variables that are used in the SELECT statement are assigned the values FETCHed from each row of the Oracle table, in the form of a list. If the HS variable does not exist for an Oracle column name, it is automatically created.

EXAMPLE:

/* FETCH all entries from the Oracle table named "TEST" */
sql("SELECT * from TEST");

/* The SELECT command produces

   ...creating variable STRING from SELECT list
   ...creating variable INTVAL from SELECT list
   ...creating variable FLOATVAL from SELECT list
   ...creating variable DATEVAL from SELECT list
   3 rows processed
 */

/* Print out the results */
put ( STRING ) ;
"funny       " "happy        " "newvar       "
put ( INTVAL ) ;
10 20 30
put ( FLOATVAL ) ;
5.200000 10.800000 20.020000
put ( DATEVAL ) ;
"11-SEP-97IST" "23-MAY-69IST" "07-NOV-57IST"
``

_

EXAMPLE:

/* FETCH the STRING and FLOATVAL fields from records in
   TEST where INTVAL = 20 */
   
int I = 20 ;
sql("SELECT STRING, FLOATVAL FROM TEST WHERE INTVAL  :I" ) ;

/* The SELECT command produces

   3 rows processed
*/
``

_