Find_by_sql without a table

[raimon]

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]

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

[raimon]

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

[raimon]

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]

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]

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

[raimon]

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]

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?

Eric

[raimon]

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?

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