current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 2 down vote favorite

I am trying to write some values in a MS Access database using Jackcess. My values are originally represented using String. The code I am using is the following:

int nColumns = 0;

// get table from internal representation
ModelDatabaseTable table = this.DB.getTable(tableName);

// create new table
TableBuilder DBTableBuilder = new TableBuilder(tableName);

// get table's columns and their Jackcess datatype    
Map<String, DataType> columns = table.getColumns();

// for each column, insert it in the actual database
for (String columnName : columns.keySet()) {
    DataType dt = columns.get(columnName);
    ColumnBuilder cb = new ColumnBuilder(columnName).setType(dt);
    if (dt.isVariableLength()) {
        cb.setMaxLength();
    }
    DBTableBuilder.addColumn(cb);
    nColumns += 1;
}

// if columns were inserted
if (nColumns > 0) {
    // write table to actual database
    Table DBTable = DBTableBuilder.toTable(this.DBConnection);

    // for each row
    for (ModelDatabaseRow row : table.getRows()) {

        // get list of values (represented using String)
        List<String> values = new ArrayList<String>();

        // for each column get its value and insert it in values
        for (String columnName : columns.keySet()) {
            String columnValue = row.getColumn(columnName);
            values.add(columnValue);
        }

        // print current row
        System.out.println(values.toString());

        // insert row in database table. Exception rises here:
        // java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Boolean
        DBTable.addRow(values.toArray());
    }
}

A basic example which does not work is the following (routine data is described using JSON). In this case the routine stops when trying to insert BOOLEAN values (HasM, HasZ) but it is able to insert DOUBLE values - which are all given as parameters to Table.addRow() function as a String array. 

{
  "tables": {
    "Table1": {
      "rows": [
      {
        "items": {
        "ExtentBottom": "45.050715999999994",
        "ExtentLeft": "7.644834000000003",
        "ExtentRight": "7.670400999999998",
        "ExtentTop": "45.07392899999999",
        "FieldName": "Shape",
        "HasM": "false",
        "HasZ": "false",
        "IdxGridSize": "3.7252903001966386E-7",
        "IdxOriginX": "0.0",
        "IdxOriginY": "0.0",
        "MHigh": "NaN",
        "MLow": "NaN",
        "SRID": "1",
        "ShapeType": "4",
        "TableName": "GDB_Items",
        "ZHigh": "NaN",
        "ZLow": "NaN"
        }
      },
      {
        "items": {
        "ExtentBottom": "4989476.8181",
        "ExtentLeft": "393329.1171000004",
        "ExtentRight": "395300.25320000015",
        "ExtentTop": "4992023.569399999",
        "FieldName": "Shape",
        "HasM": "false",
        "HasZ": "false",
        "IdxGridSize": "0.009311329524584121",
        "IdxOriginX": "0.0",
        "IdxOriginY": "0.0",
        "MHigh": "NaN",
        "MLow": "NaN",
        "SRID": "2",
        "ShapeType": "4",
        "TableName": "Building_DIMMER_01",
        "ZHigh": "NaN",
        "ZLow": "NaN"
        }
      }
      ],
      "columns": {
      "ExtentBottom": "DOUBLE",
      "ExtentLeft": "DOUBLE",
      "ExtentRight": "DOUBLE",
      "ExtentTop": "DOUBLE",
      "FieldName": "TEXT",
      "HasM": "BOOLEAN",
      "HasZ": "BOOLEAN",
      "IdxGridSize": "DOUBLE",
      "IdxOriginX": "DOUBLE",
      "IdxOriginY": "DOUBLE",
      "MHigh": "DOUBLE",
      "MLow": "DOUBLE",
      "SRID": "LONG",
      "ShapeType": "LONG",
      "TableName": "TEXT",
      "ZHigh": "DOUBLE",
      "ZLow": "DOUBLE"
      } 
    }
  }
}

The preceding JSON represents the internal representation of data used by my program and it is structured in this way:

{
  "tables": {
    "TableName": {
      "rows": [
        {
          "items: {
            "columnName1": "columnValue1",
            ...
            "columnNameN": "columnValueN"
           }
        },
        {
          "items: {
            "columnName1": "columnValue1",
            ...
            "columnNameN": "columnValueN"
           }
        }
      ],
      "columns": {
        "columnName1": "columnDataType1",
        ...
        "columnNameN": "columnDataTypeN"
      }
    }
  }
}

In case it is not clear ask me,

Thanks

share|improve this question
    
The JSON boolean values are true and false (without the quotes) if that helps ... – Johann-Christoph Jacob Jan 30 '15 at 13:35
1  
The error seems pretty clear to me. Parse the Boolean columns from String before attempting to insert them into your Access table. What isn't clear about the Exception? You mentioned that Double worked as you expected, but that's got to be how your database schema is defined (note, you did not post your schema). – Elliott Frisch Jan 30 '15 at 13:35
    
@Johann-ChristophJacob Thanks, but in this case all data types are represented as String (I used JSON only to visualize them).. @ElliottFrisch The fact that is not clear to me is that numbers LONG and DOUBLE are automatically parsed from their String representation, BOOLEAN are not. Yes, I did not post the schema because I am not able to create it right now. However, the data types of the field are presented at the end of the second code section. Tell me if it is not clear.. – fbrundu Jan 30 '15 at 13:55
up vote 2 down vote accepted

Jackcess normally expects to work with strongly-typed Java objects. In this case Jackcess must be trying to be helpful by automatically converting a String to a Double when adding the Row. For whatever reason, Jackcess is not willing to automatically convert a String to a Boolean*, even if that conversion seems obvious (to us), so for a table with fields

ID: AutoNumber, Primary Key
DoubleField: Numeric(Double)
YesnoField: Yes/No

the following code will throw the exception you are seeing

String doubleFieldValue = "3.14159";
String yesnoFieldValue = "true";
tbl.addRow(Column.AUTO_NUMBER, doubleFieldValue, yesnoFieldValue);

However, this will work

String doubleFieldValue = "3.14159";
String yesnoFieldValue = "true";
tbl.addRow(Column.AUTO_NUMBER, doubleFieldValue, Boolean.parseBoolean(yesnoFieldValue));

As a general rule it is best to try and use objects of the correct type when working with Jackcess to avoid little "surprises" like this, so by rights we really should be using

String doubleFieldValue = "3.14159";
String yesnoFieldValue = "true";
tbl.addRow(Column.AUTO_NUMBER, Double.parseDouble(doubleFieldValue), Boolean.parseBoolean(yesnoFieldValue));

* Edit: 2015-03-21

Automatic conversion of String values to Boolean was added to Jackcess 2.0.9, released today.

share|improve this answer
up vote 0 down vote

On the line:

List<String> values = new ArrayList<String>();

you specifically specify you want values to only contain String objects, if you want to insert Booleans you need a more generic object declaration.

List<Object> values = new ArrayList<Object>();

though you'll probably lose some needed string functionality.

share|improve this answer
    
Unfortunately it does not work – fbrundu Jan 30 '15 at 14:38
    
hmm, well its kind of hacky but you could, on the line before you insert, test the bool condition and depending on the case, insert a string 'true' or 'false' instead? edit: i see you are inserting 2 booleans, in that case* a 4 way switch startement (true,true.......False,False) and insert the strings. hacky but should get you going. *heh – Bryan Devaney Feb 2 '15 at 10:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.