LLBLGen Pro - concepts - database drivers.

Concepts - Database drivers

Preface

The LLBLGen Pro system uses specific database drivers per database vendor, using the 'provider model'. The drivers are used by the designer. The generated code doesn't use these drivers however, instead it uses Dynamic Query Engines (DQE's). It ships with the SqlServer 7/2000/2005/2008 driver, Oracle 8i/9i driver (ODP.NET), Oracle 10g/11g driver (ODP.NET), Oracle 8i/9i/10g/11g driver (Microsoft Oracle provider), MySql 4.x/5.x driver, IBM DB2 7.x/8.x /9.x driver, Firebird 1.x/2.x driver, PostgreSql 7.4+/8.x driver, Sybase ASE driver, Sybase ASA driver and Microsoft Access 2000/XP/2003/2007 driver.

A database driver is used to connect to a database server and to retrieve all available schema information for the credentials and connection information specified. This information is then stored in LLBLGen Pro's own format which is used to define all other elements in a project which are based on schema information or which link to schema information. This way LLBLGen Pro can be used with every database system that has an LLBLGen Pro database driver.

Database driver's tasks

The database drivers have a well defined set of tasks:

Providing connectivity with the database server for the designer;
Providing ways to construct connection strings
Converting database types to different .NET types
Retrieval of catalog names (if applicable)
Retrieval of schema definitions inside a catalog / database / owner schema. This will return all tables, views, stored procedure metadata and their details like table fields and parameters, foreign key constraints and unique constraints
Refresh of schema information.

The schema meta data produced by the database driver is serialized into the project, which makes a project connection independent: the complete schema set and all information a driver can provide is saved in the project. When a user wants to work with a project, there doesn't have to be a connection with the database. Only when the schema set has to be refreshed, the user has to re-connect to the database.

Supported features per database driver

Each database driver supports a variety of features and these are listed below.

SqlServer

SqlServer 7: All features of SqlServer 7, including user defined types (type synonyms).
SqlServer 2000: Scope_identity() and all SqlServer 2000 specific types.
SqlServer 2005: Xml datatype, Varchar(MAX) and Varbinary(MAX), all database constructs, including User Defined Types (UDTs) written in .NET, except XQuery queries.
SqlServer 2008: All SqlServer 2005 features as well as the 4 new types: Date, DateTime2, DateTimeOffset and Time.
Multiple catalogs per project.

Oracle (ODP.NET)

Oracle 8i (8.1.7) or higher.
Tables, views, sequences and procedures.
Synonyms for tables, views and sequences.
REF CURSOR output parameters in procedures.
All native Oracle types, including *LOB and synonyms for types like the type INT.
XMLType support on 9i and higher.
All joins are non-ansi by default (10g/11g use ansi joins by default), to support 8i. Configurable to use ansi-joins through config file settings
Multiple schemas per project
No support for user defined types.

Required for Oracle 8i/9i: ODP.NET 9.2.0.x (free download from Oracle), Oracle client 9.2 (requirement for ODP.NET), Oracle 8i (8.1.7) or higher.
Required for Oracle 10g/11g: ODP.NET 10.x (free download from Oracle), Oracle client 10.2 (requirement for ODP.NET) or higher, Oracle 10g.

Oracle (Microsoft Oracle provider)

For supported features, see Oracle (ODP.NET). The Microsoft Oracle Provider is available and supported on .NET 1.1 or higher. Microsoft's Oracle provider requires an Oracle client present on the system, please consult the Microsoft Oracle provider documentation in the .NET reference manual (System.Data.OracleClient).

Restrictions in the Microsoft Oracle provider:

XMLType is not supported by LLBLGen Pro and the MS Oracle driver, as that type isn't supported by the Microsoft Oracle provider. If you need to use this type, use the ODP.NET version of the Oracle driver.
All NUMBER(x, y) types are seen as System.Decimal. This can be a huge disadvantage. In that case, consider using an ODP.NET based driver

Firebird/Interbase

All features of Firebird 1.5 and higher, except array types.
Dialect 3 only.
.NET 1.1 or higher

Required for Firebird: the Firebird.NET provider by Carlos Alvarez, available at the sourceforge download site for Firebird. The driver is built against the latest FireBird.NET provider v2.0 build of the Firebird.NET provider. The .NET 1.1 DQE you'll use in your .NET 1.1 code is compiled against the Firebird.NET provider v1.7.1 for .NET 1.1. The .NET 2.0+ DQE is built against the Firebird.NET provider v2.0.1.0 for .NET 2.0.

PostgreSql

