- Stores Each Connection URL in a Combo Box
As each new connect URL is used, it is stored in the connection combo box and placed in BasicQuery's properties file.
- Stores Each SQL Statement in a Drop-Down Control
As each new SQL statement is executed it is stored in the SQL statement drop-down and added to the currently opened SQL statement file.
- Displays Result Set Data in a Table
A JTable is used to display the output of select queries and stored procedure calls returning a result set
- Provides Details of Each Statement Execution
A message area at the bottom of the main window displayes information about each statement as it is executed. This includes timing information, OUT parameter values from parameterized SQL statements, and error messages.
- Optional Use of a Pooled Connection
Pool Connect checkbox found on the main screen
Speeds execution of SQL by reusing an existing connection. This also allows for realistic timing results when testing based on a pooled or non-pooled design.
- Control of Auto-Commit on the JDBC Connection
Auto Commit checkbox found on the main screen
This allows you to adjust the auto commit setting on the connection for JDBC drivers that support this setting.
- Control of Read-Only Setting on the JDBC Connection
Read Only checkbox found on the main screen
Allows control of the read-only setting on the JDBC connection.
- Use Multiple SQL Statement Files
File|Open [ALT]+[O]
Allows a set of SQL statements to be stored in individuals files. These collections can then be run as a group using the Run All Queries feature. We typically break up SQL statement files by project and purpose (test-bed setup, timing tests, scratchpad, etc.).
- Logging of SQL Execution Statistics to a CSV File
File|Log Stats checkbox menu item [ALT]+[L]
If enabled, the timing and result count statistics, which are reported in the message window, are also written to a CSV file named BasicQuery.Stats.csv
- Log All Select Query Result Data
File|Log Results checkbox menu item [ALT]+[G]
If enabled, causes the returned column data to be written to a CSV file. Each result is appended to the file. The file is named BasicQuery.Results.csv
- Write Current Select Results to a CSV File
File|Export Results As CSV [ALT]+[E]
Writes the currently displayed results to a CSV file. The file path and name are controlled by the user using a file dialog.
- Save BLOB Field Contents to a File
File|Save BLOBs [ALT]+[B]
Exports the BLOB fields from the currently selected row to individual files. There will be as many files created as there are BLOB fields on the row. The file names are automatically generated based on the blob column name and the key value of the BLOB record. The suffix .dat is then appended. These files are written to BasicQuery's execution directory.
- Raw Export Mode
File|Raw Export checkbox menu item
If this mode is selected the CSV output of data will not place quotes around text field (VARCHAR, CHAR, etc.) values in output files.
- Prevent Line Breaks Between Output Records
File|No CR Added to Export checkbox menu item
If this mode is selected individual result rows will not be separated by new lines in the output CSV files. This can be useful when the rows are storing sections of data, such as the way that Sybase handles stored procedure source code.
- Store Last Password
File|Save Password
By default the password is not stored between runs of the program. If you want BasicQuery to remember the last password used, select this menu option. Note that this option will expose the plain text password in the properties file! :O
- Sort By Multiple Columns
Edit|Sort by Selected Columns
By selecting multiple columns and choosing this option, the existing rows will be sorted using the left-most column as the primary sort value and using columns to the right to resolve ordering decisions for duplicate values in the left-hand column. This is an alternative to adding an order by clause to the SQL and re-executing the query. Note that if the columns are not in the correct left-to-right order to sort the way you require you will first need to rearrange the columns by dragging them to the right or left.
- Create a Select Query Based on the Current Results
Query|Select Statement [ALT]+[S]
Causes a select statement to be built using the table and columns that are currently displayed. Any column selected will be used to create the skeleton of a where clause while the unselected columns will be named as the columns to be selected. Note that this feature requires that the JDBC driver support the metadata getTablename() method.
- Create an Insert Statement Based on the Current Results
Query|Insert Statement [ALT]+[I]
Causes the skeleton of an insert statement to be built using the table and columns that are currently displayed. Note that this feature requires that the JDBC driver support the metadata getTablename() method.
- Create an Update Statement Based on the Current Results
Query|Update Statement [ALT]+[U]
Causes the skeleton of an update statement to be built using the table and columns that are currently displayed. Any column selected will be used to create the skeleton of a where clause while the unselected columns will be listed as the columns to be updated. Note that this feature requires that the JDBC driver support the metadata getTablename() method.
- Create a Select * Query Based on the Selected Cell
Query|Select * [ALT]+[T]
Causes a select statement to be built using the currently selected cell as the table name. This is useful if the results listed are from a show tables or select tablename from all_tables type of query.
- Obtain Metadata Based on the Selected Cell
Query|Select * [ALT]+[T]
Causes a select statement to be built using the currently selected cell as the table name but setting the query type to describe. This is useful if the results listed are from a show tables or select tablename from all_tables type of query.
- Run All Queries as a Batch
Query|Run All Queries [ALT]+[R]
Executes all of the SQL statements in the currently opened SQL statement file, one or more times. This is useful for carrying out a redundant set of steps, such as setting up test data or to collect performance testing results. When executing the queries multiple times you determine whether they are executed first to last and then again or to repeat each statement the requested number of times and then move onto the next. In other words, given 3 queries to be executed twice, you can execute them as 1, 2, 3, 1, 2, 3 or as 1, 1, 2, 2, 3, 3. A negative value for the number of executions causes the latter ordering to be used.
- Create Parameterized SQL Statements
A specialied syntax allows a parameterized SQL statement to be created. Although IN parameters may be used, this feature is probably most useful for accessing OUT parameter values. An overview of the syntax is available by selecting the menu item Help|Parameterized SQL Statements and is repeated here.
The format for an IN parameter is: $PARAM[IN,DATATYPE,DATA]$
IN is the literal text IN
DATATYPE is a defined DATATYPE (listed below)
DATA is the data (text, number) you are supplying in the parameter
The format for an OUT parameter is: $PARAM[OUT,DATATYPE]$
OUT is the literal text OUT
DATATYPE is a defined DATATYPE (listed below)
Note that OUT parameters have their values displayed in the message area at the bottom of the Basic Query window.
Known DATATYPE values are: BOOLEAN, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, NULL, STRING
Example Parameterized SQL Statements:
select * from all_objects where object_type like $PARAM[IN,STRING,FUNC%]$
This results in a where clause of 'object_type = '?' and sets a param of 'FUNC%'
$PARAM[OUT,INTEGER]$ = {call im_worktodo($PARAM[IN,STRING,DPR]$}
This calls the stored procedure im_worktodo with the param value 'DPR' capturing the integer return value and displaying it in the message area at the bottom of the window.
- View Metadata Based on a Select Statement
Describe radio button beneath the SQL input text area
By selecting the describe mode, the query is executed but the results are not retrieved. Instead the metadata for the selected columns is displayed in the results area. The SQL statement used for this operation should be a select or stored procedure call that returns a result set.
- Limit Rows Retrieved
Max Rows drop-down selection beneath the SQL input text area
Places a limit on the number of rows that will be retrieved, regardless of hhow many are actually returned. if the maximum number of rows set is al retrieved the number of rows displayed in the message area will be highlighted as a reminder that this may not be all of the data being returned.
- Remove a SQL Statement from the Set of Stored Statements
Remove button beneath the SQL input text area
Remove the currently selected SQL statement from the drop-down of SQL statements.
- Comment-out a SQL Statement in the Set of Stored Statements
Comment button beneath the SQL input text area
Toggles between commenting and un-commenting the currently selected SQL statement in the drop-down of SQL statements. A statement is considered commented-out if it begins with two slashes (//). This causes BasicQuery to ignore the statement if you attempt to execute it. This is particularly useful when you intend to use the Run All Queries feature but have a few statements you do not want to execute.
