Quick Start

A Quick Start to Office-QB

This section is a quick start to Office-QB, the best visual query builder for office environment. In few simple steps you will learn how to use the Office-QB application to create queries helpful in your daily tasks running your business.

In creating RDBMS (Relational Database Management Systems) queries a little knowledge of the SQL language is helpful. But if you are not familiar with SQL command syntax, do not worry, because Office-QB is designed precisely for that purpose. The visual interface of Office-QB will help you create queries with a few drag-drop and field selection methods. In due time, it will also improve your SQL language skills and abilities, so that you will be able to create fairly complex or advance SQL queries.

· How can I use the query builder in office environment?

· How do I make a QB-project?

· How do I get back to an existing QB-project?

· How do I connect to a database?

· How do I start with a query?

· Can I edit the content of a query result?

· How do I export the result of a query to other applications?

· How do I output the result of a query in HTML format?

· Can I mail the query result directly?

How can I use the query builder in office environment?
In most corporations or business environments, a multitude of business processes develop or occur. A large and critical portion of these processes dwell in or around a legacy system, customized especially for the particular business information system. Smaller but more frequent portions of these data processes require more creative interaction or rather more flexible treatment than the controlled alternative of the methods available in the legacy system. Such processes may involve the retrieval of data from several databases of various types to be used with Microsoft Office® products or other software applications, transfer of data from one database system to another, data warehousing and analysis, database administrative tasks, generation of query based reports in HTML or other formats, as well as other such data processes.
The aim of Office-QB application is to bring these non-trivial tasks to the level of average office employee skills. So that the end-users may create and run queries against a variety of back-end databases, both inside and outside the organization's legacy system. Business analysts may use Office-QB application to analyze critical business data. Database administrators may use Office-QB application as a smart query builder tool in their daily work. Finally, it is always wise to have a smart query builder tool around, since there is always a data processing question hanging around in the mind of any office employee. The relevant question is how can one familiarize oneself with SQL queries in a short period of time, and advance their business information logic and skills.
Office-QB may be used along with Microsoft Office® products, such as Excel, Access, and Word, against Microsoft SQL Server, Microsoft Access, as well as other non-Microsoft databases (such as Oracle, Sybase, DB2, etc.), to enhance the overall data retrieval and control skills in your office environment. In particular, using Office-QB you may access data from MS-Access, MS-SQL Server, Oracle, Sybase, DB2 and other databases, all in one single QB-project. Moreover, you may utilize copy/paste methods to transfer selected data from query results of Office-QB to Microsoft Office® applications. You may also use the Import feature of Office-QB to transfer bulk data between different database types. In addition, using the output features of Office-QB one may export query results as HTML documents, or send direct mail that contains query results via the Mail Query special menu.

How do I make a QB-project?
To start with Office-QB application, you will need to create a QB-project (Query Builder project). A QB-project will organize your work into a user-specified directory on your system. In this way saving you time and effort by avoiding the undesirable alternative of query files scattered all over your system.
To create a project run the Office-QB application, and from the File menu select New Project. This will open the New Project dialog (see Figure 1 for more details),

Figure 1. Showing the New Project dialog,

In the default New tab of this dialog, enter the Project name field and set the Project storage location via the button next to the edit-box. Note that this 3-dot button will open the standard Windows Browse for Folder dialog, in which you may select a path for project storage files. After entering the project name and path, click on the OK button to create the project. This will display the project as an MDI child window in the main window of the Office-QB application, with the Connections pane containing only the root node, namely the Databases node (note that the Connections pane is located on the upper-right corner of the main window).
The next step you need to know in using Office-QB is to define a data access connection to the desired database, against which queries will be executed. This step will be described next, but first we need to inform that you may get back to this project by using the project explorer of the Recent tab via this same dialog.

How do I get back to an existing QB-project?
 If you want to get back to an existing QB-project, you may call the New Project dialog just like the way you used in creating new project, but this time you need to click on the Recent tab of the dialog. In this case the dialog has the appearance displayed in Figure 2 below:

Figure 2. Showing the New Project dialog with the Recent tab selected,

Note that the Recent tab of this dialog resembles a project explorer, in the sense that, when selecting a recently created project, the project's content is displayed in the tree-view control on the right side of the dialog. In this way you may explore the project contents before opening it in the main window of Office-QB. Note that sometimes the links of this Recent Projects list-box need to be refreshed via the Update button.
To open an existing project, you may select the project from the Recent Projects list-box and then click the OK button.

How do I connect to a database?
 After you create or open a QB-project in Office-QB, from the File menu select New Connection menu item, which will open the New Database Connection dialog, as shown in Figure 3 below (note that an alternative way to access this dialog may be via the New item of the popup menu, using the right-mouse click on the Databases root-node of the Connections pane):

Figure 3. Showing the New Database Connection dialog

In the New Database Connection dialog, the user must then select a data access method using one of the items in the Data Access Type list-box, and enter a user-friendly name for the connection in the Connection Name text-box. When the user OK-s this dialog, depending on the data access type selected, different type of data access dialogs may be prompt. For illustration purpose, let us select the OLE DB Dynamic Data Access : ADO item from the Data Access Type list-box. This will prompt the Data Link Properties dialog shown in Figure 4 below:

Figure 4. Showing the Data Link Properties dialog

In this Data Link Properties dialog, the user must first select the OLE DB provider from the Provider tab. For example, for MS-SQL Server the user must select the Microsoft OLE DB Provider for SQL Server item from the list of providers. For Oracle the best option is the native Oracle Provider for OLE DB. For MS-Access one may use the Microsoft Jet 4.0 OLE DB Provider, while for all other ODBC compliant databases the Microsoft OLE DB Provider for ODBC may be used.

For example let us assume that we want to connect to the Northwind database, which comes as a sample database in MS-SQL Server. We proceed by first selecting the Microsoft OLE DB Provider for SQL Server item from the list in the Provider tab and clicking the Next >> button. This will switch the tab to the Connection tab, where more details must be entered about the database connection, such as: data source name / location of data / server name, server logon information, initial catalog or database name.

In this Connection tab, for step 1, you need to specify the server name, by either selecting it from the combo-list, or (if not present) you must type-in the exact name. For step 2, you must select either of the radio buttons for authentication to the SQL Server. For example, the Use Windows NT Integrated Security option, will use the Windows NT logon information and therefore requires no further logon/password information. On the other hand, the Use a specific user name and password option, requires the user's SQL logon information, which was granted by the database administrator. Finally in step 3, you must select a database name from the list of database in the combo-box.

When the connection information is specified and the dialogs submitted, a new connection node is added to the Databases root-node. So that in the future you do not need to redefine this connection information for the specific database connection. All you have to do to establish connection to the specific database, is to double-click on this connection node.

Note that, in the future if you want to modify the properties of an existing connection node, you may use the Properties menu item from the popup menu, obtained via the right-mouse button click method on the selected connection node itself. This will display the connection information dialog corresponding to the data source and data access type for the selected connection node, with which the user may modify the data source name / location of data / server name, server logon information, initial catalog or database name.

Once a connection node is defined and open, the user may create any number of queries for that particular database connection. These queries will appear as child nodes under the connection node in the Connections pane. In the following paragraph we will give you and example of how to create a simple query on the Northwind sample database in the MS-SQL Server.

How do I start with a query?
 After defining the connection to the desired database, we are ready to create queries against this database. To create a new query on the particular database connection, we use the New menu item from the popup menu (see Figure 5), with the right-mouse button click on that connection node. This will create a query child node under this connection node, and will open this new query into the query builder panes. By default any new query has the name “New Query”, which may be modified by a second left-mouse click on the query node making the node label editable.

Figure 5. Showing the popup menu,

Alternatively, the user may create a new query based on an existing query by simply copying the existing query via the Copy menu item of the right-mouse popup menu, and then using the Paste menu item of this same popup menu triggered on the connection node, the user may paste a copy of the existing query with the name prefixed by “Copy_of_” string.

To save modifications to a query the user may use the Save Query menu item under the File menu. This will store the query changes in the binary file for the connection, over which the query is defined. Note that, the user may open an existing query into the query builder by double-clicking on the node corresponding to that query. Only one query may be opened for edit at the same time.

After we create a new query, the Relations, Columns and SQL panes are initially empty. We drag-drop selected tables from the DB Browser pane into the Relations pane. For example try grabbing the Orders table from the Tables folder of the Northwind database node in the DB Browser pane, drag-drop it into the Relations pane. Similarly drag-drop the Order Details table into the Relations pane (see Figure 6 for the result):

Figure 6. Showing the Orders and Order Details tables with relationship,

Observe that Office-QB automatically added the enforced relation between the Orders and Order Details tables. It also inserted the following SQL command text in the SQL pane:

SELECT *
FROM Orders INNER JOIN [Order Details] ON
    [Order Details].OrderID = Orders.OrderID
To add specific fields to this current query, we click on the field check-boxes in the tables in the diagram of the Relations pane to select them. For example try selecting the ShipVia field of the Orders table and the Quantity field of the Order Details table. You may alternatively select these fields via the drop-down Column combo-box in the Columns pane.

In the Columns pane we can define grouping, sorting as well as criteria. For example, select the field Orders.OrderDate via the combo-box in the Columns pane, and enter the text >'11-10-1997' in the Criteria column for that field-row. That is we want to query all orders with order date greater than November 10, 1997. This will automatically update the SQL command text in the SQL pane, by appending the following WHERE-clause:

WHERE
    (Orders.OrderDate > '11-10-1997')

Now execute the query by clicking the blue arrow button in the query builder child window's toolbar. The result of the query will be displayed in the Result tab-view.

Can I edit the content of a query result?
For most simple queries, the user may run the query and then edit any unconstraint field directly in the Result tab-view. For example, create a new query, drag-drop the Orders table from the Northwind database into the Relations pane, and then execute this new query. All records of the Orders table will be displayed in the grid of the Result tab-view. Try editing the content of the cells under the ShipName field-column (Note: you may use the DOWN-ARROW key followed by the END key to continuously edit cells under a field-column).

How do I export the result of a query to other applications?
 In the Result tab-view of Office-QB application, one may use the Copy and Paste menu items to transfer portions of data between Office-QB and other applications. Moreover, the Import Result menu of Office-QB may be used to transfer bulk data from one data source to another.

How do I output the result of a query in HTML format?
 Office-QB application comes with a built-in HTML output feature, which gives the user the ability to output the result of any query directly in HTML format. Essentially, in HTML format the output of the query result is imbedded in HTML-table. Moreover the widths of the columns of the HTML output are controlled by the widths of the field-columns of the grid of the Result tab-view. So that the user may adjust the column-widths of the HTML output by resizing the grid-columns in the Result tab-view. To output a query in HTML format, first define the query and execute it. When the result of the query is displayed in the Result tab-view, adjust the widths of the field-columns in the grid to proper sizes, and from the Command menu select the Output Result as HTML menu item. This will generate the HTML output and display it in the default web browser on your system.

Can I mail the query result directly?
In Office-QB application, one can directly e-mail the query result via the Mail Query menu item of the Command menu. This feature is similar to the HTML output, but will display the output in Outlook Express New Message dialog, from where the user may send the e-mail containing the HTML output of the query result (see Figure 7 for more details):

Figure 7. Showing the e-mail New Message dialog containing the output of the query result.


Introduction

The Office-QB is a visual interface used for the creation of complex queries in much the same manner as MS-Access query design interface.

The Office-QB visual interface essentially consists of the following five panes:

· Connections pane, in which the user defines or manipulates database connections,

· DB Browser pane, which displays the database objects for the currently active database connection in the Connections pane,

· Relations pane, in which relations and joins are defined for selected database objects,

· Columns pane, in which the user selects fields and defines criteria, as well as grouping and sorting,

· SQL pane, in which the finalized SQL command is displayed or directly entered.

With the Connections pane the user can create database connections for various types of database engines including MS-SQL Server, MS-Access, Oracle, DB2, Sybase, as well as, for other ODBC compliant OLE DB providers. Using the DB Browser pane one may display the database objects, such as tables, views and stored-procedures, for any connection defined in the Connections pane. The user may drag-drop objects from the DB Browser pane into the Relations pane, in which relations or joins between objects may be defined. In the Columns pane the user may define fields or expressions using the Columns functions toolbar. In addition, criteria, grouping and sorting may be defined via this Columns pane. All these operations together construct the SQL statement displayed in the SQL pane. Moreover, the SQL pane is an advance editor with color syntax, edit operations, find/replace and advance selection features. In this editor the user may manually enter the SQL statement, which upon update will synchronize the other panes. At the end the user may verify and run the query, and the result will be displayed in the Result tab-view of the query builder. Both the SQL pane and the grid in the Result tab-view have export and print support, so that the user may export the content to MS-Office applications (or simply print this content).

In addition, the user may edit most queries directly from the result of the query in the Result tab-view. For example, one may define a query via the Relations, Columns and SQL panes, and then execute the query. The result will be displayed in the Result tab-view, where the user may directly modify some fields of some records, or apply the Replace feature to modify a field for several records at once.

The Office-QB has integrated ADO data access mechanisms, so that the user may dynamically create any database connection, using the Connection Manager interface integrated in the Connections pane. Each query builder instance (or session window) may have it’s own associated storage location, a directory in which all the connection binary files for the instance are created. Each connection created in the Connections pane has a different such binary file, in which all the queries under that connection are stored. Multiple projects of distinct directory paths may be opened simultaneously, in which case connection nodes may be transferred between QB-projects using simple drag-drop method.


The Integrated Query Builder GUI

The Office-QB has an integrated GUI, which simplifies considerably the creation of complex queries assuming only beginner’s background in SQL language. For example, the user may use the proper SQL function via the Columns function combo-buttons, even if she/he never used it before. In this way the novice user may generate a complex query without referring to books or help files about the SQL syntax for the desired database engine.

Figure 1.1. Showing the Office-QB GUI,

This integrated GUI is displayed in Figure 1.1, which is a snapshot of the query builder in Office-QB application.


The Connection Pane

The Connections pane

Essentially, the user begins with the query builder using the Connections pane (see Figure 1.2). This Connections pane is used to define connections to various databases on which queries may be defined. A connection is displayed in the Connections pane as connection node under the Databases root node.

Figure 1.2. Connections pane

Each connection node will contain all the queries created for that connection. To operate on the Connections pane, the user may use the popup menu commands accessible via the right-mouse button click combination over the desired node of the tree in the pane. The menu items of this popup menu are interpreted depending on the context of the particular node on which the user clicks. For example, the New menu item will mean new connection when the user right-mouse button clicked over the Databases root node. While when the click is applied on a connection, then the New menu item will be interpreted as new query for that connection.

Figure 1.3. Popup menu

We list next the interpretations of these menu items:

New... | This menu item is interpreted as new connection when the right-mouse button click is applied on the Databases root node, while on any connection node it must be interpreted as new query. Anywhere else it will be disabled.
Open | This menu item is interpreted as expanding the tree when the right-mouse button click is applied on the Databases or any connection node, while on queries it must simply open the query into the query builder closing the currently open query. The alternative to this command is via the left-mouse button double-click method on the any selected node.
Rename | This menu item simply makes the node label editable so that the user may modify the name of selected connection or query.
Properties... | This menu item only applies to connections and will display the connection properties in the Data Link Properties dialog. This dialog is used to modify the database connection properties.
Remove | This menu item will remove the selected node on which the menu command is applied.
Copy | This menu item together with Paste menu item is used for duplicating an existing query. The user first applies the Copy command on selected query and then using the connection node applies the Paste command. This will paste a copy of the query with the name prefixed by “Copy_of_”.
Paste | (see the Copy menu item).


Opening a connection either via the Open menu item or double

Opening a connection

Opening a connection either via the Open menu item or double-clicking on the connection node will update the DB Browser pane displaying the tables, views and stored-procedures in the database corresponding to that connection (see Figure 1.4).

Figure 1.4. DB Browser pane

The DB Browser pane is utilized for viewing the database objects of the currently active connection, and more importantly to drag-drop such objects into the Relations pane.

Essentially, this pane will display the currently open database with static folders Tables, Views and Stored Procedures, respectively containing all the existing tables, views and stored-procedures in that database. These objects being nodes in respective Tables, Views and Stored Procedures folders, will have their field or parameter structures as child-nodes.

At this point, we should observe that the Connections pane is used to open a database connection and with the help of the DB Browser pane to view the content of that database. Moreover, for the currently active database we may use the Connections pane to open or create a particular query, in which case this query will be displayed via the Relations, Columns and SQL panes. Observe also that only one query may be opened or viewed at a time, and that the query builder has some sort of SDI (Single Document Interface) user interface.

More precisely, for each project instance of the Office-QB query builder, the GUI behaves in SDI manner.


As we have stated that essentially a query is displayed via the Relations

The Main Toolbar

