SQL/MX Programming Manual for Java

SQLJ Programming
HP NonStop SQL/MX Programming Manual for Java—523726-003
3-28
Host Variables
Host Variables
Unlike embedded variable declarations in C or COBOL programs, Java host variables
are not declared within a BEGIN and END block. You can declare them anywhere Java
variable declarations are allowed in a Java program. The following examples show
how you can use host variables, which are highlighted in bold, in basic SELECT INTO,
DELETE, INSERT, and UPDATE statements.
Example—SELECT INTO Statement
The Java variable empname is a host variable that accepts a value from the NonStop
SQL database. In this case, the SELECT INTO statement retrieves the last name of an
employee and stores it in the Java host variable called empname:
String empname = null;
...
#sql {SELECT last_name
INTO :empname
FROM employee
WHERE empnum = 65
};
...
If more than one row qualifies for a SELECT INTO statement, SQL/MX retrieves only
the first row that qualifies and returns an error message. To avoid the error message,
use FIRST 1 or ANY 1 in the select list of a query that might return multiple rows. For
the syntax, see the SELECT statement in the SQL/MX Reference Manual.
Example—DELETE Statement
The host variable, :empNum, passes its value to the EMPNUM column for comparison.
The DELETE statement deletes a row of the EMPLOYEE table where the employee
number is the value specified by the host variable:
...
#sql {DELETE FROM employee
WHERE empnum = :(empNum)
};
Example—INSERT Statement
This INSERT statement inserts a row into the EMPLOYEE table by using the values of
the host variables:
int empNum = 996;
String firstName = new String("Gary");
String lastName = new String("Ragbrai");
int deptNum = 4000;
int jobCode = 450;
java.math.BigDecimal salary =
new java.math.BigDecimal("60000.00");
...
#sql {INSERT INTO employee