Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

CUBRID Ruby API Documentation


Contents

Overview

This tutorial will explain all steps that you need to perform in order to connect to CUBRID Database and run SQL statements through Ruby programming language.

At any time if you feel lost somewhere, you can always visit the main page for Ruby Programming to start from the beginning.

Getting Help

If you have any troubles connecting to CUBRID through Ruby, post your questions to Ruby Forum at http://www.cubrid.org/forum.

Preparing the Environment

Preparing CUBRID

  1. First thing you have to do is to make sure you have installed CUBRID Database according to all the steps explained in Install CUBRID. If you have not installed CUBRID yet, Download CUBRID and follow the Installation Instructions. In this tutorial I will be using the latest stable release CUBRID 9.1.0. On my machine it is installed in C:CUBRID. However, you do not have to worry much about it, as the CUBRID Installer automatically adds its location path to the Windows environment.
  2. NOTE For Windows users: Make sure you have installed Java Development Kit (JDK) and Microsoft C++ 2008 Redistributable Package.

Preparing Ruby

Gem_install_cubrid_web.png

On Windows

Install Ruby

You can download Ruby Installer from various places. I prefer to Download Ruby Installer from http://rubyinstaller.org. At this point you will see me using pure Ruby 1.8.7-p299. Currently Ruby 1.9.1 is not supported, as the Ruby Driver for CUBRID has been compiled in the 1.8.7 environment. But we have already started building for 1.9.1, so it will be available soon.

Install Ruby Driver for CUBRID

If you have installed the above mentioned Ruby 1.8.7-p299, you should already have RubyGem installed. RubyGem is required to install additional external packages such as CUBRID Database driver, ActiveRecord, and other similar extensions. In order to install the Ruby Driver for CUBRID, in the command line type gem install cubrid as shown below.

C:\Ruby> gem install cubrid
Successfully installed cubrid-0.65-x86-mingw32
1 gem installed
Installing ri documentation for cubrid-0.65-x86-mingw32...
Installing RDoc documentation for cubrid-0.65-x86-mingw32...

At this point you should see the message about the successful installation of cubrid-0.65-x86-mingw32 driver. The Ruby Driver provides the same functions as the original CUBRID CCI (C Interface) Library does. Below you will see sample tests which illustrate how to communicate with the CUBRID Database.

On Linux

Install Ruby

In order to install Ruby, we will use the generic Linux command. In this tutorial we will work under Ubuntu 10.4, and install Ruby 1.8.7. Type the following command to get Ruby and other necessary packages installed together.

sudo apt-get -y install ruby1.8-dev ruby1.8 ri1.8 rdoc1.8 irb1.8 libreadline-ruby1.8 libruby1.8 libopenssl-ruby rubygems1.8
sudo ln -s /usr/bin/ruby1.8 /usr/bin/ruby
sudo ln -s /usr/bin/rdoc1.8 /usr/bin/rdoc
sudo ln -s /usr/bin/irb1.8 /usr/bin/irb
sudo ln -s /usr/bin/ri1.8 /usr/bin/ri

The first line indicates which packages should be installed. Notice that I am installing Ruby 1.8 version. At this moment Ruby 1.9.1 is not supported. But we have already started building for 1.9.1, so it will be available soon. The rest four lines create symbol links so that you are able to run the commands link "ruby", "rdoc", "irb", and "ri". Actually, you will need only the first one.

Install Ruby Driver for CUBRID

On Linux there are two ways to get Ruby Driver for CUBRID installed.

  1. Install Ruby Gem.
  2. Manually compile the code and install the library.

Install Ruby Gem

  • CUBRID gem has been successfully pushed to RubyGems.org. So, now on Linux users can get CUBRID connector through gem.
  • Add the -E flag when installing the gem with sudo like "sudo -E gem install cubrid". The -E flag is necessary to make sure sudo does not reset the user path where CUBRID Database has been installed.

Manually Build the Library

  • Download source code of Ruby Driver for CUBRID.
  • Extract, then run the following command in the terminal, which means that you are running the Ruby code within extconf.rb file. It will create a Makefile.
ruby extconf.rb
  • After the Makefile has been successfully created, type the following command to install the driver in your system.
make install

That's all! To see if everything has successfully been installed, print out the CUBRID Database version. Remember that in order to connect to a certain database, you have to first start that database. Otherwise, you will receive an error.

Example

Create a file called "testcubrid.rb" and paste the following Ruby code.

require 'rubygems'
require 'cubrid'
db = 'demodb'
con = Cubrid.connect(db)
if con
	puts "connection established"
	puts "CUBRID Database version is: #{con.server_version}"
	con.close()
else
	puts "Connection could not be established"
end

Then type the following commands in the terminal.

