[archive] Calling a Stored Procedure from Acu Cobol

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Hi,
I think that the only way is to use ADO objects.
You've to call ADO (ActiveX Data Objects) and then use them to issue an ADO command that'll first passes parameters to your store proc and than calls it.
bye Giovanni.

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Hi,
I think that the only way is to use ADO objects.
You've to call ADO (ActiveX Data Objects) and then use them to issue an ADO command that'll first passes parameters to your store proc and than calls it.
bye Giovanni.

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Many thanks for your suggestion Giovanni.

Regards


Richard

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Many thanks for your suggestion Giovanni.

Regards


Richard

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

You will need version 7.2 or higher to do this:

Two types of stored procedures can be executed. Those that return no result
sets, and those that return a single result set. Any stored procedures which
return more than one result set can not be executed (rather, results will
not be available to the COBOL program). Output parameters and return code
values are supported.

Consider the following stored procedure (which has one output parameter
and also returns a value):

create procedure sp_listcustomer
@lastname varchar(100) = NULL,
@numrows int output
as
select @numrows = count(*) from customer where c_last_name = @lastname
select c_last_name, c_first_name, c_birthday from customer
where c_last_name = @lastname
return 23

This stored procedure returns a single result set (c_last_name, c_first_name,
and c_birthday). Depending on the rows in the customer table, this may
consist of many rows.

There are two ways to execute this from a COBOL program

1) Ignore the result sets, and just use the output params and return code.
This can be done with the following code:

display "Enter the name to search for: ", no.
accept c-last-name.

EXEC SQL exec :ret-code = sp_listcustomer :c-last-name,
:num-rows out
END-EXEC.

Note that c-last-name, ret-code and num-rows need to have been declared as
valid variables in a DECLARE section. When executed this way, all the
rows returned by the stored procedure are thrown away, and only the num-rows
variable and the ret-code variable will be changed. ret-code will be the
value 23 (based on the "return 23" in the stored procedure) and num-rows
will be the number of rows which match the WHERE.

2) Have the result set returned to the COBOL program. This requires a cursor
to be declared, and is done with the following code:

display "Enter the name to search for: ", no.
accept c-last-name.
EXEC SQL declare spcursor cursor for
exec :ret-code = sp_listcustomer :c-last-name,
:num-rows out
END-EXEC.

EXEC SQL
open spcursor
END-EXEC.

move 0 to num-rows-read.
perform until SQLCODE not = 0
EXEC SQL
FETCH spcursor into
:c-last-name, :c-first-name,
:c-birthday
END-EXEC
if SQLCODE = 0
add 1 to num-rows-read
display c-last-name, ", ", c-first-name, ", ",
c-birthday
end-if
end-perform.
if num-rows not = num-rows-read
display "stored procedure error, " num-rows,
" not = ", num-rows-read
end-if.

Note that c-last-name, ret-code, num-rows, c-first-name and c-birthday all
need to have been declared as valid variables in a DECLARE section. When
executed this way, the num-rows and ret-code variables will be set to the
values given by the stored procedure at the time the cursor is opened.
You must then execute FETCH commands in order to get the result set columns.
This particular example tests the number of rows actually fetched against
the num-rows value returned by the stored procedure.

In general, there are two new types of syntax allowed by the precompiler
when MSSQL syntax is in effect (-Pk mssql).

1) EXEC SQL EXEC [:status-var = ] procedure-name [[:param-var [out[put]]],
...]

2) EXEC SQL DECLARE cursor-name CURSOR FOR EXEC
[:status-var = ] procedure-name [[:param-var [out[put]]], ...]

The second form requires all the usual steps necessary for cursors - you must
open the cursor, and then fetch from it until all the rows have been fetched.

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

You will need version 7.2 or higher to do this:

Two types of stored procedures can be executed. Those that return no result
sets, and those that return a single result set. Any stored procedures which
return more than one result set can not be executed (rather, results will
not be available to the COBOL program). Output parameters and return code
values are supported.

Consider the following stored procedure (which has one output parameter
and also returns a value):

create procedure sp_listcustomer
@lastname varchar(100) = NULL,
@numrows int output
as
select @numrows = count(*) from customer where c_last_name = @lastname
select c_last_name, c_first_name, c_birthday from customer
where c_last_name = @lastname
return 23

This stored procedure returns a single result set (c_last_name, c_first_name,
and c_birthday). Depending on the rows in the customer table, this may
consist of many rows.

There are two ways to execute this from a COBOL program

1) Ignore the result sets, and just use the output params and return code.
This can be done with the following code:

display "Enter the name to search for: ", no.
accept c-last-name.

EXEC SQL exec :ret-code = sp_listcustomer :c-last-name,
:num-rows out
END-EXEC.

Note that c-last-name, ret-code and num-rows need to have been declared as
valid variables in a DECLARE section. When executed this way, all the
rows returned by the stored procedure are thrown away, and only the num-rows
variable and the ret-code variable will be changed. ret-code will be the
value 23 (based on the "return 23" in the stored procedure) and num-rows
will be the number of rows which match the WHERE.

2) Have the result set returned to the COBOL program. This requires a cursor
to be declared, and is done with the following code:

