Why do a physical inventory? A physical inventory count is necessary to reconcile the quantity on hand stored in a database versus what is really on the shelf. In a retail environment, this process is typically done once a year. In a perfect world, these values would always be in sync.

The value would be decremented when items are sold and incremented when items are received. Unfortunately, this is not always the case. Consider shrinkage, shoplifting, and, my personal favorite, software bugs.

Through a case study, this article will show how easy it is to architect a wireless real-time PocketBuilder application to replace a hardware-based data collection device used for physical inventory updates to an enterprise database. Specifically, a Symbol PPT8800 Pocket PC will be compared and contrasted to an American Micro M5000 Laser barcode scanner. We will show how the Pocket PC can be programmed to do real-time inventory updates as opposed to the batch upload necessary for the hardware-based device.

After reading this article you will be able to:

• Access an ASA database wirelessly from a Pocket PC
• Use PocketBuilder to develop a real-time physical inventory application
• Extend the enterprise by developing other great mobile applications

What you will need:

• A Symbol Pocket PC with 802.11b capabilities; the PPT8800 is used in this example.
• A wireless router or access point; the Linksys WRT54G is used in this example.
• A version of ASA that allows you to connect directly to a specified I.P. address and port; version 9.01 build 1873 is used in this example.
• A database that has a table with a SKU and an on-hand quantity to update.
• PocketBuilder version 1.5x or higher.
• Some barcodes to scan.

The Batch Upload Method
The M5000 is a device for doing physical inventory (see Figure 1). It scans bar codes writing to an ASCII text file that is uploaded to the user's PC via a serial connection. That file must then be parsed and used to update your inventory table. A look-ahead algorithm is used. The layout of an entry can be either a SKU or a SKU followed by a quantity. Its format is shown below (parsing is messy!).

9780131103627
9781565923249
5

There are several disadvantages to using this device and method:

• There is latency from the time the information is captured to the time the database is updated. Changes may have occurred before the update is applied. This may result in an incorrect inventory count.
• The M5000 is a dumb device. It's only used to capture bar codes and quantities to an ASCII text file. Additional programming is required to update the target database. Moreover, since there is no way to connect to the user's database, real-time updates cannot be done.
• It cannot be used to write other data-driven mobile applications.

The Real-Time Method
Contrasted to the M5000, the Symbol PPT8800 is a multi-functional device (see Figure 2). It can be used for several purposes, one of which is performing a physical inventory. There are several clear advantages to using this device and method:

• Since it is possible to have a real-time data connection to our database, there is no latency between the time data is captured and the time the database is updated.
• Contrasted to the M5000, the PDA is a multi-functional device. We are only limited by our imagination with respect to the number and type of data-driven applications we can write.

That being said, we will go step by step to show how we can easily architect our own physical inventory application using PocketBuilder.

Step 1: Connect your Pocket PC to an existing wireless network
On the Symbol PPT8800, this can be done by selecting the icon in the lower right-hand corner of the home screen. Choose Find WLANs. A list will appear with all the available selections. Highlight your wireless network and tap connect. Two horizontal arrows, each facing the opposite direction, should appear indicating that you are now connected.

Step 2: Start a network server on the computer where the database resides (see Figure 3)

c:\blwin32\database>dbsrv9 -n blwserver blwmed.db

Step 3: Find out the IP address of the database server

C:\blwin32\Database>ipconfig
Windows IP Configuration
Ethernet adapter Local Area
  Connection 2:
	    Connection-specific DNS
      Suffix  . :
        IP Address. . . . . . . . . . . . : 192.168.120.111
        Subnet Mask . . . . . . . . . . . : 255.255.255.0
        Default Gateway . . . . . . . . . : 192.168.120.1

Step 4: Create a file DSN
Windows CE does not provide an ODBC Administrator. Rather, ASA uses ODBC data sources stored in ANSI format files. The file has the same name as the data source, with the extension .dsn, and is usually stored at the root level of the device. You can create the file on your PC and then use Active-Sync to transfer it to your device.

An Example File DSN

[ODBC]
uid=dba
pwd=sql
enginename=blwserver
CommLinks=TCPIP(dobroadcast=DIRECT;
HOST=192.168.120.111;port=2638)
databasename=blwmed
driver=dbodbc9.dll