WIXLIB

PrefaceFinding Additional InformationOur online help system (CHM files) are listed in the table below. These files areavailable on the 9700 HMS installation CD.This online help system.9700 CA/EDCincludes information about.w installation and setup procdures for the 9700 CreditAuthorization/Electronic Draft Capture drivers9700 Mobile MICROSSetup & Usagew configuration procedures and functional information9700 Programmingw programming a 9700 System, file by file9700 Remote ManagementConsole Tourw basic navigation and usage of the 9700 Remote9700 Report Writer(with Autosequencer)w designing and generating reports, and using the9700 SAR Setup and Usagefor the Mobile MICROS hand-held deviceManagement ConsoleAutosequencer modulew configuration procedures and functional informationfor the 9700 Stand Alone Resilient POS Clientw software modules used for administrative tasks, such9700 System Administrationas reloading the system and viewing system activityw setting up an EBUTO System9700 User’sxivw step-by-step instructions for various operator tasks

PrefaceFinding Additional InformationSymbolsThis symbol is used to bring special attention to a relatedfeature.This symbol is used to indicate a special tip for using thecurrent feature.This symbol indicates that care should be exercised whenprogramming a feature or performing an action.Design and ProductionThis manual was written with Adobe FrameMaker. Some illustrations anddiagrams were designed in CorelDraw and Microsoft Visio.9700 SQL Manualxv

PrefaceFinding Additional Informationxvi

Chapter 119700sql Building BlocksIn This ChapterThis chapter describes the 9700sql program and the command fileformat that the program calls, and how to use 9700sql to importinformation into and export data out of the 9700 database.Understanding 9700sql. 1-2Creating 9700sql Scripts . 1-3Exporting Data Files. 1-18Importing Data Files. 1-25Error Checking . 1-29Programming Notes. 1-309700 SQL Manual1-1

9700sql Building BlocksUnderstanding 9700sqlUnderstanding 9700sqlThe 9700sql, MICROS’s version of Structured Query Language (SQL), is adatabase access language that features intelligent, selective data exporting andimporting for the 9700 System database—without the need to manually enter9700 System data into a format that can be used by other applications. In addition,9700sql exports and imports ASCII files that are easily exchanged withspreadsheet programs and database management systems. The 9700sql does not,however, allow ad hoc queries of the 9700 database.9700sql can be used in any of the following ways:1-2wData can be exported to files used by other applications.wData from other applications can be imported into the 9700 System.wExported files can be used for journal printing (fields can be padded, so thatthe printed output is easy to read). If your journal printing needs do notrequire the full journal capabilities provided by the 9700 System, you can use9700sql to print a journal file that includes specific fields and files.w9700sql can be run as part of a PC Autosequence at any interval you define,such as monthly, daily, or even by shift. A spreadsheet program can beautomatically updated to reflect sales or any other activity in the 9700 HMS.This is especially useful for EIS (Executive Information System) applications.From such an application, management is able to view property-wide activity,then react to it immediately by remotely changing items such as menu pricesand labor schedules.

9700sql Building BlocksCreating 9700sql ScriptsCreating 9700sql ScriptsWhen using 9700sql to export or import data, remember the following:wCreate a command file that tells 9700sql what information to access.wMake sure that the 9700 System is ON at the Control Panel.wRun 9700sql from a Windows command line or as part of a PC Autosequence.If used in a PC Autosequence, 9700sql can automatically export or import the datayou desire when reports are reset. Details about PC Autosequences may be foundin the 9700 A to Z Feature Reference Manual.Regardless of which method you choose to run 9700sql, the syntax is as follows:8700sql -f filename -[argument options]where filename1 refers to the full pathname of the command file containing thecommands to be executed, and argument options refers to the arguments used toappend the command syntax (see page 14 for a complete list).The format requirements of the command file are discussed in the next section,Command File Format.For best results, view the SQL log files in Microsoft Notepad, sothat you can format the text (i.e., enable the Word Wrap feature) ifdesired.1. For easy reference, all System filenames are listed alphabetically in Appendix A, and all fieldnames arelisted by file in Appendix B.9700 SQL Manual1-3