cubrid service start
cubrid server start demodb
ruby --rubygems testcubrid.rb
  1. The first line will start CUBRID services.
  2. The second line will start the database called "demodb".
  3. The third line will run the Ruby code inside "testcurbid.rb" file.

You should see the following output.

connection established
CUBRID Database version is: 8.2.2.0261

Ruby API

Connection Class

Data Constants

Description

The following Data Types represent the Cubrid class data type constants declared in the Ruby API.

Syntax

Cubrid::DATE
Support Data Type Constants
CHAR
VARCHAR
STRING
NCHAR
VARNCHAR
BIT
VARBIT
NUMERIC
INT
SHORT
MONETARY
FLOAT
DOUBLE
DATE
TIME
TIMESTAMP
SET
MULTISET
SEQUENCE
OBJECT
INCLUDE_OID
READ_LOCK
WRITE_LOCK

auto_commit=

Description

This method is used to set the auto_commit mode of the current connection. If auto_commit is set to true, after every Statement.execute call the transaction will be immediately committed.

Syntax

auto_commit= true or false ? nil

Return value

  • nil

Example:

 

# insert data without commit
con = Cubrid.connect($database,$host,$port,$user,$password)
if con
  con.query("DROP TABLE IF EXISTS test_cubrid")
  con.query("CREATE TABLE test_cubrid (a INT)")
  con.auto_commit = false
  con.query("INSERT INTO test_cubrid values (11)")
  con.close
else
  puts "Connection could not be established"
end
con = Cubrid.connect($database,$host,$port,$user,$password)
stmt = con.query('SELECT * FROM test_cubrid')
assert_equal(0, stmt.affected_rows);

 

auto_commit?

Description

This method is used to determine whether auto_commit mode of the current connection is set to true or false.

Syntax

auto_commit? ? true or false

Return value

  • true: auto_commit is set to true
  • false: auto_commit is set to false

connect

Description

This method is used to establish the connection with the database server by using the given information such as the database name, server address, port number, username and password. If the username and password are not set, public is used as default the default username and "" (empty password) is used as a password.

Example

@con = Cubrid.connect('db_name', 'host', port, 'db_user', 'db_password')
 
#OR
 
@con = Cubrid.connect('db_name')
  • db_name : a string representing the database name
  • host : a string representing the host name of the server (by default it is localhost)
  • port : an integer representing the server port (by default it is 30000 - thirty thousand)
  • db_user : a string representing the database user name (by default it is public)
  • db_passwd : a string representing the database user password (by default it is "" - empty password)

Return Value

  • @con: The connection has successfully been established!
  • nil: Filed to establish the connection.

close

Description

This method is used to terminate the connection with the server. All pending Statements will also be closed.

Example

@con.close()

commit

Description

This method is used to commit the transactions being performed currently in the connection.

Example

@con.commit

Return value

  • nil

rollback

Description

This method is used to rollback the last performed transactions. If the transaction is terminated, all Statement objects will also be closed.

Example

 

con = Cubrid.connect($database,$host,$port,$user,$password)
if con
  con.query("DROP TABLE IF EXISTS test_cubrid")
  con.query("CREATE TABLE test_cubrid (a INT)")
 
  con.auto_commit = false
  con.query("INSERT INTO test_cubrid values (11)")
  stmt = con.query('SELECT * FROM test_cubrid')
  assert_equal(1, stmt.affected_rows);
 
  # after rollback
  con.rollback
  stmt = con.query('SELECT * FROM test_cubrid')
  assert_equal(0, stmt.affected_rows);
  con.close
else
  puts "Connection could not be established"
end

 

Return value

  • nil

query

Description

This method directly executes the SQL statement, which has been passed as a parameter, without a need to perform the execute method.

Syntax

query(sql [, option]) ? Statement
 
OR
 
query(sql [, option]) { |row|
  ...
} ? nil 

Parameters

  • sql : a string representing the SQL statement
  • option[Optional] If Cubrid::INCLUDE_OID is passed as an option, the query method will behave like the prepare method.

Return value

  • stmt: The request handler
  • nil: The query has not been executed

Examples

A.

sql = "INSERT INTO table1 (id, name) VALUES (1, 'Ruby')"
stmt = @con.query(sql)
@con.commit

B.

con = Cubrid.connect('demodb')
stmt = con.query('SELECT * FROM db_user')
 
while row = stmt.fetch
   print row
end

stmt.close
stmt = con.query('SELECT * FROM db_user') { |row|
   print row
}

con.close

prepare

Description

This method is used to prepare an SQL statement.

Syntax

prepare(sql <, option>) ? Statement
 
#OR
 
prepare(sql <, option>) { |stmt|
  ...
} ? nil 

Parameters

  • sql : a string representing the SQL statement
  • option : [Optional] If Cubrid::INCLUDE_OID is passed as an option, the returned results will include OID, which can be later retrieved by calling stmt.get_oid method.

