# SQLite3::SQLException: no such function: get\_zy\_string

**URL:** <https://www.ruby-forum.com/t/sqlite3-no-such-function-get-zy-string/117035>\
**Category:** Ruby\
**Created:** [October 12, 2007, 10:00am UTC](https://www.ruby-forum.com/t/sqlite3-no-such-function-get-zy-string/117035 "2007-10-12T10:00:41Z")\
**Posts on this page:** 3\
**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 12, 2007, 10:00am UTC](https://www.ruby-forum.com/t/sqlite3-no-such-function-get-zy-string/117035/1 "2007-10-12T10:00:41Z")

</div>

with this script :

require ‘sqlite3’

db = SQLite3::Database.new( “backpb.db” )  
rows = db.execute( “select distinct UID, lastName, firstName,  
fullLastName from devicephonebook” )

rows.each { | row |  
db.execute( “UPDATE devicephonebook SET lastName =  
‘#{row[1].name\_capitalize}’, firstName = ‘#{row[2].name\_capitalize}’,  
fullLastName = ‘#{row[1].name\_capitalize}#{row[2].name\_capitalize}’  
WHERE UID = #{row[0]}”)  
}

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 35  
method each  
in test.rb at line 34  
at top level  
in test.rb at line 34

line 35 being "db.execute( “UPDATE devicephonebook SET …” )

I don’t understand this error message…

For info, here is my String#name\_capitalize :

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

doing such capitalizations :

pavin de lafarge -o-\> Pavin de Lafarge  
marie-claude -o-\> Marie-Claude  
von beethoven -o-\> von Beethoven  
Smith -o-\> Smith

---

<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, 5:04pm UTC](https://www.ruby-forum.com/t/sqlite3-no-such-function-get-zy-string/117035/2 "2007-10-12T17:04:14Z")

</div>

#sql statements need to terminate with a ;

require ‘rubygems’  
require ‘sqlite3’

db = SQLite3::Database.new(‘backpb.s3db’)

db.execute(“SELECT UID, lastName, firstName, fullLastName FROM  
devicephonebook;”) do |row|  
p row  
#executing an an update doesn’t work in the block. seems to mess up the  
result #set with the new execute command???

end

workaround=db.execute(“SELECT UID, lastName, firstName, fullLastName  
FROM devicephonebook;”)

workaround.each do |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

p ‘–records-after-update–’  
p db.execute(“SELECT UID, lastName, firstName, fullLastName FROM  
devicephonebook;”)

Here is the output:

> ruby sqlite3\_replace.rb  
> [“1”, “ronald”, “mcdonnald”, “ronaldmcdonnald”]  
> [“2”, “Andy”, “Mcdonnald”, “andymcdonnald”]  
> “–update-records–”  
> “–records-after-update–”  
> [[“1”, “Ronald”, “Mcdonnald”, “RonaldMcdonnald”], [“2”, “Andy”,  
> “Mcdonnald”, “AndyMcdonnald”]]  
> Exit code: 0

---

<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, 6:31pm UTC](https://www.ruby-forum.com/t/sqlite3-no-such-function-get-zy-string/117035/3 "2007-10-12T18:31:00Z")

</div>

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

> #sql statements need to terminate with a ;

yes, i know, but i forgot…

> p ‘–records-after-update–’  
> p db.execute(“SELECT UID, lastName, firstName, fullLastName FROM  
> devicephonebook;”)

I’ve tried your workaround over the original and complex database, i get  
the same prb.

Even my own script works well over a simplified database having this  
shema :

BEGIN TRANSACTION;  
CREATE TABLE devicephonebook( UID INTEGER PRIMARY KEY AUTOINCREMENT  
,lastName TEXT NOT NULL DEFAULT(‘’),firstName TEXT NOT NULL  
DEFAULT(‘’),fullLastName TEXT );  
COMMIT;

the original database (i can’t change it because it comes from my mobile  
phone) :

BEGIN TRANSACTION;  
CREATE TABLE devicephonebook( UID INTEGER PRIMARY KEY AUTOINCREMENT  
,lastName TEXT NOT NULL DEFAULT(‘’),firstName TEXT NOT NULL  
DEFAULT(‘’),phoneType0 INTEGER NOT NULL DEFAULT(0) CHECK(phoneType0\>=0  
and phoneType0\<=5) ,phoneNumber0 TEXT NOT NULL DEFAULT(‘’) ,phoneType1  
INTEGER NOT NULL DEFAULT(1) CHECK(phoneType1\>=0 and phoneType1\<=5)  
,phoneNumber1 TEXT NOT NULL DEFAULT(‘’) ,phoneType2 INTEGER NOT NULL  
DEFAULT(2) CHECK(phoneType2\>=0 and phoneType2\<=5) ,phoneNumber2 TEXT NOT  
NULL DEFAULT(‘’) ,phoneType3 INTEGER NOT NULL DEFAULT(3)  
CHECK(phoneType3\>=0 and phoneType3\<=5) ,phoneNumber3 TEXT NOT NULL  
DEFAULT(‘’) ,phoneType4 INTEGER NOT NULL DEFAULT(4) CHECK(phoneType4\>=0  
and phoneType4\<=5) ,phoneNumber4 TEXT NOT NULL DEFAULT(‘’) ,EMailAddr0  
TEXT NOT NULL DEFAULT(‘’) ,EMailAddr1 TEXT NOT NULL DEFAULT(‘’)  
,phoneCount INTEGER NOT NULL DEFAULT(0) ,mobileCount INTEGER NOT NULL  
DEFAULT(0) ,mailCount INTEGER NOT NULL DEFAULT(0) ,lastChar INTEGER  
,fullLastName TEXT ,ZYFLName TEXT ,PYFLName TEXT ,hasPhoto INTEGER  
DEFAULT(0) ,timeStamp TEXT);  
CREATE INDEX nameIndex ON devicephonebook (PYFLName, lastName,  
firstName, lastChar, hasPhoto);  
CREATE TRIGGER DELETE\_EXT\_TRIGGER AFTER DELETE ON devicephonebook  
BEGIN DELETE FROM extraphonebook WHERE extraphonebook.UID = OLD.UID;  
SELECT delete\_from\_cache(OLD.UID); END;  
CREATE TRIGGER INSERT\_PBK\_TRIGGER AFTER INSERT ON devicephonebook BEGIN  
UPDATE devicephonebook SET phoneCount=(NEW.phoneNumber0!=‘’) +  
(NEW.phoneNumber1!=‘’) + (NEW.phoneNumber2!=‘’) + (NEW.phoneNumber3!=‘’)

- (NEW.phoneNumber4!=‘’) ,mobileCount=(NEW.phoneNumber0!=‘’ and  
NEW.phoneType0=0) + (NEW.phoneNumber1!=‘’ and NEW.phoneType1=0) +  
(NEW.phoneNumber2!=‘’ and NEW.phoneType2=0) + (NEW.phoneNumber3!=‘’ and  
NEW.phoneType3=0) + (NEW.phoneNumber4!=‘’ and NEW.phoneType4=0)  
,mailCount=(NEW.EMailAddr0!=‘’) + (NEW.EMailAddr1!=‘’)  
,fullLastName=NEW.lastName||NEW.firstName  
,ZYFLName=get\_zy\_string(NEW.lastName||NEW.firstName)  
,PYFLName=get\_py\_string(NEW.lastName|| NEW.firstName)  
,lastChar=get\_first\_char(NEW.lastName|| NEW.firstName) WHERE  
UID=NEW.UID; END;  
CREATE TRIGGER UPDATE\_NAMES\_TRIGGER AFTER UPDATE OF lastName, firstName  
ON devicephonebook BEGIN UPDATE devicephonebook SET fullLastName =  
(SELECT lastName||firstName FROM devicephonebook WHERE UID=NEW.UID)  
,ZYFLName=(SELECT get\_zy\_string(lastName||firstName) FROM  
devicephonebook WHERE UID=NEW.UID) ,PYFLName=(SELECT  
get\_py\_string(lastName||firstName) FROM devicephonebook WHERE  
UID=NEW.UID) ,lastChar=(SELECT get\_first\_char(lastName||firstName) FROM  
devicephonebook WHERE UID=NEW.UID) WHERE UID=NEW.UID; END;  
CREATE TRIGGER UPDATE\_PHONECOUNT\_TRIGGER AFTER UPDATE OF phoneType0,  
phoneNumber0, phoneType1, phoneNumber1, phoneType2, phoneNumber2,  
phoneType3, phoneNumber3, phoneType4, phoneNumber4 ON devicePhonebook  
BEGIN UPDATE devicephonebook SET phoneCount=(SELECT (phoneNumber0!=‘’)
- (phoneNumber1!=‘’) + (phoneNumber2!=‘’) + (phoneNumber3!=‘’) +  
(phoneNumber4!=‘’) FROM devicephonebook WHERE UID=NEW.UID),  
mobileCount=(SELECT (phoneNumber0!=‘’ and phoneType0=0) +  
(phoneNumber1!=‘’ and phoneType1=0) + (phoneNumber2!=‘’ and  
phoneType2=0) + (phoneNumber3!=‘’ and phoneType3=0) + (phoneNumber4!=‘’  
and phoneType4=0) FROM devicephonebook WHERE UID=NEW.UID) WHERE  
UID=NEW.UID; END;  
CREATE TRIGGER UPDATE\_MAILCOUNT\_TRIGGER AFTER UPDATE OF EMailAddr0,  
EMailAddr1 ON devicephonebook BEGIN UPDATE DEVICEPHONEBOOK SET  
mailCount=(SELECT (EMailAddr0!=‘’) + (EMailAddr1!=‘’) FROM  
DEVICEPHONEBOOK WHERE UID=NEW.UID) WHERE UID=NEW.UID; END;  
COMMIT;

anyway thanks a lot i’ll apply your workaround !