Really stuck with my SQL Statement. Please help!

Firegod · 24 Oct 2007 at 19:36

Hello.

I'm trying to pass a variable to my SQL Statement but it just won't output the correct results from the table.

It's nice and simple.

I have a value stored in a variable, so for example,

String abc;

This is fine, if I perhaps do out.println(abc); it will output the variable value depending on what the user enters.

Now though when I pass it to the SQL Statement, nothing...

selectStatement = "SELECT Emp_Name FROM Employees WHERE Emp_Sal = 'abc' ";

So I think you can see what I'm trying to do. The 'abc' value will be different depending on what salary the user enters for the search. However at the moment it is basically looking for a value called 'abc' and always finding nothing. How do I make it actually use the value stored within the variable and not just take the variable name???

Cheers.

Markus123 · 24 Oct 2007 at 19:49

You didn't mention what language you're using but if it's Java (or C#) you can use:

selectStatement = "SELECT Emp_Name FROM Employees WHERE Emp_Sal = '" + abc + "'";

Hope that helps, also I recommend looking up prepared statements.

Goody_GTI · 24 Oct 2007 at 19:57

What language are you trying to do this in?

Firegod · 24 Oct 2007 at 20:27

Sorry yes, it's running the SQL on a Servlet... so J2ME (Java).

Markus123, thanks I'll give that a go.

Anymore suggestions more than welcome!!

Goody_GTI · 24 Oct 2007 at 20:27

Quick effort, but should it not be like this:

selectStatement := "SELECT Emp_Name FROM Employees WHERE Emp_Sal = '" + abc + "' ;"

(also im sure the semi-colon has to be in the quotes of the select)

Firegod · 25 Oct 2007 at 12:41

Thanks guys that worked a treat.

One more question if I may please then? Now that I am able to do this, I save the returned result and save it to a bind variable, for example, :x.

I have read into my code another new number which I now need to add to this :x variable.

Here is the code I have, and it updates successfully but keeps changing the row to empty. :confused:

For example,

selectStatement = "SELECT Time FROM Employees WHERE Emp_ID='"+abc+"';

Works great, whatever Emp_ID is entered, the correct Time is retrieved. Now I save that answer to a variable,

SELECT Time INTO :x FROM Employees WHERE Emp_ID='"+abc+"';

Great, SQL outputs the correct time again and saves it the variable "x".

Now though is where I get stuck,

VARIABLE x number
UPDATE Employee SET Time = :x + abc2 WHERE Employee_ID="'+abc+"';

You will notice I have abc2. This is another example value which the user has entered previously. Basically I want to add this additional time onto the value already stored. It seems to fall down at the SET Time = ..... area.

How do I get the variable :x value to add on this new time? Do I need more speechmarks around the whole thing? I tried,

SET Time= ':x + :x'

thinking that it would just double the value, so say if x was 10 it would make it 20. Instead it entred it as a literal string, so the Time column read :x + :x. Lol. Which isn't right.

Thanks again.

Firegod · 25 Oct 2007 at 15:19

Apparently I can't do any sort of concatenation with the SET command, so I guess I can't add them together afterall?

I've been trying to use CAST and CONVERT as well, just messing on, but can't get that to work either.

For example, how do I convert say a char string '40.00.00' to a number? Like I say I've tried CAST and CONVERT with int, number types but it says invalid.

Another wasted day! lol.

stx666 · 25 Oct 2007 at 15:36

thats because your numeric is invalid, it has 2 decimal places. If you want to cast to an integer it can't have any decimal places.

you will have to strip out the decimal places.