SQL Syntax Errors

Rails
1

Hi,

I’m running Rails on Mac OS X 10.4. I’ve installed ruby/gem from
darwinports, and then rails through gem.

I have a recurring problem where I get an SQL syntax error:

Mysql::Error: #42000You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ‘’ at line 1: INSERT INTO quote_items (name,
quote_id, body) VALUES(#Quote:0x248a32c, #Quote:0x2485aac,
#Quote:0x247ff30)

I’ve tried this with both MySQL and SQLite and I get a similar error in
both.

I’ve tried recreating the project by retracing my steps, as well as
reinstalling Ruby on Rails in its entirety.

I imagine this is probably a problem with my specific project.
Basically I am trying to create a sub-record (quote_item) from the super
page. I am using the video with the blog example as a model.

I’m creating my record thusly:
Quote.find(params[:id]).quote_items.create(params[:new_quote_item])

and I know that the record is being sent:
(from the error page)
Parameters: {“commit”=>“Add Item”, “new_quote_item”=>{“name”=>“j”,
“body”=>“jkl;”}, “id”=>“1”}

What could I be doing wrong?

Thanks,

Scott Quiring

2

Scott,

Have you tried using inverted commas around the values you are adding to
the
database?

Tim

----- Original Message -----
From: “Scott Quiring” [email protected]
To: [email protected]
Sent: Tuesday, April 18, 2006 2:16 PM
Subject: [Rails] SQL Syntax Errors

3

Tim,

Where would I do that?

The data is coming from a form. I’ve verified that Rails does indeed
get the string properly.

The problem as I see it is that somewhere in the process of
generating the SQL code, the string is being converted to
#Quote:0x248a32c
or some other number in hex.

Thanks,
Scott

4

Scott,

Try creating your SQL query like this;

query = “INSERT INTO quote_items (name, quote_id, body) VALUES('”
+
#Quote:0x248a32c + “', '” + #Quote:0x2485aac + “', '” +
#Quote:0x247ff30 + “')”

and running that string. I’m guessing that you are getting an error
because
you are trying to execute a query such as

INSERT INTO table (‘a’, ‘b’, ‘c’) VALUES (value1, value2, value3)

instead of

INSERT INTO table (‘a’, ‘b’, ‘c’) VALUES (‘value1’, ‘value2’, ‘value3’)

MySQL needs these apostrophes for table values.

I hope this helps.

Tim

----- Original Message -----
From: “Scott Quiring” [email protected]
To: [email protected]
Sent: Tuesday, April 18, 2006 3:02 PM
Subject: Re: [Rails] SQL Syntax Errors

5

Tim,

I’m not actually trying to insert “#Quote:0x248a32c” into the
database. What I’m trying to do is take some other text (“foo” for
example) from a Web form and insert that into the database. The
problem is that somewhere along the way, “foo” is being converted
into “#Quote:0x248a32c”.

“foo” survives at least until I send it into the ActiveRecord
“create” method.

Thanks for your help.

Scott

6

Scott Quiring wrote:

Tim,

I’m not actually trying to insert “#Quote:0x248a32c” into the
database. What I’m trying to do is take some other text (“foo” for
example) from a Web form and insert that into the database. The
problem is that somewhere along the way, “foo” is being converted
into “#Quote:0x248a32c”.

“foo” survives at least until I send it into the ActiveRecord
“create” method.

Thanks for your help.

Scott

You should post up what your form looks like as well -
‘#Quote:0x2485aac’ is a string representation of an instance of an
object, i.e. you passing the object itself, instead one of the object’s
attributes. I think - can’t tell without the form tho.

7

Here’s what my form looks like:

<%= form_tag :action => “new_quote_item”, :id => @quote %>
<%= text_field “new_quote_item”, “name” %>
<%= text_field “new_quote_item”, “body” %>
<%= submit_tag “Add Item” %>

And it’s getting sent properly into params:
Parameters: {“commit”=>“Add Item”, “new_quote_item”=>{“name”=>“foo”,
“body”=>“bar”}, “id”=>“1”}

Thanks,
Scott

8

Hmmm… that looks right. Let’s have a look at the controller method.

9

Scott Quiring wrote:

I’m creating my record thusly:
Quote.find(params[:id]).quote_items.create(params[:new_quote_item])

and I know that the record is being sent:
(from the error page)
Parameters: {“commit”=>“Add Item”, “new_quote_item”=>{“name”=>“j”,
“body”=>“jkl;”}, “id”=>“1”}

I’d try, for the sake of argument, breaking things down a bit :slight_smile:
def blah
@quote = Quote.find(params[:id])
logger.info( @quote.inspect )

quote_item_params = params[:new_quote_item]
logger.info ( quote_item_params )

@new_quote_item = QuoteItem.create( quote_item_params )
logger.info( @new_quote_item )

@quote.quote_items << @new_quote_item
end

10

On 18-Apr-06, at 7:23 AM, Alan F. wrote:

logger.info( @new_quote_item )

@quote.quote_items << @new_quote_item
end

Okay, this has changed the error message. Now, there’s no data being
sent to MySQL:
Mysql::Error: #42000You … at line 1: INSERT INTO quote_items
(name, quote_id, body) VALUES(, , )

Logged info:

@quote.inspect
#<Quote:0x26d3e80 @attributes={“name”=>“a”, “quote_items_count”=>“0”,
“is_template”=>“0”, “notes”=>“sdf”, “id”=>“1”}>

quote_item_params
namefoobodybar

Mike Evron wrote:

Hmmm… that looks right. Let’s have a look at the controller
method.

Before I changed it:

def new_quote_item
Quote.find(params[:id]).quote_items.create(params[:
new_quote_item])
flash[:notice] = “Added item.”
redirect_to :action => “show”, :id => params[:id]
end