Welcome to the Beta of SQL Express, the new and faster way to build SQL statements in Delphi. ----------------- Table of Contents ----------------- 1. Advantages 2. Installation 3. Bugs and suggestions 4. SQL Express 5. Small tutorial 6. Not yet supported 7. Order and Availability 1. Advantages Modeless - SQL Express is modeless. You can work in SQL Express and the rest of Delphi at the same time. Switch databases - You can easily change databases in SQL Express without having to get out and come back in. Parameters - Add/Edit your parameters in the same window you design your SQL statements. DataType - You know longer have to be bothered with setting the DataType for the Params property in TQuery. Typing - The only typing you will have to do is for the values and parameter names. Click, Click, Click and you statement is done. 2. Installation Simply place all files in a directory under Delphi and install the SQLEXPR.PAS file into Delphi 2.0 thru the Component/Install menu item. Go to the SQL property of TQuery and that is it. * * IMPORT NOTE: This is limited use BETA. It will expire after 99 times. At that point you will need to uninstall SQLEXPR from Delphi. Start Beta testing. 3. Bugs and Suggestions Please report any bugs and suggestions to my internet mail address lewiskr@airmail.net. 4. SQL Express SQL Express is divided into 8 tabs and each are described below: a. Query: This tab display 4 panel views to work with. I. The top left panel with the drop down is simply for information about the database you select in the combo box. After selecting a database a list of tables will appear in the listbox below the combo box. Note: Double clicking on a table name will build a Quick select all statement for that table. Example: SELECT * FROM EMPLOYEE (From IBLOCAL) II. The top right text window is for entering SQL statement manually. Fell free to enter SQL statements in this window and press the Execute button (button with a > arrow glyph). III. The middle panel holds several buttons to work with and several should be very familiar. Check button - Accept and close the form. X button - Cancel any changes and close the form. Apply button - Apply current changes to the TQuery component. Navigator button - Used for navigating and editing the records retrieved from the SQL statement above. (Note: If you are working with a SQL database like IBLOCAL you may need to select Request Live under the options menu. Also certain types of statement will not be editable such as statements linked and ordered). Execute SQL Statement - Execute the current SQL statement above. Get SQL - Build the SQL statement created from the remaining tabs in SQL Express. Note: Also in Options menu you can have a statement Run Automatically as well as replace the SQL statement without confirming. Restore - Restore will restore SQL Express to the previous applied contents from TQeury. IV. The bottom grid is the result set of a SELECT statement. Click Execute SQL Statement to execute the current SQL statement above. You can edit the contents depending on the query performed and the database you are connected to. The navigator buttons will reflect what is possible. b. Joins: The joins tab starts off the SQL statement by selecting one or more table to create a valid SQL statement. Table 1 list box will always be the starting point in building a SQL statement. To join two tables together simply click on a table in Table1 listbox and a table in Table2 listbox and select the two key fields that will be the link between the two tables and either double click in either fields list or click the Joins >> button create the join. Joins are created with a type of Standard as show in the Join Type listbox. To change the type simply click on the join in the Selected Joins listbox and click on the desired join type in the Join Type listbox. Once you have selected one or more tables you can now continue with the rest of the tables to build your statement. Alias Names: Simply click on the table name and enter the alias name and press to accept the name or exit from the text box. c. Select: The select table is where fields will be selected as part of the fields that will be part of the result set. Click on the table name and select fields to be added to the Selected Fields list. Note: Double click on the table name will add all fields for that table to the list. Aggregate Fields: Simply select the fields you wish to be an aggregate and click on any of the Aggregate buttons and it will be added to the list. Note: Adding an aggregate to the list with fields will automatically turn on Group By in the Group By tab and is mandatory for the statement. d. Where: The where tab is exactly what it says. Here you will select fields and build expressions for the WHERE part of the SQL statement. Click on the table and field to place an expression on it. Once you select both the Expression, AND, NOT, OR... and Sort listboxes will be enabled. The default expression will always be = (Equals) and the caret will be sitting in the Value text box. Simply enter the value you wish to be apart of the expression and you will see it appear in the Selected Fields listbox. Example: (EMPLOYEE.FIRST_NAME='Kenneth')<'Kenneth'> I. Expression listbox: LIKE - The LIKE expression will display the LIKE group box to assign wildcards to the value. BETWEEN - The BETWEEN expression will display a BETWEEN group box to edit the between values. IN - The IN expression will display the IN Group box for editing the IN values. IS NULL and IS NOT NULL - Are used for checking null values and do not have editable values. II. AND, NOT, OR...: Select AND, NOT, OR to be apart of the SQL statement. III. Sort: Select fields to be part of the ORDER BY part of the SQL statement. Once you select fields for ascending or descending these fields will appear in the Order By Tab. Parameters: Parameter are edited in the same manner as regular values except you cannot add spaces or invalid characters. Click on the Edit Params check box to edit between Values and Parameters. That's it SQL Express takes care of the rest in TQuery. e: Aggregate Fields: Fields that are added in the Select tab as aggregate fields can be modified in this tab. Simply click on the field name and you can change the properties of an aggregate field in the other list boxes of this tab. Aggregate: This listbox allow you to change any aggregate of a field on the fly. Aggregate support for each type of aggregate done through the listbox below the Aggregate listbox. Note: Only one field can be Count(*) aggregate in a SQL statement. f: Group By: Group by is exactly the same as the Where tab except only the fields selected in the Select Tab will show up in this list. Here they have the same capabilities of the Where tab but also allows the order of presendence to be set for the fields. g: Order By: The order by tab allows you to set the order presedence that will be used in the ORDER BY part of the SQL statement. h: SQL Preview: Click on this tab will preview what the SQL Statement that will be built from editing in the Joins to Order By tabs. Note: Get SQL in the Query Tab performs the same action and places this SQL statement into the memo box in that tab. 5. Small tutorial. I will now take you into a short but sweet session of the power of SQL Express. STEP 1: Select IBLOCAL from the Database Names combo box. (Password required 'masterkey') STEP 2: Click on Joins tab and select the EMPLOYEE table in the Tables 1 listbox. Enter E as the alias name for EMPLOYEE. STEP 3: Click on the DEPARTMENT table in Tables 2 listbox. Enter D as the alias name for DEPARTMENT. STEP 4: Click on D.DEPT_NO field in both field list boxes and click Join >> button. STEP 5: Select INNER as the Join Type. STEP 6: Click on the Select Tab. Double click the EMPLOYEE table to add all fields from EMPLOYEE table. Click on E.EMP_NO and click on the COUNT aggregate. Double click on the DEPARTMENT table. STEP 7: Click on the Where Tab and select EMPLOYEE and click on the E.FIRST_NAME field. Enter the letter K in the value text box. STEP 8: Click on the LIKE expression. Now click on the Edit Params check box and enter the word AFirst and press the Enter Key. STEP 9: Click on E.FIRST_NAME and select Ascending in the Sort By listbox. Do the same for E.LAST_NAME field. STEP 10: Now go to the Aggregate Fields Tab and click on E.EMP_NO and enter EmpCount as the AS field name and press the Enter Key. STEP 11: Now go to the Group By Tab and click on the D.DEPARTMENT field and click the LIKE expression and enter Co. Click on the Edit Params checkbox and enter ADept and press the Enter Key. STEP 12: Click on the Order By Tab and click on E.LAST_NAME and click the Move Up button. STEP 13: Click the SQL Preview Tab to preview the entire SQL Statement. STEP 14: Click on the Query Tab and click the Get SQL button. STEP 15: Congratulations on your first SQL Express experience. 6. Not yet support Multiple LIKE's, BETWEEN's and computed fields will be supported in the final release. 7. Order and Availability Check out our web site for order and availability or call at 1-888-OOPSOFT. OOPSoft, Inc. HTTP://WWW.OOPSOFT.COM