Develop By Example – Working with custom queries using Node.js

In all the previous blog posts we covered a lot of examples of how to perform actions in a MySQL server set up as a document store. In all the code examples we used the XSession to communicate to the MySQL server.

The XSession is a logical session that abstracts our connection. With it, we can be connected to one or more servers and also give us the required support to work with collections and relational tables. But there is something that the XSession does not provide, full SQL language support.

When you need to execute a query that is not supported by an XSession, use a NodeSession instead. The NodeSession object can be useful, for example, when you want to connect to a specific MySQL server and do operations specifically on this server, or when you want to execute a specific function or create a stored procedure.

The following example demonstrates how to use a NodeSession object.

In the previous example, we use the method getNodeSession to get the NodeSession object (nodesession). Then we call the nodesession objects’s executeSql method to set the schema that we are going to use. To execute the query, we need to call the execute method. When the query that sets the current database completes, it returns a Result object (result) that contains information about the execution of the query. In this instance, the result object does not contain any useful information other than reporting the success or failure of the operation. Then, we call the executeSql method again, setting a query that may return one record. Once the query is executed, a result object is received, but in this case, it contains a row returned from the query represented as an array object. Each item in the array represents a column of a row. We access the array object by indexes to get the information we require, in the code we get the value of the column 0 (index 0) to write it in the console.

Of course, the previous example can be accomplished without using a NodeSession object. For that reason, in the next example we are going to create a more complex query that cannot be handled by an XSession object.

Suppose that we have a collection where we store the reviews for a movie, and the review contains a rating. We want to get the 10 best rating movies based on the average of the rating that the movie has. Currently, we do not have support to calculate the average using an XSession object, and so for this case, we need a NodeSession.

The next example demonstrates how to accomplish what we want.

In the previous example we get the NodeSession object (nodesession). Then we call the nodesession object’s executeSql method to define the query we want to execute. As you can see in this example, we don’t set the schema we want to use, but specify it in the query (mySchema.reviews). Once the query has executed, we log the results returned to the console and at the end the node session is closed.

In the query you can notice that we are using a function called “JSON_EXTRACT”, which is one of the many functions introduced in MySQL Server 5.7 that enable you to create and query JSON documents. For more information about these functions, see the MySQL Reference Guide articles JSON Functions and Manipulation JSON Data.

See you in the next blog post.

HowTo: Starting with MySQL EF Core provider and Connector/Net 7.0.4

This article shows the essential parts of the configuration for a quick console application using the .NET Core command-line (CLI) tool. The application will show how to create an EF Core model and some basic operations that can easily be done in Windows, Linux or OSx.

1. Requirements

Install the sdk, framework and tools:

Windows:
Install
– .NET Core SDK for Windows direct link here
– Visual Studio 2015 Update 3* here’s the publication page with more information about the update.
– .NET Core 1.0 tooling for Visual Studio direct link here

Linux:
An official detailed guide can be followed here.

OSx:
Follow the instructions and download the official installer here

2. Create the console application

This tutorial shows instructions for developing the application in Windows, but the same application can be done just changing the commands to the equivalent in the corresponding platform.

1. Create a folder where the new project will be saved.

On a command prompt type:
> mkdir efcore

2. Create the project
> cd efcore
> dotnet new

3. Create an appsettings.json file to hold your connection string information. Example:

Note: Adjust your server settings accordingly to your MySQL server configuration and user.

Notice the sslmode key in the connection string. This tells the server to use a plain connection instead of a secured one. If you like to use ssl mode then change this value to Preferred.

4. Modify the project.json to add the EntityFrameworkCore dependencies, add the MySQL references and specify that the appsettings.json file must be copied to the output (buildOptions section) so it becomes available to the application when building it.

The result should look like the following:

5. After project.json modifications a restore of the packages in the application is mandatory. This command will download all the dependencies needed. In the console window type the following:

> dotnet restore

6. Create a database context for Entity Framework
Inside a text editor, or the IDE you prefer to use, create a EmployeesContext.cs file.
Here is an example of a context with some entities.

7. Replace the contents of the Program.cs file with the following code:

8. Build the application with the following command:

> dotnet build

9. Run the application

> dotnet run

The output from this console application is:

Employee was saved in the database with id: 1

Conclusion

Entity Framework is a well known technology for data access in .NET applications. With this new Core version, developers can create applications for Windows, Linux and OSX without changing the application code. The MySQL provider and EF core is a great combination for applications that target the new .NET Core version.

We love to hear your thoughts or any comments you have about our product. Please send us your feedback at our forums, fill a bug at our community site, or leave us any comment at the social media channels in Twitter or Facebook.

Enjoy and thanks for the support!

On behalf of the MySQL Connector/Net team

MySQL Connector/NET 7.0.4 m3 has been released

Dear MySQL users,

MySQL Connector/Net 7.0.4 is the first development release that expands cross-platform support to Linux and OS X when using Microsoft’s .NET Core framework. Now, .NET developers can use the X DevAPI with .NET Core and Entity Framework Core (EF Core) 1.0 to create server applications that run on Windows, Linux and OS X. We are very excited about this change and really look forward to your feedback on it!

