Using MongoDB with Toad Data Point

• Setting the Environment
• Creating a MongoDB Collection
• Creating a Connection to MongoDB from Toad Data Point
• Using the DB Explorer
• Using the Query Builder
• Exporting Query Result to Excel Spreadsheet
• Opening Multiple Connections

Setting the Environment

The following software is required for this tutorial.

-Toad Data Point

-MongoDB Database

Download the toaddatapoint_pro_4.1.0.226.exe application from https://www.quest.com/products/toad-data-point/ and double-click on the .exe file to install Toad Data Point.  Download the mongodb-win32-x86_64-3.4.4-signed.exe application from https://www.mongodb.com/download-center?jmp=nav#community and double-click on the .exe file to install MongoDB.

Add the directory path to the MongoDB bin directory C:Program FilesMongoDBServer.4bin to the PATH environment variable.

Start MongoDB server with the following command.

>mongod

MongoDB server gets started, as shown in Figure 1.

Figure 1. Starting MongoDB Server

Creating a MongoDB Collection

Create a MongoDB collection using the Mongo CLI, which is started with the following command.

>mongo

A connection gets established to mongodb://127.0.0.1:27017 and the Mongo CLI command prompt gets displayed, as shown in Figure 2.

Figure 2. Starting Mongo CLI

List the database instances with the show dbs command, and the local db should be listed as shown in Figure 3. Select the local database as the current database with the use local command.

Figure 3. Listing DBs

The database commands may be run using the Mongo shell helper method db.runCommand().The equivalent JavaScript shell helper methods may also be used to create a database and a collection.  To create a collection use the db.createCollection() method. As an example, create a collection called “catalog”.

>db.createCollection("catalog")

List the collections with the show collections command, and the catalog collection should be listed as shown in Figure 4.

Figure 4. Listing Collections

Set the collection to use as catalog with the use catalog command. Add a few documents to the catalog collection.

>use catalog
>doc1 = {"catalogId" : "catalog1", "journal" : 'Oracle Magazine', "publisher" : 'Oracle Publishing', 
    "edition" : 'November December 2013',"title" : 'Engineering as a Service',"author" : 'David A. Kelly'}
>db.catalog.insert(doc1)
>doc2 = { "catalogId" : 'catalog2', "journal" : 'Oracle Magazine', "publisher" : 'Oracle Publishing',
    "edition" : 'November December 2013',"title" : 'Quintessential and Collaborative',"author" : 'Tom Haunert'}
>db.catalog.insert(doc2)

Each document stored in MongoDB must include a primary key field called _id of type ObjectId. The primary key field may be specified explicitly, as in the following command.

>doc1 = {"_id": ObjectId("507f191e810c19729de860ea"), "catalogId" : "catalog1", "journal" : 'Oracle Magazine', 
    "publisher" : 'Oracle Publishing', "edition" : 'November December 2013',
    "title" : 'Engineering as a Service',"author" : 'David A. Kelly'};
>db.catalog.insert(doc1)

If not specified in a BSON document construct, the _id field gets added automatically when a document is added.

Creating a Connection to MongoDB from Toad Data Point

 In this section we shall create a connection to MongoDB from Toad Data Point. Select Connect>New Connection in Toad Data Point as shown in Figure 5.

Figure 5. Connect>New Connection

In the list of data sources displayed, select MongoDB as shown in Figure 6.

Figure 6. Selecting MongoDB as Data Source to Connect to

The Create New Connection wizard gets started, as shown in Figure 7.

Figure 7. Create New Connection Wizard

Specify Host as 127.0.0.1, Port as27017,Database as local and click on Connect as shown in Figure 8.

Figure 8. Connecting to MongoDB

Toad Data point connects to MongoDB and performs an initial analysis of the content, such as getting column and data type information, as indicated by the dialog prompt in Figure 9. Click on OK.

Figure 9. Performing initial Content Analysis

A connection to MongoDB gets established and a connection node gets added to the Navigation Manager, as shown in Figure 10. The Tables section in Object Explorer lists the tables (collections), including the catalog collection created earlier.

Figure 10. Connection to MongoDB added in Navigation Manager

The columns for the catalog collection include a catalog_key identifier key for the _id field in the collection, as shown in Figure 11.

Figure 11. The Primary Key is the catalog_key

Using the DB Explorer

The DB Explorer in Toad Data Point provides an interface to explore a data source. Select the catalog collection in Tables and right-click on the MongoDB connection node and select New DB Explorer as shown in Figure 12.

Figure 12. New DB Explorer

The Columns tab is selected by default. The different columns in the catalog collection, including information about column data types, whether a column is null and primary key, get displayed as shown in Figure 13.

Figure 13. Table Information

Select the Data tab to display the data in the catalog collection as shown in Figure 14.

Figure 14. Displaying Data in catalog Collection

To display the diagram/s, including dependency tables, select the Relationships tab.  A dialog prompt gets displayed to inform about the Diagram Dependencies, as shown in Figure 15. Click on OK.

Figure 15. Diagram Dependencies

A diagram for the catalog table gets displayed, as shown in Figure 16.

Figure 16. Diagram for the catalog Table

Using the Query Builder

Toad Data Point provides a Query Builder to query a data source. In this section we shall query the MongoDB collection catalog with the Query Builder.  Right-click on the connection node and select New Query Builder as shown in Figure 17.

Figure 17. New Query Builder

The Query Builder wizard gets started, as shown in Figure 18. The Query Builder has three tabs: Diagram, Query, and Result. The Diagram tab, which is selected by default, displays the diagram of the table with selectable columns to build a query.

Figure 18. Query Builder Diagram

Select the columns to be included in the query as shown in Figure 19.

Figure 19. Selecting Columns to include in a Query

Select the Results tab to display the query as shown in Figure 20.

Figure 20. Displaying Query

To run the query, click on the Execute the SQL Statement button as shown in Figure 21.

Figure 21. Running SQL Query

Click on the Results tab to display the query results, as shown in Figure 22.

Figure 22. Query Results

Exporting Query Result to Excel Spreadsheet

In this section a query result shall be exported to an Excel spreadsheet. Click on the Export data to an Excel file icon drop-down list as shown in Figure 23.

Figure 23. Export Data to an Excel file

Select from one of the file formats, Excel File, to export to an Excel file as shown in Figure 24.

Figure 24.  Exporting to an Excel File

The query result gets exported to an Excel file, as shown in Figure 25.

Figure 25. Export Finished

To list the event log click on the View Event Log link. The event log information about the start and end time of the export, the duration taken for the export, the Excel version, the number of objects exported as shown in Figure 26.

Figure 26. Event Log

The Excel file may be opened at the link listed in the Export Finished dialog to display the export result, as shown in Figure 27.

Figure 27. Exported Excel File

Opening Multiple Connections

Multiple connections to MongoDB may be opened. To create another connection right-click on the connection and select Create Like as shown in Figure 28.

Figure 28. Create Like

In the Create New Connection wizard the connection properties are pre-specified. Select a Category from those listed as shown in Figure 29.

Figure 29. Selecting a Category for the new Connection

Select Development as an example and click on Connect as shown in Figure 30.

Figure 30. Making a Duplicate Connection

Another connection node gets added, as shown in Figure 31.

Figure 31. Duplicate Connection

Conclusion

In this tutorial we discussed accessing MongoDB from Toad Data Point including creating a connection, using the DB Explorer to display the table/s data, using the Query Builder to query a MongoDB table, and exporting a query result to an Excel file.