IMAGE/SQL Intrinsics

The IMAGE/SQL Intrinsics (aka the API)

For an MPE program to access an IMAGE/SQL database it must call something known as an intrinsic. An instrinsic is really a library of subroutines, but intrinsic is as good a name as API. The IMAGE/SQL intrinsics have names starting with DB, as in DBGET. In most contexts, the intrinsic name is caseless, but in some situations you must specify the actual case, which is upper case. Unlike many of the system intrinsics, none of the parameters to the IMAGE/SQL intrinsics are optional, and all must be passed "by reference," never "by value." This means that you have to declare a variable named mode-1 and initialize it with the value 1, so that you can pass it to IMAGE/SQL.

The parameters are consistent, with the first four being identical for all intrinsics:

    Dbget (Base, Zzzz, Mode, Stat, List, Buf ... )

where

Base is the database name with two blanks at the start, replaced by baseid in DBOPEN
Zzzz can be Set, Password, Text, or Lock Descriptor
Mode is an integer that selects a specific function. Pass a variable containing a 1 if there is only one function.
Stat is a ten-word array that returns status. The first word contains the "condition word" and returns zero if the call worked. A non-zero value indicates an error, most of which are fatal. Check for errors after every call and invoke an error module for unexpected errors. Errors to expect are 17, no entry; 15, end of chain; 14, start of chain; 11, end of file; 10, start of file. The other nine words either describe the error or the action taken (second word = entry word length, 3/4 = entry number, 5/6 = chain length, 7/8 = backward pointer, 9/10 = forward pointer).
List specifies which fields from the dataset are desired. It usually contains a list of field names separated by commas and ending with a semicolon, or one of several shortcuts. List and Buf appear in DBGET, DBUPDATE and DBPUT.
Buf is a data buffer that must match the field list.

Here are the IMAGE functions and their parameters:

Text version.

DBBEGIN (base,text,mode,stat,textlen). Start transaction. Mode- 1 specifies static transaction on 1 base. Mode-3 specifies multibase transaction with one log record per database. Mode-4 is multibase with a single log record. For multibase calls, base is an array of 16-bit values (1-2 must contain 0, 3 is count, 4-n contain baseid values of databases). Positive textlen for words, - for bytes, maximum +256.
DBCLOSE (base,set,mode,stat). Mode-1 closes the database, mode-2 closes the dataset, and mode-3 reinitializes the dataset (rewinds and resets).
DBCONTROL (base,qualifier,mode,stat). Use mode-1 to defer writes to the disc (faster), -2 switches back to complete-output (default); exclusive access is required. Mode-5 enables critical item update (Ciupdate) and -6 disables it. Mode-9 enables HWput (DBPUT tries to put at highwater mark first) and -10 disables it.
DBDELETE (base,set,mode,stat). Delete current record; see DBUPTDATE below for tips. To erase a master dataset, use mode-2 DBGET calls, except after deleting a primary entry with secondaries (DBGET chain length greater than one). In this case, switch to Mode-1 on the next DBGET and re-read current position to avoid missing the migrating secondary.
DBEND (base,text,mode,stat,textlen). End of a transaction. Same parameters as DBBEGIN. Mode-1 ends a static transaction and -2 also flushes to disc. Mode-3 ends multibase transaction with multiple log records; base must be 16-bit array passed to DBBEGIN mode-3. Mode-4 ends multibase transaction with a single log record and flushes to disc.
DBERROR (stat,buf,length). Return an error message for the last error, with a positive byte length, maximum 72.
DBEXPLAIN (stat). Print an explanation of the last error on $STDLIST.
DBFIND (base,set,mode,stat,keyfield,keyvalue). Find the chain head into a detail for a specific search-field value, so you can use DBGET modes-5 or -6 to get entries with the same key value. Returns 17 or 0, but 0 does not mean there are any entries for the key value; check the chain length in stat, which may be 0.
DBGET (base,set,mode,stat,list,buf,argument). Mode-1 is re-read current record, -2 serial (expect error 11), -3 backward (error -10), -4 record number in argument (error 17), -5 down chain (error 15), -6 up chain (error 14), -7 hash on key value in argument (error 17), and -8 contents of primary hash location.
DBINFO (base,zzzz,mode,stat,buf). Return information about a database in buf, based on mode. Negative item/set numbers mean write access. Names occupy 16 bytes. Most fields are 16-bit halfwords. All capacity-type counts are 32-bits.

