wood burning stoves 2.0*
The moose likes Cattle Drive and the fly likes SQL is picky! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » This Site » Cattle Drive
Bookmark "SQL is picky!" Watch "SQL is picky!" New topic
Author

SQL is picky!

Carol Murphy
village idiot
Bartender

Joined: Mar 15, 2001
Posts: 1195
Why can't I use a variable reference as a string in a select statement?
I'm trying to do something like this:
SELECT * FROM ATABLE WHERE COLUMN='variable' ;
I have tried encasing the varaible name in '"+variable+"' '+variable+' "'"+variable+"'" and all other kinds of variations on this theme. Why doesn't this work? I keep getting an SQL exception. My code works up to this point, but no further......
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
Well what does each variation mean? Which one ultimately produces a string that is a valid SQL statement?

works for me.
If you expect that the value of variable will contain single or double quotes, I believe (but am not certain) that something called a prepared statement can help out, but I haven't investigated that yet.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
For each of the variations you've tried, have you sent the whole concatenated string to the console (System.out.prinln()) or a file to see exactly what you're sending off to the database to interpret?
Carol Murphy
village idiot
Bartender

Joined: Mar 15, 2001
Posts: 1195
My system is being stinky again. I have tried to respond to your post twice, but it doesn't get posted. Maybe this one will go through.
I'm using "'"+variable+"'" but no ; at the end of the sql command, only at the end of the java statement. There seems to be a problem with the code right where the result set is created, even though the same set of statements works fine in two other servlets, this set of statements doesn't create a result set. There must be some error in my sql somewhere. I was assuming it was the way I was trying to use the variable name, but now I think it might be something else. I find SQL very fussy, but I'm sure I will warm up to it once I understand it a bit more.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
I'm guessing that you're not having any SQL problems at all. I think you're having string concatenation problems in Java.
Assignment:
Produce the following output to the console:
select * from students where name = 'Michael' ;
First assign Michael to the identifier variable, then use that identifier to build up the string you print.
I'll get things started, you fill in the ...
Carol Murphy
village idiot
Bartender

Joined: Mar 15, 2001
Posts: 1195
Well, I was able to make the line print out to the console exactly as written. I'm still working on it! I figure if I print the statement out on my web page like this prior to the actual statement, I can see what the problem is! One question: Is the semi-colon required? My other SQL statements don't have one and they work. However, I have some cleaning up to do. While fooling around with my code, I was able to do an update statement that promptly updated EVERY video in my database so they are now all the same! NOT exactly what I wanted.............
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
MySQL requires the semicolon on the command line, but apparently not when it's embedded in Java code. Interesting.


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
Originally posted by Marilyn de Queiroz:
MySQL requires the semicolon on the command line, but apparently not when it's embedded in Java code. Interesting.

But not unprecendented.
It's the same way with the setup I use at work. Oracle is the database. SQL*Plus (basic Oracle SQL window) requires SQL statements to end in a semicolon. The languages I use (SQR and PeopleCode), which contain embedded SQL statements, do not require the semicolon.
I'm guessing that MySQL window and SQL*Plus acting as "command interpreters" need the semicolon to indicate that the command has ended and the program needs to switch from a data entry mode to a data processing mode. (Actually, in Oracle the semicolon doesn't cause the SQL statement to be executed, just stored to the SQL*Plus buffer as the next-available code for execution.) Whereas with the high-level languages are sending the SQL statement across some connection, which probably must have some specified way indicating the end of the statement.
Just to be contrary, looks like Oracle PL/SQL requires the semicolon. But PL/SQL has some of the ugliest syntax I've ever seen. OK, second ugliest (Visual Basic is the worst).
 
 
subject: SQL is picky!