Professional Web Applications Themes

debugging queries.... - MySQL

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: ...

  1. #1

    Default debugging queries....

    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 Guest

  2. #2

    Default Re: debugging queries....

    maya wrote: 
    you don't need a where statement for an insert. maybe for an update but
    not for an insert.

    lark Guest

  3. #3

    Default Re: debugging queries....

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

    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.
     

    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'"


    Kevin Guest

  4. #4

    Default Re: debugging queries....

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

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

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

    Gordon Guest

Similar Threads

  1. Debugging output does not appear when use Debugging IPAddresses
    By Schroeder2 in forum Coldfusion Server Administration
    Replies: 2
    Last Post: June 7th, 11:15 PM
  2. Debugging Queries with CFQUERYPARAM
    By Richard7777 in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 23rd, 05:10 PM
  3. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM
  4. VI Debugging
    By Scott in forum ASP
    Replies: 1
    Last Post: October 22nd, 12:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139