Using imbedded SQL in Java

Let's see what is actually printed in your Query..

type this

system.out.print(query);

just above

ResultSet rs1 = s.executeQuery(query);

my guess

s is null and have not been initialized with a s = con.createStatement()

Hi guys,

Thanks for your quick response. Unfortunately, we've already tried these suggestions without result:

We do have a println as below:

Print query: SELECT kategori FROM ProduktSpecifikation INNER JOIN ProduktKatalog ON ProduktSpecifikation.katalogID =ProduktKatalog.KatalogID WHERE ProduktKatalog.mærke='Michelin'

We have this statement in the beginning of the program and works fine for two previous calls to the database:

s = con.createStatement();

and you do not have more than one s so this call uses one s and the ones that
do work uses another s ?

They use the same s. Is that a problem?

no

if they used different s it could explain the problem

Can you try a:

System.out.println(s);

to verify whether it is null or not ?

Here is what I get:
Print s: null

so s is null !

why ?

different s from the one that worked ?

some method setting it to null ?

In the two previous hits to the database, s is:

Print s: sun.jdbc.odbc.JdbcOdbcStatement@1457cb

but it is null in the call to the database that doesn't work.

could we see a little bit more code ?

Does this help?


package eLarsen;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;

public class ELarsenDBAdapter {
    private Connection con;
    private Statement s;
    private ResultSet rs;//=new ResultSet();
    private ResultSet rs1;
    private ArrayList Ok;
   
    //Constructor ELarsenDBAdapter
    public ELarsenDBAdapter(){}
   
    //Connect to database
    public String dbConnect(String dbName) {
        String url="jdbc:odbc:"+dbName;
        String username = "";
        String password = "";
        // load the driver and make the connection
        try {
            //load the driver and make the connection
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          con = DriverManager.getConnection(url,username,password);
          s = con.createStatement();
            return null;
        }
        catch (ClassNotFoundException cnfex){
            return "Failed to load JDBC/ODBC driver.";
        }
        catch (SQLException sqlex){
            return "Unable to connect.";
        }
    }
   
   
   
    //GetMærker
    public ArrayList getMærker(){
        try{
            System.out.println("In DBAdapter getMærker method.") ;
              String query =
                  "SELECT mærke FROM ProduktKatalog";
            System.out.println("Print query: "+query);
            System.out.println("Print s:"+s);
              /*        if (sorted)
                  query += " ORDER BY day, startTime"; */
                ResultSet rs = s.executeQuery(query);
            System.out.println("Print rs: "+rs);
                return mærkerAdd(rs);
        }
        catch (SQLException sqlex){
            return null;           
        }       
    }   
   
    //Convert database types (mærker) to String and read in
    private ArrayList mærkerAdd(ResultSet rs) throws SQLException {
        System.out.println("In mærkerAdd char to string conversion.");
        ArrayList rsArrayList=new ArrayList();
        //get meta data
        ResultSetMetaData rsmd=rs.getMetaData();
        //get row data
        while (rs.next()){
            String rowData="";
            for (int i=1;i<=rsmd.getColumnCount();++i){
                switch (rsmd.getColumnType(i)){
                case Types.VARCHAR:
                    rowData+=rs.getString(i).trim()+ " ";
                    break;
                default:
                    System.out.println("Type was: "
                            +rsmd.getColumnTypeName(i));
                }
            }
            rsArrayList.add(rowData);
        }
        return rsArrayList;
    }
       
   
    //SetNewRecord sli and sale
    public ArrayList setNewRecord(SalesLineItem sli, Sale sale){
        try {
            s.executeUpdate("INSERT INTO Sale"+sale);
            s.executeUpdate("INSERT INTO SalesLineItem"+sli);
            return Ok;
        }
        catch (SQLException sqlex){
            return null;
        }
    }
   
