Adb MySqlConnector Provider

This provider supports several MySql compatible databases.

Important

The underlying MySqlConnector .NET provider is focused on high performance and supports both batch and bulk insert, but lacks some features. The other MySql provider (MySqlClient) does not support bulk insert, but has an otherwise slightly wider feature set.

You can use either or both providers in a single worker system.

Note

For MariaDB and MySQL servers, "database" and "schema" have identical meanings. actionETL puts the database/schema identifier part in "*Schema*" properties, while "*Catalog*" properties are unused. See e.g. CreateTableIdentifier(String).

Data Type Mappings

This section lists which database types map to or from which .NET types when querying a MySqlConnector server and when working with its database parameters.

Unless otherwise noted, all the below .NET types can be round-tripped to the database, i.e. written out with one of the database target workers, and read back with AdbDataReaderSource<TOutput>.

Provider-Independent .NET Types

.NET types below with Shallow or SingleShallowThenDeep copy policy support multi-copy, the others (with SingleShallow) do not.

.NET Type Display Name DbType (or MySqlDbType) enum SQL Type Example Copy Policy
Any enum or nullable enum Byte, Int16, Int32, Int64 tinyint unsigned, tinyint, smallint, mediumint, int, bigint Shallow
System.Boolean, System.Boolean? Boolean bool, boolean Shallow
System.Byte, System.Byte? Byte tinyint unsigned Shallow
System.Byte[] Binary binary(7), blob, longblob, mediumblob, tinyblob, varbinary(10) SingleShallowThenDeep
System.DateTime, System.DateTime? DateTime date, datetime, timestamp Shallow
System.Decimal, System.Decimal? Decimal decimal(65,0), decimal(65,0) unsigned (aliases: dec, numeric, fixed) Shallow
System.Double, System.Double? Double double, double precision, double precision unsigned Shallow
System.Guid, System.Guid? Guid char(36). Set GuidFormat in connection string to use char(32) or binary(16). Shallow
System.Int16, System.Int16? Int16 smallint Shallow
System.Int32, System.Int32? Int32 int, integer, mediumint, year Shallow
System.Int64, System.Int64? Int64 bigint Shallow
System.Memory<System.Byte>, System.Memory<System.Byte>? Binary binary(7), blob, longblob, mediumblob, tinyblob, varbinary(10) SingleShallowThenDeep
System.SByte, System.SByte? SByte tinyint Shallow
System.Single, System.Single? Single float, float unsigned Shallow
System.String String char(32), text, varchar(50) (also with charset utf8 and charset utf8mb4 suffix). enum('Input', 'Output'), set('Red', 'Green'). Shallow
System.TimeSpan, System.TimeSpan? MySqlDbType.Time time Shallow
System.UInt16, System.UInt16? UInt16 smallint unsigned Shallow
System.UInt32, System.UInt32? UInt32 int unsigned, integer unsigned, mediumint unsigned Shallow
System.UInt64, System.UInt64? UInt64 bigint unsigned, bit, bit(64) Shallow

Provider-Specific .NET Types

This provider does not map any provider-specific .NET types. It does however use MySqlDbType.Time when mapping System.TimeSpan above.

Identifiers

Identifier Casing

Database identifier case sensitivity depends on database settings, database storage engine, and which underlying platform (Windows, Linux etc.) is used. See MariaDB Identifier Case-sensitivity or MySQL Identifier Case Sensitivity for details.

Note that dataflow column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match. See Dataflow Column Mapping for details.

Parameters

Use named parameters of the format @MyParameterName. You can use GetPlaceholder(String) to get the placeholder in a portable manner.

Stored Procedures

Here we execute a stored procedure with parameters. Even building an executable program (or utility method) for executing this single query can be very worthwhile since actionETL adds configuration, error handling, and logging.

We create the command builder outside the worker system to make it easy to return a parameter value to the caller. More commonly, commands and connections are created inside the worker system, as and when they are needed.

Note

This example uses the "MySqlClient" provider; you can equally use the "MySqlConnector" provider.

using actionETL;
using actionETL.Adb;
using actionETL.Adb.MySqlClientExternal;
using System.Data;

