anyQuery User's Manual (Version 2.5)
Contents
1. Allow Column Grouping DBO CUSTOMERS 1 f Test Query Be ns mern eng Show Record Navigation SELECT Card View PROM ina A Fit All Columns WHERE Country Show Row Headers ale i Country Show Gridlines i Ez CustomerID Text Ci CompanyName Text DI ContactName Text Address City Report Style alCode Country Phone O ContactTitle Text Print Settings x ar Av del Libertado Buenos Aires Argentina 1 123 L Address Text OE Print Preview ie _ E city Text Ing Gustavo Mo Buenos Aires Argentina 1 155 8 E Region Te Cerrito 333 Buenos Aires Export As el Argentina 1 135 C PostalCode Text Fass A me pam DI Phone Text Adenauerallee 900 Stuttgart 170563 Germany 0711 02 I Fax Text Luisenstr 48 Minster 44087 Germany 0251 02 4 4 Abb 26 Result formatting Sorting By clicking on the Allow Column Sorting menu you can enable and disable the column sorting function When the function is enabled you can sort a column by clicking on the column header Every click on the column header toggles between ascending and descending order Grouping By clicking on the Allow Column Grouping menu you can enable and disable the column grouping function When the function is enabled you can group the data by one or more columns Drag a column to the area above the grid The grid will show the group header as shaded rows2. anyQuery and its functions are described in detail In Chapter Working with anyQuery all functionalities of anyQuery are described in the order of common workflow In Chapter SQL Links you will get some useful links to SQL and database resources 3 Changes and additional Functions 3 1 New or enhanced Functions simple report writer session logging in one window and gridview transaction processing search and replace in the SQL editor working with multiple scripts comment and uncomment row increase reduce line indent sorting grouping group footers a clear user interface pick lists enhanced DDL commands with database specific type list new database explorer new install routine enhanced database support MySQL 3 2 Changes you should know Version 2 3 compared to previous versions normal one line comments starts with this is a comment the old style comments with can span more than one line anyQuery User s manual 5 06 2005 PLO NWARE 4 Functions and System Requirements 4 1 Functionality access to different databases by udl files english and german version syntax coloring creation of SQL queries and scripts with SQL builder command line parameters for batch processing database explorer simple report writer session logging transaction processing search and replace in SQL editor working with multiple scripts sorting grouping group footers a clear user interface pick list
3. forget to save the whole report to a file again otherwise your changes will be lost when you close the session or uncheck the Hold layout checkbox From the Resultgrid menu select Print Preview to see your report as it will appear printed w Print Preview Resultgrid a GG OD Page Setup 8 Print Close Category 1 43 Ipoh Coffee O o njoe Steeleye Stout avg Price 37 979166666666 sumo59 Category 2 Within the print preview click on the printer symbol to print the whole report at the moment it is not possible to print ranges or single pages anyQuery User s manual 36 06 2005 PLA NWGRE FOUI 7 5 Exporting Database Information anyQuery has 6 functions to export Information about the actual database to text files The resulting text files are tab stop delimited and will be written to the anyQuery program directory These functions are pooled in the DBInfo menu The following table lists the menu items the name of the export file and gives a short description of each function menu item export file description All Tables Tables txt provides a list of all tables in the current database All Fields Fields txt field names datatypes and properties of all fields in all tables All Views provides a list of all views in the current database All View Fields ViewFields txt field names datatypes and properties of all fields in all views All Index Fields Indexes txt index type and index fields of all i
4. udl file see Modifying a Database Connection Open Database Connection connects to database via the predefined udl file see Opening a Database Connection Open Recent Connection opens a Submenu with the last used connections See Opening a Database Connection For detailed description and examples of the following items see Working with Queries and Scripts New SQL Script creates an empty SQL worksheet Load SQL Script loads a saved query definition qry to the SQL editor Close SQL Script closes the active SQL worksheet SQL Script Save saves the active query with the assigned name SQL Script Save As lets you assign a name and location where to save it to the query Open Recent SQL Script opens a Submenu with the last used scripts Settings let you modify some path and editing preferences see Settings Dialogue Exit quits an anyQuery session anyQuery User s manual ETE 06 2005 PLA NWG RE fi 6 2 1 2 Edit SQL Menu Ucase Delete Tabs Format SQL 7 Delete Querytext Comment Row Alt Z UnComment Row Alt U Increase indent Alt Right Reduce indent Alt Left Autosyntax Coloring L Fig 6 Edit SQL vai Within the Edit SQL Menu you will see the following menu items Cut text in the SQL Editor Copy text in the SQL Editor Paste text in the SQL Editor Ucase converts the content of the SQL editor to upper case Delete Tabs deletes all tabulators from the content of the SQL editor Format SQL formats the con
5. 06 2005 12 21 he Connection H Connection aise Un Database E Dokumente und Einstellungen larsEigene Daterensnorthwind2002 mdb A With UserAdmir Con 03 06 2005 12 22 03 06 2005 12 22 i Connection 0 ff Connection established i ff On Database Vary QuenDokumente sy ertneb Produk tubersichtquenes WIND OMDB i With UserAdmin SE 03 06 2005 12 52 03 06 200512 52 SELECT a ar 1800 00 00 01 28 SELECT FROM Customers j O u er n E SE 03 06 2005 12 53 03 06 2005 1253 SELECT 1800 00 00 01 093 EI TI T a jil F Admin Y anyQuery Dokumente Wertrieb Produktiibersicht queries Fig 25 Session Tab The session tab shows a list of processed queries for the current connection with execution time and the number of rows and columns in the result set It also logs any errors Furthermore you can copy statements from here to the SQL Editor The session log will be saved in a textfile named anyQuerySession txt after you quit the program anyQuery User s manual 220 06 2005 PLA NWG RE mye 6 2 13 Helpful result formatting by popup menu By clicking the right mouse button at the result area a popup men appears There you can quick set some resultgrid formats see also Chapter Resultgrid Menu BY BY A _ fea ES e rn Je Tables Views Columns E DBO PRODUCTS FE DBO EMPLOYEES 3 Session New SQL 01 Show Group Footer
6. 2 Filo Mix 7 00 oo 71 Flotemysost 21 50 26 4 33 Geitost 2 0 E 117 2 15 Genen Shouyu 15 50 39 a Now you can click on the price to find out the smallest and the highest price The Allow Column Grouping menu and Show Group Footer menu allow you to group the resultset by the values of a specific column and display a footer for calculations at the bottom of each group To do so choose the the Allow Column Grouping menu and a box in the upper region of the grid appears Click for example on the CategoryID drag it to the box and drop it there The resultset will be grouped by the CategoryID and dark bars will highlight the groups The group header has a small button with or sign to expand or collapse the according group A CategoryID _ CategoryID ProductID Product lame UnitPrice UnitsInStock 1 67 Laughing Lumber 14 00 52 i 70 Outback Lager 15 00 s 0 1 75 Rh nbr u Kloster 7 75 125 1 34 Sasquatch Ale 14 00 111 1 35 Steeleye Stout 18 00 20 3 45 Chocolade 12 75 15 4 26 Gumbar Gummib 31 23 15 3 49 Maxilaku 20 00 10 T TE Ahhh ia Rh hla 18 nN E TA k Now choose the Show Group Footer menu and dark group footers will be displayed for each group For the group footer you can choose from a limited list of aggregate functions anyQuery User s manual 06 2005 PLANWGRE function description Displays the count of records in a group i the sum of values ina gro
7. Allow Column Grouping Show Group Footer Show Record Navigation Card View Fit All Columns Show Row Headers 28 Show Gridlines Report Style Print Settings Print Preview Export As Fig 8 Resultgrid Menu Within the Resultgrid Menu you can see the following menu items Layout settings customizes the resultgrid layout for the Screen and Printerview Report Allow Column Sorting enables sorting of data in the result grid see Building a report Allow Column Grouping enables grouping of data in the result grid see Building a report Show Group Footer displays a group footer in the result grid at the end of each data group see Building a report Show Record navigation displays buttons for record navigation at the bottom of the result grid Record 14 1 of 14 FURIE wu gt Fig 9 record navigation Card View switches the grid to a card file view Fit All Columns resizes all columns to fit best to the width of the resultset data see Building a report Show Row Headers shows a record selector for each row when enabled Show Gridlines shows grid lines when enabled Report Style shows record selectors and column headers in a report style when enabled Print Settings opens a dialogue to configure print settings see Building a report Print preview generates a print preview of the result See Building a report Export as stores the query result into a text XML or X HTML file see Editing a Query PLA
8. Functions of anyQuery You will find some of the more often used functions on the Toolbar Connect New Open Elsave BAiSave As A Delete IpExecute Stop Fig 12 Toolbar 6 2 2 1 Function Open The Open button will load a stored query into the SQL editor There are several extensions for query files except reports they are all interpreted the same way A report consists of two files the query qry and a report definition bin 6 2 2 2 Function Connect The Connect button opens a new database connection using a ud File 6 2 2 3 Function New The New button opens anew SQL Editor Tab window 6 2 2 4 Function Save The Save button will store all changes and modifications of your query 6 2 2 5 Function SaveAs The SaveAs button stores the displayed query under a different name in the selected directory 6 2 2 6 Function Delete Query The Delete Query button will drop the currently displayed content of the SQL editor without saving any changes 6 2 2 7 Function Execute The Execute button runs the Query Press function key F2 as a shortcut for running queries 6 2 2 8 Function Stop The Stop button interrupts the currently running query Function key F3 serves as a shortcut anyQuery User s manual ETE 06 2005 6 2 3 Table and View Area Piter 00 L System Objects EJ DBO CUSTOMERS Tables Views Columns EJ DEO PRODUCTS E DBO EMPLOYEES DBO CUSTOMERS Fig 13 table and view ar
9. Helpful result formatting by popup menu 6 2 14 Statusbar 6 2 15 Settings Dialog 6 2 15 1 EditTab 6 2 15 2 Language Tab 7 Working with anyQuery 7 1 Preface 7 2 Database connection 7 2 1 Creating a Database connection 7 2 2 Modifying a Database Connection 7 2 3 Opening a Database Connection 7 2 4 Creating a Connection for NWIND mdb 7 3 Working with Queries and Scripts 7 3 1 Editing a Query 7 4 Building a report 7 5 Exporting Database Information SQL Links 8 1 Links for Introduction Help and Database Provider anyQuery User s manual 06 2005 15 15 16 17 17 17 18 19 19 19 20 20 21 22 23 24 24 25 25 26 26 26 26 2 2 2 2 29 31 3 38 38 PLA NWGRE mye 1 Introduction 1 1 Versions of the document This is the version 2 5 of the anyQuery manual The screenshots are updated to the current version 2 5 and the new functionality description are added In the version 2 3 of the manual the screenshots are taken from the program version 2 2 0 They may be different from higher versions The functionality has been changed or enhanced in some places The second version of the manual for anyQuery it s based on version 2 0 10 Quite a lot of new functions have been integrated into anyQuery so there was the need to completely rewrite this manual Take a look at Changes and additional Functions for an overview of new functions and changes to prior versions 1 2 Glossary Grid dialog element
10. Iye UaIy SELECT CustomerID ddress ContactName Fax Phone FROM CUSTOMERS WHERE City London AND CustomerID NORTS will be formatted as SELECT CustomerID Address ContactName Fax Phone FROM CUSTOMERS WHERE City London AND CustomerID NORTS Keywords and field lists will be made up and tab stops will be inserted To use your query build with anyQuery in any program code it is often useful to convert it to upper case and replace the tab stops with spaces You can achieve this with the Ucase function and Delete Tabs function trom the Edit menu You can indent blocks of text yourself using the Increase Indent function and Reduce Indent function from the Edit menu You can also use the shortcuts ALT RightArrow or AL T LeftArrow To do so mark the region of text and click on the menu item or use the shortcut and the indent of the whole text block will be increased or reduced To change the font style of the SQL Editor open the Settings dialogue from the File menu and switch to the Edit tab Now you are able to change the font and the font size Keep in mind that a TrueType font will mess up the formatting of the SQL Editor 7 4 Building a report For this paragraph we will use the following statement as an example how to sort and group the result and build a report SELECT CategorylID ProductIb FroductName VUnitfrice VUnitsInstocE FROM PRODUCTS When you execute this query you
11. NW RE 6 2 1 5 DBinfo Menu All Tables All Fields All Views All View Fields All Index Fields All Stored Proc Parameter All Recordcount Fig 10 DBInfo Menu Within the DBinfo Menu you will see the following menu items All Tables creates a text file of all table names of the active database All Fields creates a text file of all field names of the active database All Views creates a text file of all view names of the active database All View Fields creates a text file of all view field names of the active database All Index Fields creates a text file of all index fields names of the active database All Recordcount creates a text file with the record count of all tables of the active database The text files are created in the anyQuery directory These files can also be imported into MS Word or MS Excel For a detailed description of this items see Exporting Database Information 6 2 1 6 Menu Help Contents Fi Help Index About anyQuery Fig 11 Menu In this menu you will find the About dialog with some version information and a link to our web site www planware com Please feel free to contact us and tell us your suggestions about anyQuery In an unregistered version of anyQuery you will also find a button to register this software Further more you will find the online help documents in this menu You can also get there by pressing F1 anyQuery User s manual 140 06 2005 PLA NWARE 6 2 2
12. anyQuery User s Manual Version 2 5 ig r N OR by PLANWARE Beratung amp Software GmbH Gollierstr 70 D 80339 M nchen Germany Munich 06 2005 PLA NWG RE Contents 1 Introduction 1 1 Versions of the document 1 2 Glossary 2 Purpose and Overview 2 1 Purpose of anyQuery 2 2 Overview of this document 3 Changes and additional Functions 3 1 New or enhanced Functions 3 2 Changes you should know 4 Functions and System Requirements 4 1 Functionality 4 2 System Requirements 4 3 Restrictions 5 Registering anyQuery 5 1 How to register 6 User Interface 6 1 anyQuery and Batch Processing 6 2 Main Window and Menu Bar 6 2 1 Menubar 6 2 1 1 File Menu 6 2 1 2 Edit SQL Menu 6 2 1 3 Query Menu 6 2 1 4 Resultgrid Menu 6 2 1 5 DBInfo Menu 6 2 1 6 Menu 6 2 2 Functions of anyQuery 6 2 2 1 Function Open 6 2 2 2 Function Connect 6 2 2 3 Function New 6 2 2 4 Function Save 6 2 2 5 Function SaveAs 6 2 2 6 Function Delete Query anyQuery User s manual 06 2005 O1 O1 OOO N 10 10 11 12 13 14 14 15 15 15 15 15 15 15 PLGA NWARE 6 2 2 7 Function Execute 6 2 2 8 Function Stop 6 2 3 Table and View Area 6 2 4 Field Area 6 2 5 SQL Commandbar Tab 6 2 6 Find Replace Tab 6 2 7 Query Options Tab 6 2 8 DDL Commandbar Tab 6 2 9 Parameter Commandbar Tab 6 2 9 1 Create new parameter 6 2 10 SQL Editor 6 2 11 Result Area 6 2 12 Layout settings 6 2 12 1 Session Tab 6 2 13
13. ation like EXCEL anyQuery User s manual 29 06 2005 PLA NWARE mye e export it as a text file with a predefined field delimiter e export it as XML file this is a non standard XML format maybe Microsoft will update their ADO library sometime To print a report you only have to choose Print Preview from the Resultgrid menu there you have some options to setup the page and print everything anyQuery includes also a small report writer which will be discussed later Drag and Drop is simple only select the rows from the record set you want to export and drag them hold the left mouse button to your preferred application Notepad doesn t work To export the whole record set to a text file click on the Export As Text File item in the Resultgrid menu choose a filename and click the Open button When the export is finished you will get a message box The standard field delimiter for text export is a tab stop but you can configure it in the anyQuery ini file in the anyQuery program directory The keyword is FildDelimiter if it is not present the tab stop will be used To define another delimiter only add modify the FldDelimiter entry in the Settings section of the ini file The value of FldDelimiter is the ASCII code of the character If you for example want to change the delimiter to a semicolon you should have a line like FldDelimiter 59 in your anyQuery ini file Changes to the ini file will only take effect after the next star
14. e 6 1 anyQuery and Batch Processing For batch processing anyQuery also can be invoked from the command line For this you can use the following parameters database connection INI lt path to udl file gt user USR lt username gt password PWD lt password gt query QRY lt path to qry file gt run immediately RUN anyQuery User s manual 8 06 2005 gt PLO NWARE Berotu ng Sothy wore CS mbH 6 2 Main Window and Menu Bar anyQuery 2 5 02 f NewSQL_01 ne Br Xf Customer s in UK and USA SELECT FROM Customers WHERE Country IN UK USA Insert Fieldlist gt _ Long Names O Address A city E CompanyName C ContactName ra a er eu en 12 Orchestra Terr C Region Fig 3 anyQuery User Interface anyQuery User s manual 9 06 2005 PLO NWG RE 6 2 1 Menubar File EditSQL Query Resultgrid DBInfo Fig 4 Menubar 6 2 1 1 File Menu New Database Connection Edit Database Connection FA Open Database Connection Open Recent Connection New SQL Script Alt y Load SOL Script F W Close SQL Script save SOL Script save SOL As Open Recent SOL Script Settings Exit At Q Loh lv ame Fig 5 File Menu Within the File Menu you will see the following menu items New Database Connection for creating a new database connection see Creating a Database connection Edit Database Connection for editing an existing
15. e found The Match Case option does a case sensitive search The Start button allows you to start the search from the beginning of the script The Next button starts the search from the current cursor position The Replace Button opens another window where you can input the search replace text ci anyQuery Find Replace Find What Replace with Search Options C Find Whole Word Only Match Case Replace Al 6 2 7 Query Options Tab Sg SOL Commands Find Replace Query Options ODL Parameter annunnnnunnnnnnnunnnunnnnnuunnnnne Ch Timeout Iminl Begin Transaction E Max Records 1 000 0 All ze BEE le ee ee Fig 19 query options All options mentioned here are dependent on the database or database driver used so they might not work at all or function differently when applied in your database environment The query options tab allows you to set some useful options for all queries and handle database transactions The Timeout property sets the maximum execution time for a query if the query exceeds this limit it will be cancelled The Max Records property sets the maximum number of records The number of records that will be retrieved is always less or equal to this value For displaying the max number of records of a select query put in a 0 into the Field and leave it by pressing TAB or ENTER The three transaction buttons gives you the possibility to group several queries to one transac
16. e this SELECT CustomerID Address ContactName Fax Phone FROM CUSTOMERS Don t think about the brackets this is access style and will be omitted when you connect to a real SQL database You can also use the Insert Fieldlist button to insert all the selected fields but this will only insert the fields not the key words Now you can extend your query a little bit we want to add a WHERE part Position the cursor behind the word customers in the editor and click on the WHERE button Double click on the field name City and on the button Now type the condition London You will get this statement SELECT CustomerID Address ContactName Fax Phone FROM CUSTOMERS WHERE City London After you have executed it we will see how to perform an update on one ofthe records Open a new query by pressing ALT N you will get an empty worksheet Select the record where the contact name is Ann Devon and from the Query menu choose Build Update and the SQL Editor will show the following statement UPDATE CUSTOMERS oR AA WHERE CUSTOMERID U EASTS They only thing you have to do is to fill in the fields and values you want to update Similar to this is the function Build Where but you will only get the where statement from above Ok now we switch back to our first query and try to export our result There are 4 possibilities to do so e print a report e use drag and drop to export it to another OLE aware applic
17. ea In the table and view area you will find several elements to browse filter and select tables and views from the set of available objects in your database The second element from the top is a combo box from which you can pick a table the table will immediately be shown in the list box below The filter box on top allows you to filter a subset of all tables or views by a portion of their names You can e g filter a certain database owner After changing the filter you have to click the Refresh button to reload the combo box E System Objects Fig 14 System Objects By choosing the System Objects option system tables are additionally loaded to the tables views combo box Tables Views E DBO PRODUCTS E DBO EMPLOYEES DBO CUSTOMERS Fig 15 table view list A double click on one of the listed tables views will insert the table view name into the SQL editor right to the current cursor position anyQuery User s manual ECE 06 2005 3 PLO NW RE 6 2 4 Field Area Insert Fieldist gt _ Long Names Indexname ir CustomerID Text 5 Hr 3 PK_Customers E CompanyName Text Me 2 CompanyName AZ O Contactame Text Ye A3 C ContactTitle Text es A4 C Address Text es AS A city Text 5 Yes 1 City AG E Region Text 15 tes 5 Region Ar C PostalCode Text 4 PostalCode AB a LTE a EEE cam DI Phone Text C Fax Text 24 Yes B1 Fig 16 field area Right above you can see the field list where all fields of a se
18. ically at startup anyQuery User s manual 24 06 2005 nono AU roy 6 2 15 1 EditTab anyQuery Settings aths Edit Language Edit Font Courier New 9 75 ae Fig 29 Edit Tab Within the edit tab you can make some font settings for the SQL editor 6 2 15 2 Language Tab anyQuery Settings Edit 7 Language German English To change the language click on Save Settings and restart the Application save Settings Abb 30 Language Tab Within the language tab you can select the User Interface language for anyQuery At the moment you can choose between German and English To activate the language selection save the settings and restart the program anyQuery User s manual 235 06 2005 PLEINWARE 7 Working with anyQuery 7 1 Preface This chapter will explain how to manage some basic tasks with anyQuery To do so we will use a sample database of Microsoft Access called NORDWIND mdb or NWIND mdb It is not absolutely necessary to have it to follow these examples you can use a different sample database as you like 7 2 Database connection Database connections are stored as udl files so you only need to setup them once To organize your connections you can set a default path in the settings dialogue Select a path under Standard UDL Path and your ud files will be opened from and saved into this folder 7 2 1 Creating a Database connection To create a data base connectio
19. ions Queries are stored as plain text you can edit them within every standard editor or execute them with any standard SQL processor We will use the database connection for the NWIND mdb we have created before for our next steps Choose Open Database Connection from the File menu and select the corresponding file After a few seconds you will see the connection information in the left corner ofthe Status Bar anyQuery User s manual 28 06 2005 PLA NWGRE mye 7 3 1 Editing a Query To compose your queries you can simply type your statements into the SQL Editor Much easier it is to use some of the facilities of anyQuery like the tables list and fields list and the SQL Command Bar Now please select the customers table from the tables combobox it will immediately appear in the tables list When you click on the customers table entry you will see the field names and extended information in the fields list Now select the customers table and click on the SELECT FROM button and you will get a complete SQL like this SELECT ul FROM CUSTOMERS Click on the Execute button to execute it the result will be displayed in the Result Grid below If you think that this is not a useful query ok delete it by clicking on the Delete button Now select some of the fields from the fields list hold down the CTRL or SHIFT key to select more than one field after that click on the SELECT lt f gt FROM button and you will get a statement lik
20. lected table are listed anyQuery will display the fieldname the datatype the field size a statement for null ability and some information about the index A double click on one of the listed fields will insert the field name into the SQL Editor right to the current cursor position You can select more than one field and insert this list of fields by clicking on Insert Fieldlist button By choosing the Long Names option the owner and table names are prefixed to the field name when inserted to the SQL editor The Indexname column gives you some information about which fields belong to which index indexname and the number shows you in which order they appear in the database 6 2 5 SQL Commandbar Tab SOL Commands Find Replace Query Options DDL Parameter SELEGT UPDATE INSERT gus ORDER GROUP WEE We Le J AC Fig 17 SQL commandbar The SQL commandbar with the SQL keywords and the operators will simplify the creation of a query and will insert simultaneously neat paragraphs 6 2 6 Find Replace Tab AEM Agua a SQL Commands Find Replace Query Options DDL Parameter Find i in SE Whole Word Match Case Fig 18 find dialog The find dialog tab is useful for long scripts to search for words or substrings anyQuery User s manual 06 2005 PLA NWG RE SB The Whole Word option finds only phrase which matches on the words boundary substrings will not b
21. lit Rail Beer Ale Art Braunschweiger Sales Manager P O Box 565 Lande Fig 23 Result Tab The result tab shows a grid to display the results of your queries see Fig 3 anyQuery User Interface You can sort the result see Sort Menu and group the view see Group Menu For processing in other tools like MS Excel transfer a part or the entire result into a MS Excel sheet via drag amp drop anyQuery User s manual 20 06 2005 PLA NWARE Bergtun w amp Sotware GmbH 6 2 12 Layout settings _ anyQuery 2 2 04 New SQL 013 Sele Hold layout Row Height Pixel C PostalCode O Country C Phone O Fax 4 SEU M Elapsed time 00 00 00 77 F 14 Records 11 5 C 44 www planware com Fig 24 Layout Tab The layout window allows you to create simple printable reports There are several functions to align and format values even some simple aggregate functions like summary are supported anyQuery Users manual 06 2005 sche dele ry 6 2 12 1 Session Tab N Seles file Edt5OL Query Resultgrid DBInfo 2 E Connect Diem Gar Open A A ff The Query delivered 11 Columns and 13 Rows f Max Records set to 1 000 ff 00 00 01 26 ff Query OK f f 03 06 2005 13 46 44 execution start SELECT Customers Country IN URK USA ff The Query delivered 11 Columns and 20 Rows Max Records set to 1 000 gt ff 00700700 90 ff Query OK 03 06 2005 12 21 02
22. n avg Price for UnitPrice sum for UnitsInStock For the changes to take effect please click the Save button Also check the Hold layout checkbox this will hold the layout even if you execute the query again If you leave the Hold layout checkbox unchecked the Result Grid will be initialized with default values and your layout will not be saved be gone When closing the layout window you will see the changes we have done in the grid A CategorylD u Product No Product Price Units 77 Original Frankfur 13 00 32 61 Sirop d rable 28 50 113 63 Vegie spread 43 90 24 a avg Price 23 0625 sum507 Category 3 48 Chocolade 12 75 15 26 Gumbar Gummib 31 23 15 49 Maxilaku 20 00 10 25 NuNuCa NuB Yo 14 00 76 16 Pavlova 17 45 29 7 Gehan Erhnbnlarda 47 On E Fa a anyQuery User s manual 34 06 2005 PLA NWG RE There are three possibilities to adjust the column width 1 inthe Layout Window set the value of the column Width for each Data Field 2 inthe Resultgrid double click on the grid line between two columns to automatically fit the column width to the contained data 3 inthe Resultgrid select Fit All Columns from the Resultgrid menu to fit all columns to the width of the grid We can now Save and print our small report For saving use the Save As button and select the filetype Report and fill in a name for it A Report consists of two files e the query with the extension rpt e
23. n choose the File menu From the submenu select New data base connection and the dialog Data Link Properties will appear Datenverkniipfungseigenschaften Provider Verbindung Erweitert Alle Wahlen Sie die Daten aus zu denen Sie eine Verbindung herstellen mochten OLE DB Provider IBM OLE DE Provider for DB Servers MediaCatalogDB OLE DB Provider MedialatalogMergedDE OLE DB Provider MediaCatalogWebDB OLE DB Provider Microsoft ISAM 1 1 OLE DB Provider Microsoft Jet 3 51 OLE DB Provider Microsoft Jet 4 0 OLE DB Provider Microsoft OLE DB Provider For Data Mining Services Microsoft OLE DB Provider for Indexing Service Microsoft OLE DB Provider for Intemet Publishing Microsoft OLE DE Provider for ODBC Drivers Microsoft OLE DB Provider for OLAF Services Microsoft OLE DB Provider for OLAP Services 8 0 Microsoft OLE OB Provider for Oracle Microsoft OLE DB Provider for Outlook Search Microsoft OLE DB Provider for SQL Server Abbrechen Fig 31 Window for creating a data link file anyQuery User s manual 26 06 2005 PLA NWARE mye From the Provider Tab choose the OLE DB Provider and press the Next Button to switch to the Connection Tab Select the file of the database that needs to be connected or fill in the parameters of the provider Username and password are optional Click the Test Connec
24. ndexes in the current database All Recordcount Recordcount txt record count of all tables in the current database anyQuery User s manual 06 2005 PLA NWGRE 8 SQL Links 8 1 Links for Introduction Help and Database Provider Homepages Database Provider MySQL http www mysal com ORACLE http www oracle com IBM DB2 http www 4 ibm com software data db2 Microsoft SQL Server http www microsoft com sql default asp Mini SQL http www hughes com au products SQL Tutorials http www dcs napier ac uk andrew sgl http www w3schools com sgal default asp http www sglcourse com http www sglcourse2 com German Tutorial for MySQL http www2 rent a database de mysagl DB2 Manuals with SQL Reference http www 4 ibm com cgi bin db2www data db2 udb winos2unix support techlib d2w report Link Portal http www sal org SQL Magazine SQL Server Magazin http www sqlmag com SQL Books Markt Technik http www mut com leseecke leseecke asp http ourworld compuserve com homepages Graeme _birchall HTM COOK htm anyQuery User s manual 38 06 2005
25. ormal one statement query with a semicolon because this will most probably produce an error A script is executed statement by statement and you will get the result printed in the Session Tab Scripts can not include SELECT queries they are only for action queries The execution of a script will not be stopped if an error occurs if you would like to preserve your data integrity you can set a transaction before you click on the Excecute button watch the logging and commit or rollback afterwards Because there is no difference between queries and scripts from now on we will only talk about queries There is no limit to the number of queries to be open at the same time in anyQuery To create a new script use the New SQL Script menu or the shortcut ALT N As long as you don t save it the query will have a name like New SQL 01 You can select a query you want to work with by clicking on the corresponding named tab below the SQL Editor To close it click on the small cross in the left corner if you made any changes to the query you will be asked to save it anyQuery User s manual 06 2005 PLGA NWARE Normally it makes sense to organize your queries in folders and subfolders to do so use the settings dialog and set the Standard Query Pathto a folder you would like your queries to be stored in If you will later on open a query from a different location anyQuery will remember this folder for the rest of the session for opening and saving operat
26. s 4 2 System Requirements anyQuery runs on Windows 98 Windows NT 4 0 Windows 2000 and Windows XP 4 3 Restrictions For all Windows platforms except Windows XP the DataAccessPack 2 7 is needed This database package can be downloaded from the Microsoft web site www microsoft com Data for free anyQuery User s manual 6 06 2005 era re 5 Registering anyQuery 5 1 How to register To get rid of the message boxes telling you have to register your version of anyQuery and order a license key from htip www planware com Having received your license key please go to the menu help menu and click on the About anyQuery item to open the about dialogue About anyQuery yi ee PLANWARE Beratung amp Software GmbH fay gt www planware com System Info ps Eu Version 2 5 02 UNREGISTERED N 2005 PLANWARE Beratung amp Software GmbH Fig 1 about dialogue Within the about dialogue click on the Register Program button to open the Enter Registration Key dialogue to fill in your license key Registration Key Enter the registration key exactly as itis given to WO Fig 2 Enter Registration Key dialogue The license key is in the form of 4 groups of 3 characters separated by a dash Having entered the license key click on the OK button After restarting anyQuery you should no longer be prompted to register anyQuery User s manual 06 2005 eT PLANWGRE 6 User Interfac
27. t of anyQuery The last possibility is to do a XML export Click on the Export As XML File item in the Resultgrid menu choose a filename and click the Open button After the export has finished you will get a message box Now to some additional features comments and formatting In every query or script you can use comments at any position Besides documentation comments are good for testing statements A comment starts with a and spans one line this is a comment SELECT CustomerID Address ContactName Fax Phone FROM CUSTOMERS and this too WHERE City London AND Customer ID NoORTS To comment or uncomment a whole block of text you can use the Comment Row function or UnComment Row function from the Edit SQL menu You can also use the shortcuts ALT C or ALT U To do so mark the region of text and click on the menu item or use the shortcut the color of the marked text will change to green The function which colors your text is named Autosyntax Coloring and can be switched on and off in the Edit menu This makes sense with long scripts in which the coloring function can consume a noticeable amount of time To debug statements which were generated by applications you can paste this often unformatted SQLs to the SQL Editor and use the Format SQL function from the Edit menu to let anyQuery do the job of making it more readable This SQL for example anyQuery User s manual 30 06 2005 PLA NWGRE m
28. tent of the SQL editor Delete Querytext deletes the content of the SQL editor without saving any changes Comment Row adds a comment sign to the beginning of each selected line UnComment Row removes a comment sign from the beginning of each selected line Increase Indent increases the indent of all selected lines Reduce Indent reduces the indent of all selected lines Auto Syntax Coloring colors keywords comments and operators word by word anyQuery User s manual 41 06 2005 PLA NWG RE a 6 2 1 3 Query Menu Ir Execute Query F2 d Stop Query F3 Build Update Build Where Build Join Insert Fieldist AltH Fig 7 Query Menu Within the Query Menu you will see the following menu items Execute Query executes the actual query see Function Execute as a shortcut you can also press F2 Stop Query cancels a running query see Function Stop as a shortcut you can also press F3 Build Update builds an Update Set statement for the currently selected table all key fields included and the selected row from the result grid Build Where builds aWhere Statement for the currently selected table all included key fields and the selected row from the result grid Build Join builds a simple Join Statement for the currently selected tables Insert Fieldlist inserts all selected Fields into the active SQL worksheet anyQuery User s manual 125 06 2005 6 2 1 4 Resultgrid Menu Layout settings Allow Column Sorting
29. the report definition with the extension rpt bin If you would like to switch between queries and reports during your anyQuery session you better save report and layout to a file because otherwise your layout will get lost when switching to another query Before printing we will take a short look at the Print Settings and the Print Preview Print Settings Header Text Lett Text Center Text Right es es Arial 9 75 Top Margin mm Header Distance mm Left Margin mm Right Mardin mm Bottom Margin mm 15 Footer Footer Distance mm Footer Text Lett Text Center Text Right Arial 9 75 Report Orientation C Landscape i Portrait Fig 32 Print Settings dialog Within the Print Settings dialog you can define the text for the left mid and right headers and footers of your report Additionally the following functions can be used to display page numbers and date in the report Use a hash in front of the function name You may also select a separate font for headers and footers by using the Font button The middle section of the dialog enables you to setup the page layout anyQuery User s manual ETE 06 2005 gchar ha function description PageNumber current page number total number of pages current date and time The Standard Settings button sets useful values for all options and you will get a layout which will fit for most purposes To save your changes click the Save button but don t
30. tion button to immediately check for a proper working connection username and password are mandatory To use the new connection click the Open Database Connection submenu in the File menu and choose the udl file from the dialog 7 2 2 Modifying a Database Connection Choose the File menu and select the submenu Edit Database Connection for editing the database connection of a stored udl file 7 2 3 Opening a Database Connection To work with anyQuery you have to connect to a database first Open a stored udl file from the File menu and select submenu Open Database Connection You can also choose a connection from the Open Recent Connection menu 7 2 4 Creating a Connection for NWIND mdb Select New Database Connection item from the File menu Select one of the Microsoft Access OLE DB drivers from the list and click the next button Select the database location and click the OK button Enter a name for the new udl File in the next dialogue and click the save button Now you have successfully created a new database connection 7 3 Working with Queries and Scripts There is no difference between queries and scripts except that a script is composed of several queries which are separated by a semicolon We prefer the extensions gry for queries sql for scripts and dadl for data definition statements but there is no need to do so anyQuery recognizes statements as a script when there are at least 2 semicolons in it You should not end a n
31. tion and commit or rollback the changes anyQuery User s manual ETE 06 2005 jerre din QUA 6 2 8 DDL Commandbar Tab Eannnnnunsnnnnnna CREATE TABLE UNIQUE NOT NULL ALTER TABLE PRIMARY KEY DROP TABLE CONSTRAINT SINGLE Fig 20 DDL Commands The DDL commandbar simplifies the creation of data definition statements All command buttons will at least display a useful frame if no Tables are selected for this statement The datatypelist contents the datatypes which were supported by the database Doubleclick to insert the datatype name into the SQL editor 6 2 9 Parameter Commandbar Tab SQL Commands Find Replace Query Options DDL Parameter Country new Fig 21b Parameter The Parameter commandbar shows the parameters in the SQL script In the field behind the Parametername you can input a value which were used at script execution new creates a new parameter tag in the SQL script 01 Customer s in UK and USA i eee ee NE PR eee J 03 SELECT 04 FROM Customers 05 WHERE Country ACountry String 50 E 4 Je nf 6 2 9 1 Create new parameter lw New Parameter Two Cancel Insert Parameter Abb 21c create new parameter dialog In the field Name you can name your parameter With type you set the parametertype String Integer Double Date With size you can give String types a input length limit for parameter values see Fig 20b above anyQ
32. to display and edit data by columns and rows Sous Sales ee hace alee aie heme date o TNN Fr T sn a en Te a die data Link File to define an OLE DB connection S S S ee i a 7 _ 5 nen as 2 Purpose and Overview 2 1 Purpose of anyQuery anyQuery is a special tool to work with all different kinds of databases Because the developer team of PLANWARE frequently does database development real soon a tool became necessary in order to simplify the work with databases Extensive investigation brought up only some poorly performing tools or excellent ones like the QueryAnalyser of SQL Server but this one is bound to SQL Server By and by anyQuery was developed according to the requirements we encountered each day Most important was the possibility to work with many different databases like SQL Server Oracle DB2 or MS Access which is well known as an eager producer of parenthesis within its queries anyQuery User s manual 4 06 2005 PLA NWARE 2 2 Overview of this document In Chapter Changes and additional Functions you will get a short overview of the changes and new functions in comparison to prior versions In Chapter Functions and System Requirements anyQuery is described with its features and system requirements For restrictions and known bugs please refer to the Readme texttfile In Chapter Registering anyQuery instructions how to register and activate anyQuery In Chapter User Interface the user interface of
33. uery User s manual ETE 06 2005 PLA NWARE 6 2 10 SQL Editor Session NewSQL_01 01 Customer s in UK and USA zZ 02 SELECT 03 FROM Customers 4 04 WHERE Country IN UR USA k Je iiil gt Fig 22 SQL Editor Within the SQL Editor you can create your queries You can load already stored queries for modifications You have the possibility to format the displayed content and to color the keywords see Editing a Query The editor is able to handle more than one query at a time you can switch between the loaded queries by clicking on the appropriately named tab at the top of the editor area In the right top corner of the main window you find a little button with a cross to close the active query 6 2 11 Result Area The result area is at the bottom of the anyQuery window County 5 CustomelD Company ame Contacth ame ContactT itle Address City z UK ISLAT Island Trading Helen Bennett Marketing Manager Garden Housel Core NORTS North South Simon Crowther Sales Associate South Houses00 Londe SEVES Seven Seas Imports Hari Kumar Sales Manager 90 Wadhurst Ad Londe EASTC Eastern Connection Ann Devon Sales Agent 35 King George Lorde b CONSH Consolidated Hold Elizabeth Brown Sales Representa Berkeley Gardens Lande BSBEW B s Beverages Victoria Ashworth Sales Representa Fauntleroy Circus Londe AROUT Around the Horn Thomas Hardy Sales Representa 120 Hanower Sg Lande E USA SFLIR Sp
34. up for the column in Dales he minimum value Ina group ore column ax Dilys ie mamom valve na group orth coum Value Count Displays the count of records with non null values in a group for the column Imagine we want to make a report with the number of items we have in stock for each group and what the average price To prepare the report choose the Layout settings menu and you will see all the parameters for the report in the layout window v Hold layout Row Height Pixel a ae Te sa i er a aaa I PESEN CategornlD a Aggregate I al ett oF 1500 Catego ylD u ProductiD None Aggregate 2 ET Left Fi FroductD u FroductName None Aggregate 3 Text Left FroductNa u UnitPrice None Aggregate 4 Text Currency Lett i UnitFrice UnitslnStoc Save Close The Data Field column shows you the field name and we will reference on that and on the column header Ok we will go through the columns from left to right and explain what to select or fill in for this report anyQuery User s manual 33 06 2005 PLA NWARE Inthe Column column fill in Product No for ProductiD Product for ProductName Price tor UnitPrice Units for UnitsinStock In the Aggregate column select Average for UnitPrice Sum for UnitsinStock In the Headeralignment column select Center for UnitPrice Center for UnitsInStock In the Visible column unselect CategoryID Inthe Group Prefix column fill in Category for CategoryID In the Total Prefix column fill i
35. will get a resultset similar to this CategoryID ProductiD Product lame UnitPrice UnitsInStock A 1 1 Chai 18 00 39 1 2 Chang 19 00 17 E 2 3 Aniseed Syrup 10 00 13 2 4 Chef Anton s Caj 22 00 53 2 5 Chef Anton s Gu 21 35 0 2 6 Grandma s Boyse 25 00 120 7 7 Unde Bob s Orga 30 00 15 2 8 Northwoods Cra 40 00 6 6 9 Mishi Kobe Miku 97 00 29 8 10 Ikura 31 00 31 11 Queso Cabrales 21 00 22 4 12 Queso Manchego 38 00 56 z Because the resultset is very small we can do some very common tasks easily without changing the query anyQuery User s manual 31 06 2005 PLA NWG RE mye To sort the resultset ascending by ProductName click right mouse button and choose the Allow Column Sorting menu to activate the sort function and on one of the column headers here the ProductName and the column will be sorted ascending If you click once more on the same column header the sort order will be toggled to descending and so on The small arrow in the column header points at the position where you will find the smaller values bottom or top of the list CategoryID ProductID ProductWame UnitPrice UnitsInStock a A 1 1 Chai 18 00 39 i 2 Chang 19 00 17 1 39 Chartreuse verte 18 00 69 m 7 4 Chef Anton s Caj 22 00 53 2 5 Chef Anton s Gu 21 35 0 3 48 Chocolade 12 75 15 1 38 C te de Blaye 263 50 17 8 58 Escargots de Bou 13 25 62 5 5
36. with or in front of it to expand or collapse the data portion Show group footer By clicking on the Show Group Footer menu you can enable and disable a group footer for each group The group footer can display a result of an aggregate function which you can define in the layout tab for each column anyQuery User s manual 23 06 2005 6 2 14 Statusbar Baers T Elapsed time 00 00 00 77 FE 14 Records L 5 C 44 www anware com Fig 27 Statusbar After you have chosen a database connection via File Menu Open Database Connection anyQuery will display the user name and the name of the database in the left portion ofthe statusbar The second field ofthe statusbar displays the execution time of the query The middle portion of the statusbar displays the number of records of the executed query shown in the result grid The right most field of the statusbar displays the actual line and column number ofthe SQL Editor 6 2 15 Settings Dialog anyQuery Settings Paths Edit Language Standard UDL Path OD Progranimne anylluery Standard Quers Path E Dokumente und Einstellungenslars gene Dateien Abfragen Startup UDL File Fig 28 Settings Dialog Path Options In the settings dialog you can define standard pathes for UDL files and queries Besides that you can assign a UDL file which will be loaded at startup The Clear button resets this so no connection will be made automat
Download Pdf Manuals
Related Search