MySQL Connector/Net 7.0.4 is also the third development release of MySQL Connector/Net to add support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see http://dev.mysql.com/doc/x-devapi-userguide/en/index.html. For more information about how the X DevAPI is implemented in Connector/Net, see http://dev.mysql.com/doc/dev/connector-net.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

To download MySQL Connector/Net 7.0.4 M3, see the “Development Releases” tab at http://dev.mysql.com/downloads/connector/net/

Also you can find the new nuget packages for the .net core framework at the nuget gallery or using the following direct links:

MySql.Data package for core framework

MySql.Data.EntityFrameworkcore for core framework

Packages for full .net framework

MySql.Data package

MySql.Data.Entity package

MySql.Web package

MySql.Fabric package

Functionality Added or Changed in 7.0.4 M3 development release

  • Added X DevAPI support for URI connection strings in the following formats:
    mysqlx://[<user>[:<password>]@]<host>[:<port>]
    mysqlx://[<user>[:<password>]@]<host>[:<port>]/<database>
    mysqlx://[<user>[:<password>]@]<host>[:<port>]/[<database>]?<option>=<value>[&<option>=<value>]
    mysqlx+ssh://[<user>[:<password>]@]<host>[:<port>]
  • Added X DevAPI support for flexible parameter lists that do not require string parsing.
  • Views are no longer implemented using a separate View class in the X DevAPI. Instead, the IsView property has been added to the Table class and views are implemented as tables.
  • Added in MySql.Data support for .NET Core 1.0, which runs on Windows, OS X, and Linux.
  • Added in MySQL.Data.EntityFrameworkCore support for Entity Framework (EF) Core (includes support for .NET Framework 4.5.1).

