Solved

accessing table view from a programme

Posted on 2004-09-01
13
326 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
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:

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now