User Manual SQL Exec - SECURI
Contents
1. General Introduction System 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 ili JANUS SQLExec User Manual iv PMO24R01 Grosvenor Technology Ltd O 2001 Table of Contents Table of Contents COPYRIGOAT NOTICE Le ode 5 LIMITATION OF LIABILITY ccceccccccccccccecececececececececececececucucueucucueueueueueaeauauauauauauauauaeaeneneneneneneneneueneneneneneness VEERSE CONTACTING GROSVENOR TECHNOLOGY LIDL NNN NEEN Il SERVICES DROMIDED IAR Il USNC TAO MAA A aa en dr deleita II INTRODUCTION E 1 KEY FEATURES OF JANUS GOLENEC 1 SEE NEUES 1 SYSTEM COMPONENTS EEE 2 NEE ee 2 DATABASE ACCESS cocina diia il dotada 2 1810 0101 1210 MAA oa 2 SYSTEM REQUIREMENT S 2 3 n_n 3 INSTALLING JANUS GOLENEC annann 3 JANUS SQLEXEG COMPONENTS cuina 4 SEE Nu 4 SQLEXEC INTEXample SE 5 MENE aa 5 31 EN EE de 5 TE 6 FUN 6 NTN 6 PN 6 VET 6 RS E E A a 6 NN 6 EXAMPLE QUERY INI FLES enee T OR IP PA IA 7 EE 7 BDE INSTALLATION AND SETTINGS ccccecececcecececececececececucucucucucucucueueueauatauaeauauaeaeaeaeaeaeaenensneneneneneneneneness 8 User Names and Haseworde 8 USING THE JANUS SQLEXEC SOFTWARE evavavnvnvnvnvnvnvnvnvnvnnnvnvnrnrnnnnnnnnnnnnnnnnnnnn2. Warranty 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 http 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 installatio
3. JANUS SOLExec User Manual Grosvenor Technology The Science of Security Welcome to JANUS SOLExec 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 Ltd 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
4. JANUS SQLExec User Manual 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 SQLEXe0 200 111297160308 291111101 ZIXLINXLOL ZIXTINLOL 29 NL NO incorrect Z INLINLOL ZOINTINLOL 29 TT LOT SNITT LOL HATLO Lo 16 Lo LOs 16 16 LOS 16 tO D ee E a LT EE 03 SQLExec is starting AS ENN 03 JImport Create Session Username or password is 03 Statistics for complete run 3 OK Failed 03 Inserted Records 0 0 03 Updated Records 0 0 03 Deleted Records 0 0 03 Validation Failed on 0 A range of error codes may be displayed in the log files see Table 6 for a description of each error code Table 6 Error codes 14 2 3 9 10 15 16 17 The function completed successfully 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 wri
5. Def 6 Address Picture Contains a JPG or BMP file Signature Contains a JPG or BMP file PicFile Full path to a JPG or BMP file If this field exists it will be used instead of the Picture field SigFile Full path to a JPG or BMP file If this field it will be used instead of the Signature Starting JANUS SQLExec 12 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 select Start Programs JANUS for Windows JANUS SQLExec Alternatively select Start 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 PMO24R01 Grosvenor Technology Ltd O 2001 Using JANUS SQLExec 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 D rf i i altel Y y JAN Es LE 6 Y Ch WEI I EXE E oi x inr gt sqglexetc exe SQGLExec Copyright Grosvenor Technology 1999 2082 Version 1 8 CIL 6 666841 Initialising Lirdevsorkveagl
6. 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 variable named Index in order to limit the number of records that will be processed select 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 A Variables may only be used within query files NOT in stored procedures Grosvenor Technology Ltd O 2001 PMO24R01 15 JANUS SOLExec 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 opt
7. 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 into the VALUES table Insert Values Alias DummyDB SQL d devwork sgqlexec 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 lt also stores a variable called the surname lt 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 B Variable stored as the surname Alias DummyDB SQL d devwork sqlexec binr users a b sql Dest Userts 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
8. 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 the 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 17 JANUS SQLExec User Manual 18 PMO24R01 Grosvenor Technology Ltd O 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 st
9. z Variable retrieved and stored as the surname Alias DummyDB SQL d devwork sqlexec binr users c h sql Dest Users Operation I variables Surname the surname As the Query 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 BDE Installation and Settings The BDE is installed to c Program Files Common 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
10. 001 Index Dest item oooccoonccccnnccocnoconncconccncncnnnnonanoss 6 Example file annannnannaannannnennnnnennnennnnnnne 7 FOT PROA o aa 5 Operation item 5 PST 6 Proc NON A ser 6 SAL HEM se 6 Username 6 Variables em 6 Record PME RN PRE 19 SQLExec ICSASIMO maskene 3 SQLExec Archtechure 1 SE EE Nr 4 Example file annannannnannannnannnnnnnnnnnnnnnnnne 5 Starting JANUS GO Ever 12 Storing variables 16 Supported Databases 2 SIS abate un 3 Requirements 3 System Components 2 User names and passwords 8 User records Data MUS Lai 12 Using JANUS SQLExec 9 o A 10 LICONSIMO oem 9 Preparation 9 Using variables 15 Validity Checks annnannannnnnnennannnnnnnnnnnnnnnnnnnne 10 Key Validity oooocccoocccccnococnconnconononanos 10 Required fields 10 KES Ge GE 15 concurrently using and updating 17 It Une e 16 AON ae e o e 16 ao EEE EOI 15 PMO24R01 21 JANUS SQLExec User Manual 22 PMO24R01 Grosvenor Technology Ltd O 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 grosvenortechno
11. 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 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 SQLEXEC 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 im
12. Valid Operation values Insert new records If the record already exists in the database the insert operation is aborted U Update existing records If the record is not found an insert operation is performed If a record is found it will be modified This is the equivalent of M M Modify existing records If the record is not found the modify operation is aborted D Delete all records identified by the query V Validate the records only Will not make any changes to the JANUS database All records that fail validation will be logged N No Operation The query will be run on the source database only No changes will be made to the JANUS database Grosvenor Technology Ltd 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 s
13. d 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 information 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 SQLRun ini LogFile C winjan SQLExec DATEH TIME log 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
14. d records Table 4 Data rules for card records EEE Surname x Must not be blank First Name v Y x Must not be blank Title Y 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 v 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 entry 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 Grosvenor Technology Ltd 2001 JANUS Card Design table PMO24R01 11 JANUS SOLExec User Manual User Records 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 ELA Surname Y x Must not be blank First Name Y Y x Must not be blank Title Y Emp Number v Def 2 Def 3 Def 4 Def 5
15. dification 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 describes 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 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 SQLEXEG 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 A
16. dministrator which is installed in the Windows Control Panel by the JANUS SQLExec installer See Windows Help for more details Supported Databases The BDE 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 NTA4 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 II 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 instal
17. ee 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 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 SQL employee queries insert sql Operation I Username sa Passwora Modify
18. esecvbinr gt S0LRun ini The following queries will be processed Insert Values Flag I source DunnyDHB Dest VALUES Info Uariables Query d devworkssqlexec binrvalues sql Insert A amp E Variable stored as the surnane Flag I source DummyDH Dest USERS Info Variables Surnanme the surname Query disdev orkveqlexec hbinrvusers ah sol Insert c to H Variable retrieved and stored as the surname Flag I Source DummyDE Dest USERS Info Variables Surname the surname AMES AER AAN Ch sol Initialisation complete Starting Processing Insert Values Insert A amp BE Variable stored as the surname LInsert c to H Variable retrieved and stored as the surname I Stopping Statistics for complete run OK Failed Inserted Records 6 2 Updated Records 4 6 Deleted Records 6 6 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 Grosvenor Technology Ltd O 2001 PMO24R01 13
19. ional 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 It 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 that occurs in that results 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 16 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
20. led 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 Windows 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 lautorun 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 O 2001 PMO24R01 3 JANUS SOLExec User Manual JANUS SOLExec 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 defaul
21. logy com
22. n and system set up 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 O 2001 Welcome to JANUS SOLExec 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
23. nnnnnnnnnnnnnnnnunnnnnnnnnnnnr 9 DGERDARATION 9 Grosvenor Technology Ltd O 2001 PMO24R01 V JANUS SQLExec User Manual IMPORT ee EE 10 VAV A 10 E EEE ENE NET ERE EEE 11 Ver RENN A y A 12 STARTING JANUS SOLE EE 12 OC PES ent 13 APPENDIX A USING VARIABLES uarnnanrnnannnanvnnannvnvennnnnnennnnnennnnnnsnnvnnennnnnvnnennnnavsnavsnennnvavsvavnnsnvanene 15 SSE CR EE 15 STORING VARIABLES rra eee een 16 INITIALISING VARIABLES 0 a a dia 16 USING AND UPDATING VARIABLES CONCURRENTLY annnnnnannnunnnnnnnnnnnnnnnnnornnrrnornnrrnnrinrrnerrnrrnurrnrrnnrrnrrnnrnae 17 GLOSSARI EE 19 INDE GE 21 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 mo
24. ore 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 control 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 It is 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 Q
25. portant 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 Manual 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 10 PMO24R01 Grosvenor Technology Ltd O 2001 Using JANUS SQLExec Card Records Table 4 defines the data rules that have to be met for JANUS SQLExec to work successfully with JANUS Car
26. t 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 section 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 Setting Description 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 ADATE 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 assume
27. tten 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 PMO24R01 Grosvenor Technology Ltd O 2001 Appendix A Using Variables 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
28. uery 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 19 JANUS SQLExec User Manual 20 PMO24R01 Grosvenor Technology Ltd O 2001 Index BDE Installation and Settings 000naanaannaannn 8 User Names and Passwords 8 Card records Data UIC Suscrito 11 A A 19 Components ccccocccnccnnnncnnnccnnncnnoncncnncnnnnnnnnnnnnnnnos 4 SOLPXEC IN E cnc ee 4 Concurrently using and updating variables 17 Lopnventons lil Data rules for card records 11 Data rules for user records 12 BELE 05 ee 19 Database Access 2 A 14 OI e 19 CS A A 19 Grosvenor Technology contactaron es i le ji Import Fields ba 10 Validity Checks annonnnannnnnnnnnennnnnnnnnnnnnnnnni 10 Il 1 2 QUIN ceca ene 5 Initialising variables 16 Installing JANUS GO Ever 3 JANUS SOL EXC iii noia 1 Components 4 installing oocococnccccncccnnccnnncncncnncnconcncnanonos 3 key features ooccoocccccccoconncconccnnnocanonnanonnanonas 1 USINO PR peered EE EENE EE 9 Licensing 3 9 ECS ae 13 Error ee 14 Override timeout ooccccocccccncccnccccnccncnocannnnon 19 Query INT ve aa 5 PETN 6 Grosvenor Technology Ltd O 2
Download Pdf Manuals
Related Search