General Information

System requirements

Visual SQL-Designer is the true Win32 application and known to be compatible with the following operation systems:

  • Windows 95 including OSR2
  • Windows 98
  • Windows NT version 4.0
  • Windows 2000
You need MS Internet Explorer 4.0 or later been installed on computer.

Compatibility note

Program used Microsoft ADO 2.1 library.

Considering the fact that there may be some misinterpretations of the unified DBMS interface specification the problems may arise with some exotic ODBC drivers, for example: missing data types, garbled non-standard data type support etc. Please contact us support@konst_one.mtu-net.ru in the case of any such trouble.


Introducing Visual SQL-Designer

Introduction

Working with relational databases, the developer spends the most part of time for a creating of the SQL queries. The existing tools of their construction not always enable to automate process, show by sight the circuit of the tables and, frequently, simply there is no toolkit of creation of more specific features of languages SQL.
Other important problem while works with RDBMS is the processes replication of the data (or part of the data) from one RDBMS to another. Let's note, in this case are considered (examined) as replication in the same information storehouses, and carry given from different storehouses (for example, from MS-SQL Server to Oracle). Certainly, in most cases similar problems do not arise daily, but, if they are, on their decision the significant time is required.
With the help Visual SQL-Designer (VSD) you can quickly construct the most part of necessary queries, using only visual interface of the user. Using practically one mouse pointer, you can be connected to the circuit given, include the tables and fields in query, to group, to put conditions and to execute operation of sorting. Besides there is an opportunity visually to create so-called fields with conditions (CF) - logic designs of a kind:
  • If = 'the director' and = 'the chief of a department' to give out 'the Chief'
  • If = 'manager' to give out 'an Average part'
  • In other cases to give out 'the Ordinary the employee'.
The data of any of the made queries it is possible replicate. Thus the table in same RDBMS is automatically created which you will specify, same fairly and for representations of the data (Views).
The important feature VSD is that SQL the offer is designed in "native" language of RDBMS at uniformity of a source of the tables. If the tables belong different RDBMS- SQL query is under construction by rules RDBMS Access. In both cases the result of performance occurs on the monitor as the table.
In VSD the designer SQL most widely widespread (distributed) RDBMS is realized: Oracle, MS-SQL Server 6.X, MS-SQL Server 7.0, Sybase, DB2, Access and other ODBC databases.


Install/Uninstall

Install program

Install program by double-clicking on distributed file "VisualSQLDesigner.exe" and following it instructions for setup.

Uninstall program

The Uninstall program removes all installed Visual SQL-Designer components and all records in the Windows registry made by the installation script. You can invoke this program by selecting the "Visual SQL-Designer" item in "Add/Remove Programs Dialog" of the "Control Panel". The Uninstall program does not remove files created by user such as SQL files etc.


About VisualSoft

Supports

Technical support: E-Mail: support@konst_one.mtu-net.ru
WWW: Web support


Working with program

  1. Creation of new query
  2. Load existing query
  3. Save query
  4. Save a text of the SQL query into the file
  5. Connection of the kept queries to sources of the data
  6. Save a query with a new name
  7. Connection to sources of the data
  8. Add table to query
  9. Creation of connection between the tables in query
  10. Add of fields to query
  11. Creation of fields with conditions
  12. Viewing of result SQL of query
  13. Creation of a condition WHERE in SQL query
  14. Creation of representation (View) on the basis of query
  15. Change of a mode viewing of result of performance SQL on the table of fields
  16. Exit from the program

1. Creation of new query

In the main form (Pic.1) press the button (1). Then, enter the name of query and press a key "OK".

Picture 1.

2. Load existing query.

In the main form (Pic.1) press the button (2). In the again opened window with the list of the kept inquiries, mouse will choose necessary, and twice click the left button.

3. Save query.

In the main form (Pic.1) press the button (3). If the query already exists - operation is executed successfully. If it is new query - in the opened window of dialogue enter its name and press a key "OK".

4. Save a text of the SQL query into the file.

In the main form (Pic.1) press the button (4). In a new window of dialogue will choose a place on a disk and enter a name of a file for keeping. Press the button "OK".

5. Connection of the kept queries to sources of the data.

In the main form (Pic.1) press the button (5). In treelike structure located in area (1) basic forms, new unit with heading “Saved queries" will be added.

6. Save a query with a new name.

In the main form (Pic.1) press the button (6). In the opened window of dialogue enter its name and press a key "OK".

7. Connection to sources of the data.

First of all it is necessary to define a source (sources) given for SQL of query. For this purpose will press the button 8 (Pic.1). In the again opened form will choose OLE DB or ODBC provider. Specify a name of the user and if, it is necessary, password for input. A tag "Remember the password" always should be checked. Otherwise, the list of the tables will not be removed on the monitor. After that, in the left column of the main form you receive the list of accessible objects. To look the data it is possible, having allocated any name in column and having pressed right button at the mouse. Having repeated operation, it is possible to connect other sources necessary for construction of query. Let's notice, that the given operation can require what that time.

8. Add table to query.

Successfully having connected sources of the data, we shall define the list of the tables for construction SQL. For this purpose it is necessary to allocate the necessary line in the field of (Pic.1) main form and to drag it with the help of the mouse in area 2 on it. Repeating this operation it is possible to include in query any reasonable number of the tables (or representations). If you bring in one object more once, in the field of 2 it will occur under a pseudonym similar to the true name (For example: ShemaName.TableName. The first name in the field of 2 main form will be same, second TableName1, third TableName2 etc.). Then, it is necessary to keep query. It can be made with the help of the button 3 (Save) of the main form. As soon as the operation of preservation is carried out, there is an opportunity to include in query of a field from the chosen objects (area 3 picture 1).

9. Creation of connection between the tables in query.

In the field of 2 main forms will choose a pair of the tables for connection. On one of them allocate a field and press (letting off) the left button at the mouse. Then drag icon on the necessary field of the second table and release (let off) the button of the mouse. If connection is successfully created, the tables will be connected by line. If necessary to put down conditions on connection or to change its type, move the mouse on the communication line and press right mouse button. The menu with the list of possible actions will appear.

10. Add of fields to query.

For a choice of fields it is necessary, that at working area 2 main forms there was even one object (see item 2). Any field from the tables in the field of 2 can be included in inquiry. For this purpose it is necessary to allocate a field and drag by mouse to move it to area 3. After that in the table of fields (3) the new line with object chosen will appear earlier. Consistently repeating the stated above rules of filling of the table, it is possible to include all necessary fields for work.
Possible operations above fields in object (3):
  • Show / hide for viewing - in a column 2 (heading View) to move by mouse to the necessary field and twice to press the left key. The red circle becomes white (or on the contrary) and, accordingly, the meanings of a field will be shown / are latent in query. If a line hide, it automatically moves downwards in the table.
  • Change the order of a conclusion - to move by mouse to the necessary field and to press, not releasing (letting off) the left key of the mouse. Dragging a line of the table in the necessary place. The numbering in a column 1 (N) will change.
  • Group / apply aggregate function - to move by mouse to the necessary field and in a column 6 (Group) the table of fields to press the right key of the mouse. From the appeared field with the list choose a necessary line. After loss of focus the color of a cell will change on gray, and the name of the group operator will appear. To cancel action it is possible, having chosen in a field with the list a line UnGroup.
  • Sort - to move by mouse to the necessary field and in a column 7 (Sort) of the table of fields to press the right key of the mouse. The further actions are identical to operation of a grouping (see item "Group"). Fields with conditions - see following chapter.

11. Creation of fields with conditions.

Probably, you had to create queries, where meanings of a column in query were function of conformity of several parameters to the limited set of meanings on an output. For an example we shall take the table Products see the Table 1.
ProductID | ProductName | UnitPrice
1 | Ceylon Tee | 9
2 | Ceylon Tee extra | 15
3 | Brazilian coffee | 8
4 | Cocaine leafs | 1500
5 | Bolivian coffee | 30

Table 1.

Let it is necessary to create a query are dividing products into 3 kinds: cheap coffee (UnitPrice < 10), road of coffee, cheap tea (UnitPrice < 10), expensive tea and other products. There are some ways of the decision, but most elegant is to create a field with conditions:
If ProductName like “ coffee ” and UnitPrice < 10 then CalcField = “Cheap coffee”
If ProductName like “ coffee ” and UnitPrice >= 10 then CalcField = “Expensive coffee”
If ProductName like “ Tee ” and UnitPrice < 10 then CalcField = “Cheap Tee”
If ProductName like “ Tee ” and UnitPrice >= 10 then CalcField = “Expensive Tee”
Else CalcField = “Other Product”.
The difficulty is, that on creation of a similar design in SQL query some time and, sometimes, essential efforts is required. VSD will allow you to make it much faster.

