declare a unique variable for mssql query in visual cobol

in sql management studio i can use following command:

use lohn;
go
declare @loc_zahl as int
set @loc_zahl = 0
if not exists (select * from sys.objects where object_id = object_id(N't_svdata') and type in (N'U'))
begin
   raiserror('t_svdata existiert', 16, 1)
end
/* select * from t_svdata */
select @loc_zahl = count(svd_schl) from t_svdata
if @loc_zahl > 0
  delete from t_svdata
/* drop table t_svdata */
go
select * from t_svdata
go

but when i use this in vc, the declare will not be accepted.

How to declare a unique variable?

Is there a precompiler or debugger to debug the mssql commands in a cobol program

here the cobol statement:

EXEC SQL                                                     
use lohn /* new command */

declare @loc_zahl int                                        
set @loc_zahl = 0                                            
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =       
           OBJECT_ID(N't_svdata') AND type in (N'U'))        
   BEGIN                                                     
     RAISERROR('t_svdata existiert', 16 , 1)                 
   END                                                       
else                                                         
  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
           OBJECT_ID(N't_svdata') AND type in (N'U'))        
     BEGIN                                                   
        RAISERROR('t_svdata existiert', 16 , 1)              
     END                                                     
ELSE                                                         
  print 'No problems here'                                   
select @loc_zahl = count(svd_schl) from t_svdata             
if @loc_zahl > 0                                             
   delete from t_svdata                                      
   /* drop table t_svdata */                                 
END-EXEC                                                     

Thanks for help

This syntax is not directly supported within an EXEC SQL statement but you could create it as a stored procedure and then call it.

This syntax is not directly supported within an EXEC SQL statement but you could create it as a stored procedure and then call it.

do you have a little example?

Thanks Chris!

where is the difference between 'call' and 'execsp'?

Example 1:

CALL myProc (:param1,:param2)   --> myProc is a cobol program with linkage section and 2 parameters?

Example 2:

CALL myProc (namedParam=:paramValue)  --> where is defined namedParam?

Example 3:

EXECSP myProc :param1,:param2 --> where is defined myProc? is it a cobol program or a sql procedure/statement

Example 4:

EXECSP :myResult = myFunction namedParam = :paramValue --> Where is defined myFunction and namedParam? Cobol or sql?

i think with working examples that will be better anderstand for all cobol and sql users!

My posted script will completly works, but not the necessary hostvariable! Why is this not possible to include this in excel sql?

The OpenESQL SQL pre-processor generally supports ANSI standard SQL. declare is a T-SQL specific syntax (perhaps others as well).

Also you are not declaring or using a host variable. Host variable means a variable that is available in the host language that is using the embedded SQL. If you want to declare a host variable try this: ( it compiles but I have not tested it)

working-storage section.

...

01 loc_zahl pic s9(8) comp-5.

...

procedure division.

EXEC SQL
* declare @loc_zahl int
set :loc_zahl = 0
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N't_svdata') AND type in (N'U'))
BEGIN
RAISERROR('t_svdata existiert', 16 , 1)
END
else
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N't_svdata') AND type in (N'U'))
BEGIN
RAISERROR('t_svdata existiert', 16 , 1)
END
ELSE
print 'No problems here'
select :loc_zahl = count(svd_schl) from t_svdata
if :loc_zahl > 0
delete from t_svdata
/* drop table t_svdata */
END-EXEC

This syntax is not directly supported within an EXEC SQL statement but you could create it as a stored procedure and then call it.

Hi Michael,

i know how to set/declare a sql-hostvariable in a cobol program, working over 25 years with sql databases.

But in this published script the cobol host variable is not allowed. The script in Management studio works fine.

The script in exec sql works also, but the "declare @loc_zahl int " is not accepted, all other will be processed.

i use this script to verify if the sql database exist, and how many lines of record exists, than delete lines or drop database.

Correction on my post above with the host variable syntax. That whole block will not work with ESQL. It is TSQL that is not supported via the OpenESQL pre-processor. As Chris suggested, you will need to create a stored procedure with your script and CALL the stored procedure via ESQL and a CALL statement

e.g..

EXEC SQL

    CALL ProcedureName

END-EXEC

or if the procedure returns a value

EXEC SQL

    : hostvar =  CALL ProcedureName

END-EXEC

the complete script works also under exec sql, when i begin the script with "use database_name", example use adventure

Yes, this is directly supported within an EXEC SQL

GREAT!!!

Which database and ESQL pre-processor are you currently using...i.e. the one that you say it works with.

What is the COBOL product/version and ESQL pre-compiler version.

In Visual COBOL the pre-processor for SQL Server SQL is OpenESQL. It does not support the TSQL script you are using

i use microsoft sql database with odbc driver 11.

i can see the result.

if @loc_zahl > 0 i delete all records

if @loc_zahl = 0 i drop the database and create a new database

cg

I understand you are using MS SQL Server, but which COBOL product are you using? I would expect TSQL Script to work in the SQL management studio. However, the support for SQL in COBOL and ESQL is going to be ANSI standard. TSQL has extensions that are proprietary to MS SQL Server. This is why the recommendation is to use a stored procedure and call it from COBOL.

Hello Michael,

i work with netexpress 5.0 and 5.1, also with visual Cobol 9.0 and the script works fine in all situation, i must define at begin the used database with the command "use mydb", then the variable @loc_zahl will be accepted. I had work withh all version of mssql from 2000 to version 2019.

Can you explain your command "call procedure_name". Where is stored the procedure? Do you have a example with "execsp"?

I will use in future more new possibilities as describe in the documentation for vc 9.0

Thanks

Chris, the complete script works also under exec sql, when i begin the script with "use database_name", example use adventure

Yes, this is directly supported within an EXEC SQL

GREAT!!!

You can create a stored procedure in COBOL and then call it. I had to put in the retcode parameter as the compiler didnt like the empty parens. In this case it isnt used for anything. Errors and messages will be returned in the sqlcode and sqlerrmc fields.

           EXEC SQL DROP PROCEDURE mysp END-EXEC
           EXEC SQL CREATE PROCEDURE mysp
              (@retcode int output )
             AS
             declare @loc_zahl int                                        
             set @loc_zahl = 0                                            
             IF EXISTS (SELECT * FROM sys.objects WHERE object_id =       
                OBJECT_ID(N't_svdata') AND type in (N'U'))        
                BEGIN                                                     
                   RAISERROR('t_svdata existiert', 16 , 1)                 
                END                                                       
             else                                                         
                IF NOT EXISTS (SELECT * FROM sys.objects
                   WHERE object_id = OBJECT_ID(N't_svdata')
                      AND type in (N'U'))
                   BEGIN                                                   
                      RAISERROR('t_svdata existiert', 16 , 1)              
                   END                                                     
                ELSE                                                         
                   print 'No problems here'                                   
                   select @loc_zahl = count(svd_schl) from t_svdata             
                   if @loc_zahl > 0                                             
                      delete from t_svdata                                      
                    
          END-EXEC
          exec sql commit work end-exec
          exec sql call mysp(:retcode out) end-exec
          display sqlcode

Thanks Chris, i will test your procedure!

I think that here must be a "use database_name" to know where will be found this procedure or not?
I don't have work in history with call procedure or execsp, this will be a new feature.

But my script works fine in Netexpress5.0, 5.1 and Visual Cobol 9.0 in EXEC SQL --- END EXEC

the same way as in Microsoft Management Studio!

You can create a stored procedure in COBOL and then call it. I had to put in the retcode parameter as the compiler didnt like the empty parens. In this case it isnt used for anything. Errors and messages will be returned in the sqlcode and sqlerrmc fields.

           EXEC SQL DROP PROCEDURE mysp END-EXEC
           EXEC SQL CREATE PROCEDURE mysp
              (@retcode int output )
             AS
             declare @loc_zahl int                                        
             set @loc_zahl = 0                                            
             IF EXISTS (SELECT * FROM sys.objects WHERE object_id =       
                OBJECT_ID(N't_svdata') AND type in (N'U'))        
                BEGIN                                                     
                   RAISERROR('t_svdata existiert', 16 , 1)                 
                END                                                       
             else                                                         
                IF NOT EXISTS (SELECT * FROM sys.objects
                   WHERE object_id = OBJECT_ID(N't_svdata')
                      AND type in (N'U'))
                   BEGIN                                                   
                      RAISERROR('t_svdata existiert', 16 , 1)              
                   END                                                     
                ELSE                                                         
                   print 'No problems here'                                   
                   select @loc_zahl = count(svd_schl) from t_svdata             
                   if @loc_zahl > 0                                             
                      delete from t_svdata                                      
                    
          END-EXEC
          exec sql commit work end-exec
          exec sql call mysp(:retcode out) end-exec
          display sqlcode

Chris, i have a compile error on :retcode, this variable must be defined in the cobol program as 77 retcode pic s9(08) comp-5.

@retcode will not be accepted

Must test if this field will be filled with the correct value!