Connecting to Your Database
Using Embedded SQL

Since PowerBuilder does not inspect all SQL statement syntax, you can qualify Informix catalog entities as necessary.

For example, these qualifications are all acceptable:

Functions

You can use any function that Informix supports (such as aggregate or mathematical functions) in SQL statements.

This example illustrates how to call the Informix function HEX in a SELECT statement:

SELECT HEX(emp_num) INTO :emp_name_var FROM employee;DBMS=ODB

Qualification

You can use the following transaction management statements with transaction objects to manage connections and transactions for Informix databases:

For example, these qualifications are all acceptable:

See also

Informix using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

This table lists each transaction management statement and describes how it works when you use the Informix IN9 interface to connect to a database:

See also

Informix performance and locking

An important consideration when designing a database application is deciding when connect and commit statements should occur to maximize performance and limit locking and resource use. A connect takes a certain amount of time and can tie up resources during the life of the connection. If this time is significant, then limiting the number of connects is desirable.

After a connection is established, SQL statements can cause locks to be placed on database entities. The more locks there are in place at a given moment in time, the more likely it is that the locks will hold up another transaction.

Rules

No set of rules for designing a database application is totally comprehensive. However, when you design a PowerBuilder application, you should do the following:

Isolation feature

Informix-OnLine databases use the isolation feature to support assorted database lock options. In PowerBuilder, you can use the Lock property of the transaction object to set the isolation level when you connect to the database.

The following example shows how to set the Lock property to Committed read:

// Set the lock property to committed read
// in the default transaction object SQLCA.
SQLCA.Lock = "Committed read"

Example 1

This script uses embedded SQL to connect to a database and insert a row in the ORDER_HEADER table and a row in the ORDER_ITEM table. Depending on the success of the statements in the script, the script executes a COMMIT or ROLLBACK:

// Set the SQLCA connection properties.
SQLCA.DBMS = "IN9"
SQLCA.database = "ORDERS"// Connect to the database. 

CONNECT USING SQLCA;
// Insert a row into the ORDER_HEADER table.
// A ROLLBACK is required only if the first row 
// was inserted successfully.

INSERT INTO ORDER_HEADER (ORDER_ID,CUSTOMER_ID)
   VALUES ( 7891, 129 ); 

// Test return code for ORDER_HEADER insertion
if SQLCA.sqlcode = 0 then

// Since the ORDER_HEADER is inserted,
// try to insert ORDER_ITEM
   INSERT INTO ORDER_ITEM 
      (ORDER_ID, ITEM_NBR, PART_NBR, QTY)
      VALUES ( 7891, 1, '991PLS', 456 ); 

// Test return code for ORDER_ITEM insertion.
   if SQLCA.sqlcode = -1 then

// If insert failed
// ROLLBACK insertion of ORDER_HEADER.
   ROLLBACK USING SQLCA;
   end if
end if

// Disconnect from the database. 
DISCONNECT USING SQLCA; 

Example 2

This example uses the scripts for the Open and Close events in a window and the Clicked event in a CommandButton to illustrate how you can manage transactions in a DataWindow control. Assume the window contains a DataWindow control dw_1 and the user enters data in dw_1 and then clicks the Cb_Update button to send the data to the database.

Since this script uses SetTransObject to connect to the database, the programmer is responsible for managing the transaction.

Window Open event script

// Set the transaction object properties
// and connect to the database. 
// Set the SQLCA connection properties. 
SQLCA.DBMS = "IN9"
SQLCA.database = "ORDERS"

// Connect to the database. 
CONNECT USING SQLCA;

// Tell the DataWindow which transaction object 
// to use. 
SetTransObject( dw_1, SQLCA ) 

CommandButton Clicked event script

// Declare ReturnValue an integer.
// integer   ReturnValue
ReturnValue = Update( dw_1 )

// Test to see if updates were successful.
if ReturnValue = -1 then

// Updates were not successful. Since we used 
// SetTransObject, rollback any changes made 
// to the database. 
   ROLLBACK USING SQLCA;
else


// Updates were successful. Since we used 
// SetTransObject, commit any changes made 
// to the database. 
   COMMIT USING SQLCA;
