Search Results for

    Show / Hide Table of Contents

    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.

    • Databases:
      • MariaDB™, v5.5, v10.1 onwards (external documentation)
      • MySQL™, v5.6 onwards (external documentation)
      • Both on-premises and cloud, including Azure MariaDB, Azure MySql and other cloud platforms
      • Note: The underlying .NET provider also supports additional databases. These have not been explicitly tested with actionETL, but might still work well: Percona Server, Amazon Aurora, Google Cloud SQL for MySQL and more
    • Features:
      • Includes all optional provider services
        • AdbMySqlConnectorTableInformationService supports regular tables and views, but temporary tables are currently not supported. This means you can use temporary tables in your SQL queries (including dataflow source queries), but you can't use temporary tables with dataflow target insert workers or the APIs that check if a (temporary) table exists or what its schema is.
      • Includes provider-specific type enumeration mappings
      • Includes Bulk Insert with AdbMySqlConnectorBulkInsertTarget<TInput>
        • Note: Requires your connection string has AllowLoadLocalInfile=true, see Using Load Data Local Infile
    • Adb provider: AdbMySqlConnectorProvider
    • .NET provider: "MySqlConnector", a free NuGet package retrieved automatically. See its documentation, as well as the other MySql provider which works in a similar way and is more extensively documented: MySqlClient.
      • Client namespace: MySqlConnector
      • Type enumeration: MySqlConnector.MySqlDbType
    • Connection strings:
      • MySqlConnector documentation
      • Note: To use bulk insert with this driver, your connection string must include AllowLoadLocalInfile=true. See Using Load Data Local Infile for details.
      • Note: To use transactions, e.g. with AdbTransactionActionWorker (see the Adb Keep Open Connection article), add IgnoreCommandTransaction=true to the connection string. Otherwise calls to GetSchema() overloads will throw when the connection has a current transaction.
    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. The listed .NET types below are fully supported by the dataflow.

    Provider-Independent .NET Types

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

    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

    • Quoting character:
      • AdbMySqlConnectorProvider.Get(): Default backtick ` , e.g. `My Table`
      • AdbMySqlConnectorProvider.Get(true): ANSI double quote ", e.g. "My Table"
    • Max length: 64 in most cases
    • MariaDB external documentation: Identifiers Names
    • MySQL external documentation: 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.

    • NumberOfColumnIdentifierParts and all NumberOfIdentifierParts below are equal to 0.
    • NumberOfColumnRestrictions and all NumberOfRestrictions below are equal to 0, i.e. the underlying MySqlConnector driver does not support the GetSchema(String, String[]) overload. Instead, use the GetSchema(String) overload and filter the return values yourself.
    === 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

    • Worker System
      • Configuration
    • SQL Database Access
      • Adb Supported Databases
        • Adb MySqlClient Provider
        • ...
      • Adb Information
      • AdbProvider
    In This Article
    Back to top Copyright © 2021 Envobi Ltd