Return Value

  • stmt: The request handle
  • nil: Filed to prepare the SQL statement.

Example

con = Cubrid.connect($database,$host,$port,$user,$password)
 if con
     con.query("DROP TABLE IF EXISTS test_cubrid")
     con.query("CREATE TABLE test_cubrid (a INT, b DOUBLE, c STRING, d DATE)")
     con.prepare('insert into test_cubrid values (?, ?, ?, ?)') { |stmt|
       stmt.execute(10, 3.14, 'hello', Time.local(2013, 10, 9))
     }
     stmt = con.query('SELECT * FROM test_cubrid')
     while row = stmt.fetch
       assert_equal(10, row0);
       assert_equal(3.14, row1);
       assert_equal('hello', row2);
       assert_equal('2013-10-09', row3.strftime("%Y-%m-%d"));
     end
 con.close
 else
   puts "Connection could not be established"
 end

to_s

Description

This method is used to get the current connection information.

Example

 

        con = Cubrid.connect($database,$host,$port,$user,$password)
         if con 
           connection_info = con.to_s
           assert_equal("host: #{$host}, port: #{$port}, db: #{$database}, user: #{$user}", connection_info)
         else
           puts "Connection could not be established"
         end

 

Return values

  • connection_info : a string representing the connection information.

server_version

Description

This method is used to get the version of the database server.

Example

 

        con = Cubrid.connect($database,$host,$port,$user,$password)
        if con 
          s_version = con.server_version
          assert_match("9.2.0", s_version)
        else
          puts "Connection could not be established"
        end

 

Return values

  • version : a string representing the version of the database server.

last insert id

Description

This method is used to get the last insert id.

Example

require 'rubygems'
require 'cubrid'
db = 'demodb'
c = Cubrid.connect(db)
c.query('create table test_cubrid(id NUMERIC AUTO_INCREMENT(10300, 1), name VARCHAR(50))')
c.query('insert into test_cubrid(name) values (\'Lily\')')
puts c.last_insert_id()

Return values

  • last_insert_id: a Fixnum/Bignum that represents the last insert id. 

 

 

Statement Class

Data Types

The table below shows the data type conversion between CUBRID native data types and Ruby representation.

CUBRID Data Types Ruby Data Type Representation
int, short fixnum, bignum
float, double, numeric, monetary float
char, varchar, ncahr, varnchar string
bit, varbit string
date, time, timestamp Time
object OID
collection array

affected_rows

Description

This method is used to get the number of rows which have been affected by the SQL statements (SELECT,INSERT, DELETE, UPDATE).

Example

 

        con = Cubrid.connect($database,$host,$port,$user,$password)
         if con 
           con.query("DROP TABLE IF EXISTS test_cubrid")
           con.query("CREATE TABLE test_cubrid (id INT, name STRING)")
         
           stmt = con.query("INSERT INTO test_cubrid VALUES (1, 'Lily')")
           assert_equal(1, stmt.affected_rows)
           stmt = con.query("INSERT INTO test_cubrid VALUES (2, 'Lucy'),(3, 'Sue'),(4, 'Paul')")
           assert_equal(3, stmt.affected_rows)
         
           stmt = con.query("UPDATE test_cubrid SET name = 'May' WHERE id = 3 or name = 'Lily'")
           assert_equal(2, stmt.affected_rows)
         
           stmt = con.query("SELECT * FROM test_cubrid")
           assert_equal(4, stmt.affected_rows)
           stmt = con.query("SELECT * FROM test_cubrid where name = 'May'")
           assert_equal(2, stmt.affected_rows)
         
           stmt = con.query("DELETE FROM test_cubrid where id <= 3")
           assert_equal(3, stmt.affected_rows)
           con.close
         else
           puts "Connection could not be established"
         end

 

Return values

  • num : an integer representing the number of rows affected by the last SQL statement.

bind

Description

This method is used to substitute the values instead of the variables indicated in the SQL queries as question marks (?) in the prepare method.

Syntax

bind(index, data [, db_type, set_type]) ? nil 

Parameters

  • index : an integer representing the order of the variable to be replaced - first, second, ...
  • data : a value representing the data to be replaced for the variable at the order index.
  • db_type : [Optional] db_type is a type of the value to be bound. It can be omitted by default. If it is omitted, the type is automatically cast to an appropriate one.
  • set_type : [Optional]

Return value

  • nil

Example

index = 1
value = "Ruby"

stmt.bind(index, value)
con = Cubrid.connect('demodb')
con.auto_commit = true
con.query('create table a (a int, b double, c string, d date)')
 
con.prepare('insert into a values (?, ?, ?, ?)') { |stmt|
   stmt.bind(1, 10)
   stmt.bind(2, 3.141592)
   stmt.bind(3, 'hello')
   stmt.bind(4, Time.local(2007, 12, 25, 10, 10, 10), Cubrid::DATE)
   stmt.execute
}
con.close

