Toad World Blog

Using Toad Edge 1.1 with MySQL Database – New Database & New Table Wizards,Text Search, Import Data Wizard

Dec 5, 2017 2:00:00 PM by Deepak Vohra

Introduction

In an earlier article on using Toad Edge 1.1 with MySQL Database we discussed running MySQL on Docker Engine and connecting to the MySQL database with Toad Edge. In this continuation article we shall discuss some of the Toad Edge features including creating a new database with the New Database wizard, creating a new database table with the New Table wizard, Text Search (added in Toad Edge 1.1), importing data with the Import Data wizard, and setting the default database. The default database was called the active database in Toad Edge 1.0.

 

Creating a Database

By default only the mysql database is created for a user and the other databases are system databases. To create a new user database right-click on the Object>Explorer>Databases node and select New Database as shown in Figure 1.

Figure 1. Databases>New Database

In the New Database wizard specify a database Name, test as an example, as shown in Figure 2. Click on Finish.

Figure 2. New Database

The new database test gets added as listed in the Object Explorer>Databases in Figure 3.

Figure 3. New Database test

Right-click on test database to list the various options for the database as shown in Figure 4.

Figure 4. Database Options

By default a database is in AutoCommit ON mode.  A database may be set in AutoCommit Off mode by selecting Database>Disable AutoCommit as listed in Figure 5.

Figure 5. Database Options

A new feature added in Toad Edge 1.1 is Text Search, which is selected with Database>Text Search, as shown in Figure 6.

Figure 6. Database>Text Search

In Text Search the databases listed may be searched for database objects from the Object types selected, as shown in Figure 7.

Figure 7. Text Search

 

Setting the Database as the Default

The database in blue (ish) color is the default (active) database. SQL scripts run in a Worksheet are run on the default database, which was called active database in Toad Edge 1.0.  Only one database may be set as the default database. To set test as the default database right-click on test and select Set Database as Default as shown in Figure 8.

Figure 8. Selecting test>Set Database as Default

The test database becomes the default database, as shown in Figure 9.

Figure 9. The test Database as the Default Database

The different database objects are listed in the test database node, as shown in Figure 10.

Figure 10. Database Objects in test Database

 

Creating a Database Table

In this section we shall create a database table in the test database. Right-click on the test>Tables node and select New Table as shown in Figure 11.

Figure 11. Selecting test>Tables>New Table

The New Table wizard gets started, as shown in Figure 12. Other than the Table name all the other fields have default settings.  Comment is an optional field.

Figure 12. New Table

Specify a Table name (wlslog) and click on Next as shown in Figure 13.

Figure 13. Setting Table Name

In Table Columns click on New to add columns as shown in Figure 14.

Figure 14. Table Columns>New

The New Column wizard gets started, as shown in Figure 15.

Figure 15. New Column Wizard

Add logid, time_stamp, category, type, servername, code, and msg columns to the table. Specify a column name (logid) and select a Data Type INT as shown in Figure 16.

Figure 16. Selecting Data Type

Different data types have different additional fields for which a value must be specified. For data type INT specify Precision (10 is default). Select the Primary Key checkbox to make the column as the primary key. A primary key is Not Null by default. Optionally a Default Value and Comment may be specified. Click on OK as shown in Figure 17.

Figure 17. Adding a Column

The logid column gets added as shown in Figure 18.

Figure 18. The logid Column

To add another column click on New again as shown in Figure 19.

 

Figure 19. Adding another column

Add the time_stamp column of Data Type VARCHAR with default Size (20), and default Character set (Default -latin1) as shown in Figure 20. Click on OK.

Figure 20. Adding time_stamp Column

The time_stamp column gets added, as shown in Figure 21.

Figure 21. The time_stamp Column

Similarly, add the other columns category, servername, type, code and msg as shown in Figure 22.

Figure 22. Completing the Table Columns

The wlslog table columns get added as shown in the Object Explorer in Figure 23.

Figure 23. The wlslog Table Columns

