AMX I!-DATABASEPLUS AND DBWIZARD User manual

instruction manual
integration!Solutions
i!-DatabasePlus and
DBWizard

Software Warranty Agreement
GRANT OF LICENSE. AMX Corporation grants to you the right to use the enclosed i!-DatabasePlus and DBWizard
software program (the SOFTWARE) on a single central processing unit (CPU). This license is for object code only and
does not convey any rights to use of the source code.
This Agreement does not authorize you to distribute the SOFTWARE.
COPYRIGHT. The SOFTWARE is owned by AMX Corporation, and is protected by United States copyright laws and
international treaty provisions. Therefore, you must treat the SOFTWARE like any other copyrighted material (e.g., a
book or musical recording) except that you may either (a) make one copy of the SOFTWARE solely for backup or archi-
val purposes, or (b) transfer the SOFTWARE to a single hard disk provided you keep the original solely for backup or
archival purposes. You may not copy the written materials accompanying the SOFTWARE.
OTHER RESTRICTIONS. You may not rent or lease the SOFTWARE, but you may transfer the SOFTWARE and
accompanying written materials on a permanent basis provided you retain no copies and the recipient agrees to the
terms of this Agreement. You may not reverse engineer, decompile, or disassemble the SOFTWARE. If the SOFT-
WARE is an update or has been updated, any transfer must include the most recent update and all prior versions.
You may use only one version of the SOFTWARE at any time. You may not use the version of the SOFTWARE not
being run on your CPU on any other CPU or loan, rent, lease or transfer them to another user whether with or without
consideration.
LIMITED WARRANTY
LIMITED WARRANTY. AMX Corporation warrants that the SOFTWARE will perform substantially in accordance with
the accompanying written materials for a period of ninety (90) days from the date of receipt. Any implied warranties on
the SOFTWARE and hardware are limited to ninety (90) days and one (1) year, respectively. Some states/countries do
not allow limitations on duration of an implied warranty, so the above limitation may not apply to you.
CUSTOMER REMEDIES. AMX Corporation’s entire liability and your exclusive remedy shall be, at AMX Corporation's
option, either (a) return of the price paid, or (b) repair or replacement of the SOFTWARE that does not meet AMX Cor-
poration's Limited Warranty and which is returned to AMX Corporation. This Limited Warranty is void if failure of the
SOFTWARE or hardware has resulted from accident, abuse, or misapplication. Any replacement SOFTWARE will be
warranted for the remainder of the original warranty period or thirty (30) days, whichever is longer.
NO OTHER WARRANTIES. AMX Corporation disclaims all other warranties, either expressed or implied, including, but
not limited to implied warranties of merchantability and fitness for a particular purpose, with regard to the SOFTWARE,
the accompanying written materials, and any accompanying hardware. This limited warranty gives you specific legal
rights. You may have others which vary from state/country to state/country.
NO LIABILITY FOR CONSEQUENTIAL DAMAGES. In no event shall AMX Corporation be liable for any damages
whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business
information, or any other pecuniary loss) arising out of the use of or inability to use this AMX Corporation product, even
if AMX Corporation has been advised of the possibility of such damages. Because some states/countries do not allow
the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you.
U.S. GOVERNMENT RESTRICTED RIGHTS
The SOFTWARE and documentation are provided with RESTRICTED RIGHTS. Use, duplication, or disclosure by the
Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of The Rights in Technical Data and Com-
puter Software clause at DFARS 252.227-7013 or subparagraphs (c)(1) and (2) of the Commercial Computer Soft-
ware--Restricted Rights at 48 CFR 52.227-19, as applicable. Manufacturer is AMX Corporation, 3000 Research Drive,
Richardson, TX 75082.
If you acquired this product in the United States, this Agreement is governed by the laws of the State of Texas.
Should you have any questions concerning this Agreement, or if you desire to contact AMX for any reason, please
write: AMX Corporation, 3000 Research Drive, Richardson, TX 75082.

Table of Contents
i
i!-Database Plus and DBWizard
Table of Contents
Introduction ...............................................................................................................1
i!-DatabasePlus ................................................................................................................. 1
DBWizard .......................................................................................................................... 1
Supported Operating Systems .......................................................................................... 1
Minimum PC Requirements .............................................................................................. 2
Installing i!-Database Plus................................................................................................. 2
Installing DBWizard ........................................................................................................... 2
Setup ................................................................................................................................. 3
Programming i!-Database Plus ...............................................................................5
Data Source Name............................................................................................................ 5
Structure Query Language (SQL) ..................................................................................... 5
HTTP, CGI and XML ......................................................................................................... 6
HTTP (Hyper-Text Transfer Protocol) ...................................................................................... 6
CGI (Common Gateway Interface)........................................................................................... 6
XML (Extensible Markup Language)........................................................................................ 7
Creating an ASP file .......................................................................................................... 9
Creating an AXI file ........................................................................................................... 9
Creating an AXS file ........................................................................................................ 15
NetlinxDBInclude.asp ...................................................................................................... 18
Functions................................................................................................................................ 18
NetlinxDBInclude.axi ....................................................................................................... 20
Constants ............................................................................................................................... 20
Variables ................................................................................................................................ 20
Structures .............................................................................................................................. 21
Functions................................................................................................................................ 22
Putting It All Together...................................................................................................... 28
Running DBWizard .................................................................................................29
File Menu......................................................................................................................... 29
Database Tab.................................................................................................................. 29
Queries Tab..................................................................................................................... 30
Sorting ............................................................................................................................. 30
File Tab ........................................................................................................................... 31
ASP file .................................................................................................................................. 31
Webserver host name or IP address...................................................................................... 31
Webserver IP port .................................................................................................................. 31

