dart_odbc #

A Dart package for interacting with ODBC databases. It allows you to connect to ODBC data sources and execute SQL queries directly from your Dart applications.

This package is inspired by the obsolete odbc package by Juan Mellado.

Usage #

• Instanciate the ODBC class by providing the path to the odbc driver on the host machine

  final odbc = DartOdbc(
    dsn: '<your_dsn>',
    pathToDriver: '<path_to_odbc_driver>',
  );

DSN (optional) #

The DSN (Data Source Name) is the name you gave when setting up the driver manager. For more information, visit this page from the MySQL Documentation If not provided, the connection can only be made via connection string.

• Connect to the database by providing the DSN (Data Source Name) configured in the ODBC Driver Manager

  await odbc.connect(
    username: 'db_username',
    password: 'db_password',
  );

• Or connect to the database via connection string

  await odbc.connectWithConnectionString(
    "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\Users\Computer\AppData\Local\Temp\test.xlsx;"
  );

Executing SQL queries #

  final result = await odbc.execute("SELECT 10");

Executing prepared statements #

• Prepared statements can be used to prevent SQL Injection
• Example query

  final List<Map<String, dynamic>> result = await odbc.execute(
    'SELECT * FROM USERS WHERE UID = ?',
    params: [1],
  );

Providing configuration for result set columns #

• The abstraction layer of DartOdbc should be able to handle output for most queries
• But output for columns with very long column size or uncommon data types could get corrupted due to issues in default memory allocation
• Thes can be handled by providing the ColumnType in the columnConfig parameter of the execute method on DartOdbc class
• Please refer the following example

  // Assume a table like this
  // +-----+-------+-------------+
  // | UID | NAME  | DESCRIPTION |
  // +-----+-------+-------------+
  // | 1   | Alice |             |
  // | 2   | Bob   |             |
  // +-----+-------+-------------+
  // The name is a column of size 150
  // The description is a column of size 500

  result = await odbc.execute(
    'SELECT * FROM USERS WHERE UID = ?',
    params: [1],

    /// The column config can be provided as this.
    /// But for most cases this config is not necessary
    /// This is only needed when the data fetching is not working as expected
    /// Only the columns with issues need to be provided
    columnConfig: {
      'NAME': ColumnType(size: 150),
      'DESCRIPTION': ColumnType(type: SQL_C_WCHAR, size: 500),
    },
  );

• Result will be a Future of List of Map objects (Future<List<Map<String, dynamic>>>) where each Map represents a row. If anything goes wrong an ODBCException will be thrown

Working with binary data #

• ODBC supports binary data types like BINARY and VARBINARY.
• To work with binary data, you must provide the ColumnType for the column in the columnConfig parameter of the execute method on DartOdbc class

  result = await odbc.execute(
    'SELECT * FROM USERS WHERE UID = ?',
    params: [1],
    columnConfig: {
      'BINARY_COLUMN': ColumnType(type: SQL_BINARY, size: 100),
      'VARBINARY_COLUMN': ColumnType(type: SQL_VARBINARY, size: 200),
    },
  );

• The resulting colummn will yield a Uint8List object.

Get Tables #

final List<Map<String, String>> tables = await odbc.getTables();

Disconnecting from the database #

• Finally, don't forget to disconnect from the database and free resources.

  await odbc.disconnect();

Accessing ODBC diver bindings directly #

• Native ODBC methods can be executed by using the LibOdbc class

• For more information on the ODBC api go to Microsoft ODBC Documentation

Testing #

Current status #

This package has been tested to be working on the following Database Servers

• Microsoft SQL Sever
• Oracle

Local testing #

• This gives an overview on how you can setup the environment for testing with SQL Server on linux. For windows or mac, please check out the official documentation from Microsoft mentioned above.

Getting SQL server up and running

1. Get a working sql server. For this you can use a sql server instance from a managed provider or install it locally or on docker.
2. For docker setup check out this guide

Setting up unixodbc and the Microsoft SQL Server ODBC driver

• For this, you can follow this detailed guide

Setting up the environment variables and the testing database.

1. Simply create a file .env in the project root, copy the content from the test.env to it and set the required variables according to your setup.
2. Connect to your SQL server and execute the commands in the test/testdb.sql file which will initialize the odbc_test database (or you can name this database any name and override it in the .env) which will be used for testing.

Run the tests

• Simply execute the following command to run the tests with dart cli

$ dart test

Support for other Database Servers #

• Although not tested, this plugin should work on any database that provides an ODBC Driver.
• For a comprehensive list of supported database servers checkout Drivers section of the official unixodbc site

💖 Support the Project #

Hey everyone! 👋 I'm actively maintaining this project while juggling my studies and other responsibilities. If you find my work useful and would like to help me keep improving this project, consider supporting me! Your contributions will help me cover expenses, buy more coffee ☕, and dedicate more time to development. 🙌

Every little bit helps, and I really appreciate your support. Thank you for helping me keep this project going! 💛

• buy me a coffee