Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

accessing table view from a programme

Posted on 2004-09-01
13
Medium Priority
?
338 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
Technology Partners: 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This video teaches viewers about errors in exception handling.
Suggested Courses

604 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