Using variables in queries?

[javoine]

some background: i normally program with asp and jsp is very new to me.

i'm trying to execute a query against a database, but for a given value off my webform.

This is the query right now:

ResultSet rsEnrolled = stmtEnrolled.executeQuery("Select EnrollDate, Count(Name) as Enrolled FROM Enrollment where EnrollDate = 2/1/2003 Group by EnrollDate"

But i want it to use a variable off my form in place of the actual date.

i tried using:
ResultSet rsEnrolled = stmtEnrolled.executeQuery("Select EnrollDate, Count(Name) as Enrolled FROM Enrollment where EnrollDate =" + varDate + " Group by EnrollDate"

but it really doesnt like that. the value of my form element is being stored in the variable varDate, but how do i use varDate in the query?

any and all suggestions would be greatly appreciated. thank you!

 

[palbano]

>> but it really doesnt like that.

That does not provide any description of the error

what is the type of stmtEnrolled?

Create your query as a String object so you can send the value of the String to System.out.println() or the Logging facility so you know what String your sending to the database server.

-pete

 

[javoine]

Thanks Pete,

But I still dont understand. How do you use a variable inside of a query. query works fine when i give it real data, how do i do it using a variable.

I guess, i'm asking, what would the escape character be? or combination of characters to use around the variable so the query can recognize it.

in asp with vbscript you can use & chr(34) & around both sides of the variable and it reads it in like a string, etc...i cant seem to find how to do that in java/jsp?

thanx again.

 

[palbano]

String sql = "Select EnrollDate, Count(Name) as Enrolled FROM Enrollment where EnrollDate =" + varDate + " Group by EnrollDate";
// this will now show up in the console of your 
// web server so you can see what string you
// built and are about to send to the database
System.out.println("DBG.SQL: " + sql);
ResultSet rsEnrolled = stmtEnrolled.executeQuery(sql);

Then if the error you get (you still have not provided the error information), indicates that the database does not like your query statement you can copy the statement into your database environment and execute it there to see where the problem is.

-pete

 

[javoine]

Ok, still not getting an answer. I really do appreciate your help, but i guess i need to give more info:

i know that it is building a timestamp. i've printed out the value of the varialbe, and it shows up as the timestame value ie 2003-09-02 10:00:00. when i physically type in the date it runs just fine, when i pass it through the variable, it cant parse the expression.

what does java need to put quotes around the variable when it is passed in?

in other words: This works:
String sql = "Select EnrollDate, Count(Name) as Enrolled FROM Enrollment where EnrollDate = " + "' 2003-09-02 10:00:00 '" + " Group by EnrollDate";

this doesnt:
String sql = "Select EnrollDate, Count(Name) as Enrolled FROM Enrollment where EnrollDate = " + varDate + " Group by EnrollDate";

the value of the variable is correct, but what do i use to get single quotes around it?

thanx again

 

[palbano]

>> but what do i use to get single quotes around it?

umm... the same thing you used in the example that works, single quotes.

I must not understand your problem, you are asking how to put single quotes in a string by posting an example of using single quotes in a string. Therefore it seems you already know how to do what your asking.

-pete