0

I am very familiar with MYSQL. Right now, trying a query on IBM SQL DB(DB2) : "select displayname from HOMEBASEDAPP.LOGINDB where username = '" + username +"' and password = '" + password +"';"

This is the error I get when I run the query.

error occurred [IBM][CLI Driver][DB2/LINUXX8664] SQL0206N "USERNAME" is not valid in the context where it is used. SQLSTATE=42703

I have attached a screenshot of how my table looks : DB Screenshot

Improve this question
4
  • You might need to check disname. It should be displayname. Commented Nov 22, 2014 at 3:42
  • Thanks for pointing out. I was running my query with "displayname", wrote it incorrectly here. Any idea why the error? Commented Nov 22, 2014 at 3:47
  • how about using with the table alias ? even though this one look ok. And how about without using the schema name. I never use bluemix before so I don't know much about how to run the query in bluemix. Commented Nov 22, 2014 at 3:51
  • What does your table creation script look like? How are you running this statement? To my knowledge USERNAME isn't a reserved word, which is what the error message would tend to imply... side note - you're wonderfully open to SQL Injection. You should be using parameterized queries, which might solve your problem as a side effect. Commented Nov 24, 2014 at 13:51

2 Answers 2

Reset to default
3

Your query is failing because DB2 expects all database objects to be uppercase by default. So much so, that it automatically translates your lower-case column names in your query to uppercase before execution.

Since your column names are lowercase, this query fails.

My recommendation is to convert your column names to uppercase, as this will save you much pain in the long run.

However, simply wrap the column names within your query in double-quotes and DB2 will preserve the correct case, and the query should work.

I believe this will work: SELECT "displayname" FROM HOMEBASEDAPP.LOGINDB WHERE \"username\" = '" + username +"' AND \"password\" = '" + password +"';"

Note: your query here is extremely insecure and is open to SQL injection attacks among other things. Hopefully you plan to use a driver within your app that will allow you to 'prepare' your query and provide means to simply pass in values as parameters.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

False. SQL in general is case insensitive (as part of the standard), unless you specifically set up object names to be case-sensitive during their creation. So this might solve the problem, but only because setup was botched in the first place.
You are incorrect. DB2 is case-sensitive by default. Additionally, if objects are created in a specific case, failing to delimit the object name in the query will cause the query to fail. My answer was accepted, so it is obviously accurate.
Nope. Searching data contained in columns is case sensitive. Object names are case-insensitive when queried (which is due to it converting everything to upper case during creation). Unless, that is, object names were qualified via escaping during creation, in which case you have to match whatever you gave it at that point (which is a pain to deal with, usually, so the recommendation is to not do that). Oh, you forgot to escape the quotes for "displayname"...
0

you need to modify your sql script ,otherwise on finding the first occurrence of double quote,db2 will treat it as terminating character,I mean to say query will be executed till below,so that you will get error:

db2 "select displayname from HOMEBASEDAPP.LOGINDB where username = '"

You need to take care of double quotes for parsing of full sql query, for detail below is a sample reference link:

http://www.justskins.com/forums/insert-with-double-quote-148522.html

Improve this answer

1 Comment

This depends entirely on how he's running that statement. If he's using Java, the form he's given is valid, and the way you concatenate strings (...which you're not supposed to do for stuff like this, but that's not the problem here).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.