instruction manual i!-DatabasePlus and DBWizard i n t e g ra t i o n ! S o l u t i o n s
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.
Table of Contents Table of Contents Introduction ...............................................................................................................1 i!-DatabasePlus ................................................................................................................. 1 DBWizard .......................................................................................................................... 1 Supported Operating Systems .......................................................
Table of Contents Webserver path...................................................................................................................... 32 Absolute database path ......................................................................................................... 32 NetLinx AXI file....................................................................................................................... 32 NetLinx local IP port ................................................................
Introduction 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.
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®/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.
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© 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).
Introduction 4 i!-Database Plus and DBWizard
Programming i!-Database Plus 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.
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® 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.
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.
Programming i!-Database Plus 3 TitleID12328612 ArtistBuffet, Jimmy TitleA-1-A CopyrightMCA LabelMCA ReleaseDate1974 NumTracks11 The pa
Programming i!-Database Plus 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: <% 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.
Programming i!-Database Plus (***********************************************************) (* 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.
Programming i!-Database Plus 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.
Programming i!-Database Plus ' 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 *) (*******************************************
Programming i!-Database Plus 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.
Programming i!-Database Plus packet, isolate the 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.
Programming i!-Database Plus 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].
Programming i!-Database Plus 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.
Programming i!-Database Plus } FOR (; nLOOP <= sTempListBox.nDISPLAY_SIZE; nLOOP++) { (* CLEAN UP EMPTY ENTRIES HERE *) SEND_COMMAND dvTP,"'!T',0+nLOOP,''" SEND_COMMAND dvTP,"'!T',10+nLOOP,''" } SEND_COMMAND dvTP,"'!T',49,'Displaying ',ITOA(sTempListBox.snFIRST),'-', ITOA(sTempListBox.snLAST),' of ',ITOA(sTempListBox.snTOTAL)" SEND_LEVEL dvTP,1,sTempListBox.nLEVEL_VAL sTitleListBox = sTempListBox } The next section creates a level for the scroll bar.
Programming i!-Database Plus TO[dcTITLES_CTRL[nIDX]] } } If you want to edit the database, you need to capture data from the user and call your other SQL building routines to send your request off to the server. NetlinxDBInclude.asp Functions The following table describe the functions contained in the NetlinxDBInclude.asp file. NetlinxDBInclude.asp Functions XMLTag The XMLTag function is used when converting record sets to XML. Converts field name to XML compatible tag.
Programming i!-Database Plus RunDBQuery The RunDBQuery function is used to convert record sets to XML. Converts ADO RS to XML. Syntax: RunDBQuery( strDBPath, strProvider ) Variables: strDBPath = Represents the file path or DSN to the database. If the file has no path included, the path is assumed to be local in the same directory as the ASP file. (Required.) strProvider = Represents the provider string for the database. No value is needed for a DNS type connection. If the file contains ".
Programming i!-Database Plus NetlinxDBInclude.axi Constants All constants can be overridden by defining your own values in your program. NetlinxDBInclude.axi Constants Parameter Value Description Notes IP_TCP = 1 (* TCP/IP COMMUNICATIONS *) Passed IP_CLIENT_OPEN and IP_CLIENT_CLOSED. nDB_MAX_TIMEOUT = 60 (* MAXIMUM TO WAIT FOR XML RESPONSE *) Time to wait for ASP to process and return XML. nDB_MAX_BUFF_SIZE = 10000 (* WEB SERVER BUFFER SIZE *) Buffer size for XML.
Programming i!-Database Plus Structures STRUCTURE _sDB_CLIENT { CHAR strBUFF[10000] CHAR strQUERYSTRING[1000] CHAR strWEB_SERVER[100] CHAR strDB_ASP_FILE[100] INTEGER nWEB_PORT CHAR strASP_COOKIE[300] DEV dvSOCKET INTEGER nMAX_TIMEOUT INTEGER nTO_COUNT CHAR bTO_LO LONG lHTML_START CHAR nVERSION_CHECK } STRUCTURE _sDB_QUE { CHAR strQUEUE[10][2000] INTEGER nQ_HEAD INTEGER NQ_TAIL CHAR bQ_HAS_ITEMS CHAR bQ_READY INTEGER NQ_MAX CHAR bINIT } STRUCTURE _sDB_HTTP_HEADERS { CHAR strHTTP_PROT[20] LONG lHTTP_CODE CH
Programming i!-Database Plus Functions The following table is a list of functions contained in the NetlinxDBInclude.axi. NetlinxDBInclude.asp Functions DB_ACK_QUE() Acknowledges the queue so the next message can be sent in the queue structure. The DB_ACK_QUE function is used to acknowledge the last message sent by the queue. It should be called whenever a message is properly processed in the DATA_EVENT for the database server script connection.
Programming i!-Database Plus NetlinxDBInclude.asp Functions (Cont.) The DB_CHECK_QUE function is used to watch the queue and send messages Sends the next command from when the client is ready. It should be called once in DEFINE_PROGRAM. Syntax: the sDB_QUE to the sDB_CLIENT database server DB_BUILD_HTTP_GET (_sDB_CLIENT sDB_CLIENT) connection. Variables: DB_CHECK_QUE() sDB_QUE = Represents the sDB_QUE for a given database server script connection. (Required.
Programming i!-Database Plus NetlinxDBInclude.asp Functions (Cont.) DB_GET_XML_VALUE() Extracts values from XML into NetLinx data structures only when not using XML_TO_VARIABLE. The DB_GET_XML_VALUE function is used to extract values from XML into NetLinx data structures only when not using XML_TO_VARIABLE. Syntax: DB_GET_XML_VALUE(CHAR strITEM_DATA[], CHAR strTAG[]) Variable: strITEM_DATA = Represents the XML stream containing the data to be extracted. (Required.
Programming i!-Database Plus NetlinxDBInclude.asp Functions (Cont.) DB_LISTBOX_INIT() Initializes an sDB_LISTBOX structure. The DB_LISTBOX_INIT should be called for all sDB_LISTBOX structures before use. Syntax: DB_LISTBOX_INIT(_sDB_LISTBOX sTempListBox, INTEGER DisplaySize, INTEGER nPanelIndex) Variables: sTempListBox = Represents the list box structure. (Required.) DisplaySize = Represents the size of page for the list box. (Required.
Programming i!-Database Plus NetlinxDBInclude.asp Functions (Cont.) The DB_PRINT_ERROR function is used to print any database script errors to the Extracts a database script NetLinx terminal. This should be called whenever the XML stream from a web server is processed. error from an Syntax: sDB_CLIENT structure and prints it to the NetLinx DB_PRINT_ERROR (_sDB_CLIENT sDB_CLIENT) terminal. Variables: DB_PRINT_ERROR() sDB_CLIENT = Represents the sDB_CLIENT for a given database server script connection.
Programming i!-Database Plus NetlinxDBInclude.asp Functions (Cont.) DB_SCALE_SLIDER() The DB_SCALE_SLIDER copies the slider value to the sTempListBox structure. Calculates the slider position, 0 - 255, for a given sDB_LISTBOX structure. Syntax: DB_SCALE_SLIDER(_sDB_LISTBOX sTempListBox) Variables: sTempListBox = Represents the list box structure. (Required.) Return Values: DB_SCALE_SLIDER returns a number representing the position in the list, with a value of 0 - 255.
Programming i!-Database Plus Putting It All Together Once you have created all the files, it is time to put them to work. The first thing you want to do is put the database, the NetLinxDBInclude.asp file, and database server script in a path accessible by your web server. If you accepted the defaults when installing your web server, the root of the server most likely exists in C:\Inetpub\wwwroot.
Running DBWizard Running DBWizard File Menu You can create, open and save DBWizard (DBW) files. The DBW file contains the database connection, all queries you have generated and all the information about the webserver connection and NetLinx code parameters. You can save your work in DBWizard; if you have a small change to make, you can add it to your existing database queries. You should note that DBwizard will re-generate the AXI file listed in the "NetLinx AXI File" text box.
Running DBWizard Queries Tab The Queries tab is where you build and decide how to access the database from NetLinx. On the left, you will see a set of tabs for Tables and Views. This represents the information contained in the database. When you select a table or view, all the fields for it will show up in the "Fields For..." window. For a given table or view, you can select any or all fields to read or write to the database.
Running DBWizard File Tab The options under the File tab allow you to control the NetLinx code (AXI file) and database server script (ASP file) that will be generated. The options are: ASP File Webserver Host name or IP Address Webserver IP Port Webserver Path Absolute Database Path NetLinx AXI File NetLinx Local IP Port NetLinx Code Prefix Encapsulate Once all these values are populated, you can select Generate Code from the Tools menu to generate the code.
Running DBWizard Webserver path The path from the web server root where the ASP file will exist. This is the path relative to the web server root where the database and database server script will be placed. If you do not know where this will be yet, leave it blank. If you have control of the web server, enter a simple directory name that represents your application. Then you can create this directory on your web server and place the database and database server script in this directory.
Running DBWizard Writing Your AXS File Now that DBWizard has generated the AXI file for you, you still have some work to do. The AXI generated by DBWizard now contains all the code necessary to read and write information to the database, as well as the basic infrastructure required to send your requests to the web server. However, you still need to decide when and what you need to write to and from the database.
brussels • dallas • los angeles • mexico city • philadelphia • shanghai • singapore • tampa • toronto* • york 3000 research drive, richardson, TX 75082 USA • 469.624.8000 • 800.222.0193 • fax 469.624.7153 • technical support 800.932.6993 033-004-2565 01/05 ©2005 AMX Corporation. All rights reserved. AMX, the AMX logo, the building icon, the home icon, and the light bulb icon are all trademarks of AMX Corporation. AMX reserves the right to alter specifications without notice at any time.