ii i!-Database Plus and DBWizard
Table of Contents
Webserver path...................................................................................................................... 32
Absolute database path ......................................................................................................... 32
NetLinx AXI file....................................................................................................................... 32
NetLinx local IP port ............................................................................................................... 32
NetLinx code prefix ................................................................................................................ 32
Encapsulate ........................................................................................................................... 32
Writing Your AXS File ..................................................................................................... 33

Introduction
1
i!-Database Plus and DBWizard
Introduction
i!-DatabasePlus
i!-Database Plus™is an application that allows you to connect a NetLinx™Master to a server or
PC database. The kit consists of two files:
 NetLinxDBInclude.asp: A server database script designed to run on Microsoft web
servers.
 NetLinxDBInclude.axi: Includes various functions to help you integrate to a server
database script.
The two files work together to allow NetLinx to access any ODBC database that the server can
connect to.
These two files connect to each other using all of the following:
 Hypertext Transfer Protocol (HTTP): the base protocol to which CGI and XML are then
added.
 Common Gateway Interface (CGI): used by the NetlinxDBInclude.axi file to make
database requests to the NetlinxDBInclude.asp file.
 Extensible Markup Language (XML)
DBWizard
DBWizard™is an application that helps you generate NetLinx code to access a PC database. The
NetLinx Master can read and write to the database via a web server connection. DBWizard helps
generate the SQL and NetLinx code needed to read and write to the database.
To access a database using NetLinx, you will need the following:
 A PC running a Microsoft Window©operating system such as Windows 95®,
Window 98®, Windows NT 4.0®Workstation, Windows NT Server®, Windows 2000®
Workstation or Windows 2000®Server.
 A Web server. You can use Microsoft Internet Information Server (IIS) or Microsoft
Personal Web Server (PWS); either is available for all operating systems listed above.
 A NetLinx Master with Ethernet
Supported Operating Systems
 Windows 95/98 (with at least 48 MB of installed memory)
 Windows NT 4.0 Workstation or Server (service pack 6 B or greater, with at least 64 MB
of installed memory)
 Windows 2000 Professional or Server (running on a Pentium 233 MHz processor
(minimum requirement); 300 MHz or faster recommended, with 96 MB of installed
memory.)

Introduction
2i!-Database Plus and DBWizard
Minimum PC Requirements
 Windows-compatible mouse (or other pointing device)
 At least 5 MB of free disk space (150 MB recommended)
 VGA monitor, with a minimum screen resolution of 800 x 600
 A Network adapter
 A Web server such as Personal Web Server (PWS) or Internet Information Server (IIS).
 Windows 95®/98®, and NT 4.0®Workstation uses PWS.
 Windows 2000®Professional or Server, and Windows NT 4.0®Server uses IIS.
Installing i!-Database Plus
1. In Explorer, double-click i!-DatabasePlusSetup.exe from the directory window where you
downloaded the i!-Database Plus install program.
2. After reading the License Agreement, select I Agree and Next to proceed.
3. The Welcome To i!-Database Plus Setup dialog appears, reminding you to close all Windows
programs before going any further. Click Next to proceed.
4. In the i!-DatabasePlus Select Components dialog, select which example programs you would
like to install.
5. In the Select i!-Database Plus Install Location dialog, use the Browse button to navigate to a
directory other than the default install directory, if desired. Click Next.
6. In the i!-Database Plus Shortcut Creation dialog, select Install Shortcut Icons for the installed
components on your desktop, if desired.
7. Click Next in the Start i!-Database Plus Installation dialog to install the selected components.
8. The program prompts you to restart your system to complete the installation.
Installing DBWizard
1. In Explorer, double-click DBWizardSetup.exe from the directory window where you
downloaded the DBWizard install program.
2. After reading the License Agreement, select I Agree and Next to proceed.
3. The Welcome To DBWizard Setup dialog appears, reminding you to close all Windows
programs before going any further. Click Next to proceed.
4. In the Select DBWizard Install Location dialog, use the Browse button to navigate to a
directory other than the default install directory, if desired. Click Next.
5. In the DBWizard Shortcut Creation dialog, select Install Shortcut Icons for the installed
components on your desktop, if desired.
6. Click Next in the Start DBWizard Installation dialog to install the selected components.
7. The program prompts you to restart your system to complete the installation.

Introduction
3
i!-Database Plus and DBWizard
Setup
NetLinxDBInclude.asp and NetLinxDBInclude.axi work in conjunction with your code to allow
access to a PC database. In order to use these two files, you will need the following:
 A PC running a Microsoft Windows©operating system such as Windows 95, Window 98,
Windows NT Workstation, Windows NT Server, Windows 2000 Workstation or
Windows 2000 Server.
 A Web server. You can use Microsoft Internet Information Server (IIS) or Microsoft
Personal Web Server (PWS). Either is available for all operating systems listed above.
 A NetLinx Master with Ethernet
