hi,

how do you know what's wrong with a query when you get

"You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near..." etc..

I'm working in JSP/Tomcat; when a query gets ignored I type it directly
in console, but it's hard to know exactly what's causing error
sometimes.. for example on this query:

qInsert = "insert into pb set caption='" + sCaptions[i] + "' WHERE
photo=" + i + "AND photopg='" + sPhPg + "'";

which to run directly in console I converted to:

insert into pb set caption='yada yada' WHERE photo=2 AND photopg='1'";
(all values are strings except 'photo', which is a tinyint)

I get the above...

obviously I don't want to post here every time I get one of those; is
there a way (like when you get java errors it tells you what's causing
error, what syntax error is, etc..) to know what's causing error in a
MySQL query? (well, this is not a query, an 'insert'....


thank you..

maya wrote:
> hi,
>
> how do you know what's wrong with a query when you get
>
> "You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near..." etc..
>
> I'm working in JSP/Tomcat; when a query gets ignored I type it directly
> in console, but it's hard to know exactly what's causing error
> sometimes.. for example on this query:
>
> qInsert = "insert into pb set caption='" + sCaptions[i] + "' WHERE
> photo=" + i + "AND photopg='" + sPhPg + "'";
>
> which to run directly in console I converted to:
>
> insert into pb set caption='yada yada' WHERE photo=2 AND photopg='1'";
> (all values are strings except 'photo', which is a tinyint)
>
> I get the above...
>
> obviously I don't want to post here every time I get one of those; is
> there a way (like when you get java errors it tells you what's causing
> error, what syntax error is, etc..) to know what's causing error in a
> MySQL query? (well, this is not a query, an 'insert'....
>
>
> thank you..
you don't need a where statement for an insert. maybe for an update but
not for an insert.

> how do you know what's wrong with a query when you get
>
> "You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near..." etc..

Usually when I get that error, it means that something is seriously
wrong with the query as opposed to just a minor syntax issue. Like
lark said, don't use WHERE with INSERT. When I get that error I look
at the structure of the query itself and go back to my MySQL book to
make sure I have the correct query structure.

> qInsert = "insert into pb set caption='" + sCaptions[i] + "' WHERE
> photo=" + i + "AND photopg='" + sPhPg + "'";
>
> which to run directly in console I converted to:
>
> insert into pb set caption='yada yada' WHERE photo=2 AND photopg='1'";
> (all values are strings except 'photo', which is a tinyint)

Also, I think you're missing a space before the AND statement. I
interpret that query as:

"insert into pb set caption='yada yada' WHERE photo=2AND photopg='1'"

>how do you know what's wrong with a query when you get
>
> "You have an error in your SQL syntax; check the manual that
>corresponds to your MySQL server version for the right syntax to use
>near..." etc..

One of the simpler things is to display the query before running it.

>I'm working in JSP/Tomcat; when a query gets ignored I type it directly
>in console, but it's hard to know exactly what's causing error
>sometimes.. for example on this query:
>
>qInsert = "insert into pb set caption='" + sCaptions[i] + "' WHERE
>photo=" + i + "AND photopg='" + sPhPg + "'";
>
>which to run directly in console I converted to:
>
> insert into pb set caption='yada yada' WHERE photo=2 AND photopg='1'";
>(all values are strings except 'photo', which is a tinyint)

If you echo the query, you won't have to guess that the variables and
the quoting, etc. were in the real query.