RE: SQLMOD (Was RE: ORACLE VS. SYBASE)

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Tue, 25 Sep 2001 14:18:44 -0700
Message-ID: <F001.00398612.20010925143528@fatcity.com>

SQLMOD ("SQL Module") is how DEC implemented 3GL access to DEC rdb. The dev/programmer created a SQLMOD file that contained ALL SQL statements. This module (file) was then "compiled" separately to create an object file. The 3GL program then just needed to call the functions and procedures defined in the SQLMOD, instead of having to worry about the mess of pre-compiling and binding variables and the like.

A very simple way of implementing SQL in *ANY* 3GL language! If you think about it, all the SQL calls are in the "compiled" SQLMOD that gets linked in with the 3GL code to create the executable. So, as far as the 3GL is concerned, the SQL calls are just another external call.

I think there is also a way to make the SQL more dynamic, but it's been too long since I've actually worked with this stuff.

How's about an example?

Here's an excerpt from a SQLMOD file:

MODULE         MY_SQL
LANGUAGE       BASIC
PARAMETER      COLONS
ALIAS          MY_RDB_DB   

DECLARE MY_RDB_DB ALIAS COMPILETIME filename 'my_rdb_db'
                RUNTIME FILENAME 'MY_RDB_DB'

declare GET_TICKET_INFO cursor for

SELECT transaction_date,

           crdt_card_stmnt_dt, 
           transaction_amount,
           transaction_status,
           tax,
           ticket_penalty,
           tt_id,
           trav_doc_type_cd

FROM ttaction
WHERE (ttaction.ticket_id = :my_ticket_id AND

ttaction.transaction_status = :my_trans_status)

PROCEDURE section procedure FETCH_TICKET_INFO SQLCODE :my_TICKET_TRANS RECORD FROM 'CDD_TRAVEL.RECORDS.TICKET_TRANSACTION' END RECORD :my_TICKET_TRANS_IND RECORD FROM 'CDD_TRAVEL.INDICATORS.RECORDS.TICKET_TRANSACTION_IND' END RECORD; FETCH GET_TICKET_INFO INTO :my_tt.transaction_date indicator :my_tt_ind.transaction_date_ind,
:my_tt.crdt_card_stmnt_dt indicator
:my_tt_ind.crdt_card_stmnt_dt_ind,
:my_tt.transaction_amount indicator
:my_tt_ind.transaction_amount_ind,
:my_tt.transaction_status indicator
:my_tt_ind.transaction_status_ind,
:my_tt.tax indicator :my_tt_ind.tax_ind,
:my_tt.ticket_penalty indicator
:my_tt_ind.ticket_penalty_ind,
:my_tt.tt_id indicator :my_tt_ind.tt_id_ind,
:my_tt.trav_doc_type_cd indicator
:my_tt_ind.trav_doc_type_cd_ind;

procedure ROLLBACK_TRANSACTION

SQLCODE;
ROLLBACK; procedure COMMIT

SQLCODE;
COMMIT;

And here's a few sample calls from a BASIC program (remember, this is for rdb on VMS!):

! open cursor
CALL OPEN_GET_TICKET_INFO( SQLCODE.L, TICKET_TRANS_REC::TICKET_ID, &

                                  STAT.CODE.L )

IF SQLCODE.L <> 0 THEN
IF SQLCODE.L = SQLCODE_DEADLOCK OR SQLCODE.L = SQLCODE_LOCK_CONFLICT THEN

            ERROR.TEXT.S = "Record locked by another User."
!            GOSUB GENERAL_ERROR
    ELSE 
            SYS.STATUS.L = RDB$MESSAGE_VECTOR::RDB$LU_STATUS
            CALL SQL$GET_ERROR_TEXT( ERROR.TEXT.S )         
            CALL ROLLBACK_TRANSACTION (SQLCODE.L)
            CAUSE ERROR BAS$K_NOTBASIC

    END IF
   END IF      WHILE SQLCODE.L = 0 ! fetch info
CALL FETCH_TICKET_INFO( SQLCODE.L, TICKET_TRANS_REC, TICKET_TRANS_IND_REC )     SELECT SQLCODE.L
    CASE 0

        ! procede with data collection
        GOSUB FILL_TICKET_DETAIL
        GOSUB PUT_RECORD
    CASE SQLCODE_EOS
        ! fall out of loop
    CASE SQLCODE_DEADLOCK, SQLCODE_LOCK_CONFLICT
        CALL ROLLBACK_TRANSACTION (SQLCODE.L)
        ERROR.TEXT.S = "Record locked by another User."
        VALID.DATA.B = FALSE.B
    CASE ELSE
        SYS.STATUS.L = RDB$MESSAGE_VECTOR::RDB$LU_STATUS
        CALL SQL$GET_ERROR_TEXT( ERROR.TEXT.S )     
        CALL ROLLBACK_TRANSACTION (SQLCODE.L)
        CAUSE ERROR BAS$K_NOTBASIC

END SELECT NEXT ! while sqlcode.l = 0

! close cursor
CALL CLOSE_GET_TICKET_INFO ( SQLCODE.L )

        IF SQLCODE.L <> 0 THEN
                SYS.STATUS.L = RDB$MESSAGE_VECTOR::RDB$LU_STATUS
                CALL SQL$GET_ERROR_TEXT( ERROR.TEXT.S )     
                CALL ROLLBACK_TRANSACTION (SQLCODE.L)
                CAUSE ERROR BAS$K_NOTBASIC
        END IF

----------------------------------------------------------------------------

If there's anybody from Oracle on this list.... :)

Rich Jesse                          System/Database Administrator
Rich.Jesse_at_qtiworld.com             Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Tuesday, September 25, 2001 14:52
To: Multiple recipients of list ORACLE-L

Care to explain further, SQLMOD concept, for those like me who have no idea what this is?

thx

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Sep 25 2001 - 16:18:44 CDT