Unable to execute a simple insert

[Jonathan Bouzekri]

Hi,

I am trying to use this wonderful library but I cannot see what I do wrong. I am using this library v4.42 with sqlite-jdbc-3.6.16.
I have a class book with a primary key integer auto increment and a field varchar title.

I copied your exemple code but when I try to persist my Book object, I have the following error :
2013-01-05 17:39:51,805 [DEBUG] DaoManager created dao for class class net.bouzekri.mymediacollection.Book with reflection
2013-01-05 17:39:51,807 [INFO] TableUtils creating table 'book'
2013-01-05 17:39:51,956 [DEBUG] JdbcConnectionSource opened connection to jdbc:sqlite:data.db got #42596014
2013-01-05 17:39:51,962 [INFO] TableUtils executed create table statement changed 0 rows: CREATE TABLE IF NOT EXISTS `book` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `title` VARCHAR )
Jan 05, 2013 5:39:51 PM net.bouzekri.mymediacollection.App main
SEVERE: null
java.sql.SQLException: Unable to run insert stmt on object net.bouzekri.mymediacollection.Book@5ca40b6e: INSERT INTO `book` (`title` ) VALUES (?)
    at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:22)
    at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:124)
    at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:394)
    at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:308)
    at net.bouzekri.mymediacollection.App.main(App.java:37)

It seems that there is

My Book class :
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;

/**
 * @author jobou
 */
@DatabaseTable(tableName = "book")
public class Book {
   
    @DatabaseField(generatedId = true)
    private int id;
   
    @DatabaseField
    private String title;
   
    public Book() {
    }
   
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
   
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
}

And my main :
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class App
{
    public static void main( String[] args )
    {
        try {
            String databaseUrl = "jdbc:sqlite:data.db";
           
            ConnectionSource connectionSource = new JdbcConnectionSource(databaseUrl);

            Dao<Book, Integer> bookDao = DaoManager.createDao(connectionSource, Book.class);

            TableUtils.createTableIfNotExists(connectionSource, Book.class);

            Book book = new Book();
            book.setTitle("My first book");

            // persist the account object to the database
            bookDao.create(book);

            // close the connection source
            connectionSource.close();
        } catch (SQLException ex) {
            Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

Do you have an idea ?

Another thing, I tried to use the version 4.9 but there is no DaoManager class. Is this normal ?

Thanks in advance

[Jonathan Bouzekri]

I didn't post all the stacktrace :
Caused by: java.sql.SQLException: NYI
    at org.sqlite.Conn.prepareStatement(Conn.java:443)
    at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:164)
    at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:89)
    ... 3 more

NYI means Not Yet Implemented.
After trying the v3.6.20 of the jdbc driver. I don't have the problem.
Hope it helps another one.

So, I am going to try another embedded database system because the development of the sqlite jdbc driver has stopped since 2011.

[Gray Watson]

Can't be of much help here Jonathan except to have you make sure you are using the right Sqlite driver. See the docs:

http://ormlite.com/docs/sqlite

Make sure you use the Xerial driver:

http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC

gray

[Gray Watson]

On Jan 5, 2013, at 11:50 AM, Jonathan Bouzekri wrote:

> Another thing, I tried to use the version 4.9 but there is no DaoManager class. Is this normal ?

DaoManager was added in 4.16. 4.9 is very old.
gray

[Jonathan Bouzekri]

Hi, thanks for your reply.

Indeed I am using the right driver :
    <dependency>
        <groupId>com.j256.ormlite</groupId>
        <artifactId>ormlite-jdbc</artifactId>
        <version>4.42</version>
    </dependency>
   
    <dependency>
      <groupId>org.xerial</groupId>
      <artifactId>sqlite-jdbc</artifactId>
      <version>3.7.2</version>
    </dependency>

For the v4.9, my bad, when i opened the maven repository url, I first took the last items without thinking :)

The problem was because of the sqllite driver version. In 3.6.16, the autoincrement option was not supported. After trying the 3.6.20, everything work perfectly.

Thanks.