Essentially a query is displayed via the Relations, Columns and SQL panes, which are contained in the Query tab of the query builder screen. The visibility of the panes of the query builder interface may be toggled, so that the user may hide any of the five panes to maximize the appearance of the other panes.

This toggling of the panes may be controlled via the first five toggle buttons of the toolbar (see Figure 1.5.). In addition to these toggle buttons the toolbar contains the Check SQL Syntax, Execute and Stop buttons. The Execute and Stop buttons work together, so that the Stop button is enabled only when a query execution is in progress.

Figure1.5. Showing the toolbar buttons

The Check SQL Syntax button will check the syntax of the SQL statement in the SQL pane against the backend database engine.

When a query is executed via the Execute button the result of the query is displayed in the grid of the Result tab-view.


The Main Toolbar

The Relations and other panes

Figure 1.6. Showing the Relations pane

We describe next the Relations, Columns and SQL panes, which work together to assist the user in creating queries. The Relations pane is used to visually add table or view objects to the query by simply dragging from the DB Browser pane and dropping them into the Relations pane (see Figure 1.6 for details). Once such objects are in the Relations pane, the user may relate fields between objects via drag-drop method. In addition, to the relation or join line being drawn when fields are related, the user may use the right-mouse button clicking on the join line node. This will display the popup menu with the following menu items:

Remove | This menu item will remove the selected relation or join line from the Relations pane,
Select All Rows from <object_name> | This menu item will define the join type (i.e. INNER or OUTER) by selecting all rows from the left side of the join line,
Select All Rows from <object_name> | This menu item will define the join type by selecting all rows from the right side of the join line,
Properties... | This menu item will display the Join Line Properties dialog for further specifics about the relation (see Figure 1.7 for more details),

Note that the Select All Rows from <object_name> are toggle menu items, where the object_name is the name of the object at the respective start or end of the join line. The first menu item from these Select All Rows from <object_name> menu items defines the join to be LEFT OUTER JOIN if checked, while the second will make the join to be RIGHT OUTER JOIN. If neither of these menu items is checked then the join becomes INNER JOIN.

Figure 1.7. Showing the Join Line Properties dialog

The Join Line Properties dialog may be used for further specifics about the join relation. For example, defining the operator between the related fields, as well as the join type (i.e. INNER, OUTER, LEFT, RIGHT or FULL).

The following are all possible operators between related fields:

{ =, <>, <, <=, >, >= }.

The default operator is the equality operator, in which case the diamond node of the join line does not show the operator symbol. In all other cases the operator symbol will be shown in this diamond node.


The Columns pane

The Columns pane is responsible for many factors involved in the creation of queries via such visual interface (see Figure 1.8 for more details). Some of these factors are as follows:

· Selecting fields in the SQL statement,

· Selecting field-columns, on which SQL functions may be applied,

· Defining query criteria, sorting and grouping on fields easily accessible via combo-box controls,

As well as other factors which together with the Relations and SQL pane simplifies the creation of complex queries.

Figure 1.8. Showing the Columns pane with the SQL functions toolbar

The SQL functions toolbar contains all SQL functions necessary to perform advance queries. This toolbar is an indispensable tool for novice users that frequently need SQL language books or help files to write SQL commands. Furthermore, this tool universalizes the SQL language so that users may create advance queries in Oracle, even if they only know MS-Access or MS-SQL Server (or visa versa). Essentially the SQL functions are categorized into buttons, with each button being a combo-box listing related SQL functions (see Figure 1.9). We list next these combo-box buttons in the order from left to right:

· Functions for concatenation of fields into string,

· Functions for masking concatenated fields,

· Aggregate functions including: AVG, COUNT, MAX, MIN, SUM, STDEV, VAR,

· String functions including: CHARINDEX, DIFFERENCE, LEFT, LEN, LOWER, LTRIM, PATINDEX, REPLACE, QUOTENAME, REPLICATE, LEFTPAD, RIGHTPAD, REVERSE, RIGHT, RTRIM, SOUNDEX, SPACE, STR, STUFF, SUBSTR, TRIM, UPPER,

· Numeric functions including: ABS, CEILING, FLOOR, ROUND, TRUNCATE, SIGN,

· Date part functions including: YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, MILLISECOND,

· Date name functions including: YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, MILLISECOND,

· Conversion to char functions,

· Conversion to number functions,

· Conversion to date functions,

· Null case function,

· Simple case function,

Figure 1.9. Showing the concatenation to string options of the SQL functions toolbar


The SQL pane

The SQL pane is by itself a complete and fully syntax colored editor, with cut, copy, paste, find, replace, multiple do/undo and other operations (see Figure 1.10). The actions performed in the Relations and Columns panes affect the content of the SQL pane producing an SQL statement, which eventually may be executed against the database defined by the currently active database connection.

Using the SQL functions toolbar the user may also insert SQL functions with arguments taken from the currently selected field-columns in the Columns pane. These actions will simply insert string segments into the SQL pane at the current location of the cursor, comprising of the desired SQL function syntax with the selected field names as arguments.

The user may enter SQL commands directly into the SQL pane without the use of the other panes, in which case the user needs to synchronize all panes via the Check SQL Syntax toolbar button.

Figure 1.10. Showing the SQL pane with syntax coloring


Creating a QB

Creating a QB-Project

To start with Office-QB application, you will need to create a QB-project (Query Builder project). A QB-project will organize your work into a user-specified directory on your system. In this way saving you time and effort by avoiding the undesirable alternative of query files scattered all over your system.
To create a project run the Office-QB application, and from the File menu select New Project. This will open the New Project dialog (see Figure 1 for more details),

Figure 1. Showing the New Project dialog,

In the default New tab of this dialog, enter the Project name field and set the Project storage location via the button next to the edit-box. Note that this 3-dot button will open the standard Windows Browse for Folder dialog, in which you may select a path for project storage files. After entering the project name and path, click on the OK button to create the project. This will display the project as an MDI child window in the main window of the Office-QB application, with the Connections pane containing only the root node, namely the Databases node (note that the Connections pane is located on the upper-right corner of the main window).

The Recent tab-view
 If you want to get back to an existing QB-project, you may call the New Project dialog just like the way you used in creating new project, but this time you need to click on the Recent tab of the dialog. In this case the dialog has the appearance displayed in Figure 2 below:

Figure 2. Showing the New Project dialog with the Recent tab selected,

Note that the Recent tab of this dialog resembles a project explorer, in the sense that, when selecting a recently created project, the project's content is displayed in the tree-view control on the right side of the dialog. In this way you may explore the project contents before opening it in the main window of Office-QB. Note that since the list of the Recent tab is stored in the system's registry, sometimes the links of this Recent Projects list-box need to be refreshed. This may be done via the Update button.
To open an existing project, you may select the project from the Recent Projects list-box and then click the OK button.


Creating a Database Connection

In this section we will describe how to define or modify a database connection for any database for which an OLE DB provider is properly installed on the user’s system. In general, the method of database connection will depend on the backend database engine and their respective supported OLE DB providers.

Using the popup menu on the Databases root-node of the Connections pane, we select the New menu item. This will open the New Database Connection dialog (see Figure 1.11 for more details).

Figure 1.11. Showing the New Database Connection dialog

The user must then select a data access method using one of the items in the Data Access Type list-box. And enter a name for the connection in the Connection Name text-box.

When the user OK-s this dialog, depending on the data access type selected different type of data access dialogs may be prompt.

In general, the Data Access Type options are as follows:

· OLE DB Dynamic Data Access using ADO, which will prompt the Data Link Properties dialog for an OLE DB provider and further properties about the data source (see Figure 1.12),

· OLE DB UDL Link-file using ADO, which will prompt the Select Data Link File dialog for the user to specify the location and name of an existing UDL data link file (see Figure 1.13),

· ODBC Machine Data Source using DSN, which will prompt the Select Data Source dialog with the Machine Data Source tab active, for the user to select an ODBC machine data source name (see Figure 1.14),

· ODBC File Data Source using DAO, which will prompt the Select Data Source dialog with the File Data Source tab active, for the user to select a data source file (see Figure 1.14).

Figure 1.12. Showing the Data Link Properties dialog

In the case of OLE DB Dynamic Data Access via ADO, the user is prompt with the Data Link Properties dialog to select the OLE DB Provider from the Provider tab, as shown in Figure 1.12 on the left.

For example, for MS-SQL Server the user must select the Microsoft OLE DB Provider for SQL Server item from the list of providers. For Oracle the best option is the native Oracle Provider for OLE DB. For MS-Access one may use the Microsoft Jet 4.0 OLE DB Provider, while for all other ODBC compliant databases the Microsoft OLE DB Provider for ODBC may be used.

