SQLExec - User Manual v2
Contents
1. Grosvenor Technology Ltd makes no warranty or representation either express or implied with respect to JANUS SQLExec or its documentation including quality performance merchantability or fitness for a particular purpose Grosvenor Technology Ltd Millars Three Southmill Road Bishops Stortford Herts CM23 3DH UK 44 0 1279 838000 Website http www grosvenortechnology com Grosvenor Technology Ltd O 2001 PMO24R01 i JANUS SQLExec User Manual Contacting Grosvenor Technology Ltd Grosvenor Technology is committed to supplying premier products and services as well as providing technical support and expertise in design implementation and systems after care grosvenor TECHNOLOGY Millars Three Southmill Road Bishops Stortford Herts CM23 3DH Tel 44 0 1279 838000 Fax 44 0 1279 504776 Email sales grosvenortechnology com support rosvenortechnology com Web htip www grosvenortechnology com Services provided Technical Training Comprehensive training programs to meet the needs of Approved Authorised Installers Courses include design installation troubleshooting and the fundamentals of PC networking and operation and administration of all JANUS systems Pre Sales Services Technical and quotation assistance including application configuration and installation planning Help Desk Services Telephone support to assist commissioning engineers with installation and system set up2. EE Insert to H Variable retrieved and stored as the surname I Stopping Statistics for complete run OK Failed Inserted Records 62 Updated Records 4 6 Deleted Records 6 8 Figure 2 JANUS SQLExec runtime details If a log file has been defined in the SQLEXEC INI file full details of the query results can be viewed An example of a log file is shown in the Log Files section Log Files A log file is generated each time JANUS SQLExec is run only if a log file has been defined in the SQLEXEC INI file see the SQLEXEC INI file section for more details Log files are listed by the date and time at which they were generated Log files are plain text files which can be opened and printed using a wide range of programs 14 PMO24R01 Grosvenor Technology Ltd O 2001 Using JANUS SQLExec The example below shows that a query failed to run because either the User ID or Password listed in the Query INI file was incorrect SQLExec 20011129 160308 ZONLIN EOT ZONA TA LOT ZINSEN TO 29 11 101 incorrect ZONA OA PINT LOL ZO Lav LOL PINT ZSNLINIOI 16 16 16 03 Inserted Records 0 0 16 16 16 16 EOS NNN 16 OS SVINN 16 03 SQLExec is starting 03 JImport Create Session Username or password is 03 Statistics for complete run OK Failed 03 Updated Records 0 0 03 Deleted Records 0 0 03 Validation Failed on 0 A range of error codes may be displayed
3. Unlimited availability during software warranty periods or when covered by a Software Support Plan Also available at published rates if not covered Remote Diagnostics Dial in assistance to aid system troubleshooting Unlimited availability during software warranty periods or when covered by a Software Support Plan Also available at published rates if not covered Product Repairs Available from our workshops in Bishop s Stortford A standard fee is charged as to Grosvenor Technology published rates Advanced warranty replacements are available upon request Database Conversions Easy upgrades from one system to another where database structures would otherwise be incompatible Fees may be charged if complicated scripts or interfacing is required Contact Grosvenor Technology in each instance PMO24R01 Grosvenor Technology Ltd 2001 Welcome to JANUS SQLExec Services on Demand Supplied where a Software Support Plan is not in force Fees are charged as to Grosvenor Technology published rates for technical support and on site services Grosvenor Technology reserves the right to refuse assistance to any Service Company or engineer that has not been fully trained to our required system standards Unqualified engineers may compromise the security and efficient working of the End User access control and or other security systems Using this manual The manual has been laid out in 3 main sections General Introduction S
4. be valid for the user name that has been set for the above alias Dest Identifies the destination for the imported records This item can currently be CARDS USERS or VALUES These options allow records to be imported into the cards and users tables in the JANUS Database or into a holding table Values which can be used to store data for use in further queries Variables In the context of SQLExec a variable is a name value pair This can be created using a query stored in the VALUES destination described above and retrieved for use in further queries Variables can only be used in queries NOT in stored procedures See Appendix A Using Variables for more details on when and how to use variables Info This can be any user defined text associated with the query This will be displayed on screen when the query is run 6 PMO24R01 Grosvenor Technology Ltd O 2001 System Components Example Query INI Files A query INI file can contain multiple queries Two examples are shown below Example 1 In this example there are two queries contained in the INI file these will run on different source databases Insert Query Alias GuardCard SOL employee queries insert sql Operation 1 Username sa Password Modify Query Alias ManagerCard Proc Update_Query Operation U Password Example 2 This example contains three queries in the INI file This first query inserts values retrieved from the source database int
5. can connect to a number of different database types These include Corel Paradox dBase Microsoft Foxpro and Microsoft Access The BDE also allows direct connection to ODBC database connections These are defined in the ODBC Control Panel application in Windows SQLExec can therefore connect to all database formats that have either a BDE driver or an ODBC driver Microsoft Windows can have the following ODBC drivers installed SQL Server Oracle dBase Foxpro Excel Text Driver Paradox Access Other drivers can be obtained from third party supplier 2 PMO24R01 Grosvenor Technology Ltd O 2001 System Requirements System Requirements JANUS SQLExec should be installed on computers running Windows 2000 Windows NT4 and Windows XP the use of Service Pack 6a or higher is recommended on NT4 If you have an older operating system such as Windows for Workgroups 3 11 Windows 95 Windows 98 or Windows NT 3 51 contact support grosvenortechnology com Grosvenor recommends a minimum specification of 350MHz Pentium Il processor PCI bus 128 Mb RAM for Windows NT Windows XP and Windows 2000 CD ROM drive SVGA colour screen Hard disk capacity is dependent on system size All JANUS computers require a network card or software equivalent to be installed to allow the correct configuration of TCP IP For Windows NT Windows 2000 and Windows XP use a network card or the software MS Loopback adapter Consult Window
6. in the log files see Table 6 for a description of each error code Table 6 Error codes The function completed successfully 16 An undefined error occurred This is a catch all error condition that is returned whenever an unexpected error occurs The given user name and password could not be validated against the JANUS user database The record could not be inserted as there is already a record of that key in the table An invalid parameter was detected The record being updated or deleted could not be found The BDE aliases could not be found or created Unable to find a JANUS installation The record being entered or updated is not valid The key provided is not valid The record could not be inserted The record could not be written Note This error can indicate that the database write has failed due to tables being corrupt The record being updated or deleted could not be locked The original record is unchanged An attempt to update a card record in the JANUS database failed because the given card number does not exist This error is returned because the card number field has to be unique An attempt to insert a card record in the JANUS database failed because the given card number already exists The PIN supplied was invalid The supplied key contained duplicate entries The supplied data record contained duplicate entries Grosvenor Technology Ltd 2001 PM024R01 15 Appendix A Using Variabl
7. the JANUS Card Table Surname Jean First Name Norma Title Ms Emp Number 1234 Issue 1 10 PMO24R01 Grosvenor Technology Ltd O 2001 Using JANUS SQLExec Suppose that SQLExec is used to change the surname to Monroe and the first name to Marilyn The following record must be supplied Surname Jean First Name Norma Title Ms Emp Number 1234 Issue 1 NEW KEY VAL Surname Monroe NEW KEY VAL First Name Marilyn The old key fields are used to locate the record in either the JANUS Cards or JANUS Users table the key fields are then set to the values in the NEW KEY VAL fields as supplied JANUS SQLExec User Manual Card Records Table 4 defines the data rules that have to be met for JANUS SQLExec to work successfully with JANUS Card records Table 4 Data rules for card records CEE EE Surname x Must not be blank First Name v Y x Must not be blank Title v Emp Number V Issue Y Card Number If the card number is supplied it must either not exist in the database or must already be used in the card record that is being updated Card Group V x Must contain a valid entry from the JANUS card groups table Expiry Date Expiry Time Card Status Y x Can contain anything up to the field s size limit with Valid making the card useable on the JANUS system Standard entries include Valid Pending Suspended Lost Stolen Destroyed Not issued Report Group Must contain a valid entr
8. E EEE A 12 SCORE RS een EEE ee 13 SR NS SOS a 13 LOS FILES a SR TUNER SCHERER 14 APPENDIX A USING VARIABLES cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccscccccccccccccccccccccees 17 UNN NN 17 STORING VARIABLES en 18 N S AIN VARIABLE ee a or idas 18 USING AND UPDATING VARIABLES CONCURRENTLY cscccscccscccsccececceccccscccuccesccsccesecusecsecsscasscescensccascenscesees 19 LNM 21 NN 23 vi PMO24R01 Grosvenor Technology Ltd O 2001 Introduction Introduction JANUS SQLExec from Grosvenor Technology Ltd can be used to update a JANUS for Windows system with data from an external data source e g an existing personnel database or student records It functions by executing SQL statements either from stored procedures or from a text file against a source database It then applies the results to the JANUS for Windows database The results of the program execution may also be stored in a log file JANUS SQLExec can only modify update fields in the JANUS Card and Users tables within the JANUS for Windows database Key features of JANUS SQLExec Bulk updates of the JANUS Cards and Users records from external data sources Scheduled execution of queries Insert only insert update update only and delete modification types Enables the use of Variables to reduce the amount of processing required Database vendor support via BDE and ODBC SQLExec Architecture Figure 1 below d
9. Innovative Access Control Systems JANUS SQLExec User Manual TEGRNEL ES Welcome to JANUS SQLExec Copyright Notice Copyright O 1997 2001 Grosvenor Technology Ltd All rights reserved The software contains proprietary information of Grosvenor Technology Ltd it is provided under a license agreement containing restrictions on use and disclosure and is protected by copyright law Reverse engineering of the software is prohibited Due to continued product development this information may change without notice The information and intellectual property contained herein is confidential between Grosvenor Technology Ltd and the client and remains the exclusive property of Grosvenor Technology Lid If you find any problems in the documentation please report them to us in writing Grosvenor Technology Ltd does not warrant that this document is error free No part of this publication may be reproduced stored in a retrieval system or transmitted in by any means electronic mechanical photocopying and recording or otherwise without prior written permission from Grosvenor Technology Ltd Windows is a trademark of the Microsoft Corporation Limitation of Liability In no event will Grosvenor Technology Ltd be responsible for direct indirect incidental cover or consequential damages arising out of the use or inability to use JANUS SQLExec or its documentation even if advised of the possibility of such damages Warranty
10. NG EE 9 Preparation EEE 9 Using variables eee 15 Validity Checks ococcccccoccnccccocnnccononcononnnnoos 10 Key Validity ocooccocococooonocononoononononnos 10 Required fields io 10 Variables 15 concurrently using and updating 17 Ta 103 1 SING RE NE 16 SLOWING se EE 16 USIN anita O 15 PMO24R01 23 JANUS SQLExec User Manual 24 PMO24R01 Grosvenor Technology Ltd 2001 grosvenor TECHNOLOGY Millars Three South mill Road Bishops Stortford Herts CM23 3DH UK Tel 44 0 1279 838000 email sales grosvenortechnology com web www grosvenortechnology com
11. OE EEE EE T 19 AMOS sustentado EN 19 Grosvenor Technology CONACHN Giran ii SOFVICES erre eee ree arenas ii Import Fields 10 Validity Checks 10 NES 2 NN 5 Initialising variables 16 Installing JANUS SQLExec 3 JANUS SOLEX8C i 1 Components 4 installing Ue o nina 3 key features rrrrrnrnrnnnrnvnnnvvnrrnvnnnvnnnrvnnnnennn 1 VET go ES E di 9 Licensing eier 3 9 EO GNSS nan che 13 Error codes aise 14 Override timeout siim 19 A IN esse 5 ANAS HEM reis E II 6 Grosvenor Technology Ltd 2001 Index Dest item 6 Example file 7 Free 5 Operation item ie 5 PASS NOTE a EEA 6 so nn 6 SEE aa a 6 USERNAME 6 Variables Mies redes 6 PROC OFC ERE RE 19 SQLExec Hel oe EEE 3 SQLExec Archlleeiure sie 1 SOLEXEC INI seen usada dd 4 Example file 5 Starting JANUS SQLExec 12 Storing variables ie 16 Supported Databases cccooccccccncnccccccnccnanonnnos 2 SVEEN ee 3 Requirements ccoocccooccccoccococononnnononononononos 3 System Components 2 User names and passwords 8 User records Data EEE 12 Using JANUS SQLEX6G coocccccccccooccccnncnnononcnnnos 9 Import Fields 10 EICENSI
12. al Import Fields This section provides guidelines on the fields that are used during SQLExec operation Validity Checks When SQLExec works with records it performs a number of validity checks against each record This ensures that the JANUS database is maintained in a consistent state Key Validity Any record in the database has to be unique This is accomplished by defining one or more of the pieces of information from the record that when taken as a whole can appear only once SQLExec enforces this rule by making sure that the supplied record has all of the key fields supplied In some cases the JANUS database allows some of the key fields to be blank However SQLExec will ensure that this is the correct option as it requires the fields to be defined as being blank Required Fields Required fields have to be entered as a valid value The value will normally have to be defined in an associated JANUS database table Other Rules Other rules are enforced when appropriate See Tables 4 and 5 for more details Modifying Key Fields To modify key fields the supplied record must contain the old values for each key field and new values for each of the key fields to be changed The field names for the new key values are the name of the key field preceded by NEW KEY VAL not case sensitive The field name for a new surname value for example would be NEW KEY VAL SURNAME Suppose a record exists with the following key in
13. at occurs in that resulis set In the following example a query is processed and the results set shown in Table 7 is returned Table7 Example results set Index 12 ExpDate 92 07 31 Name Jones This leads to the creation of the three variables shown in Table 8 Table 8 Variable details Variable Name Variable Value Index 12 ExpDate 92 07 31 Name Jones If a results set is returned containing more than one row then the behaviour of JANUS SQLExec is undefined 18 PMO24R01 Grosvenor Technology Ltd O 2001 Appendix A Using Variables Using and updating variables concurrently It may be necessary while using a variable in a query to update its value while processing the results set In the following example a variable is initialised to limit the number of records processed The value for the variable is saved as the last record number processed Since the last run of JANUS SQLExec however more records have been entered and only those records should be processed when SQLExec is next run with the last record processed again stored in the variable The Variables setting in the Query INI File would contain an entry like the one shown below Variables Emp No Last Processed This will store the values form the Emp No column in the results set in a variable called Last Processed The query should look like the following Select from Cards where Emp No gt VAR Last Processed In this way after each row in th
14. ation to the console window during execution This setting is optional If a no setting is entered then no is assumed Set to yes no to enable disable deletion of the log file on the start of each run This setting is optional Only a log file with the same name as the current log file will be deleted If a no setting is entered then no is assumed PMO24R01 Grosvenor Technology Ltd O 2001 System Components SQLEXEC INI Example File This section provides an example of a SQLEXEC INI file SOLExec QueryFile C Winjan SQOLRun ini LogFile C winjan SQOLExec DATE TIME 1log Verbose YES As the SQLEXEC INI file is fundamental to the operation of the JANUS SQLExec application Grosvenor Technology recommends that the file be protected This will ensure that only authorised users can make changes to the settings Query INI File Query INI files may contain one or more queries Each SQL query is made up of a descriptive title enclosed in square brackets and some of the following item headings See the section on Example Query INI Files for more details Operation This section identifies the type of operation that the query will perform Table 3 below describes all valid operation values Table 3 Valid Operation values A U Insert new records If the record already exists in the database the insert operation is aborted Update existing records If the record is not found an i
15. ct Start Programs JANUS for Windows JANUS SQLExec Alternatively select Start e Run Type cmd then click on OK The command prompt will open Change directory to the location of the SQLEXEC exe this is usually C winjan Type sqlexec and hit return 2 The application will automatically start and run the queries defined in the Query INI file named in the SQLEXEC INI file Grosvenor Technology Ltd 2001 PMO24R01 13 JANUS SQLExec User Manual 3 Upon completion of all operations JANUS SQLExec will close down Figure 2 shows an example of the information displayed in the command prompt window De Devkborkvesglesecbinrsgle gt JANUS SOLExec Copyright Grosvenor Technology 1999 2682 Version 1 8 CIL 6 6881 gt Initializing dsdevvorkssglexecshinrsS9LRun ini The following queries will he processed Insert Values Flag I sources DummyDB Dest VALUES Info Variables Query d i devworkssqlexecbinrwvalues sql Insert A amp E Variable stored as the surname Flag I Source DummyDB Dest USERS Info Variables Surname the surname Query d deyworkssqlexec binrsusers a b sq Insert c to H Variable retrieved and stored as the surname Flag I Source DummyDB Dest USERS Info Variables Surname the surnane Query d devvork sqlexec binrsusers c h sql Initialisation complete Starting Processing Insert Values Insert amp E Variable stored as the surname
16. ction below for more details Contains the queries that JANUS SQLExec will execute Provides a standardised method for accessing databases See BDE Installation and Settings for more details The installer places a copy of this file in the system s Windows directory which the user must modify before using the application It contains the overall settings used by the SQLExec application Table 2 SQLEXEC INI file settings QueryFile LogFile ImgOffset GTFmtPic Verbose DeleteLog Identifies the location and name of the INI file containing the sql queries that are to be executed Identifies the location and name of the file where results may be written The log file path can use two metadata values in the file name DATE will be replaced with today s date in YYYYMMDD format i e 19990809 TIME will be replaced with the time when JANUS SQLExec starts in HHMMSS format i e 172635 The maximum offset into the supplied file to look for a valid JPG or BMP when importing into the users table This value can be in the range of 1 to 1024 This setting is optional Set to yes no to inform JANUS SQLExec that pictures being imported are in Grosvenor Technology format This setting is optional with a default of no assumed if it is not supplied Note Errors will be generated if Grosvenor formatted pictures are imported without this setting Set to yes no to enable disable output of live inform
17. e results set has been processed the value of Emp No from the results set is stored in the variable Last Processed Grosvenor Technology Ltd O 2001 PMO24R01 19 JANUS SQLExec User Manual 20 PMO24R01 Grosvenor Technology Ltd 2001 Glossary Glossary API Application Programming Interface BDE Borland Database Engine Cards Everyone in the system is allocated a card Each card has a unique number which is contained in the card record within the system database along with the cardholder s details and the Access Choices that have been allocated This information determines through which doors and at what times the cardholder is allowed Database A store of information on the MAIN JANUS PC The information defines cards access choices doors etc and so determines how the access control system operates The database is edited and saved using the JANUS Database Update program Field Each part of the record in which you can enter data is called a field A record is made up of a number of fields Certain fields contain text such as a name others contain numbers such as the card number JANUS Comms The main program used by JANUS to communicate with between elements of the system including doors readers and other JANUS programs JANUS Database Update Part of the JANUS for Windows suite of applications this program is used to edit card and user records JANUS for Windows JANUS for Windows is an on line access con
18. es Appendix A Using Variables Variables are supported by JANUS SQLExec in order to preserve information between executions of the queries This is most useful when the user wishes to limit the amount of processing that SQLExec undertakes For example a user runs SQLExec to import new records into the JANUS database The query needs to be run frequently as new records are added rapidly As more records are added the result set that SQLExec uses will increase in size The result of which is an increase in the time it takes JANUS SQLExec to process the records What the system needs is a method of feeding back the key of the last record processed into the query the next time it is run In this way SQLExec will start processing from the next record in the series Variables provide this facility in JANUS SQLExec Variables are stored as name value pairs within the JANUS for Windows database The name is a textual field up to a maximum of 20 characters in length The characters allowed are subject to SQL permissible character restrictions The value part of the variable is stored as a BLOB binary large object field also subject to the same character restrictions Although it is possible to store large amounts of data within the value field the only use this data can be put to is within other SQLExec queries Using variables To make use of a variable a meta command must be added to a query For example the following query uses a va
19. escribes how JANUS SQLExec works in conjunction with BDE to query an existing database and use the results to update the JANUS for Windows database Stored QUERIES SQLExec Application Figure 1 SQLExec Architecture Grosvenor Technology Ltd O 2001 PMO24R01 1 JANUS SQLExec User Manual System Components JANUS SQLExec employs a number of different files and libraries in its operation INI Files Windows INI files control the operation of JANUS SQLExec These are plain text files which adhere to a simple format and can be read using a number of standard Windows APIs A minimum of two INI files are required one to define the configuration and overall parameters for how SQLExec operates SQLEXEC INI and another file that contains details on the queries that are to be executed Query INI See the SQLEXEC INI and Query INI File sections for further details Database Access All databases are accessed through the BDE The BDE allows database access from many vendors through a common programming interface The BDE requires the correct drivers to be installed in order to access a database A BDE alias is used to identify the type of database and its location BDE aliases are configured using the BDE Administrator which is installed in the Windows Control Panel by the JANUS SQLExec installer See Windows Help for more details Supported Databases The BDE
20. nsert operation is performed If a record is found it will be modified This is the equivalent of I M Modify existing records If the record is not found the modify operation is aborted Delete all records identified by the query Validate the records only Will not make any changes to the JANUS database All records that fail validation will be logged No Operation The query will be run on the source database only No changes will be made to the JANUS database Grosvenor Technology Ltd O 2001 PMO24R01 5 JANUS SQLExec User Manual Alias This section identifies the BDE alias used by SQLExec to access the source database This alias must exist within the BDE Administrator alias list SQL Identifies the file containing the SQL to be executed against the source database The file name can be in UNC format The query should be formatted in ANSI SQL Note This item cannot appear in the same query section as a Proc item Proc Defines the stored procedure that SQLExec will execute to create the record set for the operation Note This item cannot appear in the same query section as a SQL section Username Defines the database user identifier that is to be employed when opening the source database The password entry see below must correspond to this user id Password Identifies the password to be employed when opening the source database with the above alias item This parameter is optional though if used must
21. o the VALUES table Insert Values Alias DummyDB SQL d devwork sqlexec binr values sql Dest Values Operation 1 The second query inserts records into the USERS table for people whose surname begins with A and B It also stores a variable called the surname It does this by iterating through the columns of the result set of the query and stores the name of the column as the name part of the variable in this case Surname and the data as the value part of the variable in this case the name e g Brown Insert A amp B Variable stored as the surname Alias DummyDB SOL d devwork sqlexec binr users a b sql Dest Users Operation 1 variables Surname the surname Grosvenor Technology Ltd O 2001 PMO24R01 7 JANUS SQLExec User Manual The final query now uses the variable created by the second query The name of the item is passed to the query file using a meta value in this case VAR the surname JANUS SQLExec substitutes the value found for that name into the query before running it In this instance the value of the variable is Brown as it was the last surname ending in B Therefore this query will insert the records for people with surnames from C onwards into the USERS table of the database Insert c to z Variable retrieved and stored as the surname Alias DummyDB SOL d devwork sqlexec binr users c h sql Dest Users Operation 1 variables Surname t he surname As the Query INI file is fundamental t
22. o the operation of the JANUS SQLExec application Grosvenor Technology recommends that the file be protected This will ensure that only authorised users can make changes to the settings BDE Installation and Settings The BDE is installed to c Program FilesiCommon Files Borland Shared BDE Each database that SQLExec will be required to query must have an alias created using the BDE Administrator The BDE Administrator can be found in the Windows Control Panel See Windows Help for more details By default SQLExec also requires the JANUS for windows settings to be present These are JanusData and JanusSys and are created by the installer If the source database is connected via ODBC then a mapped BDE alias must be attached to a genuine BDE alias User Names and Passwords Some databases require the use of specific user names and passwords The manner in which these settings are used is dictated by the BDE and the underlying database management system For Paradox databases no user name is required however Microsoft Access and SQL Server databases have the username set in the BDE alias settings page The required password will be retrieved from the INI file identified by the QueryFile setting in SQLEXEC INI See the SQLEXEC INI and Query INI File sections for more details 8 PMO24R01 Grosvenor Technology Ltd O 2001 Using JANUS SQLExec Using the JANUS SQLExec software This section describes how to set up and
23. riable named Index in order to limit the number of records that will be processed Selen from MyTable where ID gt VAR Index SQLExec parses the query before it is passed to the database layer in order to substitute the V AR Index with the value of the variable named Index If the variable does not exist the meta command will be removed and nothing used to replace it Grosvenor Technology Ltd 2001 PMO24R01 17 JANUS SQLExec User Manual Storing variables To store a value into a variable and to also initialise it settings must be added to the Variables section of the Query INI file The Variables setting is a comma separated list of column names with optional aliases For example Variables MyColumn ColumnAlias Column2 MyColumn is the name of the column in the results set which is processed ColumnAlias is the name to use when storing in the variables When the variable is to be used in a query it must be referred to by the alias If an alias is NOT given then the name of the column is used as the name for the variable Initialising variables lt can often be useful to initialise variables in a one off manner For example creating a query that sets up a variable with a value without using the real query This is performed using the VALUES destination in the Query INI file When used with a query JANUS SQLExec will process the results set returned by the query and create a variable for each column th
24. s Help for details SQLExec requires version 5 or later of the Borland Database Engine Licensing Before using JANUS SQLExec for the first time the user must contact Grosvenor Technology for a licence file to enable the application to work Installing JANUS SQLExec To install the JANUS SQLExec software from the CD 1 Insert the CD labeled JANUS for Windows into your CD ROM drive If Autorun is enabled on your system the CD starts up and you can select Install JANUS from the CD autorun page Alternatively from the Start menu select Run Type F autorun exe substitute the appropriate letter of your CD ROM drive for F The installation program prompts you to select which components are to be installed Select SQLExec and click on Next Follow the instructions on the screen to proceed with the installation Once underway a screen will display both a progress bar and an estimated time to completion When all files have been installed click Finish to exit the installer Grosvenor Technology Ltd 2001 PMO24R01 3 JANUS SQLExec User Manual JANUS SQLExec Components This section identifies each of the components that are installed with SQLExec Table 1 Components required for JANUS SQLExec SQLEXEC EXE Application executable Installed by default into C WINJAN SQLEXEC INI Initialisation file for SQLEXEC EXE installed into the system s QUERY INI BDE SQLEXEC INI Windows directory See the SQLEXEC INI se
25. trol system with software options from 2 doors up to 1000 JANUS provides access control graphical alarm monitoring video imaging card production asset protection plant control and integration with safety and building management systems such as intruder fire CCTV T amp A cashless vending and building management Password This is a security measure used to restrict access to the JANUS for Windows system and resources Itis a unique string of characters that must be provided before a User ID is authorised Record Each group of information within the database for example about a card a time zone or an area is called a record SQL Structured Query Language User ID This defines the logon level of a JANUS User and so determines the degree of access to the JANUS for Windows system resources It is used in conjunction with a password Grosvenor Technology Ltd O 2001 PMO24R01 21 JANUS SQLExec User Manual 22 PMO24R01 Grosvenor Technology Ltd 2001 Index BDE Installation and Settings 8 User Names and Passwords 8 Card records Data EE 11 A ER NE drosedace eee 19 Components erraram 4 SOLEXEC NO ss ats 4 Concurrently using and updating variables 17 Conventions iii Data rules for card records 11 Data rules for user records 12 Database 22 2 nennen 19 Database Access 2 A 14 FEL e
26. use SQLExec This assumes that a successful installation has already been performed See Installing JANUS SQLExec for more details Preparation In order to use SQLExec the following items must be created 1 Initialise the SQLEXEC INI file Modify the default SQLEXEC INI file created by the installer See the SQLEXEG INI section in JANUS System Components 2 Create or modify the INI file identified by the QueryFile setting in the SQLEXEC INI file This file contains details on the queries that SQLExec will execute For details on the contents and format of this file see the Query INI File section 3 Initialise the Database Aliases SQLExec retrieves a set of records from a source database in order to update the JANUS for Windows database The source database and the JANUS for Windows database are identified to SQLExec via BDE Aliases These must be unique and are configured in the BDE Administrator located in the Windows Control Panel 4 Setup a Password and User ID The SQLExec application requires the following Password and User ID to be set up These should be entered via the JANUS Database Update program Password SQLEXEC User ID SQLEXEC The logon level of the JANUS SQLExec user is not important Before using JANUS SQLExec for the first time the user must contact Grosvenor Technology for a licence file to enable the application to work Grosvenor Technology Ltd 2001 PMO24R01 9 JANUS SQLExec User Manu
27. y from the JANUS report groups table APB Exempt Department If present must contain a valid entry from the JANUS department table Card ID Comments Start Date Start Time Global CG For use with multiple card groups Contains a comma separated list of card groups Must contain valid entries from the JANUS card group table Card Design If supplied must contain a valid entry from the JANUS Card Design table 12 PMO24R01 Grosvenor Technology Ltd O 2001 User Records Using JANUS SQLExec Table 5 defines the data rules that have to be met to successfully work with JANUS User records Table 5 Data rules for user records ETA eee Surname First Name Title Emp Number Def 2 Def 3 Def 4 Def 5 Def 6 Address Picture Signature PicFile SigFile v v Y v x Must not be blank V x Must not be blank Contains a JPG or BMP file Contains a JPG or BMP file Full path to a JPG or BMP file If this field exists it will be used instead of the Picture field Full path to a JPG or BMP file If this field exists it will be used instead of the Signature field Starting JANUS SQLExec The application can be run from the Start menu or from the Command prompt Alternatively JANUS SQLExec can be scheduled to run at a specific time using the Windows Task Scheduler which can be found in the Control Panel See Windows Help for details of using this option 1 Ensure that JANUS Comms is running and sele
28. ystem requirements and installation instructions How to use JANUS SQLExec This symbol represents information important to the operation of the JANUS SQLExec application This symbol represents a warning of potential conflict or data loss when using the application Grosvenor Technology Ltd O 2001 PMO24R01 iii JANUS SQLExec User Manual iv PMO24R01 Grosvenor Technology Ltd 2001 Table of Contents Table of Contents COPYRIGHT NOTICE sas aos ada aio I NTN MP I WARRANT cosa I CONTACTING GROSVENOR TECHNOLOGY LTD 00 000 0000 0000000000000000000000000000000000000000 00000000000 000 II SERVICES PRO VIDED said II SO MANDA ee ee ee Se EE EAE ee eee HI INTRODUCTION snekkeren sines kinad dad 1 KAREN OS SOC a 1 TE EE SYSTEM COMPONENTS seen O ene 2 VIT NN 2 DATABASE ACCESS cara e een 2 SUOMI ADN ee ee 2 SYSTEM REQUIREMENTS citadino een 3 DICEN SINO ee 3 MIT NS 3 JANUS SOLEXEC COMPONEN ES siii iii 4 VET MN 4 VEME 5 MIE hp 5 VPN 5 AM 6 PN 6 ET GR 6 PN 6 DN 6 en 1912 1 REE ENE EE EE EE ESN SEE ER EE ERE 6 A A A 0 A 6 EXAMPLE QUERO NN a a 7 TL 7 DE EEE F BDE INSTALLATION AND SETTINGS ee are ee on 8 User Names id FOSS WOTra S orrei a A NE 8 USING THE JANUS SQLEXEC SOFTWARE 000 00000000000000000000000 0000000000000000nsssnnnnnnensnnssnsnsnsssssssssssssssssssese 9 PP 9 OR NN 10 Grosvenor Technology Ltd O 2001 PMO24R01 vV JANUS SQLExec User Manual VEN 10 VNR 10 CR E
Download Pdf Manuals
Related Search