Search Results for

    Show / Hide Table of Contents

    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
    • 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:
      • connectionstrings.com
      • Microsoft documentation

    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
    

    See Also

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