AMX DATABASEPLUS AND DBWIZARD User Manual
instruction manual
i!-DatabasePlus and
DBWizard
integration!Solutions
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
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 SOFTWARE 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 Corporation'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.
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
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.
that you may either (a) make one copy of the SOFTWARE solely for backup or archi-
AMX Corporation disclaims all other warranties, either expressed or implied, including, but
. In no event shall AMX Corporation be liable for any damages
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 Computer Software clause at DFARS 252.227-7013 or subparagraphs (c)(1) and (2) of the Commercial Computer Software--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
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
i!-Database Plus and DBWizard
i
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
ii
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.
Introduction
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
Window 98
®
, Windows NT 4.0® Workstation, Windows NT Server®, Windows 2000®
Workstation or Windows 2000
A Web server. You can use Microsoft Internet Information Server (IIS) or Microsoft
©
operating system such as Windows 95®,
®
Server.
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
i!-Database Plus and DBWizard
(minimum requirement); 300 MHz or faster recommended, with 96 MB of installed
memory.)
1
Introduction
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
Windows 2000
®
/98®, and NT 4.0® Workstation uses PWS.
®
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.
2
i!-Database Plus and DBWizard
Introduction
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
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
©
operating system such as Windows 95, Window 98,
C:\WINDOWS\Desktop>
Now that your hardware is configured, you are ready to begin programming.
i!-Database Plus and DBWizard
3
Introduction
4
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.
Programming i!-Database Plus
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
DBWizard option is the easiest and fastest way to integrate to the database.
™
to help generate the SQL statements and the NetLinx code. The
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
i!-Database Plus and DBWizard
5
Programming i!-Database Plus
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
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:
®
help file. Microsoft has put a lot of good
server
databasescript.asp
?SQL=SELECT * FROM
Name of web server
Name of server script
CGI Parameters
Titles&Ps=5&start=21
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.
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.
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.
6
i!-Database Plus and DBWizard
Programming i!-Database Plus
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.
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.
start Start Index Optional. Starting index in record set to retrieve values
from.
hdr Header Optional. Echoed by the server script to identify the
XML packet.
abs Absolute Optional. Return indexes are absolute. Default is relative.
pnl Panel Index Optional. Echoed by the server script to identify as
Panel Index.
Default is 10.
Default is 1.
Default is "Unknown".
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>
Continued
i!-Database Plus and DBWizard
7
Programming i!-Database Plus
<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 <struct></struct> pair contains <var></var> pairs which contain the
The <array></array> pair indicates that an array is to follow. Each record is wrapped
The <rsHeader></rsHeader> pair indicates which query this represents: it is simply
an echo of the hdr CGI parameter.
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.
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
enclosed in
tags, where the data is enclosed in
<string></string> tags. Numerical data is enclosed in <var></var>
<data></data> tags. If the abs CGI parameter has
<array></array> tags, where the data is
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.
8
i!-Database Plus and DBWizard
Loading...