You must have IIS or PWS installed and running ASP (Active Server Pages) before beginning.
Make sure there were no errors during the installation of the web server, and you can call up an
ASP file (any file ending in .ASP). If you get a 500 Internal Server Error message when trying to
view ASP pages in a web browser, the web server is not installed properly. You must correct this
problem before proceeding.
Once the server PC is setup, make sure both the server PC and the NetLinx Master are connected to
a network and can communicate with each other. A simple way to test connectivity is by using the
PING program from the server PC's command line. Type Ping <ip address of the NetLinx
Master> and you should see something like the following:
C:\WINDOWS\Desktop>ping 192.168.13.33
Pinging 192.168.13.33 with 32 bytes of data:
Reply from 192.168.13.33: bytes=32 time<10ms TTL=62
Reply from 192.168.13.33: bytes=32 time<10ms TTL=62
Reply from 192.168.13.33: bytes=32 time<10ms TTL=62
Reply from 192.168.13.33: bytes=32 time=1ms TTL=62
Ping statistics for 192.168.13.33: Packets:
Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 1ms, Average = 0ms
C:\WINDOWS\Desktop>
Now that your hardware is configured, you are ready to begin programming.

Introduction
4i!-Database Plus and DBWizard

Programming i!-Database Plus
5
i!-Database Plus and DBWizard
Programming i!-Database Plus
i!-Database Plus consists of creating three files:
 ASP file
 AXI file
 AXS file
If you already used DBWizard to generate an include file, you can skip to Creating an AXS
file section on page 15 to finish your programming.
Prior to explaining how to program these files, a brief overview is necessary.
Data Source Name
Setting up a DSN (Data Source Name) is an easy way to provide a path to a database. It is also easy
to change the path to the database or even the type of database without changing any code in the
applications that use it. To setup a DSN:
1. Select Start > Settings > Control Panel > ODBC Data Sources, and select System DSN
from the tabs at the top. Then, click Add to add a database.
2. Select the type of database to connect to. You will be given as many choices as you have
ODBC drivers loaded. The screen allows you to enter the data source name. Pick something
relating to the database you are connecting to.
To connect to a database type not listed, contact the vendor and obtain the ODBC drivers for
that database.
3. Connect to the database. This will vary depending on the database you chose. If you chose a
Microsoft Access (MDB) database, click Select under database, and browse to the MDB file.
When you are finished, click OK and the DSN will be added.
If you have chosen another type of database and are unsure about how to connect to the database,
contact your database administrator.
Structure Query Language (SQL)
Structure Query Language, or SQL, is a common database language used to manipulate data in
many different types of databases. In the process of writing the AXS file, you will need to generate
SQL statements to access the database.
If you are not familiar with SQL, you have a couple of options:
 Learn SQL, or
 Use Microsoft Access to generate SQL statements for you.
You can use DBWizard™to help generate the SQL statements and the NetLinx code. The
DBWizard option is the easiest and fastest way to integrate to the database.
If you want to learn SQL, there are many resources available. You can find books at your local
bookstore or use web sites. If you like reading books, check out "Lan Times, Guide to SQL" by

