SQL to DBQ Mapping Examples

[Andre]

The following examples show how the DBMS data source driver processes SQL statements into DBQ commands. Users familiar with DBQ may find these examples helpful in understanding how the data source driver works and how to best utilize it. Each example shows SQL text and the DBQ commands used by the data source driver to implement the request. (Tracing writes the actual DBQ commands to the server log file for the SQL.)

Example 1: Selecting from a Table Without Key Criteria
Selecting from a table without any key criteria (set) causes Attunity Connect to read through the records in DBMS chain (sequential or sorted) order.

(SQL) SELECT PART.PART_ID, PART.PART_DESC
FROM PART


(DBQ) FIND FIRST PART
GET PART_ID PART_DESC
FREE ALL CURRENT

Example 2: Utilizing Key Columns Specified in WHERE Statement
If any key columns are specified in the WHERE statement, Attunity Connect attempts to utilize the key (set).

(SQL) SELECT PART.PART_ID, PART.PART_DESC
FROM PART
WHERE (PART.PART_ID=’BR890123’)


(DBQ) FIND FIRST PART WITHIN ALL_PARTS
WHERE PART_ID EQ "BR890123"
GET PART_ID PART_DESC
FREE ALL CURRENT

Example 3: Referencing a DBMS Set With ’_S_<SetName>’ Virtual Column Name
To reference a DBMS set that has the “_S_<SetName>” virtual column name, you must set the column equal to ‘1’. This results in the Query Processor passing the column name to the data source driver. The data source driver then utilizes the set.

(SQL) SELECT PART.PART_ID, PART.PART_DESC
FROM PART PART
WHERE (PART."_S_ALL_PARTS_ACTIVE"=’1’)


(DBQ) FIND FIRST PART WITHIN ALL_PARTS_ACTIVE
GET PART_ID PART_DESC
FREE ALL CURRENT


Example 4: Joining an Owner Record to a Member Record
To select and join an owner record to a member record, set the _M_<SetName> virtual column in the member record equal to the virtual anchor __<RecordName> of the owner record. (Note the double underscore __ in the virtual anchor name.)

(SQL) SELECT PART.PART_ID, COMPONENT.COMP_SUB_PART
FROM COMPONENT COMPONENT, PART PART
WHERE (COMPONENT."_M_PART_USES" =
PART."__PART")


(DBQ) FIND FIRST PART
GET PART_ID
FREE ALL CURRENT
FIND DBKEY
FIND FIRST COMPONENT WITHIN PART_USES
GET COMP_SUB_PART
FREE ALL CURRENT

To select and join a member record to an owner record, set the _O_<SetName> virtual column in the owner record equal to the virtual anchor __<RecordName> of the member record.

(SQL) SELECT COMPONENT.COMP_SUB_PART, PART.PART_ID
FROM COMPONENT COMPONENT, PART PART
WHERE (PART."_O_PART_USED_ON" =
COMPONENT."__COMPONENT")


(DBQ) FIND FIRST COMPONENT
GET COMP_SUB_PART
FREE ALL CURRENT
FIND DBKEY
FIND OWNER WITHIN PART_USED_ON
GET PART_ID
FREE ALL CURRENT

Example 5: Adding a Record
To add a record (simple):

(SQL) INSERT INTO CLASS
(CLASS_CODE, CLASS_DESC,
CLASS_STATUS,"__CLASS")
VALUES (’OL’, ’OL DESC’, ’N’, NULL)


(DBQ) STORE CLASS
FREE ALL CURRENT
COMMIT


To add a new record, all of the automatic insertion _M_<SetName> member virtual columns must be set to a valid DBKEY. The DBKEY can be that of a desired owner record or the DBKEY of an existing record in the table which has the owner that is needed, in the format Area:Page:Line. To add a new record with automatic insertion using an owner record and a system chain set:

