InstantSQL C.R.U.D. Example Request 1

[SiouxCityElvis]

Hello,

Anyone have good C.R.U.D.(Create,Read,Update,Delete) examples using InstantSQL. I'm brand new to using this, so I need to do some simple COBOL code accessing SQL Databases.
I'm Running RMCOBOL-85 on Linux. Just downloaded the InstantSQL Jumpstart Kit. Copied file into my /usr/rmcobol/ directory. Uncompressed/Extracted lisql23_linux.tar.gz file. Results look like:

[root@Vallon rmcobol]# tar -xzvf lisql23_linux.tar.gz
LISQLALL.CPY
LISQLDCL.CPY
LISQLDCN.CPY
LISQLDEF.CPY
LISQLDER.CPY
LISQLDPM.CPY
LISQLDQY.CPY
LISQLRPL.CPY
LISQLXCL.CPY
LISQLXCN.CPY
README.TXT
libisql.so
[root@Vallon rmcobol]#

Thanks.
-David

 

[k5tm]

David,

You may have a bit more to do. InstantSQL targets ODBC-compliant database interfaces. On Linux, you must have installed one of the ODBC driver managers (Windows comes with ODBC 'built-in').

Questions:[ul][li]What database?[/li][li]Which driver manager?[/li][li]What are you trying to accomplish (may provide insight into archtectural issues)?[/li][/ul]

Handy on-line InstantSQL Help

Tom Morrison

http://www.liant.com

 

[k5tm]

Here are some links for ODBC on Unix:

iODBC

UnixODBC

Tom Morrison

http://www.liant.com

 

[wahlers]

Yes, I have COBOL templates for SQL CRUD.
I don't have them with me at this moment, but I will publish them soon.

But, more importantly, please understand the following:

Isolate your (business) logic from your physical implementation!

That is; create a separate logical/physical conversion layer. Eventhough SQL is widely used tomorrow you may switch to another grandeur system (object database?) or even some other dialect of SQL.
In such cases it is a good and common practice to recognise and separate different responsibility layers.
Different companies recognise different layers known under various different names.
I don't want to start a 'naming' war, this remark is just added to focus your attention on this concept.

My SQL CRUD examples are to be expected after the weekend.


Regards, Wim Ahlers.

 

[fredericofonseca]

And to add to this.

You must decide if you with to have a Multi DB vendor application(ODBC) or if you are going to restrict yourself to a particular brand or group.

I say this because of a option you have if you decide to stick with ORACLE or DB2 or SYBASE (only one of them or all), then you have also the option of using ESQL (which is standard) instead of InstantSQL.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[k5tm]

BTW, David, in a more direct response to your request, you may type:
[tt] InstantSQL Example[/tt]
in the search box at

http://www.liant.com

and see several examples.

Tom Morrison

http://www.liant.com

 

[SiouxCityElvis]

Tom, I looked at some of the examples...

InstantSQL Query Example 01.
What does the *> employee part mean? COBOL "comments" are denoted by putting in asterisk(*) in the 7th position???

01 ws-emp-data.

          05 ws-number         PIC 9(09).  *> employee number

          05 ws-surname        PIC X(30).  *> employee surname

          05 ws-initials       PIC X(03).  *> employee initials

 

[k5tm]

COBOL "comments" are denoted by putting in asterisk(*) in the 7th position???

That is the old way, and still acceptable. Floating or in-line (or tail) comments are introduced by the floating comment indicator which is the two-character combination [tt]*>[/tt]; anything to the right of the floating comment indicator is considered a comment.


Brought to you by the new COBOL standard...

Tom Morrison

http://www.liant.com

 

[SiouxCityElvis]

Tom,
Wow, I haven't been keeping up on the new COBOL standards. Great to know.

Frederico,
Currently, our Java programmers use MySQL, but eventually they will be using both MySQL and Oracle SQL.

Don't know if that means I should stick with InstantSQL or ESQL. Is ESQL a free jumpstart kit download from Liant as well?

Thanks.
-David

 

[fredericofonseca]

If your're going to use MySQL then stick with InstantSQL, as MySQL does NOT have a COBOL Pre-compiler.

ESQL is used by many of the main DATABASE vendors, and is not supplyed by Liant.

