query problem in jsp

[Aditi agarwal]

i am running my following query in my jsp page but its goint into catch part & showin unable to connect to database

("select * from ams_menuitems where role is (select logintype from ams_login where loginname= '"+user1.trim()+"');");

please help me

[Bear Bibeault]

You should not be doing JDBC in a JSP. Move the code to a Java class where it will be easier to debug.

Moved this to the JDBC forum.

[Bear Bibeault]

Explain how you are establishing the DB connection. And if that's in a JSP too, then it needs to be moved to a Java class.

[Aditi agarwal]

thanks bear for showing your concern actually everything is right except that my role field is integer & logintype is string thats the problem

[Sunny Bhandari]

Aditi,

you are showing us the query code and the problem as unable to connect to database.

Please share full code of making the connection and invoking that query.

This will help you and us to resolve this in one go.

Anyways, to resolve that problem, I will straight away run the query on DB console with hard coded values.

If the query runs fine then probably my connection has not established.

[Aditi agarwal]

thank you sunny for your reply but as i mentioned above that my connection is allright but instead in my query role is an integer & logintype is a string thats what the problem is


unable to connect database i sshown because i have printed it on the catch part

if you have any solution for that please share it with me

thank you

[Swastik Dey]

Could you please explain, what exactly are you trying to achieve with this query?

[amit punekar]

Hello,

("select * from ams_menuitems where role is (select logintype from ams_login where loginname= '"+user1.trim()+"');");

Shouldn't the query have "IN" instead of "is" ?

Also subquery will not need the ";" as per my interpreation of the query posted above.

Regards,
Amit

[Aditi agarwal]

hey swastik actually i have username who is logged in nnow i want on basis of his logintype i should get roles on my jsp page

i cannot change logintype from character to integer as it will effect other forms also

hey amit i have tried is in like = everything but nothing is working

[Swastik Dey]

Aditi

You don't have to change the data type in underlying table, but you can always use some conversion function in the query. What database are you using?

[Aditi agarwal]

thank you all of you for your reply & also sory for wasing your time i have now changed my logintype datatype

its the only solution which i think is remaining

[Aditi agarwal]

Swastik i am using Postgre SQL

[Swastik Dey]

I haven't worked on this, but your query might go somewhat like this, if you don't wish to change the data type in table.

select * from ams_menuitems where role in (select to_number(logintype,'999') from ams_login where loginname= '"+user1.trim()+"'")

http://www.postgresql.org/docs/7.4/static/functions-formatting.html

[Aditi agarwal]

i must say swastik you are a genius

you have solved my 2 major problems thank you thank you so much

[Swastik Dey]

Glad to be of some help.