How to run raw SQL queries with Sequel
I have a few pointers which may be useful:
You could simply do:
@zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).first
NB: you are ignoring the fact that there could be more results matching the criteria. If you expect multiple possible rows to be returned then you probably want to build an array of results by doing ...
@zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).all
and processing all of them.
The return set is a hash. If
@zonename
points to one of the records then you can do@zonename[:column_name]
to refer to a field called "column_name". You can't do @zonename.column_name
(you could actually decorate @zonename
with helper methods using some meta-programming but let's ignore that for the moment).
Sequel is an excellent interface, the more you learn about it the more you'll like it.
How to put a raw SQL query in Sequel
You can do it a couple ways:
Use
[]
:DB["your sql string"]
Use
fetch
:DB.fetch("your sql string")
Is there a way to see the raw SQL that a Sequel expression will generate?
You can call sql
on dataset:
db.select(:id).from(:some_table).where(:foo => 5).sql # => "SELECT `id` FROM `some_table` WHERE (`foo` = 5)"
For update queries you can do this:
db.from(:some_table).update_sql(:foo => 5) # => "UPDATE `some_table` SET `foo` = 5"
Some similar useful methods:
insert_sql
delete_sql
truncate_sql
How to sanitize raw SQL in a Ruby script
I don't know Sequel, but did you try standard insert
method?
connection = Sequel.connect('...')
table_name = connection.from(:table_name)
# OR
# table_name = DB.from(:table_name)
# table_name = DB[:table_name]
table_name.insert(csv_row.to_h)
It's more reliable I believe, because you avoid difference between COLUMN_NAMES
and record_values
.
How do you execute a query in Sequel Pro?
Use ⌘+R to execute the selected Query.
Alternatively, use the dropdown that appears at the bottom right of the query editor and select Run Current
or Run Previous
depending on where your text cursor is.
How do I make a raw sql query in EF Core 2.2?
Thank you, Ivan Stoev. The solution was to use a LINQ "Select":
var myList = _context.MyTable.Select(e => e.MyColumn).Distinct().ToList();
This worked like a charm ... and it does NOT require any external dependencies.
How can I use a Sequel SQL function in an insert statement?
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))"
Rails raw SQL example
You can do this:
sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)
records_array
would then be the result of your sql query in an array which you can iterate through.
Related Topics
How to Print a Line Number in Ruby
How to Convert a Formatted String into Plain Text
What Ruby Features Are Used in Chef Recipes
Net::Ssh with Non Unix/Linux Host
Rspec - How to Test Activerecord::Recordnotfound
If Referer = "/Search", Do X, Else Do Y: "Bad Argument (Expected Uri Object or Uri String)"
Ruby: Sorting 2 Arrays Using Values from One of Them
Ruby: How to Remove Items from Array a If It's Not in Array B
Pageobject with Ruby - Set Text in a Text Field Only Works in the Main File
Custom Gem Execution Fails with Nomethoderror
Importing CSV as Test Data in Cucumber
Rails 3 Translations Within Models in Production
Error When Pushing to Heroku - ...Appear in Group - Ruby on Rails
Error: Missing Rvm Environment File After Doing Rvm Upgrade Command - Passenger 4.0.23
List of All/Best Gems for Ruby