Database Builder

View video

This is a tool that allows the management of your database into ScriptCase. This tool is avaliable to connections with: MySQL, SQLServer(Cannot be ODBC), SQLite, Oracle and PostGreSQL.

In the beginning we must select an existing connection in the project.

This tool can be accessed in the menu Database.

The options and settings on the Database Builder depends of the user database privileges and the used database.

Accessing the Database Builder

First, select the database connection that we wish to display.

Next step, we will go to the home screen from Database Builder, where we can access all the available functionalities.

At the screen left corner, we will see a few functionalities and the table list, to a quickly access of the selected database.

DB

Allows see the database that is being accessed in the moment. Here we can change the database.

To the databases which possess user access restriction, it is necessary that the user set in the connection creation has access to all the databases

SQL Commands

Allows to execute any SQL command, from a select command to procedure commands of creation and execution, for example.

In this case, we will execute a simple SELECT.

Below the code insertion area, we can see a history of commands that were executed.

If everything goes well, when runs the command, will be showed the consult result, with the edição dos registros possibility.

Below of the displayed result, we will see a summary of the used tables in the SQL, informing the number of lines, the quantity of id fields and other informations, even the executed SQL.

To return to the database builder home screen, select the table in the ‘breadcrumb’ in the page top.

Import Files

Allows you to execute .SQL files.

Export Tables

Enables the creation of a dump in the database.

We must review the default settings of the exportation, for that we can change them and format according with our necessities.

• Output - Actions around the exportation.
  • Open - Display all the generated SQL inside the ScriptCase.
  • Save - Generates a .sql file.
  • gzip - Generates a .sql.gz file.
• Format - Format of the created file.
• Database - Defines the commands utilization referring at the database creation.
• Tables - Defines the commands utilization referring at the database creation.
• Data - Defines the commands utilization at the data insertion.

Create Tables

Enables the tables creation through of a simple and intuitive interface, where we can create them quickly.

Database Change

Enables change the database name, your default collation or delete the database.

Database Schema

Display a relationship diagram of the database tables.

Privileges

Edit the users privileges to the current database.

Create View

Enables a quickly creation of a view, where we must inform only the name and the select that will be used in the view.

After save, we can see the command executed and the structure of the created view.

All the created views can be displayed toghether the database tables.

Create Procedure

This functionality makes the procedure creation more easily. The parameters must be informed like in the example below, where we obligatory must inform the parameter type (input and/or output), your name, the data type that will recieve or return and your size.

The executed command will be displayed.

All the created procedures can be visualized below the table and view list.

Executing a Procedure

There are two ways to execute a procedure inside the database builder.

At visualize the procedure and functions list, we can click in their name. We will be redirect to the procedure call, where we must inform the input parameter(in case that procedure possess).

After that, will be displayed the result of the procedure execution and the used SQL for that.

The second way is using the SQL command functionality that allow us to execute any SQL command.

In this case, we must use the command to call a procedure: ‘CALL procedure_name(parameter)’.

Creating Functions

The parameters must be informed like in the example below, where we obligatory must inform your name, the data type that will receive and the size.

We must define the datatype of the function return too.

The executed command will be displayed.

Executing a Function

There are two ways to execute a function inside the database builder.

At visualize the procedure and functions list, we can click in their name. We will be redirect to the function call, where we must inform the input parameters(in case that possess).

After that, will be displayed the result of the execution and the used SQL for that.

The second way is using the SQL command functionality that allow us to execute any SQL command.

In this case, we must use the command to call a function: ‘CALL function_name(parameter)’.