Dear MySQL Community,
We have something we want to show you that we are very excited about. Stay tuned until Monday to learn all about it on the MySQL Server Blog.
And yes, the title of this blog post already might give you a big hint. But psssst! Let’s wait till Monday…
Dear MySQL users,
MySQL Connector/Net 7.0.5 is the second 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.5 is also the fourth 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.5 M4, see the “Development Releases” tab at http://dev.mysql.com/downloads/connector/net/
Changes in MySQL Connector/Net 7.0.5 (2016-09-06, Milestone 4)
Functionality Added or Changed
Nuget packages are available at:
MySql.Data package. Compatible with .NET 4.5.1 and Core framework 1.0
MySql.Data.EntityFrameworkCore. Compatible with .NET 4.5.1 and Core framework 1.0
MySql.Web. Compatible with .NET 4.5.1
MySql.Data.Entity Compatible with .NET 4.5.1
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
We are pleased to announce the next development release of MySQL Connector/J 6.0 which supports both JDBC 4.2 API and the new X DevAPI.
MySQL Connector/J 6.0.4 can be downloaded from the official distribution channels MySQL Downloads (see the “Development Releases” tab) and The Central repository. MySQL Connector/J source is also available on GitHub.
As always, we recommend that you check the CHANGES file in the download archive and/or the release notes to be aware of changes in behavior that might affect your application.
For documentation please visit the MySQL Connector/J 6.0 Developer Guide.
Note that Connector/J 6.0.4 is a milestone release and not intended for production usage.
I’d like to highlight the most important changes in this release:
The prefix used in connection string for X DevAPI is now unified between MySQL connectors. The “mysql:x:” we used in previous Connector/J 6.0 releases doesn’t work anymore, please use the “mysqlx:” one to establish XSession:
|
1 2 3 |
// Connect to server on localhost String url = "mysqlx://localhost:33060/test?user=mike&password=s3cr3t!" XSession mySession = new MysqlxSessionFactory().getSession(url); |
The com.mysql.cj.api.x.Table interface now represents both database tables and views. Schema.getTables() returns a list of Table objects for each existing database Table and View. Schema.getTable(name) also returns a Table object if the object with a given name is a View.
A new Table interface method was added:
|
1 2 3 4 5 6 |
/** * Check if the underlying object is a view or not. * * @return true if this Table is a View */ boolean isView(); |
The com.mysql.cj.api.x.View interface existed in previous Connector/J 6.0 releases isn’t available in Connector/J 6.0.4.
MySQL Connector/J 6.0.4 is suitable for use with MySQL server versions 5.5, 5.6, and 5.7 via Java Database Connectivity (JDBC) 4.2 API.
Due to changes in X Protocol implementation MySQL Connector/J 6.0.4 requires at least MySQL 5.7.14 server when working via X DevAPI.
Enjoy this new Connector/J and thank you all for your support!
On behalf of the MySQL Connector/J Team.
It is exciting times! Recently Microsoft released .NET Core 1.0, a cross platform implementation of the .NET Framework. .NET Core works on Windows, Mac OS, and Linux and is fully open source!
We wanted to enable users of Connector/Net to be able to use .NET Core so we have been working hard for the past few months to make our provider fully .NET Core compatible. Because .NET Core does not yet fully replace the traditional .NET Framework, we will continue to ensure our provider is compatible with both frameworks.
Along with these exciting changes, we are also including in our most recent release support for Entity Framwork 7.0 and Entity Framework Core 1.0.
In this article I will explain how to install and setup .NET Core and how to make a simple sample that connects and retrieve data in screen.
Further information about this new framework, API, etc. can be found on the product website https://www.microsoft.com/net/core
The SDK and dotnet CLI is available for all the supported platforms (see the website to know the list of specific platforms supported). Visual Studio Code (available at https://code.visualstudio.com/) is a new, fully open source editor that works on Windows, Mac, and Linux and provides a nice GUI for editing, compiling, and even debugging your project. When installing Visual Studio Code, be sure and install the plugin for C#. This will enable compiling and debugging of C# projects.
The installation guide followed in this page is https://www.microsoft.com/net/core#windows
You could install only the .NET Core 1.0 SDK and use Visual Studio Code. The download for the .NET Core 1.0 SDK is at https://go.microsoft.com/fwlink/?LinkID=809122.
If you are on Windows you can choose to use Visual Studio 2015. If you do not wish to purchase Visual Studio 2015, you can use the Community Edition which can be found at https://go.microsoft.com/fwlink/?LinkId=691978
To work with .NET Core projects you ‘ll want to make sure you are using Update 3 for Visual Studio 2015. That can be found at https://go.microsoft.com/fwlink/?LinkId=691129. Also, there is a separate download to provide some “tooling” for using in Visual Studio 2015. That can be found at https://go.microsoft.com/fwlink/?LinkID=824849
.NET Core 1.0 is compatible with serveral Linux variants. We will explain how to install it for Ubuntu (14.04 or 16.04, 15.04 it is not supported) or Linux Mint 17 and the guide used is here https://www.microsoft.com/net/core#ubuntu
Other Linux distributions are supported, follow the guide from the .NET Core website
First we need to set up the apt-get feed
|
1 2 3 |
sudo sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/dotnet-release/ trusty main" > /etc/apt/sources.list.d/dotnetdev.list' sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893 sudo apt-get update |
|
1 2 3 |
sudo sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/dotnet-release/ xenial main" > /etc/apt/sources.list.d/dotnetdev.list' sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893 sudo apt-get update |
If you have any previous versions of the .NET Core is necessary to run this script to remove any previous version. It is important to do this before you continue.
Use apt-get to install the .NET Core
|
1 |
sudo apt-get install dotnet-dev-1.0.0-preview2-003121 |
The guide used is here https://www.microsoft.com/net/core#macos
It is necessary to have the latest version of OpenSSL installed. The easiest way is installing Homebrew. Please follow the guide from here
After installing Homebrew execute the following command
|
1 2 3 4 |
brew update brew install openssl ln -s /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib /usr/local/lib/ ln -s /usr/local/opt/openssl/lib/libssl.1.0.0.dylib /usr/local/lib/ |
Download and execute the installer from here https://go.microsoft.com/fwlink/?LinkID=809124
Now that the SDK is installed we can proceed to create a sample project. You could use Visual Studio 2015 or Visual Studio Code.
There are two different kind of projects for Core, Library and Console app. So far .NET Core doesn’t have support for GUI style applications. The sample we will create now is a console app
If you are using Visual Studio 2015 then create a new project of type “Console Application (.NET Core)”, type the name of the project and click the “Ok” button
If you want to create using dotnet CLI and VS Code, open a prompt command, create a new folder for your project, change to that folder and create the project.
|
1 2 3 |
mkdir MyProject cd MyProject dotnet new |
After the project was created, it is necessary to do a “restore” ir order to download the dependencies
For Visual Studio 2015 click with Right button in the project and click in the menu “Restore Packages”
For dotnet cli/VS Code
|
1 |
dotnet restore |
After this compile your project in Visual Studio 2015 or run the “build” command using dotnet cli
|
1 |
dotnet build |
And last for execute this sample hit F5 on Visual Studio 2015 or use the “run” command for cli
|
1 |
dotnet run |
You should now see the output “Hello World!”
We have now a really basic app. Now lets add some code and a reference to the MySQL Connector for .NET Core dependency. We will continue using the dotnet cli and VS Code for the rest of this article.
Open the directory in the Visual Studio Code. We will see two files, program.cs and project.json
The project.json is our configuration project file and is where we have the project properties like name, version, author. Also we set the build and pack options We establish the framework targets and their specific configurations like the dependencies.
Our recently created project has a “project.json” file that should look like this
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
{ "version": "1.0.0-*", "buildOptions": { "debugType": "portable", "emitEntryPoint": true }, "dependencies": {}, "frameworks": { "netcoreapp1.0": { "dependencies": { "Microsoft.NETCore.App": { "type": "platform", "version": "1.0.0" } }, "imports": "dnxcore50" } } } |
In order to make a connection to a MySQL Server we need to add a reference to the MySQL Connector for .NET Core. We will add the dependency after the Microsoft.NETCore.App dependency. Notice that we could also add the the dependency as a general dependency that doesn’t depend on the target (in the empty section “dependencies” before the frameworks section. This is up to you and your needs.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
{ "version": "1.0.0-*", "buildOptions": { "debugType": "portable", "emitEntryPoint": true }, "dependencies": {}, "frameworks": { "netcoreapp1.0": { "dependencies": { "Microsoft.NETCore.App": { "type": "platform", "version": "1.0.0" }, "MySql.Data.Core": "7.0.4-IR-191" }, "imports": "dnxcore50" } } } |
Lets consider that we have a MySQL server up and running and it has the Sakila sample database configured. If you need to setup the database, sql script could be found here
After we change the dependencies, we must do a restore again in order that .NET Core downloads the NuGet package and we can use the it.
|
1 |
dotnet restore |
Now open program.cs and add a using statement to the MySql.Data.MySqlClient namespace and add code for create and open a connection
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
using System; using MySql.Data.MySqlClient; namespace ConsoleApplication { public class Program { public static void Main(string[] args) { MySqlConnection connection = new MySqlConnection { ConnectionString = "server=<ServerAddress>;user id=<User>;password=<Password>;persistsecurityinfo=True;port=<Port>;database=sakila" }; connection.Open(); } } } |
Now lets add code to get information from “Category” table from Sakila database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
using System; using MySql.Data.MySqlClient; namespace ConsoleApplication { public class Program { public static void Main(string[] args) { MySqlConnection connection = new MySqlConnection { ConnectionString = "server=localhost;user id=root;password=;persistsecurityinfo=True;port=3305;database=sakila" }; connection.Open(); MySqlCommand command = new MySqlCommand("SELECT * FROM sakila.category;", connection); using (MySqlDataReader reader = command.ExecuteReader()) { System.Console.WriteLine("Category Id\t\tName\t\tLast Update"); while (reader.Read()) { string row = $"{reader["category_id"]}\t\t{reader["name"]}\t\t{reader["last_update"]}"; System.Console.WriteLine(row); } } connection.Close(); System.Console.ReadKey(); } } } |
This is all that we need to retrieve data from the server in a simple sample. Let’s do a build and then run the sample (if didn’t do a restore then that is the first thing we should do)
|
1 2 3 |
dotnet restore dotnet build dotnet run |
The output will be something like this
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Category Id Name Last Update 1 Action 15/02/2006 04:46:27 2 Animation 15/02/2006 04:46:27 3 Children 15/02/2006 04:46:27 4 Classics 15/02/2006 04:46:27 5 Comedy 15/02/2006 04:46:27 6 Documentary 15/02/2006 04:46:27 7 Drama 15/02/2006 04:46:27 8 Family 15/02/2006 04:46:27 9 Foreign 15/02/2006 04:46:27 10 Games 15/02/2006 04:46:27 11 Horror 15/02/2006 04:46:27 12 Music 15/02/2006 04:46:27 13 New 15/02/2006 04:46:27 14 Sci-Fi 15/02/2006 04:46:27 15 Sports 15/02/2006 04:46:27 16 Travel 15/02/2006 04:46:27 |
Hope this simple sample helps you. While this post was written on Windows, the command line steps and code should work the same on Linux and Mac.
Enjoy.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
var mysqlx = require('mysqlx'); mysqlx.getNodeSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (nodesession) { nodesession.executeSql("USE mySchema") .execute() .then(result => { nodesession.executeSql("SELECT * FROM myColl LIMIT 1") .execute(function (result){ console.log(result[0]); nodesession.close(); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); }); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
var mysqlx = require('mysqlx'); mysqlx.getNodeSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (nodesession) { var query = 'select movie_name from' + ' (select JSON_EXTRACT(doc, "$.movie_name")'+ ' as movie_name, round(avg(JSON_EXTRACT(doc,' + ' "$.rate")), 2) as average from mySchema.reviews' + ' group by movie_name) as bestrated' + ' order by average desc limit 10;'; const execSql = nodesession.executeSql(query); execSql.execute(function (result) { console.log(result[0]); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); nodesession.close(); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); |
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.
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:
|
1 2 3 4 5 6 |
{ "ConnectionStrings": { "SampleConnection": "server=localhost;userid=root;pwd=;port=3305;database=sakila;sslmode=none;" } } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
{ "version": "1.0.0-*", "buildOptions": { "debugType": "portable", "emitEntryPoint": true, "copyToOutput": { "include": "appsettings.json" } }, "dependencies": { "Microsoft.Extensions.Configuration": "1.0.0", "Microsoft.Extensions.Configuration.Json": "1.0.0", "Microsoft.EntityFrameworkCore": "1.0.0", "MySql.Data.Core": "7.0.4-IR-191", "MySql.Data.EntityFrameworkCore": "7.0.4-IR-191" }, "frameworks": { "netcoreapp1.0": { "dependencies": { "Microsoft.NETCore.App": { "type": "platform", "version": "1.0.0" } }, "imports": [ "dnxcore50", "portable-net452+win81"] } } } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
namespace ConsoleApplication { using Microsoft.EntityFrameworkCore; /// <summary> /// The entity framework context with a Employees DbSet /// </summary> public class EmployeesContext : DbContext { public EmployeesContext(DbContextOptions<EmployeesContext> options) : base(options) { } public DbSet<Employee> Employees { get; set; } } /// <summary> /// Factory class for EmployeesContext /// </summary> public static class EmployeesContextFactory { public static EmployeesContext Create(string connectionString) { var optionsBuilder = new DbContextOptionsBuilder<EmployeesContext>(); optionsBuilder.UseMySQL(connectionString); //Ensure database creation var context = new EmployeesContext(optionsBuilder.Options); context.Database.EnsureCreated(); return context; } } /// <summary> /// A basic class for an Employee /// </summary> public class Employee { public Employee() { } public int Id { get; set; } [MaxLength(30)] public string Name { get; set; } [MaxLength(50)] public string LastName { get; set; } } } |
7. Replace the contents of the Program.cs file with the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
namespace ConsoleApplication { using System; using Microsoft.Extensions.Configuration; public class Program { public static void Main(string[] args) { var builder = new ConfigurationBuilder() .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true); var configuration = builder.Build(); string connectionString = configuration.GetConnectionString("SampleConnection"); // Create an employee instance and save the entity to the database var entry = new Employee() { Name = "John", LastName = "Winston" }; using (var context = EmployeesContextFactory.Create(connectionString)) { context.Add(entry); context.SaveChanges(); } Console.WriteLine($"Employee was saved in the database with id: {entry.Id}"); } } } |
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
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
Functionality Added or Changed in 7.0.4 M3 development release
Bugs Fixed
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
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.)
For documentation please check at the official site.
Nuget packages are available at:
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
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
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
Bugs Fixed
Quick links:
Enjoy and thanks for the support!
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$.name == 'NewField'"; var newDoc = { name: 'NewField', description: 'a new field', extra: ['hello', 'world'] }; coll.add(newDoc).execute().then(function (added) { coll.modify(query) .set('$.newField1', 'new field 1') .execute().then(function (updated) { console.log('Document(s) updated: ' + updated.getAffectedItemsCount()); coll.find(query).execute(function (doc) { console.log(doc); session.close(); }); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$.name == 'NewField'"; var newDoc = { name: 'NewField', description: 'a new field', extra: ['hello', 'world'] }; coll.add(newDoc).execute().then(function (added) { coll.modify(query) .set('$.newField1', 'new field 1') .set('$.newField2', 'new field 2') .execute().then(function (updated) { console.log('Document(s) updated: ' + updated.getAffectedItemsCount()); coll.find(query).execute(function (doc) { console.log(doc); session.close(); }); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$.name == 'NewField'"; var newDoc = { name: 'NewField', description: 'a new field', extra: ['hello', 'world'], field1: 'one', field2: 'two'}; coll.add(newDoc).execute().then(function (added) { coll.modify(query) .unset('$.field1') .unset('$.field2') .execute().then(function (updated) { console.log('Document(s) updated: ' + updated.getAffectedItemsCount()); coll.find(query).execute(function (doc) { console.log(doc); session.close(); }); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$.name == 'NewField'"; var newDoc = { name: 'NewField', description: 'a new field', extra: ['hello', 'world'] }; coll.add(newDoc).execute().then(function (added) { coll.modify(query) .arrayAppend('$.extra', 'this is a new item') .execute().then(function (updated) { console.log('Document(s) updated: ' + updated.getAffectedItemsCount()); coll.find(query).execute(function (doc) { console.log(doc); session.close(); }); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$.name == 'NewField'"; var newDoc = { name: 'NewField', description: 'a new field', extra: ['hello', 'world'] }; coll.add(newDoc).execute().then(function (added) { coll.modify(query) .arrayInsert('$.extra[1]', 'inserting a new item') .execute().then(function (updated) { console.log('Document(s) updated: ' + updated.getAffectedItemsCount()); coll.find(query).execute(function (doc) { console.log(doc); session.close(); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$.name == 'NewField'"; var newDoc = { name: 'NewField', description: 'a new field', extra: ['hello', 'world'] }; coll.add(newDoc).execute().then(function (added) { coll.modify(query) .set('$.extra[5]', 'new item array') .execute().then(function (updated) { console.log('Document(s) updated: ' + updated.getAffectedItemsCount()); coll.find(query).execute(function (doc) { console.log(doc); session.close(); }); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
var mysqlx = require('mysqlx'); mysqlx.getSession({ host: 'host', port: '33060', dbUser: 'root', dbPassword: 'my pass' }).then(function (session) { var schema = session.getSchema('mySchema'); var coll = schema.getCollection('myColl'); var query = "$.name == 'NewField'"; var newDoc = { name: 'NewField', description: 'a new field', extra: ['hello', 'world'] }; coll.add(newDoc).execute().then(function (added) { coll.modify(query) .unset('$.extra[1]') .execute().then(function (updated) { console.log('Document(s) updated: ' + updated.getAffectedItemsCount()); coll.find(query).execute(function (doc) { console.log(doc); session.close(); }); }) .catch(function (err) { console.log(err.message); console.log(err.stack); }); }); }).catch(function (err) { console.log(err.message); console.log(err.stack); }); |
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.