Overview
The ODBC Interface provides a mechanism for the ATT software to communicate with a management system that supports an ODBC interface mechanism. It is suggested that two additional tables are created within the desired database. Information is then populated into a table for sending to the scanner. The scanner will populate a second table with the results of the scanning as illustrated in the diagram at the bottom of this page.
The ODBC interface is most commonly used to interface the ATT software with Microsoft SQL Server, Microsoft Access or a third party management software package that supports ODBC connections. There are two techniques for interfacing using ODBC enabling you to either define your own SQL or use the pre-built ATT SQL.
ODBC Data Source Configuration
On the machine that will run the ATT Translation and Interface software, the ODBC data source must be configured using the control panel called “ODBC Data Sources”. The default data source name to use is “ATT Data Source”.
Custom SQL Technique
The ODBC interface supports custom SQL queries should you wish to perform more complex interfacing with existing tables. This provides a very flexible and advanced technique for interfacing. This is now the preferred technique.
Using custom SQL queries, you avoid the need to create additional tables in your database.
ATT Pre-Built Queries Technique
If you wish, the ATT system has pre-built queries and table structures to use if you find this approach easier.
The customer’s management system must first populate a table in their database, known as “TO_SCANNER”. The ATT Translation and Interface software is then executed. The operator will run a data down load. This may also be referred to as a “get items” operation. This program will connect to the ODBC data source and copy the whole contents of the “TO_SCANNER” table onto the local machine. It will then translate the contents into a data format suitable for the scanner.
The ATT Translation and Interface software will take data from the device and translate it into a format suitable for the host system. It will then open an ODBC connection to the data source, and deliver the count information to the table “FROM_SCANNER” inside the desired database. Note that the previous contents of the “FROM_SCANNER” table will be deleted as this transfer takes place. The customer may then perform whatever reconciliation reports or data transfers are desired to populate the results of this count back into their management system. It is the customer’s responsibility to write whatever queries or reports are required in performing this step.
Schema
The following table describes the “TO_SCANNER” and “FROM_SCANNER” table schemas for ATT. Note that all fields are character fields, and have fixed length. It is recommended that the field names supplied here be used, however other field names should function correctly.
| Field Name | Length | Fld # | Comments |
| key | 30 | 0 | The primary key. This must have the exact contents of the item bar code. Typically this is copied from the “assetnum” field or the “inventorynum” field. |
| laststocktake | 8 | 1 | The counted date in “YYYYMMDD” format. The contents of this field are overwritten with the current date when scanning an item. |
| time | 6 | 2 | The counted time in “hhmmss” format. The contents of this field are overwritten with the current time when scanning an item. |
| counted | 1 | 3 | Flag to indicate if the item was counted. |
| multi | 1 | 4 | Flag to indicate multiple records. Used internally by ATT. |
| inventorynum | 25 | 5 | The inventory number or tag number of the item. Often used as the bar code of the item. Copy this value to the “key” field to use it as the bar code with ATT. |
| assetnum | 12 | 6 | The asset number of the item. Sometimes used as the bar code of the item. Copy this value to the “key” field to use it as the bar code with ATT. |
| compcode | 4 | 7 | Company code |
| description | 50 | 8 | Description |
| costcentre | 10 | 9 | Cost Centre |
| respcostcentre | 10 | 10 | Responsible Cost Centre |
| location | 10 | 11 | Location |
| detailedloc | 40 | 12 | Detailed Location |
| serialno | 18 | 13 | Serial Number |
| room | 8 | 14 | Room |
| plant | 4 | 15 | Plant |
| class | 8 | 16 | Class or type |
| inventorynote | 15 | 17 | Inventory note |
| validitydte | 8 | 18 | Validity date |
| sublocation | 4 | 19 | Sub location |
| controllertext | 30 | 20 | Controller text |
| assetsubnum | 4 | 21 | Asset or item sub-number |
| equipmentnum | 18 | 22 | Equipment number |
| equipcategory | 8 | 23 | Equipment category |
| functionalloc | 30 | 24 | Functional location |
| workcentre | 10 | 25 | Work centre |
| status | 4 | 26 | Status |
| maintext | 50 | 27 | Main text |
| internalorder | 12 | 28 | Internal order |
| userid | 20 | 29 | User ID. If blank, this is automatically populated with the identity of the scanner that performed the scanning. The scanner ID is the PalmOS “user name”. |
| controlid | 5 | 30 | Controller ID |
| spare2 | 5 | 31 | Spare field |
| caretaker | 10 | 32 | Caretaker |
| spare4 | 10 | 33 | Spare field |
| spare5 | 10 | 34 | Spare field |
| licenseplt | 20 | 35 | Licence Plate. This is sometimes used as a serial number if the “serialno” field is not long enough. |
| spare7 | 20 | 36 | Spare field |
| spare8 | 20 | 37 | Spare field |
| subloctext | 30 | 38 | Sub location text |
| spare10 | 49 | 39 | Spare field |
| scanstatus | 1 | 40 | Scanned verses keyed input status. Non blank for scanned input. |
Diagram
The diagram below displays the components involved in the communication between a generic ODBC host and the ATT software.
