IBExpert FAQ Version 0.1 1.) What is IBExpert? IBExpert is an Application to make the work with InterBase easier. It helps to create and change Database Objects such as Tables, Stored Procedures, Triggers, Domains, Indexes, Generators, Views, ... . You find Editors for all InterBase Objects and some unique features. 2.) How to connect to a Database? After starting IBExpert, you will see the Database Explorer on the left side. In the Menu "Database" you have to use "Register Database". On the page "General" you can choose, if you want to register a remote or a local database. If you choose remote, you have to enter the server name and the protocol. When using IBExpert on the InterBase Server, you only have to enter the Database Filename. If is not working, check the network with "ping srvname" in a DOS Window. The database file for a Windows Server must be on a physical drive on the server, because Interbase does not support databases on mapped driveletters. The database Alias is a descriptive name for this connected Database and you can use any name. Username and password is typically SYSDBA and masterkey. A role name is optional. A Charset is usefull, when the Database is created to be used for foreign languages. A typical Charset for Germany is WIN1252 or ISO8859_1 . When you did not used a Charset while creating the database, you should not use it here either. The Path to isc4.gdb is the path to the Interbase Securty database, containing the users list, passwords. This parameter is not obligatory, but if you are planning to work with the rights and the users, it must be set. Font Character Set depends on the Windows Language. When you use an ANSI compatible Language, use ANSI_CHARSET. Example: Server=Remote Servername=NTSRV Protocol=TCP/IP Databasefile=C:\program files\borland\interbase\Examples\v5\EMPLOYEE.GDB Database Alias=Employee Path to ISC4.GDB=C:\program files\borland\interbase\ISC4.GDB To test the connection, just press "Test Connect" and you will get a message, if everything works fine or an error message. When the error Message appears, check, if the InterBase Server is running. If you want to copy an existing Database Alias, drop down the button "Copy Alias Info" and all values are copied. Additional Parameters: For the objects in the Database Explorer, you can add some system objects, such as Systemtables and System generated Domains and Trigger. All these objects will be loaded when opening the Database Alias. When using the SQL Editor, you can always see the performance analysis. It is also possible to see the used system tables in this analysis. In the Result Grid, all Char Fields can be automatically trimmed. Autocommit Transactions: When you donīt want to use explizit Transaction Control, you can also use Autocommit, but then you cannot rollback any Transaction, so be careful. The SQL Editor saves old SQL Statements, so when you start IBExpert, you can use the last 100 SQL Statements (or as many as you entered here). Every Statement is only visible when the same Alias is in use. Log Files: If you want IBExpert to protocol all statements, that change metadata and/or that are executed from the SQL Editor, you can enter filenames. This is helpful when you want to know, what changes were made. Backup/Restore: For every Database Alias, you can store own Backup and Restore Filenames and Options. This makes it easier to Backup a Database with a single mouseclick from the services menu. The Options are similar to the original InterBase Doocumentation. Default Path: You can set Default Pathnames for Metadata Extract, Export, Quick Save and Parameters. 3.) How to create a new database? When you create a new database, you have to enter the filename in the typical InterBase Naming Conventions. To Create a new database on the same Machine, where IBExpert is in use, you do not need to enter a servername. When you want to create a new Database File on a remote Server, you should use teh following Syntax: Windows SERVER_NAME:C:\path\Database.gdb Linux SERVER_NAME:/path/database.gdb With "SERVER_NAME:" you choose TCP/IP Protocol. The SQL dialect can be 1 or 3. See Details on SQL Dialect in the InterBase 6 Documentation. A good Value for the Page_size is 4096. "Register after Creation" opens the Registration Windows direct after Creating the Database. 4.) How to use the SQL Editor? To start the SQL Editor, press F12. The SQL Editor starts with the last used command on the active Database Alias. To load the previous SQL Statement, press Ctrl+P, the next Statement can be loaded with Ctrl+N, when it is not already the last Statement. If you want to use a new editor Window, press Shift+F12. Select Statements: When you want to open the result set of a select Statement (for example "select * from org_chart"), you can press F9. The Result Page is opened automatically. by clicking on the column header, you can sort the result set. if it is an Live result set, you can also edit the data in the Grid. On the bottom, you can change between Grid View, Form View or use Print Data to make a simple report on the result. Export the result Set: When there is an Open Result Set, you can Press Ctrl+E to open the Data Export Page. Supported formats are Excel, MS Word, RTF, HTML, Text, CSV, DIF, SYLK, Latex, XML and Clipboard. Depending on the format you can change some formats on the second or third page. If you want to open the File directly after creating, donīt forget a typical file extension like *.xls for Excel files. When you press of the most right Toolbar Button, you can export the Data into a insert sql script (without blob fields). 5.) What is the meaning of the Performance Analysis? After Opening a SQL Select Statement or starting a Stored Procedure, you can have a look into the Performance Analysis. On the bottom, you find the buttons to change the view. Reads, Updates, Deletes and Insert are grapical pages, where you can see, what table is used with how many Operations and if it is using an Index (Blue) or not (Red). Select Statements will only have a Result on the Reads Page, but some Stored Procedures will have result on all pages. Summary: In the summary you find all collected Performance Data on one Page. You find informations on Prepare Time, Execute Time, Average Fetch Time, Memory Changes, Buffers and Read, Write and fetch operations. In the enhanced Info, you find all tables with the non grapical Performance result. 6.) What is the Plan? The plan in a description from interbase, how the Optimizer uses tables and indexes to get this result set. If you find the words sort, it means, that you have to check, if some improvements on the query or on the indexes are required. 7.) How to optimize a SQL Statement? When you see a lot of non indexed reads (the red ones), it is often helpful to create some indexes, reopen the query and check, if it is helpful. Have a look at the reads, writes and Fetches! Reads and Writes are typically 0, when InterBase can operate in the cache. Fetches are the internal Operations in InterBase, so when one query is slower than the other, it must not be visible directly in the graphical view, for example when InterBase creates external temporary Sort files. 8.) Are there any other features in the SQL Editor? -Hyperlinks: When you write an objectname in the sql Editor, you can click on it and the Object Editor is opened. -Code Completion: When you write only a part of an object name or a Keyword, press Ctrl+Space to drop down a listbox with the possible words -SQL Assistant: it can be opened and closed with Ctrl+A and is a window in the DB Explorer. When you click on an object in DB Explorer, you find all fields and details in the SQL Assistant. You can select some fieldnames and put them with drag and drop comma seperated in the sql editor. -Load/Save: Ctrl+L loads a SQL statement from a file and Ctrl+S saves the actual Statement into a file. -Logs: You find all Statements in the Logs Window. -Script: All ISQL Scrippts can be loaded in the SQL Script editor. To use a script without a connect statement, you can activate the button "Use current connect" on top of the Editor. -Change Connection: On Top of the Editor you find on the left side the actual connected Alias. To change , simply press on the name and change another from the Dropdown List. -Keyboard templates: To make some typical statements easier to type, you can add a "Keyboard Template" in the menu "Options", for example type "ife" and press space, then it is changed to a "if ... then ... else ..." Statement. -Visual Query Builder: Press Ctrl+Alt+B to open the Visual Query Builder. This Windows makes it easier to create large SQL Statements on more than one Table. Drop the tables from the table list on the working area and connect the tables with mouse button pressed. To change the type of the connection (Outer Joins,...), make a double Click on the connection and change the values as needed. Check every field, that is important for the result set and press F9 to see the result. To add some criterias, simply press on the circle on the left in the condition Area below the Working area. When you want to have a look at the sql statement, simply press on "Edit" on Top of the Editor. -Create View or Create Procedure from Select: When a valid select Statement is entered in the SQL Editor, you can press on then right toolbutton or click on the right mouse button. It is possible to create a view or a procedure from a SQL Statement, without typing all variables and parameters. -Create Temptables: If you want to store the result of a SQL Statement in a new table, just type "Insert into New_Table Select * from Old_Table". The new table is automatically created, when it not already exists. -Copy data from one Database to another: If you want to copy Data from a "Source_alias" to a "Destination_Alias", simply open a SQL Editor in the Source_Alias and type: "Insert into [Destination_Alias].NEW_Table select * from Old_Table" 9.) Are there any specials in the Tableeditor? -SIUD Procedures: When you right-click on a table in the DB Explorer, you will find a menu called Create SIUD procedures. SIUD means Select, Insert, Update and Delete procedures. When you want to prevent the database users from directly manipulationg the data with insert, update, delete statements, you can use these procedures, which must be executed. -Reorder Fields: The fieldlist can be reordered with Shift+Ctrl+Up and Shift+Ctrl+Down to move the selected field up or down. -Index Reorganisation: In the Index List, you can find a field with the Index Statistics (=>V1.9) and you can recompute all Index selectivity with right mouse click. -recordcount: Press this button on top of the Tableeditor, when you want to know, how many records are in the table. 10.) What features are implemented in the Procedure Editor? -Lazy Mode On/Off: By Clicking on the left Toolbar Button in this Editor, you can change the view between lazy Mode and Classic Mode. Just have a look to see what lazy mode means and what version you like more. 11.) How to debug a Stored Procedure? Open the Procedure in the Editor and Press the Debug Toolbar Button to start the debugger Window. First you should have a look at the Values of the parameters and then press F8 to go through the procedure step by step. After every step, you can see all variable Values. It is possible to add a breakpoint with F5 or start the procedure with F9. 12.) Are there typical Windows for all Object Editors? -Dependencies: All Objects, that depend on other objects or where other objects are depending on this, can be seen in the Object Editor Dependencies Page. -Performance Analysis: For Stored Procedures and SQL Editor, you can start the result set with F9 and then, you will find the Performance Result in a new Page. -Description: Shows the Desciption Field from the InterBase Database. 13.) How to setup Stored Procedure Version Control? Simply execute the SQL File named "version_history.sql" from the IBE_Scripts Dirctory. To see the older Procedure Sources open the new System table called "IBE$VERSION_HISTORY". Every change to any procedure is automatically stored. 14.) What is the meaning of the Project View? To use the Project View you have to create some objects with the SQL File "project_view.sql" from the IBE_Scripts Directory. After you created this table, click on Project Page in the DB Explorer. With a mouse right click you can add folders and add objects in these folders to organize your Database in your personal way. 15.) What is the meaning of the recent Page in DB Explorer? In this page, you find the last opened Objects. To reopen, just doubleclick it. 16.) How to use the integrated Report Manager from the Menu "Tools"? Simply open the menuitem and you can create a new Report on any Volume or in the Database (Make a doubleclick on a database entry to create the needed Objects automatically). To Edit the Report, just use Ctrl+D and the Editor will open. To create a new report, simply click right on Page1 Header and add a new dialog form. On this form you can add a database and one or more query components. Go back to Page1 and Insert some bands and rectangle objects. All Data Connection can be in the Object Inspector or after a double Click. See on www.fast-report.com to see some examples and the original components, that can be used in any Delphi/CBuilder Project as a powerful and stable replacement for Quickreport and other report tools. Other Topics will come in one of the next FAQ Versions.