Thread: Access database SQL query help

I seem to be having to trouble constructing a query that will execute on a database I have built:

import java.net.*;
import java.sql.*;
import java.io.*;
import java.util.*;
 
class MakeDB
{
 
     public static void main (String args[])
     {
          try
          {
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // force loading of driver
               String url = "jdbc:odbc:AMINO";
               String user = "";
               String password = "";
               Connection con = DriverManager.getConnection(url,user,password);
               Statement stmt = con.createStatement();
               stmt.executeUpdate("CREATE TABLE AMINO_ABBREV (AMINO_NAME VARCHAR(13), ONE_LTR VARCHAR(1), THREE_LTR VARCHAR(3))");
 
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Alanine', 'A', 'Ala')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Arginine', 'R', 'Arg')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Asparigine', 'N', 'Asn')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Aspartic Acid', 'D', 'Asp')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Cysteine', 'C', 'Cys')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Glutamine', 'Q', 'Gln')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Glutamic Acid', 'E', 'Glu')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Glcyine', 'G', 'Gly')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Histidine', 'H', 'His')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Isoleucine', 'I', 'Ile')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Leucine', 'L', 'Leu')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Lysine', 'K', 'Lys')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Methionine', 'M', 'Met')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Phenylalanine', 'F', 'Phe')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Proline', 'P', 'Pro')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Serine', 'S', 'Ser')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Threonine', 'T', 'Thr')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Tryptophan', 'W', 'Trp')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Tyrosine', 'Y', 'Tyr')");
               stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Valine', 'V', 'Val')");
 
               ResultSet r = stmt.executeQuery("SELECT AMINO_NAME, ONE_LTR, THREE_LETTER FROM AMINO_ABBREV");
               System.out.println("**ResultSet created");
               while(r.next())
               {
                    String aminoName = r.getString("AMINO_NAME");
                    String oneLtr = r.getString("ONE_LTR");
                    String threeLtr = r.getString("THREE_LTR");
                    String spcr = "          ";
                    System.out.println(aminoName + " " + oneLtr + " " + threeLtr);
               }
 
               /*stmt.executeUpdate("INSERT INTO AMINO_ABBREV VALUES('Selenocysteine', 'U', 'Sec')");
 
               r=stmt.executeQuery("SELECT AMINO_NAME, ONE_LTR, THREE_LETTER FROM AMINO_ABBREV WHERE AMINO_NAME < 'Phenylalanine'");
               while(r.next())
               {
                    String aminoName = r.getString("AMINO_NAME");
                    String oneLtr = r.getString("ONE_LTR");
                    String threeLtr = r.getString("THREE_LTR");
                    String spcr = "          ";
                    System.out.println(aminoName + " " + oneLtr + " " + threeLtr);
               }*/
 
               stmt.close();
               con.close();
          }
          catch (Exception ex)
          {
               System.out.println ("Exception "+ ex);
          }
     }
}

When I try to run the application it throws this exception:

Exception java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

I'm not quite sure what is wrong, because I had an early version of the program that executed without throwing an exception, but I seem to have broken it when I added the section of code the I commented out.

Without knowing the line the error occurs on its just a guess, so my guess is the column names: THREE_LTR versus your SELECT which specifies a column name THREE_LETTER. For what its worth, I'd recommend creating another column for a primary key. A bit more advanced, but from there you can join some tables and do some fancy things...and that's where the fun begins because you can start building/specifying Proteins, sequences, dna sequences, etc...

Originally Posted by copeg

Without knowing the line the error occurs on its just a guess, so my guess is the column names: THREE_LTR versus your SELECT which specifies a column name THREE_LETTER. For what its worth, I'd recommend creating another column for a primary key. A bit more advanced, but from there you can join some tables and do some fancy things...and that's where the fun begins because you can start building/specifying Proteins, sequences, dna sequences, etc...

I should've been more specific. The JVM itself doesn't return a line number for the database error, but it seems (from printing test statements such as "**ResultSet created") that the exception is thrown when the program creates the ResultSet. In other words, the program appears to build the database without a problem; it is the query itself that seems to be the problem.

NOTE:

I don't actually know if the database is being built as I specified; I am just assuming that it is so because there are no overt errors in the program until I query the database and, before I added the commented-out code, the query work as I wanted it to.

Re-read my first post, specifically the column naming in your select statement THREE_LETTER != THREE_LTR