Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem with MySql query and JAVA

Posted on 2004-11-29
35
Medium Priority
?
239 Views
Last Modified: 2010-03-31
I have the following query
<buddiesdelete>
                        <!-- Delete a buddy from the roster -->
                        SELECT members.id delBuddy from members where Login = #buddyid#
                        SELECT members.id delBuddyFrom members where Login = #userid#
                        DELETE FROM memfavs
                        WHERE fav = #delBuddy
                        AND id = #delBuddyFrom
                  </buddiesdelete>

Which returns the following error...



java.sql.SQLException: Syntax error or access violation: You have an error
ur SQL syntax.  Check the manual that corresponds to your MySQL server ver
or the right syntax to use near 'where Login = mimi
                                SELECT members.id delBuddyFrom where Log

I'm running a chat application and this is for users to remove buddies from their buddy list.  If I do the query without specifying 2 conditions (and) it works fine, but as soon as I'm using more than one statement, it gives me this error...

Input anyone?

Julia
0
Comment
Question by:John Account
  • 12
  • 8
  • 7
  • +3
34 Comments
 
LVL 92

Expert Comment

by:objects
ID: 12701139
>   SELECT members.id delBuddyFrom where Log

should n't there be a space in there, and a table name

  SELECT members.id delBuddy From members where Login = #userid#

0
 

Author Comment

by:John Account
ID: 12701197
no...

first of all, this is what the Java server returns...  THis is the message I'm getting....
------------------------------------
java.sql.SQLException: Syntax error or access violation: You have an error
ur SQL syntax.  Check the manual that corresponds to your MySQL server ver
or the right syntax to use near 'where Login = mimi
                                SELECT members.id delBuddyFrom where Log
-----------------------------------


If you look again at my query, you'll see that it goes like THIS


------------------------------------

<buddiesdelete>
                    <!-- Delete a buddy from the roster -->
                    SELECT members.id delBuddy from members where Login = #buddyid#
                    SELECT members.id delBuddyFrom from members where Login = #userid#
                    DELETE FROM memfavs
                    WHERE fav = #delBuddy
                    AND id = #delBuddyFrom
               </buddiesdelete>

--------------------------------------

my table is "members"  and  the FROM is there in the query... I just made a typo here... my variable name is delBuddyFrom...  I guess I'll change that cause it's confusing, obviously.  

But I still don't know why it's returning a syntax error
0
 
LVL 92

Expert Comment

by:objects
ID: 12701230
how are you sending the query?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:John Account
ID: 12701256
hmmm... well, given that I bought that server, I don't quite know how everything works.  I know that I have a config.xml page where that query resides.  That config.xml file also has my connection string to my MySql database.  Does that help?
0
 
LVL 92

Expert Comment

by:objects
ID: 12701299
> or the right syntax to use near 'where Login = mimi
>              SELECT members.id delBuddyFrom where Log

The sql mentioned in the error doesn't seem to match the sql in the xml.
What could be causing that?

Also are you sure it supports more than one query?
What does work, and what doesn't?
0
 

Author Comment

by:John Account
ID: 12701407
ok, I'm trying a bunch of things so bare with me.... right now,

this:

<!-- Delete a buddy from the roster -->
                    DELETE FROM memfavs
                    WHERE fav = #buddyid#
               </buddiesdelete>
works like a charm.... but deletes EVERYONE on a buddy list since it's looking at only one variable.

this however

<!-- Delete a buddy from the roster -->
                    DELETE FROM memfavs
                    WHERE fav = #buddyid#
                    AND user = #usernickname#
               </buddiesdelete>

returns this error...

java.sql.SQLException: Syntax error or access violation: You have an error
ur SQL syntax.  Check the manual that corresponds to your MySQL server ver
or the right syntax to use near '' at line 3
0
 
LVL 8

Expert Comment

by:pjcrooks2000
ID: 12702433
You need a space on line 3 "delBuddyFrom" should be "delBuddy From"

Hope this helps

pjcrooks2000
0
 
LVL 15

Expert Comment

by:Javatm
ID: 12702450
> DELETE FROM memfavs WHERE fav = #buddyid# AND user = #usernickname#

Why don't you try deleting the usernickname at once ? Or maybe I'm wrong.

DELETE FROM memfavs WHERE user = #usernickname#

I've been using MySQL for quite a while let me know if you have any problems.
0
 

Author Comment

by:John Account
ID: 12702609
Guys, guys, guys... you are not reading the question properly...

pjcrooks2000, this already has been covered... if you look at the threat you'll see... that "delBuddyFrom " variable has been changed anyway as this was confusing... please refer to my last post.

