# SQLite and ruby

**URL:** <https://www.ruby-forum.com/t/sqlite-and-ruby/116986>\
**Category:** Ruby\
**Created:** [October 11, 2007, 9:36pm UTC](https://www.ruby-forum.com/t/sqlite-and-ruby/116986 "2007-10-11T21:36:18Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Une\_BSSSSvS\_ue](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/u/2acd7d/32.png) [@Une\_BSSSSvS\_ue](https://www.ruby-forum.com/u/Une_BSSSSvS_ue)\
**Post date:** [October 11, 2007, 9:36pm UTC](https://www.ruby-forum.com/t/sqlite-and-ruby/116986/1 "2007-10-11T21:36:18Z")

</div>

i need to convert three fields of a SQLite db :

lastName, firstName, fullLastName

the conversion is a capitalisation ie :

dupond, jules, dupondjules

needs to become :

Dupond, Jules, DupondJules

i now how to capitalize with ruby but i’ve never use ruby with SQLite.

what should i install, as a gem for example, in order to play with such  
a database ?

---

<div class="post-metadata">

**Author:** ![Une\_BSSSSvS\_ue](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/u/2acd7d/32.png) [@Une\_BSSSSvS\_ue](https://www.ruby-forum.com/u/Une_BSSSSvS_ue)\
**Post date:** [October 11, 2007, 10:24pm UTC](https://www.ruby-forum.com/t/sqlite-and-ruby/116986/2 "2007-10-11T22:24:21Z")

</div>

> what should i install, as a gem for example, in order to play with such  
> a database ?

If you have the sqlite3 libs installed, you can install the sqlite3  
gem:

sudo gem install sqlite3-ruby

May I also suggest Sequel for a nice & simple API for ‘playing’ with  
the database:

sudo gem install sequel

Sequel lets you write stuff like this:

require ‘sequel/sqlite’

# Open the database

DB = Sequel.sqlite(‘mydb.sqlite’)

# Assuming the table name is ‘people’

class Person \< Sequel::Model(:people)  
def capitalize  
p[:lastName].capitalize!  
p[:firstName].capitalize!  
p[:fullName].capitalize!  
save  
end  
end

Person.each {|p| p.capitalize}

You can find out more about Sequel here:

[Google Code Archive - Long-term storage for Google Code Project Hosting.](http://code.google.com/p/ruby-sequel/)

The API is here:

[http://sequel.rubyforge.org/](http://sequel.rubyforge.org/)

best  
Sharon

---

<div class="post-metadata">

**Author:** ![Une\_BSSSSvS\_ue](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/u/2acd7d/32.png) [@Une\_BSSSSvS\_ue](https://www.ruby-forum.com/u/Une_BSSSSvS_ue)\
**Post date:** [October 11, 2007, 11:15pm UTC](https://www.ruby-forum.com/t/sqlite-and-ruby/116986/3 "2007-10-11T23:15:54Z")

</div>

Une Bévue [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> i now how to capitalize with ruby but i’ve never use ruby with SQLite.

After installing sqlite3-ruby by gem i’m trying :

#! /usr/bin/env ruby

require ‘sqlite3’

db = SQLite3::Database.new( “backpb.db” )  
db.execute( “select distinct UID, lastName, firstName, fullLastName from  
devicephonebook” ) do | row |  
p row  
db.execute( “UPDATE devicephonebook SET lastName =  
‘#{row[1].capitalize}’, firstName = ‘#{row[2].capitalize}’, fullLastName  
= ‘#{row[1].capitalize}#{row[2].capitalize}’ WHERE UID = #{row[0]}”)  
end

where i get the following error :

SQLite3::SQLException: no such function: get\_zy\_string  
method check  
in errors.rb at line 94  
method initialize  
in statement.rb at line 71  
method new  
in database.rb at line 184  
method prepare  
in database.rb at line 184  
method execute  
in database.rb at line 211  
at top level  
in test.rb at line 8  
method execute  
in database.rb at line 214  
method each  
in resultset.rb at line 162  
method execute  
in database.rb at line 214  
method prepare  
in database.rb at line 187  
method execute  
in database.rb at line 211  
at top level  
in test.rb at line 6  
Program exited.

line 6 being db.execute( “select distinct UID, …”)  
line 8 being db.execute( “UPDATE devicephonebook SET …”)

---

<div class="post-metadata">

**Author:** ![Une\_BSSSSvS\_ue](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/u/2acd7d/32.png) [@Une\_BSSSSvS\_ue](https://www.ruby-forum.com/u/Une_BSSSSvS_ue)\
**Post date:** [October 11, 2007, 11:20pm UTC](https://www.ruby-forum.com/t/sqlite-and-ruby/116986/4 "2007-10-11T23:20:35Z")

</div>

Sharon R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> If you have the sqlite3 libs installed, you can install the sqlite3  
> gem:
> 
> sudo gem install sqlite3-ruby

already done, thanks 😉

> May I also suggest Sequel for a nice & simple API for ‘playing’ with  
> the database:
> 
> sudo gem install sequel

[…]

> You can find out more about Sequel here:
> 
> [Google Code Archive - Long-term storage for Google Code Project Hosting.](http://code.google.com/p/ruby-sequel/)
> 
> The API is here:
> 
> [http://sequel.rubyforge.org/](http://sequel.rubyforge.org/)

OK thanks a lot about your advice upon “sequel” i’ll look for.

i’ve already done a first test using sqlite3-ruby only however i got an  
error (see my last post) )))

---

<div class="post-metadata">

**Author:** ![Une\_BSSSSvS\_ue](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/u/2acd7d/32.png) [@Une\_BSSSSvS\_ue](https://www.ruby-forum.com/u/Une_BSSSSvS_ue)\
**Post date:** [October 12, 2007, 1:16am UTC](https://www.ruby-forum.com/t/sqlite-and-ruby/116986/5 "2007-10-12T01:16:26Z")

</div>

Une Bévue [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> ```
> else
> return self.capitalize
> end
> 
> ```
> 
> end  
> end

OOOPPPSSS !!!

this has to be read as :

PARTICULES = [“de”, “di”, “von”, “van”]

class String  
def name\_capitalize  
if self.include?(“-”)  
l = self.split(“-”)  
lo = []  
l.each { |ll| lo \<\< ll.capitalize }  
return lo.join(“-”)  
elsif self.include?(" “)  
l = self.split(” “)  
lo = []  
l.each { |ll|  
if PARTICULES.include? ll  
lo \<\< ll  
else  
lo \<\< ll.capitalize  
end  
}  
return lo.join(” ")  
else  
return self.capitalize  
end  
end  
end

---

<div class="post-metadata">

**Author:** ![Une\_BSSSSvS\_ue](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/u/2acd7d/32.png) [@Une\_BSSSSvS\_ue](https://www.ruby-forum.com/u/Une_BSSSSvS_ue)\
**Post date:** [October 12, 2007, 1:01am UTC](https://www.ruby-forum.com/t/sqlite-and-ruby/116986/6 "2007-10-12T01:01:14Z")

</div>

Sharon R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> ```
> p[:lastName].capitalize!
> p[:firstName].capitalize!
> p[:fullName].capitalize!
> save
> end
> 
> ```
> 
> end

I had to re-arrange your script the following way :

class Person \< Sequel::Model(:devicephonebook)  
def capitalize  
@values[:lastName] = @values[:lastName].name\_capitalize  
@values[:firstName] = @values[:firstName].name\_capitalize  
@values[:fullLastName] = @values[:lastName] + @values[:firstName]  
p "[" + @values[:lastName] + ", " + @values[:firstName] + “, " +  
@values[:fullLastName] + " ]”  
#save  
end  
end

i can’t use String#capitalize! for three reasons :

- 1 - if a name is already capitalize String#capitalize! returns nil ;
- 2 - “aristocratic” people having name like that :

di Girolamo  
Pavin de Lafarge  
von Beethoven  
van Houthen

or composite name :

- 3 - Harley-Davidson

then i wrote :

class String  
def name\_capitalize  
if self.include?(“-”)  
l = self.split(“-”)  
l.each { |ll| ll = ll.capitalize }  
return l.join(“-”)  
elsif self.include?(" “)  
l = self.split(” “)  
l.each { |ll| ll = ll.capitalize if !TRUC.include? ll }  
return l.join(” ")  
else  
return self.capitalize  
end  
end  
end

everything works well until i’ve uncomented “#save” where i get the same  
error as another script not using Sequel :

RuntimeError: no such function: get\_zy\_string  
method hold  
in connection\_pool.rb at line 68  
method execute\_insert  
in sqlite.rb at line 42  
method insert  
in sqlite.rb at line 121  
method save  
in record.rb at line 82  
method capitalize  
in test-sequel.rb at line 38  
at top level  
in test-sequel.rb at line 42  
method []  
in dataset.rb at line 330  
method each  
in dataset.rb at line 330  
method call  
in sqlite.rb at line 115  
method fetch\_rows  
in sqlite.rb at line 115  
method each  
in resultset.rb at line 162  
method fetch\_rows  
in sqlite.rb at line 112  
method query  
in database.rb at line 278  
method execute\_select  
in sqlite.rb at line 52  
method hold  
in connection\_pool.rb at line 62  
method execute\_select  
in sqlite.rb at line 52  
method fetch\_rows  
in sqlite.rb at line 109  
method each  
in dataset.rb at line 330  
method each  
in untitled document at line 1  
method send  
in model.rb at line 60  
method method\_missing  
in model.rb at line 60  
at top level  
in test-sequel.rb at line 42  
Program exited.

This appeared AFTER the first p in Person.each {|p| p.capitalize } ???

may be better to ask to the Sequel google-list but the prob does exist  
in sqlite3-ruby…