# MySQL/Ruby

[Japanese]

This is the MySQL API module for Ruby. It provides the same functions for Ruby programs that the MySQL C API provides for C programs.

## Download

- RubyForge
- tmtm.org

## Requirement

- MySQL 5.0.67
- Ruby 1.8.7, 1.9.1

The module may work for other versions, but that has not been verified.

## License

This program is under Ruby's license.

## Install

1st:

    % ruby extconf.rb

or

    % ruby extconf.rb --with-mysql-dir=/usr/local/mysql

or

    % ruby extconf.rb --with-mysql-config

then

    % make

extconf.rb has following options:

--with-mysql-include=dir
  MySQL header file directory. Default is /usr/local/include.
--with-mysql-lib=dir
  MySQL library directory. Default is /usr/local/lib.
--with-mysql-dir=dir
  Same as --with-mysql-include=dir/include, --with-mysql-lib=dir/lib.
--with-mysql-config[=/path/to/mysql_config]
  Get compile-parameter from mysql_config command.

2nd:

    % ruby ./test.rb -- [hostname [user [passwd [dbname [port [socket [flag]]]]]]]

3rd:

    # make install

### Note

If you get error like 'libmysqlclient not found' when testing, you need to specify the directory in which the library is located so that make can find it.

    % env LD_RUN_PATH=libmysqlclient.so directory make

test.rb is tested on Linux only.

## Usage

The names of methods provided by this module basically are the same as the names of the functions in the C API, except that the Ruby method names do not begin with a 'mysql_' prefix. For example, the Ruby query() method corresponds to the C API mysql_query() function. For details on the use of each Ruby method, see the descriptions of the corresponding C functions in the MySQL Reference Manual.

Some Ruby methods may be invoked under other names that serve as equivalent aliases, as noted below.

If an error occurs when a method executes, it raises a Mysql::Error exception.

## Mysql class

### CLASS METHODS

init()

  It return Mysql object. It not connect to mysqld.

real_connect(host=nil, user=nil, passwd=nil, db=nil, port=nil, sock=nil, flag=nil)
connect(host=nil, user=nil, passwd=nil, db=nil, port=nil, sock=nil, flag=nil)
new(host=nil, user=nil, passwd=nil, db=nil, port=nil, sock=nil, flag=nil)

  connect to mysqld and return Mysql object.

escape_string(str)
quote(str)

  quote string for insert/update.

get_client_info()
client_info()

  return client version information.

get_client_version()
client_version()

  return client version as number.

debug(str)

  same as C API mysql_debug().

### OBJECT METHODS

options(opt, val=nil)

  same as C API mysql_options().

real_connect(host=nil, user=nil, passwd=nil, db=nil, port=nil, sock=nil, flag=nil)
connect(host=nil, user=nil, passwd=nil, db=nil, port=nil, sock=nil, flag=nil)

  same as Mysql.real_connect().

affected_rows()

  return affected rows.

autocommit(mode)

  set autocommit mode.

change_user(user=nil, passwd=nil, db=nil)

  change user.

character_set_name()

  return character set.

close()

  close connection.

commit()

  commit transaction.

create_db(db)

  create database.

drop_db(db)

  drop database.

dump_debug_info()

  same as C API mysql_dump_debug_info().

errno()

  return error number.

error()

  return error message.

escape_string(str)
quote(str)

  quote strings for insert/update. same as C API mysql_real_escape_string().

field_count()

  return number of columns of last query.

get_client_info()
client_info()

  return client version information.

get_client_version()
client_version()

  return client version number.

get_host_info()
host_info()

  return connection information.

get_proto_info()
proto_info()

  return connection protocol version.

get_server_info()
server_info()

  return server version information.

get_server_version()
server_version()

  return server version number.

info()

  return information of last query.

insert_id()

  return last AUTO_INCREMENT value.

kill(id)

  kill thread.

list_dbs(db=nil)

  return database list.

list_fields(table, field=nil)

  return Mysql::Result object.

list_processes()

  return Mysql::Result object.

list_tables(table=nil)

  return table list Array.

more_results?()

  returns true if more results exist from the currently executed query.

next_result()

  returns true if more results exist from the currently executed query. after this, you do store_result() to get result table of query.

ping()

  check server.

prepare(q)

query(q)
real_query(q)

  do query and store_result(). return Mysql::Result object. If query_with_result is false, it does not store_result().

query(q) {|res| ...}
real_query(q) {|res| ...}

  do query and execute block with Mysql::Result object. Mysql::Result object is freed when exiting block. If multiple statement mode, it does repeat block each query.

  Since MySQL/Ruby 2.8, it no longer turn on multiple statement mode automatically. If you want to turn on multiple statement mode, set Mysql::CLIENT_MULTI_STATEMENTS for Mysql.connect or execute Mysql#set_server_option(Mysql::OPTION_MULTI_STATEMENTS_ON).

refresh(r)

  flush server log or cache.

reload()

  reload access privilege table.

rollback()

  rollback transaction.

select_db(db)

  select database.

set_server_option(opt)

  set option to server. options is one of Mysql::OPTION_MULTI_STATEMENTS_ON, Mysql::OPTION_MULTI_STATEMENTS_OFF.