items 101 item number for item name
102 item table: name, type, sublen, subcnt, 0, 0
103 items in base (count and 1-1023 item nums)
104 fields in set (count and 1-255 item num
sets 201 set number for set name
202 set table: name, type, entrylen, blkfac, 0, 0, entries, capacity
203 sets in base (count and 1-199 setnums)
204 sets with item (count and 1-199 setnums)
205 202 plus highwater mark, max capacity, initial capacity,
incremental entries, incremental percent,
dynamic capacity flag (1=on).
206 number of dataset chunks for jumbo dataset (16b)
207 206 plus entries per chunk (32b)
paths 301 paths for set (count, n * [setnum, keynum, sortnum])
302 primary key into set (keynum, setnum or 0 for master)
log 401 logid name, base flag, user flag, trans flag, trans #.
402 ILR info (IRL flag, date, time, 12 more words).
403 Dynamic Roll-back (same as 401, plus XM set size,
XM set type, attached flag, dynamic flag, XM set name).
404 Multi-base (base flag, user flag, roll-back flag, ILR
flag, mustrecover flag, remote flag, logical trans flag,
logid name, log index, transid, base count, baseids).
sys 501 Subsystem access (0=no access, 1=read, 3=read/write).
502 CIupdate (0 for no, 1 for ok, 2 for on; current setting).
TPI 8nn Third-Party Indexing.
NLS 901 Native Language Support (langid for base).

DBBEGIN	(base,text,mode,stat,textlen). Start transaction. Mode- 1 specifies static transaction on 1 base. Mode-3 specifies multibase transaction with one log record per database. Mode-4 is multibase with a single log record. For multibase calls, base is an array of 16-bit values (1-2 must contain 0, 3 is count, 4-n contain baseid values of databases). Positive textlen for words, - for bytes, maximum +256.
DBCLOSE	(base,set,mode,stat). Mode-1 closes the database, mode-2 closes the dataset, and mode-3 reinitializes the dataset (rewinds and resets).
DBCONTROL	(base,qualifier,mode,stat). Use mode-1 to defer writes to the disc (faster), -2 switches back to complete-output (default); exclusive access is required. Mode-5 enables critical item update (Ciupdate) and -6 disables it. Mode-9 enables HWput (DBPUT tries to put at highwater mark first) and -10 disables it.
DBDELETE	(base,set,mode,stat). Delete current record; see DBUPTDATE below for tips. To erase a master dataset, use mode-2 DBGET calls, except after deleting a primary entry with secondaries (DBGET chain length greater than one). In this case, switch to Mode-1 on the next DBGET and re-read current position to avoid missing the migrating secondary.
DBEND	(base,text,mode,stat,textlen). End of a transaction. Same parameters as DBBEGIN. Mode-1 ends a static transaction and -2 also flushes to disc. Mode-3 ends multibase transaction with multiple log records; base must be 16-bit array passed to DBBEGIN mode-3. Mode-4 ends multibase transaction with a single log record and flushes to disc.
DBERROR	(stat,buf,length). Return an error message for the last error, with a positive byte length, maximum 72.
DBEXPLAIN	(stat). Print an explanation of the last error on $STDLIST.
DBFIND	(base,set,mode,stat,keyfield,keyvalue). Find the chain head into a detail for a specific search-field value, so you can use DBGET modes-5 or -6 to get entries with the same key value. Returns 17 or 0, but 0 does not mean there are any entries for the key value; check the chain length in stat, which may be 0.
DBGET	(base,set,mode,stat,list,buf,argument). Mode-1 is re-read current record, -2 serial (expect error 11), -3 backward (error -10), -4 record number in argument (error 17), -5 down chain (error 15), -6 up chain (error 14), -7 hash on key value in argument (error 17), and -8 contents of primary hash location.
DBINFO	(base,zzzz,mode,stat,buf). Return information about a database in buf, based on mode. Negative item/set numbers mean write access. Names occupy 16 bytes. Most fields are 16-bit halfwords. All capacity-type counts are 32-bits.

items	101	item number for item name
	102	item table: name, type, sublen, subcnt, 0, 0
	103	items in base (count and 1-1023 item nums)
	104	fields in set (count and 1-255 item num
sets	201	set number for set name
	202	set table: name, type, entrylen, blkfac, 0, 0, entries, capacity
	203	sets in base (count and 1-199 setnums)
	204	sets with item (count and 1-199 setnums)
	205 202	plus highwater mark, max capacity, initial capacity, incremental entries, incremental percent, dynamic capacity flag (1=on).
	206	number of dataset chunks for jumbo dataset (16b)
	207 206	plus entries per chunk (32b)
paths	301	paths for set (count, n * [setnum, keynum, sortnum])
	302	primary key into set (keynum, setnum or 0 for master)
log	401	logid name, base flag, user flag, trans flag, trans #.
	402	ILR info (IRL flag, date, time, 12 more words).
	403	Dynamic Roll-back (same as 401, plus XM set size, XM set type, attached flag, dynamic flag, XM set name).
	404	Multi-base (base flag, user flag, roll-back flag, ILR flag, mustrecover flag, remote flag, logical trans flag, logid name, log index, transid, base count, baseids).
sys	501	Subsystem access (0=no access, 1=read, 3=read/write).
	502	CIupdate (0 for no, 1 for ok, 2 for on; current setting).
TPI	8nn	Third-Party Indexing.
NLS	901	Native Language Support (langid for base).

Text version.

DBLOCK (base,zzzz,mode,stat). Mode-1 lock base, -2 lock base if free, -3 lock set, -4 lock set if free, -5 lock entries, -6 lock entries if free (i.e., item-level locking).
DBMEMO (base,text,mode,stat,textlen). Write a memo to the log file. Same parameters as for DBBEGIN.
DBOPEN (base,password,mode,stat). Opens base in the mode specified:

DBLOCK	(base,zzzz,mode,stat). Mode-1 lock base, -2 lock base if free, -3 lock set, -4 lock set if free, -5 lock entries, -6 lock entries if free (i.e., item-level locking).
DBMEMO	(base,text,mode,stat,textlen). Write a memo to the log file. Same parameters as for DBBEGIN.
DBOPEN	(base,password,mode,stat). Opens base in the mode specified:

1. Modify with enforced locking; allow concurrent -1,-5.
2. Update, allow concurrent -2,-6.
3. Modify exclusive.
4. Modify, allow concurrent -6 read.
5. Read, allow concurrent -1,-5.
6. Read, allow concurrent -6 and either -2, one -4, or -8.
7. Read exclusive.
8. Read, allow concurrent read in modes -6,-8.

DBPUT (base,set,mode,stat,list,buf). Add new entry. Missing fields are set to 0. Requires search and sort fields.
DBUNLOCK (base,set,mode,stat). Remove all locks you have made on the database.
DBUPDATE (base,set,mode,stat,list,buf). Update the current record. If CIupdate is On, can modify detail set key and sort items, but not master set keys. Be careful between DBGET and the matching DBUPDATE or you may update the wrong record. If you have unlocked the entry, before you update it you must relock, reget, and compare.
DBXBEGIN (base,text,mode,stat,textlen). Start a dynamic roll-back transaction. Text and textlen are the same as DBBEGIN.
DBXEND (base,text,mode,stat,textlen). End a dynamic transaction. Mode-1 ends transaction, while -2 writes it to disc. Text and textlen are same as in DBBEGIN.
DBXUNDO (base,text,mode,stat,textlen). Roll back the active dynamic transaction. Text and textlen are same as DBBEGIN.

DBPUT	(base,set,mode,stat,list,buf). Add new entry. Missing fields are set to 0. Requires search and sort fields.
DBUNLOCK	(base,set,mode,stat). Remove all locks you have made on the database.
DBUPDATE	(base,set,mode,stat,list,buf). Update the current record. If CIupdate is On, can modify detail set key and sort items, but not master set keys. Be careful between DBGET and the matching DBUPDATE or you may update the wrong record. If you have unlocked the entry, before you update it you must relock, reget, and compare.
DBXBEGIN	(base,text,mode,stat,textlen). Start a dynamic roll-back transaction. Text and textlen are the same as DBBEGIN.
DBXEND	(base,text,mode,stat,textlen). End a dynamic transaction. Mode-1 ends transaction, while -2 writes it to disc. Text and textlen are same as in DBBEGIN.
DBXUNDO	(base,text,mode,stat,textlen). Roll back the active dynamic transaction. Text and textlen are same as DBBEGIN.

Item-Level Locking

Modes-5 and -6 of DBLOCK take a table of lock descriptors as the second parameter. The first word tells how many lock descriptors follow. The first word of each lock descriptor is the length in halfwords.

number of descriptors {one-word, single integer}
Lock Descriptor 1:
1 length {in words, including this word}
2-9 set (x16) {name, #, "", or ";" to ignore}
10-17 item (x16) {name, #, or "" for entire set}
18 relop (x2) {"= " or " =", "<=", or ">="}
19... value {value to compare against}
Lock Descriptor 2 ...

number of descriptors {one-word, single integer}
Lock Descriptor 1:
1	length		{in words, including this word}
2-9	set	(x16)	{name, #, "", or ";" to ignore}
10-17	item	(x16)	{name, #, or "" for entire set}
18	relop	(x2)	{"= " or " =", "<=", or ">="}
19...	value		{value to compare against}
Lock Descriptor 2 ...

Using a lock descriptor, you may lock an entire database (set "= " ""), an entire dataset (item "= " ""), or a group of entries in a dataset (item "= " "xxxxx" or item ">=" "xxxxx").

TPI: Third-Party Indexing

Two vendors, Bradmark and DISC, provide add-on software to increase the flexibility of IMAGE indexing. Although the products, Superdex and Omnidex, have different capabilities, they both provide indexed-sequential access (for example, give me the customer names in sorted order) and keyword indexing (for example, show me all the parts with the string "chainsaw" in the description). In order to increase the power of IMAGE/SQL, HP decided to make it possible to write software tools and applications that could use either indexing add-on.

This enhancement to IMAGE/SQL is called Third-Party Indexing, or TPI for short. Designed in conjuction with Bradmark and DISC, TPI provides a number of new modes for the standard IMAGE intrinsics so that, if TPI is enabled for the database, the calls are passed to the third-party software and used by it. Thus, TPI defines a common API extension that both vendors will adhere to for the subset of their products that is similar, and provides for guidelines for accessing the custom features of each vendor. The DBINFO intrinsic has been extended with new modes which allow you to find out whether TPI exists and is enabled, which type is enabled, what version, etc. TPI allows other software, such as our Suprtool product, to take advantage of TPI in a vendor-independent manner, if it is present on the machine.

The following information is extracted from a document included as "Appendix A" in the Superdex User Manual. The document is titled "TurboIMAGE/XL Interface to Third Party Indexing Products SPECIFICATIONS." For all intrinsics not documented below the following applies: the range of modes 1000 - 1999 (inclusive) is for TPI functions beyond the standardized functions.

For all IMAGE instrinsics the following applies: Error codes with values in the ranges 3000 - 3999 (inclusive) are reserved for reporting conditions beyond the standardized conditions.

Text version.

DBCONTROL
mode-800 = DBFIND mode-12 returns the prior list if the refinement results in zero entries qualified.
mode-801 = DBFIND mode-12 with no entries if the refinement results in zero entries qualified.
mode-802 = exposes the third-party index data sets within the database.
mode-803 = hides the third-party index data sets within the database.

DBFIND

mode-10 = performs a standard DBFIND on a detail search item passed through the item parameter -- same as mode-1
mode-11 = performs a range retrieval for numeric sorted keys of types E, I, J, K, P, R and Z. Argument contains the start and stop values for the range which must be passed as the same data- type and length as the key.
mode-12 = performs a keyword search and returns a chain count.
mode-13 = restores back to the most recently qualified list for single or multiple keyword/key retrievals.
mode-21 = same as mode-1 but does not return a chain count
mode-22 = same as mode-11 but no chain count
mode-23 = same as mode-12 but no chain count

DBCONTROL
mode-800	=	DBFIND mode-12 returns the prior list if the refinement results in zero entries qualified.
mode-801	=	DBFIND mode-12 with no entries if the refinement results in zero entries qualified.
mode-802	=	exposes the third-party index data sets within the database.
mode-803	=	hides the third-party index data sets within the database.
DBFIND
mode-10	=	performs a standard DBFIND on a detail search item passed through the item parameter -- same as mode-1
mode-11	=	performs a range retrieval for numeric sorted keys of types E, I, J, K, P, R and Z. Argument contains the start and stop values for the range which must be passed as the same data- type and length as the key.
mode-12	=	performs a keyword search and returns a chain count.
mode-13	=	restores back to the most recently qualified list for single or multiple keyword/key retrievals.
mode-21	=	same as mode-1 but does not return a chain count
mode-22	=	same as mode-11 but no chain count
mode-23	=	same as mode-12 but no chain count

The following modes represent programmatic methods of enforcing the relational operators:

mode-1nn = performs an equal-to (=) operation on the argument - by words if positive, bytes if negative.
mode-2nn = performs a greater than (>) operation on the argument
mode-3nn = performs an equal to or greater than (>=) operation on the argument
mode-4nn = performs a less than (<) operation on the argument
mode-5nn = performs a less than or equal to (<=) operation on the argument

mode-1nnn = allows for third party extensions beyond the standardized functions

DBGET
mode-5 = if following a generic key search it reads the next record in the chain using the third party indexing product.
mode-6 = if following a generic key search it reads the previous record in the chain using the third party indexing product.
mode-11 = resets the pointer to the beginning of the list of qualified records for generic key search.
mode-12 = moves pointer forward "n" entries in the list of qualified records for a generic key search.
mode-13 = moves pointer backward "n" entries in the list of qualified records for a generic key search.
mode-15 = same as mode-5 but not inhibited by an end-of-chain.
mode-16 = same as mode-6 but not inhibited by a beginning-of-chain.
mode-21 = resets pointer to the beginning of the list of qualified entries for a keyword retrieval.
mode-22 = moves pointer forward "n" entries in the list of qualified records for a keyword search.
mode-23 = moves pointer backward "n" entries in the list of qualified records for a keyword search.
mode-25 = retrieves next record buffer from those qualified by keyword retrieval.
mode-26 = retrieves previous record buffer from those qualified by keyword retrieval.
mode-1nnn = the range of modes 1000 - 1999 (inclusive) is reserved for TPI functions beyond the standardized functions.
DBOPEN
mode +100 = adding 100 to existing modes opens the database but disables TPI - affects DBFIND and DBGET only.
mode 1nnn = the range of modes 1000 - 1799 (inclusive) is reserved for TPI functions beyond the standardized functions
DBINFO
mode-801 = returns information on the TPI package.
mode-802 = number of external and internal TPI index files
mode-803 = indicates whether TPI is enabled.
mode-811 = describes access available for the data item or TPI key.
mode-812 = describes the TPI key.
mode-813 = identifies all data items including all TPI keys and type of access allowed.
mode-814 = identifies all data items available in a specific set.
mode-821 = identifies all data sets available which contain the specified TPI key.
mode-831 = identifies TPI generic search keys for specified data set.
mode-832 = identifies third-party single or keyword search keys for specified data set.
mode-833 = describes a specific TPI key.
mode-834 = describes all items grouped with the specified key.

	1. Modify with enforced locking; allow concurrent -1,-5.
	2. Update, allow concurrent -2,-6.
	3. Modify exclusive.
	4. Modify, allow concurrent -6 read.
	5. Read, allow concurrent -1,-5.
	6. Read, allow concurrent -6 and either -2, one -4, or -8.
	7. Read exclusive.
	8. Read, allow concurrent read in modes -6,-8.

mode-1nn	=	performs an equal-to (=) operation on the argument - by words if positive, bytes if negative.
mode-2nn	=	performs a greater than (>) operation on the argument
mode-3nn	=	performs an equal to or greater than (>=) operation on the argument
mode-4nn	=	performs a less than (<) operation on the argument
mode-5nn	=	performs a less than or equal to (<=) operation on the argument
mode-1nnn	=	allows for third party extensions beyond the standardized functions

DBGET
mode-5	=	if following a generic key search it reads the next record in the chain using the third party indexing product.
mode-6	=	if following a generic key search it reads the previous record in the chain using the third party indexing product.
mode-11	=	resets the pointer to the beginning of the list of qualified records for generic key search.
mode-12	=	moves pointer forward "n" entries in the list of qualified records for a generic key search.
mode-13	=	moves pointer backward "n" entries in the list of qualified records for a generic key search.
mode-15	=	same as mode-5 but not inhibited by an end-of-chain.
mode-16	=	same as mode-6 but not inhibited by a beginning-of-chain.
mode-21	=	resets pointer to the beginning of the list of qualified entries for a keyword retrieval.
mode-22	=	moves pointer forward "n" entries in the list of qualified records for a keyword search.
mode-23	=	moves pointer backward "n" entries in the list of qualified records for a keyword search.
mode-25	=	retrieves next record buffer from those qualified by keyword retrieval.
mode-26	=	retrieves previous record buffer from those qualified by keyword retrieval.
mode-1nnn	=	the range of modes 1000 - 1999 (inclusive) is reserved for TPI functions beyond the standardized functions.
DBOPEN
mode +100	=	adding 100 to existing modes opens the database but disables TPI - affects DBFIND and DBGET only.
mode 1nnn	=	the range of modes 1000 - 1799 (inclusive) is reserved for TPI functions beyond the standardized functions
DBINFO
mode-801	=	returns information on the TPI package.
mode-802	=	number of external and internal TPI index files
mode-803	=	indicates whether TPI is enabled.
mode-811	=	describes access available for the data item or TPI key.
mode-812	=	describes the TPI key.
mode-813	=	identifies all data items including all TPI keys and type of access allowed.
mode-814	=	identifies all data items available in a specific set.
mode-821	=	identifies all data sets available which contain the specified TPI key.
mode-831	=	identifies TPI generic search keys for specified data set.
mode-832	=	identifies third-party single or keyword search keys for specified data set.
mode-833	=	describes a specific TPI key.
mode-834	=	describes all items grouped with the specified key.