Bugs Fixed

  • Passing no document or a DbDoc object that contained an empty array to the Add() method of a collection would throw an exception. Now, passing in either an empty document or array of documents returns a Results object in which RecordsAffected is zero. (Bug #23542066)
  • Passing a DbDoc object that contained an array to the Add() method of a collection would throw an exception. (Bug #23542031)

We love to hear your thoughts or any comments you have about our product. Please send us your feedback at our forums, fill a bug at our community site, or leave us any comment at the social media channels.

Enjoy and thanks for the support!

On behalf of the MySQL Connector/Net team

MySQL Connector/NET 6.8.8, and 6.9.9 have been released

Dear MySQL users,

MySQL Connector/Net 6.8.8 and 6.9.9 are maintenance releases for the 6.8 and 6.9 series of the .NET driver for MySQL. They can be used for production environments.

It is appropriate for use with MySQL server versions 5.5-5.7.

These are now available in source and binary form from our downloads and mirror sites. (note that not all mirror sites may be up to date at this point-if you can’t find this version on some mirror, please try again later or choose another download site.)

Functionality Added or Changed

  • Added TLS support for TLSv1.1 and TLSv1.2 when connecting to MySQL Server 5.7.

Bugs Fixed

  • Improvements were made to how the connector handles aborted connections. (Bug #23346197, Bug #80997).
  • With Entity Framework 6, building a table with a primary key would not enclose the key name in quotes, which caused a syntax error. (Bug #22696180, Bug #22696207, Bug #76292).
  • The connector was not disposing the transaction returned by DBContext.Database.BeginTransaction() when exiting a using code block. (Bug #22514355, Bug #22514363).
  • The connector did not attempt to enumerate stored procedures via mysql.proc(). Instead it looked up stored procedures in INFORMATION_SCHEMA.ROUTINES. This could have led to performance degradation in certain scenarios. (Bug #20960373, Bug #23528155, Bug #74116)

For documentation please check at the official site.

Nuget packages are available at:

6.9.9 Packages

MySql.Data 6.9.9

MySql.Data.Entity 6.9.9

MySql.Fabric 6.9.9

MySql.Web 6.9.9

6.8.8 Packages

MySql.Data 6.8.8

MySql.Data.Entity 6.8.8

MySql.Web 6.8.8

We love to hear your thoughts or any comments you have about our product. Please send us your feedback at our forums, fill a bug at our community site, or leave us any comment at the social media channels.

Enjoy and thanks for the support!

On behalf of the MySQL Connector/Net team

MySQL Notifier 1.1.7 has been released

Dear MySQL users,

The MySQL Windows Experience Team is proud to announce the release of MySQL Notifier version 1.1.7.

MySQL Notifier enables developers and DBAs to easily monitor, start and stop all their MySQL database instances. It provides a familiar Microsoft SQL Server look and feel and integrates with MySQL Workbench.

 

MySQL Notifier is installed using the MySQL Installer for Windows.

The MySQL Installer for Windows comes in 2 versions

  • Full (150 MB) which includes a complete set of MySQL products with their binaries included in the download
  • Web (1.5 MB – a network install) which will just pull the MySQL Notifier over the web and install it when run.

You can download the MySQL Installer for Windows from our official Downloads page at http://dev.mysql.com/downloads/installer/.

 

Changes in MySQL Notifier 1.1.7 (2016-08-01)

Functionality Added or Changed

  • The automatic migration of connections from MySQL Notifier to MySQL Workbench can now be delayed for a period of time or suspended indefinitely.
  • An option for setting the interval to ping monitored MySQL Server instances for status changes was added to the Actions > Options menu.

Bugs Fixed

  • The Configure Instance option was renamed to Manage Instance.
  • An error was displayed when valid domain user credentials were used to add a remote Windows service.
  • Testing new connections for remote MySQL instances would display a “High Severity Error” message.
  • When MySQL Notifier was installed using the MySQL Installer for Windows, an exception was thrown and MySQL Notifier failed to start.
  • The SQL Editor… link to MySQL Workbench was disabled for each monitored MySQL instance or service when MySQL Workbench was installed.
  • Validation checking identified domain\username as an invalid format to use when adding a remote Windows service to be monitored. Now, domain users with sufficient permissions to access the remote computer can connect using the domain\username format.
  • The main XML element in “%APPDATA%\Oracle\MySQL Notifier\settings.config” was renamed to MySQLNotifier from MySQLForExcel. Indentations were added to improve the overall readability of configuration settings.
  • Windows services on a remote computer were not retrieved and could not be added to the list services to be monitored.
  • A corrupted connections.xml file caused MySQL Notifier to display a “Root element is missing” error message when started.
  • Attempts to subscribe to a remote Windows service for the first time failed and returned a “High Severity Error” message when the remote service was an instance of MySQL.
  • Case-sensitive naming prevented status changes to MySQL instances running as services for Windows. Now, service names for MySQL instances are added with compatible casing.
  • A “High Severity Error” raised by MySQL Notifier appeared intermittently during installations performed using the MySQL Installer for Windows.

 

Quick links:

Enjoy and thanks for the support!

Develop By Example – Document Store: Working with documents using Node.js

In the previous blog post we explained how work with the collection CRUD operations. In this blog post we are going to explain other functions that are related to document management.

We already know how to create collections, as well as how to add, delete, update and retrieve documents from them. But, how can we add a new field to a document or documents that are in a collection?

The following code demonstrates how to do it:

In the previous code, first we get the schema and the collection objects (schema and coll). Then we define the query variable that contains the where clause, and newDoc with the document used in the example. Next, we call the coll object’s modify method that receives the query variable as a parameter. Chained to the modify method is the set method, which receives a pair of objects; the first one is the field name and the second one is the value for the field. If you have already read the previous blog post, you may notice that the code is the same as the one used to update the value of a field in a document, except for the line that adds the new document we want to work with. The set method is used to add new fields or to update their value. Once the execute method is finished, the number of documents updated are written to the console as well as the updated document, and finally the connection is closed.

The following code demonstrates how to add multiple fields to a document:

As you can see in the previous code, we just chained the set method two times to add multiple fields to a document. You can chain the set method as many times as needed. If the query passed to the modify method finds multiple documents, all the documents found will be updated adding the new fields sent; so be careful with it.

What if we want to remove a field? We just need to use the unset method to accomplish this. In the next example we demonstrate how to do it:

The previous code looks almost identical to the code used to add fields to a document; the difference is that we use the unset method instead of the set method. The rest of the code is the same.

In the previous examples we used an array as the value for one of the fields of the documents we added. Connector/Node.js also has methods to handle the items inside the array.

If we want to add items to an array we have several options. One such option is demonstrated in the following example:

The code looks quite similar to the code of the previous examples; there is just a new method: appendArray. The method receives two parameters; the first one is the field that has an array as its value. The second parameter is the value for the item to be added, which will be added at the end of the array.

As well as for the set method, if you send a query to the modify method that returns multiple documents; all the documents will be updated as well.

Another way of adding items to an array field is by using the arrayInsert method:

As you can see the previous code is almost equal to the code where we used the appendArray method. The only difference is that we now use the arrayInsert method instead. The arrayInsert method receives two parameters; the first one is the name of the field including the index of the array where you want to insert the new item, if the index is out of bounds it will be inserted at the end of the array. The second parameter is the value of the new item.

If you remember, in previous examples we used the set method to update field values and to add new fields to a document.

Well we can also use set to add new items to an array. The following code demonstrates how to do it.

The code above is identical to previous examples which used the set method. In this example when calling the set method we use an index that is out of bounds, by doing that the method understands that the item does not exist in the array; so it will be added to the end of the array. But if the index passed to the method is the index of an existing item, the method will update it instead of adding a new item.

At this point we know how to add and update items in an array, but also we can remove items. The following code is demonstrates this:

In this example we pass the name of the field and the index position of the item we want to remove as parameters to the unset method. If we pass an index that is out of bounds of the array, no item will be removed from the array and we will not receive an exception, so be careful with that.

See you in the next blog post.