sqljocky5 1.0.0

  • README.md
  • Example
  • Installing
  • Versions
  • 39

SQLJocky5 #

** This is a fork of the original SQLJocky. The goal is to maintain SQLJocky5 and give Dart a proper MySQL connector **

This is a MySQL connector for the Dart programming language. It isn't finished, but should work for most normal use. The API is getting reasonably close to where I want it to be now, so hopefully there shouldn't be too many breaking changes in the future.

It will only work in the command-line VM, not in a browser.

News #

The changelog has now been moved to CHANGELOG.md

Usage #

Create a connection pool:

var pool = new ConnectionPool(
    host: 'localhost', port: 3306,
    user: 'bob', password: 'wibble',
    db: 'stuff', max: 5);

Execute a query:

var results = await pool.query('select name, email from users');

Use the results: (Note: forEach is asynchronous.)

results.forEach((row) {
  print('Name: ${row[0]}, email: ${row[1]}');

Or access the fields by name:

results.forEach((row) {
  print('Name: ${row.name}, email: ${row.email}');

Prepare a query:

var query = await pool.prepare(
  'insert into users (name, email, age) values (?, ?, ?)');

Execute the query:

var result = await query.execute(['Bob', 'bob@bob.com', 25]);

An insert query's results will be empty, but will have an id if there was an auto-increment column in the table:

print("New user's id: ${result.insertId}");

Execute a query with multiple sets of parameters:

var results = await query.executeMulti([['Bob', 'bob@bob.com', 25],
    ['Bill', 'bill@bill.com', 26],
    ['Joe', 'joe@joe.com', 37]]);

Use the list of results:

for (result in results) {
  print("New user's id: ${result.insertId}");

Use a transaction:

var trans = await pool.startTransaction();
var result = await trans.query('...');
await trans.commit();

Development #

To run the examples and tests, you'll need to create a 'connection.options' file by copying 'connection.options.example' and modifying the settings.

Licence #

It is released under the GPL, because it uses a modified part of mysql's include/mysql_com.h in constants.dart, which is licensed under the GPL. I would prefer to release it under the BSD Licence, but there you go.

The Name #

It is named after Jocky Wilson, the late, great darts player. (Hence the lack of an 'e' in Jocky.)

Things to do #

  • Compression
  • More connection pool management (close after timeout, change pool size...)
  • Better handling of various data types, especially BLOBs, which behave differently when using straight queries and prepared queries.
  • Implement the rest of mysql's commands
  • Handle character sets properly? Currently defaults to UTF8 for the connection character set. Is it necessary to support anything else?
  • Improve performance where possible
  • Geometry type
  • Decimal type should probably use a bigdecimal type of some sort
  • MySQL 4 types (old decimal, anything else?)
  • Test against multiple mysql versions

Changelog #

v0.14.5 #

  • Fix package references

v0.14.3 #

  • Merged in Kevin Moore's PR from original SQLJockey

v0.14.1 #

  • Fix the changelog formatting, so you can actually see what changed in v0.14.0

v0.14.0 #

  • Requires Dart 1.11
  • Use newer logging library
  • Use async/await in library code and examples.
  • Fix bug with closing prepared queries, where it sometimes tried to close a query which was in use.
  • Don't throw an error if username is null.
  • Fix bug in blobs, where it was trying to decode binary blobs as UTF-8 strings.
  • Close connections and return them to the pool when a connection times out on the server.

v0.13.0 #

  • Fixes an issue with executeMulti being broken.
  • Fixes an issue with query failing if the first field in a SELECT is an empty string

v0.12.0 #

  • Breaking change: ConnectionPool.close() has been renamed to ConnectionPool.closeConnectionsNow. It is a dangerous method to call as it closes all connections even if they are in the middle of an operation. ConnectionPool.closeConnectionsWhenNotInUse has been added, which is much safer.
  • Fixed an issue with closing prepared queries which caused connections to remain open.

v0.11.0 #

  • Added support for packets larger than 16 MB. ConnectionPool's constructor has a new parameter, 'maxPacketSize', which specifies the maximum packet size in bytes. Using packets larger than 16 MB is not currently particularly optimised.
  • Fixed some issues with authentication. In particular, errors should now be thrown when you try to connect to a server which is using an old or unsupported authentication protocol.

v0.10.0 #

  • Added SSL connections. Pass 'useSSL: true' to ConnectionPool constructor. If server doesn't support SSL, connection will continue unsecured. You can check if the connections are secure by calling pool.getConnection().then((cnx) {print(cnx.usingSSL); cnx.release();});

v0.9.0 #

  • Added ConnectionPool.getConnection() which returns a RetainedConnection. Useful if you need to keep a specific connection around (for example, if you need to lock tables).

v0.8.3 #

  • Fixed connection retention error in Query.executeMulti

v0.8.1 #

  • Can now access fields by name.

v0.8.0 #

  • Breaking change: Results no longer has a 'stream' property - it now implements Stream itself. As a result, it also no longer has a 'rows' property, or a 'toResultsList()' method - you can use 'toList()' to convert it into a list instead.

v0.7.0 #

  • Rewritten some connection handling code to make it more robust, and so that it handles stream operations such as 'first' correctly (i.e. without hanging forever).
  • Updated spec for Dart 1.0

v0.6.2 #

  • Support for latest SDK (removal of dart:utf8 library)

v0.6.1 #

  • Support for latest SDK

v0.6.0 #

  • Change prepared statement syntax. Values must now be passed into the execute() method in an array. This change was made because otherwise prepared statements couldn't be used asynchronously correctly - if you used the same prepared query object for multiple queries 'at the same time', the wrong values could get used.

v0.5.8 #

  • Handle errors in the utils package properly
  • Pre-emptively fixed some errors, wrote more tests.

v0.5.7 #

  • Fixed error with large fields.

v0.5.6 #

  • Hopefully full unicode support
  • Fixed problem with null values in prepared queries.

v0.5.5 #

  • Some initial changes for better unicode handling.

v0.5.4 #

  • Blobs and Texts which are bigger than 250 characters now work.

v0.5.3 #

  • Make ConnectionPool and Transaction implement QueriableConnection
  • Improved tests.

v0.5.2 #

  • Fix for new SDK

v0.5.1 #

  • Made an internal class private

v0.5.0 #

  • Breaking change: Now uses streams to return results.

v0.4.1 #

  • Major refactoring so that only the parts of sqljocky which are supposed to be exposed are.

v0.4.0 #

  • Support for M4.

v0.3.0 #

  • Support for M3.
  • Bit fields are now numbers, not lists.
  • Dates now use the DateTime class instead of the Date class.
  • Use new IO classes.

v0.2.0 #

  • Support for the new SDK.

v0.1.3 #

  • SQLJocky now uses a connection pooling model, so the API has changed somewhat.


import 'package:sqljocky5/sqljocky.dart';
import 'package:sqljocky5/utils.dart';
import 'package:options_file/options_file.dart';
import 'dart:async';

 * This example drops a couple of tables if they exist, before recreating them.
 * It then stores some data in the database and reads it back out.
 * You must have a connection.options file in order for this to connect.

class Example {
  ConnectionPool pool;


  Future run() async {
    // drop the tables if they already exist
    await dropTables();
    print("dropped tables");
    // then recreate the tables
    await createTables();
    print("created tables");
    // add some data
    await addData();
    // and read it back out
    await readData();

  Future dropTables() {
    print("dropping tables");
    var dropper = new TableDropper(pool, ['pets', 'people']);
    return dropper.dropTables();

  Future createTables() {
    print("creating tables");
    var querier = new QueryRunner(pool, [
      'create table people (id integer not null auto_increment, '
          'name varchar(255), '
          'age integer, '
          'primary key (id))',
      'create table pets (id integer not null auto_increment, '
          'name varchar(255), '
          'species text, '
          'owner_id integer, '
          'primary key (id),'
          'foreign key (owner_id) references people (id))'
    print("executing queries");
    return querier.executeQueries();

  Future addData() async {
    var query =
        await pool.prepare("insert into people (name, age) values (?, ?)");
    print("prepared query 1");
    var parameters = [
      ["Dave", 15],
      ["John", 16],
      ["Mavis", 93]
    await query.executeMulti(parameters);

    print("executed query 1");
    query = await pool
        .prepare("insert into pets (name, species, owner_id) values (?, ?, ?)");

    print("prepared query 2");
    parameters = [
      ["Rover", "Dog", 1],
      ["Daisy", "Cow", 2],
      ["Spot", "Dog", 2]
//          ["Spot", "D\u0000og", 2]];
    await query.executeMulti(parameters);

    print("executed query 2");

  Future readData() async {
    var result =
        await pool.query('select p.id, p.name, p.age, t.name, t.species '
            'from people p '
            'left join pets t on t.owner_id = p.id');
    print("got results");
    return result.forEach((row) {
      if (row[3] == null) {
        print("ID: ${row[0]}, Name: ${row[1]}, Age: ${row[2]}, No Pets");
      } else {
            "ID: ${row[0]}, Name: ${row[1]}, Age: ${row[2]}, Pet Name: ${row[3]}, Pet Species ${row[4]}");

main() async {
  OptionsFile options = new OptionsFile('connection.options');
  String user = options.getString('user');
  String password = options.getString('password');
  int port = options.getInt('port', 3306);
  String db = options.getString('db');
  String host = options.getString('host', 'localhost');

  // create a connection
  print("opening connection");
  var pool = new ConnectionPool(
      host: host, port: port, user: user, password: password, db: db, max: 1);
  print("connection open");
  // create an example class
  var example = new Example(pool);
  // run the example
  print("running example");
  await example.run();
  // finally, close the connection
  print("K THNX BYE!");

Use this package as a library

1. Depend on it

Add this to your package's pubspec.yaml file:

  sqljocky5: ^1.0.0

2. Install it

You can install packages from the command line:

with pub:

$ pub get

Alternatively, your editor might support pub get. Check the docs for your editor to learn more.

3. Import it

Now in your Dart code, you can use:

import 'package:sqljocky5/constants.dart';
import 'package:sqljocky5/sqljocky.dart';
import 'package:sqljocky5/utils.dart';
Version Uploaded Documentation Archive
2.2.1 Feb 15, 2019 Go to the documentation of sqljocky5 2.2.1 Download sqljocky5 2.2.1 archive
2.2.0 Sep 6, 2018 Go to the documentation of sqljocky5 2.2.0 Download sqljocky5 2.2.0 archive
2.1.5 Sep 5, 2018 Go to the documentation of sqljocky5 2.1.5 Download sqljocky5 2.1.5 archive
1.0.0 Jul 12, 2017 Go to the documentation of sqljocky5 1.0.0 Download sqljocky5 1.0.0 archive
Describes how popular the package is relative to other packages. [more]
Code health derived from static analysis. [more]
Reflects how tidy and up-to-date the package is. [more]
Weighted score of the above. [more]
Learn more about scoring.

The package version is not analyzed, because it does not support Dart 2. Until this is resolved, the package will receive a health and maintenance score of 0.

Analysis issues and suggestions

Support Dart 2 in pubspec.yaml.

The SDK constraint in pubspec.yaml doesn't allow the Dart 2.0.0 release. For information about upgrading it to be Dart 2 compatible, please see https://dart.dev/dart-2#migration.

Maintenance issues and suggestions

Make sure dartdoc successfully runs on your package's source files. (-10 points)

Dependencies were not resolved.


Package Constraint Resolved Available
Direct dependencies
Dart SDK >=1.11.0 <2.0.0