Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 4 of 4

Thread: Access database SQL query help

  1. #1
    Member
    Join Date
    Jun 2010
    Posts
    75
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default 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.


  2. #2
    Super Moderator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,235
    Thanks
    176
    Thanked 817 Times in 760 Posts
    Blog Entries
    5

    Default Re: Access database SQL query help

    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...

  3. #3
    Member
    Join Date
    Jun 2010
    Posts
    75
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default Re: Access database SQL query help

    Quote Originally Posted by copeg View Post
    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.

  4. #4
    Super Moderator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,235
    Thanks
    176
    Thanked 817 Times in 760 Posts
    Blog Entries
    5

    Default Re: Access database SQL query help

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

Similar Threads

  1. Replies: 6
    Last Post: August 18th, 2010, 05:41 PM
  2. [SOLVED] Interesing JButton Query
    By ravjot28 in forum AWT / Java Swing
    Replies: 2
    Last Post: January 14th, 2010, 10:19 AM
  3. Default Access (package access) confusion
    By gauravrajbehl in forum Java Theory & Questions
    Replies: 1
    Last Post: November 18th, 2009, 03:11 AM
  4. How can i store ArrayList objects in Access database
    By frankycool in forum JDBC & Databases
    Replies: 0
    Last Post: November 3rd, 2009, 11:44 PM
  5. access database connectivity from outside an application
    By suchirag in forum JDBC & Databases
    Replies: 0
    Last Post: October 29th, 2009, 02:03 AM