• Sign In
  1. Messages

Help with javascript API's insertRows()

LabKey Support Forum (Inactive)

View Message

view list print
Help with javascript API's insertRows() tstellin  2012-12-21 08:47
Status: Closed
 
Hi,

I'm developing a labkey 12.3 w/Ext4 file module that inserts rows into a table in a user-created schema (in a postgres db). The table contains multiple date columns whose dates are collected via a Ext form with datefield xtype widgets. Every time I try to insert I get this error:

ERROR: column "date_used" is of type timestamp without time zone but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 240

Where "date_used" could be any one of the date columns. I tried creating my table with date columns of type "date," "timestamp without time zone," and "timestamp with time zone." I get the same error every time. I tried getting the values for each date with form.getValues() and creating a new Date() object from each date field, but got the same error. I also tried getting each Ext datefield widget individually by name and used its getValue() method, but got the same error. When I inspect the values just before LABKEY.Query.insertRows, each date field's value is a Date() object (although I did try using plain date strings as well). Any ideas as to what I could be doing wrong?
 
 
Matthew Bellew responded:  2012-12-21 09:07
It doesn't sound like you're doing anything wrong. It sounds like the error is that somewhere we are asking Postgres to do an implicit conversion from string->date and it doesn't want to. We do usually try do convert to the correct type before handing off to Postgres.

Can you send me the .sql script you used to create your table? If you can find this error in the log and send that along that will help a lot.

Matt
 
tstellin responded:  2012-12-21 09:30
Here’s my table definition:

CREATE TABLE specimen_qc.lot_details(
    lot_number text
  , lot_prepared timestamp without time zone NOT NULL
  , parallel_testing_date_1 timestamp without time zone NOT NULL
  , parallel_testing_date_2 timestamp without time zone NOT NULL
  , date_approved_for_use timestamp without time zone NOT NULL
  , approved_by userid NOT NULL
  , date_used timestamp without time zone NOT NULL
  , comment text
  , createdby userid
  , created timestamp
  , modifiedby userid
  , modified timestamp
  , container entityid NOT NULL
  , CONSTRAINT PK_lot_details PRIMARY KEY (lot_number)
);

Here’s the error from tomcat’s logs:

ERROR Table 2012-12-21 09:28:03,239 http-8080-1 : SQL Exception
org.postgresql.util.PSQLException: ERROR: column "date_used" is of type timestamp without time zone but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 240
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at org.labkey.api.data.dialect.StatementWrapper.execute(StatementWrapper.java:790)
    at org.labkey.api.data.Table.insert(Table.java:985)
    at org.labkey.api.query.DefaultQueryUpdateService._insert(DefaultQueryUpdateService.java:264)
    at org.labkey.api.query.DefaultQueryUpdateService.insertRow(DefaultQueryUpdateService.java:223)
    at org.labkey.api.query.AbstractQueryUpdateService._insertRowsUsingInsertRow(AbstractQueryUpdateService.java:297)
    at org.labkey.api.query.AbstractQueryUpdateService.insertRows(AbstractQueryUpdateService.java:371)
    at org.labkey.query.controllers.QueryController$CommandType$1.saveRows(QueryController.java:2757)
    at org.labkey.query.controllers.QueryController$BaseSaveRowsAction.executeJson(QueryController.java:2949)
    at org.labkey.query.controllers.QueryController$InsertRowsAction.execute(QueryController.java:3016)
    at org.labkey.query.controllers.QueryController$InsertRowsAction.execute(QueryController.java:3009)
    at org.labkey.api.action.ApiAction.handlePost(ApiAction.java:152)
    at org.labkey.api.action.ApiAction.handleRequest(ApiAction.java:87)
    at org.labkey.api.action.BaseViewAction.handleRequestInternal(BaseViewAction.java:177)
    at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
    at org.labkey.api.action.SpringActionController.handleRequest(SpringActionController.java:353)
    at org.labkey.api.module.DefaultModule.dispatch(DefaultModule.java:921)
    at org.labkey.api.view.ViewServlet.service(ViewServlet.java:164)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.labkey.api.data.TransactionFilter.doFilter(TransactionFilter.java:36)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.labkey.core.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:118)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.labkey.api.module.ModuleLoader.doFilter(ModuleLoader.java:791)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.labkey.api.security.AuthFilter.doFilter(AuthFilter.java:170)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:679)
