8.1.1 Processing Warnings

Similar to the execution of single statements committing or rolling back a transaction can also trigger warnings. To be able to process these warnings the replied result object of Session.commit(); or Session.rollback(); needs to be checked.

This is shown in the following example. The example assumes that the test schema exists and that the collection my_collection does not exist.

MySQL Shell JavaScript Code

var mysqlx = require('mysqlx');

// Connect to server
var mySession = mysqlx.getSession( {
        host: 'localhost', port: 33060,
        user: 'user', password: 'password' } );

// Get the Schema test
var myDb = mySession.getSchema('test');

// Create a new collection
var myColl = myDb.createCollection('my_collection');

// Start a transaction
mySession.startTransaction();
try
{
    myColl.add({'name': 'Rohit', 'age': 18, 'height': 1.76}).execute();
    myColl.add({'name': 'Misaki', 'age': 24, 'height': 1.65}).execute();
    myColl.add({'name': 'Leon', 'age': 39, 'height': 1.9}).execute();

    // Commit the transaction if everything went well
    var reply = mySession.commit();

    // handle warnings
    if (reply.warningCount){
      var warnings = reply.getWarnings();
      for (index in warnings){
        var warning = warnings[index];
        print ('Type ['+ warning.level + '] (Code ' + warning.code + '): ' + warning.message + '\n');
      }
    }

    print ('Data inserted successfully.');
}
catch(err)
{
    // Rollback the transaction in case of an error
    reply = mySession.rollback();

    // handle warnings
    if (reply.warningCount){
      var warnings = reply.getWarnings();
      for (index in warnings){
        var warning = warnings[index];
        print ('Type ['+ warning.level + '] (Code ' + warning.code + '): ' + warning.message + '\n');
      }
    }

    // Printing the error message
    print ('Data could not be inserted: ' + err.message);
}

MySQL Shell Python Code

from mysqlsh import mysqlx

# Connect to server
mySession = mysqlx.get_session( {
        'host': 'localhost', 'port': 33060,
        'user': 'user', 'password': 'password' } )

# Get the Schema test
myDb = mySession.get_schema('test')

# Create a new collection
myColl = myDb.create_collection('my_collection')

# Start a transaction
mySession.start_transaction()
try:
    myColl.add({'name': 'Rohit', 'age': 18, 'height': 1.76}).execute()
    myColl.add({'name': 'Misaki', 'age': 24, 'height': 1.65}).execute()
    myColl.add({'name': 'Leon', 'age': 39, 'height': 1.9}).execute()

    # Commit the transaction if everything went well
    reply = mySession.commit()

    # handle warnings
    if reply.warning_count:
      for warning in result.get_warnings():
        print('Type [%s] (Code %s): %s\n' % (warning.level, warning.code, warning.message))

    print('Data inserted successfully.')
except Exception as err:
    # Rollback the transaction in case of an error
    reply = mySession.rollback()

    # handle warnings
    if reply.warning_count:
      for warning in result.get_warnings():
        print('Type [%s] (Code %s): %s\n' % (warning.level, warning.code, warning.message))

    # Printing the error message
    print('Data could not be inserted: %s' % str(err))

C# Code

// Connect to server
var session = MySQLX.GetSession("server=localhost;port=33060;user=user;password=password;");

// Get the Schema test
var db = session.GetSchema("test");

// Create a new collection
var myColl = db.CreateCollection("my_collection");

// Start a transaction
session.StartTransaction();
int warningCount = 0;
try
{
	var result = myColl.Add(new { name = "Rohit", age = 18, height = 1.76}).Execute();
	warningCount += result.Warnings.Count;
	result = myColl.Add(new { name = "Misaki", age = 24, height = 1.65}).Execute();
	warningCount += result.Warnings.Count;
	result = myColl.Add(new { name = "Leon", age = 39, height = 1.9}).Execute();
	warningCount += result.Warnings.Count;

	// Commit the transaction if everything went well
	session.Commit();
	if(warningCount > 0)
	{
	  // handle warnings
	}

	Console.WriteLine("Data inserted successfully.");
}
catch (Exception err)
{
	// Rollback the transaction in case of an error
	session.Rollback();
	if(warningCount > 0)
	{
	  // handle warnings
	}

	// Printing the error message
	Console.WriteLine("Data could not be inserted: " + err.Message);
}

Python Code

import mysqlx

