MySQL Connector/Net 8.0.8-dmr has been released

MySQL Connector/Net 8.0.8 is the fifth development release that expands cross-platform
support to Linux and macOS 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 macOS. We are very
excited about this change and really look forward to your feedback on it!

MySQL Connector/Net 8.0.8 is also the seventh 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.

Note

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 8.0.8-dmr, see the “Development Releases” tab at
http://dev.mysql.com/downloads/connector/net/

 


Changes in MySQL Connector/Net 8.0.8 (2017-07-10, Development Milestone)

   MySQL Connectors and other MySQL client tools and
   applications now synchronize the first digit of their version
   number with the (highest) MySQL server version they support.
   For example, MySQL Connector/Net 8.0.12 would be designed to
   support all features of MySQL server version 8 (or lower).
   This change makes it easy and intuitive to decide which
   client version to use for which server version.

   MySQL Connector/Net 8.0.8 is the first release to use the new
   numbering. It is the successor to MySQL Connector/Net 7.0.7.

     * Functionality Added or Changed

     * Bugs Fixed

   Functionality Added or Changed

     * X DevAPI: The format of document ID values generated when
       adding documents to a collection has changed. It is still
       a string of 32 hexadecimal digits based on UUID, but the
       order of digits was changed to match the requirement of a
       stable ID prefix.

     * All connections created using MySQL Connector/Net now are
       secure by default. Also, the Ssl-Enable connection option
       has been replaced by Ssl-Mode. Permitted Ssl-Mode values
       are None, Required (the default), VerifyCA, and
       VerifyFull.

       With this change, a non-SSL enabled server now requires
       the Ssl-Mode option be set to None explicitly in the
       connection string or the connection will fail.

     * X DevAPI: It is no longer permitted to pass an empty
       search condition, such as the NULL value or an empty
       string, to the Collection.Modify and Collection.Remove
       methods.
	
     * X DevAPI: The NodeSession class has been renamed to
       Session and the MySQLX.GetNodeSession method has been
       renamed to MySQLX.GetSession. Also, the XSession class
       has been removed.

     * X DevAPI: When creating a new connection, multiple hosts
       now can be specified as part of the connection string,
       which will try each host until a successful connection is
       established or all elements from the host list have been
       tried. The following connection-string formats are
       supported:

	    var mySession = MySQLX.GetSession(
	      "mysqlx://dbuser:password@[" +
	      "localhost:33060," +
	      "192.1.10.10:33060," +
	      "[2001:db8:85a3:8d3:1319:8a2e:370:7348]:33060" +
	      "]"
	    );

	    var mySession = MySQLX.GetSession(
	      "user=dbuser;" +
	      "password=dbpassword;" +
	      "server=" +
	      "192.1.10.10," +
	      "server.example.com," +
	      "localhost;" +
	      "port=33060;"
	    );

   Bugs Fixed

     * EF Core: The Database First feature did not support the
       following data types: BINARY, VARBINARY, MEDIUMBLOB,
       LONGBLOB, SET, DATE, TIME, and YEAR. (Bug #25493209)

     * EF Core: Database First support produced an error when
       the existing MySQL database included one or more views.
       (Bug #25493086)

     * EF Core: Using
       System.ComponentModel.DataAnnotations.Schema.TableAttribu
       te to initialize a new class instance that specified the
       name of an existing MySQL table produced incorrect
       mappings of table and column names.
       (Bug #25394223, Bug #84423)

Nuget

Packages are available at:

https://www.nuget.org/packages/MySql.Data/8.0.8-dmr
https://www.nuget.org/packages/MySql.Web/8.0.8-dmr
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/8.0.8-dmr
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore.Design/8.0.8-dmr

Enjoy and thanks for the support!

On Behalf of MySQL/Oracle Release Engineering Team
Prashant Team

MySQL Connector/Java 8.0.7-dmr has been released

Dear MySQL users,

MySQL Connector/J 8.0.7 Development Release is a development milestone release for the 8.0.x series.
This release includes the following new features and changes, also described in more detail on https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-7.html

MySQL Connectors and other MySQL client tools and applications now synchronize the first digit of their version number with the (highest) MySQL server version they support.
This change makes it easy and intuitive to decide which client version to use for which server version.

Connector/J 8.0.7 is the first release to use the new numbering. It is the successor to Connector/J 6.0.6

As always, we recommend that you check the “CHANGES” file in the download archive to be aware of changes in behavior that might affect your application.

To download MySQL Connector/J 8.0.7 dmr, see the “Development Releases” tab at http://dev.mysql.com/downloads/connector/j/

Enjoy!


Changes in MySQL Connector/J 8.0.7 (2017-07-10, Development Milestone)

MySQL Connectors and other MySQL client tools and
applications now synchronize the first digit of their version
number with the (highest) MySQL server version they support.
This change makes it easy and intuitive to decide which
client version to use for which server version.

Connector/J 8.0.7 is the first release to use the new
numbering. It is the successor to Connector/J 6.0.6.

* Functionality Added or Changed

* Bugs Fixed

Functionality Added or Changed

* X DevAPI: There are changes to some methods related to
the Result interface:

+ getLastDocumentId() and getLastDocumentIds() have
been replaced with getDocumentId() and
getDocumentIds(), which are put under a new
AddResult interface that extends Result.

+ A new getAutoIncrementValue() method is added to the
new InsertResult interface that extends Result.
See MySQL Connector/J X DevAPI Reference
(http://dev.mysql.com/doc/dev/connector-j) for more
details. (Bug #25207784)

* X DevAPI: It is no longer permitted to pass an empty
search condition, such as the NULL value or an empty
string, to the Collection.Modify() and
Collection.Remove() methods.

* X DevAPI: Connections using the X Protocol are now secure
by default. Also, the xdevapi.ssl-enable connection
option has been replaced by the xdevapi.ssl-mode option,
which has DISABLED, REQUIRED (default), VERIFY_CA, and
VERIFY_IDENTITY as its permitted values; see the
description for the new option in Configuration
Properties
http://dev.mysql.com/doc/connector-j/8.0/en/connector-j-
reference-configuration-properties.html
for details.

* X DevAPI: Consolidated the BaseSession, NodeSession, and
XSession interfaces into a single
com.mysql.cj.api.xdevapi.Session interface. The following
related changes were also made:

+ Renamed XSessionFactory to SessionFactory.

+ Consolidated the AbstractSession, NodeSessionImpl,
and XSessionImpl classes into the
com.mysql.cj.xdevapi.SessionImpl class.

+ Removed the Session.bindToDefaultShard() method and
the VirtualNodeSession interface.

+ The mysqlx.getNodeSession() method has been renamed
to mysqlx.getSession() and it now returns a Session
object.

+ The DatabaseObject.getSession() method now returns a
Session object (instead of the old Session
interface).
See MySQL Connector/J X DevAPI Reference
(http://dev.mysql.com/doc/dev/connector-j) for more
details.

* To avoid using JDBC statements inside core Connector/J
classes, the following changes have been implemented:

+ Created a new com.mysql.cj.api.Query interface,
which is implemented by StatementImpl.

+ Replaced the
com.mysql.cj.api.jdbc.interceptors.StatementIntercep
tor interface with the
com.mysql.cj.api.interceptors.QueryInterceptor
interface.

+ Added a new method, PacketPayload
preProcess(PacketPayload queryPacket), to
QueryInterceptor.

+ Renamed the connection property
statementInterceptors to queryInterceptors. See
Configuration Properties
(http://dev.mysql.com/doc/connector-j/8.0/en/connector-j-
reference-configuration-properties.html)
for details.

* Added Japanese collation for the utf8mb4 character set.

Bugs Fixed

* X DevAPI: createView() failed with a NullPointerException
when there were null inputs to it. This fix adds checks
for nulls, and makes Connector/J throw the proper errors
for them. (Bug #25575156)

* X DevAPI: createaTable() failed with a
NullPointerException when there were null inputs to it.
This fix adds checks for nulls, and makes Connector/J
throw the proper errors for them. (Bug #25575103)

* X DevAPI: The connection properties
enabledSSLCipherSuites, clientCertificateKeyStoreUrl,
clientCertificateKeyStoreType, and
clientCertificateKeyStorePassword were ignored for
connections using the X Protocol. (Bug #25494338)

* X DevAPI: Calling getNodeSession() with an URL string
containing SSL parameters caused a
CJCommunicationsException. This has been fixed by
creating a byte buffer to handle SSL handshake data.
(Notice that getNodeSession() has since been consolidated
into getSession().) (Bug #23597281)

* X DevAPI: Concurrent asynchronous operations resulted in
hangs, null pointer exceptions, or other unexpected
exceptions. This has been fixed by correcting a number of
problems with the SerializingBufferWriter and by limiting
the number of buffers sent with a gathering write. (Bug
#23510958)

* X DevAPI: When a thread failed to make a connection to
the server using the X Protocol, the client application
hung. A new connection property,
xdevapi.asyncResponseTimeout (default value is 300s), now
provides a duration beyond which the attempt to connect
timeouts, and a proper error is then thrown. See
description for the new option in Configuration
Properties
(http://dev.mysql.com/doc/connector-j/8.0/en/connector-j-
reference-configuration-properties.html)
for details. (Bug #22972057)

* Connector/J failed a number of regression tests in the
test suite related to geographic information system (GIS)
functions, because of changes to GIS support by the MySQL
server. The fix corrects the tests. (Bug #26239946, Bug
#26140577)

* Configuration templates named by the connection property
useConfigs were not recognized by Connector/J. (Bug
#25757019, Bug #85555)

* A NullPointerException was returned when getDate(),
getTime(), or getTimestamp() was called with a null
Calendar. This fix makes Connector/J throw an
SQLException in the case. (Bug #25650305)

* An ArrayIndexOutOfBoundsException was thrown when a
server-side prepared statement was used and there was a
NULL in a BLOB, TEXT, or JSON type column in the
ResultSet. (Bug #25215008, Bug #84084)

On Behalf of MySQL/ORACLE RE Team
Gipson Pulla

MySQL Connector/C++ 2.0.4 m2 has been released

MySQL Connector/C++ 2.0.4 is the next development milestone of the MySQL

Connector/C++ 2.0 series. Connector/C++ 2.0 can be used to access MySQL
implementing Document Store or in a traditional way, using SQL queries. It
allows writing both C++ applications using X DevAPI or plain C applications
using XAPI.

To learn more about how to write applications using X DevAPI, see X
DevAPI User Guide (http://dev.mysql.com/doc/x-devapi-userguide/en/) and X
DevAPI reference at
https://dev.mysql.com/doc/dev/connector-cpp/devapi_ref.html. For more
information about using plain C XAPI see XAPI reference at
http://dev.mysql.com/doc/dev/connector-cpp/xapi_ref.html. For generic
information on using Connector/C++ 2.0, see
http://dev.mysql.com/doc/dev/connector-cpp/.

Note

Connector/C++ 2.0 requires MySQL Server version 5.7.12 or higher
with X Plugin enabled. For general documentation about how to get
started using MySQL as a document store, see Using MySQL as a Document
Store (http://dev.mysql.com/doc/refman/5.7/en/document-store.html).

To download MySQL Connector/C++ 2.0.4, see the “Development Releases”
tab at http://dev.mysql.com/downloads/connector/cpp/

Changes in MySQL Connector/C++ 2.0.4 (2017-03-21, Development
Milestone)

Functionality Added or Changed

  • Support was added for secure sessions over TLS
    connections. A secure session can be requested either via
    the ssl-enable and ssl-ca options of a connection string,
    or using explicit session creation options. For X DevAPI
    session settings, see http://dev.mysql.com/doc/dev/connector-cpp/classmysqlx_1_1_session_settings.html.
    For XAPI session settings, see
    http://dev.mysql.com/doc/dev/connector-cpp/group__xapi.html
    (check the documentation for enum mysqlx_opt_type_t).
  • The format of document ID values generated when adding
    documents to a collation has changed. It is still a
    string of 32 hexadecimal digits based on UUID, but the
    order of digits was changed to match the requirement of a
    stable ID prefix.
  • The X DevAPI Schema object now supports methods for view
    manipulation: createView(), alterView(), and dropView().
    XAPI now contains functions that implement similar
    functionality: mysqlx_view_create(),
    mysqlx_view_replace(), mysqlx_view_modify(), and
    (implemented previously) mysqlx_view_drop().
    As with other XAPI operations, there are functions that
    create a statement handle without executing it:
    mysqlx_view_create_new(), mysqlx_view_replace_new(), and
    mysqlx_view_modify_new().
    These XAPI functions modify view DDL statements before
    execution: mysqlx_set_view_algorithm(),
    mysqlx_set_view_security(),
    mysqlx_set_view_check_option(),
    mysqlx_set_view_definer(), and mysqlx_set_view_columns().
  • Connector/C++ now supports IPv6 target hosts in
    connection strings and when creating sessions using other
    methods.

Bugs Fixed

  • When rList is an empty list, table.insert().rows(rList)
    caused a segmentation fault. (Bug #25515964)

On Behalf of the MySQL/ORACLE RE Team

Develop by Example – Document Store: Working with Express.js, AngularJS and Node.js

In previous blog posts we explained how to perform certain actions in a MySQL database set up as a document store using Connector/Node.js. In this blog post we are going to use some of the examples covered to explain how to start working with an application created with Express.js, AngularJS, Node.js, and MySQL Connector/Node.js.

Required

Optional

Before we start we need to install MySQL Server, Node.js and download MySQL Connector/Node.js.

Configuring MySQL Server

If you are using Windows and you installed MySQL Server using the MySQL Installer you might have already enabled the MySQL X Protocol plugin or you can enable it through a re-configuration of the MySQL Server, for more details go to the Plugins and Extensions section in the official documentation.

If you are not using windows or the MySQL Installer, once MySQL Server is installed and running we need to enable the X Plugin to be able to use MySQL as a document store.

To enable the X Plugin in the MySQL server using the MySQL command-line client you need to use the root account or an account with INSERT privilege on the mysql.plugin table:

  • Invoke the MySQL command-line client: mysql -u user –p
  • Run the following command: INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’;

Note: Replace mysqlx.so with mysqlx.dll for Windows.

Please refer to the official documentation for more information about setting up MySQL as a document store.

Installing and using Express

After Node.js is also installed we need to open a terminal and navigate to the path where we want to create our project. Once we are in the desired path, we are going to install the Express application generator, which will help us to create the skeleton for our application. To install it, run the following command in the terminal:

$ npm install express-generator g

The -g flag specifies that the package will be installed globally.

Now we are going to create the skeleton for our application by running the following command the in terminal:

$ express mysql-news --ejs

The –ejs flag is to specify that we are going to use EJS as our template language to use standard HTML instead of Jade, which is the default view engine used by Express. The name for our application was specified as mysql-news. After running the command, we are going to see a folder with the same name as our application with the following structure:

mysql-news/
├───app.js
├───package.json
├───bin/
└─www
├───public/
├───images/
├───javascripts/
└───stylesheets/
└─style.css
├───routes/
├─index.js
└─users.js
└───views
├─error.ejs
└─index.ejs

Now we need to install the dependencies of the template created. Run the following command in the terminal at the path level were our application folder were created:

$ cd mysql-news && npm install

To start the newly created application, run the following command in the terminal:

$ npm start

To verify that our application is running, open a browser and type: http://localhost:3000.
If everything it is ok, we will be able to see a webpage with the text: “Welcome to Express”

Installing MySQL Connector/Node.js

In our application folder copy the MySQL Connector/Node.js tar.gz file that you may have downloaded previously. Once it has copied, run the following command to add the package to our application:

$ npm install mysql-connector-nodejs-1.0.4.tar.gz

Note: verify that the version of the file downloaded matches the version of the command.

Once MySQL Connector/Node.js is installed, we can navigate to the path /node_modules/@mysql/xdevapi to see where it is located.

Installing AngularJS and Angular-Route

In this blog post we are going to use AngularJS and the Angular-Route module both using the 1.5.8 version. To install them, run the following command:

$ npm install angular@1.5.8 angular-route@1.5.8

Once the packages are downloaded and installed in the node_modules folder we are going to copy the folders angular and angular-route inside our public/javascripts folder.

Note: to avoid doing this manually you can install Bower, use the CDN for the AngularJS modules, or any other alternative.

Optional Software

Now just the optional software is left. If you want a visual representation of the schema and collections in the application, download and install MySQL Workbench. And if you want to add a boost to the front-end development, install Bootstrap. In this blog post we are using just the CDN for the CSS, but you can install the complete package.

The folder structure

The last thing before we start working on the code of our application is to create two folders in the root path of our application: models and data. In the end, our application will have something like the following structure:
mysql-news/
├───app.js
├───mysql-connector-nodejs-1.0.4.tar.gz
├───package.json
├───bin/
└─www
├───data/
├───models/
├───node_modules/
├───public/
├───images/
├───javascripts/
└───stylesheets/
└─style.css
├───routes/
├─index.js
└─users.js
└───views
├─error.ejs
└─index.ejs

Building the application

Uploading initial data

As you may have guessed by its name, this application will display news about MySQL. We will have the ability to create new posts or add comments to the news items that are already in the database.

Now we can start building out our application. The first thing that we are going to do is to add a JSON file in the data folder. We are going to name it news.json or you can choose another name. Copy and paste the following data to the file:

This initial data will help us to visualize our progress. To continue we need to update our application to create our schema and collection where we are going to upload the initial data.

Open the www file that it is inside the bin folder. This file contains the configuration for the web server that will host our application. The first thing that we are going to add is a function that creates our schema, our collection, and then upload the data to the collection. Add the following code at the end of the file:

In the previous code we define the configureDataBase function where we create a session to our local instance of MySQL server. Once we have the session we verify if the schema mysqlNews exists. If not, we create it and the news collection. Finally, we load the initial data into the news collection.

We have the function that initializes our schema and now we need to add another function that calls it just one time when the application starts. In the www file you will find a section that creates the http server below the line:

var server = http.createServer(app);

We are going to add the following code below that line:

In the previous code first we load our initial data into the initialData variable as well as the xdevapi module into the mysql variable. These variables are used by the configureDataBase function. The variables need to be defined before the function is called. Then we create an instance of an EventEmitter, and we configure the event that is in charge of calling the function that creates our schema and collection.

To complete the configuration, we just need to add the following lines inside the initializeDatabase function:

Note: the lines should be below our initializeDatabase function, verify that are not duplicated

Since we are going to load some data when the application starts, we need to first configure our database before the server accepts connections, that is the reason we are moving the above lines inside our function. In the end, our function should look like this:

Now that we have the code that configures our schema and loads some initial data, it is time to create the function that retrieves the data from the collection.


Getting the data

In our model folder we are going to add a new file called news.js. This module will contain all the methods used to perform actions in our collections. The first method that we are going to add to this module is the one that gets the documents from our collection.

First we are going to define two variables, one to load the MySQL xdevapi module and other one to store the configuration used to create connections to our server.

Now we are going to create the method that returns all the documents stored in our collection:

First we added the method getAll to the exports object of our module and then we call the getSession method to create a connection to our server. Once we have the session we get the schema and the collection where our documents are stored. We define an array variable to store the documents returned from the collection and we execute the find method without a filter to return all the documents. When the execute method is reached every document will be added to the array variable that will be returned. Then when the execute method is completed, if we received any document; we return null as the error message and the array of documents, or an error message and null as the array of documents otherwise.

Now we need to create the required files to call the method and display the documents from the database in the application.

In this blog post we are going to follow the folder structure suggested by AngularJS, putting each feature/entity in its own file and having a directory for feature. First we are going to create an AngularJS component named app.module.js inside the folder path public\javascripts, we are not creating a folder for this component because it will be used by all the features we are going to add.

In this file we are going to configure the modules required by the application and a factory to access the methods that we are going to add to our model file (news.js). For more information about a factory please see the AngularJS documentation.

We are going to add the following code in the file:

In the first line we set a name for our application (mysqlNews), and specify the modules that we are going to use: ngRoute and newsList. The newsList module is going to be the module that loads the documents in our application. And the ngRoute is an AngularJS module that provides the route and deeplinking services.

Then we create a factory that is going to be used by the other modules, the factory is named as newsFactory and we specify the module $http as the module that is going to be used by our factory. The $http module is core service that facilities the communication with the HTTP servers. Inside the factory we define an object that will have a property named news that will store the documents of our application and a method called getAll that will get the documents and copy them into the news property.

Now we are going to create another component named app.config.js, and also this component does not have a directory because will be used by all the features too. In this file we are going to configure the routes of our application.

In the previous code we set ‘!‘ as our prefix for our routing configuration and also we configure the template that is going to be used to load the news in our application.

To continue we are going to create a folder named newsList, this folder will contain the components and the template to display the news in our application. To this folder we are going to add a component named news-list.module.js with the following content:

angular.module('newsList', []);

The previous file just contains the name of this module, if we require additional modules we can set it here in this file. Now add another component named news-list.component.js with the following content:

The code we have just seen configures the template and also the controller that loads the documents into the template via the factory we created earlier. The news from the factory are copied to a property of the controller and then the method to get all the news is called, and also a property named order that is going to be used to sort the news, is added to the controller. The last file that we are going to add to this folder is the template file to display the documents. The file will be named news-list.template.html with the following content:

In the template we have a search and a filter section, and a list where we will display our documents. The search input is set to the controller’s search property that used in this template ($ctrl.search), the property is applied to the list of items to filter them. The sort select is set to the controller’s order property that is also applied to the list of items to sort them by title or by date published.

To complete this section code and have something we can test, we need to do just two more things. The first one is to update the file index.js that is inside the routes folder. We need to load our model, append the following line to the section where the variables to do that are defined:

var mysql = require('../models/news.js');

And we are going to add a new get method:

In the above method we are calling our model’s getAll method and sending back the information in JSON format. The last step to test our application is to update the index.ejs file inside the views folder.

And the second thing is to update our index file index.ejs. To start with, we update the html tag to include the language and the application name. The name is as defined in the app.module.js file: mysqlNews. If we do not set the ng-app directive AngularJS will not find the root element of our application and our components will not be loaded. The html tag should appear as follow:

<html lang="en" ng-app="mysqlNews">

Then we are going to add the link to our scripts and to the bootstrap CSS in the header:

And finally we are going to update the body adding the ng-view directive that is used by $route service to render the template configured by the current route loaded:

To test the application and verify that everything is configured properly, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We should see a search/sort section on the left-hand side of your browser and a list of the initial documents loaded to our collection next to it.

index

Adding Comments

At this time, we have an application that list the documents from a collection and let us do a search to filter the information and sort the documents by the published field. But the Comments section is still empty, so let us add the functionality to add comments.

Go to our model file news.js, and add the following method at the end:

In the previous method, first we create the session to work in our server then we get the schema and the collection. Once we have the collection we are calling the modify method specifying the _id of the post where we want to add the new comment. The comment is added to the document using the arrayAppend method which appends the comment to the end of the array. Once the execute method finishes, we verify how many documents were affected and sent the results back basis on it.

Then we need to update our factory (app.module.js), adding the new module that will be used to add comments newComment:

Also we need to add the AngularJS $location and $filter modules. We are going to use the $location module to change the URL of the application, and the $filter module to know to which post a new comment belongs.

Finally, we are going to add the method to create comments or cancel the addition:

In the addComment method, we send the data to the /comments route. Then the result returned is added to our local copy of the data. The $filter module is used to find by _id the post to which belongs the new comment. To finish we change the location of the application to the main page.

The next step is to update our route provider in the app.config.js file to handle the add comments route:

Now as we did for the components to display the news, we are going to create a folder in the public/javascripts path with the name comments, this folder will contain the components and the template to add new comments. We are going to start adding the new-comment.module.js component to the folder with the following content:

angular.module('newComment', ['ngRoute']);

In the file we are specifying that our new module has the name newComment and it will use the ngRoute module. To continue we are going to add another component named new-comment.component.js with the following code:

The previous code configures the template that is going to be used to add new comments and defines the controller to be used in the view. The controller receives the _id of the news to update and is stored in a controller property, then two methods are defined: addComment that sends the new comment to be added to our factory and cancelAddComment that cancels the current action.

The template that we are going to use to add new comments is going to be called new-comment.template.html and is defined next:

The item template consists only of a text area where the user can enter a comment and some basic validation. The Submit button is enabled only when the comment passes validation, this is done by the ng-disabled directive that check if all the inputs in the form (addComment) has valid values. The ng-model directive is used to set the value of the input to a controller’s property, comment is the property’s name in this case.

Then we need to update our route file (routes/index.js) adding a new method to save the new comments:

This method sends the new comment to the model to be stored in collection. Once it has been stored successfully, it returns the comment in JSON format.

We are going to add a link that will load the new view when it is clicked, we are going to update the template in the path javascripts/newsList/news-list.template.html adding the following code under the paragraph that lists the comments:

And the last step is to add the reference to our new scripts in the header of the index file (views/index.ejs):

To test these changes, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We see the new option to add coments:

add_comment

And when we click on it, we see a form to write new comments:

submit_comment

We see the new comment below the news item in the index page:

display_comment

Adding new posts

To finish the application, we are going to add the feature to add new posts to the application. We are going to start adding a new method to our model (models/news.js) at the end:

This new method accepts as a parameter a JSON object to be added to the collection. Once the new document is stored in the collection it is returned to the application. If the document cannot be stored, the method returns null and display an error message to the user.

Then we need to update our factory (app.module.js), and again the first thing to update is to add the new module that will be used to add new posts addNews:

Now we are going to add the new methods that add a new post or cancel the process:

The first method receives the data for the new post and sends it to the /addNews route, the document returned is added to our local copy of the data and the app location is changed to the index.

Next we are going to update our route provider in the app.config.js file to handle the add news route:

Now we are going to create a folder in the public/javascripts path with the name news. This folder will contain the components and the template used to add new posts. Then we continue adding the add-news.module.js component to the folder with the following content:

angular.module('addNews', ['ngRoute']);

In the component we are specifying that our new module has the name addNews and it will use the ngRoute module. Then we add another component named add-news.component.js with the following code:

The previous code configures the template that is going to be used to add new posts and defines the controller to be used in the view. The controller receives the data to create a JSON object and set the published date to be the current date. The addNews method sends the JSON object to the factory and the cancelAddNews method cancels the current action.

The last file for this folder is going to be the view template named add-news.template.html, and is defined with the following:

The item template contains the text inputs to set the Title, Link and Introduction for the new post. It also contains some validation checks to enable the Submit button just when the required fields are valid. Also the value of the inputs is set to the controller’s properties: title, link and introduction.

Then we need to update our route file (routes/index.js) adding a new method to save the new post:

This method sends the new post to our model and it returns the new document added in JSON format once it is saved in the collection.

We are going to add a link that will load the new view when it is clicked, we are going to update the file in the path javascripts/newsList/news-list.template.html adding the following code under the paragraph that has the search and sort options:

And the last step is to add the reference to our new scripts in the header of the index file (views/index.ejs):

To test the latest changes, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We see the new option to add new posts:

add_post

And when we click on it, we see a form to add new posts:

submit_post

After we add a new post, it is displayed in the index page:

display_post

In this post we learned how to create a full stack JavaScript application using MySQL, Node.js, Express, AngularJS. We hope you enjoyed the post and please stay tuned because we have a lot of new content to share with you.

See you in the next blog post.

MySQL Connector/C++ 2.0.3 m3 Development Release has been released

MySQL Connector/C++ 2.0.3 is the next development milestone of the MySQL Connector/C++ 2.0 series, and the first public release. Apart from covering more X DevAPI features, it adds a new, plain C API, called XAPI, that offers functionality similar to X DevAPI to applications written in plain C. Thus, not only can MySQL Connector/C++ be used to write C++ applications, as before.

Now, using the XAPI, MySQL Connector/C++ can be used to write plain C applications to access MySQL Database implementing a document store as well as execute traditional plain SQL statements. For more information about XAPI, refer to the documentation at http://dev.mysql.com/doc/dev/connector-cpp/xapi_ref.html.

To learn more about how to write applications using the X DevAPI, see X DevAPI User Guide (http://dev.mysql.com/doc/x-devapi-userguide/en/). For more information about how to use Connector/C++ 2.0 and how the X DevAPI is implemented in it, see http://dev.mysql.com/doc/dev/connector-cpp/.

Note

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 Using MySQL as a Document Store (http://dev.mysql.com/doc/refman/5.7/en/document-store.html).

To download MySQL Connector/C++ 2.0.3 m3, see the “Development Releases” tab at http://dev.mysql.com/downloads/connector/cpp/

X DevAPI Notes

New X DevAPI features added in this MySQL Connector/C++ release:

  • Methods for starting and controlling transactions
  • Using an X DevAPI URI or connection string to specify new session parameters
  • Capability of binding a session to the default shard and execute SQL statements there (using XSession.bindToDefaultShard())
  • Methods for counting elements in a table or collection
  • Access to multiple result sets if present in a query result
  • Methods to count items in a result set and fetch a complete result set at once (using fetchAll()), instead of accessing items one by one (using fetchOne())
  • Access to warnings reported when processing a statement (getWarnings())
  • Access to information about affected rows, generated auto-increment values, and identifiers of documents added to a collection

On Behalf of the MySQL/ORACLE RE Team

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.

Develop By Example – Document Store: working with collections using Node.js

In the previous blog post we explained how to create schemas and collections. In this one we are going to explain how to work with collections: adding, updating and deleting documents.

The following code demonstrates how to add a single document to an existing collection:

In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) that we want to work with. An object using JSON (newDoc) is created and is passed as a parameter to the coll object’s add method. Calling the execute method the document is added to the collection. Once the execute method has finished, we receive an object (added) that contains information about the document added. To verify if the document was added, we can call the added object’s getAffectedItemsCount method, which will return how many documents were added.

But, what if you want to add multiple documents?

You can do it with almost no changes in your code. The following code adds two documents at the same time:

As you can see, the previous code is almost identical to the first example. We just add an extra line to declare the new document (newDoc2), and we add the new document as a parameter in the coll object’s add method. At the end we call added object’s getAffectedItemsCount method to verify we added the two documents.

Now we know how to add multiples documents to a collection using multiple variables and passing them as parameters, but we can also do the same using an Array object. In the following code example we create an array object to use it to add new documents to a collection.

The previous code is almost identical to the first example; the difference is that we pass an array object as a parameter instead of a JSON object. The rest of the code is the same. This could be useful if you receive an array of objects from the client or if you load the data from a JSON file, you just pass the whole array to upload it to the collection.

Updating a field in a document is also very easy to do. The following code is an example of how to do it:

In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) we want to work. Then we declare the query variable which contains the where clause for our update. 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 to update and the second one the new value to be set. As we did in our previous examples we call the execute method to perform the action requested. When the execute method finishes we receive an object (updated) with information about the update. To know how many documents were updated we call the updated object’s getAffectedItemsCount method.

Now that we know how to add and update documents in a collection, we are going to explain how to remove them. The following code demonstrates it.

The previous code defines the objects that represent the schema (schema) and the collection (coll) where we want to work. Then, we define the query variable again to contain the where clause for our operation, the remove in this case. To remove a document we call the coll object’s remove method followed by the execute method. Once the execute method is completed, we receive an object (deleted) with the information about the operation that has finished. By calling the deleted object’s getAffectedItemsCount method, we know how many documents were removed from the collection.

Now we are going to see how to get documents from a collection. In the following example, we are retrieving the document that match the _id that we want:

The previous code defines the objects that represent the schema (schema) and the collection (coll) we want to work with. Then the query variable is defined and the where clause is set to it. Then we call the coll object’s execute method to perform the query. When the execute method completes, we receive the document that match our search criteria and is send to the console to view it.

But, what if we want all the records from a collection? Well that is simple; we just need to remove the search criteria from the find method. The updated code would look like the following:

Now we know how to search a specific document and get all the documents from a collection. What if we want to get just some number of documents that match query criteria? The next example shows the code to do it:

The previous code looks very similar to the example that returns one document with a specific _id, the difference here is that our query is performing a like and we are adding a call to the coll object’s limit method. Note that the query statement is case sensitive; this means that if we have documents that have  ‘test’ in the field ‘name’, those documents will not be returned because we are searching for ‘Test’ names.

See you in the next blog post.

Develop By Example – Creating schemas and collections using Node.js

In a previous post we explained how to connect to a MySQL server configured as a document store using the new MySQL Connector/Node.js. In this post we are going to explain how to create a schema, create a collection and add documents to the collection.

Creating a new schema is very easy; the following code demonstrates how to do it:

In the previous code example we created a connection and then used the XSession object to create a schema, finally we closed the connection.

The first line of code loads the Connector/Node.js client module, mysqlx. We then call its getSession() method to get the object that we are going to use to create the schema. We then call the createSchema method to create our schema; once the schema is created we call the session object’s close method to close our connection.

But, what will happen if we try to create a schema that already exists? An error will be thrown.

To avoid that situation we can verify if the schema exists before trying to create it.

In the previous code, before trying to create the schema we call the session object’s getSchema method. The object (schema) returned represents the schema we want to create, we then call its existsInDatabase method which will return false if the schema does not exist in the server or true otherwise. The following lines of code attempt to create the schema. The program notifies the user if the schema is created successfully, or if the schema already exists. Then it closes the session.

Now that we know how to create a schema and verify if it already exists in the database, it is time to work with collections. When creating a collection, we need to know which schema object it will belong to. This can be a new schema, or an existing one. If we try to create the collection in a schema where that collection is already present, the program returns an error. The following example demonstrates how to check if a collection already exists before creating it.

In the previous code, the first lines are the same for the code to verify if a schema exists if not it is created. Then we call the schema object’s getCollection method which returns an object that represents the collection (coll) we want to create. As we did for the schema object, we call the coll object’s existsInDatabase method. If the collection does not exist, it is created by calling the schema object’s createCollection method and we receive a confirmation message. If the collection already exists we are notified of the fact. Finally, the session is closed.

To add new documents to a collection we need to use a JSON notation for the document to create. In the following code, we are going to demonstrate how to add a document to a collection, bear in mind that in the code we assume that a schema and a collection already exist in the server.

In the previous code to add a new document, first, we get the objects that represent the schema (schema) and the collection (coll) that we want to work with. Then we created an object using JSON (newDoc). To add the document to the collection we call the coll object’s add method followed by the execute method, once the execute method has finished we receive an object (added) that contains information about the document added. By calling the added object’s getAffectedItemsCount method we know if the document was added.

In the next blog post, we are going to explain in more detail how to work with collections.
See you then.

Develop By Example – Document Store Connections using Node.js

In this post we are going to explain how to connect a Node.js application to a MySQL server using the new MySQL Connector/Node.js; needless to say that we will be using the MySQL server as a document store.

There are two types of session that a connection can provide: XSession and NodeSession.
An XSession encapsulates access to a single MySQL server running the X Plugin or
multiple MySQL Cluster nodes; and the NodeSession serves as an abstraction for a physical connection to exactly one MySQL server running the X Plugin. To enable the XPlugin in the MySQL server using the MySQL Client command line you need to use the root account or an account with INSERT privilege to mysql.plugin table:

  • Invoke the MySQL command-line client: mysql -u user –p
  • Run the following command: INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’;

Click here for more information about how to setting up MySQL as document store.

Creating a connection to a MySQL server as a document store is quite similar to create a connection to a traditional MySQL server; we require the following connection parameters: host, database user, user password, and port.

The following example demonstrates how to connect to a single MySQL Server using XSession:

In the previous code example, we created and closed a connection to a server using an XSession; as you can see the code is very simple and easy to read.

The first line of code loads the Connector/Node.js client module, mysqlx. We then call its getSession method. This method implements a promise. If the connection to the MySQL server is successful the promise is fulfilled by returning an XSession (session) object. We then call the session object’s close method to close the connection.

In the previous code there are two important things to note. The first one is that we do not specify a schema because the XSession works similar to a traditional session: You do not need to specify a schema because, at the time you connect, your working schema might not exist yet. The second one is the port. By default the X DevAPI uses the port 33060; we are assuming that the running server is using the default port for TCP/IP connections. The port can be configured when the server starts and is stored in a server variable.

The following example demonstrates how to connect to a single MySQL Server using NodeSession:

The NodeSession example code is almost the same code used to get an XSession object, the difference is the method that is called to get the session object and the port; the code does exactly the same.

You might need to use a NodeSession in certain scenarios where you require access to SQL features that are not supported by an XSession. In a subsequent post we are going to cover some examples about how to use the NodeSession.

To work with schemas and collections we need to add some extra lines of code. The following code demonstrates how to do it.

In the last code example; from the session object, we call the getSchema method to get an object (schema) that represents the schema in which we want to work. Once we have the schema object we execute the method getCollection to get an object (coll) that represents the collection we want to work with. In this example, we want to retrieve the document with an id value of ‘1’ from the collection. First we call the find method passing the JSON path and value we are searching for. Then we call the execute method to perform the query. The execute method returns a promise which supplies the requested document when the method completes.

See you in the next blog post where we are going to explain more about the operations that can be performed using collections.

Develop By Example – New MySQL Document Store Series

Examples are a great way to learn new things. As many of you may or may not know we’ve added some new things to MySQL Server 5.7.12  and the ecosystem around it, extending it  to allow you to use the MySQL as a Document Store. Meeting the challenge meant expanding Developer Interfaces and Database tools.

  • Addressing information with a both classic and modern data architectures
  • For all types of data – structured, semi, and unstructured
  • Empowering developers – Simpler, Faster, Flexible
  • Leveraging latest NoSQL oriented tools/methods – JavaScript, Node.js, JSON, CRUD, Methods chaining, and more

From the developer side the MySQL Document Store new APIs by introducing a JSON/Document Store oriented called the MySQL X DevAPI. This programming API provides the option for accessing MySQL; and its design unifies JSON document and table access; and it includes SQL support as well. Since the API features a popular fluent interface style, you will be able to use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents.

This new API is provided to developers in our latest MySQL Connectors. As again as learning by example is often the best way to get started with new things we’re also providing an example application we’re calling Movie Review to show you how it’s used in real applications with use cases that likely map to how you’d want to develop.

As there are a range of languages supported by the MySQL Connectors and Drivers – New Connector/Node.js as well as connectors for Java, Python, .NET, C, C++, and PHP – we plan to do Movie Review examples applications across the various languages.
Were calling our new example application – Movie Review – and as you might have guessed it revolves around developing web applications that allows users to review movies via a simple application that demonstrates the usage of the new features available in the X Dev API and connectors.

We will have 2 user types within the application – users and administrators.

The users can:

  • Search for a movie to view its description and any existing reviews
  • Review a movie
  • Update or delete a review they have written

The administrators can:

  • See the movies to view its description and any existing reviews
  • Upload new information to the database manually or from a JSON file
  • View the current data in the collections to edit it or delete it
  • View some simple reports.

With these use cases we hope to quickly and simply take you through the key development concepts quickly using CRUD type programming.

The Document Store Data Model

Since the application will be kept simple, we’re also including simple document store database you can easy load. It includes four collections: Actors, Movies, Reviews, and Users. It comes with the entire example data loaded that you’ll need as well. This will help to teach you some of the basics of document store style modeling.

We’ll provide the steps to install, etc in the example blogs and have you up and running and developing with MySQL Document Store in no time.

See you in the next blog post – where we get into the Movie Review application written with Node.js.