• Sign In
  1. Messages

Labkey.Query.insertRows throws null value exception for primary key

LabKey Support Forum

View Message

view list print
Labkey.Query.insertRows throws null value exception for primary key Maya Li  2011-11-28 13:50
Status: Closed
 
I'm developing a module with my own defined database schema. I am trying to insert a row into a table with the following definition:

CREATE TABLE litterbox.specimenrequirements
(
  m_requirementid serial NOT NULL,
  studyid bigint,
  sequencenumber integer,
  organ character varying(50),
  numtumorcases integer,
  desirepairnormal boolean,
  numtissuesamples integer DEFAULT 0,
  sizepertissuesample character varying(10),
  numbloodsamples integer DEFAULT 0,
  volperbloodsample integer,
  typeofblood character varying(10),
  mintumorperblock integer DEFAULT 0,
  maxtumorperblock integer DEFAULT (-1),
  sourcesurgery boolean,
  sourcebiopsy boolean,
  transportmethod character varying(10),
  container entityid,
  entityid entityid,
  createdby userid,
  created timestamp without time zone,
  modifiedby userid,
  modified timestamp without time zone,
  CONSTRAINT pk_specimenrequirements PRIMARY KEY (m_requirementid),
  CONSTRAINT fk_specimenrequirements FOREIGN KEY (studyid)
      REFERENCES litterbox.study (m_studyid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

I am simply trying to insert a row with the following JavaScript code:

function addTissueSpec() {
        alert('add tissue start');
         alert(InitTissueForm.organ.value);
         LABKEY.Query.insertRows({
                schemaName: 'litterbox',
                queryName: 'specimenrequirements',
                rowDataArray: [{
                        "studyid": _studyId,
                        "organ" : InitTissueForm.organ.value,
                        "numtissuesamples": InitTissueForm.numtissuesamples.value,
                        "sizepertissuesample" : InitTissueForm.sizepertissuesample.value
                    }],
                
                successCallback: function(data) {
                    var requirementId = data.rows[0].m_requirementid;
                    alert(requirementId + " added success");
                },
                failureCallback: function() {alert('Something went wrong');}
            });
       
    }

When I call addTissueSpec(), I get the following javascript error (truncated to show only the top):

{
    "exceptionClass": "org.labkey.api.query.InvalidKeyException",
    "exception": "Value for key field 'm_requirementid' was null or not supplied!",
    "stackTrace": [
        "org.labkey.api.query.DefaultQueryUpdateService.getKeys(DefaultQueryUpdateService.java:397)",
        "org.labkey.api.query.DefaultQueryUpdateService.getRow(DefaultQueryUpdateService.java:134)",
        "org.labkey.api.query.AbstractQueryUpdateService.getRows(AbstractQueryUpdateService.java:72)",
        "org.labkey.query.controllers.QueryController$CommandType$1.saveRows(QueryController.java:2611)",
        "org.labkey.query.controllers.QueryController$BaseSaveRowsAction.executeJson(QueryController.java:2769)",
        "org.labkey.query.controllers.QueryController$InsertRowsAction.execute(QueryController.java:2826)",
        "org.labkey.query.controllers.QueryController$InsertRowsAction.execute(QueryController.java:2819)",
        "org.labkey.api.action.ApiAction.handlePost(ApiAction.java:147)",
        "org.labkey.api.action.ApiAction.handleRequest(ApiAction.java:98)",
        "org.labkey.api.action.BaseViewAction.handleRequestInternal(BaseViewAction.java:166)",
        "org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)",
..

I have written similar code which has worked on other table with similar set up before. I have tried to also add 'm_requirementid: 8' in the rowDataArray config and that didn't seem to work either. Any help with possible leads is appreciated.

Maya
 
 
Maya Li responded:  2011-11-28 15:43
I solved the problem by fluke, without understanding what is really happening. In the original schema definition, specimenrequirements table primary key m_requirementid is not defined with "NOT NULL". It was added as a constraint implicit with the primary key definition. When I went back to redefine the database table to include "NOT NULL" explicitly, I was able to make the LabKey.query.insertRows call work. The similar problem in the editorGrid's add record button also went away.

I will close this issue for now but I would love to hear if anyone has a good explanation for this strange behavior.
 
Matthew Bellew responded:  2011-11-28 17:31
This is very strange behavior. I'll take a look as soon as I have a second. My guess is that the code does not realize that m_requirementid is a serial.

I should know, but I'm not sure how this is reported by JDBC. I do know that there is a way to specify this in the schema xml.

        <ns:datatype>serial</ns:datatype>
        <ns:isAutoInc>true</ns:isAutoInc>
        <ns:isReadOnly>true</ns:isReadOnly>
        <ns:isKeyField>true</ns:isKeyField>
 
Ben Bimber responded:  2011-11-28 17:41
Glad you got it working. I agree with Matt's explanation, although I'm not sure what's causing it for you. I tried made a module using your initial SQL (including 'not null'). I then used the following XML and insertRows worked. Here's the XML:

<ns:tables xmlns:ns="http://labkey.org/data/xml">
  <ns:table tableName="specimenrequirements" tableDbType="TABLE">
    <ns:columns>
      <ns:column columnName="m_requirementid"/>
      <ns:column columnName="studyid"/>
      <ns:column columnName="sequencenumber"/>
      <ns:column columnName="organ"/>
      <ns:column columnName="numtumorcases"/>
      <ns:column columnName="desirepairnormal"/>
      <ns:column columnName="numtissuesamples"/>
      <ns:column columnName="sizepertissuesample"/>
      <ns:column columnName="numbloodsamples"/>
      <ns:column columnName="volperbloodsample"/>
      <ns:column columnName="typeofblood"/>
      <ns:column columnName="mintumorperblock"/>
      <ns:column columnName="maxtumorperblock"/>
      <ns:column columnName="sourcesurgery"/>
      <ns:column columnName="sourcebiopsy"/>
      <ns:column columnName="transportmethod"/>
      <ns:column columnName="container"/>
      <ns:column columnName="entityid"/>
      <ns:column columnName="createdby"/>
      <ns:column columnName="created"/>
      <ns:column columnName="modifiedby"/>
      <ns:column columnName="modified"/>
    </ns:columns>
  </ns:table>
</ns:tables>

I didnt need to manually specify isAutoInc. There may be differences in our postgres versions or differences in how they report the column. I have seen problems in the past with PKs not being reported correctly, in which case I needed to manually specify it using XML.

If this happens again, the first thing I'd do is check the schema browser and see if this column has 'AI' (auto increment) under the attributes for that field.
 
Maya Li responded:  2011-11-28 23:33
Thanks, Matt. I will try to use those column elements if this happens again for a quicker fix (I had to rebuild my schema this time, unfortunately.) . I did try setting IsAutoInc, which did not solve the issue.

Ben, I am sorry that I must have confused you. The SQL script that I included in my first message was from my PgAdmin, which added "not null" automatically since m_requirementid is defined as a primary key. If you would be willing to try the example again, leaving the "not null" out for m_requirementid, you may be able to replicate my problem. I will look out for AI in the schema browser next time. I believe I did see it but I cannot confirm.

Thanks again for your responses.
 
Ben Bimber responded:  2011-11-29 06:03
Hi Maya,

It didnt seem to make a difference either way. let us know if you run into something similar in the future.
 
Matthew Bellew responded:  2011-11-29 11:23
I could not repro either way. I suspect the problem is not related to "NOT NULL". What version of postgres are you using?
 
Maya Li responded:  2011-11-29 11:28
I am using Postgres 9.0.4.
 
sadcat responded:  2012-02-23 16:50
I am afraid this error is raising its ugly head again (I am working with Maya's code, except for the database is MSSQL server now). The code for table creation is below, when I try to insert a row, I get an error:
Value for key field 'm_studyid' was null or not supplied!"

When I look at the table in Schema Browser, m_studyid does come up as PK, but not AK (AutoIncrement)? If this is the problem, how do I create the table so that it is indeed recognized as an autoincrementing row?

Thanks!
X
 
Table creation code:
CREATE TABLE litterbox.study
(
 m_studyID BIGINT PRIMARY KEY IDENTITY(1,1) NOT NULL,
 CBR_StudyID varchar(50) unique,
 repository bigint,
 study_title varchar(50),
 study_PI userid,
 pickup_delivery_contact userid,
 pickupAfter5 bit,
 holdSampleOverNight bit,
 collection_start date,
 collection_end date,
 consentForm bigint,
 container entityid ,
 entityid entityid,
 createdby userid ,
 created datetime,
 modifiedby userid ,
 modified datetime,
 -- CONSTRAINT PK_Study PRIMARY KEY (m_studyID),
 CONSTRAINT FK_StudyRepo FOREIGN KEY (repository) REFERENCES
litterbox.repository(m_repoID),
 CONSTRAINT FK_study_consent FOREIGN KEY (consentForm) REFERENCES
litterbox.consentForm(formID)

);
 
jeckels responded:  2012-02-24 17:28
Hi Xenia,

Looking at the relevant code, it looks like we're specifically checking for the INT column type on SQLServer when determining if it's autoincrement. We should improve our detection code, but if you can at least temporarily use INT instead, the server should pick it up.

https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=14136

Thanks,
Josh