end if

Window Close event script

// Disconnect from the database.
DISCONNECT USING SQLCA; 

The statements that do not involve cursors or stored procedures are:

See also

Informix DELETE, INSERT, and UPDATE

Informix SELECT

Internally, PowerBuilder processes DELETE, INSERT, and UPDATE statements the same way. PowerBuilder inspects them for any PowerScript variable references and replaces all such references with a constant that conforms to Informix rules for that data type.

Example

Assume you enter the following statement:

DELETE FROM employee WHERE emp_id = :emp_id_var;

In this example, emp_id_var is a PowerScript variable with the data type of integer that has been defined within the scope of the script that contains the DELETE statement. Before the DELETE statement is executed, emp_id_var is assigned a value (for example, 691) so that when the DELETE statement executes, the database receives the following statement:

DELETE FROM employee WHERE emp_id = 691;

See also

Informix SELECT

The SELECT statement contains input variables and output variables. Input variables are passed to the database as part of the execution and the substitution as described above for DELETE, INSERT, and UPDATE. Output variables are used to return values based on the result of the SELECT statement.

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; 

Here emp_id_var, emp_salary_var, and emp_name_var are PowerScript variables defined within the scope of the script that contains the SELECT statement, 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 Informix 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 no errors are returned on the execution, data locations are internally bound 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 line edit 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, 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

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_employees(emp_id,emp_name)
   SELECT emp_id, emp_name FROM employee
      WHERE emp_age >= :EmployeeAgeLowerLimit 
      AND emp_age <= :EmployeeAgeUpperLimit;

In embedded SQL, statements that retrieve data and statements that update data can both involve cursors.

Retrieval statements

The retrieval statements that involve cursors are:

Update statements

The update statements that involve cursors are:

See also

Informix retrieval using cursors

Informix FETCH statements

Informix CLOSE for cursors

Retrieval using cursors is conceptually similar to the singleton SELECT discussed earlier. The main difference is that there can be multiple rows in a result set when you use a cursor and you control when the next row is fetched into PowerScript variables.

If you expect only a single row to exist in the employee table with the specified emp_id, use the singleton SELECT. In a singleton SELECT, you specify the SELECT statement and destination variables in one concise SQL statement:

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

However, when a SELECT may return multiple rows, you must:

Declaring and opening a cursor

Declaring a cursor is tightly coupled with the OPEN statement. The DECLARE specifies the SELECT statement to be executed, and the OPEN actually executes it.

Scroll cursors

When you fetch rows in an Informix database table, using a scroll cursor allows you to fetch rows in the active set in any sequence. That is, you can fetch the next row, previous row, last row, or first row.

To specify that you want to use a scroll cursor when connecting to an Informix database, set the Scroll DBParm parameter to 1. By default, PowerBuilder does not use scroll cursors in an Informix connection (the Scroll parameter is set to 0).

See also

Informix nonupdatable cursors

Informix updatable cursors

Declaring a cursor is similar to declaring a variable; a cursor is a nonexecutable statement just like a variable declaration. The first step in declaring a nonupdatable cursor is to define how the result set looks. To do this, you need a SELECT statement. You must associate the result set with a logical name so you can refer to it in subsequent SQL statements.

Example

Assume the SingleLineEdit control sle_1 contains the state code for the retrieval:

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

// Declare cursor emp_curs for employee table.
// retrieval

DECLARE emp_curs CURSOR FOR
   SELECT emp_id, emp_name FROM Employee 
   WHERE emp_state = :sle_1.text;

// Declare local variables for retrieval.
string   emp_id_var
string   emp_name_var

// Execute the SELECT statement with 
// the current value of sle_1.text.
OPEN emp_curs;

// At this point, if there are no errors, 
// the cursor is available for further processing.

To declare an updatable cursor, use the FOR UPDATE keywords in the declaration.

Example

This statement uses the FOR UPDATE syntax to declare an updatable cursor:

DECLARE emp_curs CURSOR FOR
   SELECT emp_id, emp_name FROM Employee
   WHERE emp_state = :sle_1.text
   FOR UPDATE;

