Member Avatar for k2k

Hi, I am trying write a program to input and output data from a microsoft access file with java.sql class and the swing class.

basically i am on the first stage, trying to figure out different parts.

the first thing i would like to know is:
(assume it is already connected to the db with some data and column names are also there) i have a method to go into the database and get the columnNames and places them to a Vector.

as below:

public Vector getColumnNames() throws SQLException
	{
		ResultSet rs = null;
		Vector columnNames = new Vector();
		
		try
		{
			ResultSetMetaData rsmd = rs.getMetaData();
		
			for(int i=1; i<rsmd.getColumnCount(); i++)
			{
				columnNames.addElement(rsmd.getColumnName(i));
			}
		}
		
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	
		return columnNames;	
	}
////  does it look right? compiling was fine

***my first question is, how do i see the elements in the vector?

i tried to set the return columnNames result to a new vector but apparently it is not the right way.

public static void main(String args[])
	{
		DataHolder dh = new DataHolder();
		
		Vector columns = new Vector();
		columns.addElement(dh.getColumnNames);	
	} /// this has compiling error

is it possible to set a new Vector = a Vector returned by a method?

and how do i print out each element in the vector?

thank you

  • 2 Contributors
  • 3 Replies
  • 280 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by k2k

Recommended Answers

All 3 Replies

Member Avatar for k2k

huh, this main actually got it compiled.

public class Screen 
{
	
	public static void main(String[] args) throws SQLException
	{
		
		DataHolder dh = new DataHolder();
		
		Vector columns = new Vector();
		Vector rows = new Vector();
		
		
		rows.addElement(dh.getRowData());
		columns.addElement(dh.getColumnNames());
		
		/*
		for(int i=0; i< columns.size(); i++)
		{
			System.out.println(columns.get(i));
		}
		*/
		System.out.println(columns.get(2));
	}
	
}

however, now i got:

Exception in thread "main" java.lang.NullPointerException
	at DataHolder.getRowData(DataHolder.java:102)
	at Screen.main(Screen.java:17)

does it mean it can't read anything from the file? and how do i test if it is reading anything?

as reference, here is my class, you might only need to see a part of it though.

import java.sql.*;
import javax.swing.JOptionPane;
import java.util.*;

public class DataHolder
{
	private Connection connection;
	private LinkedList<Employee> employeeList;
	
	DataHolder()
	{
		////////////////////     get connected to the Database     ///////////////////////////////
		
		String url = "jdbc:odbc:MS Access Database"+";DBQ=c:\\employee.mdb"; 
		String username = "anonymous";
		String password = "guess";
		
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			this.connection = DriverManager.getConnection(url, username, password );
		}
		
		catch(ClassNotFoundException cnfex)
		{
			System.err.println("Failed to load JDBC/ODBC driver.");
			cnfex.printStackTrace();
			System.exit(1);
		}
		
		catch(SQLException sqlex)
		{
			System.err.println("Unable to connect");
			sqlex.printStackTrace();
		}
		
		System.out.println("Connected successful");
		
		getAllData();
		
		shutDown();
	}// end constructor


////////////////////////////////////////////////////////
	
	public LinkedList<Employee> getEmployeeList()
	{
		return employeeList;
	}
	