After selecting a proper OLE DB Provider, the user may click the Next >> button which will switch the tab to Connection, to enter more details about the connection, such as: data source name / location of data / server name, server logon information, initial catalog or database name.

In the case of OLE DB UDL Link-file using ADO, it is assumed that the user wants to use an already existing UDL-file (Universal Data Link), which stores the connection information. Therefore using the Select Data Link File dialog the user may locate and open the UDL-file (see Figure 1.13 on the right).

Note that once the connection is defined in the Connections pane, the connection information is transferred from this UDL-file into the project directory for the current instance of the query builder.

Figure 1.13. Showing the Select Data Link File dialog

For the options using ODBC through a machine data source name or data source file, the user is prompt with the Select Data Source dialog (see Figure 1.14).

Figure 1.14. Showing the Select Data Source dialog

In the Select Data Source dialog the user may select an existing machine DSN or data source file, respectively from the Machine Data Source or File Data Source tabs.

Note these ODBC data access options may be used when the backend data source does not support any adequate OLE DB Provider. In general, we recommend the use of the OLE DB Dynamic Data Access or OLE DB UDL Link-file options utilizing ADO.

In all cases when the connection information is specified and the dialogs submitted, a new connection node is added to the Databases root-node.

To modify an existing connection node the user may use the Properties menu item from the popup menu obtained via the right-mouse button click method on the selected node itself. This will display the connection information dialog corresponding to the data source and data access type for the selected connection node, with which the user may modify the data source name / location of data / server name, server logon information, initial catalog or database name.


The Data Browser

The DB Browser pane is used for displaying the database table, view and stored-procedure objects of the currently active connection (see Figure 1.4). To display a particular connection’s objects in the DB Browser, the user may double-click on that connection’s node in the Connections pane. This will update (or refresh) the DB Browser displaying the database object structure.

Figure 1.4. Showing the DB Browser,

Essentially, there are the Tables, Views and Stored Procedures folders under the database root folder in the DB Browser pane. The Tables folder will contain the table objects of the database, with each table object node containing the fields as child nodes. Similarly, the Views folder will contain the view objects of the database, with each view object node containing the fields as child node. These objects may be drag-dropped into the Relations pane, where further field relation manipulations may be performed.

The Stored Procedures folder will contain the stored procedures of the database, with each stored procedure object node containing the arguments and return data types as child nodes. These stored procedures may not be drag-dropped into the Relations pane, and they preliminarily serve the user to display structural information about the stored procedure.

In addition to the structural information about the fields and arguments of the objects displayed in the DB Browser, we can also obtain detailed information about these objects via the Get Details menu item of the popup menu on the DB Browser pane. In fact, the Get Details command will display the database script via a textbox window for any selected object in the DB Browser pane. This Get Details function makes the DB Browser a very powerful data view tool generating object scripts much more quickly and efficiently than, for example, the MS-SQL Server script generator.


Creating and Saving a Query

To create a new query for a particular connection one may use the New menu item from the popup menu (see Figure 1.3), with the right-mouse button click on that connection node. This will create a query child node under this connection node, and will open this new query into the query builder panes. By default any new query has the name “New Query”, which may be modified by a second left-mouse click on the query node making the node label editable.

Figure 1.3. Showing the popup menu,

The user may open an existing query into the query builder by double-clicking on the node corresponding to that query. Only one query may be open for edit at the same time (i.e. similar to SDI documents).

Alternatively, the user may create a new query based on an existing query by simply copying the existing query via the Copy menu item of the right-mouse popup menu, and then using the Paste menu item of this same popup menu triggered on the connection node, the user may paste a copy of the existing query with the name prefixed by “Copy_of_” string.

To save modifications to a query the user may use the Save Query menu item under the File menu. This will store the query changes in the binary file for the connection, over which the query is defined. Note that each connection has a different binary file with the name derived from the name of the connection node, for example: if the connection node name is MyConn, the binary filename will be MyConn.con. In general, the file extension CON will be used for connection binary files.


Defining Relations and Joins

The Relations pane is used to relate similar fields between table, view or stored-procedure objects (see Figure 1.6 for more details). Essentially, objects are drag-dropped from the DB Browser pane into the Relations pane, where they are displayed as lists of fields with check-boxes. Fields between a pair from these objects in the Relations pane may be related via drag-drop method, in this way, defining a relation or a join between them.

Figure 1.6. Showing the Relations pane with four table objects,

When a join is defined via drag-drop between two objects in the Relations pane, a join line is drawn with a diamond at the middle. Moreover the proper segment of the command is inserted into the SQL pane in the FROM-clause of the SQL command. In the case, when the related fields have an enforced constraint relation between foreign key and primary key, this line will have respectively the infinity and key icons at the endpoints. Otherwise there will be no icons at the endpoints of the join line.

The user may use the right-mouse click on the diamond icon of the join to popup the menu, from which selecting the Properties menu item, the Join Line Properties dialog may be displayed. Observe that if in this dialog the join operator is selected to be other than the equality symbol, then this operator will be shown in the diamond icon. Moreover, the join type is defined via the Include rows section of the dialog, as a result of which the diamond shape is complemented with brackets on the left or the right side.

Figure 1.15. Showing the diamond shape and brackets for join types

For example, see Figure 1.15, which shows the join lines for INNER, LEFT OUTER, and RIGHT OUTER join types.

The join type may be also selected via the Select All Rows from menu items of the popup menu (triggered over the diamond).

Sometimes when the user enters join conditions via the SQL pane directly, there may be situations when this join condition will have an expression instead of a field on one side of the equation (or inequality). In these cases the join line is drawn from a field of one object to the title bar of the other object in the Relations pane (corresponding to the expression), with the diamond icon having the “f*” label. Similarly, if both sides of the join condition equation contain expressions rather than simple fields, then the join line is drawn between the title bars of the objects.

In addition to tables and views that are drag-dropped into the Relations pane from the DB Browser pane, the Relations pane may also contain nested SELECT-statements, that are entered directly as segments of the FROM-clause of the SQL command in the SQL pane. For example the following SQL command entered directly into the SQL pane will result into two objects:

    SELECT *
    FROM	orders INNER JOIN
            (SELECT * FROM customers) AS cust
        ON orders.customer_id = cust.customer_id

namely, orders and cust objects, where the field listing of cust object will be the field structure of the customers table. Note that all nested SELECT-statements must have object aliases. Moreover, if object alias is furnished then the corresponding object name in the Relations pane will be this alias, otherwise the actual object’s name is used.

Finally, the Relations pane may be used to select fields from the objects by simply checking the check-boxes corresponding to these field items.


Selecting Fields and Defining Criteria

The Columns pane consists of the field columns grid and the SQL function toolbar sections (see Figure 1.8 for details).

Figure 1.8. Showing the Columns pane with several selected fields,

The field columns grid consists of the following columns:

Column | This grid-column is a combo-box control (with edit-box style), which initially contains a combined list of all field names derived from all the objects in the Relations pane.
Alias | This grid-column is an edit-box control, and is used for optional alias name entry for the content of the Column grid-column (by default it is empty).
Object | This grid-column is a combo-box control (with edit-box style), which initially contains a list of the names of all objects in the Relations pane.
Object Alias | This grid-column is an edit-box control, and is used for optional object alias name entry for the content of the Object grid-column (by default it is empty).
Output | This grid-column is a check-box control, and is used to indicate whether the field is to be outputted or not.
Sort Type | This grid-column is a combo-box control containing the choices Ascending, Descending and No-Sort.
Sort Order | This grid-column is a combo-box control containing the possible order choices depending on the currently selected sort fields. For example, if we have 3 sort fields selected, then the choices will be 1, 2, 3, and No-Sort.
Group By | This grid-column is a check-box control, and is used to indicate whether the field is used as aggregate.
Criteria | This grid-control is an edit-box control, and is used to enter criteria for the query.
Or... | This grid-control is an edit-box control, and is used to enter criteria for the query.

Note that for a new field-row in the Columns pane, initially the Column combo-box lists a combined list of all field names derived from all the objects in the Relations pane, but if the Object grid-column is specified this list will be restricted to only the field names of the selected object. Similarly, the Object combo-box initially lists the object names of all objects in the Relations pane, but when the Column grid-column is not empty, only object names that have this Column value as a field name will be listed.

The Columns pane is mainly used to define criteria for the currently open query. In the most standard situation the user may define query criteria by selecting a field from the Column grid-column, and then entering some value under the Criteria grid-column. This value may be preceded with one of the following operators =, <, >, <=, >=, !<, !>, !=, <>, IS, IS NOT, NOT, IN, LIKE, and BETWEEN. Note that the default operator is ‘=’, so that if the user does not specify an operator the equality is assumed.

Observe that when the user specifies criteria via direct entry into the WHERE-clause of the SQL statement in the SQL pane, some of the conditions may be considered expressions and may entirely be put under the Column grid-column. This is specially the case when a criteria condition cannot be represented in the following standard form:

<field_name> <operator> <value>

Also, note that any thing following the LIKE operator will be automatically put inside quotes (if it is not already inside quotes). The syntax of the LIKE-expression, which may be any regular expression, depends on the back-end database engine. For example, in the case of the MS-SQL Server the syntax is outlined below:

Wildcard character | Description | Example
% | Any string of zero or more characters. | WHERE lastname LIKE ‘Har%’ will include all last names which start with “Har”, for example Harry, Harold, etc.
_ (underscore) | Any single character. | WHERE lastname LIKE ‘H_r_ld’ will include all last names which start with “H” and end with “ld”, with three middle characters separated with an “r”, for example Herald, Harold, etc.
[] | Any single character within the specified range ([a-f]) or set ([abcdef]). | WHERE lastname LIKE ‘H[ae]rald’ will include all last names which start with “H” and end with “rald”, with one character in the set {a, e}, for example Herald, Harald, etc.
[^] | Any single character not within the specified range ([^a-f]) or set ([^abcdef]). | WHERE lastname LIKE ‘H[^a]rald’ will include all last names which start with “H” and end with “rald”, with one character between them that is not an “a”, for example Herald, Hyrald, but not Harald.

We illustrate next the usage of the grid in the Columns pane. Essentially, the vertical direction represents conjunction (i.e. AND), while the horizontal direction represents disjunction (i.e. OR). For example, if we want to query all the contact names of the customers whose last names start with “John” and first name is “Ben”, or whose last name is “Benjamin” and the first name starts with “Frank”, then we may enter the following entries in the cells of the Columns grid:

Column | Criteria | Or… | Or…
last_name | LIKE ‘John%’ | = ‘Benjamin’ |
first_name | = ‘Ben’ | LIKE ‘Frank%’ |

which results into the following WHERE-clause in the SQL pane:

WHERE (last_name LIKE ‘John%’ AND first_name = ‘Ben’)

              OR (last_name = ‘Benjamin’ AND first_name LIKE ‘Frank%’)

Observe that we may include several disjunctions under one Criteria/Or… grid-cell. For example, the following WHERE-clause

WHERE ((last_name LIKE ‘John%’ OR last_name LIKE ‘Jon%’)

AND first_name = ‘Ben’)

              OR (last_name = ‘Benjamin’ AND first_name LIKE ‘Frank%’)

may be represented in the following way:

Column | Criteria | Or… | Or…
last_name | LIKE (‘John%’ OR ‘Jon%’) | = ‘Benjamin’ |
first_name | = ‘Ben’ | LIKE ‘Frank%’ |

Note that more Or… columns may be added as needed and that any criteria condition for a query may be represented into this grid of the Columns pane, given that complex expressions involving SQL functions may be entirely included in the Column grid-column not using the Criteria/Or… grid-columns.

Finally, other than specifying query criteria, sorting and grouping, the Columns pane may also be used as a collection of fields on which the SQL functions toolbar may be applied. We will consider these SQL functions in the next section.


Using SQL functions

The SQL functions toolbar features include all the SQL functions that are needed for the creation of complex queries. It is destined to help novice SQL users to get started without referencing various reference books for the syntax of the SQL functions supported in the particular backend database engine in use. These functions are categorized into 10 drop-down combo-box buttons plus two buttons as is shown in Figure 1.16. In this section we will outline their general usage.

Figure 1.16. Showing the SQL functions toolbar with categorization

We illustrate now the general usage of the SQL functions toolbar. Essentially, there are two ways to use these SQL functions:

· Select fields in the Columns pane and then apply the SQL function,
· Apply the SQL function without any selection in the Columns pane,

The first method will substitute some arguments of the selected function by the selected field names and then insert the syntax string into the SQL pane at the current mouse-cursor location. While the other method will insert the syntax string into the SQL pane, with all arguments of the SQL function being dummy variables. Note that these dummy variables must be replaced with proper values after the insertion. In general, all the dummy arguments of an inserted function must be replaced by either a field name or some constant value.

Figure 1.17. Showing the SQL function application

For example to apply the Left SQL function to the ContactName field, we first position the mouse-cursor in a desired location in the SQL pane, then select the ContactName field-row in the Columns pane (see Figure 1.17 for more details). Then we drop-down the String functions toolbar button and select the Left function option from the drop-down list. As a result of these operations the following string will be inserted into the current mouse-cursor location in the SQL pane:

LEFT(Customers.ContactName, nLen)

where nLen is a dummy variable and therefore must be replaced by some number. Note that when the focus of the active pane becomes the Relations pane, the Columns pane is updated so that a new field-row is appended as a result of these operations.


Functions for concatenation of fields into string

For these functions the user may select multiple rows in the grid of the Columns pane, and then apply any of the options in the drop-down combo-box list. In general, all selected fields are first converted into string data type and then concatenated in the pattern defined by the selected function option. We list next these function options:

Fld1+“, ”+...+“, ”+FldN | Convert fields to string, then concatenate with comma+space between fields,
Fld1+“,”+...+“,”+FldN | Convert fields to string, then concatenate with comma between fields,
Fld1+“ ”+...+“ ”+FldN | Convert fields to string, then concatenate with space between fields,
Fld1+...+FldN | Convert fields to string, then concatenate,
Fld1+“-”+...+“-”+FldN | Convert fields to string, then concatenate with hyphen between fields,
Fld1+“ - ”+...+“ - ”+FldN | Convert fields to string, then concatenate with space+hyphen+space between fields,
Fld1+“, ”+Fld2+“ ”+Fld3 | This is for “LastName, Firstname MI” format,
Fld1+“ ”+Fld2+“. ”+Fld3 | This is for “FirstName MI. Lastname” format.


Functions for masking concatenated fields

These function options may be applied on multiple fields selected in the grid of the Columns pane. They implement a combination of conversion, concatenation and mask formatting. We list next all the mask format functions:

Phone [LO] : 1-999-999-9999 | Long phone number with local and area codes, the concatenation is assumed to be an 11-character string.
Phone [LO] : 1 999-999-9999 | Long phone number with local and area codes, the concatenation is assumed to be an 11-character string.
Phone [AC] : (999) 999-9999 | Phone number with area code, the concatenation is assumed to be 10-character string.
Phone [AC] : 999-999-9999 | Phone number with area code, the concatenation is assumed to be 10-character string.
Phone [AC] : 999.999.9999 | Phone number with area code, the concatenation is assumed to be 10-character string.
Phone [ACX]: (999) 999-9999 (ext 00000) | Phone number with area code and extension, the concatenation is assumed to be more than 10-character string.
Phone [SH] : 999-9999 | Short phone number, the concatenation is assumed to be 7-character string.
Area Code : (999) | Area code, storage is assumed to be 3-character string
Extension : (ext 00000) | Extension
Extension : -X00000 | Extension
SS# : 999-99-9999 | Social security number, storage is assumed to be 9-character string.
Fed ID : 99-9999999 | Federal ID, storage is assumed to be 9-character string.
Zip Code : 99999-9999 | Zip code, storage is assumed to be 9-character string.
Bank Acc : 99999-99999 | Bank account number, storage is assumed to be 10-character string.
Cred Card : 9999-9999-9999-9999 | Credit card number, storage is assumed to be 16-character string.
ISBN : 9-99-999999-9 | ISBN, storage is assumed to be 10-character string.
ISBN : 9-999-99999-9 | ISBN, storage is assumed to be 10-character string.
ISBN : 9-9999-9999-9 | ISBN, storage is assumed to be 10-character string.
ISBN : 9-99999-999-9 | ISBN, storage is assumed to be 10-character string.
ISBN : 9-999999-99-9 | ISBN, storage is assumed to be 10-character string.


Aggregate functions

The aggregate functions apply to a single field selection in the grid of the Columns pane. We will list all aggregate function options with the actual string that is inserted into the current cursor location in the SQL pane. Note that we will only give the MS-SQL Server versions of these insert strings leaving the Oracle or other versions to the user to experiment.

Option name in the popup list | Inserted string (where Fld is the selected field)
Average | AVG(Fld)
Average Distinct | AVG(DISTINCT Fld)
Count | COUNT(Fld)
Count Distinct | COUNT(DISTINCT Fld)
Maximum | MAX(Fld)
Minimum | MIN(Fld)
Standard Deviation | STDEV(Fld)
Sum | SUM(Fld)
Sum Distinct | SUM(DISTINCT Fld)
Variance | VAR(Fld)


String functions

The string functions may apply on one or two selected fields from the grid of the Columns pane. We list these functions next (again, we only specify the MS-SQL Server version of the interpretation of the functions):

Option name in the popup list | Inserted string (where Fld is the selected field)
CharIndex | CHARINDEX(sSearchFor, Fld, nPos)
Difference | DIFFERENCE(Fld1, Fld2)
Left | LEFT(Fld, nLen)
Length | LEN(Fld)
Lower | LOWER(Fld)
LTrim | LTRIM(Fld)
PatIndex | PATINDEX(sPattern, Fld)
Replace | REPLACE(Fld, sSearchFor, sReplace)
QuoteName | QUOTENAME(Fld,']')
LeftPad | REPLICATE(' ', nLen-LEN(Fld))+Fld
RightPad | Fld+REPLICATE(' ', nLen-LEN(Fld))
Reverse | REVERSE(Fld)
Right | RIGHT(Fld, nLen)
RTrim | RTRIM(Fld)
Soundex | SOUNDEX(Fld)
Space | SPACE(nLen)
Str | STR(Fld, nLen, nDecimal)
Stuff | STUFF(Fld, nStart, nLen, sStuffWith)
SubString | SUBSTRING(Fld, nStart, nLen)
Trim | LTRIM(RTRIM(Fld))
Upper | UPPER(Fld)


Numeric functions

The numeric function are listed next (we only specify the MS-SQL Server version of the interpretation of the functions):

Option name in the popup list | Inserted string (where Fld is the selected field)
Absolute Value | ABS(Fld)
Ceiling | CEILING(Fld)
Floor | FLOOR(Fld)
Round | ROUND(Fld, nLen, 0)
Truncate | ROUND(Fld, nLen, 1)
Sign | SIGN(Fld)


Date Part functions

The date part functions are listed next (we only specify the MS-SQL Server version of the interpretation of the functions):

Option name in the popup list | Inserted string (where Fld is the selected field)
Year | DATEPART(yy, Fld)
Quarter | DATEPART(qq, Fld)
Month | DATEPART(mm, Fld)
Day of Year | DATEPART(dy, Fld)
Day | DATEPART(dd, Fld)
Week | DATEPART(ww, Fld)
Weekday | DATEPART(dw, Fld)
Hour | DATEPART(hh, Fld)
Minute | DATEPART(mi, Fld)
Second | DATEPART(ss, Fld)
Millisecond | DATEPART(ms, Fld)


Date Name functions

The date name functions are listed next (we only specify the MS-SQL Server version of the interpretation of the functions):

Option name in the popup list | Inserted string (where Fld is the selected field)
Year | DATENAME(yy, Fld)
Quarter | DATENAME(qq, Fld)
Month | DATENAME(mm, Fld)
Day of Year | DATENAME(dy, Fld)
Day | DATENAME(dd, Fld)
Week | DATENAME(ww, Fld)
Weekday | DATENAME(dw, Fld)
Hour | DATENAME(hh, Fld)
Minute | DATENAME(mi, Fld)
Second | DATENAME(ss, Fld)
Millisecond | DATENAME(ms, Fld)


Conversion to char functions

We outline now the conversions to strings from all the data types (we only specify the MS-SQL Server version of the interpretation of the functions):

Option name in the popup list | Inserted string (where Fld is the selected field)
Float [6-digit] | CONVERT(VARCHAR, Fld, 0)
Float [8-digit Scientific] | CONVERT(VARCHAR, Fld, 1)
Float [16-digit Scientific] | CONVERT(VARCHAR, Fld, 2)
Other Numeric | CONVERT(VARCHAR, Fld)
Currency [9999.99] | CONVERT(VARCHAR, Fld, 0)
Currency [9,999.99] | CONVERT(VARCHAR, Fld, 1)
Currency [9999.9999] | CONVERT(VARCHAR, Fld, 2)
Date-Time [mm/dd/yy] | CONVERT(VARCHAR, Fld, 1)
Date-Time [mm/dd/yyyy] | CONVERT(VARCHAR, Fld, 101)
Date-Time [yy.mm.dd] | CONVERT(VARCHAR, Fld, 2)
Date-Time [yyyy.mm.dd] | CONVERT(VARCHAR, Fld, 102)
Date-Time [dd/mm/yy] | CONVERT(VARCHAR, Fld, 3)
Date-Time [dd/mm/yyyy] | CONVERT(VARCHAR, Fld, 103)
Date-Time [dd.mm.yy] | CONVERT(VARCHAR, Fld, 4)
Date-Time [dd.mm.yyyy] | CONVERT(VARCHAR, Fld, 104)
Date-Time [dd-mm-yy] | CONVERT(VARCHAR, Fld, 5)
Date-Time [dd-mm-yyyy] | CONVERT(VARCHAR, Fld, 105)
Date-Time [dd mon yy] | CONVERT(VARCHAR, Fld, 6)
Date-Time [dd mon yyyy] | CONVERT(VARCHAR, Fld, 106)
Date-Time [Mon dd, yy] | CONVERT(VARCHAR, Fld, 7)
Date-Time [Mon dd, yyyy] | CONVERT(VARCHAR, Fld, 107)
Date-Time [mm-dd-yy] | CONVERT(VARCHAR, Fld, 10)
Date-Time [mm-dd-yyyy] | CONVERT(VARCHAR, Fld, 110)
Date-Time [yy/mm/dd] | CONVERT(VARCHAR, Fld, 11)
Date-Time [yyyy/mm/dd] | CONVERT(VARCHAR, Fld, 111)
Date-Time [yymmdd] | CONVERT(VARCHAR, Fld, 12)
Date-Time [yyyymmdd] | CONVERT(VARCHAR, Fld, 112)
Date-Time [mon dd yyyy hh:miAM] | CONVERT(VARCHAR, Fld, 0)
Date-Time [mon dd yyyy hh:mi:ss:mmmAM] | CONVERT(VARCHAR, Fld, 9)
Date-Time [dd mon yyyy hh:mm:ss:mmm] | CONVERT(VARCHAR, Fld, 13)
Date-Time [hh:mm:ss] | CONVERT(VARCHAR, Fld, 8)
Date-Time [hh:mi:ss:mmm] | CONVERT(VARCHAR, Fld, 14)
Date-Time [yyyy-mm-dd hh:mi:ss] | CONVERT(VARCHAR, Fld, 20)
Date-Time [yyyy-mm-dd hh:mi:ss.mmm] | CONVERT(VARCHAR, Fld, 21)
Date-Time [yyyy-mm-ddThh:mm:ss:mmm] | CONVERT(VARCHAR, Fld, 126)


Conversion to number functions

We outline next all the conversion forms to numeric data type (we only specify the MS-SQL Server version of the interpretation of the functions):

Option name in the popup list | Inserted string (where Fld is the selected field)
Integer [99999999] | CAST(Fld AS NUMERIC)
Decimal [99999999.9] | CAST(Fld AS NUMERIC(38,1))
Decimal [99999999.99] | CAST(Fld AS NUMERIC(38,2))
Decimal [99999999.999] | CAST(Fld AS NUMERIC(38,3))
Decimal [99999999.9999] | CAST(Fld AS NUMERIC(38,4))
Decimal [99999999.99999] | CAST(Fld AS NUMERIC(38,5))
Decimal [99999999.999999] | CAST(Fld AS NUMERIC(38,6))


Conversion to date functions

We outline next all the conversion forms to date data type (we only specify the MS-SQL Server version of the interpretation of the functions):

Option name in the popup list | Inserted string (where Fld is the selected field)
Date-Time | CAST(Fld AS DATETIME)
Small Date-Time | CAST(Fld AS SMALLDATETIME)
Time-Stamp | CAST(Fld AS TIMESTAMP)


Null Case function

This toolbar button will insert the syntax for Null Case conditional in the SQL pane, for example in the case of MS-SQL Server this string is as follows:

COALESCE(Fld, vNullCaseValue)

where Fld is the selected field in the Columns pane and vNullCaseValue must be replaced with a proper value, which will handle the case when the field Fld is NULL.

Note that in Oracle this Null Case function is as follows:

NVL(Fld, vNullCaseValue)


Simple Case

This toolbar button will insert the syntax for Case conditional in the SQL pane, for example in the case of MS-SQL Server this string is as follows:

