Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Oct 2010
    Location
    Texas
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Writing an MSAccessdatabase through Java, problems with SQL statements

    USING JCREATOR
    I have gotten a connection and created 3 tables and dropped one. I've gotten it to let me insert an integer value into the only column
    Code:
    s.execute("insert into STUDENTS values(1)");
    for the first column. I created a second table with multiple columns but I cannot figure out how to update one or all of the columns. How do I do that? I've tried many different techniques. Also, after I had created three tables it wouldn't let me create any more, only drop tables.
    Code:
    s.execute("create table STUDENTS (ID integer)");
    s.execute("drop table STUDENTS");
    What is causing this?
    Last edited by ashley.morgan; 10-08-2010 at 04:56 AM. Reason: Specification

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Access definitely isn't limited to only a few tables. Are you certain you are catching the SQL exception and printing it out? The only thing I can think of on a direct creation query is that you are trying to reuse an existing table, using reserved words, using bad datatypes, or have bad SQL structure.

    This isn't a java question, this is a database one. The problem isn't with Java.
    To do multiple insertions, you simply state in your queries what columns (optional if you are writing to all columns, required if you are not writing to null fields) and the associated data. Access (from what I can tell) does not allow multiple insertions (except from a select).
    Updates are simply done with the UPDATE {tablename} SET {columnName} = value WHERE {condition} syntax. Separate multiple columnName = value pairs with commas. Be aware of your datatypes though, if its a string you are expected to wrap it in single quotations.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    It's not required, but it's a really really really good idea to list all the columns you are inserting into.

    Example:
    Code:
    s.execute("create table STUDENTS (ID integer, name VARCHAR(50), grade double)");
    s.execute("insert into STUDENTS (id,name,grade) VALUES(1,'adam',3.5)");
    s.execute("insert into STUDENTS (id,name,grade) VALUES(2,'bob',2.723)");
    s.execute("insert into STUDENTS (id,name,grade) VALUES(3,'carl',1.92)");
    Also, unlike MySQL, if you have an autonumber field you *CAN NOT* mention it in the INSERT, which effectively *forces* you to explicitly call out the other columns:
    Code:
    s.execute("create table STUDENTS (ID counter, name VARCHAR(50), grade double)");
    s.execute("insert into STUDENTS (name,grade) VALUES('adam',3.5)");
    s.execute("insert into STUDENTS (name,grade) VALUES('bob',2.723)");
    s.execute("insert into STUDENTS (name,grade) VALUES('carl',1.92)");
    COUNTER is (one of) Access's alias for auto-number.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    ashley.morgan (10-13-2010)

  • #4
    New to the CF scene
    Join Date
    Oct 2010
    Location
    Texas
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    When I got my code to work, I tried to integrate it with a GUI and put the connection and sql statements in a method

    private void updater(String str1, String str2, String sr3, String str4)
    {
    try
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String dataSourceName = "mdbTEST";
    String dbURL = "jdbcdbc:" + dataSourceName;
    Connection con = DriverManager.getConnection(dbURL, "Admin","");
    Statement s = con.createStatement();

    s.execute("insert into s1 (CLASS1, CLASS2, CLASS3, CLASS4) VALUES('" + str1 +"', '"+ str2 +"', '"+ str3 +"','"+ str4+"')");
    s.execute("insert into s1 (CLASS1, CLASS2, CLASS3, CLASS4) VALUES('BLANK', 'BLANK', 'BLANK', 'BLANK'");
    }
    catch (Exception e)
    {
    System.out.println("Error: " + e);
    }


    and just called the method with the parameters I needed. I am getting this exception,

    java.secutity.AccessControlException: access denied <java.lang.RuntimePermission accessClassInPackage.sun.jdbc.odbc>

    Can you tell me how I can fix this please?


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •