SQL DataSource Not Converting Boolean Correctly

chimpeenuts

Registered Developer

Join Date: Jul 2011
Posts: 100

SQL DataSource Not Converting Boolean Correctly

15 Feb 2012, 18:25

Using Smart GWT 3.0 nightlies. Power version.

Error:

Have a TreeGrid bound to the following data source:

Code:

<DataSource ID="categoryDevice" serverType="sql" tableName="categoryDevice"
  dropExtraFields="true">
  <fields>
    <field name="id" type="sequence" hidden="true" primaryKey="true" />
    <field name="title" type="text" title="Categories" />
    <field name="device_id" type="integer" foreignKey="device.id" />
    <field name="hostName" type="text" tableName="device" />
    <field name="userId" type="integer" />
    <field name="parentId" type="text" hidden="true" foreignKey="id" />
    <field name="category" type="boolean" hidden="true" />
    <field name="open" type="boolean" hidden="true" />
    <field name="locked" type="boolean" hidden="true" />
  </fields>

  <operationBindings>
    <operationBinding operationType="fetch">
      <tableClause>categoryDevice LEFT OUTER JOIN device ON
        categoryDevice.device_id = device.id</tableClause>
    </operationBinding>
  </operationBindings>


</DataSource>

The MySQL data table structure for the 'category' field is:

Code:

Field      Type   Length   Unsigned   Zerofill    Binary   Allow Null    Key  Default                                                                                         
category BIT    1

I am trying to programmatically add a record to this datasource/backend SQL table using the following client code:

Code:

Record newCategory = new Record();

newCategory.setAttribute("category", true);
newCategory.setAttribute("title", "New Category");
newCategory.setAttribute("userId", getCurrentUserId());
newCategory.setAttribute("locked", false);
newCategory.setAttribute("open", true);

_tree.addData(newCategory);

When the 'addData' method tries to execute, however, I get the following Warning popup in the UI:

Code:

Data truncation: Data too long for column 'category' at row 1

Here is the stack trace:

Code:

=== 2012-02-15 21:18:06,921 [l0-0] WARN  RequestContext - dsRequest.execute() failed: 
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'category' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3595)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at com.isomorphic.sql.SQLDriver.doUpdate(SQLDriver.java:528)
	at com.isomorphic.sql.SQLDriver.update(SQLDriver.java:482)
	at com.isomorphic.sql.SQLDriver.executeUpdate(SQLDriver.java:602)
	at com.isomorphic.sql.SQLDataSource.executeNativeUpdate(SQLDataSource.java:403)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1374)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:293)
	at com.isomorphic.sql.SQLDataSource.executeAdd(SQLDataSource.java:245)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1289)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1948)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:199)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:156)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:121)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1097)
	at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:259)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:324)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

What the error appears to be telling me is that the value 'true' for the column/field 'category' is *too long*. I think it's trying to send perhaps an Integer to a Bit database field? If I try to change the database column type from 'BIT' to say 'TINYINT' or 'INT', then I get this error:

Code:

Incorrect integer value: 'true' for column 'category' at row 1

Stack Trace:

Code:

=== 2012-02-15 21:25:28,101 [l0-2] WARN  RequestContext - dsRequest.execute() failed: 
java.sql.SQLException: Incorrect integer value: 'true' for column 'category' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at com.isomorphic.sql.SQLDriver.doUpdate(SQLDriver.java:528)
	at com.isomorphic.sql.SQLDriver.update(SQLDriver.java:482)
	at com.isomorphic.sql.SQLDriver.executeUpdate(SQLDriver.java:602)
	at com.isomorphic.sql.SQLDataSource.executeNativeUpdate(SQLDataSource.java:403)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1374)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:293)
	at com.isomorphic.sql.SQLDataSource.executeAdd(SQLDataSource.java:245)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1289)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1948)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:199)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:156)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:121)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1097)
	at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:259)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:324)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

Tags: None

Isomorphic

Administrator

Join Date: May 2006

Posts: 54656
#2

15 Feb 2012, 18:32

Have you looked at DataSourceField.sqlStorageStrategy?
Comment
chimpeenuts

Registered Developer

Join Date: Jul 2011

Posts: 100
#3

15 Feb 2012, 19:07

Thanks! That was exactly what I needed.

Setting

Code:

<field name="category" type="boolean" hidden="true" sqlStorageStrategy="number" />

did the trick!
Comment

Previous template Next

Announcement

SQL DataSource Not Converting Boolean Correctly

Comment

Comment