After creating a database table the table properties may need to be modified. The column data type or column size may need to be modified, for example. We shall add table data in the next section and the sample data requires some of the column widths to be increased. Increase the column width for the msg and the time_stamp column as the default setting of 20 may not be enough for the sample data. To increase the column width from the default 20 double-click on the time_stamp column as shown in Figure 24.

Figure 24. Selecting the time_stamp Column for Modification

In the Edit Column Data Type dialog modify the Size from 20 to 200 as shown in Figure 25. Click on OK.

Figure 25. Increasing Column Size to 200

Similarly, increase the column size for the msg column. The Column sizes for the time_stamp and msg column are shown as increased in Figure 26.

Figure 26. Modified Column Size

Next, we shall add data to the wlslog table. Table data may be added by running a SQL Script with DML statements as shown in Figure 27.

Figure 27. Running SQL Script

Alternatively, data may be imported as we shall discuss in the next section.

 

Importing Data into Table

In this section we shall import data to the wlslog table. Create a CSV file (wlslog.csv) and copy the following sample data to the file.  The first line in the wlslog.csv file is for the column headers. The field delimiter in the sample data is “,” and the text delimiter is single quotes.

logid, time_stamp, category, type,servername,code, and msg

1,'Apr-8-2014-7:06:16-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STANDBY'
2,'Apr-8-2014-7:06:17-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STARTING'
3,'Apr-8-2014-7:06:18-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to ADMIN'
4,'Apr-8-2014-7:06:19-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RESUMING'
5,'Apr-8-2014-7:06:20-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000361','Started WebLogic AdminServer'
6,'Apr-8-2014-7:06:21-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RUNNING'
7,'Apr-8-2014-7:06:22-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000360','Server started in RUNNING mode'

The number of fields in the CSV file should be the same as the number of columns in which data is to be imported. The wlslog.csv file is shown in Figure 28

Figure 28. Sample data wlslog.csv File

Next, right-click on the wlslog table and select Import Data as shown in Figure 29.

Figure 29. Import Data

In the Import Data dialog select the Import Format as shown in Figure 30. Select the CSV file or ZIP archive with CSV content.  Click on the browse button for the Source field to select the CSV file to import data from.

Figure 30. Import Data

Select the wlslog.csv file as shown in Figure 31 and click on Next.

 

Figure 31. Selecting the wlslog.csv

The CSV settings (default) get displayed, as shown in Figure 32.The Destination Database Table is selected as wlslog. The CSV Format Settings should include the Field Delimiter as "," and the Text Delimiter as single quotes  "''". Click on Finish.

Figure 32. CSV Settings

The Data Import Result dialog indicates that Data import finished successfully, as shown in Figure 33. Click on Close.

Figure 33. Data Import Result

In the wlslog table detail click on the Data tab as shown in Figure 34.

Figure 34. Selecting the Data tab

The sample data imported gets displayed, as shown in Figure 35.

Figure 35. Sample Data in wlslog Table

To export data right-click on the wlslog table and select Export Data as shown in Figure 36.

Figure 36. Export Data

 

Disconnecting a Connection

To disconnect a connection right-click on the connection in the Connections view and select Disconnect as shown in Figure 37.

Figure 37. Disconnect

The connection gets disconnected as shown in Figure 38.

Figure 38. Disconnected Connection

A disconnected connection may be connected again by selecting Connect.  To remove a connection select Remove Connection as shown in Figure 39. In Confirm click on OK.

Figure 39. Remove Connection

 

Removing the Docker Service

To remove the Docker service run the docker  service rm command.

docker service rm mysql

The Docker service gets removed. Subsequently, list the services and the mysql service does not get listed as shown in Figure 40.

Figure 40. Removing Docker Service mysql

 

Conclusion

In this article we discussed some of the new features in Toad Edge 1.1 such as the Text Search feature and the active database (Toad Edge 1.0) being called the default database.

Tags: Toad Edge Development

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.