Programming i!-Database Plus
6i!-Database Plus and DBWizard
James R. Groff and Paul N. Weinberg. If you like to read on the web, surf to:
http://www.informix.co.za/answers/english/docs/visionary/infoshelf/sqls/sqls.ix.html.
Another option is to read the Microsoft Access®help file. Microsoft has put a lot of good
information regarding SQL into the help file.
If you are familiar with Access, build the queries in Access and switch to the SQL view from the
View menu to see what you built. This provides a simple and intuitive way to build your SQL.
The last and easiest option is to use AMX's DBWizard program. This program peeks into your
database and helps you build the SQL to read and write to any of the tables or views in the database.
It also generates a large amount of the NetLinx code for you, so your development time is greatly
reduced. This program is available from www.amx.com.
HTTP, CGI and XML
HTTP (Hyper-Text Transfer Protocol)
An example URL to retrieve records 1 - 5 from a table called Titles is:
http://server/databasescript.asp?SQL=SELECT * FROM Titles&Ps=5&start=21
where:
You can use a URL like this to run a query directly against the server script, like the NetLinx
system. This is often useful during testing to make sure the server script is operating properly.
CGI (Common Gateway Interface)
CGI provides a simple way to pass parameters to a program from an HTTP request. Most
commonly, the CGI parameters appear directly on the URL line. For example:
http://www.somehost.com/index.htm?myname=amx&myhometown=dallas. The first part of
the URL looks familiar: it contains a protocol, a host name and a file to retrieve
(protocol = http://, host=www.somehost.com, file=/index.htm). The last part is CGI. CGI
parameters are passed in name-value pairs separated by an "=". The "?" is used to signify the start
of CGI parameters. The "&" is used to separate CGI parameters. So this URL contains two CGI
parameters: the first is a CGI parameter called myname and contains a value of amx. The second is
myhometown and contains a value of dallas. The CGI application on the web server has access to
these variables and can use them to help understand the context in which the request was made.
server Name of web server
databasescript.asp Name of server script
?SQL=SELECT * FROM
Titles&Ps=5&start=21
CGI Parameters
If you do use a URL like this, you will notice that once you hit enter, the spaces are
replaced with %20. URL's have a certain set of characters that are excluded from
normal use. These include space, question mark, ampersand, slash and colon. The
reason for this is these characters are used in other parts of URL's. To use them
again would cause confusion during URL parsing. One of the functions in the
NetLinxDBInclude.axi file takes care of this conversion for you.

Programming i!-Database Plus
7
i!-Database Plus and DBWizard
The NetLinx Database Gateway uses CGI to pass the database request to the server script. Both the
NetLinxDBInclude.axi and NetLinxDBInclude.asp must agree to use the same CGI parameters to
pass this information. The standard set of CGI parameters they use are in the following table.
XML (Extensible Markup Language)
Once the request is made, the server script opens the database, executes the SQL statement, and
retrieves the results. Once the script has these results, it converts all the records into XML format.
XML allows any data to be formatted in a structured way. XML is ideal for packaging up the results
from the database query and returning them to the NetLinx Master. The NetLinx code can then
parse this XML and extract the information the XML contains.
The XML format used by the NetLinx Database Gateway conforms to a certain standard so that the
NetLinxDBInclude.axi always knows how find and parse the data. An example of this format is
shown below (the actual data is highlighted to make it easier to read):
<rsHeader>
<struct>
<var><name>Start</name><data>1</data></var>
<var><name>PageSize</name><data>10</data></var>
<var><name>NumberRecords</name><data>3</data></var>
<var><name>TotalRecords</name><data>3</data></var>
</struct>
<array>
<struct>
<index>1</index>
<var><name>TitleID</name><data>11101000</data></var>
<array><name>Artist</name><string>Buffet, Jimmy</string></array>
<array><name>Title</name><string><![CDATA[Living & Dying in 3/4 Time]]>
</string></array>
<array><name>Copyright</name><string>MCA</string></array>
<array><name>Label</name><string>MCA</string></array>
<array><name>ReleaseDate</name><string>1974</string></array>
<var><name>NumTracks</name><data>11</data></var>
</struct>
<struct>
<index>2</index>
<var><name>TitleID</name><data>17248229</data></var>
<array><name>Artist</name><string>Buffet, Jimmy</string></array>
<array><name>Title</name><string>Off to See the Lizard</string></array>
<array><name>Copyright</name><string>MCA</string></array>
<array><name>Label</name><string>MCA</string></array>
<array><name>ReleaseDate</name><string>1989</string></array>
<var><name>NumTracks</name><data>12</data></var>
</struct>
CGI Parameters
Parameter Parameter Name Description Notes
sql SQL statement The SQL statement passed to the database. Required.
ps Page Size Optional. Number of records to retrieve from a record
set.
Default is 10.
start Start Index Optional. Starting index in record set to retrieve values
from.
Default is 1.
hdr Header Optional. Echoed by the server script to identify the
XML packet.
Default is "Unknown".
abs Absolute Optional. Return indexes are absolute. Default is relative.
pnl Panel Index Optional. Echoed by the server script to identify as
Panel Index.
Continued

Programming i!-Database Plus
8i!-Database Plus and DBWizard
<struct>
<index>3</index>
<var><name>TitleID</name><data>12328612</data></var>
<array><name>Artist</name><string>Buffet, Jimmy</string></array>
<array><name>Title</name><string>A-1-A</string></array>
<array><name>Copyright</string><data>MCA</string></array>
<array><name>Label</name><string>MCA</string></array>
<array><name>ReleaseDate</name><string>1974</string></array>
<var><name>NumTracks</name><data>11</data></var>
</struct>
</array>
</rsHeader>
 The <rsHeader></rsHeader> pair indicates which query this represents: it is simply
an echo of the hdr CGI parameter.
 The <struct></struct> pair contains <var></var> pairs which contain the
Start,PageSize, NumberRecords, TotalRecords and PanelIndex variables. The value
of these variables, wrapped in the <data></data> pairs, provide information about the
quantity of records.
 The <array></array> pair indicates that an array is to follow. Each record is wrapped
in a <struct></struct> pair and the columns of each record are represented by the
<var></var> pairs.
 The <index></index> pair represents the index into the array that this structure or
record represents. String data is enclosed by <array></array> tags, where the data is
enclosed in <string></string> tags. Numerical data is enclosed in <var></var>
tags, where the data is enclosed in <data></data> tags. If the abs CGI parameter has
been set, these would be absolute indexes (in this case, 51,52 and 53 instead of 1, 2 and
3).
A script error may be present. In this case, the error will be reported in the following XML format:
<rsHeader>
<scriptError>
<errorNumber>ErrorNumber</errorNumber>
<errorDescription>ErrorDescription</errorDescription>
</scriptError>
</rsHeader>
 ErrorNumber is a singled 32-bit value representing an error.
 ErrorDescription is a string describing the error.
The errors returned by the script may be generated by the underlying ADO technology used to
access the database. In all cases, the ErrorDescription contains information about how to fix
the error.

Programming i!-Database Plus
9
i!-Database Plus and DBWizard
Creating an ASP file
All you need to do in your ASP file is include the NetLinxDBInclude.asp file, and call a single
function, RunDBQuery. There are only two lines of code:
<!-- #INCLUDE FILE="NetlinxDBInclude.asp" -->
<% RunDBQuery "DeluxeCD.mdb", "" %>
The first parameter is the name of a Microsoft Access database. The second parameter is used for
advanced applications and normally should be an empty string. The RunDBQuery function will
take care of making the connection to the database and returning all the records to the NetLinx
system. If you want to use a DSN entry instead of a Microsoft Access database, simply include the
DSN= and the DSN entry name. The following syntax connects to a database setup using the DSN
entry MyDSNEntry:
<!-- #INCLUDE FILE="NetlinxDBInclude.asp" -->
<% RunDBQuery "DSN=MyDsnEntry", "" %>
For more information about setting up a data source name (DSN), see the Data Source
Name section on page 5.
Creating an AXI file
Now that the ASP file is complete, it's time to program the NetLinx AXI file. You need to include
NetlinxDBInclude.axi in order to provide access to the support functions that make your job much
easier. In the process of writing the AXI file, you will need to generate SQL statements to access
the database. If you are not familiar with SQL, you have a couple of options. You can learn SQL,
use Microsoft Access to generate SQL statements for you, or use DBWizard to help generate the
SQL statements and the NetLinx code. The DBWizard option is the easiest and fastest way to
integrate to the database. To learn more about SQL, return to the Structure Query Language
(SQL) section on page 5.
The first part of the AXI file includes the NetlinxDBInclude.axi file and defines an IP device
number for the connection to the web server:
#INCLUDE 'NetlinxDBInclude.axi'
(***********************************************************)
(* DEVICE NUMBER DEFINITIONS GO BELOW *)
(***********************************************************)
DEFINE_DEVICE
dvDB_CLIENT = 0:3:0
The next section creates NetLinx Data structures that will be used to hold the data returned from the
database. These structures closely match the column names and data types that are defined in the
database. This structure represents the titles table and contains information about each CD in the
database:
Continued

Programming i!-Database Plus
10 i!-Database Plus and DBWizard
(***********************************************************)
(* TYPE DEFINITIONS GO BELOW *)
(***********************************************************)
DEFINE_TYPE
(* TITLES *)
STRUCTURE _sDB_TITLES
{
CHAR strArtist[128]
CHAR strCopyright[128]
CHAR strLabel[128]
CHAR strReleaseDate[128]
CHAR strTitle[128]
SLONG slTitleID
}
The next section creates NetLinx variables needed to store the data and connect to the database.
The sDB_CLIENT variable contains information about the current database transaction, the location
of the web server database script. The sDB_QUE variable keeps track of all database request and
makes sure the script is sent one request at a time. The sDB_TITLES variable is an array of the
sDB_TITLES structure to hold the data from the database. In order to keep track of the discs in the
window, an sDB_LISTBOX structure is needed.
(***********************************************************)
(* VARIABLE DEFINITIONS GO BELOW *)
(***********************************************************)
DEFINE_VARIABLE
(* QUE VARS *)
VOLATILE _sDB_CLIENT sDB_CLIENT (* DB CLIENT *)
VOLATILE _sDB_QUE sDB_QUE (* dB QUE *)
(* DATABASE STRUCTURES *)
_sDB_TITLES sDB_TITLES[10] (* TITLES *)
_sDB_LISTBOX sTitleListBox
The next section creates four calls to delete, add, update and read information in the database. It is
here that we need our SQL and database setup information. When building SQL statements, you
need to know the names of the tables and/or view and usually the names of any columns you are
interested in reading or writing.
Another key piece of information is which column(s) in a table are the primary keys. Each record in
a database usually has a unique column called the primary key. This field is unique for this entry in
a given table. When deleting or updating a record in a database, you should reference the primary
key of the table to make certain it is the record you are interested in modifying. TitleID is the
primary key in our example.
When adding to a database, we may also need to provide a value for the primary key. In this case,
you must supply a key that is not currently used. Many databases offer a column typed called
AutoNumber. This is usually used as a unique key that automatically increments every time you
add a record to the database. When using AutoNumber types for your primary key, you do not need
to specify the value since it is automatically generated.
All of these calls generate an SQL statement to run against the database. Some people may find this
part the hardest since SQL may be a new language to many people. If you are having trouble with
SQL, refer to the Structure Query Language (SQL) section on page 5.

Programming i!-Database Plus
11
i!-Database Plus and DBWizard
Once the SQL has been generated, the request is entered into a queue to be sent to the web server.
The parameters for DB_ADD_TO_QUE are the queue structure for the connection, a tag, the SQL
statement and a list box structure. The tag will be returned by the web server to identify the results.
Tags should be unique for every query or sets of queries generated. (In this case, the tags are shared
for all functions that operate on the same table.) The list box structure tells the server how many
records we want to read starting from what position. Normally, you only need to read one page at a
time and the list box routines manage that. The list box value has no effect if you are not reading
from the database.
You may notice the calls to DB_STRING_REPLACE. These calls are used to replace any single
quotes with two single quotes. SQL represents a single quote as two single quotes, like Axcess and
NetLinx do.
Sometimes, the column names contains other characters that SQL considers invalid, like spaces and
dashes. If you are using column names with these characters or continually get SQL syntax errors,
try wrapping the column names in a pair of brackets [ ]. This is SQL's way of handling invalid
characters.
(*********************************************)
(* NAME: DB_DELETE_TITLES *)
(***************************************************************************)
(* Format query to delete an entry of TITLES data *)
(***************************************************************************)
DEFINE_FUNCTION DB_DELETE_TITLES(SLONG slTitleID)
STACK_VAR
CHAR strSQL[1000]
_sDB_LISTBOX sTempListBox
{
(* BUILD A QUERY STRING *)
strSQL = "'DELETE FROM Titles WHERE TitleID = ',ITOA(slTitleID)"
(* SEND THE QUERY *)
DB_ADD_TO_QUE(sDB_QUE,'TITLES',strSQL,sTempListBox)
}
(*********************************************)
(* NAME: DB_ADD_TITLES *)
(***************************************************************************)
(* Format query to add an entry to TITLES data *)
(***************************************************************************)
DEFINE_FUNCTION DB_ADD_TITLES(CHAR strArtist[128],
CHAR strCopyright[128],
CHAR strLabel[128],
CHAR strReleaseDate[128],
CHAR strTitle[128],
SLONG slTitleID)
STACK_VAR
CHAR strSQL[1000]
_sDB_LISTBOX sTempListBox
{
(* ESCAPE ANY SINGLE QUOTES *)
strArtist = DB_STRING_REPLACE(strArtist,"39","39,39") (* ' FOR '' *)
strCopyright = DB_STRING_REPLACE(strCopyright,"39","39,39") (* ' FOR '' *)
strLabel = DB_STRING_REPLACE(strLabel,"39","39,39") (* ' FOR '' *)
strReleaseDate = DB_STRING_REPLACE(strReleaseDate,"39","39,39") (* ' FOR ''
*)
strTitle = DB_STRING_REPLACE(strTitle,"39","39,39") (* ' FOR '' *)
(* BUILD A QUERY STRING *)
strSQL = "'INSERT INTO Titles (Artist, Copyright, Label, ReleaseDate, Title,
TitleID) ',
Continued

Programming i!-Database Plus
12 i!-Database Plus and DBWizard
' VALUES (',$27,strArtist,$27,', ',$27,strCopyright,$27,',
',$27,strLabel,$27,', ',
$27,strReleaseDate,$27,', ',$27,strTitle,$27,',
',ITOA(slTitleID),')'"
(* SEND THE QUERY *)
DB_ADD_TO_QUE(sDB_QUE,'TITLES',strSQL,sTempListBox)
}
(*********************************************)
(* NAME: DB_UPDATE_TITLES *)
(***************************************************************************)
(* Format query to update an entry to TITLES data *)
(***************************************************************************)
DEFINE_FUNCTION DB_UPDATE_TITLES(CHAR strArtist[128],
CHAR strCopyright[128],
CHAR strLabel[128],
CHAR strReleaseDate[128],
CHAR strTitle[128],
SLONG slTitleID)
STACK_VAR
CHAR strSQL[1000]
_sDB_LISTBOX sTempListBox
{
(* ESCAPE ANY SINGLE QUOTES *)
strArtist = DB_STRING_REPLACE(strArtist,"39","39,39") (* ' FOR '' *)
strCopyright = DB_STRING_REPLACE(strCopyright,"39","39,39") (* ' FOR '' *)
strLabel = DB_STRING_REPLACE(strLabel,"39","39,39") (* ' FOR '' *)
strReleaseDate = DB_STRING_REPLACE(strReleaseDate,"39","39,39") (* ' FOR ''
*)
strTitle = DB_STRING_REPLACE(strTitle,"39","39,39") (* ' FOR '' *)
(* BUILD A QUERY STRING *)
strSQL = "'UPDATE Titles SET Artist = ',$27,strArtist,$27,', Copyright = ',
$27,strCopyright,$27,', Label = ',$27,strLabel,$27,', ReleaseDate =
',
$27,strReleaseDate,$27,', Title = ',$27,strTitle,$27,', TitleID = ',
ITOA(slTitleID),' WHERE TitleID = ',ITOA(slTitleID)"
(* SEND THE QUERY *)
DB_ADD_TO_QUE(sDB_QUE,'TITLES',strSQL,sTempListBox)
}
(*********************************************)
(* NAME: DB_READ_TITLES *)
(***************************************************************************)
(* Format query to read a page of TITLES data *)
(***************************************************************************)
DEFINE_FUNCTION DB_READ_TITLES(_sDB_LISTBOX sTempListBox,CHAR strSEARCH[])
STACK_VAR
CHAR strSQL[1000]
{
(* BUILD A QUERY STRING *)
strSQL = "'SELECT * FROM Titles'"
IF (LENGTH_STRING(strSEARCH))
strSQL = "strSQL,' WHERE ',strSEARCH"
strSQL = "strSQL,' ORDER BY Artist'"
(* SEND THE QUERY *)
DB_ADD_TO_QUE(sDB_QUE,'TITLES',strSQL,sTempListBox)
}
The next section initializes the connection to the database script and creates a buffer to hold the
results. The call to DB_INIT_CLIENT initializes the sDB_CLIENT structure created in the variable
section. The parameters are the: IP address of the web server, IP port of the web server (usually 80)
and path to the database script. This path is the directory and file name of the ASP file. You will

Programming i!-Database Plus
13
i!-Database Plus and DBWizard
need to fill this out properly. See the section Putting It All Together section on page 28 for
reference. Also, call DB_LISTBOX_INIT to initialize your listbox.
(***********************************************************)
(* STARTUP CODE GOES BELOW *)
(***********************************************************)
DEFINE_START
(* INIT dB WEB CLIENT *)
DB_INIT_CLIENT (sDB_CLIENT,dvDB_CLIENT,'192.168.12.175',80,'/dB/CDExample.asp')
CREATE_BUFFER dvDB_CLIENT,sDB_CLIENT.strBUFF
(***********************************************************)
(* STARTUP CODE GOES BELOW *)
(***********************************************************)
DEFINE_START
DB_LISTBOX_INIT(sTitleListBox,10,1)
The next section of code reads the information from the web server and copies the data to the
structure you created to hold the results. The DATA_EVENT below has a couple of different parts.
The ONERROR section of the DATA_EVENT occurs whenever we encounter a communications
problem. The code here simply clears the buffer, calls DB_GET_IP_ERROR to return a text error
from our error code (held by DATA.NUMBER) and prints the error to the terminal.
The ONLINE section of the DATA_EVENT occurs whenever a connection to the web server is
established. The code here clears our buffer and calls DB_BUILD_HTTP_GET to format the request
for the web server. Send this request to the server and it will process the results and return the
information.
The OFFLINE section of the DATA_EVENT occurs whenever a connection to the web server is
dropped. A web server will normally drop the connection whenever is has finished processing the
request, so this is a great time for to interpret the results received. Normally, this would occur in a
STRING section of a DATA_EVENT. The first thing to do is create a series of variables that needed
through the event. To kick off the processing, call DB_PROCESS_HTTP_HEADER which will parse
the HTTP header information from the response and deal with any cookies the web server wants
you to deal with. Once that has happened, drop into a select active to process the data.
The first active in your select active makes sure the HTTP return code is 200. If not, some error has
occurred but luckily your call to DB_PROCESS_HTTP_HEADER has already processed all the errors
and printed those errors to terminal. This call will detect standard HTTP errors as well as ASP
script errors. ASP script errors may be generated by incorrect SQL so if something does not appear
to work, check the terminal for a description of the problem.
If DB_PROCESS_HTTP_HEADER has returned a value of 200 (this is a HTTP code meaning OK),
then there is data for to process. The first thing to do is look for your tag. This is the tag you sent
with the request with a few extra characters added. The first is the </rs which signifies then end of
an XML tag whose name is rs<Our Tag> where rs stands for record set. The trailing >is the end
of the XML tag. One of the things our call to DB_PROCESS_HTTP_HEADER did for you was find the
start of the XML packet. This is the point from which you begin your search.
Once we have identified what the response is to, we can process it. The next thing we do is pull out
the list box information contained in the XML by calling DB_PROCESS_LISTBOX. This function
returns the position to begin to parsing for more information. You should expect to see a series of
structures that represent your data. Simply loop as long as you can find <struct> in the XML

Programming i!-Database Plus
14 i!-Database Plus and DBWizard
packet, isolate the <struct></struct> package and remove the data contained within. The
helper function DB_GET_XML_VALUE removes the data for a given field name and returns the string
containing the data. All you need to know is the column name from that database and this function
returns the data. If the data is not of string type, you need to convert that data to the acceptable type
using a call to ATOI, ATOL or ATOF. Once we have processed all the data from the XML, call a
DISPLAY_ function to display the data.
(***********************************************************)
(* EVENTS GO BELOW *)
(***********************************************************)
DEFINE_EVENT
(*********************)
(*** dB CLIENT URL ***)
(*********************)
DATA_EVENT [dvDB_CLIENT]
{
ONERROR: (* EVENT - ERROR *********************)
{
(* CLEAR BUFFER *)
CLEAR_BUFFER sDB_CLIENT.strBUFF
SEND_STRING 0,dB_GET_IP_ERROR (DATA.NUMBER)
}
ONLINE: (* EVENT - ONLINE *********************)
{
(* SEND THE HTTP GET TO THE WEB SERVER... *)
CLEAR_BUFFER sDB_CLIENT.strBUFF
SEND_STRING dvDB_CLIENT, dB_BUILD_HTTP_GET(sDB_CLIENT)
}
OFFLINE: (* EVENT - OFFLINE ********************)
{
STACK_VAR
LONG lCODE (* HTTP RETURN CODE *)
INTEGER nLIST_PTR (* INDEX FOR RECORDS *)
LONG lLOOP (* LOOPS OVER RECORDS *)
INTEGER nFIRST (* POSITION OF START OF RECORD *)
INTEGER nLAST (* POSITION OF END OF RECORD *)
INTEGER nCOUNT (* CHARACTERS IN RECORD *)
CHAR strDB_RECORD[2000] (* HOLDS A RECORD *)
CHAR strDB_ITEM[100] (* ITEM OF RECORD *)
_sDB_LISTBOX sTempListBox (* A LIST BOX *)
(* GET COOKIE? *)
lCODE = DB_PROCESS_HTTP_HEADER(sDB_CLIENT)
SELECT
{
(* Bad Webserver return *)
ACTIVE (lCODE <> 200): {}
(* TITLES *)
ACTIVE(FIND_STRING(sDB_CLIENT.strBUFF,'</rsTITLES>',sDB_CLIENT.lHTML_START)):
{
(* Found an , so parse it for data *)
lLOOP = DB_PROCESS_LISTBOX (sDB_CLIENT,sTempListBox)
nLIST_PTR = 0
WHILE(lLOOP)
{
nFIRST = FIND_STRING(sDB_CLIENT.strBUFF,'<struct>',lLOOP)
nLAST = FIND_STRING(sDB_CLIENT.strBUFF,'</struct>',lLOOP)
nCOUNT = nLAST - nFIRST + LENGTH_STRING('</struct')
IF (nFIRST)
{
nLIST_PTR = nLIST_PTR + 1
strDB_RECORD = MID_STRING(sDB_CLIENT.strBUFF, nFIRST, nCOUNT)
Continued

Programming i!-Database Plus
15
i!-Database Plus and DBWizard
sDB_TITLES[nLIST_PTR].strArtist =
DB_GET_XML_VALUE(strDB_RECORD,'Artist')
sDB_TITLES[nLIST_PTR].strCopyright =
DB_GET_XML_VALUE(strDB_RECORD,'Copyright')
sDB_TITLES[nLIST_PTR].strLabel =
DB_GET_XML_VALUE(strDB_RECORD,'Label')
sDB_TITLES[nLIST_PTR].strReleaseDate =
DB_GET_XML_VALUE(strDB_RECORD,'ReleaseDate')
sDB_TITLES[nLIST_PTR].strTitle =
DB_GET_XML_VALUE(strDB_RECORD,'Title')
sDB_TITLES[nLIST_PTR].slTitleID =
ATOL(DB_GET_XML_VALUE(strDB_RECORD,'TitleID'))
}
lLOOP =
FIND_STRING(sDB_CLIENT.strBUFF,'<struct>',nLAST+LENGTH_STRING('<struct'))
}
IF (sTempListBox.snTOTAL > 0) sTempListBox.snTOTAL =
TYPE_CAST(nLIST_PTR)
DB_DISPLAY_TITLES(sDB_TITLES,sTempListBox)
}
}
(* CLEAR BUFFER *)
DB_ACK_QUE (sDB_QUE,sDB_CLIENT)
CLEAR_BUFFER sDB_CLIENT.strBUFF
}
}
The very last bit of code keeps your queue going. Call DB_CHECK_QUE in mainline to make sure all
request get processed and sent to the web server.
(***********************************************************)
(* THE ACTUAL PROGRAM GOES BELOW *)
(***********************************************************)
DEFINE_PROGRAM
DB_CHECK_QUE(sDB_QUE,sDB_CLIENT)
Creating an AXS file
The CDExample.axs file contains the remaining part of the code. You still need to provide the user
with some way to read and possibly update all this information. The first thing to do is create a
touch panel device to use as a user interface and include your AXI file.
(***********************************************************)
(* DEVICE NUMBER DEFINITIONS GO BELOW *)
(***********************************************************)
DEFINE_DEVICE
dvTP = 128:1:0
(***********************************************************)
(* INCLUDE FILES GO BELOW *)
(***********************************************************)
#INCLUDE 'CDList.axi'

Programming i!-Database Plus
16 i!-Database Plus and DBWizard
The next section creates some variables you will need. Create a DEVCHAN set to hold the buttons
which control the movement through the list. This action can be seen when you get to the
DEFINE_EVENT section below.
(***********************************************************)
(* VARIABLE DEFINITIONS GO BELOW *)
(***********************************************************)
DEFINE_VARIABLE
_sDB_LISTBOX sTitleListBox
DEVCHAN dcTITLES_CTRL[] = {
{ dvTP,11 }, { dvTP,12 }, { dvTP,13 }, { dvTP,14 },
{ dvTP,15 }
}
The next section creates a display call to display our results. This function is called whenever
TITLE data is processed from the server in your DATA_EVENT. An array of structures will be passed
containing the data and an _sDB_LISTBOX structure. The listbox structure will contain information
about the number of records and from what location they have started.
Basically, loop over the valid number of records and display the data you are interested in. There
are two Send_Command's that send Artists and CD Title information to our touch panel. The next
loop is used for clean up. Continue looping up to the display size, which is the total number of
items that can be displayed, and clear out the variable text. The last bit of code uses information
contained in your listbox. The first Send_Command displays record information in the form
displaying 1 - 10 of 100. The listbox also has a value you can send to a slider to represent the
current position in the list, like a scroll bar. The last code copies the listbox to the listbox variable
you defined. A template for this function is generated by DBWizard and included in the include
file. The easiest way to write this function is to copy it from the .axi file and add any customizations
required.
(***********************************************************)
(* SUBROUTINE DEFINITIONS GO BELOW *)
(***********************************************************)
(*********************************************)
(* NAME: DB DISPLAY TITLES *)
(***************************************************************************)
(* Display a page of TITLES data *)
(***************************************************************************)
DEFINE_FUNCTION DB_DISPLAY_TITLES(_sDB_TITLES sDB_TITLES[],_sDB_LISTBOX
sTempListBox)
LOCAL_VAR
INTEGER nLOOP
{
(* The following are elements of the structure where nLOOP=1 to nCOUNT: *)
FOR (nLOOP = 1; nLOOP <= sTempListBox.nCOUNT; nLOOP++)
{
(*
SEND_STRING 0,"'strArtist=',sDB_TITLES[nLOOP].strArtist"
SEND_STRING 0,"'strCopyright=',sDB_TITLES[nLOOP].strCopyright"
SEND_STRING 0,"'strLabel=',sDB_TITLES[nLOOP].strLabel"
SEND_STRING 0,"'strReleaseDate=',sDB_TITLES[nLOOP].strReleaseDate"
SEND_STRING 0,"'strTitle=',sDB_TITLES[nLOOP].strTitle"
SEND_STRING 0,"'slTitleID=',ITOA(sDB_TITLES[nLOOP].slTitleID)"*)
(* ADD DISPLAY CODE HERE *)
SEND_COMMAND dvTP,"'!T',0+nLOOP,sDB_TITLES[nLOOP].strArtist"
SEND_COMMAND dvTP,"'!T',10+nLOOP,sDB_TITLES[nLOOP].strTitle"
Continued