2

I would like to use a SQL function when inserting a new value into a dataset. However, I keep getting TypeError: can't convert Sequel::SQL::Function into String errors. The Sequel docs explain how to select using functions, but not insert.

Ideally I would like to avoid DB.run since I am inserting dynamically and raw SQL is messy and inflexible.

This is what I'm trying to do:

INSERT INTO dataset (col1, col2, col3) VALUES ('something', 3, func(value))

This is what I have in Sequel:

x = 'something'
y = 3
func_value = Sequel.function(:func, value)
DB[:dataset].insert (:col1 => x, :col2 => y, :col3 => func_value)

Is this possible? If so, what am I missing?

Jillian Foley
  • 373
  • 3
  • 7
  • I don't know sequel, but I suggest trying to isolate the problem. For me, step 1 would be to output func_value to see what it is. – Dan Bracuk Mar 14 '13 at 16:18
  • func_value is an instance of Sequel::SQL::Function. I guess my question boils down on what I can do to a Sequel::SQL::Function instance to pass it into a Sequel insert method. – Jillian Foley Mar 14 '13 at 16:46
  • I ended up using a DB.run to pull the appropriate value for the func_value variable, and still using the DB[:dataset].insert syntax for flexibility: `DB.run("select func(#{value});")` If there's another answer, I'd love to see it! – Jillian Foley Mar 15 '13 at 12:35
  • i need to do this as i'm trying to unhex a UUID that I am trying to use as a PK. Got the idea from this post: http://stackoverflow.com/questions/10950202/how-to-store-uuid-as-number – Poul Apr 29 '13 at 18:41

2 Answers2

0

Figured it out. Create a Sequel::function object, and then make it the value in your hash.

irb(main):028:0> tbl
=> #<Sequel::MySQL::Dataset: "SELECT * FROM `foo`.`bar`">
irb(main):029:0> uhex = Sequel.function(:unhex,7)
=> #<Sequel::SQL::Function @args=>[7], @f=>:unhex>
irb(main):030:0>  tbl.insert_sql( {:field_name => uhex })
=> "INSERT INTO `foo`.`bar` (`field_name`) VALUES (unhex(7))"
Poul
  • 3,426
  • 5
  • 37
  • 43
0

Looks like there a few different ways syntactically of calling SQL functions in Sequel: http://sequel.rubyforge.org/rdoc/files/doc/dataset_filtering_rdoc.html

PriceHardman
  • 1,693
  • 1
  • 12
  • 14