[EoDsql-users] SQL Exception on insertion

[Ricardo P. <rpm...@ya...>]

Hi,

I'm being unable to properly process insertions on a Derby DB table. I
MUST be doing something wrong, but I'm at a dead point, sorry.

This is the POJO to be persisted, including the BaseQuery extending
interface:

package pruebaswing.datamodel;

import java.sql.SQLException;
import net.sf.eodsql.AutoGeneratedKeys;
import net.sf.eodsql.BaseQuery;
import net.sf.eodsql.DataSet;
import net.sf.eodsql.GeneratedKeys;
import net.sf.eodsql.ResultColumn;
import net.sf.eodsql.Select;
import net.sf.eodsql.Update;

public class Product {
    private int id;
    private String prodCode;
    private String prodName;
    private String url;
    private String notes;

    public Product() {
    }

    public int getId() {
        return id;
    }

    @AutoGeneratedKeys
    public void setId(int val) {
        this.id = val;
    }

    public String getProdCode() {
        return prodCode;
    }

    @ResultColumn("PRODCODE")
    public void setProdCode(String val) {
        this.prodCode = val;
    }

    public String getProdName() {
        return prodName;
    }

    @ResultColumn("PRODNAME")
    public void setProdName(String val) {
        this.prodName = val;
    }

    public String getUrl() {
        return url;
    }

    @ResultColumn("PRODURL")
    public void setUrl(String val) {
        this.url = val;
    }

    public String getNotes() {
        return notes;
    }

    @ResultColumn("PRODNOTES")
    public void setNotes(String val) {
        this.notes = val;
    }

    public String toString() {
        return getProdCode();
    }

    public interface ProductQuery extends BaseQuery {
        @Select("SELECT * FROM products WHERE id = ?1")
        public DataSet<Product> getProductById(int id);

        @Select("SELECT * FROM products WHERE prodcode = ?1")
        public DataSet<Product> getProductByCode(String code);

        @Select("SELECT * FROM products ORDER BY prodcode")
        public DataSet<Product> getAllProducts();

        @Update("UPDATE products SET prodcode = ?1, prodname = ?2, " +
                "produrl = ?3 WHERE id = ?4")
        public int updateProduct(String prodcode, String prodname,
                                 String url, int id)
                                 throws SQLException;

        @Update("DELETE products WHERE ID = ?1")
        public int deleteProduct(int id) throws SQLException;


//      @Update(sql="INSERT INTO products (prodcode, prodname) " +
//              "VALUES (?1, ?2)",
//              keys = GeneratedKeys.RETURNED_KEYS_DRIVER_DEFINED)
        @Update(sql="INSERT INTO products (prodcode, prodname) " +
                "VALUES (?1, 'Mozilla Thunderbird')",
                keys = GeneratedKeys.RETURNED_KEYS_DRIVER_DEFINED)
        public int insertProduct(String prodCode, String prodName)
                throws SQLException;
    }
}


Please note that I'm playing with the SQL INSERT command line to
narrow the problem as much as possible... and I've ended with two
different problems. :-(

With the currently active command, I get this exception:

java.sql.SQLException
at
net.sf.eodsql.DataMappingTool.createMappedObject(DataMappingTool.java:82)
at
net.sf.eodsql.QueryInvokationHandler$Handler.handleResultSet(QueryInvokationHandler.java:186)
at net.sf.eodsql.UpdateMethodImpl.invoke(UpdateMethodImpl.java:83)
at
net.sf.eodsql.QueryInvokationHandler.invoke(QueryInvokationHandler.java:70)
at $Proxy7.insertProduct(Unknown Source)
at pruebaswing.datamodel.ProductList.addElement(ProductList.java:52)
(...)
Caused by: java.lang.InstantiationException: int
at java.lang.Class.newInstance0(Class.java:335)
at java.lang.Class.newInstance(Class.java:303)
at
net.sf.eodsql.DataMappingTool.createMappedObject(DataMappingTool.java:77)

...which seems to be, at SF SVN repository, this code:

75     public T createMappedObject(ResultSet results)
                throws SQLException {
76         try {
77             T obj = type.newInstance();

I don't really know what is causing this exception to happen, sorry.

If I switch the commented parts (I don't actually comment in and out,
I change the SQL command back and forth) so the active annotation is this:

      @Update(sql="INSERT INTO products (prodcode, prodname) " +
              "VALUES (?1, ?2)",
              keys = GeneratedKeys.RETURNED_KEYS_DRIVER_DEFINED)

then I get this:

ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 54.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
(...)
at net.sf.eodsql.EoDQuery.createStatement(EoDQuery.java:93)
at net.sf.eodsql.UpdateMethodImpl.invoke(UpdateMethodImpl.java:72)
at
net.sf.eodsql.QueryInvokationHandler.invoke(QueryInvokationHandler.java:70)
at $Proxy7.insertProduct(Unknown Source)
at pruebaswing.datamodel.ProductList.addElement(ProductList.java:52)

I've made sure that no empty values are passed to the INSERT
annotation, and that I'm not failing to pass any constraint in the DB.
When I replaced parameters by literals, everything went fine
(although, of course, it is not very useful). :-)

I've also tried to single-quote the parameters ("VALUES (?1, '?2')"),
because the second one will often contain spaces, but then I get an
SQLException telling me that the prepared statement only has one
parameter. I'm a bit stuck with this and unable to see what I'm doing
wrong. Can anyone help me?

TIA