Problem With Inserting

[Brandon Tom]

So here's the code:

/*
 * JDBCTest.java
 *
 * Created on February 15, 2006, 3:04 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
 
import java.sql.*;
 
/**
 *
 * @author Brandon Tom
 */
public class JDBCTest {
    
    /** Creates a new instance of JDBCTest */
    public JDBCTest() {
    }
    
    public static void main ( String [] parameters )
    {
        try
        {
            Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
            
            String dataSourceName = "TestDatabase";
            String dataSourceURL = "jdbc :-o	 dbc:" + dataSourceName;
            
            Connection con = DriverManager.getConnection( dataSourceURL, "", "" );
            
            Statement s = con.createStatement();
            s.execute( "INSERT INTO TestTable ( SomeString ) VALUES( \"Hello\" )" );
            
            con.close();
        }
        catch ( Exception ex )
        {
            ex.printStackTrace ();
        }
    }
    
}

It compiles fine, but when I try to run it, an exception is thrown.

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
        at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
        at JDBCTest.main(JDBCTest.java:34)

I'm REALLY new with JDBC, so I have no idea what this "Too few parameters" deal is all about.

Thanks!

[Jeff Albertson]

It's never too early to learn how to use prepared statements:

String sql = "insert into TestTable ( SomeString ) values ( ? )";
PreparedStatement s = con.prepareStatement(sql);
s.setString(1, "Hello");
s.executeUpdate();

Tutorial : Using Prepared Statements

[stu derby]

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

The MS driver and/or database is really really really stupid in certain repects; it reports that error in way too many cases when the real problem is either bad SQL or a mismatch between the SQL and the table structure.

In your case, it's bad SQL, change \"Hello\" to 'Hello'.

Actually, now is the time to learn one of the most important things you need to know about JDBC and the number one beginner mistake - you should usually use a PreparedStatement instead of a Statement. The reasons are many and very very very substantial; performance, security, and ease of coding top the list. For example, Google for: JDBC "sql injection"

Here's how you should do it:

            String sql = "INSERT INTO TestTable ( SomeString ) VALUES( ? )";
            PreparedStatement s = con.prepareStatement(sql);
            s.setString(1,"Hello");
            s.executeUpdate(  )" );

[ February 15, 2006: Message edited by: stu derby ]

[Brandon Tom]

Cool. The book I have is just getting to prepared statements.

[Brandon Tom]

Oddly enough, I'm getting an error that's saying that the JDBC-ODBC bridge does not support prepared statements =(

[Ulf Dittmer]

Stus advice on using PreparedStatement is a good one. Here's another one: Avoid the JDBC/ODBC bridge. It's slow, it's buggy, it doesn't support a number of JDBC features, and doesn't multi-thread well, if at all. Use a real driver (unless you're using Access, in which case I think there aren't any other drivers).

[Brandon Tom]

I am, in fact, using Access, but really only for learning purposes. All is not lost however, I'm downloading MS SQL Express Server 2005 and the 350 megabyte .NET Framework 2.0 SDK required to run it on right. *twiddles thumbs patiently*

I guess I could always just download MySQL. There's a type 4 JDBC for that right?

[stu derby]

Originally posted by Brandon Tom:
I guess I could always just download MySQL. There's a type 4 JDBC for that right?

Yep. There are/were a dozen or so other drivers for Access of varying quality, but I think they're all commercial. Sun has a partial driver list here:
http://developers.sun.com/product/jdbc/drivers

Other good free choices for self education that I know of these days are:

Totaly free (as in beer):
Postgres (some people swear it's better than mysql, but it doesn't have the broad user community)
Derby (an Apache project; derived from an IBM donation)
Hypersonic (HSQLDB)

Free (as in beer) with significant restrictions:
Oracle
(either any version, single user development only license, or the new free Express Edition (in public beta for Linux and Windows), it's multi-user, missing a number of uber-advanced features and has a 4GB size limit on the DB and other restrictions, but is also free for commercial or other use, with no support)
http://www.oracle.com/technology/products/database/xe/index.html

They all have their own quirks: Oracle's got the biggest learning curve, Derby and