public static partial class ExecuteStoredProcedureMySql
{
    public static DateTime GetMaxDateTime()
    {
        // Get a command builder using "actionetl.aconfig.json" connection details.
        // StoredProcedure(string) sets both CommandText and CommandType properties.
        var cb = AdbMySqlClientProvider.Get()
            .CreateCommandBuilderFromDefaultAConfig("MySqlClientTester")
            .StoredProcedure("GetMaxDateTime");

        // Many methods return an instance and allow method chaining.
        // Whenever possible, set the database type explicitly.
        cb.Parameters.Add("countryId").SetDbValue(42, DbType.Int32);

        // Database type not set, provider has to guess the type (which is less good)
        cb.Parameters.Add("category").SetValue("Batteries");

        // Save the parameter reference, since we retrieve the returned value later
        var maxDateTime = cb.Parameters.Add("maxDateTime", ParameterDirection.Output)
            .SetDbType(DbType.DateTime);

        new WorkerSystem()
            // Curly braces and semi-colon not needed in 1-line lambda
            .Root(ws => _ = new AdbExecuteNonQueryWorker(ws, "Call GetMaxDateTime", cb))
            .Start()
            .ThrowOnFailure();

        // Value is of type object, and must be cast
        return (DateTime)maxDateTime.DbValue;
    }
}

/* The example assumes the following stored procedure already exists:
    CREATE PROCEDURE GetMaxDateTime (
        countryId int, 
        category varchar(50) charset utf8mb4,
        OUT maxDateTime DateTime
        )
    BEGIN
        -- Use input parameters...

        SET maxDateTime := '2017-03-13';
    END
 */

Adb Information

The Adb Information example program dumps the below information with this provider querying a MariaDB server. See that example for more information. A MySql server will typically have identical values apart from Provider, ServerVersion, and DataSourceProductVersion.

Important

The MySqlConnector underlying driver hasn't implemented the following APIs. They are seldom used, but if you need them, use the Adb MySqlClient Provider provider instead.

=== AdbConnectionBuilder ===
Provider=MySqlConnector:MySQL and MariaDB:


=== AdbConnection ===
DataSourceName=localhost
DatabaseName=actionetl_tester
NumberOfColumnIdentifierParts=0
NumberOfColumnRestrictions=0
ServerVersion=5.5.5-10.3.9-MariaDB


=== AdbConnection.Information ===
CompositeIdentifierSeparator=.
CompositeIdentifierSeparatorPattern=\.
DataSourceProductName=MySQL
DataSourceProductVersion=5.5.5-10.3.9-MariaDB
GetPlaceholder("MyName")=@MyName
GroupByBehavior=Unrelated
IdentifierCase=Insensitive
IdentifierPattern=(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\"[^\"\0]|\"\"+\"$)
IdentifierQuotePrefix=`
IdentifierQuotePrefixes=`"
IdentifierQuoteSuffix=`
IdentifierQuoteSuffixes=`"
OrderByColumnsInSelect=False
ParameterMarkerFormat={0}
ParameterMarkerPattern=(@[A-Za-z0-9_$#]*)
ParameterNameMaxLength=128
ParameterNamePattern=^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)
QuotedIdentifierCase=Sensitive
QuotedIdentifierPattern=(([^\`]|\`\`)*)
QuoteIdentifierPart("My Table")=`My Table`
StatementSeparatorPattern=;
StringLiteralPattern='(([^']|'')*)'
SupportedJoinOperators=Inner, LeftOuter, RightOuter, FullOuter
UsesPositionalParameters=False


=== AdbConnection.TableInformation.CreateTableIdentifier(`My Database`.MyTable) ===
OriginalCompositeName=`My Database`.MyTable
OriginalTableName=MyTable
OriginalSchemaName=`My Database`
OriginalCatalogName=
QuotedCompositeName=`My Database`.`MyTable`
QuotedTableName=`MyTable`
QuotedSchemaName=`My Database`
QuotedCatalogName=
UnquotedTableName=MyTable
UnquotedSchemaName=My Database
UnquotedCatalogName=


=== AdbConnection.SchemaInformationCollection ===
Note: The metadata schemas can be retrieved with AdbConnection.GetSchema().
The returned information is provided from the .NET provider as-is. Some collections
can be inaccurate, and different values can also be needed for different purposes,
which is why database specific actionETL providers can be created that replace
individual values with the desired ones. See the vendor provider documentation for
details on the returned information.

CollectionName=DataSourceInformation
IsCommon=True
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=MetaDataCollections
IsCommon=True
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=CharacterSets
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Collations
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=CollationCharacterSetApplicability
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Columns
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Databases
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=DataTypes
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Engines
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=KeyColumnUsage
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=KeyWords
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Parameters
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Partitions
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Plugins
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Procedures
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=ProcessList
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Profiling
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=ReferentialConstraints
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=ReservedWords
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=ResourceGroups
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=SchemaPrivileges
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Tables
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=TableConstraints
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=TablePrivileges
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=TableSpaces
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Triggers
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=UserPrivileges
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Views
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

See Also