Javatm :

Same here... please refer to my last post.

I can't use your solution because I need to meet two requirements in order to delete the buddy... the FAV column must be equal to the BUDDYID and the USER must be equal to USERNICKNAME... IF i do your solution, it will delete everyone in my list...

0
 
LVL 15

Expert Comment

by:Javatm
ID: 12702742
Okey I just recreated a similar senario using MySQL and this should work :

// So change YourBuddyId w/ a real Id and Usernickname w/ a name.
DELETE FROM memfavs WHERE fav ='YourBuddyId' AND user = 'Usernickname';

Hope that helps . . .
Javatm
0
 
LVL 15

Expert Comment

by:Javatm
ID: 12702750
I used the MySQL Command Line Client and the MySQL Query Browser to test it and its working.
0
 

Author Comment

by:John Account
ID: 12702791
I'm sorry, I don't follow you Javatm.  Maybe I'm dumb or something, but if I do change my query from
DELETE FROM memfavs WHERE fav = #buddyid# AND user = #usernickname#
to
DELETE FROM memfavs WHERE fav ='48' AND user = '4';
for example, I'm not advanced... These variables (buddyid , usernickname) are being populated by another query and depending on who's logged in and who a user wants to delete from their buddylist, these constantly change.

So I don't get your point...

Julia

0
 

Author Comment

by:John Account
ID: 12702803
ok, Javatm, I did what you suggested, and it did remove my data... but I need variables in that query.. so why the heck doesn't it work when I feed it variables???

Julia
0
 
LVL 15

Expert Comment

by:Javatm
ID: 12702810
Your variables ? where is it comming from JSP ? is it a string containing a value ?
0
 

Author Comment

by:John Account
ID: 12702838
Ok... I'm gonna post the whole code, so maybe you'll be able to visualize this more...

I didn't program this, and I'm not good with java... so hopefully, you'll be able to help.

all the variables are retrieved from the MySql database by these statements :

--------------------

<sqlstatements>
                  <login>SELECT login as userid, members.login as usernickname,

members.id as myId, pictures.picture as userpicture from members, pictures WHERE

LOWER(login) = LOWER('#username#') AND pswd = '#password#' </login>


   <buddies>SELECT members.id as MyBuddyId, members.login buddyid, members.login

buddynickname, 'pictures/'+members.login  buddypicture,

memfavs.ex groupname, members.login blocked from members, memfavs, members usr WHERE

members.id = memfavs.fav AND usr.id = memfavs.user AND LOWER(usr.login) =

LOWER('#userid#')</buddies>
                  
                  <buddiesinsert>
                        <!-- Add a buddy to the roster -->
                        INSERT INTO buddies (user_id, user_id_buddy, group_name)
                        SELECT u.user_id, b.user_id, '#groupname#'
                        FROM users u, users b
                        WHERE b.user_id = #buddyid#
                        AND u.user_id = #userid#
                  </buddiesinsert>
                  <buddiesupdate>
                        <!-- Update a buddy to the roster -->
                        UPDATE buddies
                        SET group_name = '#groupname#',
                        blocked = '#blocked#'
                        WHERE user_id_buddy = #buddyid#
                        AND user_id = #userid#
                  </buddiesupdate>
                  <buddiesdelete>
                        <!-- Delete a buddy from the roster -->
                    DELETE FROM memfavs
                    WHERE fav = '#myId#'
                    AND user = '#MyBuddyId#'
                  </buddiesdelete>
                  <buddiessearch>
                  </buddiessearch>
            </sqlstatements>
------------------------------

does that make any more sense?  Thanks for your time Javatm.

Julia
0
 
LVL 15

Expert Comment

by:Javatm
ID: 12702889
I'm sorry but I'm not that good in xml but I sure did tried helping. Anyways if your using a global
variable maybe you can try this :

// I cant assure you if this will work in xml :

" DELETE FROM memfavs WHERE fav = " + #buddyid# + " AND user = " +  #usernickname#;
0
 

Author Comment

by:John Account
ID: 12702981
Nope... not working..
but thanks for the help anyway...

Julia
0
 
LVL 15

Expert Comment

by:Javatm
ID: 12703043
Maybe you can also ask a follow-up question to the xml area.
0
 
LVL 8

Expert Comment

by:pjcrooks2000
ID: 12704980
I still think the reported error in the question in line three was what I said and others said above, but obvioulsy you needed another way to do this and that is fair enough.

0
 
LVL 3

Expert Comment

by:RMaruszewski
ID: 12707150
Well, you have the following query:

SELECT members.id delBuddy from members where Login = #buddyid#

