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.
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.
Using custom SQL queries, you avoid
the need to create additional tables in your database.
ATT Pre-Built Queries Technique
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.
ATT Version 3 Schema
The following table describes the
“TO_SCANNER” and “FROM_SCANNER”
table schemas for ATT version 3. 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. There
is a difference schema for ATT version 2 and ATT version 3. Please ensure you use the
correct schema for your version of ATT.
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 “assetnum” 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.
|
“TO_SCANNER”
Table – ATT Version 2
The following table describes the
“TO_SCANNER” table schema for ATT version 2. 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. There is
a difference schema for ATT version 2 and ATT version 3. Please ensure you use the
correct schema for your version of ATT.
Field Name
|
Length
|
Comments
|
|
inventorynum
|
25
|
The bar code number, if it differs
from the internal item number. ATT
can be configured to use this field as the main item identifier instead
of “assetnum”.
|
|
compcode
|
4
|
Un-modified on the scanner. May be used for any
information.
|
|
assetnum
|
12
|
The internal item number. ATT can be configured to
use this field as the main item identifier instead of
“inventorynum”.
|
|
description
|
50
|
Description of the item
|
|
costcentre
|
10
|
The current cost centre of the
item, according to host.
|
|
location
|
10
|
The current location of the item,
according to host.
|
|
detailedloc
|
40
|
Un-modified on the scanner. May be used for any
information.
|
|
date1
|
8
|
The last tracked date. Format YYYYMMDD.
|
|
time1
|
6
|
The last tracked time. Format hhmmss.
|
|
counted
|
1
|
Must be set to blank.
|
|
multi
|
1
|
Must be set to blank.
|
“FROM_SCANNER”
Table – ATT Version 2
The following table describes the
“FROM_SCANNER” table schema for ATT version 2. 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
|
Comments
|
|
inventorynum
|
25
|
Un-modified.
The value supplied to
scanner.
|
|
compcode
|
4
|
Un-modified.
The value supplied to
scanner.
|
|
assetnum
|
12
|
Un-modified.
The value supplied to
scanner.
|
|
description
|
50
|
Un-modified.
The value supplied to
scanner.
|
|
costcentre
|
10
|
The cost centre entered by the
tracking operator.
|
|
location
|
10
|
The location entered by the
tracking operator.
|
|
detailedloc
|
40
|
Un-modified.
The value supplied to
scanner.
|
|
date1
|
8
|
The tracked date.
Format YYYYMMDD
|
|
time1
|
6
|
The tracked time.
Format hhmmss.
|
|
counted
|
1
|
Equal to ‘1’
for all records.
|
|
multi
|
1
|
Varies, depending on configuration
of ATT software.
|
The diagram below displays the
components involved in the communication between a generic ODBC host
and the ATT software.
|
