Connecting to Your Database
Using Embedded SQL with SAP Adaptive Server Enterprise

The SELECT statement contains input variables and output variables.

Example 1

Assume you enter the following statement:

SELECT emp_name, emp_salary 
   INTO :emp_name_var, :emp_salary_var 
   FROM employee WHERE emp_id = :emp_id_var;

In this example, emp_id_var, emp_salary_var, and emp_name_var are variables defined within the scope of the script that contains the SELECT statement, and emp_id_var is processed as described in the DELETE example above.

Both emp_name_var and emp_salary_var are output variables that will be used to return values from the database. The data types of emp_name_var and emp_salary_var should be the PowerScript data types that best match the Adaptive Server Enterprise data type. When the data types do not match perfectly, PowerBuilder converts them.

Assume the value for emp_id_var is 691 as in the previous example. When the SELECT statement executes, the database receives the following statement:

SELECT emp_name, emp_salary 
   FROM employee WHERE emp_id = 691;

If the statement executes with no errors, data locations for the result fields are bound internally. The data returned into these locations is then converted as necessary and the appropriate PowerScript data variables are set to those values.

Example 2

This example assumes the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has executed. It also assumes the data type of the emp_id column in the employee table is CHARACTER[10].

The user enters an employee ID into the single line edit field sle_Emp and clicks the button Cb_Delete to delete the employee.

The script for the Clicked event in the CommandButton Cb_Delete is:

// Make sure we have a value.
if sle_Emp.text <> "" then
// Since we have a value, try to delete it. 
   DELETE FROM employee
   WHERE emp_id = :sle_Emp.text;
// Test to see if the DELETE worked. 
   if SQLCA.sqlcode = 0 then
// It seems to have worked, let user know. 
      MessageBox( "Delete",& 
      "The delete has been successfully "& 
      +" processed!") 
      COMMIT; 
   else 
//It didn't work. 
      MessageBox( "Error", & 
      "The delete failed. Employee ID is not "& 
      +"valid.")
      ROLLBACK;
   end if
else
// No input value. Prompt user.
      MessageBox( "Error",& 
      "An employee ID is required for "& 
      +"delete!" ) 
   end if

Example 3

This example assumes the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has executed. The user wants to extract rows from the employee table and insert them into the table named extract_employees. The extraction occurs when the user clicks the button Cb_Extract. The boolean variable YoungWorkers is set to TRUE or FALSE elsewhere in the application.

The script for the Clicked event for the CommandButton Cb_Extract is:

integer    EmployeeAgeLowerLimit
integer    EmployeeAgeUpperLimit

// Do they have young workers?
if ( YoungWorkers = TRUE ) then

// Yes - set the age limit in the YOUNG range.
// Assume no employee is under legal working age.
   EmployeeAgeLowerLimit = 16

// Pick an upper limit.
   EmployeeAgeUpperLimit = 42
else

// No - set the age limit in the OLDER range.
   EmployeeAgeLowerLimit = 43

// Pick an upper limit that includes all
// employees.
   EmployeeAgeUpperLimit = 200
end if 

INSERT INTO extract_employee(emp_id,emp_name)
   SELECT emp_id, emp_name FROM employee
      WHERE emp_age >= :EmployeeAgeLowerLimit
      AND emp_age <= :EmployeeAgeUpperLimit;

// If there are no errors, commit the changes.
if SQLCA.sqlcode = 0 then
   COMMIT;
else

// If there are errors, roll back the changes and
// tell the user.
   ROLLBACK;
   MessageBox( "Insert Failed", SQLCA.sqlerrtext)
end if

See also

SAP Adaptive Server Enterprise DELETE, INSERT, and UPDATE