Skip to content

Home

Jump to bottom
Mitchell Weg edited this page May 6, 2021 · 1 revision

Welcome to the MonetDB-ruby wiki!

Introduction

A typical sequence of events is as follows: Create a database instance (handle), invoke query using the database handle to send the statement to the server and get back a result set object.

A result set object has methods for fetching rows, moving around in the result set, obtaining column metadata, and releasing the result set. A result set object is an instance of the MonetDBData class.

Records can be returned as arrays and iterators over the set.

A database handler (dbh) is and instance of the MonetDB class.

Connection management

connect: establish a new connection

  • user: username (default is monetdb)

  • passwd: password (default is monetdb)

  • lang: language (default is sql)

  • host: server hostname or ip (default is localhost)

  • port: server port (default is 50000)

  • db_name: name of the database to connect to

  • auth_type: hashing function to use during authentication (default is SHA1)

  • is_connected? - returns true if there is an active connection to a server, false otherwise

  • reconnect - reconnect to a server

  • close - terminate a connection

  • auto_commit? - returns true if the session is running in auto commit mode, false otherwise

  • auto_commit - enable/disable auto commit mode.

  • query - fire a query

Currently MAPI protocols 8 and 9 are supported.

Managing record sets

A record set is represented as an instance of the MonetDBData class; the class provides methods to manage retrieved data.

The following methods allow to iterate over data:

  • fetch - iterates over the record set and retrieves one row at a time. Each row is returned as an array.

  • each_record - works as ruby each method. The method takes a block as parameter and yields each record to this block.

  • fetch_hash - iterates over the record set and retrieves one row at a time. Each row is returned as a hash.

  • each_record_as_hash - works as ruby each method. The method takes a block as parameter and yields each record, as hash, to this block.

  • fetch_all - returns all rows as a two dimensional array.

  • fetch_all_as_column_hash - returns all records as a hash with the column name as the keys and an array with all column values as values

information about the retrieved record set can be obtained via the following methods:

  • num_rows - returns the number of rows present in the record set
  • num_fields - returns the number of fields (columns) that compose the schema
  • name_fields - returns the (ordered) name of the schema's columns
  • type_fields - returns the (ordered) types list of the schema's columns

To release a record set MonetDBData#free can be used.

Type conversion

All values from the database are converted to the closest ruby type, i.e: INTEGER to int, TIME to time, CLOB to string Some of the more complex datatypes are not recognized, such as INTERVAL, these are converted to strings

Transactions

By default monetdb works in auto_commit mode. To turn this feature off MonetDB#auto_commit(flag=false) can be used.

Once auto_commit has been disable it is possible to start transactions, create/delete savepoints, rollback and commit with the usual SQL statements.

Savepoints IDs can be generated using the MonetDB#save method. To release a savepoint ID use MonetDB#release.

Savepoints can be accessed (as a stack) with the MonetDB#transactions method.

example.rb contains usage example of the above mentioned methods.

Clone this wiki locally