CUBRID Ruby API Documentation

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 2.2. 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

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.61-x86-mingw32
1 gem installed
Installing ri documentation for cubrid-0.61-x86-mingw32...
Installing RDoc documentation for cubrid-0.61-x86-mingw32...

At this point you should see the message about the successful installation of cubrid-0.61-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 '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 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

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

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.rollback

Return value

• nil

glo_new

Description

This method is used to create a new GLO object. The glo_new method will return the OID of the newly created object. When glo_new is called CUBRID provides GLO which will store the binary data. Later GLO object can be directly accessed by OID.

Syntax

glo_new(classname <, filename>) ? OID 

Return value

• OID:

Example

The below example illustrates how to use glo_new method. When called, the data inside the file, whose filename has been passed as a parameter, will be stored in the database. If the filename is not indicated, the method will create an empty GLO object.

con = Cubrid.connect('subway')
con.query('create table attachfile under glo (name string)')
con.commit
 
glo = con.glo_new('attachfile', 'pic.jpg')
glo.glo_size  #=> 1234
 
glo = con.glo_new('attachfile', '')
glo.glo_size  #=> 0
glo.glo_save('pic.jpg')
glo.glo_size  #=> 1234

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

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

con = Cubrid.connect('demodb')
stmt = con.query('SELECT * FROM db_user')
 
while row = stmt.fetch
   print row
end
 
stmt.close
con.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

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

con = Cubrid.connect('demodb')
stmt = con.prepare('SELECT * FROM db_user')
 
stmt.execute
r = stmt.fetch
 
stmt.close
con.close

con.prepare('SELECT * FROM db_user') { |stmt|
   stmt.execute
   r = stmt.fetch
}
con.close

to_s

Description

This method is used to get the current connection information.

Example

# same as
# puts @con
connection_info = @con.to_s

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

version = @con.server_version

Return values

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

Statement Class

Data Types

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

affected_rows

Description

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

Example

stmt = @con.query("UPDATE cities SET name = 'Paris' WHERE id = 4")
@con.commit
num = stmt.affected_rows

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('demodb')
con.prepare('SELECT * FROM db_user') { |stmt|
   stmt.execute
   stmt.column_info.each { |col|
     print col['name']
     print col['type_name']
     print col['precision']
     print col['scale']
     print col['nullable']
   }
}
con.close

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.bing(1, value1)
stmt.bing(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

get_oid

Description

In case a statement has been prepared with Cubrid::INCLUDE_OID option, the get_oid method will return the Oid of the created GLO object.

Syntax

get_oid() ? Oid 

Return value

• Oid: the OID of the newly created GLO object.

Example

con = Cubrid.connect('demodb')
con.prepare('SELECT * FROM db_user', Cubrid::INCLUDE_OID) { |stmt|
   stmt.execute
   stmt.fetch
   oid = stmt.get_oid
   print oid.table
}
con.close

Oid Class

[](col_name)

Description

Returns the column data of the OID.

Syntax

[](col_name) ? obj 

Return value

• obj: the column data of the OID.

Example

con = Cubrid.connect('demodb')
stmt = con.prepare('SELECT * FROM db_user', Cubrid::INCLUDE_OID)
stmt.execute
stmt.fetch
oid = stmt.get_oid
print oid['name']
print oid.name
stmt.close
con.close

Another way to access the data in the column is to call the name as a method.

oid = stmt.get_oid
print oid.name

[]=(col_name, obj)

Description

Stores the data to the OID column. In order to reflect the saved data in the database server save method should be called afterwards.

Syntax

[]=(col_name, obj) ? nil 

Return value

• obj: the column data of the OID.

Example

con = Cubrid.connect('demodb')
stmt = con.prepare('SELECT * FROM db_user', Cubrid::INCLUDE_OID)
stmt.execute
stmt.fetch
oid = stmt.get_oid
oid['name'] = 'foo'
oid.save
stmt.close
con.close

It is also possible to store and save the data using the corresponding name and save methods for the column.

oid = stmt.get_oid
oid.name = 'foo'
oid.save

drop

Description

Removes the OID from the database server.

Syntax

drop() ? self

Return value

• self

each

Description

Passes the column name and its data to the following block.

Syntax

each() { |name, val|
  ...
} ? nil

Return value

• nil

Example

con = Cubrid.connect('demodb')
stmt = con.prepare('SELECT * FROM db_user', Cubrid::INCLUDE_OID)
stmt.execute
stmt.fetch
oid = stmt.get_oid
oid.each { |name, val|
   print name
   print val
}

glo_drop

Description

Deletes the GLO object from the database server.

Syntax

glo_drop() ? self

Return value

• self

Example

con = Cubrid.connect('demodb')
stmt = con.prepare("SELECT * FROM attachfile WHERE name = 'pig.jpg'", Cubrid::INCLUDE_OID)
stmt.execute
stmt.fetch
oid = stmt.get_oid
oid.drop

glo_load

Description

Save the GLO object, which is stored the database server, to the file.

Syntax

glo_load(filename) ? File

Parameter

• filename : the name of the file where the GLO object will be saved.

Return value

• File: The file containing the GLO object.

Example

con = Cubrid.connect('subway')
con.query('create table attachfile under glo (name string)')
con.commit
 
glo = con.glo_new('attachfile', 'pic.jpg')
newfile = glo.glo_load('pic_copy.jpg')

glo_save

Description

Save the data of the file as a GLO object to the database server.

Syntax

glo_save(filename) ? self 

Parameter

• filename : the name of the file to be saved to the database server.

Return value

• self

Example

con = Cubrid.connect('subway')
con.query('create table attachfile under glo (name string)')
con.commit
 
glo = con.glo_new('attachfile')
glo.glo_save('pic.jpg')

glo_size

Description

Returns the size of the stored GLO object.

Syntax

glo_size() ? int

Return value

• int: the size of the stored GLO object

lock

Description

Sets the Cubrid::READ_LOCK or Cubrid::WRITE_LOCK to the OID.

Syntax

lock(lockmode) ? self

Parameter

• lockmode : the mode of the lock. Can be either Cubrid::READ_LOCK or Cubrid::WRITE_LOCK.

Return value

• self

refresh

Description

Reads the OID data from the database server. The column data of the OID is not automatically synchronized with the database server. In case much time has been elapsed since the Oid object has been created, the refresh method has to be called to read the data from the database server.

Syntax

refresh() ? self

Return value

• self

Example

con = Cubrid.connect('demodb')
stmt = con.prepare('SELECT * FROM db_user', Cubrid::INCLUDE_OID)
stmt.execute
stmt.fetch
oid = stmt.get_oid
print oid['name']
 
#after some time
oid.refresh
print oid['name']
stmt.close
con.close

save

Description

Saves the data of OID to the database server.

Syntax

save() ? self

Return value

• self

table

Description

Returns the table name of the OID.

Syntax

table() ? string

Return value

• string : the table name of the OID

to_hash

Description

Returns the OID data in a hash.

Syntax

to_hash() ? Hash

Return value

• Hash : The OID data. The key will indicate on its name, and the value - its data.

to_s

Description

Returns the OID string.

Syntax

to_s() ? string

Return value

• string : the OID string.

See also

• Ruby Programming with CUBRID
• CUBRID API References