CASE Fld

WHEN when_expression_1 THEN result_expression_1

ELSE else_expression END

where Fld is the selected field on which the case will be defined, while the dummy-variables when_expression_1, result_expression_1 and else_expression must be edited after this insertion into the SQL pane. Note that the WHEN-phrase may be more than one case, which may be added by the user after the insertion.

Note that the corresponding simple case string in the case of Oracle may utilize the DECODE function, for example, in the following format:

DECODE(Fld, when_expression_1, result_expression_1, else_expression)


Defining SQL Manually

The user may define a query directly via the SQL pane, using this pane as an SQL editor. To check the validity of the SQL text that is entered manually into the SQL pane, the user may habitually use the F5 short-key to trigger the Check SQL Syntax button (or alternatively click this button). This will check the SQL text against the backend database defined by the current data access connection. One may also copy / paste text from external programs into the SQL pane, for example the user may use the Get Details command over the DB Browser pane, from which text may be copied to be pasted directly into the SQL pane.

Direct entry into the SQL pane is indispensable when the query is one of the following types:

· INSERT SQL statement,

· UPDATE SQL statement,

· DELETE SQL statement,

We should emphasize that in the current version of Office-QB application, the Relations and Columns panes may be useless for such SQL statements, and therefore Office-QB interface may be used only as an SQL editor.

Office-QB used as an SQL editor, also supports Data Definition Language SQL commands, for example you may use CREATE, DROP, ALTER, etc.


Running the Query and the Result tab

After creating a query via the Office-QB visual interface, the user may execute the query by clicking the blue arrow button in the toolbar on the right of the tabs, or use the Execute menu item under the Command menu or alternatively push the F8 short-key on the keyboard. When executing a query, the resulting records will be displayed in the Result tab-view. Note that in the case when a query needs a long time to execute, the arrow button becomes disabled and the red stop button becomes enabled, so that the user may interrupt the execution process. Therefore the execution process in the Office-QB is multi-threaded and asynchronous, with a fast response to halt the process whenever the user clicks the stop button.

Sometimes some queries require a long time to execute, for which the query timeout parameter needs to be changed. To change the query timeout parameter in Office-QB, from the Command menu select the Options menu item. This will display the Options dialog, where you can modify the query timeout parameter.


Exporting and Printing

The export and print features apply to both the content of the SQL pane and the content of the Result tab-view. In particular, using copy/paste operations the user may copy the content of the SQL pane to the clipboard, and then paste this content into another text editor application. The user may also print the content of the SQL pane to get a hardcopy of the query text. In addition to the export and print operations there are Find and Replace operations that may be applied on the content of both the SQL pane and Result tab-view.

In the case of the Result tab-view, the copy/paste operation is more useful, since any segment of the result grid may be copy/paste into other MS-Office applications (e.g. Excel, Word, etc.). The user may also print the result of a query via the Print menu item.


Storage and File Processing Support

Office-QB has an internal storage mechanism that consists of a PRJ-file for each project, and CON-file for each connection node in the project. The PRJ-file essentially stores path information for the project, while a CON-file will store the following information:

· The data access connection information,

· The query information for queries created under the connection node,

We should emphasize that the CON-files are binary files and should not be edited by external text editors. The data access connection information stored in a CON-file includes all necessary data access information needed to connect to the backend database, including password (if password save feature is used in the definition of the data access connection). Note that all passwords are stored in an encrypted manner, providing some degree of security.

A CON-file stores all the saved queries under a connection node. In general, only the text of the SQL pane is stored, from which the content of the other panes is derived whenever the query is opened. Each data access connection node displayed in the Office-QB will correspond to a separate CON-file, so that under a project subdirectory there may be several CON-files, one for each connection node.


Transferring Data Between Connections

Office-QB has a powerful data transfer feature, which may be used to transfer the resulting data of the currently active open query into any database defined by the connections in the Connection pane. There are two options for importing data into a selected connection, namely:

· Inserting the result of the active query into a new table in the destination database defined by the selected connection, in which case the new table structure is created before import.

· Appending the result of the active query into an existing table in the destination database defined by the selected connection.

To transfer data between connections, first open a query in the source connection defining the source database. This query must be a SELECT statement, in particular a query that returns records. Test your query and execute it against the backend database to check or see that the data is to your liking. Then from your application’s menu, select either Insert into new table or Append to existing table menu item under the menu Import Result. In either case, the Import dialog is displayed for further selection or specification of destination connection, database and table name (see Figure 1.18 for more details).

Figure 1.18. Showing the Import dialog

The Import dialog allows import into any of the existing connections in the Connections pane (i.e. all CON-files in the QB-project). Therefore, a good habit towards the aim of data transfer, is to define connections for each and all databases that interests you in your work. Given this then, the Import dialog makes it very easy to transfer data from one connection into the other. Essentially, all you have to do is to specify the destination connection from the Select destination connection list-box, check the Import Option, then select the database from the Database Name combo-box, and finally either define a table name (if you selected Insert into new table option in the Import Option) or select a table from the list of existing tables in the selected database (if you selected Append to an existing table option in the Import Option).

Once the proper values in the Import dialog is selected, you may click the Import button to run the import process. The import process will create the table structure if necessary, and then import the collection of all records resulting from the active query into the destination table.

Note that when importing data into a new table in the destination database, the structure of the destination table (which was created as a result of the import process) will not have any field constraints.


Output to HTML or Mail Query

Office-QB application comes with a built-in HTML output feature, which gives the user the ability to output the result of any query directly in HTML format. Essentially, in HTML format the output of the query result is imbedded in HTML-table. Moreover the widths of the columns of the HTML output are controlled by the widths of the field-columns of the grid of the Result tab-view. So that the user may adjust the column-widths of the HTML output by resizing the grid-columns in the Result tab-view. To output a query in HTML format, first define the query and execute it. When the result of the query is displayed in the Result tab-view, adjust the widths of the field-columns in the grid to proper sizes, and from the Command menu select the Output Result as HTML menu item. This will generate the HTML output and display it in the default web browser on your system.

In Office-QB application, one can directly e-mail the query result via the Mail Query menu item of the Command menu. This feature is similar to the HTML output, but will display the output in Outlook Express New Message dialog, from where the user may send the e-mail containing the HTML output of the query result (see Figure 7 for more details):

Figure 7. Showing the e-mail New Message dialog containing the output of the query result.


The Option dialog

The Options dialog

In the Options dialog, the user may set some global parameters for the Office-QB application as a whole. There are two tabs in this dialog (see Figure 10 and 11 for more details).

· The Query tab, which contains the Query Timeout and the Editable query results controls,

· The HTML/MIME tab, which contains some options for the HTML/MIME output format,

Figure 10. Showing the Query tab of the Options dialog,
Figure 11. The HTML/MIME tab of the Options dialog,

The Query Timeout parameter controls the timeout delay interval for a query execution. That is, if a query takes a longer time then specified to return records, then Office-QB will stop the execution and report a query timeout expire error message. For most purposes a value of 120 seconds is enough time to execute queries with millions of records.
The Editable query results check-box controls the edit capability of Office-QB in the Result tab-view. To be able to edit the result of queries you must have this check-box checked.

The Include description section check-box of the HTML/MIME tab controls the inclusion of an empty Description section in the HTML output. This is specially useful in the case of e-mail output, where the user may enter some comments about the query result.
The Include SQL command text check-box controls the inclusion of the SQL command text for the query, above the query result output.
The Include ELS-logo in the footnotes check-box controls the inclusion of the Office-QB logo as a footnote at the bottom of the query result output.


Introduction to ELS

Introduction to ELS-QB ActiveX component

ELS-QB ActiveX is a query builder component which gives your end-users the ability to create complex queries via intuitive visual interface. The interface is more advanced than MS-Access, SQL Server or other similar query construction interfaces. The component may be integrated with Visual Basic or Visual C++ application with just few lines of code. In Visual Basic you may use this control via drag-drop into a form and sizing the control and setting a few design-time properties.

Main features include the following:

  • MS-Access style query building visual interface,
  • Run-time multiple database connection manager with built-in storage support,
  • Data browser pane integrated with the Connections and other panes,
  • View database object information or script in the Data Browser pane,
  • Construct joins via simple drag-drop method using the Relations pane,
  • MS-Access style group, sort, and criteria construction via the Columns pane,
  • Columns toolbar combo-buttons comprising all the SQL functions including data conversion, date, aggregate, rounding, masking and other functions,
  • Supports all database type including MS-Access, SQL Server, Oracle, DB2, Sybase and all ODBC compliant OLE DB providers,
  • Built-in file processing and file storage support for the manipulation of queries,
  • Syntax colored SQL editor with advance editing features similar to the Visual C++ editor,
  • Sophisticated SQL parser that will translate any valid SQL statement into the visual Relations and Columns panes,
  • Show / Hide toggle buttons to maximize desired pane view,
  • Integrate with your application by setting a few properties or a few lines of code,
  • Complete editing and file processing external control over the ActiveX component,
  • Call functions for exporting and printing the content of the SQL pane and query results,
  • Export query result in HTML and e-mail formats,
  • Edit query directly from the result grid,
  • Royalty free run-times,
  • Package includes source code of sample applications,

The Office-QB application is a good example of how the ELS-QB ActiveX component may be utilized.

For more information about the ELS-QB ActiveX component please visit the following URL: http://www.epsilon-logic.net/ELSQBDetails79.asp


ELS-QB ActiveX FAQ

· What is the ELS-QB ActiveX component?

· How may the ELS-QB ActiveX component be used?

· Does ELS-QB ActiveX component support file processing?

· How effective is the ELS-QB ActiveX component’s SQL parser?

· Does the SQL pane of the ELS-QB ActiveX component support syntax coloring?

· What database engines are supported by the ELS-QB ActiveX component?

· What types of connections can the database Connection Manager create?

· What types of database objects are supported in the Data Browser?

· What are the function buttons at the bottom of the Columns pane?

· What type of SQL joins are supported by ELS-QB ActiveX component?

· Can we export the content of the Result window?

· Is there any print support for the ELS-QB ActiveX component?

· What does the sample application include?

· Can one use the sample application to build a custom application for distribution?

· Can one wrap the ELS-QB ActiveX component into a COM and distribute?

What is the ELS-QB ActiveX component?

The ELS-QB ActiveX component is a COM with all the interface and functionality to create queries in manners similar to Microsoft Access and SQL Server. This component may be used by Visual Basic or Visual C++ programmers to enhance their applications with advance query builder interfaces. In most cases, the ELS-QB ActiveX component is the most sophisticated query builder available as an ActiveX component.

How may the ELS-QB ActiveX component be used?

The ELS-QB ActiveX component is an excellent addition to the repertoire of components of a Visual Basic / Visual C++ programmer. It may be integrated with any database application as an advance query builder interface, with which the end-users of the application may generate complex queries using visually intuitive interfaces. An excellent application would be in the special situations when in the custom database application, multiple connections to various database engines are required. The ELS-QB ActiveX component is equipped with a build-in Connection Manager, with which the end-user may create database connections at run-time.

Does ELS-QB ActiveX component support file processing?

The ELS-QB ActiveX component has a built-in file processing support, with which the queries may be stored in special files that are defined by the connection on which the query is defined. Therefore when the end-user of your custom application defines a connection, a corresponding file is created. All queries that are created and saved under this database connection will then be stored in the corresponding file. The location of these files may be controlled via SetStoragePath function, while the actual naming of the files themselves is left to the end-user. Queries may be created, modified, duplicated and removed from a connection container. In general, all file processing is built-in into the ELS-QB ActiveX component, so that the programmer needs no elaborate coding to add file processing and storage support to her/his custom application.

How effective is the ELS-QB ActiveX component’s SQL parser?

The SQL parser in the ELS-QB ActiveX component is extremely advance, in some cases exceeding the Microsoft’s query builders. Any valid SQL statement may be parsed into the Relations and Columns pane format, so that the user may work in the query builder’s visual interface. The join creator of the Relations pane creates optimized joins from the drag-drop and link operations performed by the end-user.

Does the SQL pane of the ELS-QB ActiveX component support syntax coloring?

The SQL pane of the ELS-QB ActiveX component is by itself a complete and fully syntax colored editor, with cut, copy, paste, find, replace and other operations. All of these operations are available for external calls, so that the programmers may design their own custom user interface from which these operations are controlled. The editor is as advance as the Microsoft’s Visual C++ editor with extended selection, multiple do/undo and other features.

What database engines are supported by the ELS-QB ActiveX component?

The Connection Manager and therefore the Data Browser and the query builder panes support any kind of database including Microsoft’s Access and SQL Server, Oracle, IBM’s DB2, Sybase, as well as other ODBC compliant databases. In general, the database access is via OLE DB providers for these database engines.

What types of connections can the database Connection Manager create?

The Connection Manager has a built-in connection creation processes with integrated connection dialogs to properly create ADO database access via UDL, dynamic OLE DB, OLE DB provider for ODBC, and others, depending on the OLE DB provider components installed on your system. The connection parameters may be stored in a persistent manner in the tree contained in the Connections pane.

What types of database objects are supported in the Data Browser?

The database objects that are displayed in the Data Browser consist of tables, views and stored procedures. In addition, the Data Browser has the Get Details function, with which the end-user may access detailed information about the structure of tables, view or procedures. These objects may be drag-dropped into the Relations pane for further query building.

What are the function buttons at the bottom of the Columns pane?

The function buttons at the bottom of the Columns pane are combo-boxes, which contain most of the SQL common functions that are often needed for the creation of a complex query. These functions are used in combination with ordered user-selected field-rows in the Columns pane. In particular, these functions include the following SQL functions: concatenation of field into strings, masking, aggregate functions (AVG, COUNT, MAX, MIN, SUM, STDEV, VAR), string functions (CHARINDEX, DIFFERENCE, LEFT, LEN, LOWER, LTRIM, PATINDEX, REPLACE, QUOTENAME, REPLICATE, LEFTPAD, RIGHTPAD, REVERSE, RIGHT, RTRIM, SOUNDEX, SPACE, STR, STUFF, SUBSTR, TRIM, UPPER), numeric functions (ABS, CEILING, FLOOR, ROUND, TRUNCATE, SIGN), date part and name functions (YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, MILLISECOND), conversion functions, and case functions.

What type of SQL joins are supported by ELS-QB ActiveX component?

In ELS-QB ActiveX component the joins may be interpreted as a segment in the FROM-clause of the SQL statement or sometimes in the WHERE-clause. In particular, it supports INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, as well as the Oracle (+) outer join notation in the WHERE-clause. This covers all existing database engine implementations.

Can we export the content of the Result window?

Yes, the Result tab is in a grid form, and therefore the content of the Result window can be easily exported to Excel, Word or other Office products. This may be performed via the Copy / Paste functions, which are available as external calls to the ELS-QB ActiveX component.

Is there any print support for the ELS-QB ActiveX component?

Yes there is print support for both the content of the SQL pane and the content of Result window. So that, the end-user may print the query statement, as well as the resulting data, if necessary. There are external call functions to access the ELS-QB ActiveX component’s print operations.

What does the sample application include?

The sample applications demonstrate the use of all the features available to the ELS-QB ActiveX component. In particular, they are full-fletched query builder applications to be used on their own. This applications may be used to develop queries for almost any kind of database engine, including Microsoft SQL Server, Oracle and IBM’s DB2. It also, illustrates the use of multiple instances of the QB-objects in an MDI application, with each document being a separate database project.

Can one use the sample application to build a custom application for distribution?

Yes, the licensed programmer-user may use this sample and customize to her/his needs and requirements. The user may create a new EXE-file and distribute this EXE-file together with the ELS-QB run-time components, without any royalty whatsoever, provided that the reference information of the sample application is changed and substantial additions are made to the original sample application. The only condition for distribution is that Epsilon-Logic Systems must not be liable to any defects or error caused as a result of changes or additions to the original shipped source code of the sample application. We must caution however, that the main program must be in binary EXE-file format, and that no internal features of the ELS-QB ActiveX component must be exposed to the end-user.

Can one wrap the ELS-QB ActiveX component into a COM and distribute?

In addition to using and customizing the sample application for distribution purposes, the programmer-user may also create a component with exposed interface, using the ELS-QB ActiveX component, and distribute this component to other programmers. In such case however, we impose the condition that the ELS-QB ActiveX component in this custom component must not be more than 50% of the whole component’s features and capabilities, and that this resulting product must not compete with ELS-QB ActiveX component on the market level.


Technical Support

The Office-QB package comes with free technical support via e-mail.

For technical support and other information, please send mail directly to the following addresses:

Technical Support: support@epsilon-logic.net
Product Information: info@epsilon-logic.net

All questions or problems will be addressed within 48 hours by the ELS-QB Group at Epsilon-Logic Systems, Inc.

For further information please visit Epsilon-Logic Systems main web site at: http://www.epsilon-logic.net