SQLite3::SQLException: no such function: get_zy_string

Ruby
1

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

2

#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

3

Greg H. [email protected] 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 !