Find_by_sql without a table

hello,

I’m trying to implement an experimental sql command.

I have a page, with a text field inside a form, and a button that sends
this via AJAX to a controller.

In this controller, I send this string inside a
find_by_sql(params[:expert_command])

This is ok if I only want to send SQL commands for just one table, but I
want to freely send any valid SQL.

For what I see, I can send @result =
Master.find_by_sql(params[:expert_command]) and the sql is SELECT * FROM
dades limit 5, as you can see, I’m sending an SQL that affects another
table, the one that’s no called via the .find_by_sql.

Well, in the debug I have:

  • !ruby/object:Master
    attributes:
    reporter: REPORTER
    partner: PARTNER
    period: PERIOD
    product: PRODUCT
    id: “1”
    indicator_value: INDICATOR_VALUE
    flow: FLOW
    indicators: INDICATORS
  • !ruby/object:Master
    attributes:
    reporter: EU27
    partner: EU27_INTRA
    period: “199952”
    product: “89011010”
    id: “2”
    indicator_value: “70417621”
    flow: “1”
    indicators: VALUE_IN_EUR

so, it says is a !ruby/object:Master, when really it’s from the table
‘dades’, and the attributes are really from the ‘dades’ table.

so, it seems it’s working fine, no matter from wich table I send the
.find_by_sql.

there’s a better way to do this ?

and, as I never know wich command is going to run, an easy way to
retrieve the attribute just one, for creating the headers, and then only
the values for creating the rows, and put them in a table ?

every value of @result will have the same attributes …

I can do the loop, and that’s all …

I also tried with: <%= @result.each {|key, value| puts “#{key} is
#{value}” } %>

but I’m getting only #

as always, thanks for your suggestions …

regards,

rai

Raimon Fs wrote:

I also tried with: <%= @result.each {|key, value| puts “#{key} is
#{value}” } %>

but I’m getting only #

as always, thanks for your suggestions …

regards,

rai

instead of using find_by_sql for complex queries, you can instead use:
c = ActiveRecord::Base.connection
results = c.execute(“Bad ass query here”)

if you are using mysql then you can do this for even easier handling
results = c.execute(“as above”).all_hashes

hth

ilan

Ilan B. wrote:

instead of using find_by_sql for complex queries, you can instead use:
c = ActiveRecord::Base.connection
results = c.execute(“Bad ass query here”)

if you are using mysql then you can do this for even easier handling
results = c.execute(“as above”).all_hashes

thanks Ilan, this is perfect.

with this project I’m using SQLite, and I see I’m receiving a hash also.

I’m going to try to get the attributes just once for creating the labels
of the table, and then extract only the values.

thanks again,

rai

Stephan W. wrote:

Raimon Fs wrote:

hello,

I’m trying to implement an experimental sql command.

I have a page, with a text field inside a form, and a button that sends
this via AJAX to a controller.

Do you mean text-area not text-field?

In this controller, I send this string inside a
find_by_sql(params[:expert_command])

One possibility,

Define a model class

class ExpertSQLResult < ActiveRecord::Base
end

Obtain sql results in your controller like this.

ExpertSQLResult.find_by_sql(params[:expert_command])

Would that be “good enough”?

Stephan

thanks Stephan,

the solution from Ilan is what I was looking for …

rai

Raimon Fs wrote:

hello,

I’m trying to implement an experimental sql command.

I have a page, with a text field inside a form, and a button that sends
this via AJAX to a controller.

Do you mean text-area not text-field?

In this controller, I send this string inside a
find_by_sql(params[:expert_command])

One possibility,

Define a model class

class ExpertSQLResult < ActiveRecord::Base
end

Obtain sql results in your controller like this.

ExpertSQLResult.find_by_sql(params[:expert_command])

Would that be “good enough”?

Stephan

Raimon Fs wrote:

Stephan W. wrote:

Raimon Fs wrote:

hello,

I’m trying to implement an experimental sql command.

I have a page, with a text field inside a form, and a button that sends
this via AJAX to a controller.

Do you mean text-area not text-field?

In this controller, I send this string inside a
find_by_sql(params[:expert_command])

One possibility,

Define a model class

class ExpertSQLResult < ActiveRecord::Base
end

Obtain sql results in your controller like this.

ExpertSQLResult.find_by_sql(params[:expert_command])

Would that be “good enough”?

Stephan

thanks Stephan,

the solution from Ilan is what I was looking for …

That execute doesn’t do it for me with SQLServer; I get, for example,

ActiveRecord::Base.connection(‘select * from schema_info’)
=> nil

ActiveRecord::Base.connection(‘select * from dbo.schema_info’)
=> nil

With mysql I can see it working.

execute doesn’t sound like selecting data. Also , sending an “update”
statement to a find_by_sql will raise an exception, not so with execute.

Stephan

Stephan W. wrote:

That execute doesn’t do it for me with SQLServer; I get, for example,

ActiveRecord::Base.connection(‘select * from schema_info’)
=> nil

ActiveRecord::Base.connection(‘select * from dbo.schema_info’)
=> nil

With mysql I can see it working.

execute doesn’t sound like selecting data. Also , sending an “update”
statement to a find_by_sql will raise an exception, not so with execute.

ok, thanks for the extra info.

at this moment is working for me, and it’s just an experiment, the
selects works perfect with the execute, also the select * from
schema_info, I didn’t tried yet any update/delete, but they should work
also.

rai

Raimon Fs wrote:

In this controller, I send this string inside a
find_by_sql(params[:expert_command])

I assume you are processing this SQL to avoid SQL Injection attacks
right? :slight_smile:

Eric

Eric A. wrote:

Raimon Fs wrote:

In this controller, I send this string inside a
find_by_sql(params[:expert_command])

I assume you are processing this SQL to avoid SQL Injection attacks
right? :slight_smile:

Eric

well, this is an experimental project for my sister and myself, no plans
to give access outside of my net, and there’s no private data, only a
'small database with 2.000.000 rows, so at this moment I’m not worried
about sql injection attacks, but good observation …

thanks!

rai

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs