WinSQL Professional
User's Guide
Revision 3.8
Thank you for using WinSQL professional. WinSQL is a simple yet powerful utility for programmers, DBAs and system administrators.
This guide provides you detailed instructions on how to use the program. Although the user interface is very intuitive and easy to understand, you might find this guide useful when doing some advanced features.
If you find any area in the document that needs improvement, please contact us at support@indus-soft.com
Getting Started *
Requirements
*Installation and setup
*Uninstalling WinSQL
*Configuring ODBC *
Opening the ODBC administrator
*Different kinds of DSN
*User DSN
*System DSN
*File DSN
*Creating a new DSN
*Running Queries *
Connection options
*Server side cursor
*Auto commit transactions
*Opening more than one query sets
*Displaying resultset in Grid or Text
*Comments
*SQL syntax
*Supported functions
*How to use scalar functions
*String functions
*Numeric functions
*Time, Date, and Interval functions
*System functions
*Parameterized queries
*Syntax highlighting
*Limitation
*Viewing and Printing Database Catalog *
Providing filters
*Browsing data
*Applying Filter
*Sorting records
*Using Insert / Update record wizard
*Using Strings and Dates in the wizard
*Ignoring a field and NULL values
*Using new table wizard
*Reverse Engineering a table design
*Printing
*Publishing HTML Pages *
Export templates
*Creating new HTML template
*Using existing HTML template
*Generating test data *
Starting the wizard
*Example
*Populating primary tables
*Let’s get started
*Text file
*Number of records to generate
*Transaction support
*Running queries while data is being generated
*Populating secondary tables
*Ignoring a field
*Drawing Entity-Relationship Diagrams *
Toolbar buttons
*Saving and Reading the file
*Exporting Data *
Export templates
*Starting a new export
*Using existing templates
*Export Template File Editor *
Command Line Options *
Usage:
*Examples
*Incorrect usage
*WinSQL Plugins *
XML file format *
Plugin Elements
*Extension nodes
*Database Diff Report *
How does it work
*
WinSQL is a 32-bit program and will only run on Windows 95, 98, NT, Windows 2000 and above. It uses ODBC to connect to any target database for which a driver is available. WinSQL requires that you use version 3.x or higher as the ODBC Manager. Although, version 3.x is required for the Manager, you can use a driver that is compliant with version 2.x. However, if you use an older driver, you may not be able to use all of the features of WinSQL.
Most of the off-the-shelf products these days come with an installer. These installation programs, besides copying files into the target folder also copy DLLs and OCXs into your Windows/System32 folder and change your system configuration. If these DLLs are ActiveX controls, they even have to register themselves with the Windows Registry. If two vendors use a same name for their files, it can result in breaking one or more programs on the system. One of the features of WinSQL is zero installation solution, which means you do not have to run any installation script that will change your system configuration. There are no DLLs or OCX to register. You can manually copy the executable on your hard drive; optionally, create a shortcut on your desktop and double click to run it.
Although, all you need is an executable file, the program can generate a few text files in the directory where it is located. For example, if you create new string formats for "test data generation wizard", WinSQL will create a file called sFormat.dat in the same directory where the .EXE is located. Therefore, we suggest that you copy the file in a new folder.
If you decide not to use WinSQL, you can simply delete the file from your hard drive. However, WinSQL will create some entries in the Windows Registry. These setting are located at:
"HKEY_CURRENT_USER\Software\Indus\WinSQL".
You can use REGEDIT or REGEDT32 to delete these settings.
Relatively speaking, configuring ODBC and creating and data source name is probably the most difficult part in the whole setup. However, most likely since programmers, DBAs and system administrators will use this tool, most of the users would already know how to do it.
Opening the ODBC administrator
Double click the ODBC32 icon in the control panel to start the ODBC Manager. Remember you can only use version 3.x or above in order to connect to the databases. You can confirm the version of manager by clicking on the "About" tab on the administrator. You can download the latest version from
http://www.microsoft.com/data/odbc
You can create three different kinds of data source names.
These data sources are local to a computer, and may only be used by the current user.
These data sources are local to a computer, rather than dedicated to a user. The system, or any user having privileges, can use a data source set up with a system DSN.
These are file-based data sources that may be shared between all users that have the same drivers installed, so have access to the database. These data sources are not dedicated to a user and are not machine specific. WinSQL does not support File DSNs.
Depending on your needs you can decide what type of DNS would be suitable for you. In the example below we will create a System DSN that will point to an MS SQL Server database.
Once you have created a DSN, you should be able to run SQL queries against the database. First you have to connect to a database. You will see the newly created DSN in the database connection box of WinSQL.
Some databases like MS SQL Server and Sybase does not allow opening multiple statements at the same time unless a server side cursor is used. Although, WinSQL does not use multiple statements, some implementation of the driver might require server side cursors. This will degrade the performance of the queries and therefore, if you are not sure, leave this option unchecked.
When checked, all the transactions will be automatically committed. Some databases, like Oracle, do not allow explicit transactions. Therefore, it may be useful to turn this option off which will allow you to manually COMMIT and ROLLBACK any transaction.
When this option is OFF, a red indicator appears on the status bar. This is to remind you to COMMIT or ROLLBACK before closing the connection.
Once the connection is established, you can write any SQL statement and click the play button to run it.
As soon as you hit the run button, the query gets submitted to the database in a secondary thread. This way even if it takes long for a result set to come back, the client stays active to the user.
Opening more than one query sets
You can open as many query sets as you want. The number is limited only by available memory on the machine. Click the "New Query Page" button on the screen to create a new query set. You can go back to the previous one by selecting it from the combo box. Starting from version 2, query set saves both the contents of the query tab as well as the results tab.
Displaying resultset in Grid or Text
The program allows you to display text in two formats:
WinSQL recognizes two kinds of comments:
Starting from version 3.5 you have an option of letting WinSQL parse the comments before the SQL statement is submitted to the server. This allows you to use comments even if the back-end database does not support them.
We recommended that you let the DBMS handle comments if they are supported. This option can be changed by selecting Edità Options from the menu.
WinSQL does not have syntax of its own. It submits the query as-is to the back-end database with a few exceptions. These exceptions occur in case of Parameterized queries and comments.
Besides supporting all the functions that are supported by the back-end database, WinSQL also supports ODBC scalar functions. In most cases, you will not need to use these scalar functions since the back-end database usually has a richer set. However, in some cases, for instance when the source is a text file, you will find these functions extremely helpful.
Following are the types of scalar functions:
The syntax for using scalar functions is as follows:
{fn
FUNCTION_NAME( PARAMETERS ) }The string
{fn MUST precede the function name and a } must be followed after the close-parenthesis.
Example:
select
{fn SUBSTRING(first_name, 1, 3)} FirstThree, first_NameThis query displays a result:
FirstThree first_Name
---------- -------------------------
Den Denzil
1 Row(s) affected
First field only displays the first 3 characters of the name.
Important:
Your driver may not support all the functions listed below. Check the driver help file for a detail listing.
Function Name |
Description |
ASCII( string_exp) |
Returns the ASCII code value of the leftmost character of string_exp as an integer |
BIT_LENGTH( string_exp) |
Returns the length in bits of the string expression |
CHAR( code) |
Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255; otherwise, the return value is data source-dependent. |
CONCAT( string_exp1, string_exp2) |
Returns a character string that is the result of concatenating string_exp2 to string_exp1. The resulting string is DBMS-dependent. For example, if the column represents by string_exp1 contained a NULL value, DB2 would return NULL, but SQL Server would return the non-NULL string. |
DIFFERENCE( string_exp1, string_exp2) |
Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2. |
INSERT( string_exp1, start, length, string_exp2) |
Returns a character string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp, beginning at the start. |
LCASE( string_exp) |
Returns a string equal to that in string_exp with all uppercase characters converted to lowercase. |
LEFT( string_exp, count) |
Returns the leftmost count characters of string_exp. |
LENGTH( string_exp) |
Returns the number of characters in string_exp, excluding trailing blanks. |
LOCATE( string_exp1, string_exp2, [start]) |
Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first character position in string_exp2 unless the optional argument, start, is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned. |
LTRIM( string_exp) |
Returns the characters of string_exp, with leading blanks removed. |
REPEAT( string_exp, count) |
Returns a character string composed of string_exp repeated count times. |
REPLACE( string_exp1, string_exp2, string_exp3) |
Search string_exp1 for occurrences of string_exp2 and replace with string_exp3. |
RIGHT( string_exp, count) |
Returns the rightmost count characters of string_exp. |
RTIM( string_exp) |
Returns the characters of string_exp with trailing blanks removed. |
SOUNDEX( string_exp) |
Returns a data source – dependent character string representing the sound of the words in string_exp. For example, SQL Server returns a 4-digit SOUNDEX code; Oracle returns a phonetic representation of each word. |
SPACE( count) |
Returns a character string consisting of count spaces. |
SUBSTRING( string_exp, start, length) |
Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters. |
UCASE( string_exp) |
Returns a string equal to that in string_exp with all lowercase characters converted to uppercase |
Function Name |
Description |
ABS( numeric_exp) |
Returns the absolute value of numeric_exp |
ACOS( float_exp) |
Returns the arccosine of float_exp as an angle, expressed in radians. |
ASIN( float_exp) |
Returns the arcsine of float_exp as an angle, expressed in radians. |
ATAN( float_exp) |
Returns the arctangent of float_exp as an angle, expressed in radians. |
ATAN2( float_exp1, float_exp2) |
Returns the arctangent of the x and y coordinates, specified by float_exp1 and float_exp2. |
CEILING( numeric_exp) |
Returns the smallest integer greater than or equal to numeric_exp. |
COS( float_exp) |
Returns the cosine of float_exp, where float_exp is a angle expressed in radians. |
COT( float_exp) |
Returns the cotangent of float_exp, where float_exp is an angle expressed in radians. |
DEGREES( numeric_exp) |
Returns the number of degrees converted from numeric_exp radians. |
EXP( float_exp) |
Returns the exponential value of float_exp. |
FLOOR( numeric_exp) |
Returns the largest integer less than or equal to numeric_exp. |
LOG( float_exp) |
Returns the natural logarithm of float_exp. |
LOG10( float_exp) |
Returns the base 10 logarithm of float_exp. |
MOD( integer_exp1, integer_exp2) |
Returns the remainder (modulus) of integer_exp1 divided by integer_exp2. |
PI() |
Returns the constant value of pi as a floating point value. |
POWER( numeric_exp, integer_exp) |
Returns the value of numeric_exp to the power of integer_exp. |
RADIANS( numeric_exp) |
Returns the number of radians converted from numeric_exp degrees. |
RAND( [integer_exp]) |
Returns a random floating point value using integer_exp as the optional seed value. |
ROUND( numeric_exp, integer_exp) |
Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to |integer_exp| places to the left of the decimal point. |
SIGN( numeric_exp) |
Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, –1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned. |
SIN( float_exp) |
Returns the sine of float_exp, where float_exp is an angle expressed in radians. |
SQRT( float_exp) |
Returns the square root of float_exp. |
TAN( float_exp) |
Returns the tangent of float_exp, where float_exp is an angle expressed in radians. |
TRUNCATE( numeric_exp, integer_exp) |
Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to |integer_exp| places to the left of the decimal point. |
Time, Date, and Interval functions
Function Name |
Description |
CURDATE() |
Returns the current date. |
CURTIME() |
Returns the current local time. |
DAYNAME( date_exp) |
Returns a character string containing the data source – specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English, or Sonntag through Samstag for a data source that uses German) for the day portion of date_exp. |
DAYOFMONTH( date_exp) |
Returns the day of the month based on the month field in date_exp as an integer value in the range of 1 – 31. |
DAYOFWEEK( date_exp) |
Returns the day of the week based on the week field in date_exp as an integer value in the range of 1 – 7, where 1 represents Sunday. |
DAYOFYEAR( date_exp) |
Returns the day of the year based on the year field in date_exp as an integer value in the range of 1 – 366. |
HOUR( time_exp) |
Returns the hour based on the hour field in time_exp as an integer value in the range of 0 – 23. |
MINUTE( time_exp) |
Returns the minute based on the minute field in time_exp as an integer value in the range of 0 – 59. |
MONTH( date_exp) |
Returns the month based on the month field in date_exp as an integer value in the range of 1 – 12. |
MONTHNAME( date_exp) |
Returns a character string containing the data source – specific name of the month (for example, January through December or Jan. through Dec. for a data source that uses English, or Januar through Dezember for a data source that uses German) for the month portion of date_exp. |
NOW() |
Returns current date and time as a timestamp value. |
QUARTER( date_exp) |
Returns the quarter in date_exp as an integer value in the range of 1 – 4, where 1 represents January 1 through March 31. |
SECOND( time_exp) |
Returns the second based on the second field in time_exp as an integer value in the range of 0 – 59. |
TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2) |
Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid values of interval are the following keywords: SQL_TSI_FRAC_SECONDSQL_TSI_SECONDSQL_TSI_MINUTESQL_TSI_HOURSQL_TSI_DAYSQL_TSI_WEEKSQL_TSI_MONTHSQL_TSI_QUARTERSQL_TSI_YEAR where fractional seconds are expressed in billionths of a second. For example, the following SQL statement returns the name of each employee and the number of years he or she has been employed: SELECT NAME, {fn TIMESTAMPDIFF(SQL_TSI_YEAR,{fn CURDATE()}, HIRE_DATE)}FROM EMPLOYEES If either timestamp expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps. If either timestamp expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps. Important: Not all drivers support all of the intervals. |
WEEK( date_exp) |
Returns the week of the year based on the week field in date_exp as an integer value in the range of 1 – 53. |
YEAR( date_exp) |
Returns the year based on the year field in date_exp as an integer value. The range is data source – dependent. |
Function Name |
Description |
DATABASE() |
Returns the name of the database corresponding to the connection. |
IFNULL( exp_value) |
If exp is null, value is returned. If exp is not null, exp is returned. |
USER() |
Returns the user name in the DBMS. |
Starting from version 2.0, WinSQL can accept parameters in the queries. Any text that is following by two colons is considered as a parameter and the user can supply the value for that parameter when the query is run. WinSQL remembers the values for the old parameters so that the user does not have to type them every time.
Tip:
Once you have specified a value for the first parameter, you can hit ENTER to move to the next one. WinSQL, will try to determine the data type for the parameter. You can change the default data type by selecting one from the list.Important: You cannot use parameterized queries to export data.
WinSQL will highlight the text appropriately as you type your SQL statements. If you like to change the format, select "Syntax Highlighting" from the edit menu.
The result is limited to 32767 characters per field when the data is displayed in a grid control and 4096 characters when displayed in the text control. Therefore, the data will get truncated if you run a select statement that has one or more fields of type BLOB. This will not happen if you try to export the field to another database.
Viewing and Printing Database Catalog
Viewing database catalog can be very handy when writing SQL statements. WinSQL displays this information is a hierarchical format.
Important:
Catalog information depends heavily on the capability ODBC driver. WinSQL queries these drivers and finds out what information is available. For example the CLI drivers that comes with Informix client setup cannot display the parameters for a stored procedure.
In some situations where the number of tables is very high, it may take a long time to display all the tables and their fields. Catalog window provides two filtering parameters that can restrict the total number of objects displayed. These filters are:
Click the "Browse Data" node to browse the first few records in a table or view. The default number of records is 100. However, this value can be changed in the Options dialog box.
You can restrict the records that appear in the browse data window by clicking the right mouse button and selecting "Apply Filter" on the data grid. This opens up a window that create the necessary where clause for the SELECT statement.
Simply click the header column of the grid control to sort the data. Click it again to reverse the order.
Using Insert / Update record wizard
Double click an existing row in the Browse Data grid to bring up the Update record wizard or an empty row to bring up the Insert wizard. You can also invoke this wizard by clicking the right mouse button and then selecting "Update record" from the pop-up menu.
This wizard allows the user to input the values in the grid and generates appropriate SQL script.
Using Strings and Dates in the wizard
WinSQL will automatically enclose the values with appropriate prefix and suffix characters. Therefore, when typing the values in the grid, you DO NOT have to enclose string values in quotes. Similarly, date can be entered in the format that is understood by your system. This format is specified in the "Regional Settings" available in Control Panel.
Example:
Column Name |
Data Type |
You Enter |
FirstName |
VARCHAR |
Sarah |
LastName |
VARCHAR |
$$NULL |
ID |
INT |
430 |
CustSince |
DATETIME |
4/25/1963 3:00 PM |
Based on these values, WinSQL will generate the query as follows:
INSERT
INTO customers(first_name, last_name, id, custsince)
Notice that it appropriately encloses the string value and format the datetime field. This example assumes that the database is Informix and therefore, formatting is done accordingly.
Ignoring a field and NULL values
Double click any row to ignore it. When a row is ignore, text in all the columns get crossed out. In case of an INSERT, when a field is ignored, it will either put NULL or the default specified in the table.
If you wish to explicitly set the value to null, type $$NULL in the grid.
You can open this wizard either by clicking the right mouse button in the catalog window or by clicking the speed button. This wizard allows you to type new fields and select their data types and based on the values, it generates the SQL statement for you. Once you have entered all the fields, you can modify the SQL statement to include additional information like primary key, foreign key or tablespace information.
Reverse Engineering a table design
You can generate the CREATE TABLE statement based on the schema. Please note that this string is limited to the capability of the ODBC driver and may not include all the properties of the table. For example, ODBC does not provide the information about table spaces and therefore, this information is not generated.
Click the right mouse button on the tree view control in the catalog window and select "Generate CREATE TABLE Statement" from the popup menu.
The catalog information can be printed through the printing wizard in WinSQL. By answering just a few questions, you can print the complete schema of a database in matter of minutes.
Starting from version 3.0, you can publish the result of an SQL query to linked HTML pages.
You can create export templates files with WinSQL. Once these files are created, you can use them for future use. You will also need these files if you wish to run the export routines from Command line.
The HTML publishing has default values for all the required fields. This allows the user to hit the next button until the summary screen is visible.
WinSQL exports data based on a SQL query. Therefore, you must select a query in the query tab before clicking the export button. You cannot use parameterized queries to publish HTML. Once you are satisfied with the SQL script, click the "Publish as HTML" button to start the Wizard.
Include files
WinSQL uses two files, header and footer, to enclose the exported data. This allows the user to format the output to suit their needs.
Rules for header file:
Rules for footer file:
Example Header file
<
html></
head><!—You can add more data here -->
<
div align="center"><center><
table border="0" cellpadding="2">Example Footer file
</
table></center></div></body></html>
Table header, rows and columns properties
The next screen allows you to specify the properties for table header, rows and columns. The value in these field must conform to HTML rules and must have a
Example:
Table Header:
<th
bgcolor="#000000"><font color="#FFFFFF"> ::DATA </font></th>
Table Rows
<tr
BGCOLOR="#FFFFCC"> ::DATA </tr>Table Columns
<td>
::DATA </td>
Once all the necessary information has been entered, click the next button until you get to the summary screen. Click the Finish button to publish the results.
It is important to save the template if you need to run the publisher from a command prompt.
Simply select an HTML export template ( HET )file and click next until you get to the summary screen. Click finish to publish the result.
Starting from version 3.5, WinSQL Professional allows you to create a test environment. This feature can be very useful when some random data is required during the development and testing phase of a project. Test data generation wizard allows you to create millions of rows of meaningful data into a database.
First, you must select the table in the catalog window for which you want to generate the data. Once the desired table is highlighted, you can either click the "Test data generation wizard" speed button or select it from the pop-up menu by clicking the right mouse button on the table name.
In order to understand all the steps easily, we will use an example database and will create test records in all the tables. The E-R diagram of the table is shown below.
In our example, we have used Microsoft SQL Server 7.0 and therefore all the data types are shown accordingly.
Primary tables are those tables that do not have any foreign key constraints defined. Therefore, the data can be generated without depending on other entities. In our example, customers, employees and products fall into this category.
First, select the "customer" table from the catalog window. Then click the right mouse button and select "Generate test data" from the popup menu.
The first screen asks you if you want to create a new template or use an existing one. Since we do not have an existing template, we will create a new one.
Note: The wizard has default values for all the required fields. Therefore, if you click the next button all the way through, WinSQL will generate about 1000 rows in the table that you selected.
Click the next button to go the second screen.
Use this screen to pick a field and select the data format that you wish to generate. All fields are listed on the left-hand side list box. There are four sources of data:
Random
This option will generate completely random data. Although the text will not make any sense, this is the fastest method to generate data. This is the default value for all the fields except if there is a unique index on the field, in which case unique numbers will be generated.
Formatted
This option allows you to select the format of the data. Depending on the data type of the field, available options can change. For instance if the data type is datetime, you can specify the starting and ending date. Moreover, you can also specify if you wish to include time along with date.
String Formats
Following table specifies the rules for specifying format for strings.
Keywords |
Description |
/ |
Escape character |
# |
Numeric values |
$ |
Uppercase characters only |
^ |
Lowercase characters only |
@ |
Alphanumeric with upper case letters |
% |
Alphanumeric with lower case letters |
~ |
Alphanumeric with mixed case letters |
* |
This character MUST appear at the end and it means that one or more characters can appear on the right hand side of the generated string |
A database table
This option is useful if you have a foreign key constraint define that references another table.
WinSQL queries the ODBC driver to find out if there are any foreign key constraints defined. If found, the program automatically selects this option with appropriate values for the fields. This feature however, depends heavily on the capabilities of the ODBC driver. For example the ODBC driver for MS Access does not return this information to the application and therefore users will manually have to specify these values.
Cached records
When records are read from either a table or text file, WinSQL reads a certain amount of rows in the memory. These records are called cached records. The default number of records in cache is 1000. Once enough records are generated, the program discards all the rows and refreshes the cache.
Percentage per cache
This is the percentage of total records that should be assign from a batch of cached records.
The following figure explains the concept graphically.
In the above example, we have to insert 1000 records in the target table and there are 1750 records in the input file/table. The specified percent cache is 40% which means that WinSQL will read 1000 rows from the table or text file and randomly pick records 400 values. Then, discard the cache data and fill it again with new values. However, the second time it comes around, there are only 750 records left in the input source. Therefore, the remaining 60% of the data will be created using the 750 records that are in the cache.
Use this option to read data from a text file. This text file MUST have one row per line and can have multiple columns separated by a | (pipe) character. You can either sequentially read the file or assign records randomly. In case of sequential read, there MUST be enough records in the input file. You can download WinSQL Option Pack that comes with sample text files from
Token number
Data in one text file can be a source for multiple columns. Consider the following scenario. You want to insert city, state & zip information in a table. If these three fields came from three different text files, there would be no relation between city, state and zip. This can cause New York City to become part of California with a zip code of Orlando, Florida. To avoid this situation, WinSQL can read multiple fields from a text file. These fields are separated by a | (pipe) character and are called tokens. A sample text file can look like:
New York City|New York|10001
Woodbridge|Virginia|22192
Palo Alto|California|94301
Now lets come back to our example. The first field,
cust_id is a AGN (automatically generated number) for which the database is responsible for assigning values. It is recommended that you don’t change the values for this field.The second and third fields are first name and last name respectively. We will use a sample data file (SDF) as a source for the data. You can either generate this file yourself or download the option pack from
http://www.indus-soft.com that has sample files.Highlight "firstName" from the list on the left-hand side. On the right, click "Read from a text file" button. Click the browse button to select FirstName.sdf. Leave the remaining fields to its default values. Next, select "lastName" and repeat the same procedure.
Now click "city" on the left and select "Read from text file" button. Select "cityState.sdf" as a source. Repeat the same procedure for "state" and "zip", however, type 2 and 3 for token numbers.
Lastly, click "phone" in the list box. Select "Formatted Data" and pick "US Telephone Numbers" from the combo box. If you need any other format, you can add as many as you like by clicking the button right next to the combo box. Once satisfied with all the values, click the next button.
Next screen allows you to specify the number of records you wish to generate. There is no limit as to how many records you generate.
Important
: If you chose the input source to be a text file for any field and also checked the sequential flag, you MUST have enough records in the SDF file or else WinSQL will start over from the beginning of the last batch and will create duplicate records.
Optionally, you can generate data within a transaction. This allows you to ROLLBACK in case of any errors. By default, this option is off. Before turning this option on, you must refer to your database documentation regarding transaction support. Some databases, like Informix, allow you to temporarily disable transactions, in which case you will not be able use this feature.
Running queries while data is being generated
This feature allows you to run a query after a certain number of records are generated. This could be useful if you need to backup the transaction log so that more records can be inserted.
Repeat the above steps for employee and products tables.
Once all the primary tables are populate, data to satisfy foreign keys relationship in the secondary tables is now present. You should follow the same steps to generate data for these except for one difference: Now you can select a table as an input source. If your ODBC driver supports foreign key functionality, WinSQL will automatically select appropriate input source that will point to a primary key in another table.
In our example, lineItem is the last table in the dependence-hierarchy since it depends on the orders table. Therefore, the next candidate for data generation should be the orders table.
If you need WinSQL to ignore a field for any particular reason, simply double click the column name in the list box. This will cross out its name and display it in a gray color.
Although ignoring a field is almost like setting the NULL percentage to 100, there can be situations where they can differ. Consider the following example:
You have a table that is defined as:
create
table myTable(id autoNumber
primary key,In the case where the name fields is ignore, WinSQL will generate the following code:
However, if the field is set to produce 100% NULL values, the generated SQL will look like:
insert
into myTable(name, phone)In the above example it does not matter if the name field is ignore or set to NULL. In either case the output is same. However, if the table definition looked like following, there would be a difference.
create
table myTable(id autoNumber
primary key,Drawing Entity-Relationship Diagrams
Select "Draw E/R Diagram" from the Tools menu to create a new E/R Diagram.
Button |
Description |
|
Add New Table - Click here to display a list of Tables and Views in your database. In the list select and double click a table to add it in the diagram. |
|
Add New Relation - Creates a Master-Detail relationship between two tables. After you click this button, the mouse cursor changes to a hand. You MUST select the master table first and then the detail table. |
|
Zoom In - Click this button to enlarge the size of the image on the screen as well as the printed output. |
|
Zoom Out - Click this button to reduce the size of the image on the screen as well as the printed output. |
Click the save button to save the diagram to the disk. The default Extension for the file is .WER which stands for WinSQL E/R Diagram. Similarly, use the Open file button to read a previously created diagram.
Exporting data to a different data source can be a very useful feature for many users. WinSQL not only export the data but, to a certain extent, can even create the schema for you. Besides exporting normal fields like characters, integers and date-time, it can even export BLOB types to the target database.
Starting from Version 2, you can create export templates files with WinSQL. Once these files are created, you can use them for future use. You will also need these files if you wish to run the export routines from Command line.
WinSQL exports data based on a SQL query. Therefore, you must select a query in the query tab before clicking the export button. You cannot use parameterized queries to export data. Once you are satisfied with the SQL script, click the export button to start the Export Wizard.
Export Options:
Creating a new table in the target database
If you decide to export the data into a new table, WinSQL can generate the SQL for you.
WinSQL tries to match the data types as close as possible. However sometimes you might want to change the type for any reason. Furthermore, you can add any referential integrity constraints you wish.
Matching the source and target fields
It is very important that the order of source and the target fields have a one-to-one correspondence.
Use the arrow buttons to move a target field up or down until it matches with the corresponding source field. If the fields don’t match, either the data will get exported to incorrect columns or the export process will fail. By double clicking a row you can exclude a column from the data transfer.
Saving the export template
Once you are satisfied with all the options and settings, you can save the template file for future use. If you specify a file name that already exists, WinSQL will overwrite the file with the new format.
Using an existing template is pretty straightforward. Select a WinSQL template file (WET), and click the next button and finally the finish button.
Important:
If you are using an existing template that was created on some other machine, make sure that both the source and target DSN exists
Export template embeds all the necessary information to connect to the source as well as target database. Therefore, when you run an export routine from command line you do not need to provide the connection string parameters. However, there may be times when you need to modify this file, for instance when the database password gets changed. You can use this editor to modify the file. Select "Export Template File Editor" from the Tools menu to open up the editor and then load appropriate .wet file.
IMPORTANT: Changing the .wet file incorrectly may corrupt the transfer routine. Therefore, avoid changing the SQL statement in the template.
Starting from Version 2.0 WinSQL supports extensive command line options, which makes WinSQL a very useful utility. You can both run an SQL script that is saved in a file or run an export task. This way WinSQL can be executed from a DOS batch file or a scheduler that can run external task.
If your back-end server support external command, you can even call WinSQL from a stored procedure or trigger. For example you can use the xp_cmdshell stored procedure in Sybase to launch WinSQL to export data to an Informix database.
WinSQL <filename> | <option-Value>
Options |
Value |
Meaning |
-a |
NULL |
Close WinSQL automatically after the task is complete. |
-d |
DSN Name |
Contains the name of the ODBC DSN. If there is a space in the name, it must be enclosed in quotes. |
-g |
NULL |
Runs WinSQL in debug mode. Will create a debug.log file that can be used to pin point any potential problem. |
-o |
Output file |
This file is used to store any error messages or data returned from the server as a result of a query execution. If this option is not specified, there will be no output generated. |
-q |
Query File |
Name of the file that holds the SQL query. |
-p |
Password |
User password for database connection |
-u |
User Name |
User name for database connection |
-x |
Export template |
Name of the export template file. |
-h |
Publish HTML |
Name of the HTML template file |
-? |
NULL |
Displays a help dialog box displaying all the command line options |
Following example shows how to run any SQL query from the command line. It will open a file called "updateStudents.sql" and connect to the database using specified DSN, User Name, and Password. All the error messages or any output data will go to "output.txt" file and WinSQL will close itself once the query is complete.
WinSQL "-dStudent DSN" –uAdmin –pAdmin –qc:\updateStudents.sql –oc:\output.txt -a
Following example just open a file in the editor. It does not run it.
WinSQL c:\student.sql
Following example export the data based on a template file "StudentTemplate.wet"
WinSQL –xStudentTemplate.wet
Notice that you do not have to provide a user id, password and dsn name when specifying a template file. This is because all this information is embedded in the file itself. If you need to change any parameters in the template file, use the Export Template File Editor under the Tools menu.
Important:
There should not be any space between the option and its valueLimitation: You can only run one SQL statement from the command prompt. If you have multiple statements separated by a terminator, it could generate errors and the query may not run.
Following examples are incorrect.
Example 1.
WinSQL –xStudentTemplate.wet –dStudentDSN –uAdmin –pAdmin
You cannot specify any other parameter along with –x. The template file stores all the necessary connection parameters.
Example 2
WinSQL –dStudentDSN –uAdmin –qc:\updateStudents.sql –oc:\output.txt -a
Here the –p option for password is not specified. If WinSQL does not see a –p option, it will prompt the user by displaying a dialog box. If the password is blank or your database does not need a password, you must still provide this parameter with no value specified.
Plugins enhances the capability of WinSQL. ODBC is a generic API and therefore, does not support database specific functions. Most of this functionality is achieved by querying the system tables (also known as system catalog). Plugins provide the script for these queries, which are in plain text and in XML format.
XML is becoming the de facto standard for configuration files and to store small documents. Many programmers and system administrators are already familiar with its semantics and syntax. Therefore, WinSQL uses XML to create plugins, which makes its editing very easy.
The following shows a complete example of a plugin for Oracle 8i.
<
?xml version="1.0" encoding="iso-8859-1" ?>The example shows the basic outline of the document. The root element is WinSQLDatabaseType, which has four subelements: view, storedProcedures, triggers and extensions. The elements are described in more detail below.
Element name: <view>
This node gets the SQL query that was used to generate the view.
Children node(s) - viewDDL
Attribute name |
Description |
supported |
Can be "yes" or "no. If views are supported, this value must be "yes". The value is case sensitive. |
Element name: <viewDDL>
Children node(s) – query
Attribute name |
Description |
supported |
Can be "yes" or "no. If querying a system table can return the script for a view, this value should be "yes". The value is case sensitive. |
Element name: <storedProcedure>
Children node(s) - storedProcDDL
Attribute name |
Description |
supported |
Can be "yes" or "no. If stored procedures are supported, this value must be "yes". The value is case sensitive. |
Element name: <storedProcDDL>
Children node(s) - query
Attribute name |
Description |
supported |
Can be "yes" or "no. If querying a system table can return the script for a stored procedure, this value should be "yes". The value is case sensitive. |
Element name: <triggers>
Children node(s) - triggerDDL
Attribute name |
Description |
supported |
Can be "yes" or "no. If triggers are supported, this value must be "yes". The value is case sensitive. |
Element name: <triggerDDL>
This node holds the query node required to get the information regarding the triggers on a database.
Children node(s) - query
Attribute name |
Description |
Children nodes |
supported |
Can be "yes" or "no. If querying a system table can return the script for a trigger, this value should be "yes". The value is case sensitive. |
storedProcDDL |
Element name: <query>
This element is used in many places within the document. It is used to specify an SQL statement that is run against the database to fetch any data. Below is a snippet from the XML document. This snippet contains <query>, <sqlString> and <paramList> nodes
<query>
Children node(s) - sqlString, paramList
Attribute name |
Description |
none |
Element name: <sqlString>
Holds the SQL query within a CDATA section. Run time parameters are specified by a ?.
SQL string for views and stored procedures
This query should only return 1 column. If more than one column is returned, the remaining columns will be ignored. Concatenating all the rows will create the value of the script.
SQL string for triggers
In case of triggers, this query should return 3 columns. The first columns should be the name of the trigger. If names are not supported, query must return ‘Not Available’. The second column is the type of trigger. It could be Update, Delete or Insert. The third column is the text containing the SQL script
SQL string for extension
There are two kinds of extensions. Level 1 and level 2. In case of level 1, there can be only one parameter, which is the schema or the owner of the object. The number of columns returned by the query depends on the display type of the extension. Refer to displayType node for further details.
Attribute name |
Description |
none |
Element name: <paramList>
Holds the param node
Children node(s) - param
Attribute name |
Description |
None |
Element name: <param>
Holds information regarding the parameter of the query.
Attribute name |
Description |
position |
This is a numeric value that represents the parameter number, which starts from 1 |
dataType |
This is the data type of the parameter. It MUST be a
valid datatype of the backend database. You can get a list of valid
datatypes from the catalog tab of WinSQL. For example if you are changing
the plugin for MS SQL Server and one of the datatype is a CHAR, writing
CHARACTER instead of CHAR will generate errors. |
value |
This signifies the meaning of the parameter. It tells WinSQL where to get the value at run time. Possible entries are: WinSQL_SCHEMA – This is owner of the object WinSQL_OBJNAME – This is the name of the object WinSQL_FULLOBJNAME – This is the schema followed by a period and the name of the object WinSQL_PARENTVALUE – This value is only useful in the extension node where level is 2. This is the result from a parent query. The tag attribute specifies the column number. |
tag |
This attribute is only useful in the extension node where level is 2. This specifies the column number of the parent query. |
Extension nodes provide the SQL statement to fetch database specific feature like sequences in Oracle. There are two kinds of extension nodes:
When a user clicks on the Tree View control on the left of the catalog tab, level 1 query gets executed. Level 2 provides a drill-down query for level 1.
Following code is an extract of the extension node from the example above.
<extension
level="1" name="Last Error" displayType="WinSQL_TreeView"hasChildren=
"yes" useInDBDiff="no">displayType=
"WinSQL_GridView" hasChildren="no" useInDBDiff="no">value=
"WinSQL_PARENTVALUE" tag="1"/>First lets examine the attributes of the <extension> element.
Attribute name |
Description |
level |
This is the level number of the extension. It must be
either 1 or 2 |
name |
This is only valid for level 1. It is the text that appears in the tree view control in the catalog tab |
displayType |
This is the target for the query result. There are four possible values for this attribute: WinSQL_TreeView – Use this to display results in tree view control, which appears on the left-hand side of the catalog window. Only the first the column is displayed in the window. However, the query can return more than one column. This is useful if you want to use data from the second column to appear in the WHERE clause of level 2 query. WinSQL_GridView – Use this to display results in a grid control, which appears on the right-hand side of the catalog window. The query can return as many rows and columns that are necessary. Field name will be displayed as column headers WinSQL_ListView – Use this to display results in a list view control, which appears on the right-hand side of the catalog window. The query can only return one row of data. If more rows are returned, their results will be discarded. WinSQL_TextView – Use this to display results in a text box. The query can only return 1 column but multiple rows. The result of every row is concatenated with the previous row. This is useful to display SQL queries.
|
hasChildren |
Can be either "yes" or "no". A yes means that there is a level 2 extension for this node |
useInDBDiff |
Can be either "yes" or "no". If yes, it means that this extension will be used DBDiff report. |
This feature allows you to compare the schema of two databases. This feature is very useful if you have more than one database in your environment. WinSQL creates a report in HTML format that describes the differences.
IMPORTANT: The report created is a one-way report, meaning it compares the first database, called a pivot, with the second. Consider the following example where we have two databases called development and production.
|
|
In the example above, if the development database is selected first and made a pivot database, the report will be generated with respect to the development database. In other words it will only report objects that exist in Development but are missing or does not match in Production database. Therefore, it will report that contract table is missing from Production, but will not report the existence of employee table and empid field in orders table.
Tip:
If you need to generate a two-way report, simply run the wizard twice and switch the order of the databases.
Start the wizard by selecting "Database Diff" from the tools menu. Select the new template button and click next.
Tip:
Remember to select the correct database type if a plugin is availableThe first database is considered the pivot DB.
Click on the connection buttons to establish a database connection. Select the user name if required and click Next.
Select appropriate options and the target HTML file. Click Next when done.
WinSQL uses Cascading Style Sheets to format the HTML report, which is a very convenient way of modifying the look and feel of the report. You have an option of using a built-in style sheet or specify an external sheet.
IMPORTANT:
File names specified in this screen must use relative path with respect to the output HTML file. For example if the output HTML file is in the folder C:\MyReports\DBDiff.htm, DBDiff.css, check.jpg and cross.jpg must also be in the same folder.
Extended Attributes
These are optional attributes that you could specify that modify the look and feel of the HTML report. The table below summarizes these options.
Attribute |
Purpose |
Header file |
If this option is specified, WinSQL will omit the <HTML> and <BODY> tags and will replace it with the contents of this file. |
Footer file |
If this option is specified, WinSQL will omit the </HTML> and </BODY> tags and will replace it with the contents of this file. |
Match image |
This must point to an image file that will be used to display a matching condition. It should be a small image, approximately 16 x 16 pixels in either GIF or JPEG format. |
Mismatch image |
This must point to an image file that will be used to display a mismatching condition. It should be a small image, approximately 16 x 16 pixels in either GIF or JPEG format. |
Once finished inserting the values, click next. Verify the options on the summary screen and again click Next. At the last screen, you have an option of saving all the steps as a template. Finally, click Finish to run the report.