|
|
|
|
|
|
|
|
|
RazorSQL
Query, Edit, Browse, and Manage Databases
RazorSQL is an SQL query tool, database browser, SQL editor, and database administration tool for Windows, macOS, Mac OS X, Linux, and Solaris.
RazorSQL has been tested on over 40 databases, can connect to databases via either JDBC or ODBC.
RazorSQL Highlights
The database browser provided by RazorSQL gives users the ability to browse database structures. The structures shown depend on the database, but for almost all databases, the minimum level of detail includes tables, views, and procedures. For most databases indexes, functions, constraints, and triggers are also included. For some databases, RazorSQL also provides security information such as users and roles.
The database browser displays varying levels of information depending on the object. For example, for database tables, the browser displays such information as column names, column data types, column lengths, column nullability, and primary and foreign key information. For indexes, the browser displays the index name, uniqueness, the table referenced by the index, etc.
In addition to the information displayed for each database structure, the database browser also includes a context menu for performing operations on the structure. For example, for tables, the database browser includes options for altering the table, such as renaming the table, renaming columns, adding and dropping keys, adding constraints, etc. For stored procedures, the database browser gives users the option to view the code behind the stored procedure, or edit the stored procedure. The database browser also has options for searching database tables, generating DDL for tables, views, and indexes, and much more.
The database browser also includes menu options for easily creating SQL statements for tables. For example, the browser provides the ability to generate select *, select, update, insert, and delete SQL statements.
RazorSQL is available for several operating systems including Mac OS X, Windows, and Linux. Listed below is a screen shot of the RazorSQL database browser:
RazorSQL contains a SQL editor that includes syntax highlighting and function lookup for many languages such as SQL, PL/SQL for Oracle, Transact-SQL for SQL Server and Sybase, SQL PL for DB2, and support for other popular programming languages like PHP, Java, JavaScript, HTML, CSS, XML, and many more.
The SQL editor also supports auto completion and auto lookup. For example, when typing an SQL select query in the SQL editor, after typing a keyword such as where, the editor will perform on auto column lookup to display the list of available columns pertaining to the current query. After typing a keyword such as from, the SQL editor will display a list of available tables to choose from.
The SQL editor also allows users to execute multiple queries at a time using the execute all option, or to execute only queries based on the current cursor position assuming queries are separated by semi-colons. Users can also manually highlight queries they wish to execute.
The SQL editor displays query results in a tabular format. Each query gets its own tab. The query results section includes many features such as one-click editing of data, data searching, filter and sort tools, and data import and export tools.
The editor tries to determine the syntax highlighting type based on the type of file and / or the type of database connection. For example, if the file ends in .xml, RazorSQL will display XML syntax highlighting unless the user changes the highlighting type manually.
Normally the default syntax highlighting type is the SQL type. However, if connected to Oracle, SQL Server, Sybase, or DB2, the editor will set the highlighting type to that particular database's programming language, i.e., PL/SQL for Oracle, TSQL for SQL Server and Sybase, and SQL PL for DB2.
The SQL Editor will also display custom sytnax highlighting for MySQL and PostgreSQL keywords.
Backup Table: Information on the Table Backup tool.
Create Table: Information on the Create Table tool.
Create View: Information on the Create View tool.
Create Index: Information on the Create Index tool.
Create Sequence: Information on the Create Sequence tool.
Edit Table: Information on the Edit Table tool.
Describe Table: Information on the Describe Table tool.
Alter Table: Information on the Alter Table tool.
Alter View: Information on the Alter View tool.
View Table: Information on the View Table tool.
Column Lookup: Information on the Column Lookup tool.
Table Lookup: Information on the Table Lookup tool.
Query Builder: Information on the Query Builder tool.
Drop Table: Information on the Drop Table tool.
Drop View: Information on the Drop View tool.
Drop Index: Information on the Drop Index tool.
Drop Sequence: Information on the Drop Sequence tool.
Listed below are screen shots of the Create Table Tool and the Edit Table Tool.
RazorSQL contains a database query tool that works in conjunction with the database browser and SQL editor to allow users to view query results in a multi-tabular display, one tab per result set. The query results section provides such options as sorting, filtering, searching, and more.
The Database Import Tool allows users to import data from delmited files, Excel spreadsheets, fixed width files, or files of SQL insert statements into database tables.
The first step in the import tool is selecting the type of import (xls, delimited file, etc.) and the database table to import the data into. If launching the import tool from the database browser, the database table and schema information will be pre-selected.
Once the type and table are selected, the next screen differs based on the type of import. For SQL insert statement files, there is little more information to enter. For delimited files, RazorSQL needs to collect more information before the import can run. Listed below are some of the settings required by the import tool.
Import Options
Normal Mode: Select this option if the data being imported does not contain line breaks. This option causes the Import Tool to look at the delimited file line by line and then break each line up based on the delimiter.
Advanced Mode: Use this option if there are line breaks contained in the data to import. If there are line breaks in the data, the entire data cell should be wrapped in double qutoes.
Delimiter: Select or enter the delimiter used to separate values in the file.
Delimited File: Select the file that contains the delimited data to import.
Delimited File Start Row: If using the line by line import option, tell the Import Tool which line to start with by using this option.
Delimited File End Row: If using the line by line import option, tell the Import Tool which line to end with by using this option.
Escape Single Quotes: Select this option to escape any single quotes located in the data. Select this option if the data contains single quotes and they are not escaped.
Escape Character: The escape character to use if escaping single quotes.
Column Options
Populate Column: If using the line by line option, this option gives the user the ability to select which columns correspond to the data in the delimited file.
Delimited File Column Number: If using the line by line option, this option gives the user the ability to tell the Import Tool which column of the delimited file corresponds to the column of the table.
Wrap in Single Quotes: This option allows the user to specify whether or not to wrap the column data in single quotes when generating the SQL to import. If textual data is already wrapped in single quotes, deselect this option.
The Excel and Fixed Width file import options have the same options that are listed above.
Once the above options are entered, the final screen offers some more settings. Listed below is some documentation on these.
Generate SQL Output File: The import tool will generate SQL insert statements based on the data in the import file and write those SQL statements to the specified output file. This is useful if you would like to run the import at a later time, or to just preview the entire import.
Halt On Error: If this option is checked, if an error occurs, RazorSQL will stop execution of the import. If not selected, RazorSQL will continue to import data from the import file until there is not more data to import.
Execute As Batch: If this option is selected, RazorSQL will batch up groupings of data inserts into one call. This can be useful if you have slow or latent network connection to the database. There is an option for how many statements to bundle up per call. The higher the statement count, the fewer the network calls. If there is an error that occurs while doing the import in this mode, it is harder to determine exactly where the error occurred as it could be any one of the statements in the batch. If you are concerned about errors, it may be wiser to use the Execute One Statement at a Time option.
Execute One Statement at a Time: This option generates the SQL necessary to insert data into the database for each row in the import file. It sends these statements one at a time to the database. If a statement fails, RazorSQL generates an output log detailing exactly which statement at which row number failed. This option may be slower than the Execute As Batch option since more network calls are required.
Save to File Only: If this option is selected, RazorSQL will not execute the SQL inserts generated by the import tool. It will simply save those SQL insert statements to an output file.
The Database Export Tool allows users to export single tables, query results, or all tables in a database and/or schema as a delimited file, insert statements, HTML, XML, Excel, JSON, and Text formats.
Exporting Options
The Export Tool gives the following options:
Escape Single Quotes: Select this box if you would like single quotes to be escaped. This is useful if exporting in a format that will be executed as SQL.
Escape Character: The character to use if escaping single quotes.
Include Column Names: Select this option if you would like the column names to be included in the exported data.
Convert Nulls to Blanks - If the export type selected is not SQL Insert, then this option will convert null values to blanks in the exported data.
Wrap Values in Quotes when Delimiter Found in Value - If you are exporting delimited data, and there is a delimiter found in the actual data, this option will wrap the entire data value in quotes. Doing this allows the exported data to be re-imported.
Export Types
Delimited File: This option allows data to be exported using a delimiter. Each column is separted by the specificed delimer. For example, if selecting a comma as the delimiter, the following is an example of the exported data:
1,John,Smith
2,Jane,Doe
If selecting the delimited file option, the user can choose to export data with one line of the export file corresponding to one row of the data, or to combine all data and separate each value with a delimiter.
The "combine all data" option would typically be selected if there are line breaks in the data to be exported.
Insert Statements: This option is not available for query results, but is available if exporting a single table or all tables in a database / schema.
The Insert Statements option allows users to export data as SQL insert statements.
HTML: This option allows users to export data in HTML table format.
XML: This option allows users to export data in XML format.
XLS: This option allows users to export data in Microsoft Excel format.
JSON: This option allows users to export data in JSON format.
Text: This option allows users to export data in text format. The Export Tool will attempt to align columns to make them symmetrical.
The SQL Query Builder Tool allows users to visually build select, insert, update, or delete SQL statements against tables.
The Query Builder Tool allows the user to select the columns to be used in the SQL statement as well as the SQL operations to be included in the query. It also allows for the selection of aggregate functions and group by clauses and allows for selection of multiple tables for join selects.
After selecting the appropriate options and entering any custom values, the Query Builder Tool can generate and/or execute the SQL corresponding to the selections.
The query builder is divided into four tabs - select, insert, update, and delete. Select the appropriate tab for the type of SQL statement you wish to generate.
For select statements, if you wish to join in other tables, click the add table button. You can then select columns from the original table and the new table to tell RazorSQL which columns to use for the join clause. If joining multiple tables, RazorSQL will allow the user to specify join conditions on all of the tables.
For update and delete statements, the query builder gives users the option to specify what conditions to include in the update or delete where clause. For update statements, users can enter what to set column values to.
For insert statements, users can select which columns they would like in the insert statement, and the values for those columns.
RazorSQL contains a built-in relational database. The database requires no user configuration and can be launched right out of the box. To launch the built-in database, select the Connections -> Connect to Built-in Database option.
The current built-in database uses the HSQLDB database engine, although other engine options may be introduced in later versions of RazorSQL.
Once connected to the built-in database, the database browser will show schema information for the database. For the current configuration, the available schemas will be PUBLIC and INFORMATION_SCHEMA. The INFORMATION_SCHEMA contains details about the structures contained in the current database. The PUBLIC schema is where users should create their database objects.
The first step should be to use the Create Table Tool to create a table. Once the table is created, the Edit Table Tool can be used to enter data into the table. The data in the table can then be displayed using the database browser right-click menu and selecting View Contents or Search, or by manually executing SQL statements in the SQL editor.
Below is a screen shot of RazorSQL connected to its built-in database.
|
|
The Edit Table Tool allows users to visually edit tables. The Edit Table tool can generate and/or execute the SQL that corresponds to any changes made by the user.
After selecting the database and/or schema name and table name, the Edit Table Tool displays a window showing the column names for the table with the primary keys of the table preselected. If there are no primary keys for the table, RazorSQL requires the user to select enough columns to guarantee uniqueness among all selected columns so that the generated SQL does not update multiple rows of data. After selecting or accepting the primary key information, the Edit Table Tool displays a window showing the table data and several options. The options are the following:
Search Data: Allows the user to search for data in the table.
Find / Replace Data: Allows the user to find, replace, and replace all data in the table.
Preview / Generate SQL: Generates the SQL for whatever changes have been made and displays the generated SQL.
Commit / Execute Changes: Generates and executes the SQL for whatever changes have been made. Also displays the SQL that was executed.
Edits in New Window: Select this box if you would like to edit data in a new window. This would be preferrable for data the requires line breaks or large blocks of text. If this box is not selected, the user can edit the table cells directly.
Escape Single Quotes: Select this box if you would like single quotes to be "escaped", meaning preceded by an escape character when generating the SQL. This option should almost always be selected.
Escape Character: This is the character to use to escape single quotes if the Escape Single Quotes box is selected. For most databases the escape character is also a single quote.
Max Rows Retrieved: The Max Rows Retrieved is the number of rows that will display for editing. Change this value and hit apply to display more rows.
Change Query Button: The Change Query Button can be selected to change the where clause of the query used to display the data to edit. Users should do this if they want to filter the displayed data.
Refresh Button: Select the Refresh button to repopulate the displayed data with data from the database.
The Data Compare Tool allows users to compare data in tables, views, or queries across databases or in the same database.
The options that can be selected on the data compare screen are the connection that to use to query the table or run the query, and whether to compare a table or a query. Once the options are selected, the compare tool will compare the data and display the differences similar to the unix diff tool.
Users can choose to do table to table compares, query to query compares, or table to query compares.
|
Additional Information
RazorSQL provides both easy to use visual tools and advanced features to allow users to do database browsing, editing, management, administration, and programming.
Features
© Copyright 2000-2023 COGITO SOFTWARE CO.,LTD. All rights reserved