shutdown()

  shutdown server.

ssl_set(key=nil, cert=nil, ca=nil, capath=nil, cipher=nil)

  use SSL.

stat()

  return server status.

stmt_init()

  return Mysql::Stmt class object.

store_result()

  return Mysql::Result object.

thread_id()

  retrun thread id.

use_result()

  return Mysql::Result object.

warning_count()

  return warning count last query.

### OBJECT VARIABLES

query_with_result

  If true, query() also invokes store_result() and returns a Mysql::Result object. Default is true.

reconnect

  If true, reconnect to server automatically when disconect to server. Default is false.

## Mysql::Result class

### OBJECT METHODS

free()

  free memory of result table.

data_seek(offset)

  seek row.

fetch_field()

  return next Mysql::Field object.

fetch_fields()

  return Array of Mysql::Field object.

fetch_field_direct(fieldnr)

  return Mysql::Field object.

fetch_lengths()

  return Array of field length.

fetch_row()

  return row as Array.

fetch_hash(with_table=false)

  return row as Hash. If with_table is true, hash key format is "tablename.fieldname".

field_seek(offset)

  seek field.

field_tell()

  return field position.

num_fields()

  return number of fields.

num_rows()

  return number of rows.

row_seek(offset)

  seek row.

row_tell()

  return row position.

### ITERATOR

each() {|x| ...}

  'x' is array of column values.

each_hash(with_table=false) {|x| ...}

  'x' is hash of column values, and the keys are the column names.

## Mysql::Field class

### OBJECT VARIABLES(read only)

name
  field name
table
  table name
def
  default value
type
  field type
length
  field length
max_length
  max field length
flags
  field flag
decimals
  number of decimals

### OBJECT METHODS

hash()

  return field as Hash.

  ex.) obj.name == obj.hash['name']

is_not_null?()

  True if this field is defined as NOT NULL.

is_num?()

  True if this field type is numeric.

is_pri_key?()

  True if this field is a primary key.

inspect()

  return "#<Mysql::Field:fieldname>"

## Mysql::Stmt class

Example:

    my = Mysql.new(hostname, username, password, databasename)
    st = my.prepare("insert into tblname (col1,col2,col3) values (?,?,?)")
    st.execute("abc",123,Time.now)
    st.prepare("select col1,col2,col3 from tblname")
    st.execute
    st.fetch  # => ["abc", 123, #<Mysql::Time:2005-07-24 23:52:55>]
    st.close

### OBJECT METHODS

affected_rows()

bind_result(class, ...)

close()

data_seek(offset)

execute(arg, ...)

fetch()

  Type mapping:

  MySQL type | Ruby class
  --- | ---
  TINYINT, SMALLINT, MEDIUMINT, YEAR | Fixnum
  INT, BIGINT | Fixnum or Bignum
  FLOAT, DOUBLE | Float
  DECIMAL | String
  DATE, DATETIME, TIMESTAMP, TIME | Mysql::Time
  CHAR, VARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, TINYBLOB, TINYTEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET, BIT | String
  NULL | NilClass

field_count()

free_result()

insert_id()

num_rows()

param_count()

prepare(q)

result_metadata()

row_seek(offset)

row_tell()

sqlstate()

### ITERATOR

each() {|x| ...}

## Mysql::Time class

### CLASS METHODS

new(year=0,month=0,day=0,hour=0,minute=0,second=0,neg=false,second_part=0)

### OBJECT VARIABLES

year
month
day
hour
minute
second
neg
second_part

## Mysql::Error class

### OBJECT VARIABLES(read only)

error
  eror message
errno
  error number

## History

2010-02-11
  version 2.8.2
  - Fix: Mysql#insert_id returns invalid value when larger than 2**32.
2009-02-01
  version 2.8.1
  - correspond to Ruby 1.9.1
2008-09-29
  version 2.8
  version 2.7.7
  - When connecting to MySQL, EINTR is occurred sometimes ([ruby-dev:31842])
  - MySQL/Ruby 2.7.* can not be compiled on Ruby 1.8.5.
2008-06-20
  version 2.8pre4
  - [ruby-dev:35152]
2008-06-17
  version 2.8pre3
  version 2.7.6
  - On 64bit machine, Mysql::Stmt#execute raise error on large numeric value(>= 2**30).
2008-03-08
  version 2.8pre2
  version 2.7.5
  - On 64bit machine, Mysql::Stmt#fetch return invalid numeric value.
2007-12-26
  version 2.8pre1
  - for Ruby 1.9.0
  - Incompat: Mysql::Result#each_hash don't create column name string each row. it's shared.
  - Incompat: Mysql#query with block no longer turn on multi-statements mode automatically.
2007-08-22
  version 2.7.4
  - BUG: Mysql::Stmt#execute memory leak.
2006-12-20
  version 2.7.3
  - BUG: Mysql#query with block is stopped when last query failed.
