Feedback Print

Database Builder

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 Postgres.

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.

Acessing the Database Builder

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

Connection select screen

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

Database Builder home screen

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.

Execute SQL

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

Executed commands history

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

Executed SQL result

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.

Used tables summary

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.

Execute SQL

Export Tables

Enables the creation of a dump in the database.

Tables exportation

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.

Create table

Database Change

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

Change database

Database Schema

Display a relationship diagram of the database tables.

Table diagram

Privileges

Edit the users privileges to the current database.

Privileges

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.

Creating a View

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

View creation result

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

View and tables result

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.

Creating a procedure

The executed command will be displayed.

Log of command executed in procedure creation

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

Procedure 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).

Calling procedure through the list

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

Procedure call result

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)’.

Calling procedure using the SQL command

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.

Creating a function

The executed command will be displayed.

Creating a function

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).

Calling procedure through the list

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

Procedure call result

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)’.

Calling procedure through SQL command