Picture 2.

Move the mouse to the table of fields (3) and click the right button. On the appeared menu will choose " Add CalcField ". On the opened form will choose a type of a created field with a condition and press "OK". In the table of fields the new line will appear, at which 9 ("Expression") the column has as against usual fields an emerald background. Move the mouse to there and also press left key. Before you the form (pic.2) - "Creation of fields with conditions" with the following sections will appear:
  • "Accessible fields" (Region 1) - list of all fields of the tables in query.
  • "Accessible operations, the calendar, values of a field" (Region 2) - will help you to enter from fields with the list the necessary operation (2.2.1), date (2.2.2) and values of a field (2.2.3), previously marked in 2.1.
  • Of working area for creation of conditions (Region 3) - two-piece. The first (2.3.1) parts contain directly line of a condition. A part the second (2.3.2) table of the generated conditions divided logic OR or AND by the operators. To add a condition, put with the help of the mouse focus on the necessary cell of the table, press right mouse button and further will choose operation "to add a condition". In the same way it is possible to remove already existing condition from the table. If it is necessary to create a completely new condition in a field 2.3.1 buttons 2.3.3 completely will remove its contents. The buttons 2.3.4 and 2.3.5 add in the table 2.3.2 a line / column. The completely generated condition is added by the button 2.3.6 for the following step of designing (see below).
  • Variants of filling of a field (Region 4) - finishing stroke in work on creation of a field with conditions. The button 2.3.6 adds in 2.4.1 the generated condition "IF" (see example). The value of a field at performance of a condition is brought in 2.4.2. The button 2.4.3 adds a new condition IF … THEN … i.e. practically creates one more block (2.4.2 and 2.4.3). The button 2.4.4 deletes a condition IF … THEN … if on it there is a focus. The button 2.4.5 keeps a field with a condition in query, previously having checked up all generated conditions. If any of conditions is incorrect, the semaphore of a field (2.4.6) will have red color. If the design is correct - green.
After creation of a field with conditions, to him is possible to apply the list of operations above fields (sorting, operation of summing).

12. Viewing of result SQL of query.

Previously generated query can be looked. For this purpose in the main form (Pic.1) press the button (9). Instead of the table of fields (3) the result of performance SQL will appear.

13. Creation of a condition WHERE in SQL query.

Picture 3.

For creation of conditions in query on the main form (Pic.1) press the button (11). The open form (Pic.3) has the title "Make the filter" and contains some areas:
  • "Accessible fields" (3.1) - list of all fields of the tables in query.
  • "Accessible operations, calendar, the values of a field", working line of input of a condition (Region 2) - will help you to enter from fields with the list the necessary operation (3.2.1), date (3.2.3) and value of a field (3.2.0), previously highlighted in 3.1. The button (3.2.6) clears contents of a working line of input (3.2.2). The buttons with dark blue arrows (3.2.4, 3.2.5) will add with preliminary check the created condition in the table located below on the form. It on structure is identical to the table of fields with conditions considered in the chapter 2 (item 2.3.2). Let's notice, if the condition does not satisfy to rules SQL, it will not be included in the table.

14. Creation of representation (View) on the basis of query.

In the main form (Pic.1) press the button (12). In the opened window of dialogue enter a name of representation and press a key "OK". The note: If you work with RDBMS Access, at preservation of query the representation is created automatically.

15. Change of a mode viewing of result of performance SQL on the table of fields.

In the main form (Pic.1) areas (3) have some bookmarks. To receive the necessary information will choose the necessary bookmark and will click on it the left button of the mouse.

16. Exit from the program.

If you want to finish work with the program, in the main form (Pic.1) press the button (15).


Interface description

Toolbar command:

  • New Query
  • Open Query
  • Save Query
  • Save Query SQL into file (*.sql)
  • View saved queries
  • Save Query as another
  • Options
  • Connect to Data Source
  • Execute current Query SQL
  • Edit SQL
  • Make Filter for Query (WHERE)
  • Create VIEW into Data Source from current Query SQL
  • Show/Hide Query constructor
  • Help
  • Exit