Search Results for

    Show / Hide Table of Contents

    Adb MySqlClient Provider

    You can use these databases (among others) in your ETL:

    • 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
    • Features:
      • Includes all optional provider services
        • AdbMySqlClientTableInformationService supports regular tables and views. Temporary tables are currently not supported.
      • Includes provider-specific type enumeration mappings
    • Adb provider: AdbMySqlClientProvider
    • .NET provider: "MySql.Data.MySqlClient", a free MySql.Data NuGet package retrieved automatically (external documentation)
      • Type enumeration: MySql.Data.MySqlClient.MySqlDbType
      • Type namespace: MySql.Data.Types
      • Client namespace: MySql.Data.MySqlClient
    • Connection strings:
      • connectionstrings.com
      • MySQL documentation
    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 MySqlClient 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.Int16, System.Int16? Int16 smallint
    System.Int32, System.Int32? Int32 int, integer, mediumint, year
    System.Int64, System.Int64? Int64 bigint
    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:
      • AdbMySqlClientProvider.Get(): Default backtick ` , e.g. `My Table`
      • AdbMySqlClientProvider.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.

    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 have identical values apart from Provider, ServerVersion, and DataSourceProductVersion.

    === AdbConnectionBuilder ===
    Provider=MySql.Data.MySqlClient:MySQL and MariaDB:
    
    
    === AdbConnection ===
    DataSourceName=localhost
    DatabaseName=actionetl_tester
    NumberOfColumnIdentifierParts=4
    NumberOfColumnRestrictions=4
    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=MetaDataCollections
    IsCommon=True
    NumberOfRestrictions=0
    NumberOfIdentifierParts=0
    
    CollectionName=DataSourceInformation
    IsCommon=True
    NumberOfRestrictions=0
    NumberOfIdentifierParts=0
    
    CollectionName=DataTypes
    IsCommon=False
    NumberOfRestrictions=0
    NumberOfIdentifierParts=0
    
    CollectionName=Restrictions
    IsCommon=False
    NumberOfRestrictions=0
    NumberOfIdentifierParts=0
    
    CollectionName=ReservedWords
    IsCommon=False
    NumberOfRestrictions=0
    NumberOfIdentifierParts=0
    
    CollectionName=Databases
    IsCommon=False
    NumberOfRestrictions=1
    NumberOfIdentifierParts=1
    
    CollectionName=Tables
    IsCommon=False
    NumberOfRestrictions=4
    NumberOfIdentifierParts=2
    
    CollectionName=Columns
    IsCommon=False
    NumberOfRestrictions=4
    NumberOfIdentifierParts=4
    
    CollectionName=Users
    IsCommon=False
    NumberOfRestrictions=1
    NumberOfIdentifierParts=1
    
    CollectionName=Foreign Keys
    IsCommon=False
    NumberOfRestrictions=4
    NumberOfIdentifierParts=3
    
    CollectionName=IndexColumns
    IsCommon=False
    NumberOfRestrictions=5
    NumberOfIdentifierParts=4
    
    CollectionName=Indexes
    IsCommon=False
    NumberOfRestrictions=4
    NumberOfIdentifierParts=3
    
    CollectionName=Foreign Key Columns
    IsCommon=False
    NumberOfRestrictions=4
    NumberOfIdentifierParts=3
    
    CollectionName=UDF
    IsCommon=False
    NumberOfRestrictions=1
    NumberOfIdentifierParts=1
    
    CollectionName=Views
    IsCommon=False
    NumberOfRestrictions=2
    NumberOfIdentifierParts=3
    
    CollectionName=ViewColumns
    IsCommon=False
    NumberOfRestrictions=3
    NumberOfIdentifierParts=4
    
    CollectionName=Procedure Parameters
    IsCommon=False
    NumberOfRestrictions=5
    NumberOfIdentifierParts=1
    
    CollectionName=Procedures
    IsCommon=False
    NumberOfRestrictions=4
    NumberOfIdentifierParts=3
    
    CollectionName=Triggers
    IsCommon=False
    NumberOfRestrictions=2
    NumberOfIdentifierParts=4
    

    See Also

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