9700sql Building BlocksCreating 9700sql ScriptsCommand File FormatThe Command file is an ASCII file that you create using the text editor of yourchoice, such as Windows Notepad. The Command file defines the file beingimported or exported, as well as the format and content of the informationincluded.A Command file must contain the following commands in the order shown:wControl Commands (see page 1-5)wInput/Output Command (see page 1-9)wSQL Commands (see page 1-10)An example of a command file is shown below:Control CommandsInput/Output CommandSQL StatementsMAX FIELDS PER LINE 20MAX LINE LENGTH 64FIELD SEPARATOR ,DATE FORMAT MM/DD/YYOUTPUT TO mi2salesSELECT * FROM mi ttls.2WHERE sls ttl GT 0.00The commands in the file are not case sensitive. (For clarity, commands in thismanual are written in upper case, and file and fieldnames are written in lowercase.)Even though a carriage return does not need to be entered after every commandand all white space characters are skipped, including the carriage returns andwhite spaces will make scripts more readable.Comments can be inserted into the command lines by typing two forward slashes(//) as shown in the example:OUTPUT TO mi2sales//Sales totals from the barAll characters that follow the slashes are ignored by SQL.1-4

9700sql Building BlocksCreating 9700sql ScriptsControl CommandsBefore entering the supported SQL Commands (which define the content of theOutput/Input file) in the Command file, you must enter any Control Commands todefine the format of the information. The format may be specified to meet theneeds of other applications which may then import the data.Control CommandsDescriptionDefaultValueCommentsDATE FORMAT MM-DD-YY Defines the date format asMM-DD-YY, which canbe entered in any order.MM-DD-YYThe delimitercan be aforward slash(/), a dash (-),or nothing.DATE TIME NUMERIC ON, OFF Causes database fields ofthe “TIME” type to behandled as a numericvalue, instead of asseparate fields containing“HH:MM” and “DDMM-YY”, or “HH:MMDD-MM-YY.” Validonly ifOFFThis timeformat isapplied tobothimported andexportedfields.OFFThis timeformat isapplied tobothimported andexportedfields.DATE TIME NUMERICis set OFF.This allows forcomputationalcomparison of time ordate values.DATE TIME TWO FIELDS ON, OFF Causes database fields ofthe “TIME” type to behandled as two separatefields containing“HH:MM” and “DDMM-YY”, instead of onefield containing“HH:MM DD-MM-YY.”Correspondingly,TCTIMES is handled asfour fields.9700 SQL Manual1-5

9700sql Building BlocksCreating 9700sql ScriptsControl CommandsDECIMAL CHAR DefaultValueDescriptionCommentsDefines the decimalcharacter, char . Thiscommand can be used, ifnecessary, to change thedecimal character in aninput file.decimal point(.)The decimaland the fieldseparatormust bedifferentcharacters.DECIMAL PLACES #Defines the number ofdecimal places formonetary data.2The availablerange is 0-3.FIELD SEPARATOR char Determines the characterthat is inserted betweenfields when exportingdata. When inputting, thiscommand defines, for the9700, the field separatorcharacter.The tabcharacter,which can beentered as thestring “TAB”The fieldseparator andthe decimalmust bedifferentcharacters.no limitThiscommandonly appliesto exportedfiles.orDECIMAL CHAR .If a field separator is partof a string that is enclosedin quotes, it is treated asan alphanumericcharacter, and not as afield separator. Forexample, if your fieldseparator is the comma (,)the string “Bond, JamesBond” would be treatedas one field.MAX FIELDS PER LINE(#, NO LIMIT)Defines the maximumnumber of fields on oneline of the output file.If another value waspreviously entered, usethe value NO LIMIT toreset this parameter.1-6

9700sql Building BlocksCreating 9700sql ScriptsControl CommandsMAX LINE LENGTH(#, NO LIMIT)DescriptionDefines the maximumnumber of characters on aline of output.DefaultValueCommentsno limitThiscommanddoes notapply toimporting.Fields are not split, andthere is a minimumrequirement of 40characters.MAX OUTPUT RECORDS(#, NO LIMIT)Defines the maximumnumber of recordsexported.no limitThiscommandapplies onlyto the exportof variablelength files.PAD NUMBERS ON, OFF Determines whether ornot number fields arepadded with spaces whenthe length of a number isless than the maximumfield length.ONPadding isnot relevantwhenimporting afile becauseall spaces areskipped bythe System.ONPadding isnot relevantwhenimportingbecause allspaces areskipped bythe System.Type PAD NUMBERSOFF to turn padding off.If padding is turned ON,the fields are rightjustified.PAD STRINGS ON, OFF Determines whether ornot fields are padded withspaces when the length ofa string is less than themaximum field length.Type PAD STRINGSOFF to turn padding off.If padding is turned on,the fields are leftjustified.9700 SQL Manual1-7

9700sql Building BlocksCreating 9700sql ScriptsControl CommandsTOTALS PERIOD #DescriptionExports totals fromperiods other than thecurrent totals period.Valid entries are 0-9: avalue of 1 equals totalsperiod 1, as defined in theTotals Set file; a value of2 equals totals period 2,and so on.For closed check totals,the period defined mustbe current or P1. For opencheck totals, the perioddefined must be current.TOTALS SET #Supplies an objectnumber in the Totals SetDefinition file and can beused on either a totalsperiod (0 - 9) or on asaved totals set.For closed check totals,the period defined mustbe current or P1. For opencheck totals, the perioddefined must be current.1-8DefaultValue0 (currenttotals period)CommentsThiscommandcannot beused toaccess savedtotals sets(only currentthroughperiod 9).

9700sql Building BlocksCreating 9700sql ScriptsInput/Output CommandsThe Input/Output Commands define the file that is addressed by the SQLCommands and should appear in the Command file before the SQL Commandsand their arguments.These commands can appear more than once if the command parameter needs tobe changed between Select and Update Statements.Input/OutputCommandsINPUT FROM filename DescriptionUsed inside a command file whenimporting data.DefaultValue9700sql.inThe filename refers to the data filebeing imported.OUTPUT TO filename Used inside a command file whenexporting data.9700sql.outThe filename refers to the file inwhich the exported data is to beplaced.The pathname may be specified tomeet the needs of other applicationsthat import the data.9700 SQL Manual1-9

9700sql Building BlocksCreating 9700sql ScriptsSQL CommandsThe following table lists the keywords supported by atorsFilter ClauseJoin OperatorDefinitionGTgt Greater than.LTlt Less than.GEge Greater than or Equal to.LEle Less than or Equal to.EQeq Equal to.NEne Not equal to.CTctContains.1ANDAllows a statement to continue if both oftwo conditions are true.ORAllows a statement to continue if either oftwo conditions is true.()Parentheses are used to define the order ofoperations when two or more logicaloperators are used, for example: (A and B)or C is a different statement than A and (Bor C).WHEREIndicates the start of a filter expressionthat qualifies or limits the Statement, usingBoolean or Arithmetic operators.USINGAllows selection from more than one fileby comparing values in specified records.FROMIndicates the file from which records areread.INTOIndicates the file to which records will bewritten.Prepositions1-10

9700sql Building BlocksCreating 9700sql ScriptsKeywordsDefinitionDELETERemoves records from files.INSERTInserts records into files.SELECTRetrieves records from files.UPDATEChanges records in a file.Statements2 This argument applies only to character fields. If the character string contains spaces,the string must be enclosed in quotes as shown in the following example:WHERE name CT “hot dog”AliasesFilenames may be given aliases within an SQL statement. Aliases may simplifycoding by allowing the user to create shorter names to refer to files.Aliases are required in order to create the qualified fieldnames that are used instatements with parenthetical expressions. An alias can be any alphanumericcharacter string, up to 48 characters.For example, in the following expression, the Major Group Definition file,maj grp def, is assigned the alias D.SELECT * FROM maj grp def DLater in the command file, SQL will recognize the alias DWHERE D.number 2as referring to the Major Group Definition file.Qualified FieldnamesParentheses can be used to alter the order of operations on files, so this type ofsequential orientation is no longer sufficient to link a field with its file.When parentheses are used in a statement, therefore, qualified fieldnames must beused. A qualified fieldname is a fieldname linked to the name of the file or its aliasin the following format: filename . fieldname 9700 SQL Manual1-11

9700sql Building BlocksCreating 9700sql ScriptsTo continue the previous example, the Major Group Definition file includes thefield rpt grp. A qualified fieldname for this field would bemaj grp def.rpt grpfilenamefieldnameThe use of filename aliases can simplify this process. Continuing the previousexample:D.rpt grpalias fieldname1-12

9700sql Building BlocksCreating 9700sql ScriptsData StructuresAny field that contains [n-n]. in its fieldname is a data structure, meaning thatit has multiple subrecords with subordinate fields. When a structure name is usedin importing or exporting, all of its subordinate fields will be included.For example, the System Totals file’s sales profile totals include the structure:sls prof.ordr type[1-4].net sls ttlAs indicated by the numerals in brackets, this structure has four subrecords. If youspecify this structure in a command (see the example below), it will include all ofthe subordinate fields:sls prof.ordr type[1]or you can specify each subrecord and fieldname as shown below:sls prof.ordr type[3].net sls ttlsls prof.ordr type[3].cov cntsls prof.ordr type[4].net sls ttlThe following example is used with structures where several, but not all, elements(such as RVCs or records) are exported together, without a separate command filefor each different element:FIELD SEPARATOR ,//Control Command char TOTALS PERIOD 2//Control Command number PAD NUMBERS OFF//Control CommandOUTPUT TO spttl.sql//Output Command filename SELECT trgrp[99],//Select Statement data structure sls prof.ordr type[4].ntsls ttl,sls prof.ordr type[4].cov cntFROM srv period ttls.3//Preposition filename WHERE number eq 29700 SQL Manual//Filter Clause value Arithmetic//Operator value 1-13

9700sql Building BlocksCreating 9700sql ScriptsArgumentsThe arguments listed in the table below may be appended to the command syntaxin any order.Argument-aUsageThis argument allows the UPDATE command to add recordsfrom a source file to the 9700 database file. This feature isdescribed on page 1-26.This argument may not be used in conjunction with the -iargument described below.-cThis argument changes the destination directory and basefilename for the .errlog file created by a consolidatedtransaction. The System defaults to the following directory:/usr/tmp/sql tr.errlogTo rename the .errlog file on Systems where disk space is tightin the partition containing /usr, the base filename (without anyextensions) must follow the argument as shown in the examplebelow:-c/work/logs/sql trlog-eThis argument echoes commands to the standard output (usuallythe display). In the case of Select operations, the output of theoperation will also be echoed to the display. For example:8700sql -f filename -eThis is recommended for testing your command file. If anyerrors occur, the error messages are written to the end of theoutput file, and the command is terminated. An explanation oferror checking may be found at the end of this chapter.-iThis argument allows SQL to ignore records in an Update filethat do not exist in the database.For example, this feature can be used when maintaining acentral Menu Item Master file for use with several differentsites, each of which uses only a subset of these menu items.When updating names in the Menu Item Definition file at aspecific site, the central Menu Item Master file can be used. The“-i” switch allows SQL to ignore any records in the Menu ItemMaster file for which there is no corresponding record in theMenu Item Definition file.This argument may not be used in conjunction with the -aargument described above.1-14

9700sql Building BlocksCreating 9700sql

The 9700 SQL Manual is your guide to the MICROS 9700sql module. Procedures for exporting data from and importing data into the 9700 database are explained in this manual. The 9700 SQL Manual does not provide guidance for modifying or programming the 9700 System's database or for installing, troubleshooting, or servicing the 9700 System.