    //GetMiljøIDs
    public ArrayList getMiljøIDs(){
        try{
            System.out.println("In DBAdapter getMiljøIDs method.") ;
              String query =
                  "SELECT MiljøID FROM MiljøGruppe";
          System.out.println("Print query: "+query);
          System.out.println("Print s: "+s);
                ResultSet rs = s.executeQuery(query);
            System.out.println("Print rs: "+rs);
            return miljøIDsAdd(rs);
        }
        catch (SQLException sqlex){
            return null;
        }
    }
//    Convert database types (miljøIDs) to ArrayList and read in
    private ArrayList miljøIDsAdd(ResultSet rs) throws SQLException {
        System.out.println("In miljøIDsAdd ArrayList conversion.");
       
        ArrayList rsArrayList=new ArrayList();
        //get meta data
        ResultSetMetaData rsmd=rs.getMetaData();
        //get row data
        while (rs.next()){
            Object rowData = null;
            for (int i=1;i<=rsmd.getColumnCount();++i){
                switch (rsmd.getColumnType(i)){
                case Types.INTEGER:
                    rowData=rs.getObject(i);
                    System.out.println("MiljøID Integer type for rowData: "+rowData);
                    break;
                default:
                    System.out.println("Type was: "
                            +rsmd.getColumnTypeName(i));
                }
            }
            rsArrayList.add(rowData);
        }
        return rsArrayList;
    }
   
    //Get Kategorier
    public ArrayList getKategorier(String maerke) {
        try{
   
            System.out.println("In DBAdapter getKategorier method."+
                    "\nMærke value: "+maerke) ;           
              String query =                  
                  "SELECT kategori FROM ProduktSpecifikation "+
                  "INNER JOIN "+
                  "ProduktKatalog ON ProduktSpecifikation.katalogID "+
                  "=ProduktKatalog.KatalogID WHERE "+
                  "ProduktKatalog.mærke='"+maerke+"'";
                 
                System.out.println("Print query: "+query);
                System.out.println("Print s: "+s);
                    ResultSet rs1 = s.executeQuery(query);
                System.out.println("Print rs: "+rs1);
                           
//              System.out.println("Print query: "+query);     
//            ResultSet rs = s.executeQuery(query);
//            System.out.println("Print rs: "+rs);
   
                return kategorierAdd(rs1);
        }
        catch (SQLException sqlex){
            return null;
        }
    }
//    Convert database types (kategorierAdd) to String and read in
    private ArrayList kategorierAdd(ResultSet rs1) throws SQLException {
        System.out.println("In kategorierAdd char to string conversion.");
        ArrayList rsArrayList=new ArrayList();
        //get meta data
        ResultSetMetaData rsmd=rs.getMetaData();
        //get row data
        while (rs.next()){
            String rowData="";
            for (int i=1;i<=rsmd.getColumnCount();++i){
                switch (rsmd.getColumnType(i)){
                case Types.VARCHAR:
                    rowData+=rs.getString(i).trim()+"";
                    break;
                default:
                    System.out.println("Type was: "
                            +rsmd.getColumnTypeName(i));
                }
            }
            rsArrayList.add(rowData);
        }
        return rsArrayList;
    }

it has the information we need

but the error is not obvious

Here is the code that calls the method for mærke:

