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
- MariaDB™, v5.5, v10.1 onwards (external documentation)
- Features:
- Includes all optional provider services
- AdbMySqlClientTableInformationService supports regular tables and views. Temporary tables are currently not supported.
- Includes provider-specific type enumeration mappings
- Includes all optional provider services
- 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:
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"
- AdbMySqlClientProvider.Get(): Default backtick
- 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