wood burning stoves 2.0*
The moose likes JDBC and the fly likes Need more values from Other DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need more values from Other DB" Watch "Need more values from Other DB" New topic
Author

Need more values from Other DB

Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Hi ranchers,
I am back with bunch of questions...
I have lot of tables like customer,vendor,item and rma table... I generate a report of products that were return by customers to our company.. Here in this table i had a item_id field which is an foreign key which is the primary key of item table...My problem when admin clicks the report button some details will be displayed in screen from the RMA table along with the ITEM tables description field... I think you got it..
It retrieves all the product related information from RMA table.. In RMA table i had a field item_id and using this field i want to manipulate the item_description field that too displayed in the same report..

Please help me..

Sorry for my poor english...


Never try to be a hard-worker. Be a smart-worker.
My Blog
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

What I assumed from your question is that all you need to is play with joins.

Try making some query and post your query, if you feel your query is not right. Also let us know why that query is wrong.

Thanks,
Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

RMA TABLE
---------
item_id -> varchar(FK)
customer_id -> varchar(FK)
vendor_id -> varchar(FK)
and so on..

ITEM TABLE
---------
item_id -> varchar
item_description -> varchar

My question is...
I generate a report where all the details about the RMA product is displayed from the RMA table...
I also need the item_description of the items with its item_id....But it is not in the RMA table and in ITEM table... How i get the item description of each and every product using the item_id... I ain't write any query for that....Please help me...
I need a output like this...
CustID | InvDate | InvNo | ItemDesc | FaultDesc | SerialNo

But now i had a output like this....
CustID | InvDate | InvNo | ItemID | FaultDesc | SerialNo

I hope ranchers will help me......

Sorry.... i just wrote it by mistake... Any way if you suggest me a solution then i'll get the CustName from customer table by the same method i get item_description....
[ August 11, 2008: Message edited by: Rajkumar balakrishnan ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

I think one inner join with item table will solve your problem. I guess you are selecting from RMA TABLE


But could you tell me how are you getting CustName because RMA Table is having a column as customer_id .

You must be using inner join with customer table, if you can do it for one table then you can also do it for other tables too.

Thanks,
Shailesh
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Originally posted by Shailesh Chandra:
I think one inner join with item table will solve your problem. I guess you are selecting from RMA TABLE


But could you tell me how are you getting CustName because RMA Table is having a column as customer_id .

You must be using inner join with customer table, if you can do it for one table then you can also do it for other tables too.

Thanks,
Shailesh


I edited the post as i write CustName instead of CustID... Now please let me know how could i achieve that using innerjoin.......
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Rajkumar read below URL on join and come up with a query.

http://www.w3schools.com/Sql/sql_join.asp

All ranchers are there to help you out, once you will put some effort.

http://faq.javaranch.com/java/DoYourOwnHomework

Thanks,
Shailesh
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Hey friends,
i finally got what i want, but now my problem how do i implement it with JSP to get the desired output.. Here is my query and when i type it in MySQL console it returns the desired output... Here is my query...



And i need to print it in JSP page and i dont know how to get the results and iterate through it...
Here is the code i used but i dont get my desired output as i got in the MySQL console... whats the problem....


I dont get the item_description here....Whats the ptoblem..Please help me ranchers....

[ August 12, 2008: Message edited by: Rajkumar balakrishnan ]
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Any one is there to help me.....
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


tbl_item.item_description as ProductDetails

You are using what is called an alias here. It means the name of the column you return in your results is "ProductDetails" not "tbl_item.item_description". The ResultSet method getString(String) method needs to use the correct name.

By the way, its not a good ideas to mix JDBC code and JSP. Better to use a bean and seperate the nuts and bolts of talking to the database from the presentation logic in the JSP.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Rajkumar balakrishnan:
Any one is there to help me.....


Be patient Patience Is A Virtue

You are doing it right, but two query you have given are not identical. The query you are running on console is diferent with the code. can you check the same.

Thanks,
Shailesh
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Originally posted by Shailesh Chandra:


Be patient Patience Is A Virtue

You are doing it right, but two query you have given are not identical. The query you are running on console is diferent with the code. can you check the same.

Thanks,
Shailesh


Yeah... the one i write in console is to check the working of inner join and one i put in jsp is for my purpose.. So no worry about that.. So please help me ranchers....
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Did you follow Paul's post, It seems you have edited your query


tbl_item.item_description as ProductDetails


If your query is like <as ProductDetails> then use rs.getString("ProductDetails") else rs.getString("tbl_item.item_description");

Let us know if it is working.

Thanks,
Shailesh
Sh
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Originally posted by Shailesh Chandra:
Did you follow Paul's post, It seems you have edited your query



If your query is like <as ProductDetails> then use rs.getString("ProductDetails") else rs.getString("tbl_item.item_description");

Let us know if it is working.

Thanks,
Shailesh
Sh


Here is the code as per ranchers suggestion i made it... it runs flawlessly in console and not in JSP.. Why..


[ August 12, 2008: Message edited by: Rajkumar balakrishnan ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

See my earlier post. This is the reason rs.getString("tbl_item.item_description") returns nothing.
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Originally posted by Paul Sturrock:
See my earlier post. This is the reason rs.getString("tbl_item.item_description") returns nothing.


After changing that tpp i cant get anything.... Here is the modified code...


Will someone please give me a solution....
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I'm going to assume you mean you get no results?

Does:

return any results when you run it directly in your SQL client?
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Originally posted by Paul Sturrock:
I'm going to assume you mean you get no results?

Does:

return any results when you run it directly in your SQL client?

This is what i got when i type the query in console....



But not in JSP file,,,,
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

OK, so one of a number of things could be happening:
  • You are not running the same query in your JSP as you are in the console.
  • You are not connected to the database you think you are
  • Some sort of exception is occuring in your JDBC code
  • Something is messing up how you render your results. Have you checked the source of the page you return?


  • This is not related, but I notice you are using a PreparedStatement but not using bind variables. You probably want to fix that too.
    Rajkumar balakrishnan
    Ranch Hand

    Joined: May 29, 2008
    Posts: 445

    Originally posted by Paul Sturrock:
    OK, so one of a number of things could be happening:
  • 1.You are not running the same query in your JSP as you are in the console.
  • 2.You are not connected to the database you think you are.
  • 3.Some sort of exception is occuring in your JDBC code.
  • 4.Something is messing up how you render your results. Have you checked the source of the page you return?


  • This is not related, but I notice you are using a PreparedStatement but not using bind variables. You probably want to fix that too.

    Answer for your questions...
    1. No
    2.Surely not
    3.yes. i got after the end of result set exception.. but it is not giving me any problem
    4.Thats what i am thinking about.. If somebody do find any messy code please inform me..

    When i just give the below query in my JSP page it renders the result ...

    But when i give these queries it wont render the output...


    [ August 13, 2008: Message edited by: Rajkumar balakrishnan ]
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    1. No

    How do you know for sure? Are you logging the sql statement you are running in your Java app? I'd do this just to be absolutiely sure.


    2.Surely not

    You'd be surprised how often this mistake happens.


    yes. i got after the end of result set exception.. but it is not giving me any problem

    I'd look into that if I were you. There is normally no such thing an ignorable exception.


    But when i give these queries it wont render the output...

    Do you mean you get no results at all, or the table is rendered with no values in it? Have you tried using rs.getString(int) method instead? If there are no results at all, I'd assume that your join condition is never met i.e. there are no records WHERE tbl_rmadetails.item_id = tbl_item.item_id. Add the item_id to the sql query you got to work and check.


    4.Thats what i am thinking about.. If somebody do find any messy code please inform me..

    Look at the source of the HTML page you JSP renders. Is it valid HTML?

    With regards to messy code, like I said earlier its messy to mix JSP and JDBC code. Use a bean. Its also messy to use a PreparedStatement and not use bind variables.
    [ August 13, 2008: Message edited by: Paul Sturrock ]
    Shailesh Chandra
    Ranch Hand

    Joined: Aug 13, 2004
    Posts: 1081

    I would like to add one point only, can you try with query below.
    I have removed join condition. It will give you cartesian product of both table,also It will let you know if your join clause actually is being satisfied or not




    Thanks,
    Shailesh
    Rajkumar balakrishnan
    Ranch Hand

    Joined: May 29, 2008
    Posts: 445

    Originally posted by Shailesh Chandra:
    I would like to add one point only, can you try with query below.
    I have removed join condition. It will give you cartesian product of both table,also It will let you know if your join clause actually is being satisfied or not




    Thanks,
    Shailesh


    Sorry for such late reply as i am not here for the past few days... As for your query it gives all the item descriptions in the item table like this



    But i need something like this... item_description of product must be based on item_id of product in the rma table.....



    Please let me know how do i get the result.... I'll give you the complete code in which i find problems......


    Please help me ranchers.......
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    Have you tried using rs.getString(int) instead? You might also try looking at the ResultSetMEtaData to see what columns have been returned.

    NB: if your JDBC code wasn't mixed in with your JSP code you would be able to debug this easily without any concerns about soemthing else (such as invalid HTML) hiding output.
    [ August 19, 2008: Message edited by: Paul Sturrock ]
    Rajkumar balakrishnan
    Ranch Hand

    Joined: May 29, 2008
    Posts: 445

    Originally posted by Paul Sturrock:
    Have you tried using rs.getString(int) instead? You might also try looking at the ResultSetMEtaData to see what columns have been returned.

    NB: if your JDBC code wasn't mixed in with your JSP code you would be able to debug this easily without any concerns about soemthing else (such as invalid HTML) hiding output.


    Sure i will take a look on it and report it here.....Could i use this one
    rs.getString("tbl_rmadetails.serial_no"); instead of rs.getString("serial_no")???
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    Now I'm confused. I didn't think it was the serial_no field you were having trouble with?

    You shouldn't need the table name on the field name. Your database should return a result set that just includes the field. The field name should be the same in your SQL Client and via JDBC.
    Rajkumar balakrishnan
    Ranch Hand

    Joined: May 29, 2008
    Posts: 445

    Originally posted by Paul Sturrock:
    Now I'm confused. I didn't think it was the serial_no field you were having trouble with?

    You shouldn't need the table name on the field name. Your database should return a result set that just includes the field. The field name should be the same in your SQL Client and via JDBC.


    I just write that for an example.... But the problem is really with the "item_id" field... When the resultset traverse and when it comes to item_id it didn't return anything.... As i check it it returns something like this...


    The remainig fields seems to be null.... ie..the item details is the problematic area.... (The output shown above is not in exact format as i get)
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    So the above result is what you get in your MySQL client when you run the SQL:

    Yes? Now I'm even more confused because in earlier posts you said that this query returned different results?

    So are we trying to fix the query or its display logic?


    (The output shown above is not in exact format as i get)

    I'm not sure there is any point us trying to help if you don't tell us exactly what is happening and how it is going wrong. Without that we are just guessing.
    [ August 20, 2008: Message edited by: Paul Sturrock ]
    Rajkumar balakrishnan
    Ranch Hand

    Joined: May 29, 2008
    Posts: 445

    Originally posted by Paul Sturrock:
    So the above result is what you get in your MySQL client when you run the SQL:

    Yes? Now I'm even more confused because in earlier posts you said that this query returned different results?

    So are we trying to fix the query or its display logic?


    I'm not sure there is any point us trying to help if you don't tell us exactly what is happening and how it is going wrong. Without that we are just guessing.

    [ August 20, 2008: Message edited by: Paul Sturrock ]


    I apologise for that but i just mention that the output is not same as it is in graphical format. But this is what i got in my HTML page.
    May be its display logic is poor.... I too suspect that but i don't know how to fix it....
    Please help me ranchers...


    [ August 21, 2008: Message edited by: Rajkumar balakrishnan ]
    Rajkumar balakrishnan
    Ranch Hand

    Joined: May 29, 2008
    Posts: 445

    I got an exception like this



    Will it be the cause of my problem...???
    I swear on it....

    Please help me ranchers...
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Need more values from Other DB