And the question is: this "buddyid" is of numerical, or string (varchar) type? Assuming that this is a string varialbe - and apparently it is, as you try to insert value "mimi" there - your query should look like this:

SELECT members.id delBuddy from members where Login = '#buddyid#'
0
 

Author Comment

by:John Account
ID: 12719176
Both the buddyid and the userid are strings.. so Like this RMaruszewski?

<buddiesdelete>
                  <!-- Delete a buddy from the roster -->
                    SELECT members.id MyId from members where Login = '#userid#'
                    SELECT members.id MyBuddyId from members where Login = '#buddyid#'
                    DELETE FROM memfavs
                    WHERE fav = #MyBuddyId#
                    AND user = #MyId#
               </buddiesdelete>

still returns this error
java.sql.SQLException: Syntax error or access violation: You have an error
ur SQL syntax.  Check the manual that corresponds to your MySQL server ver
or the right syntax to use near 'SELECT members.id MyBuddyId from members where Login = 'John'

I think I will go ahead and request this question to be closed... I will go with another solution in php.

Thanks anyways guys

And pjrcrooks.. this WAS NOT the problem... my query is fine now and it still returns the same error.  I DO HAVE a space between my variable and the FROM...

Julia
0
 
LVL 3

Expert Comment

by:RMaruszewski
ID: 12720107
HEY, BUT YOU DO HAVE WRONG QUERY!!!
 
You cannot write

<myquery>
SELECT ...
SELECT ...
DELETE ...
</myquery>

as it apparently has to be ONE, atomic, executable query.
And here you have THREE queries.

What did you try to change in the original version of this query, BTW?
And how is it supposed to work now?

(the original query was, as I presume, this one:

               <buddiesdelete>
                    <!-- Delete a buddy from the roster -->
                    DELETE FROM memfavs
                    WHERE fav = '#myId#'
                    AND user = '#MyBuddyId#'
               </buddiesdelete>

)
0
 
LVL 3

Expert Comment

by:RMaruszewski
ID: 12720162
And another thing - look at the original version:

                    WHERE fav = '#myId#'
                    AND user = '#MyBuddyId#'
 
There are apostrophes '' around #myId# and #MyBuddyId# - did you notice?
Your "where" clause looks very similar (?), but you miss the apostrophes there:

                  WHERE fav = #MyBuddyId#
                  AND user = #MyId#
0
 
LVL 92

Expert Comment

by:objects
ID: 12721424
> as it apparently has to be ONE, atomic, executable query.

According to previous posts a single query is also failing.
0
 
LVL 3

Expert Comment

by:RMaruszewski
ID: 12721620
Yup. That's because there are no apostrophes in statements. :-)
0
 

Author Comment

by:John Account
ID: 12722594
no guys... a single query does work... anyway, I kinda give up...
so I guess that's it.  I can only have one query.
Julia
0
 

Author Comment

by:John Account
ID: 12722621
Ok is there a way for me to put all that in the same query???
0
 
LVL 3

Expert Comment

by:RMaruszewski
ID: 12723628
Quite possible.
Try something like this:

               <buddiesdelete>
                    <!-- Delete a buddy, operating on login names -->
                    DELETE FROM memfavs
                    WHERE fav = (SELECT id from members where Login = '#buddyid#')
                    AND user = (SELECT id from members where Login = '#userid#')
               </buddiesdelete>
0
 

Author Comment

by:John Account
ID: 12736993
No, doesn't work either...

well thanks for the help anyway guys... Did my solution in php instead.  

Requested this to be closed.

thanks again

Julia
0
 
LVL 3

Expert Comment

by:RMaruszewski
ID: 12737596
> No, doesn't work either...

Fine. Paste the error description then.
I'm answering your questions not to get virtual points, but to learn something...
If you don't give me any feedback, I will never know why such a simple SQL statement doesn't work on MySql.
0
 
LVL 3

Expert Comment

by:RMaruszewski
ID: 12737634
OK - I've found the answer already:
> Not on MySQL 3.23, no subquery support until 4.1

It seems you have old version of MySQL, and you cannot use subqueries there.
0
 
LVL 3

Expert Comment

by:RMaruszewski
ID: 12739847
Well... We've found the proper answer... And I've learnt something new... So I have no objections. ;-]
0
 
LVL 15

Expert Comment

by:Javatm
ID: 12761613
I object because I've done so many research about the problem and tried helping to the best level that I can
some how I provided an answer that works in JSP but I did'nt realize that the asker was having an issue
w/ an xml format file. I assumed that the queries was done using mysql so I provided a solution that
works in MySQL and JSP.

Anyways I'll respect your decision regarding this.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12764281
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question