如何使用Sequel运行原始SQL查询

I am not clear yet on the proper way to run raw SQL queries with Sequel.

我还不清楚如何正确地使用Sequel来运行原始SQL查询。

Currently I am trying this:

目前我正在尝试:

DB.fetch("SELECT * FROM zone WHERE dialcode = '#{@dialcode}' LIMIT 1") do |row|
 @zonename = row
end

How can I can run the queries as raw SQL then access the results like normal?

如何才能以原始SQL的形式运行查询，然后像往常一样访问结果?

if @zonename.name = "UK"

2 个解决方案

#1

I have a few pointers which may be useful:

我有一些有用的建议:

1. 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 ...

   NB:你忽略了一个事实，那就是可能有更多符合标准的结果。如果您希望返回多个可能的行，那么您可能希望通过以下操作来构建一个结果数组。

   @zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).all
   

   and processing all of them.

   并对它们进行处理。

2. The return set is a hash. If @zonename points to one of the records then you can do

   返回集是一个散列。如果@zonename指向一条记录，那么您可以这样做

   @zonename[:column_name] 
   

   to refer to a field called "column_name". You can't do @zonename.colum_nname (you could actually decorate @zonename with helper methods using some meta-programming but let's ignore that for the moment).

   引用一个名为“column_name”的字段。你不能做@zonename。colum_nname(您可以使用一些元编程使用helper方法来修饰@zonename，但是我们暂时先忽略它)。

Sequel is an excellent interface, the more you learn about it the more you'll like it.

Sequel是一个优秀的界面，你了解的越多你就越喜欢它。

#2

Note that instead of:

请注意,而不是:

DB.fetch("SELECT * FROM zone WHERE dialcode = '#{@dialcode}' LIMIT 1")

you should do:

你应该做的是:

DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode)

Otherwise, you open yourself to SQL injection if you don't control the contents of @dialcode.

否则，如果不控制@dialcode的内容，您就会接受SQL注入。