Топ-100
 

SQL editor

Supported syntax

CerebroSQL supports 2 code syntaxes for all DBMS (except MS SQL Server).

 

It is permissible to use both at the same time in one script!

1. Classic Oracle syntax

The create, declare, begin operation must end with the "/" character located on a separate line in case of script execution

For other commands, the separator is ";" at the end of the command

2. MS SQL Server syntax

The separator is the keyword " go " on a separate line, the command must not end with another special character, unless required by its syntax

CerebroSQL query executing
CerebroSQL query output
 

Query editor window

Consists of a panel of control buttons and a "Pages" area

Query editor.jpg

Control buttons

Query editor panel.jpg

Create a new sheet for working with the "MySQL" DBMS

Delete current sheet

Show "Program Monitor"

Show "Error Manager"

 

Keyboard shortcuts

​​

  • Shift + F1  -  create a new sheet for working with the "Oracle" DBMS

  • Shift + F2  -  create a new sheet for working with databases "SQLite"

  • Shift + F3  -  create a new sheet for working with "PostgreSQL" DBMS

  • Shift + F4  -  create a new sheet for working with the "MySQL" DBMS

  • Shift + F5  -  create a new sheet for working with the "MSSQL Server" DBMS

  • F3  -  delete the current active sheet

  • Ctrl + F1  -  show the main program window

  • F4  -  Show query execution plan on current sheet/selected query

  • F5 or F8  -  execute selected request/all requests on a sheet/selected requests

  • F6  -  show the contents of the selected table | show the contents of the table in the name of which the cursor is positioned (the first n lines are displayed, depending on the size of the Data window)

  • Ctrl + G  -  show properties of selected table (from editor text)

  • Ctrl + W  - create an alias for the selected request

  • Ctrl + F  -  search for text in the editor

  • Ctrl + R  -  find and replace text in the editor

  • Ctrl + T  -  create a copy of the current sheet (the new sheet is automatically connected to the database)

  • Ctrl + F10  -  show detailed information about the connection of the sheet

  • Ctrl + F12  -  stop loading data into the object tree

  • Alt + F1  -  show the main program window

  • Alt + F2  -  show connection settings panel

  • Alt + F3  -  generate and show a short report on the state of the database

  • Alt + E  -  show the code for creating the selected object in the editor (DDL)

  • Alt + F6  -  show a list of columns and their type in the editor grid

 

History of completed requests

By default, the program saves in the local database all queries executed in the editor.

CerebroSQL: view history execute sql.jpg

 

When the window is opened, the "Query list" list displays a list of all executed queries since the last start of the program.

Searching for queries in the storage ("Filter" block)

Field "Date begin" - limits search by date (minimum value)

Field "Date end" - limits search by date (maximum value)

Field "Database name" - the name of the database, restrict the search for queries executed in a specific database

The "Search text" field is the search string / part of the string. To search for multiple conditions, use the separator |

Example: select | dba | declare

a query containing the 3 specified keywords will be searched for.

The search is case insensitive.

list find query.jpg

Double-clicking on a line in the "Query list" adds a query to the active editor sheet.

"Details" block - detailed information about the executed request, execution status, duration, ...

 

Query Alias Manager

Serves to assign an alias (synonym) to the request for its subsequent execution by the given name.

You can open the dispatcher either by pressing the corresponding button on the control panel or from the current sheet using the Ctrl + W key combination. If the query is highlighted in the editor, it is automatically copied to the "Text" field of the dispatcher.

Query alias.jpg

 

Alias ​​creation

  • Open dispatcher

  • Select the type of DBMS from the drop-down list of the "New alias" button

  • Fill in the "Request alias" field - it is allowed to specify several words separated by a space

  • In the "Request text" field, add a request

  • Save by clicking the "Save" button

The "Comment" field is informational, has no semantic meaning

For the "alias" view of the execution plan is also available.

Query editor - use alias.jpg
 

A hint is a hint to the editor engine to perform additional actions.

Each hint must be located on a separate line, character ; at the end is indicated.

  • print_on - enable information output to the Output tab of the current sheet of the query editor.

In this case, the information is not displayed in the grid, the program opens the cursor, traverses the entire data set and displays information in the field, and then closes the cursor

  • print_off - disable output of information to the Output window of the current sheet

  • clear_out - clear the Output tab

  • colname_on - Show the names of the output columns

  • colname_off - Do not show output column names (default)

  • cstr ('') - Output a string in parentheses. The string must be surrounded by single quotes

  • delim () - add the delimiter specified in parentheses for lines in the output. Any character, string, or numbers can be used as a separator. Single quotes are not required

  • qpr_on - enable the output of the text of the completed request

  • qpr_off - disable the output of the text of the completed request (by default)

  • rdelim_on - display the column separator in the output

  • rdelim_off - do not display the column separator in the output (default)

  • snum_on - add line numbering to output

  • snum_off - do not show line numbering in output (default)

  • qset - create a global variable for the sheet. A variable is a pair: variable name = value

  • command format:

  • qset ('variable name', 'value')

  • IMPORTANT: variable names must differ by the first characters, not the last, i.e.

  • Right:

  • qset ('str', 'value 1');

  • qset ('2str', 'value 2');

  • Not properly:

  • qset ('str', 'value 1');

  • qset ('str2', 'value 2');