///////////////////////////////////////////////////////	
	private void getAllData()
	{
		Statement statement;
		ResultSet resultSet;
		
		try
		{
			String query = "SELECT * FROM employees";
			statement = connection.createStatement();
			resultSet = statement.executeQuery(query);
			statement.close();	
		}
		
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	}// end getAllData
//////////////////////////////////////////////////////
	
	public Vector getColumnNames() throws SQLException
	{
		ResultSet rs = null;
		Vector columnNames = new Vector();
		
		try
		{
			ResultSetMetaData rsmd = rs.getMetaData();
		
			for(int i=1; i<rsmd.getColumnCount(); i++)
			{
				columnNames.addElement(rsmd.getColumnName(i));
			}
		}
		
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	
		return columnNames;	
	}
	
	
	public Vector getRowData() throws SQLException
	{
		ResultSet rs= null;
		Vector rowData = new Vector();
		
		try
		{
			ResultSetMetaData rsmd = rs.getMetaData();
			
			for(int i=1; i<rsmd.getColumnCount(); i++)
			{
				do
				{
					rs.next();
					rowData.addElement(getNextRow(rs, rsmd));
				
				}while(rs.last() == true);    // if last row is not valid then stop adding
				
				}
		}
		
		catch( SQLException sqlex ) 
		{
			sqlex.printStackTrace();
	    }
		
		return rowData;
	} // end getRowData
	
		
	
	
	
	private Vector getNextRow( ResultSet rs, ResultSetMetaData rsmd ) throws SQLException
	{
		Vector currentRow = new Vector();

		for( int i=1; i <= rsmd.getColumnCount(); i++ )
		{  
			switch( rsmd.getColumnType(i) ) 
			{
				case Types.VARCHAR:
				{
					currentRow.addElement(rs.getString(i));
					break;
				}
				
				case Types.INTEGER:
				{
					currentRow.addElement(new Integer(rs.getInt(i)));
					break;
				}

				default: 
					System.out.println( "Type was: " + rsmd.getColumnTypeName( i ) );
			} // switch    
		}// end for
		
		return currentRow;
		
	}// end getNextRow
		
	
	public void shutDown()
	{
		try 
		{
			connection.close();
	    }
	    
		catch( SQLException sqlex ) 
		{
			System.err.println( "Unable to disconnect" );
			sqlex.printStackTrace();
	    }
	 }// shutDown
	
	
	
	
	////////////////////  main class ///////////////////////
	/*
	public static void main(String args[])
	{
		DataHolder dh = new DataHolder();
		
		Vector<String> columns = new Vector();
		columns.addElement(dh.getColumnNames);
		
		
	}
} // end class
} // end class
Member Avatar for quuba

Inside getRowData() null value

public Vector getRowData() throws SQLException {
		ResultSet rs= null;//NULL
		Vector rowData = new Vector();
		try{
			ResultSetMetaData rsmd = rs.getMetaData();
                                    //rs =NULL

I made some changes.

import java.util.Vector;

public class Screen {

    public static void main(String[] args) {
        DataHolder dh = new DataHolder();
        Vector columns = new Vector();
        Vector rows = new Vector();
        rows.addElement(dh.getRowData());
        columns.addElement(dh.getColumnNames());
        dh.shutDown();
        System.out.println("columns=" + columns.get(0));
        System.out.println("rows=" + rows.get(0));
    }
}

===

import java.sql.*;
import java.util.*;

public class DataHolder {

    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private ResultSetMetaData rsmd;

    DataHolder() {
        //get connected to the Database
        String username = "";// "anonymous";
        String password = "";// "guess";
        //String url = "jdbc:odbc:DRIVER=Driver do Microsoft Access (*.mdb);DBQ=C:\\examle.mdb";
        String url = "jdbc:odbc:example";
        //"jdbc:odbc:MS Access Database"+";DBQ=c:\\employee.mdb";
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException ex) {
            System.err.println("A " + ex);
            System.err.println("Failed to load JDBC/ODBC driver.");
        }
        try {
            //
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException ex) {
            System.err.println("B " + ex);
            System.err.println("Unable to connect");
        }
        try {
            statement = connection.createStatement();
        } catch (SQLException ex) {
            System.err.println("C " + ex);
            System.err.println("Unable to create statement");
        }
        System.err.println("if no errors Connected successful");

    //1. to early to shutDown
    //   shutDown();
    }// end constructor

    public Vector getRowData() {
        Vector rowData = new Vector();
        int cols = 0;
        String query = "SELECT * FROM employees";
        try {
            resultSet = statement.executeQuery(query);
        } catch (SQLException ex) {
            System.err.println("E " + ex);
        }
        try {
            rsmd = resultSet.getMetaData();
            cols = rsmd.getColumnCount();
        } catch (SQLException ex) {
            System.err.println("F " + ex);
        }
        try {
            System.err.println("cols=" + cols);
            for (int i = 0; i < cols; i++) {
                System.err.println(i + " " + rsmd.getColumnType(i + 1));
            }
        } catch (SQLException ex) {
            System.err.println("G " + ex);
        }
        for (int i = 1; i <= cols; i++) {
            Vector columnData = new Vector();
            //2. seems that resultSet need to be refreshed each iteration
            try {
                resultSet = statement.executeQuery(query);
            } catch (SQLException ex) {
                System.err.println("H " + ex);
            }
            try {
                while (resultSet.next()) {
                    System.err.println("col=" + i);
                    switch (rsmd.getColumnType(i)) {
                        case Types.VARCHAR: {
                            columnData.addElement(resultSet.getString(i));
                            System.err.println("Types.VARCHAR:");
                            break;
                        }
                        case Types.INTEGER: {
                            columnData.addElement(new Integer(resultSet.getInt(i)));
                            System.err.println("Types.INTEGER:");
                            break;
                        }
                        default:
                            System.err.println("Type was: " + rsmd.getColumnTypeName(i));
                    } // end switch
                } // end while
            } catch (SQLException ex) {
                System.err.println("I " + ex);
            }
            rowData.add(columnData);
        }//end for
        return rowData;
    } // end getRowData

    public Vector getColumnNames() {
        Vector columnNames = new Vector();
        try {
            rsmd = resultSet.getMetaData();
        } catch (SQLException ex) {
            System.err.println("J " + ex);
        }
        try {
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                columnNames.addElement(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException ex) {
            System.err.println("K " + ex);
        }
        return columnNames;
    }

    public void shutDown() {
        try {
            statement.close();
        } catch (SQLException ex) {
            System.err.println("L " + ex);
        }
        try {
            connection.close();
        } catch (SQLException ex) {
            System.err.println("M " + ex);
            System.err.println("Unable to disconnect");
        }
    }
} // end class
Member Avatar for k2k

thanks very much for the reply.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.