I have one variable with number and text concatenated (e.g. "[79511]Rocket"). If this variable does contains numbers inside the brackets, I must store then (numbers only) in one column of my table. I'm validating this with the below code:
enterprise_id = "[79511]Rocket".split(/[\[\]]/x)[1].match(/^(\d)+$/) rescue nil
When I test in with Puts, it works as 79511, fine. But then when I run the code to insert into database like below:
enterprise_id = "[79511]Rocket".split(/[\[\]]/x)[1].match(/^(\d)+$/) rescue nil
insert_ds = DB["INSERT INTO pd_deals ( enterprise_id ) VALUES (?)", enterprise_id]
insert_ds.insert
The target column is Integer type in table.
It throws an error:
/home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:1252:in `literal_other_append': can't express #<MatchData "79511" 1:"1"> as a SQL literal (Sequel::Error)
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:108:in `literal_append'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:673:in `block in placeholder_literal_string_sql_append'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:670:in `loop'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:670:in `placeholder_literal_string_sql_append'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/sql.rb:109:in `to_s_append'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:1214:in `literal_expression_append'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:86:in `literal_append'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:345:in `literal'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:1534:in `static_sql'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/sql.rb:23:in `insert_sql'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/dataset/actions.rb:334:in `insert'
from /home/bm93/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.34.0/lib/sequel/adapters/shared/postgres.rb:1355:in `insert'
from tt.rb:12:in `<main>'
I'm doing this way because I need to extract a number from a string. Any hint there? Is there any way better?
If you execute:
"[79511]Rocket".split(/[\[\]]/x)[1].match(/^(\d)+$/)
in irb, you'll see:
=> #<MatchData "79511" 1:"1">
because String#match returns a MatchData instance and calling inspect on a MatchData gives you #<MatchData ...>. If you puts that value:
puts "[79511]Rocket".split(/[\[\]]/x)[1].match(/^(\d)+$/)
you'll see 79511 because puts calls to_s on its arguments to convert them to strings.
Sequel won't call to_s, it will try to figure out how to translate the argument to something the database understands on its own and it doesn't know what to do with a MatchData instance, hence the:
can't express #<MatchData "79511" 1:"1"> as a SQL literal
error.
You could call to_s yourself:
DB["INSERT INTO pd_deals ( enterprise_id ) VALUES (?)", enterprise_id.to_s.presence]
or use the MatchData#[] method:
DB["...", enterprise_id ? enterise_id[0] : nil]
or even use String#[] as Cary Swoveland suggests:
enterprise_id = "[79511]Rocket"[/(?<=\[)\d+(?=\])/]
DB["...", enterprise_id.presence]
I'm not that familiar with Sequel so you might need some to_i calls in there to convert the '79511' strings to 79511 numbers.