CerebroSQL using qset procedure

  • qunset - remove a global variable. Command format: qunset ('variable name')

 

OPENLIST procedure

The openlist procedure is used to use a connection to the database of one sheet in another.

The procedure can be used in 2 operations: create and insert

IMPORTANT: data transfer is carried out in text form, i.e. on the receiver side, the table will be created with fields of type Text or varchar, depending on the DBMS

Using a Procedure in a CREATE Statement

Syntax:

create table <table name> as openlist ('<Sheet name>', '<Query executed on the source side>')

Requirements:

  • escaping quotes inside a request is forbidden

  • only data types that can be cast to text are supported

 

Example

openlist using.jpg

 

Execution log:

The query: 04/24/2020 01:27:59
Query text: create table feature_usage_sta ...
-------------------------------------------------- -------
Position: 42
################## Remote query ##################
SELECT name,
detected_usages detected,
total_samples samples,
currently_used used,
to_char (last_sample_date, 'DD-MM-YYYY: HH24: MI') last_sample,
sample_interval interval
FROM dba_feature_usage_statistics
where version = (select version from v$instance)
order by currently_used desc, name
################## Parameter ##################
Remote list name: LIST1
Remote list type: ORACLE
Current list name: LIST3
Current list type: MSSQL
##################### Command ####################
create table feature_usage_statistics as
Code: 507
Count: 4
SELECT name,
detected_usages detected,
total_samples samples,
currently_used used,
to_char (last_sample_date, 'DD-MM-YYYY: HH24: MI') last_sample,
sample_interval interval
FROM dba_feature_usage_statistics
where version = (select version from v$instance)
order by currently_used desc, name
feature_usage_statistics
Toper: 3
create table feature_usage_statistics ("NAME" Text, "DETECTED" Text, "SAMPLES" Text, "USED" Text, "LAST_SAMPLE" Text, "INTERVAL" Text)
Table create - successful
Starting load data
Rows transferred: 243 [ALL ROWS: 243]
Run time: 0.592c.
Log operation
C:\CerebroSQL3.8.0.1x64\tmp\lists\openist2020_04_24_012759.log

File:

openist<date>.log - log file.

openist<date>.query - error file, when an error is caught while processing a line, both the error text and the request are dumped to the file

 

HELP

Code syntax hint, as well as a wizard for creating code blocks for the syntax hint

Code hinter starts

  • double click in the tree on "HELP"

  • Alt + H key combination

  • in the window menu: general - show helper

CerebroSQL help sql command.jpg
 
View help topic page

View saved command descriptions with code examples.

Window structure
  • Filter - field for searching command description

  • rdbms version - global filter for displaying command descriptions of the selected version of the DBMS

  • List command - list of topics

  • URLs official document - html link to official documentation

  • Text document - command description

  • Example - example code

Edit topic page

Command description editor

Topic creation

  • Select the type of DBMS from the drop-down list "RDBMS"

  • Press the button "New topic"

 

Data export / import wizard to / from CSV file / a

The wizard allows you to unload all table data (or part, based on a query) into a CSV file of the selected encoding and specified formatting

Export data to CSV file
 
CerebroSQL export data in file.jpg

​​

  • Owner - the name of the schema in which the table is located (read-only)

  • Table name - the name of the table from which the data is being downloaded (read-only)

or 

  • SQL - the text of the query retrieving data from the database

  • Operation type - type of operation (informational)

  • File path - full path to the CSV file

  • Encoding - encoding of the generated CSV file (ANSI, UTF8, UTF16)

  • Delimiter is a character used to frame text lines. The default is double quote

  • Separator - column separator. The default is a comma

  • WithFieldsNames - add column names to the CSV file as the first line ( it is always recommended to set! )

  • TrimLeft - remove spaces from the left

  • TrrmRight - remove spaces on the right

Press the " RUN " button

The execution log is displayed on the " Log " tab

When unloading data, the title of the window displays the number of lines uploaded to the file  

Import data from CSV
  • Owner - the name of the schema in which the table is located (read-only)

  • Table name - the name of the table into which the data is loaded (read-only)

  • Operation type - type of operation (informational)

  • File path - full path to the CSV data file

  • Encoding - CSV file encoding (ANSI, UTF8, UTF16)

  • Delimiter is a character used to frame text lines. The default is double quote

  • Separator - column separator. The default is a comma

  • WithFieldsNames - generate mapping of columns in the file and fields in the table automatically based on the line with the name of the columns in the file ( it is always recommended to set it! )

  • TrimLeft - remove spaces from the left

  • TrrmRight - remove spaces on the right

Press the " RUN " button