Forum: Ruby on Rails SQL Syntax Errors

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ca198f33707fb1eb00a0adecac237498?d=identicon&s=25 Scott Quiring (squiring)
on 2006-04-18 06:16
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
54cbe4258125a45a51f379307adf064e?d=identicon&s=25 Tim Murphy (Guest)
on 2006-04-18 06:53
(Received via mailing list)
Scott,

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

Tim


----- Original Message -----
From: "Scott Quiring" <scottquiring@gmail.com>
To: <rails@lists.rubyonrails.org>
Sent: Tuesday, April 18, 2006 2:16 PM
Subject: [Rails] SQL Syntax Errors
Ca198f33707fb1eb00a0adecac237498?d=identicon&s=25 Scott Quiring (squiring)
on 2006-04-18 07:05
(Received via mailing list)
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
54cbe4258125a45a51f379307adf064e?d=identicon&s=25 Tim Murphy (Guest)
on 2006-04-18 07:17
(Received via mailing list)
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" <scottquiring@gmail.com>
To: <rails@lists.rubyonrails.org>
Sent: Tuesday, April 18, 2006 3:02 PM
Subject: Re: [Rails] SQL Syntax Errors
Ca198f33707fb1eb00a0adecac237498?d=identicon&s=25 Scott Quiring (squiring)
on 2006-04-18 07:38
(Received via mailing list)
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
F0379e983ab499fac4d972494beab5c0?d=identicon&s=25 Brez! !! (brez)
on 2006-04-18 07:48
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.
Ca198f33707fb1eb00a0adecac237498?d=identicon&s=25 Scott Quiring (squiring)
on 2006-04-18 08:00
(Received via mailing list)
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" %>
</form>

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

Thanks,
Scott
31dd25d3aacf15779219edb873eda6b4?d=identicon&s=25 Mike Evron (evronm)
on 2006-04-18 08:37
Hmmm...  that looks right.  Let's have a look at the controller method.
4daf0b71d5d9a3882e583c0e72eaf5dc?d=identicon&s=25 Alan Francis (Guest)
on 2006-04-18 15:23
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 :-)
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
Ca198f33707fb1eb00a0adecac237498?d=identicon&s=25 Scott Quiring (squiring)
on 2006-04-18 17:36
(Received via mailing list)
On 18-Apr-06, at 7:23 AM, Alan Francis 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
This topic is locked and can not be replied to.