Introduction
Elastic COBOL accesses most databases through JDBC. As far as a COBOL programmer is concerned the interface to the database is managed through EXEC SQL statements.
JDBC
The Microsoft® SQL Server™ Driver for JDBC™ is a Type 4 JDBC driver that provides connectivity for the enterprise Java environment. The Microsoft JDBC Driver for SQL Server can be located at:
http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx
The JDBC (Java DataBase Connectivity) driver must be available to the running program. The JDBC driver is specific to the database, in this case SQL Server, and these JDBC drivers come from the database vendor such as IBM, Oracle and Microsoft. It will be a file ending in .zip or .jar (do not extract it). The filename for it changes over time, so check your database product documentation.
To make the driver available to an Elastic COBOL project within the IDE, right-click the project, select Properties. This is where the COBOL and Java settings for the project are available. Since this is an element of Java functionality being exposed to the Elastic COBOL application, select Java Build Path.
The libraries tab lists all Java libraries available to the program. Using the 'External JARs' button, add the SQL JDBC driver library to the project. It's now available to the program at runtime.
Establishing the SQL Connection
To enable it from the program, you must connect to the database before you may use it. The typical connection statement is of the form:
EXEC SQL CONNECT TO
"jdbc:sqlserver://127.0.0.1:1433;databaseName=hcidb;selectMethod=direct;"
USER 'sa'
PASSWORD 'sapass'
DRIVER 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
END-EXEC.
CONNECT TO describes the URL for the connection. It always begins with “jdbc:” and specifies which driver to use. The host is the hostname location of the database. “1433” is the default port for SQL Server, but if you have SQL Server using another port change it to match. And “databaseName” is the database you want to connect to.
USER and PASSWORD can refer to COBOL host variables (e.g. “:userid”, “:password”) rather than literals. However they can also be specified directly if desired, although the user is often prompted for password so host variables are most often used.
DRIVER specifies the classname of the JDBC driver. The classname is typically published in the JDBC driver's documentation (often by showing a snippet of Java code Class.forName("drivername")).
NOTE: A SQL Server install that is local to the machine has a faster driver with a separate name; both drivers (one for local, one for remote) are usually included with the JDBC driver package.
NOTE: Specifying SERIALIZABLE is a frequently used option.
The connection to SQL Server from within an application server will be a little different, but all changes are encapsulated within the CONNECT statement. If running in a managed environment such as an application server where it says a DataSource is available, the CONNECT TO piece is just "ds:name" where name is exported by the application server (and so the DRIVER clause is not required).
Elastic COBOL SQL General Notes
SQLCODE and SQLSTATE can be any definition sufficient to hold the data, such as PIC S999 and PIC X(5). Their usage clauses do not matter.
When creating a new program, you can select “SQL Program” as a template to give a basic reminder of the SQL uses.
SQL Server General Notes
Due to an implementation issue of the JDBC package from Microsoft, Elastic COBOL’s support for SQL Server means that in order for output parameters to work, the input parameters must be treated as output parameters as well.
The sample below illustrates that itemno and itemname are actually “input” parameters although they are also both specified as “out” parameters. Only the parameter called success is the real “out” parameter.
COBOL Sample Program
The following program illustrates how a stored procedure can be called using Elastic COBOL in conjunction with Microsoft SQL Server:
* Copyright (C) 2010-2013 Heirloom Computing Inc. All Rights Reserved.
* This sample shows the call to a SQL Server stored procedure.
IDENTIFICATION DIVISION.
PROGRAM-ID. SqlServerStoredProcedure.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
77 itemno PIC X(10).
77 itemname PIC X(10).
77 success PIC X(5).
EXEC SQL END DECLARE SECTION END-EXEC.
*EXEC SQL INCLUDE SQLCA END-EXEC.
copy "SQLCA".
******************************************************************
* P R O C E D U R E D I V I S I O N
******************************************************************
PROCEDURE DIVISION.
MAIN SECTION01.
begin.
EXEC SQL
CONNECT TO "jdbc:sqlserver://127.0.0.1:1433;databaseName=hcidb;
- selectMethod=direct;"
USER 'sa'
PASSWORD 'sapass'
DRIVER 'com.microsoft.jdbc.sqlserver.SQLServerDriver'
END-EXEC.
IF SQLCODE NOT = 0
DISPLAY "FAILED " SQLCODE UPON SYSERR
DISPLAY SQLERRM UPON SYSERR
GO TO FIN.
move "20" to itemno.
move "table" to itemname.
move "no" to success.
DISPLAY "input values:", space, itemno, space, itemname,
space, success upon sysout.
EXEC SQL
exec testproc :itemno,:itemname,:success
END-EXEC.
display "success= ", success upon sysout.
go to FIN.
FIN.
DISPLAY "End of Program." upon sysout.
stop run.
Calling a Stored Procedure
A sample stored procedure:
CREATE PROCEDURE [testproc]
(@itemno [varchar](10) out,
@itemname [varchar](10) out,
@success [varchar](5) out)
AS INSERT INTO [hcidb].[dbo].[testdata]
values (@itemno, @itemname )
set @success = 'yes'
commit
GO
Input and output parameters as illustrated in the COBOL program, itemno and itemname are both input parameters while success is the output parameter. The variable success is initially "no". After running the “stored procedure” using the following lines it should output the line "success= yes".
EXEC SQL
exec testproc :itemno,:itemname,:success
END-EXEC.display "success= ", success upon sysout.
Due to a limitation in the capability of the Microsoft JDBC driver, itemno and itemname are both specified as output parameters although they are only used as input parameters.
0 Comments