ODBC
SELECT
The SELECT statement contains input and output variables.
-
Input variables
are passed to the database as part of the execution, and the
substitution is as described for DELETE, INSERT, and UPDATE. -
Output variables
return values based on the result of the SELECT
statement.
Example 1
Assume you enter the following statement:
|
1 2 3 |
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
PowerScript variables defined within the scope of the script containing
the SELECT statement, and emp_id_var is an input variable and 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 data type in the database. When the data types do not match perfectly,
PowerBuilder converts them.
How big should numeric output variables be?
For numeric data, the output variable must be large enough to hold
any value that may come from the database.
Assume the value for emp_id_var is 691 as in the previous example.
When the SELECT statement executes, the database receives this
command:
|
1 |
SELECT emp_name, emp_salary FROM employee WHERE emp_id = 691; |
If no errors are returned when the statement executes, data
locations are bound internally for the result fields. The data returned
into these locations is converted if necessary, and the appropriate
PowerScript 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.
The script for the Clicked event in the CommandButton Cb_Delete
is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
// Make sure we have a value. if sle_Emp.text <> "" then // Since we have a value, let's 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!") else // It didn't work. MessageBox("Error", & "The delete failed. Employee ID "& +"is not valid.") end if else // No input value. Prompt user. MessageBox("Error", & "An employee ID is required for "& +"delete!") end if |
Error checking
Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate a
specific point.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
integer EmployeeAgeLowerLimit integer mployeeAgeUpperLimit // 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_employees(emp_id,emp_name) SELECT emp_id, emp_name FROM employee WHERE emp_age >= :EmployeeAgeLowerLimit AND emp_age <= :EmployeeAgeUpperLimit; |