(SQL) INSERT INTO PART
(PART_ID, PART_DESC, PART_STATUS,
PART_PRICE, PART_COST, PART_SUPPORT,
"_S_ALL_PARTS_ACTIVE", "_M_CLASS_PART",
"_M_RESPONSIBLE_FOR", "__PART")
VALUES (’AA0001’,’DESC’, ’G’, 1.5,
0.5, ’Y’, ’1’, ’2:4:1’, NULL, NULL)


(DBQ) FIND DBKEY RETAINING ALL EXCEPT CLASS_PART
STORE PART
FREE ALL CURRENT
COMMIT

Example 6: Deleting a Record
To delete a record:

(SQL) DELETE FROM CLASS WHERE (CLASS_CODE = ’OL’)


(DBQ) FIND FIRST CLASS WITHIN ALL_CLASS
WHERE CLASS_CODE EQ "OL"
GET CLASS_CODE CLASS_DESC CLASS_STATUS
FREE ALL CURRENT
FIND DBKEY RETAINING ALL EXCEPT ALL_CLASS
FIND NEXT CLASS WITHIN ALL_CLASS
WHERE CLASS_CODE EQ "OL"
FREE ALL CURRENT
FIND DBKEY
ERASE
FREE ALL CURRENT
COMMIT

To delete a record that has a mandatory member with records, the member records must be removed first. As shown in the following example, attempting to delete such a record fails, and the transaction is rolled back.

(SQL) DELETE FROM CLASS WHERE (CLASS_CODE = ’PC’)


This statement results in the following error:
Modify Rows failed: Table name = CLASS.


(DBQ) FIND FIRST CLASS WITHIN ALL_CLASS
WHERE CLASS_CODE EQ "PC"
GET CLASS_CODE CLASS_DESC CLASS_STATUS
FREE ALL CURRENT
FIND DBKEY RETAINING ALL EXCEPT ALL_CLASS
FIND NEXT CLASS WITHIN ALL_CLASS
WHERE CLASS_CODE EQ "PC"
FREE ALL CURRENT
FIND DBKEY
ERASE
DB_FS_INTERFACE(35); Error:
DB_DBMS_INTERFACE(2), %DBM-F-ERASEMANDT, MANDATORY
member can be erased only with
ERASE ALL; EXECUTE DB_DBMS_INTERFACE(2),
ERASE(DELETE)
FREE ALL CURRENT
ROLLBACK

Example 7: Utilizing a Connect on the PART Record
The following example shows a connect on the PART record:

(SQL) UPDATE PART SET "_M_RESPONSIBLE_FOR" =’4:8:1’
WHERE ("_M_RESPONSIBLE_FOR" IS NULL AND
PART_ID = ’AZ000003’)


(DBQ) FIND FIRST PART WITHIN ALL_PARTS
WHERE PART_ID EQ "AZ000003"
GET PART_ID PART_DESC PART_STATUS PART_PRICE
PART_COST
PART_SUPPORT
FIND CURRENT WITHIN ALL_PARTS_ACTIVE
RETAINING ALL
FIND OWNER WITHIN CLASS_PART RETAINING ALL
FREE ALL CURRENT
FIND DBKEY RETAINING ALL EXCEPT ALL_PARTS
FIND NEXT PART WITHIN ALL_PARTS
WHERE PART_ID EQ "AZ000003"
FREE ALL CURRENT
FIND DBKEY 1:57:4
FIND DBKEY RETAINING ALL EXCEPT
RESPONSIBLE_FOR 4:8:1
FETCH DBKEY RETAINING ALL 1:57:4
CONNECT PART TO RESPONSIBLE_FOR
FREE ALL CURRENT
COMMIT

Note
If you place any member virtual columns _M_<SetName> into a SELECT, the data source driver reads the member records to get a DBKEY value for the member virtual column. This occurs regardless of whether the SELECT includes a specific value or a wildcard for the member virtual column. You should do this only if you either need information from the member record or will join to the member record.

Also see the post on virtual columns.

http://www.attunity.com/forums/odbc-...s.html#post329