ERROR Table 2012-12-21 09:28:03,241 http-8080-1 : SQL [8999]
    INSERT INTO specimen_qc.lot_details
        (lot_number, lot_prepared, parallel_testing_date_1, parallel_testing_date_2, date_approved_for_use, date_used, comment, createdby, created, modifiedby, modified, container)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ?[1] 999005006.V3
    ?[2] Thu Dec 06 00:00:00 PST 2012
    ?[3] Thu Dec 06 00:00:00 PST 2012
    ?[4] Thu Dec 06 00:00:00 PST 2012
    ?[5] Thu Dec 06 00:00:00 PST 2012
    ?[6] 2012-12-06T08:00:00.000Z
    ?[7] test
    ?[8] 8654
    ?[9] 2012-12-21 09:28:03.237
    ?[10] 8654
    ?[11] 2012-12-21 09:28:03.237
    ?[12] cc98654e-5fc2-102c-8aed-d0cf379742c5

    org.labkey.api.query.DefaultQueryUpdateService._insert(DefaultQueryUpdateService.java:264)
    org.labkey.api.query.DefaultQueryUpdateService.insertRow(DefaultQueryUpdateService.java:223)
    org.labkey.api.query.AbstractQueryUpdateService._insertRowsUsingInsertRow(AbstractQueryUpdateService.java:297)
    org.labkey.api.query.AbstractQueryUpdateService.insertRows(AbstractQueryUpdateService.java:371)
    org.labkey.query.controllers.QueryController$CommandType$1.saveRows(QueryController.java:2757)
WARN ApiAction 2012-12-21 09:28:03,242 http-8080-1 : ApiAction exception:
org.postgresql.util.PSQLException: ERROR: column "date_used" is of type timestamp without time zone but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 240
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at org.labkey.api.data.dialect.StatementWrapper.execute(StatementWrapper.java:790)
    at org.labkey.api.data.Table.insert(Table.java:985)
    at org.labkey.api.query.DefaultQueryUpdateService._insert(DefaultQueryUpdateService.java:264)
    at org.labkey.api.query.DefaultQueryUpdateService.insertRow(DefaultQueryUpdateService.java:223)
    at org.labkey.api.query.AbstractQueryUpdateService._insertRowsUsingInsertRow(AbstractQueryUpdateService.java:297)
    at org.labkey.api.query.AbstractQueryUpdateService.insertRows(AbstractQueryUpdateService.java:371)
    at org.labkey.query.controllers.QueryController$CommandType$1.saveRows(QueryController.java:2757)
    at org.labkey.query.controllers.QueryController$BaseSaveRowsAction.executeJson(QueryController.java:2949)
    at org.labkey.query.controllers.QueryController$InsertRowsAction.execute(QueryController.java:3016)
    at org.labkey.query.controllers.QueryController$InsertRowsAction.execute(QueryController.java:3009)
    at org.labkey.api.action.ApiAction.handlePost(ApiAction.java:152)
    at org.labkey.api.action.ApiAction.handleRequest(ApiAction.java:87)
    at org.labkey.api.action.BaseViewAction.handleRequestInternal(BaseViewAction.java:177)
    at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
    at org.labkey.api.action.SpringActionController.handleRequest(SpringActionController.java:353)
    at org.labkey.api.module.DefaultModule.dispatch(DefaultModule.java:921)
    at org.labkey.api.view.ViewServlet.service(ViewServlet.java:164)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.labkey.api.data.TransactionFilter.doFilter(TransactionFilter.java:36)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.labkey.core.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:118)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.labkey.api.module.ModuleLoader.doFilter(ModuleLoader.java:791)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.labkey.api.security.AuthFilter.doFilter(AuthFilter.java:170)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:679)
 
Matthew Bellew responded:  2012-12-21 11:20
Thanks, that's interesting. I wish I had written the type of the parameters into the log, but I'm pretty sure that the first 4 dates are java.util.Date and the last two are java.sql.Timestamp. I suspect that the remaining value "2012-12-06T08:00:00.000Z" is the problem. Anything different about date_used that you can figure?

I will try to reproduce this scenario here.

Matt
 
tstellin responded:  2012-12-21 12:57
Hi Matt,

My apologies, it must've been something wrong on my end. The problem went away after I changed my date columns from date -> timestamp and restarted tomcat.
 
Matthew Bellew responded:  2012-12-21 13:02
Good to hear. Since we only create timestamp columns, we may have a little testing to do to completely support date-only columns in external schemas.

Matt