Problem with MySql query and JAVA

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
John AccountAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

objectsCommented:
>   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
John AccountAuthor Commented:
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
objectsCommented:
how are you sending the query?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

John AccountAuthor Commented:
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
objectsCommented:
> 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
John AccountAuthor Commented:
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
pjcrooks2000Commented:
You need a space on line 3 "delBuddyFrom" should be "delBuddy From"

Hope this helps

pjcrooks2000
0
JavatmCommented:
> 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
John AccountAuthor Commented:
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
JavatmCommented:
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
JavatmCommented:
I used the MySQL Command Line Client and the MySQL Query Browser to test it and its working.
0
John AccountAuthor Commented:
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
John AccountAuthor Commented:
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
JavatmCommented:
Your variables ? where is it comming from JSP ? is it a string containing a value ?
0
John AccountAuthor Commented:
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
JavatmCommented:
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
John AccountAuthor Commented:
Nope... not working..
but thanks for the help anyway...

Julia
0
JavatmCommented:
Maybe you can also ask a follow-up question to the xml area.
0
pjcrooks2000Commented:
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
RMaruszewskiCommented:
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
John AccountAuthor Commented:
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
RMaruszewskiCommented:
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
RMaruszewskiCommented:
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
objectsCommented:
> as it apparently has to be ONE, atomic, executable query.

According to previous posts a single query is also failing.
0
RMaruszewskiCommented:
Yup. That's because there are no apostrophes in statements. :-)
0
John AccountAuthor Commented:
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
John AccountAuthor Commented:
Ok is there a way for me to put all that in the same query???
0
RMaruszewskiCommented:
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
John AccountAuthor Commented:
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
RMaruszewskiCommented:
> 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
RMaruszewskiCommented:
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
RMaruszewskiCommented:
Well... We've found the proper answer... And I've learnt something new... So I have no objections. ;-]
0
JavatmCommented:
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
moduloCommented:
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.