display "Enter the name to search for: ", no.
accept c-last-name.
EXEC SQL declare spcursor cursor for
exec :ret-code = sp_listcustomer :c-last-name,
:num-rows out
END-EXEC.

EXEC SQL
open spcursor
END-EXEC.

move 0 to num-rows-read.
perform until SQLCODE not = 0
EXEC SQL
FETCH spcursor into
:c-last-name, :c-first-name,
:c-birthday
END-EXEC
if SQLCODE = 0
add 1 to num-rows-read
display c-last-name, ", ", c-first-name, ", ",
c-birthday
end-if
end-perform.
if num-rows not = num-rows-read
display "stored procedure error, " num-rows,
" not = ", num-rows-read
end-if.

Note that c-last-name, ret-code, num-rows, c-first-name and c-birthday all
need to have been declared as valid variables in a DECLARE section. When
executed this way, the num-rows and ret-code variables will be set to the
values given by the stored procedure at the time the cursor is opened.
You must then execute FETCH commands in order to get the result set columns.
This particular example tests the number of rows actually fetched against
the num-rows value returned by the stored procedure.

In general, there are two new types of syntax allowed by the precompiler
when MSSQL syntax is in effect (-Pk mssql).

1) EXEC SQL EXEC [:status-var = ] procedure-name [[:param-var [out[put]]],
...]

2) EXEC SQL DECLARE cursor-name CURSOR FOR EXEC
[:status-var = ] procedure-name [[:param-var [out[put]]], ...]

The second form requires all the usual steps necessary for cursors - you must
open the cursor, and then fetch from it until all the rows have been fetched.

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

You will need version 7.2 or higher to do this:

Two types of stored procedures can be executed. Those that return no result
sets, and those that return a single result set. Any stored procedures which
return more than one result set can not be executed (rather, results will
not be available to the COBOL program). Output parameters and return code
values are supported.

Consider the following stored procedure (which has one output parameter
and also returns a value):

create procedure sp_listcustomer
@lastname varchar(100) = NULL,
@numrows int output
as
select @numrows = count(*) from customer where c_last_name = @lastname
select c_last_name, c_first_name, c_birthday from customer
where c_last_name = @lastname
return 23

This stored procedure returns a single result set (c_last_name, c_first_name,
and c_birthday). Depending on the rows in the customer table, this may
consist of many rows.

There are two ways to execute this from a COBOL program

1) Ignore the result sets, and just use the output params and return code.
This can be done with the following code:

display "Enter the name to search for: ", no.
accept c-last-name.

EXEC SQL exec :ret-code = sp_listcustomer :c-last-name,
:num-rows out
END-EXEC.

Note that c-last-name, ret-code and num-rows need to have been declared as
valid variables in a DECLARE section. When executed this way, all the
rows returned by the stored procedure are thrown away, and only the num-rows
variable and the ret-code variable will be changed. ret-code will be the
value 23 (based on the "return 23" in the stored procedure) and num-rows
will be the number of rows which match the WHERE.

2) Have the result set returned to the COBOL program. This requires a cursor
to be declared, and is done with the following code:

display "Enter the name to search for: ", no.
accept c-last-name.
EXEC SQL declare spcursor cursor for
exec :ret-code = sp_listcustomer :c-last-name,
:num-rows out
END-EXEC.

EXEC SQL
open spcursor
END-EXEC.

move 0 to num-rows-read.
perform until SQLCODE not = 0
EXEC SQL
FETCH spcursor into
:c-last-name, :c-first-name,
:c-birthday
END-EXEC
if SQLCODE = 0
add 1 to num-rows-read
display c-last-name, ", ", c-first-name, ", ",
c-birthday
end-if
end-perform.
if num-rows not = num-rows-read
display "stored procedure error, " num-rows,
" not = ", num-rows-read
end-if.

Note that c-last-name, ret-code, num-rows, c-first-name and c-birthday all
need to have been declared as valid variables in a DECLARE section. When
executed this way, the num-rows and ret-code variables will be set to the
values given by the stored procedure at the time the cursor is opened.
You must then execute FETCH commands in order to get the result set columns.
This particular example tests the number of rows actually fetched against
the num-rows value returned by the stored procedure.

In general, there are two new types of syntax allowed by the precompiler
when MSSQL syntax is in effect (-Pk mssql).

1) EXEC SQL EXEC [:status-var = ] procedure-name [[:param-var [out[put]]],
...]

2) EXEC SQL DECLARE cursor-name CURSOR FOR EXEC
[:status-var = ] procedure-name [[:param-var [out[put]]], ...]

The second form requires all the usual steps necessary for cursors - you must
open the cursor, and then fetch from it until all the rows have been fetched.

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Many thanks Dilbert.

The extensive example code and expanation is really appreciated.

Would your solution require the need of AcuSQL?


Regards


Richard

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Many thanks Dilbert.

The extensive example code and expanation is really appreciated.

Would your solution require the need of AcuSQL?


Regards


Richard

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Yes AcuSQL is needed

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Yes AcuSQL is needed

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Thanks Dilbert.

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Thanks Dilbert.

[Migrated content. Thread originally posted on 07 December 2007]

Good afternoon.

Is there a method of calling an existing Stored Procedure from and Acu Cobol program without the use of AcuSQL?

Database used is MS SQL 2000.


Regards to all


Richard

Thanks Dilbert.