Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

SQR User’s Guide Version 4.3.2 1080 Marsh Road, Menlo Park, California 94025 SQ

SQR User’s Guide Version 4.3.2 1080 Marsh Road, Menlo Park, California 94025 SQR User’s Guide, Version 4.3.2 Part Number: 06-M-4302-R01 Copyright © 1998 SQRIBE Technologies All rights reserved. Printed in the USA. This publication pertains to SQR 4.3.2 and to any subsequent release until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of the agreement. LIMITED WARRANTY THE SQR PROGRAM AND LANGUAGE TUTORIAL ARE SOLD "AS IS," WITHOUT WARRANT AS TO THEIR PERFORMANCE, MERCHANTABILITY, OR FITNESS FOR ANY PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE RESULTS AND PERFORMANCE OF THIS PROGRAM IS ASSUMED BY YOU. HOWEVER, TO THE ORIGINAL PURCHASER ONLY, THE PUBLISHER WARRANTS THE MAGNETIC MEDIUM ON WHICH THE PROGRAM IS RECORDED TO BE FREE FROM DEFECTS IN MATERIALS AND FAULTY WORKMANSHIP UNDER NORMAL USE FOR A PERIOD OF NINETY DAYS FROM THE DATE OF PURCHASE. IF DURING THE NINETY-DAY PERIOD THE MEDIUM SHOULD BECOME DEFECTIVE, IT MAY BE RETURNED TO THE PUBLISHER FOR A REPLACEMENT WITHOUT CHARGE, PROVIDED YOU HAVE PREVIOUSLY EXECUTED A SOFTWARE LICENSE AGREEMENT. SQRIBE, SQR, SQR Execute, SQR Print, SQR Viewer, VisualSQRIBE, InSQRIBE, PowerSQRIBE, WebSQRIBE, ReportMart, RM/API, RM/InSQRIBE, RM/Publisher, RM/Script, and Instant HTML are trademarks or registered trademarks of SQRIBE Technologies. All other company and product names used herein may be the trademarks or registered trademarks of their respective companies. SQR User’s Guide iii Contents Introduction.....................................................................................................................ix Audience........................................................................................................................ x How to Use the SQR User’s Guide .............................................................................. x Related Documents......................................................................................................xi Syntax Conventions ....................................................................................................xii Setting up the Sample Database.................................................................................xii If You Need Help .......................................................................................................xiii Part 1—SQR Basics 1 A Simple SQR Program ............................................................................................... 1 Creating and Running an SQR Program..................................................................... 1 SQR Output ................................................................................................................... 2 2 Headings and Footings................................................................................................. 5 3 Selecting Data from the Database .............................................................................. 9 The SQR SELECT Statement ...................................................................................... 11 Syntax of the SELECT Statement ..................................................................... 11 Positioning Data ................................................................................................ 12 4 Column Variables....................................................................................................... 15 5 Break Logic .................................................................................................................. 19 Using ON-BREAK....................................................................................................... 21 Skipping Lines Between Groups................................................................................ 22 Arranging Multiple Break Columns.......................................................................... 23 Break Processing with Enhancements....................................................................... 25 Handling Page Breaks....................................................................................... 29 Printing the Date................................................................................................ 29 Obtaining Totals ................................................................................................ 30 Hyphens and Underscores ............................................................................... 31 Setting Break Procedures with BEFORE and AFTER .............................................. 31 Understanding the Order of Events................................................................. 32 Controlling Page Breaks with Multiple ON-BREAK Columns ............................... 38 Saving a Value When a Break Occurs ....................................................................... 38 Using ON-BREAK on a Hidden Column.................................................................. 39 Restrictions and Limitations of ON-BREAK............................................................. 41 6 The SETUP Section..................................................................................................... 45 Using DECLARE-LAYOUT ....................................................................................... 46 Part 2—SQR Reports 7 Master/Detail Reports ................................................................................................ 49 Contents SQR 4.3 iv SQR User’s Guide Correlating Subqueries ...............................................................................................51 8 Cross-Tabular Reports................................................................................................55 Arrays...........................................................................................................................56 Creating the Array ......................................................................................................59 Grouping by Category ................................................................................................60 Using Multiple Arrays ................................................................................................63 9 Printing Mailing Labels .............................................................................................69 Defining Columns and Rows .....................................................................................70 Running the Program..................................................................................................72 10 Creating Form Letters ...............................................................................................75 Laying Out the Letter..................................................................................................75 11 Exporting Data to Other Applications ....................................................................79 Part 3—Fonts and Graphics 12 Using Graphics..........................................................................................................83 Adding Graphics.........................................................................................................84 Sharing Images among Reports..................................................................................88 Printing Bar Codes ......................................................................................................92 13 Business Charts .........................................................................................................95 Creating a Chart ..........................................................................................................95 Defining the Chart.......................................................................................................99 Printing the Chart...................................................................................................... 100 Running the Program................................................................................................ 100 Passing Data to the Chart ......................................................................................... 102 14 Changing Fonts........................................................................................................ 105 Positioning Text......................................................................................................... 105 Using WRAP.............................................................................................................. 108 15 Writing Printer-Independent Reports .................................................................. 111 Guidelines for Printer-Independent Reports........................................................... 111 Part 4—Advanced SQR Programming 16 Dynamic SQL and Error Checking ....................................................................... 115 Using Variables in SQL............................................................................................. 115 Dynamic SQL............................................................................................................. 117 SQL Error Checking.................................................................................................. 119 SQL and Substitution Variables ............................................................................... 120 17 Procedures, Argument Passing, and Local Variables......................................... 123 Procedures ................................................................................................................. 123 Local Variables................................................................................................. 124 SQR 4.3 Contents SQR User’s Guide v Argument Passing............................................................................................124 18 Multiple Reports......................................................................................................131 19 Using DML and DDL SQL Statements...............................................................137 Using BEGIN-SQL.....................................................................................................137 20 Working with Dates ................................................................................................141 Date Arithmetic .........................................................................................................141 Date Formats..............................................................................................................144 String to Date Conversions .............................................................................145 Date to String Conversions .............................................................................145 Using Dates with the INPUT Command.................................................................146 Date Edit Masks.........................................................................................................146 Declaring Date Variables ..........................................................................................148 21 National Language Support ...................................................................................151 Locales........................................................................................................................151 Available Locales .............................................................................................151 The Default Locale.....................................................................................................152 Switching Locales ......................................................................................................153 Modifying Locale Preferences ..................................................................................154 Keywords—NUMBER, MONEY, and DATE .........................................................154 22 Interoperability........................................................................................................157 Calling SQR from Another Application...................................................................158 Using the SQR API ....................................................................................................158 Extending SQR—UFUNC.C .....................................................................................161 Adding a User Function............................................................................................162 Step 1. Add Function Prototype.....................................................................162 Step 2. Add Entry to USERFUNCS Table .....................................................163 Step 3. Add Implementation Code ................................................................164 Step 4. Relink SQR...........................................................................................165 ufunc on Windows 95 & NT.....................................................................................165 Implementing New User Functions on Windows 95 & NT ...................................166 23 Testing and Debugging..........................................................................................167 Using the Test Feature...............................................................................................167 Using the #DEBUG Command.................................................................................168 Using Compiler Directives for Debugging..............................................................169 Common Programming Errors.................................................................................169 24 Performance and Tuning........................................................................................171 SQR Performance and SQL Statements ...................................................................171 Simplifying a Complex SELECT...............................................................................172 Using LOAD-LOOKUP to Simplify Joins................................................................172 Contents SQR 4.3 vi SQR User’s Guide Improving SQL Performance with Dynamic SQL.................................................. 174 Examining SQL Cursor Status.................................................................................. 175 Avoiding Temporary Database Tables.................................................................... 176 Using and Sorting Arrays ............................................................................... 176 Using and Sorting Flat Files............................................................................ 182 Creating Multiple Reports in One Pass ................................................................... 185 Tuning SQR Numerics.............................................................................................. 185 Compiling SQR Programs and Using SQR Execute ............................................... 186 Processing Limits....................................................................................................... 186 Buffering Fetched Rows............................................................................................ 187 Executing Programs on the Database Server .......................................................... 187 Part 5—Running and Printing 25 Compiling Programs and Using SQR Execute ................................................... 189 26 Printing Issues......................................................................................................... 193 27 Using the SQR Command Line............................................................................. 199 Command-Line Flags................................................................................................ 201 Specifying Command-Line Arguments................................................................... 201 How SQR Retrieves the Arguments............................................................... 201 Specifying Arguments and Argument Files .................................................. 202 Using an Argument File .................................................................................. 202 Passing Command-Line Arguments—Other Approaches........................... 203 Reserved Characters........................................................................................ 204 Creating an Argument File from a Report..................................................... 204 Using Batch Mode..................................................................................................... 205 VAX/VMS........................................................................................................ 205 UNIX, Windows 95, and Windows NT ......................................................... 205 28 Working with HTML.............................................................................................. 207 SQR Capabilities Available with HTML ................................................................. 207 Producing HTML Output ............................................................................... 208 HTML Output.................................................................................................. 208 Using -PRINTER:EH........................................................................................ 209 Setting HTML Attributes under -PRINTER:EH............................................ 211 Using -PRINTER:HT........................................................................................ 214 "Bursting" and Demand Paging...................................................................... 215 Setting Attributes with HTML Procedures.................................................... 218 Using Additional HTML Procedures............................................................. 219 Output File Types ............................................................................................ 219 Testing .............................................................................................................. 220 Using HTML Procedures in an SQR Program........................................................ 220 How to Use HTML Procedures...................................................................... 221 SQR 4.3 Contents SQR User’s Guide vii Positioning Objects...........................................................................................221 Table Procedures..............................................................................................223 Headings...........................................................................................................225 Highlighting......................................................................................................225 Hypertext Links................................................................................................226 Images...............................................................................................................227 Lists ...................................................................................................................227 Paragraph Formatting......................................................................................228 User-Defined HTML........................................................................................229 Modifying an Existing SQR Program.......................................................................229 Publishing the Report................................................................................................232 Viewing the Published Report ........................................................................233 Publishing Using an Automated Process.......................................................233 Publishing Using a CGI Script ........................................................................234 Creating the Fill-Out Form..............................................................................235 Creating the CGI Script ...................................................................................236 Passing Arguments to the SQR Program .......................................................238 29 Tables of Contents...................................................................................................241 Index ..............................................................................................................................249 Sample Programs ex1a.sqr—A Simple Program................................................................................................1 ex2a.sqr—Printing a Heading and Footing............................................................................6 ex3a.sqr—Selecting Data from the Database.........................................................................9 ex5a.sqr—A Simple Tabular Report....................................................................................19 ex5b.sqr—A Report with Break Logic.................................................................................21 ex5c.sqr—Program with Multiple ON-BREAK Columns....................................................24 ex5d.sqr—Program with Break Logic and Enhancements ...................................................25 ex5e.sqr—Order of Events in Break Processing...................................................................33 ex5f.sqr—Using Break Logic on a Hidden Column .............................................................39 ex7a.sqr—Master/Detail Report ..........................................................................................50 ex8a.sqr—Cross-Tabular Report .........................................................................................57 ex8b.sqr—Cross-Tabular Report with Grouping by Category ..............................................61 ex8c.sqr—Cross-Tabular Report with Grouping by Two Categories ....................................63 ex9a.sqr—Mailing Labels Program.....................................................................................69 ex9b.sqr—Mailing Labels Program (Alternate Layout) .......................................................73 ex10a.sqr—Form Letter Program........................................................................................76 ex11a.sqr—Program to Create Tab-Delimited File..............................................................79 ex12a.sqr—Tabular Report without Graphics......................................................................83 ex12b.sqr—Tabular Report with Graphics...........................................................................85 acme.inc...............................................................................................................88 ex12c.sqr—Form Letter with Graphics................................................................................89 Contents SQR 4.3 viii SQR User’s Guide ex13a.sqr—Business Chart .................................................................................................96 ex14a.sqr—Report with Multiple Fonts............................................................................. 107 ex16a.sqr—Using Variables in SQL ................................................................................. 116 ex16b.sqr—Dynamic SQL ................................................................................................ 118 ex16c.sqr—SQL and Substitution Variables...................................................................... 121 spell.inc—spell_number procedure ................................................................................... 125 ex17a.sqr—Check-Writing Program................................................................................. 128 ex18a.sqr—Multiple Report Program................................................................................ 131 ex19a.sqr—External Files and Database Inserts................................................................ 138 ex24a.sqr—QuickSort Procedure ...................................................................................... 178 ex24b.sqr—Sorting a Flat File .......................................................................................... 183 ex28a.sqr—Simple Tabular Program with HTML Procedures........................................... 223 ex28b.sqr—Program ex12a.sqr Modified with HTML Procedures..................................... 230 Tables Table 1. Syntax Conventions..............................................................................................xii Table 2. Commands Available in the SETUP Section.........................................................45 Table 3. Sample Date Edit Masks .................................................................................... 146 Table 4. The SQR API..................................................................................................... 159 Table 5. Error Values Returned by the SQR API.............................................................. 161 Table 6. Compile-Time Commands and Run-Time Equivalents....................................... 191 Table 7. Command-Line Flags and Output Types............................................................. 193 Table 8. Print Commands by Operating System ............................................................... 196 Table 9. SQR Command-Line Arguments........................................................................ 199 SQR User’s Guide ix Introduction Welcome to the SQR User’s Guide. This guide is designed to help you learn SQR, a specialized language for database processing and reporting. By working through code examples, you will learn how to write SQR programs that select data from a database and present it in a report. This User’s Guide is filled with real examples and sample programs, and we encourage you to copy code from it. It will help you create the kinds of SQR programs that are important to your organization. The first three parts of the User’s Guide teach basic uses of SQR. You will learn how to: • Create a variety of reports, such as tabular, cross-tabular and master/detail reports. • Produce mailing labels, form letters, and envelopes. • Enhance your reports with typeset-quality fonts and graphics. • Produce graphs and charts that will help you present data and trends in visual terms. The next two parts of the User’s Guide describe the advanced features and uses of SQR. You will learn how to: • Create HTML output and publish your reports on the Internet or an Intranet or Extranet. • Create reports uploads/Litterature/ sqr-guide.pdf