    private javax.swing.JComboBox getVælgMærke() {
        if(VælgMærke == null) {
           
            String[] string = e.arrayToGui();
            VælgMærke = new javax.swing.JComboBox(string);
            VælgMærke.setName("");
            VælgMærke.addItemListener(
                new ItemListener() {
                        public void itemStateChanged(java.awt.event.ItemEvent event) {
                            if( event.getStateChange() == ItemEvent.SELECTED){
                               
                                String maerke = VælgMærke.getSelectedItem().toString();                               
                                System.out.println(maerke);
                                maerke=maerke.trim();
                                //KontantSalgHandler h = new KontantSalgHandler();                               
                                ArrayList kategorier=h.enterMærke(maerke);
                                System.out.println("KontantSalg01 kategorier: "+kategorier);
                            }                           
                        }
            });       

           
        }
        return VælgMærke;
    }

Here are the error messages and printlns when we run the program:

In DBAdapter getMærker method.
Print query: SELECT mærke FROM ProduktKatalog
Print s in getMærker:sun.jdbc.odbc.JdbcOdbcStatement@86f241
Print rs: sun.jdbc.odbc.JdbcOdbcResultSet@18ac738
In mærkerAdd char to string conversion.
getMærker values [Goodyear , Continental , Michelin ]
In DBAdapter getMiljøIDs method.
Print query: SELECT MiljøID FROM MiljøGruppe
Print s in getMiljøIDs: sun.jdbc.odbc.JdbcOdbcStatement@86f241
Print rs: sun.jdbc.odbc.JdbcOdbcResultSet@bb6ab6
In miljøIDsAdd ArrayList conversion.
MiljøID Integer type for rowData: 1
MiljøID Integer type for rowData: 2
MiljøID Integer type for rowData: 3
MiljøID Integer type for rowData: 4
MiljøID Integer type for rowData: 5
MiljøID Integer type for rowData: 6
MiljøID Integer type for rowData: 7
getMiljøIDs values [1, 2, 3, 4, 5, 6, 7]
Outlist1 in getMærker: [Vælg Mærke, Goodyear , Continental , Michelin ]
OutList2 in getMiljøIDs:  [1, 2, 3, 4, 5, 6, 7]
In DBAdapter getMærker method.
Print query: SELECT mærke FROM ProduktKatalog
Print s in getMærker:sun.jdbc.odbc.JdbcOdbcStatement@1457cb
Print rs: sun.jdbc.odbc.JdbcOdbcResultSet@18fef3d
In mærkerAdd char to string conversion.
getMærker values [Goodyear , Continental , Michelin ]
In DBAdapter getMiljøIDs method.
Print query: SELECT MiljøID FROM MiljøGruppe
Print s in getMiljøIDs: sun.jdbc.odbc.JdbcOdbcStatement@1457cb
Print rs: sun.jdbc.odbc.JdbcOdbcResultSet@a3bcc1
In miljøIDsAdd ArrayList conversion.
MiljøID Integer type for rowData: 1
MiljøID Integer type for rowData: 2
MiljøID Integer type for rowData: 3
MiljøID Integer type for rowData: 4
MiljøID Integer type for rowData: 5
MiljøID Integer type for rowData: 6
MiljøID Integer type for rowData: 7
getMiljøIDs values [1, 2, 3, 4, 5, 6, 7]
Outlist1 in getMærker: [Vælg Mærke, Goodyear , Continental , Michelin ]
OutList2 in getMiljøIDs:  [1, 2, 3, 4, 5, 6, 7]
Michelin
In DBAdapter getKategorier method.
Mærke value: Michelin
Print query: SELECT kategori FROM ProduktSpecifikation INNER JOIN ProduktKatalog ON ProduktSpecifikation.katalogID =ProduktKatalog.KatalogID WHERE ProduktKatalog.mærke='Michelin'
Print s in getKategorier: null
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
    at eLarsen.ELarsenDBAdapter.getKategorier(ELarsenDBAdapter.java:158)
    at eLarsen.KontantSalgHandler.enterMærke(KontantSalgHandler.java:97)
    at eLarsen.Kontantsalg01$2.itemStateChanged(Kontantsalg01.java:364)
    at javax.swing.JComboBox.fireItemStateChanged(Unknown Source)
    at javax.swing.JComboBox.selectedItemChanged(Unknown Source)
    at javax.swing.JComboBox.contentsChanged(Unknown Source)
    at javax.swing.AbstractListModel.fireContentsChanged(Unknown Source)
    at javax.swing.DefaultComboBoxModel.setSelectedItem(Unknown Source)
    at javax.swing.JComboBox.setSelectedItem(Unknown Source)
    at javax.swing.JComboBox.setSelectedIndex(Unknown Source)
    at javax.swing.plaf.basic.BasicComboPopup$Handler.mouseReleased(Unknown Source)
    at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at javax.swing.plaf.basic.BasicComboPopup$1.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

Thank you for putting me on the right track. The problem is solved.

How do I assign these points to arne who put me on the right track to answering it?

I make a real "reply"