2006-10-28
  version 2.7.2
  - BUG: Mysql::Stmt#result_metadata don't return nil. (Thanks to Hidetoshi)
  - BUG: Mysql#close check mysql_errno.
  - BUG: multistatement Mysql#query with block ignore error.
  - extconf.rb for Visual C++. (Thanks to Shugo Maeda)
  - support MySQL BIT type.
  - add Mysql::Field::TYPE_BIT, TYPE_NEWDECIMAL.
2006-06-04
  version 2.7.1
  - change free() to xfree(). To avoid crash on Windows. (Thanks Tobias Grimm)
2005-08-22
  version 2.7
  - add constants for Mysql#options: Mysql::OPT_GUESS_CONNECTION, Mysql::OPT_USE_EMBEDDED_CONNECTION, Mysql::OPT_USE_REMOTE_CONNECTION, Mysql::SET_CLIENT_IP
  - test.rb: for 4.0.x, 5.0.x
2005-08-16
  version 2.7-beta3
  - add Mysql::Stmt#bind_result
2005-08-02
  version 2.7-beta2
  - BUG: mysql.c.in: fetch_hash: nil value doesn't exist in hash. (Thanks Stefan Kaes)
  - add constant Mysql::VERSION.
  - add Mysql#prepare
2005-07-24
  version 2.7-beta
  - add Mysql#stmt_init method
  - add Mysql::Stmt, Mysql::Time, Mysql::RowOffset class
  - add Mysql::Error#sqlstate method
  - change offset value to Mysql::RowOffset object that is used by Mysql::Result#row_seek,row_tell
2005-07-31
  version 2.6.3
  - add constant Mysql::VERSION.
2005-07-26
  version 2.6.2
  - BUG: mysql.c.in: fetch_hash: nil value doesn't exist in hash. (Thanks Stefan Kaes)
2005-06-28
  version 2.6.1
  - mysql.c.in: fix to compile error on MacOSX.
2005-04-25
  version 2.6
  - add constants for Mysql#option(): Mysql::OPT_PROTOCOL, Mysql::OPT_READ_TIMEOUT, Mysql::OPT_WRITE_TIMEOUT, Mysql::SET_CHARSET_DIR, Mysql::SET_CHARSET_NAME, Mysql::SHARED_MEMORY_BASE_NAME, Mysql::SECURE_AUTH
  - add methods: Mysql#more_results?(), Mysql#next_result(), Mysql#set_server_option(), Mysql#sqlstate()
  - add constants for Mysql#connect(): Mysql::CLIENT_MULTI_STATEMENTS, Mysql::CLIENT_MULTI_RESULTS
  - add constants for Mysql#set_server_option(): Mysql::OPTION_MULTI_STATEMENTS_ON, Mysql::OPTION_MULTI_STATEMENTS_OFF
  - add Mysql#query() with block
  - add Mysql#reconnect(), Mysql#reconnect=()
  - When connection was closed, it don't try to reconnect by default.
2005-02-12
  version 2.5.2
  - BUG: Mysql#connect make object to not close. (Thanks Andres Salomon)
2004-09-20
  version 2.5.1
  - add Mysql#set_ssl().
2004-08-31
  version 2.5
  - correspond to MySQL 4.1.x.
  - change MysqlRes, MysqlField, MysqlError to Mysql::Result, Mysql::Field, Mysql::Error.
  - add Mysql.client_version(), Mysql.get_client_version(), Mysql#client_version(), Mysql#get_client_version(), Mysql#server_version(), Mysql#get_server_version(), Mysql#warning_count(), Mysql#commit(), Mysql#rollback(), Mysql#autocommit().
  - add Mysql::Field#is_not_null?(), Mysql::Field#is_pri_key?(), Mysql::Field#is_num?().
  - add MysqlField::TYPE_VAR_STRING.
2003-08-10
  version 2.4.5
  - extconf.rb: correspond to MySQL 4.1.
  - mysql.c.in: correspond to Ruby 1.8.
2003-02-23
  version 2.4.4a
  - make extconf.rb to correspond to Ruby 1.8.0
2003-01-29
  version 2.4.4
  - add Mysql::OPT_LOCAL_INFILE.
  - add --with-mysql-config option to extconf.rb.
  - extconf.rb automatically detect typical library.
2003-01-05
  version 2.4.3c
  - modified English README. Thanks to Paul DuBois.
2002-12-24
  version 2.4.3b
  - make extconf.rb to correspond to Ruby 1.6.8.
2002-11-07
  version 2.4.3a
  - fix bug duplicating constant.
2002-09-10
  version 2.4.3
  - for error number with prefix ER_ .
  - get error constant from errmsg.h and mysqld_error.h automatically.
2002-01-07
  version 2.4.2
  - for MySQL 4.0.
  - change `uint' to `unsigned int' (for mswin).
2001-12-02
  version 2.4.1
  - remove `extern' (for Cygiwn).
  - change option of extconf.rb.
2001-10-12
  version 2.4.0
  - for Ruby 1.7.
  - add Mysql::debug(), Mysql#change_user(), Mysql#character_set_name(), Mysql#dump_debug_info().

## Author

e-mail: TOMITA Masahiro tommy@tmtm.org http://tmtm.org

Last modified: Sun Feb 1 17:48:41 JST 2009