# Connect to server
my_session = mysqlx.get_session({
    'host': 'localhost', 'port': 33060,
    'user': 'user', 'password': 'password'
})

# Get the Schema test
my_schema = my_session.get_schema('test')

# Create a new collection
my_coll = my_schema.create_collection('my_collection')

# Start a transaction
my_session.start_transaction()
try:
    my_coll.add({'name': 'Rohit', 'age': 18, 'height': 1.76}).execute()
    my_coll.add({'name': 'Misaki', 'age': 24, 'height': 1.65}).execute()
    my_coll.add({'name': 'Leon', 'age': 39, 'height': 1.9}).execute()

    # Commit the transaction if everything went well
    result = my_session.commit()

    # handle warnings
    if result.get_warnings_count() > 0:
        for warning in result.get_warnings():
            print('Type [{0}] (Code {1}): {2}'.format(warning['level'], warning['code'], warning['msg']))

    print('Data inserted successfully.')
except Exception as err:
    # Rollback the transaction in case of an error
    result = my_session.rollback()

    # handle warnings
    if result.get_warnings_count() > 0:
        for warning in result.get_warnings():
            print('Type [{0}] (Code {1}): {2}'.format(warning['level'], warning['code'], warning['msg']))

    # Printing the error message
    print('Data could not be inserted: {0}'.format(err))

Java Code

// c.f. "standard transaction handling"

C++ Code

/*
  Connector/C++ does not yet provide access to transaction warnings
  -- Session methods commit() and rollback() do not return a result object.
*/

By default all warnings are sent from the server to the client. If an operation is known to generate many warnings and the warnings are of no value to the application then sending the warnings can be suppressed. This helps to save bandwith. session.setFetchWarnings() controls whether warnings are discarded at the server or are sent to the client. session.getFetchWarnings() is used to learn the currently active setting.

MySQL Shell JavaScript Code

var mysqlx = require('mysqlx');

function process_warnings(result){
  if (result.getWarningCount()){
    var warnings = result.getWarnings();
    for (index in warnings){
      var warning = warnings[index];
      print ('Type ['+ warning.level + '] (Code ' + warning.code + '): ' + warning.message + '\n');
    }
  }
  else{
    print ("No warnings were returned.\n");
  }
}

// Connect to server
var mySession = mysqlx.getSession( {
  host: 'localhost', port: 33060,
  user: 'user', password: 'password' } );

// Disables warning generation
mySession.setFetchWarnings(false);
var result = mySession.sql('drop schema if exists unexisting').execute();
process_warnings(result);

// Enables warning generation
mySession.setFetchWarnings(true);
var result = mySession.sql('drop schema if exists unexisting').execute();
process_warnings(result);

MySQL Shell Python Code

from mysqlsh import mysqlx

def process_warnings(result):
  if result.get_warnings_count():
    for warning in result.get_warnings():
      print('Type [%s] (Code %s): %s\n' % (warning.level, warning.code, warning.message))
  else:
    print("No warnings were returned.\n")


# Connect to server
mySession = mysqlx.get_session( {
  'host': 'localhost', 'port': 33060,
  'user': 'user', 'password': 'password' } );

# Disables warning generation
mySession.set_fetch_warnings(False)
result = mySession.sql('drop schema if exists unexisting').execute()
process_warnings(result)

# Enables warning generation
mySession.set_fetch_warnings(True)
result = mySession.sql('drop schema if exists unexisting').execute()
process_warnings(result)

Java Code

// Connect to server
Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=user&password=password");

Schema db = mySession.getSchema("test");

// Create a new collection
Collection myColl = db.createCollection("my_collection");

// Start a transaction
mySession.startTransaction();
try {
    Result res = myColl.add("{\"name\":\"Rohit\", \"age\":18}", "{\"name\":\"Misaki\", \"age\":24}", "{\"name\":\"Leon\", \"age\":39}").execute();

    System.out.println(res.getWarningsCount());

    Iterator<Warning> warnings = res.getWarnings();
    while (warnings.hasNext()) {
        Warning warn = warnings.next();
        System.out.println(warn.getCode() + ", " + warn.getLevel() + ", " + warn.getMessage());
    }

    mySession.commit();
    System.out.println("Data inserted successfully.");
} catch (Exception err) {
    // Rollback the transaction in case of an error
    mySession.rollback();

    // Printing the error message
    System.out.println("Data could not be inserted: " + err.getMessage());
}