?
Solved

accessing table view from a programme

Posted on 2004-09-01
13
Medium Priority
?
335 Views
Last Modified: 2011-08-18
hi,

I have designed an application using Java with tree control on left and fields on right. I am able to retreive data from oracle db into tree and also display data on the fields.
Now I have made a view taking data from two tables as I need to display data from both the tables. View is created successfully and showing the right results, but when I am accessing from the prog, Im getting only the first row.
For eg:- Im retreiving emp_number into the first node of the tree control, when I am retreiving this from the normal(original) table, its showing me all the emp_numbers, but when I am using view, its showing me only the first row.

So my question is, is it problem with view or shld I change something in the code. As far as I read from books, one of the main advantages of view is joining tables and retreiving from the prog, so that user doesnt have to worry about joining tables.

All contributions will be greatly appreciatted.
0
Comment
Question by:vihar123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
13 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 11950341
Please show your sql
0
 
LVL 92

Expert Comment

by:objects
ID: 11950344
our db developer avoids views, i think cause they are a maintenance nightmare.
0
 

Author Comment

by:vihar123
ID: 11950378
>>our db developer avoids views, i think cause they are a maintenance nightmare.

but what if I have to access many tables, for each one do i need to build a new connection?
whats the general method that is followed :-)
--------------------------------------------------------------------------------------------------------------------------------------------


CREATE OR REPLACE VIEW regdet (
  ang_nr1,
  proj_nr1,
  prod_bez1,
  kunde_nr1,
  faktor1,
  basisprojekt1,
  ver_nr1,
  ver_besh1,
  gesc_jahr1,
  curr1,
  ges_kalk1,
  kalk_mek1,
  kalk_ele1,
  ref_nr1
) AS
SELECT r.ang_nr, r.proj_nr, r.prod_bez, r.kunde_nr, r.faktor, r.basisprojekt,
          d.ver_nr, d.ver_besh, d.gesc_jahr, d.curr, d.ges_kalk, d.kalk_mek, d.kalk_ele, d.ref_nr
FROM offreg1 r, offrdet1 d
WHERE r.ang_nr = d.ang_nr
/

SELECT * FROM regdet;

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:TimYates
ID: 11950402
> but what if I have to access many tables, for each one do i need to build a new connection?

Can't you just run the SQL:

SELECT r.ang_nr, r.proj_nr, r.prod_bez, r.kunde_nr, r.faktor, r.basisprojekt,
          d.ver_nr, d.ver_besh, d.gesc_jahr, d.curr, d.ges_kalk, d.kalk_mek, d.kalk_ele, d.ref_nr
FROM offreg1 r, offrdet1 d
WHERE r.ang_nr = d.ang_nr

instead of using the view?  I have had real problems with views and live updates before...  changing them at run-time causes some db's to get things wrong...

However, if they are static, they should work...
0
 
LVL 92

Expert Comment

by:objects
ID: 11950403
> but what if I have to access many tables, for each one do i need to build a new connection?


not at all, you can do your join in a single statement
or better still use a stored proc
0
 

Author Comment

by:vihar123
ID: 11950447
>>not at all, you can do your join in a single statement

can you give me an example of how to join them in a single statement?

an other problem is I dont have direct access to the tables, so I thought using views would be a better solution, anyway i think experience counts more, so any more suggestions, how to achieve this.

I think its clear what i want to do, i need to retreive data from two tables and display. tree control is important cause on its node selection i want to display related data.
0
 
LVL 92

Expert Comment

by:objects
ID: 11950477
you already have the sql :)

SELECT r.ang_nr, r.proj_nr, r.prod_bez, r.kunde_nr, r.faktor, r.basisprojekt,
          d.ver_nr, d.ver_besh, d.gesc_jahr, d.curr, d.ges_kalk, d.kalk_mek, d.kalk_ele, d.ref_nr
FROM offreg1 r, offrdet1 d
WHERE r.ang_nr = d.ang_nr
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11950488
>>tree control is important cause on its node selection i want to display related data.

You will need a custom TableModel, but there are plenty of examples of getting the results:

http://javaalmanac.com/egs/java.sql/CreateResultSet.html
0
 
LVL 92

Expert Comment

by:objects
ID: 11950526
dxepending on how much data you are using it mat be worth loading all the data up front, and store it in a Map keyed on emp number for easy lookup when user selects node.
0
 

Author Comment

