Adb ODBC Provider
You can use these databases (among others) in your ETL:
- Databases: Most ODBC data sources
- Both relational and NoSQL databases and querying systems
- Both on-premises and cloud
- Features:
- Optional provider services:
- Includes AdbInsertTarget<TInputError> on SQL-92 syntax data sources
- Includes TableInformation and related workers on many data sources
- Excludes TableCommand / AdbTableNonQueryWorker
- Optional provider services:
- Adb provider overloads:
- GetUniversal(Boolean) with TableInformation and related workers
- GetUniversalBasic(Boolean) without TableInformation and related workers
- .NET provider: "System.Data.Odbc", included with operating system
(external documentation)
- Type enumeration: OdbcType
- Client namespace: System.Data.Odbc
- ODBC driver: Install the ODBC driver for your data source
- Connection strings:
Data Type Mappings
This section lists which database types map to or from which .NET types when querying an ODBC server and working with its database parameters. The listed .NET types below are fully supported by the dataflow.
Note
Mappings tagged with AdbColumnSchema below depend on whether or not an
AdbColumnSchema is supplied to
SetMappedType(AdbParameter, Type, AdbColumnSchema, ParameterDirection).
Of the out of box workers, this is only supplied by AdbInsertTarget<TInputError>.
Provider-Independent .NET Types
| .NET Type Display Name | DbType (or OdbcType) enum |
SQL Type (uses SQL Server as example) |
|---|---|---|
System.Boolean, System.Boolean? |
Boolean | bit |
System.Byte, System.Byte? |
Byte | tinyint |
System.Byte[] |
Binary | binary(7), image, varbinary(10), varbinary(max) |
System.DateTime, System.DateTime? |
OdbcType.DateTime | datetime. Without AdbColumnSchema also: date, datetime2, smalldatetime. |
System.DateTime, System.DateTime? |
OdbcType.Date, OdbcType.DateTime, OdbcType.SmallDateTime | With AAdbColumnSchema: date, datetime2, smalldatetime |
System.Decimal, System.Decimal? |
Decimal | decimal(38,0), money, numeric(10,2), smallmoney |
System.Double, System.Double? |
Double | float |
System.Guid, System.Guid? |
Guid | uniqueidentifier |
System.Int16, System.Int16? |
Int16 | smallint |
System.Int32, System.Int32? |
Int32 | int, integer |
System.Int64, System.Int64? |
Int64 | bigint |
System.Single, System.Single? |
Single | real |
| System.String | String | char(32), nchar(32), ntext, nvarchar(max), text, varchar(max) |
Provider-Specific .NET Types
This provider does not map any provider-specific .NET types. It does however use OdbcType
when mapping System.DateTime above.
Parameters
Use positional parameters of the format ?. 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.
Note
The values below will be different if querying a different type of server.
=== AdbConnectionBuilder ===
Provider=System.Data.Odbc:Universal:TableInformation=false
=== AdbConnection ===
DataSourceName=MYHOST\LOCALDB#6A3842B2
DatabaseName=actionetl_tester
NumberOfColumnIdentifierParts=4
NumberOfColumnRestrictions=4
ServerVersion=13.00.4001
=== AdbConnection.Information ===
CompositeIdentifierSeparator=.
CompositeIdentifierSeparatorPattern=\.
DataSourceProductName=Microsoft SQL Server
DataSourceProductVersion=13.00.4001
GetPlaceholder("MyName")=?
GroupByBehavior=MustContainAll
IdentifierCase=Insensitive
IdentifierPattern=
IdentifierQuotePrefix="
IdentifierQuotePrefixes="
IdentifierQuoteSuffix="
IdentifierQuoteSuffixes="
OrderByColumnsInSelect=False
ParameterMarkerFormat=?
ParameterMarkerPattern=\?
ParameterNameMaxLength=0
ParameterNamePattern=
QuotedIdentifierCase=Insensitive
QuotedIdentifierPattern="(([^"]|"")*)"
QuoteIdentifierPart("My Table")="My Table"
StatementSeparatorPattern=
StringLiteralPattern=
SupportedJoinOperators=Inner, LeftOuter, RightOuter, FullOuter
UsesPositionalParameters=True
=== AdbConnection.TableInformation.CreateTableIdentifier([My Database].[My Schema].MyTable) ===
OriginalCompositeName=[My Database].[My Schema].MyTable
OriginalTableName=MyTable
OriginalSchemaName=[My Schema]
OriginalCatalogName=[My Database]
QuotedCompositeName="[My Database]"."[My Schema]"."MyTable"
QuotedTableName="MyTable"
QuotedSchemaName="[My Schema]"
QuotedCatalogName="[My Database]"
UnquotedTableName=MyTable
UnquotedSchemaName=[My Schema]
UnquotedCatalogName=[My Database]
=== 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=Columns
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=Indexes
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=Procedures
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3
CollectionName=ProcedureColumns
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=ProcedureParameters
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=Tables
IsCommon=False
NumberOfRestrictions=3
NumberOfIdentifierParts=3
CollectionName=Views
IsCommon=False
NumberOfRestrictions=3
NumberOfIdentifierParts=3