close

Description

This method is used to close the Statement.

Syntax

close() ? nil 

Return value

  • nil

Example

stmt.close

column_info

Description

Returns the column information of the fetched rows. Because of the column information is represented as a hash, it is returned in the form of array.

Syntax

column_info() ? array 

Return value

  • array: an array representing the hash of the key name, type_name, precision, scale, nullable, which refer to the column's name, the column's data type, precision, scale, null-ability, respectively.

Example

         con = Cubrid.connect($database,$host,$port,$user,$password)
         if con 
           con.query("DROP TABLE IF EXISTS test_cubrid")
           con.query('CREATE TABLE test_cubrid (col_1 INT NOT NULL, col_2 VARCHAR(256), col_3 DATE, col_4 SEQUENCE)')
           stmt = @con.query("SELECT * FROM test_cubrid")
           stmt.execute        
           assert(stmt.column_info, "Get statement column info failed.")         
           col_name_answer = 'col_1','col_2','col_3','col_4'
           col_name_result = Array.new;         
           col_precision_answer = 0,256,0,0
           col_precision_result = Array.new;        
           col_null_answer = 1,0,0,0
           col_null_result = Array.new;         
           stmt.column_info.each { |col|
             col_name_result.push(col'name')
             col_precision_result.push(col'precision')
             col_null_result.push(col'nullable')
           }       
           assert_equal(col_name_answer, col_name_result)
           assert_equal(col_precision_answer, col_precision_result)
           assert_equal(col_null_answer, col_null_result)
           con.close
         else
           puts "Connection could not be established"
         end

each

Description

Returns the fetched rows one by one in a cycle to the inside block. The row is represented as an array.

Syntax

each() { |row| block } ? nil 

Return value

  • nil

Example

con = Cubrid.connect('demodb')
con.prepare('SELECT * FROM db_user') { |stmt|
   stmt.execute
   stmt.each { |r|
   print r[0]
   }
}
con.close

each_hash

Description

Returns the fetched rows one by one in a cycle to the inside block similar to the each() method. The difference is that each_hash returns a row which is represented as a hash.

Syntax

each_hash() { |hash| block } ? nil

Return value

  • nil

Example

con = Cubrid.connect('demodb')
con.prepare('SELECT * FROM db_user') { |stmt|
   stmt.execute
   stmt.each_hash { |r|
   print r['name']
   }
}
con.close

execute

Description

This method is used to execute a given SQL statement. It can receive zero or more parameters depending on whether the bind method has been used before or not. If the statement stmt has been already binded' to the values, the execute method does not need any parameter. If the statementstmt has not been binded' to any values yet, the execute method will instantly bind the receive parameters and execute the SQL statement. If auto_commit is set to true, the execute method will automatically commit the transaction.

Syntax

execute() ? int
execute(...) ? int

Parameters

  • values : [Optional] a value representing the data to be binded to each variable in the SQL statement.

Return value

  • int: Returns 1, if successful; 0 in case of failure.

Example

con = Cubrid.connect('demodb')
con.prepare('insert into a values (?, ?, ?, ?)') { |stmt|
   stmt.execute (10, 3.141592, 'hello', Time.local(2007, 12, 25))
}
con.close
sql = "INSERT INTO table1 (id, name) VALUES (?, ?)"
stmt = @con.prepare(sql)
value1 = 1
value2 = "Ruby"
stmt.bind(1, value1)
stmt.bind(2, value2)
stmt.execute

OR

sql = "INSERT INTO table1 (id, name) VALUES (?, ?)"
stmt = @con.prepare(sql)
stmt.execute(value1, value2)

fetch

Description

Returns the row under the cursor as an array of column data. Once fetched the cursor is incremented by one, that is pointed to the next row. The row is represented as an array.

Syntax

fetch() ? array or nil 

Return value

  • array : an array representing the row with columns.
  • nil: if there is no row to be fetched, the method returns nil.

Below is a table which illustrates how

Example

con = Cubrid.connect('demodb')
con.prepare('SELECT * FROM db_user') { |stmt|
   stmt.execute
   r = stmt.fetch
   print r[0]
}
con.close

fetch_hash

Description

Returns the row under the cursor as a hash of column data. Once fetched the cursor is incremented by one, that is pointed to the next row. The row is represented as an array.

Syntax

fetch_hash() ? hash or nil 

Return value

  • hash : a hash representing the row with columns.
  • nil: if there is no row to be fetched, the method returns nil.

Example

con = Cubrid.connect('demodb')
con.prepare('SELECT * FROM db_user') { |stmt|
   stmt.execute
   r = stmt.fetch_hash
   print r['name']
}
con.close

See also

comments powered by Disqus
Page info
viewed 11295 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 3 months ago by
View revisions
Share this article