by:vihar123
ID: 11951116
well guys, i want to try it out with view though its not advisory, and the present problem im facing is it is showing only one record when im accessing from the programme. how can i solve this problem.

my code looks like this

--------------
Connection conn1;
       
       Statement stmt1;
       
       ResultSet rset1 = null;
       
       
        try
        {
            DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
                     
           
            conn1 =
            DriverManager.getConnection ("");
           
            stmt1 = conn1.createStatement ();
                rset1 = stmt1.executeQuery("select * from regdet");
                
                           while (rset1.next())
               {
                       
                DefaultMutableTreeNode Angnr = new DefaultMutableTreeNode(rset1.getString("ang_nr1"));
              topnode.add(Angnr);              
              DefaultMutableTreeNode Version = new DefaultMutableTreeNode(rset1.getString("ver_nr1"));
              Angnr.add(Version);
              DefaultMutableTreeNode UA = new DefaultMutableTreeNode(rset1.getString("faktor"));
              Version.add(UA);
           
                              }
                              conn1.close();
                              stmt1.close();                                                      
                              rset1.close();
                              
                        
                                
                               
                    jTree1.setRootVisible(true);
                    treeModel.reload();      
                   
                     
        }
                 
        catch (SQLException e)
        {
            System.out.println(e);
        }
       
 
          jTree1.addTreeSelectionListener(new TreeSelectionListener() {
              
                public void valueChanged(TreeSelectionEvent evt) {
                                                                                       
                                TreePath tp = evt.getNewLeadSelectionPath();
       
                                 Connection conn2;
       
                               Statement stmt2;
       
                               ResultSet rset2 = null;
                               DefaultMutableTreeNode node1 = (DefaultMutableTreeNode)          
                                                                tp.getLastPathComponent();                               
                               String empNo = node1.toString();
                            

                                                              
                               try
                                {
                              DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
                     
                        conn2 = DriverManager.getConnection ("");
           
                              stmt2 = conn2.createStatement ();
                      rset2 = stmt2.executeQuery("select * from regdet WHERE ang_nr1 = '" + empNo + "'");
                                  
                               
                               while (rset2.next())
                           {
                            jTextField1.setText(node1.toString());
                            jTextField2.setText(rset2.getString("ang_nr1"));
                            jTextField3.setText(rset2.getString("prod_bez1"));
                            jTextField4.setText(rset2.getString("kunde_nr1"));
                            jTextField5.setText(rset2.getString("faktor1"));
                            jTextField6.setText(rset2.getString("proj_nr1"));
                            jTextField7.setText(rset2.getString("basisprojekt1"));
                                    
                           }
                              
                                          conn2.close();
                                          stmt2.close();
                                          rset2.close();
                                          
                                    
                                  }
                 
                          catch (SQLException e)
                                {
                                    System.out.println(e);
                                }
                                  
                                                                                        
                                                                                        
                                                                                        }
               
                                                                                                    }
       
                                                          );      
---------------------------------------

and the view looks like this

CREATE OR REPLACE VIEW regdet (
  ang_nr1,
  proj_nr1,
  prod_bez1,
  kunde_nr1,
  faktor1,
  basisprojekt1,
  ver_nr1,
  ver_besh1,
  gesc_jahr1,
  curr1,
  ges_kalk1,
  kalk_mek1,
  kalk_ele1,
  ref_nr1
) AS
SELECT r.ang_nr, r.proj_nr, r.prod_bez, r.kunde_nr, r.faktor, r.basisprojekt,
          d.ver_nr, d.ver_besh, d.gesc_jahr, d.curr, d.ges_kalk, d.kalk_mek, d.kalk_ele, d.ref_nr
FROM offreg1 r, offrdet1 d
WHERE r.ang_nr = d.ang_nr
/
-------------------------------------------

and the results from the view look like this.........

17.001      test      38      1,08      1234      2      Test Zdenko      2004      EUR      test1

test2      test3      17

........................................................


and the problem tree is showing only one record, ie the first column 17.001, its not showing other numbers, can anybody tell me where the problem is
0
 

Accepted Solution

by:
vihar123 earned 0 total points
ID: 11951283
ok guys, i solved the problem by myself, its working now. problem was with the view, i did not commit and didnt use column name properly, anyway thanks for the contributions.
--------------------------------------------------------------------------------------------------------------------------------------------
Hello Venabili,

Could you please delete the question, and refund the points.

Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses
Course of the Month11 days, 16 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question