Connecting to Your Database
Using Embedded SQL with ADO.NET

PowerBuilder requires a declarative statement to identify the database stored procedure that is being used and a logical name that can be referenced in subsequent SQL statements.

Syntax for declaring a procedure in SQL Server:

DECLARE logical_procedure_name PROCEDURE FOR 
  procedure_name 
  @Param1 = value1, @Param2 = value2 , 
  @Param3 = value3 OUTPUT, 
  {USING transaction_object} ;

Syntax for declaring a procedure in Oracle:

With parameter names:

declare logical_procedure_name procedure for procedure_name(Param1=> value1,  Param2=> value2) {USING transaction_object};

Without parameter names:

declare logical_procedure_name procedure for procedure_name(value1,value2) {USING transaction_object};

Syntax for declaring a procedure in PostgreSQL:

With parameter names:

declare logical_procedure_name procedure for procedure_name(Param1 = value1) {USING transaction_object};

Without parameter names:

declare logical_procedure_name procedure for procedure_name(value1) {USING transaction_object};

where logical_procedure_name can be any valid PowerScript data identifier and procedure_name is the name of the stored procedure in the database.

The parameter references can take the form of any valid parameter string that ADO.NET accepts. PowerBuilder does not inspect the parameter list format except for purposes of variable substitution. You must use the reserved word OUTPUT to indicate an output parameter (except PostgreSQL which cannot use the OUTPUT keyword). The USING clause is required only if you are using a transaction object other than the default transaction object (SQLCA).

Example 1 (for SQLServer)

Assume a stored procedure proc1 is defined as:

create procedure proc1
(@i_id int,
@i_age int,
@o_name nvarchar(50) output)
AS
 BEGIN  
  SELECT @o_name = emp_fname  FROM employee where id = @i_id and age = @i_age; 
END;

To declare that procedure for processing within PowerBuilder, enter:

declare lcur_pro procedure for proc1(@i_id = :li_id,@i_age = :li_age,@o_name = :ls_name output);
execute lcur_pro;
fetch  lcur_pro  INTO :ls_name;

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 executes, the procedure is invoked. The EXECUTE refers to the logical procedure name:

EXECUTE emp_proc;

Example 2 (for SQL Server)

To declare a procedure with input and output parameters, enter:

DECLARE sp_duration PROCEDURE FOR pr_date_diff_prd_ken
   @var_date_1 = :ad_start,
   @var_date_2 = :ad_end,
   @rtn_diff_prd = :ls_duration OUTPUT;

Example 3 (for Oracle)

Assume a stored procedure proc1 is defined as:

create or replace PROCEDURE proc1  
(i_id IN INT,
i_age IN INT,
o_name OUT varchar2
)
AS 
BEGIN
  SELECT emp_name into o_name FROM employee where id = i_id and age = i_age;
END proc1;

To declare that procedure for processing within PowerBuilder, enter:

declare lcur_pro procedure for proc1(i_id =>:li_id,i_age => :li_age);
execute lcur_pro;
fetch  lcur_pro  INTO :ls_name;

Example 4 (for PostgreSQL)

Assume a stored procedure proc1 is defined as:

create procedure proc1
(in i_id int,
in i_age int,
out o_name varchar
)
LANGUAGE 'plpgsql'
AS $BODY$
begin
 SELECT emp_name into o_name FROM employee where id = i_id and age = i_age;
end
$BODY$;

To declare that procedure for processing within PowerBuilder, enter:

declare lcur_pro procedure for proc1(:li_id,:li_age,:ls_name);
execute lcur_pro;
fetch  lcur_pro  INTO :ls_name;