Qualification

The Informix database interfaces support the following FETCH statements:

See also

Informix FETCH NEXT

Informix FETCH FIRST, FETCH PRIOR, and FETCH LAST

In the singleton SELECT, you specify variables to hold the values for the columns within the selected row. The FETCH statement syntax is similar to the syntax of the singleton SELECT. Values are returned INTO a specified list of variables.

// Go get the first row from the result set
FETCH emp_curs INTO :emp_id_var, :emp_name_var; 

If at least one row can be retrieved, this FETCH places the values of the emp_id and emp_name columns from the first row in the result set into the PowerScript variables emp_id_var and emp_name_var. Executing another FETCH statement will place the variables from the next row into specified variables.

FETCH statements typically occur in a loop that processes several rows from a result set (one row at a time); fetch the row, process the variables, and then fetch the next row.

See also

Informix FETCH statements

Informix FETCH FIRST, FETCH PRIOR, and FETCH LAST

In addition to the conventional FETCH NEXT, the Informix interface supports FETCH FIRST, FETCH PRIOR, and FETCH LAST statements.

Example

This cursor example illustrates how you can loop through a result set. Assume the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has been executed.

The statements retrieve rows from the employee table and then display a message box with the employee name in each row that is found.

// Declare the emp_curs 
DECLARE emp_curs CURSOR FOR
   SELECT emp_name FROM EMPLOYEE 
   WHERE emp_state = :sle_1.text;

// Declare a destination variable for employee
// names.
string   emp_name_var 

// Get current value of sle_1.text.
OPEN emp_curs;

// Fetch the first row from the result set.
FETCH emp_curs INTO :emp_name_var;

// Loop through result set until exhausted
DO WHILE SQLCA.sqlcode = 0

// Pop up a message box with the employee name
   MessageBox("Found an employee!",emp_name_var)

// Fetch the next row from the result set
   FETCH emp_curs INTO :emp_name_var;

LOOP

// All done, so close the cursor
CLOSE emp_curs;

See also

Informix FETCH statements

Informix FETCH NEXT

The CLOSE statement terminates processing for the specified cursor. CLOSE releases resources associated with the cursor, and subsequent references to that cursor are allowed only if another OPEN is executed. Although you can have multiple cursors open at the same time, you should close the cursors as soon as possible for efficiency reasons.

Qualification

One of the most significant features of Informix is support for database stored procedures. You can use database stored procedures for:

PowerBuilder supports all of these uses in embedded SQL.

See also

Informix retrieval using database stored procedures

Informix update using database stored procedures

Informix database stored procedures

Informix retrieval using database stored procedures

Qualification

PowerBuilder uses a construct that is very similar to cursors to support retrieval using database stored procedures. In PowerBuilder embedded SQL, there are four commands that involve database stored procedures:

See also

Informix DECLARE and EXECUTE

Informix FETCH

Informix CLOSE

PowerBuilder requires a declarative statement to identify the database stored procedure that is being used and specify a logical name. The logical name is used to reference the procedure in subsequent SQL statements. The general syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR
   Informix_procedure_name
   ({:arg1,:arg2 , ...})
   {USING transaction_object};

where logical_procedure_name can be any valid PowerScript identifier and Informix_procedure_name is the name of the stored procedure in the Informix database. The parentheses after Informix_procedure_name are required even if the procedure has no parameters.

Creating a stored procedure

