• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

accessing table view from a programme

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
vihar123
Asked:
vihar123
  • 4
  • 4
  • 2
  • +1
1 Solution
 
CEHJCommented:
Please show your sql
0
 
objectsCommented:
our db developer avoids views, i think cause they are a maintenance nightmare.
0
 
vihar123Author Commented:
>>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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
TimYatesCommented:
> 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
 
objectsCommented:
> 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
 
vihar123Author Commented:
>>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
 
objectsCommented:
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
 
CEHJCommented:
>>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
 
objectsCommented:
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
 
vihar123Author Commented:
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
 
vihar123Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now