as fas as I know the following DB's ahve it.
DB2 (all OS'es)
Oracle
Sybase
ADABAS
Informix (used to have not sure now)

For Liant's COBOL to access DB2, Oracle and Sybase it requires a "interface" that I sell, but as I said, it is no good for you if you with to use MySQL.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[wahlers]

I promised some templates...but later (sorry about that)

Just a quick reply to: AmorilloElvis:

I don't know instantSQL (except of what I read in the link posted by Tom, see above) but I know that MySQL is NOT a fully developed relational database (note: maybe it has become one in the latest release...I have to check).

If I remember correctly it did miss some referential integrity normally present in commercial relational databases (I am not sure but I believe it also missed - or still misses - some basics of the SQL standard).
My information is dated (last year) and sketchy (just from memory, which is notoriously unreliable ).

Of course, all this does not matter if you just want a non-distrubuted, reasonbly reliable, and above all FREE database!


Regards, Wim Ahlers.

 

[fredericofonseca]

MySQL is only free if the software you develop with it is also FREE SOURCE.
Otherwise you need to buy the commercial version.

Offcourse that for internal use it is hard to say it is a commercial version so people might get away with it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[k5tm]

our Java programmers use MySQL

David, perhaps your Java programmers/network administrator can install MyODBC which is the ODBC driver for MySQL. It is available as a Linux RPM here.

Tom Morrison

http://www.liant.com

 

[SiouxCityElvis]

Yes, I think that's what our Java programmer installed.

Thanks.
-David

 

[SiouxCityElvis]

I'm using a snippet from the Liant's SQL examples.
I'm going step by step and am on the 1st step. Simply trying to establish connection with a database named "test".

I get a compiler error of course...

Open error for file "lisqlrpl.cpy".

   19          +1+001000     COPY "lisqlrpl.cpy".
                                  $             $
*****   1) 0133: E Text-name or file-name in COPY statement is not accessible t
*****   2) 0005: I Scan resumed.
Open error for file "lisqldef.cpy".

   22          +1+001300 COPY "lisqldef.cpy".
                              $             $
*****   1) 0133: E Text-name or file-name in COPY statement is not accessible t
*****   2) 0005: I Scan resumed.
*****   2) 0044: W Repeated period space separator is not permitted.
*****Previous diagnostic message occurred at line 19.

   27                    01 WS-UPDATE-SQL.
                         $  $            $
*****   1) 0073: W Pseudo-text operand expected for REPLACE statement.
*****   2) 0669: E Reserved word "BY" expected. (scan suppressed).
*****   3) 0005: I Scan resumed.
*****Previous diagnostic message occurred at line 22.

   28                       05 FILLER                PIC X(33)
                            $
*****   1) 0466: E Level-number 01 or 77 expected in Working-Storage Section. (
*****Previous diagnostic message occurred at line 27.

   32                    01 WS-EMP-DATA.
                         $
*****   1) 0005: I Scan resumed.
*****Previous diagnostic message occurred at line 28.

   41    000005              SQL CONNECT DATASOURCE sql-ConnectionHandle
                             $
*****   1) 0465: E Verb expected. (scan suppressed).
*****Previous diagnostic message occurred at line 32.

   44                            "".
                                   $
*****   1) 0005: I Scan resumed.
*****Previous diagnostic message occurred at line 41.

   45    000009              IF NOT sql-OK
                                    $
*****   1) 0263: E Identifier is not defined. (scan suppressed).
*****Previous diagnostic message occurred at line 44.

   46    000013                 DISPLAY "<Error connecting to test data source.
                                $
*****   1) 0005: I Scan resumed.
*****Previous diagnostic message occurred at line 45.

   47                        ELSE
                             $
*****   1) 0469: E Scope terminator does not match preceding unterminated verb.
*****Previous diagnostic message occurred at line 46.

   48    000021                 DISPLAY "<Connection Good!.>"
                                $
*****   1) 0005: I Scan resumed.
*****Previous diagnostic message occurred at line 47.

   49                        END-IF.
                             $
*****   1) 0469: E Scope terminator does not match preceding unterminated verb.
*****Previous diagnostic message occurred at line 48.

   50    000029              STOP RUN.
                             $
*****   1) 0005: I Scan resumed.
*****Previous diagnostic message occurred at line 49.


Total generated object size:        404 (X"00000194") bytes

Errors: 8, Warnings: 2, Lines: 50 for program SQLEXAMPLE.

Previous diagnostic message occurred at line 50.
Compilation complete -- Programs: 1, Errors: 8, Warnings: 2.

My Program is...

       IDENTIFICATION DIVISION.
       PROGRAM-ID. SQLEXAMPLE.
debug *
debug * This example is for SQL access from COBOL.
debug *
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       COPY "/usr/rmcobol/LISQLALL.CPY".

       01 WS-UPDATE-SQL.
          05 FILLER                PIC X(33)
             VALUE "UPDATE Employees SET PayPeriod = ".
          05 WS-UPDATE-PERIOD      PIC 99.

       01 WS-EMP-DATA.
          05 WS-NUMBER             PIC 9(09).  *> employee number
          05 WS-SURNAME            PIC X(30).  *> employee surname
          05 WS-INITIALS           PIC X(03).  *> employee initials

       PROCEDURE DIVISION.
       A.

      * Connect to data source named "test"
           SQL CONNECT DATASOURCE sql-ConnectionHandle
               "test"
               "root"
               "".
           IF NOT sql-OK
              DISPLAY "<Error connecting to test data source.>"
           ELSE
              DISPLAY "<Connection Good!.>"
           END-IF.
           STOP RUN.

The password is empty, so I left empty quotes.

Any idea why I'm getting the compile errors?

Thanks.
-David

 

[SiouxCityElvis]

I am running my example in /payroll directory.
The copybooks are in /usr/rmcobol

Don't know if that helps....

Any help would be great.
I don't know why I have this error. My copy books happened to be in all CAPS, so I've tried it with all caps on the "lisqlall.cpy" as well as exactly like the example on Liant's site. No dice either way.

Any help would be great.

Thanks.
-David

 

[k5tm]

Any idea why I'm getting the compile errors?

Because the compiler doesn't have enough information to find the copybooks. It appears as if you changed the pathname in your program:

COPY "/usr/rmcobol/LISQLALL.CPY".

However, that copybook also includes COPY statements. Instead, you should set the RMPATH shell environment variable to include /usr/rmcobol in the directory search sequence. In that manner the compiler, which uses the RMPATH environment variable to find source files, will be able to find the copybooks. If you are unfamiliar with the RMPATH shell environment variable, you will find it described quite thoroughly in the RM/COBOL User's Guide.

Tom Morrison

http://www.liant.com

 

[SiouxCityElvis]

Something must be off with the RMPATH shell. I tried compiling my program from my /payroll directory which resulted in my above stated problems.

Then, I moved my SQLEXAMPLE.CBL program to the directory where my LISQL copy books are located(/usr/rmcobol directory). I tried to compile there with COPY "LISQLALL.CPY" and the results were the same.

-David

 

[SiouxCityElvis]

Okay,

Got with the IT fella here. He said to put this in our profile located in /etc:

RMPATH="/usr/rmcobol"

export JAVA_HOME J2EE_HOME JBOSS_HOME PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC RMPATH

Same bugs remain when trying to compile.

Ideas?
-Thanks.
-David

 

[SiouxCityElvis]

I've discovered that all the copy files on the examples are lowercase, and all my extracted files with same names in the examples are UPPER case. So, I copied all the files to the same names with lowercase. Now, I have upper and lower case files in the /usr/rmcobol directory.

I even took out the RMPATH changes I made and got a clean compile.

Now, I'm getting a runtime error(both with RMPATH set and not set).

[root@Vallon rmcobol]# runcobol SQLEXAMPLE.COB

RM/COBOL Runtime - Version 8.01.00 for Large File Linux (Intel).
RM/COBOL Runtime System issued to HERRING NATIONAL BANK.
Configured for 20 users.
Copyright (c) 1985-2003 by Liant Software Corp. All rights reserved.
Registration Number: 8L-1000-08093-0020

COBOL procedure error 204 (program not found) at line 1130 in program
SQLEXAMPLE.COB (/usr/rmcobol/SQLEXAMPLE.COB) compiled 2004/09/08 10:19:32.
[root@Vallon rmcobol]#

Unfortunately, it looks like the 204 pertains to a copy book somewhere as I don't have 1130 lines in my SQLEXAMPLE.CBL program.

I know on the mainframe I used to work on I could use Expediter to walk through during execution.

How do I pinpoint the problem using RMCOBOL-85 in this type of situation?

Thanks.
-David