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
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