All features of PostgreSql 7.4 or higher, except array types.
All datatypes supported by Npgsql are supported.

Required for PostgreSql: the Npgsql provider, available at the PostgreSql website. LLBLGen Pro comes with the Npgsql .NET provider dll for .NET 2.0 as the Npgsql provider doesn't install itself in the GAC. To use the generated code in your own project, be sure to either download the latest Npgsql .NET provider for .NET 1.x or .NET 2.0, depending on the target platform of your code.

Microsoft Access

All features of MS Access 2000, except parameterized stored queries, so no stored procedure calls
Database passwords, security files are supported.
Limiting the number of objects to return in a query requires the primary key field(s) to be added to a sort clause if a sort clause is specified.
LLBLGen Pro transaction savepoints aren't supported.

Required for Microsoft Access: OleDB driver for Jet v4.0, which is shipped with MDAC 2.5 or higher (a requirement for .NET so these are installed) and a .mdb file in the MS Access 2000 format or higher.

IBM DB2 UDB

IBM DB2 UDB v7.x/8.x/9x.
Tables, views, sequences, identity columns and procedures.
All native IBM DB2 UDB types, including *LOB.
No support for user defined types.
No support for table / view aliasses. A table / view alias is a public alias for an existing schema table and defined as such in the schema. Aliasses for tables in queries are supported.
No support for iSeries DB2 installations.

Required for DB2: IBM DB2 .NET provider, shipped with the latest ClientAccess version, also available through the DB2 personal edition installation, or through the IBM website for DB2 licensees.

MySql

MySql v4.x (4.1 or higher with InnoDB preferred) or v5.x
Tables, identity columns, primary keys, unique constraints, subqueries
All native MySql types except SET and ENUM (will be converted to VarChar).
v4.x: No support for database defined foreign keys. Foreign key constraint meta-data defined in the database isn't read by the MySql driver from a v4.x MySql database.
No support for stored procedures.

Required for MySql: CoreLab's MySqlDirect.NET provider v4.x, or DevArt's MySqlDirect.NET provider v5.x. LLBLGen Pro supports MySqlDirect.NET v3.55.x, see note below.

Note:

.NET 1.x: Support for MySql using the CoreLab MySqlDirect provider v3.55.x has its own driver dll, please copy the driver dll from the LLBLGenPro Installation Folder\Drivers\MySql\v355 folder into the LLBLGenPro Installation Folder\Drivers\MySql\ folder overwriting the v4 using driver and restart the LLBLGen Pro designer.

Note:

.NET 2.0+: Starting with v2.6, driver build 06262009, the MySql Driver, templates and DQE use the ADO.NET DbProviderFactory to communicate with the ADO.NET provider for MySql from CoreLab or DevArt. This has the consequence that you don't need to reference the MySql Direct provider from CoreLab or DevArt, however you have to install the provider on the target system your application will run on. This is documented in the MySql Direct provider documentation from CoreLab or DevArt, under the section 'Licensing'.

This change was necessary to provide support for the new CoreLab provider, which is now called DevArt's MySqlDirect, and also keep supporting customers who have purchased the CoreLab provider.

Sybase Adaptive Server Enterprise (ASE)

All features of Sybase Adaptive Server Enterprise v12.x+ are supported except Java based types and proxy tables
Single catalog per project
Floats with precision < 16 are mapped on System.Single, floats with precision >= 16 are mapped on System.Double
Output parameters for procedures aren’t recognized, as Sybase ASE doesn’t store this information in the meta-data, so output parameters are always seen as input parameters.
Grouped (overloaded) procedures are supported.
The Sybase ASE’s client class AseParameters collection isn’t CLS compliant, which can cause compile problems where ClsCompliancy is forced through the assemblyinfo file.
numeric identity (Identity columns) are always set to DBType ‘int’.

Required for Sybase ASE: The latest ASE ADO.NET provider from Sybase, v1.1.5. This provider is compiled against .NET 1.1 and therefore there's no .NET 1.0 support.

Sybase iAnywhere (ASA)

All features of v8.x or higher are supported except Java based types and proxy tables
Single catalog per project
.NET 2.0 or higher is supported.
iAnywhere 8 or higher is supported.
Owners ‘SYS’, ‘dbo’, ‘SA_DEBUG’, ‘rs_systabgroup’ are filtered out.
(Long)varbit bitarrays are mapped to strings, as the iAnywhere provider does that too.
Users should specify the database service name for the service to connect to, not the server name (or IP address) the database service runs on.

Required for Sybase ASA: The latest iAnywhere ADO.NET provider from Sybase for .NET 2.0, v10.0.1.34152 or higher.