The default SQL terminator character for the Database painter is a semicolon (;). Informix also uses a semicolon in its stored procedure syntax. Therefore, to create a stored procedure in the Database painter, you must change the SQL terminator character to something other than a semicolon, such as a backquote (`).

To change the Database painter's SQL terminator character, type the character you want in the SQL Terminator Character box in the Database Preferences dialog box.

The parameter references can take the form of any valid parameter string that Informix accepts. PowerBuilder does not inspect the parameter list format except for purposes of variable substitution. The USING clause is required only if you are using a transaction object other than the default transaction object (SQLCA).

Example

Assume a stored procedure proc1 is defined as:

CREATE PROCEDURE proc1 AS
   SELECT emp_name FROM employee 

To declare that procedure for processing within PowerBuilder, enter:

DECLARE emp_proc PROCEDURE FOR proc1; 

Note that this declaration is a nonexecutable statement, just like a cursor declaration. Where cursors have an OPEN statement, procedures have an EXECUTE statement.

When an EXECUTE statement is executed, the procedure is invoked. The EXECUTE refers to the logical procedure name:

EXECUTE emp_proc; 

Issuing EXECUTE statements

Use PowerBuilder embedded SQL syntax when you enter an embedded EXECUTE statement in a script; do not enter the PROCEDURE keyword. Use this syntax:

EXECUTE procedure_name; 

Specify the EXECUTE statement the same way whether or not a stored procedure takes arguments. The arguments used in the DECLARE statement get passed automatically, without your having to state them in the EXECUTE statement.

To access rows returned in a result set, you use the FETCH statement the same way you use it for cursors. The FETCH statement can be executed after any EXECUTE statement that refers to a procedure that returns a result set.

Example 1

FETCH emp_proc INTO :emp_name_var; 

You can use this FETCH statement only to access values produced with a SELECT statement in a database stored procedure. You cannot use the FETCH statement to access computed rows.

The result sets that will be returned when a database stored procedure executes cannot be determined at compile time. Therefore, you must code FETCH statements that exactly match the format of a result set returned by the stored procedure when it executes.

Example 2

Assume you changed the second fetch statement in the preceding statement to:

FETCH emp_proc2 INTO :part_var1,:part_var2,:part_var3;

The code would compile without errors, but an execution error would occur because the number of columns in the FETCH statement does not match the number of columns in the current result set. The second result set returns values from only one column.

If a database stored procedure returns a result set, it must be closed when processing is complete.

Closing a procedure looks the same as closing a cursor:

CLOSE emp_proc; 

As with cursors, if a procedure executes successfully and returns at least one result set and is not closed, a result set is pending and no SQL commands other than the FETCH can be executed. Procedures with result sets should be closed as soon as possible.

The procedure remains open until you close it, execute a COMMIT or a ROLLBACK, or end the database connection.

Database stored procedures that only perform updates and do not return a result set are handled in much the same way as procedures that return a result set. The only difference is that after the EXECUTE procedure_name statement executes, no result set is pending and no CLOSE statement is required.

Using the SQLCode property

If you know that a particular procedure can never return a result set, only the EXECUTE statement is required. If there is a procedure that may or may not return a result set, you can test the SQLCode property of the referenced transaction object for +100 (the code for not found) after the EXECUTE.

This table shows all possible values for SQLCode after an EXECUTE:

Example 1

This example illustrates how to execute a database stored procedure that does not return a result set:

// good_employee is an Informix stored procedure. 
// Declare the procedure.
DECLARE good_emp_p 1roc PROCEDURE FOR good_employee;
EXECUTE good_emp_proc;

// Test return code. Allow for +100 since you do 
// not expect a result set.
if SQLCA.sqlcode = -1 then

// Issue error message since it failed.
   MessageBox("Stored Procedure Error!", &
   SQLCA.sqlerrtext)
end if 

Example 2

This example illustrates how to pass parameters to a database stored procedure that returns a result set. Emp_id_var has been set elsewhere to 691:

// Get_employee is an Informix stored procedure.
// Declare the procedure.
DECLARE get_emp_proc PROCEDURE FOR 
   get_employee @emp_id_parm = :emp_id_var;

// Declare a destination variable for emp_name
string   emp_name_var

// Execute the stored procedure using the 
// current value for emp_id_var. 
EXECUTE get_emp_proc;

// Test return code to see if it worked.
if SQLCA.sqlcode = 0 then

// We got a row, so fetch it and display it.
   FETCH get_emp_proc INTO :emp_name_var;

// Display the employee name.
   MessageBox("Got my employee!",emp_name_var)

// You are all done, close the procedure. 
   CLOSE Get_emp_proc; 
end if 

You can use database stored procedures as a data source for DataWindow objects. The following considerations apply:

When you use database stored procedures in a PowerBuilder application, keep the following points in mind: