[archive] Stored Procedures

[Migrated content. Thread originally posted on 20 March 2003]

I would like to know if it is possible to call
SQL Server Stored Procedures from AcuCobol programs?
If anyone has any experience with this please let me know!

Thanks a lot

Yes, I do this when using Acu4GL for MSSQL. I call the sql.acu program from Acucorp. It can be passed a string that can consist of almost any t-sql or ddl command you like including the execution of a stored procedure. The problem is you can't receive back results. So, your stored procedure will have to store the results in a table and your acucobol program can open this sql table and read it. Remember to generate an XFD for this table.

** sample call to mssql stored procedure via sql.acu and acu4gl.
STRING "EXEC sp_activity_stats ",
"'", STARTDATE, "',"
"'", ENDDATE, "'"
DELIMITED BY SIZE
INTO SQL-COMMAND.
CALL "sql.acu" USING SQL-COMMAND.
IF RETURN-CODE = 0
STRING ACTSTATS-FILE-NAME, "-HOST"
DELIMITED BY SIZE
INTO ACTSTATS-FILE-HOST
SET ENVIRONMENT ACTSTATS-FILE-HOST TO "MSSQL"
OPEN INPUT ACTSTATS-FILE.
|Add code here to start/read next the ACTSTATS-FILE

The SQL-COMMAND string we have is 100 bytes. I'm not sure sql.acu will allow for a longer one. Shouldn't be a problem unless you need many or long parmamters to the stored procedure.

AcuSQL also can also call stored procedures but I'm not certain about the returning results issue. I've never used AcuSQL.

If you are not using Acu4GL or AcuSQL then you could probably make calls to the ms sql command line tools ISQL and OSQL and route results to a text file and read the text file. For administering and managing the sql server you could get fancier and use SQL-DMO Automation with the COM/ActiveX support in acucobol. There are several artices and VB samples on MSDN that show how to use SQL-DMO Automation. Here is one article: http://msdn.microsoft.com/msdnmag/issues/01/05/sqldmo/default.aspx

Also, for other sql server data access programming options that you can use with Acucobol Activex/COM support(like ADO) look to this article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bldgapps/ba_highprog_3fg3.asp

ADO is probably the most popular, or at least most recommended by microsoft, method for accessing sql server data. It has solid support for executing stored procedures. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg01_1kwv.asp