Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
Clear All
new posts
Previous template Next
    #1

    How to get auto_increment key of a newly added record?

    Using: SmartClient_v110p_2017-05-15_PowerEdition

    Feels like I'm missing something simple (again).

    I have a situation where I want the user to add an entry to a MySQL table using a form, and then I want to offer them the ability to add/edit records to related tables based on the primary key value that gets created automatically by MySQL during the add. So far I can't figure out how to retrieve the new key value after the save though.

    I populate the form programatically,

    Then check for validation error:
    newPersonValidateForm.checkForValidationErrors(...)

    Then save in response to the user clicking a button:
    ​​​​​​newPersonValidateForm.saveData(...)

    Then I'm stumped as to how to get the value of the newly created primary key. Is it an argument to the callback? Or returned some other way?

    Querying the data directly using SQL shows that the save is working and creating the new key value as expected. The developer.getClue() function is failing though.

    Thanks
    Russ Poyner.

    Tags: None
    #2
    dsResponse.data contains the complete inserted record, including the sequence value. This assumes you correctly declared your PK as type "sequence".

    Comment

      #3
      That seems to make sense, but I'm still having problems. When I dump the dsResponse to console log data appears empty.

      newPersonValidateForm.saveData(function(dsResponse,data) {console.log("responseData",dsResponse)});

      Shows in the console log:
      responseData
      Object {operationId: "people_add", clientContext: Object, internalClientContext: Object, context: Object, transactionNum: 5…}
      affectedRows:1
      callbackArgs:null
      clientContext:Object
      clientOnly:undefined
      context:Object
      data:Array(0)
      length:0
      __proto__:Array(0)
      endRow:0

      ... etc.


      The PK declaration:

      <field name="id_People" type="sequence" title="5-digit unique ID number" primaryKey="true" foreignKey="supervisorTree.id_People" columnCode="b80bb7740288fda1f201890375a60c8f">

      Something still not right with my code?

      Comment

        #4
        No idea, as you didn't post the server log. That's always where troubleshooting for a server-side issue starts.

        Comment

          #5
          A section of the server side log from one of theses saves. Which contains the new PK value!

          With some things redacted...

          === 2017-09-11 22:43:02,564 [c-11] INFO RequestContext - URL: '/isomorphic/IDACall', User-Agent: 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36': Safari with Accept-Encoding header
          === 2017-09-11 22:43:02,565 [c-11] DEBUG XML - Parsed XML from (in memory stream): 1ms
          === 2017-09-11 22:43:02,566 [c-11] DEBUG RPCManager - Processing 1 requests.
          === 2017-09-11 22:43:02,566 [c-11] DEBUG RPCManager - Request #1 (DSRequest) payload: {
          values:{
          id_People:null,
          nameLast:"redactI",
          nameFirst:"redact",
          hrsPersonID:"redact",
          uwPVI:"redact",
          isisEmplID:"redact",
          isisCampusID:"redact",
          uwPhotoID:"redact",
          uwNetID:"redact",
          nameMiddle:null
          },
          operationConfig:{
          dataSource:"people",
          repo:null,
          operationType:"add",
          textMatchStyle:"exact"
          },
          componentId:"newPersonValidateForm",
          appID:"builtinApplication",
          operation:"people_add",
          oldValues:{
          },
          criteria:{
          }
          }
          === 2017-09-11 22:43:02,566 [c-11] INFO IDACall - Performing 1 operation(s)
          === 2017-09-11 22:43:02,566 [c-11] DEBUG Relation - Caching instance of toDS 'supervisorTree' in the DSRequest map
          === 2017-09-11 22:43:02,566 [c-11] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
          === 2017-09-11 22:43:02,566 [c-11] DEBUG DeclarativeSecurity - DataSource people is not in the pre-checked list, processing...
          === 2017-09-11 22:43:02,567 [c-11] DEBUG AppBase - [builtinApplication.people_add] No userTypes defined, allowing anyone access to all operations for this application
          === 2017-09-11 22:43:02,567 [c-11] DEBUG AppBase - [builtinApplication.people_add] No public zero-argument method named '_people_add' found, performing generic datasource operation
          === 2017-09-11 22:43:02,567 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] Processing security checks for DataSource null, field null
          === 2017-09-11 22:43:02,567 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] Request is not a client request, ignoring security checks.
          === 2017-09-11 22:43:02,567 [c-11] DEBUG AppBase - [builtinApplication.people_add, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
          === 2017-09-11 22:43:02,567 [c-11] DEBUG AppBase - [builtinApplication.people_add, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
          === 2017-09-11 22:43:02,567 [c-11] INFO SQLDataSource - [builtinApplication.people_add, builtinApplication.null] Performing fetch operation with
          outputs: [hrsPersonID, id_People] criteria: {hrsPersonID:"redact"} values: {hrsPersonID:"redact"}
          === 2017-09-11 22:43:02,568 [c-11] INFO SQLDataSource - [builtinApplication.people_add, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
          === 2017-09-11 22:43:02,568 [c-11] INFO SQLDataSource - [builtinApplication.people_add, builtinApplication.null] 119: Executing SQL query on 'personnel': SELECT people.id_People, people.hrsPersonID FROM people WHERE (people.hrsPersonID='00880822')
          === 2017-09-11 22:43:02,568 [c-11] DEBUG PoolableSQLConnectionFactory - [builtinApplication.people_add, builtinApplication.null] Executing pingTest 'select 1 from dual' on connection 1549960032
          === 2017-09-11 22:43:02,569 [c-11] DEBUG SQLConnectionManager - [builtinApplication.people_add, builtinApplication.null] Borrowed connection '1549960032'
          === 2017-09-11 22:43:02,569 [c-11] DEBUG SQLTransaction - [builtinApplication.people_add, builtinApplication.null] Started new personnel transaction "1549960032"
          === 2017-09-11 22:43:02,569 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add, builtinApplication.null] Setting DSRequest as being part of a transaction
          === 2017-09-11 22:43:02,570 [c-11] INFO SQLDriver - [builtinApplication.people_add, builtinApplication.null] Executing SQL query on 'personnel' using connection '1549960032': SELECT people.id_People, people.hrsPersonID FROM people WHERE (people.hrsPersonID='redact')
          === 2017-09-11 22:43:02,577 [c-11] INFO DSResponse - [builtinApplication.people_add] DSResponse: List with 0 items
          === 2017-09-11 22:43:02,577 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] Processing security checks for DataSource null, field null
          === 2017-09-11 22:43:02,577 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] Request is not a client request, ignoring security checks.
          === 2017-09-11 22:43:02,577 [c-11] DEBUG AppBase - [builtinApplication.people_add, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
          === 2017-09-11 22:43:02,577 [c-11] DEBUG AppBase - [builtinApplication.people_add, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
          === 2017-09-11 22:43:02,578 [c-11] INFO SQLDataSource - [builtinApplication.people_add, builtinApplication.null] Performing fetch operation with
          outputs: [uwPVI, id_People] criteria: {uwPVI:"UW749V884"} values: {uwPVI:"UW749V884"}
          === 2017-09-11 22:43:02,578 [c-11] INFO SQLDataSource - [builtinApplication.people_add, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
          === 2017-09-11 22:43:02,578 [c-11] INFO SQLDataSource - [builtinApplication.people_add, builtinApplication.null] 119: Executing SQL query on 'personnel': SELECT people.id_People, people.uwPVI FROM people WHERE (people.uwPVI='UW749V884')
          === 2017-09-11 22:43:02,578 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add, builtinApplication.null] Setting DSRequest as being part of a transaction
          === 2017-09-11 22:43:02,578 [c-11] INFO SQLDriver - [builtinApplication.people_add, builtinApplication.null] Executing SQL query on 'personnel' using connection '1549960032': SELECT people.id_People, people.uwPVI FROM people WHERE (people.uwPVI='UW749V884')
          === 2017-09-11 22:43:02,586 [c-11] INFO DSResponse - [builtinApplication.people_add] DSResponse: List with 0 items
          === 2017-09-11 22:43:02,586 [c-11] INFO SQLDataSource - [builtinApplication.people_add] Performing add operation with
          criteria: {id_People:null,nameLast:"SUBRAMANIAN SESHADRI",nameFirst:"redact",hrsPersonID:"redact",uwPVI:"redact",isisEmplID:"redact",isisCampusID:"redact",uwPhotoID:"redact",uwNetID:"redact",nameMiddle:null} values: {id_People:null,nameLast:"redact redact",nameFirst:"redact",hrsPersonID:"redact",uwPVI:"redact",isisEmplID:"redact",isisCampusID:"redact",uwPhotoID:"redact",uwNetID:"redact",nameMiddle:null}
          === 2017-09-11 22:43:02,586 [c-11] DEBUG SQLValuesClause - [builtinApplication.people_add] Sequences: {id_People=__default}
          === 2017-09-11 22:43:02,586 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add] Setting DSRequest as being part of a transaction
          === 2017-09-11 22:43:02,586 [c-11] INFO SQLDriver - [builtinApplication.people_add] Executing SQL query on 'personnel' using connection '1549960032': INSERT INTO people (hrsPersonID, isisCampusID, isisEmplID, nameFirst, nameLast, nameMiddle, uwNetID, uwPVI, uwPhotoID) VALUES ('redact', 'redact', 'redact', 'redact', 'redact redact', NULL, 'redact', 'redact', 'redact')
          === 2017-09-11 22:43:02,588 [c-11] DEBUG SQLDriver - [builtinApplication.people_add] Found 1 sequence(s)/autoGenerated PK field(s): [id_People]

          ***The new PK value!******

          === 2017-09-11 22:43:02,588 [c-11] DEBUG SQLDriver - [builtinApplication.people_add] Discovered zero or one generated key via JDBC: {id_People=14499}


          === 2017-09-11 22:43:02,588 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add] add operation affected 1 rows
          === 2017-09-11 22:43:02,588 [c-11] INFO SQLDataSource - [builtinApplication.people_add] primaryKeys: {}
          === 2017-09-11 22:43:02,588 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add] Added JDBC-generated values to the list of primaryKeys. Key values are now: {id_People=14499}
          === 2017-09-11 22:43:02,588 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] Processing security checks for DataSource null, field null
          === 2017-09-11 22:43:02,588 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] DataSource people is not in the pre-checked list, processing...
          === 2017-09-11 22:43:02,588 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] Processing security checks for DataSource supervisorTree, field id_People
          === 2017-09-11 22:43:02,588 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] DataSource supervisorTree is not in the pre-checked list, processing...
          === 2017-09-11 22:43:02,588 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] Processing security checks for DataSource supervisorTree, field isFolder
          === 2017-09-11 22:43:02,588 [c-11] DEBUG DeclarativeSecurity - [builtinApplication.people_add] DataSource supervisorTree is not in the pre-checked list, processing...
          === 2017-09-11 22:43:02,588 [c-11] DEBUG AppBase - [builtinApplication.people_add, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
          === 2017-09-11 22:43:02,588 [c-11] DEBUG AppBase - [builtinApplication.people_add, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
          === 2017-09-11 22:43:02,588 [c-11] INFO SQLDataSource - [builtinApplication.people_add, builtinApplication.null] Performing fetch operation with
          criteria: {id_People:14499} values: {id_People:14499}
          === 2017-09-11 22:43:02,589 [c-11] INFO SQLDataSource - [builtinApplication.people_add, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause AND $defaultJoinWhereClause
          === 2017-09-11 22:43:02,589 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add, builtinApplication.null] Using SQL Limit query
          === 2017-09-11 22:43:02,589 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add, builtinApplication.null] SQL windowed select rows 0->-1, result size 1. Query: SELECT people.id_People, people.creationName, people.creationTimestamp, people.modificationName, people.modificationTimestamp, people.nameLast, people.nameFirst, people.nameMiddle, people.namePreferred, people.nameInitials, people.spouseName, people.startDate, people.startDateOverride, people.endDate, people.endDateOverride, people.dateOfBirth, people.gender, people.hrsPersonID, people.uwPVI, people.isisEmplID, people.isisCampusID, people.uwPhotoID, people.uwNetID, people.caeUserName, people.payrollNotes, people.isActive, people.includeInDirectory, people.isGroupPI, people.directoryType, people.directoryTitle1, people.directoryTitle2, people.directoryTitle3, people.personalCode, people.emailAddress, people.homeStreet, people.homeCity, people.homeState, people.homeZipCode, people.homePhone, people.homeAddressOmit, people.emergencyContactName, people.emergencyContactAddress1, people.emergencyContactAddress2, people.emergencyContactCity, people.emergencyContactState, people.emergencyContactPostalCode, people.emergencyContactCountry, people.emergencyContactPhone, people.forwardingName, people.forwardingAddress1, people.forwardingAddress2, people.forwardingCity, people.forwardingState, people.forwardingPostalCode, people.forwardingCountry, people.forwardingPhone, people.forwardingModificationDate, people.forwardingModificationName, people.personNotes, concat(people.nameLast,', ',people.nameFirst) AS lastFirst, supervisorTree.isFolder FROM supervisorTree, people WHERE (people.id_People=14499) AND people.id_People = supervisorTree.id_People LIMIT 0, 1
          === 2017-09-11 22:43:02,589 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add, builtinApplication.null] SQL windowed select rows 0->-1, result size 1. Query: SELECT people.id_People, people.creationName, people.creationTimestamp, people.modificationName, people.modificationTimestamp, people.nameLast, people.nameFirst, people.nameMiddle, people.namePreferred, people.nameInitials, people.spouseName, people.startDate, people.startDateOverride, people.endDate, people.endDateOverride, people.dateOfBirth, people.gender, people.hrsPersonID, people.uwPVI, people.isisEmplID, people.isisCampusID, people.uwPhotoID, people.uwNetID, people.caeUserName, people.payrollNotes, people.isActive, people.includeInDirectory, people.isGroupPI, people.directoryType, people.directoryTitle1, people.directoryTitle2, people.directoryTitle3, people.personalCode, people.emailAddress, people.homeStreet, people.homeCity, people.homeState, people.homeZipCode, people.homePhone, people.homeAddressOmit, people.emergencyContactName, people.emergencyContactAddress1, people.emergencyContactAddress2, people.emergencyContactCity, people.emergencyContactState, people.emergencyContactPostalCode, people.emergencyContactCountry, people.emergencyContactPhone, people.forwardingName, people.forwardingAddress1, people.forwardingAddress2, people.forwardingCity, people.forwardingState, people.forwardingPostalCode, people.forwardingCountry, people.forwardingPhone, people.forwardingModificationDate, people.forwardingModificationName, people.personNotes, concat(people.nameLast,', ',people.nameFirst) AS lastFirst, supervisorTree.isFolder FROM supervisorTree, people WHERE (people.id_People=14499) AND people.id_People = supervisorTree.id_People LIMIT 0, 1
          === 2017-09-11 22:43:02,589 [c-11] DEBUG SQLDataSource - [builtinApplication.people_add, builtinApplication.null] Setting DSRequest as being part of a transaction
          === 2017-09-11 22:43:02,872 [c-11] INFO DSResponse - [builtinApplication.people_add] DSResponse: List with 0 items
          === 2017-09-11 22:43:02,872 [c-11] INFO DSResponse - DSResponse: List with 0 items
          === 2017-09-11 22:43:02,872 [c-11] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
          === 2017-09-11 22:43:02,872 [c-11] DEBUG SQLTransaction - Committing personnel transaction "1549960032"
          === 2017-09-11 22:43:02,906 [c-11] DEBUG RPCManager - non-DMI response, dropExtraFields: false
          === 2017-09-11 22:43:02,906 [c-11] DEBUG SQLTransaction - getConnection() looked for transactional connection for personnel: hashcode "1549960032"
          === 2017-09-11 22:43:02,906 [c-11] DEBUG SQLTransaction - Ending personnel transaction "1549960032"
          === 2017-09-11 22:43:02,907 [c-11] DEBUG SQLConnectionManager - About to close connection with hashcode "1549960032"
          === 2017-09-11 22:43:02,908 [c-11] DEBUG PoolableSQLConnectionFactory - Executing pingTest 'select 1 from dual' on connection 1549960032
          === 2017-09-11 22:43:02,908 [c-11] DEBUG PoolableSQLConnectionFactory - Passivating connection '1549960032
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 119
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 1498
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 1498
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 119
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 119
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLTransaction - getConnection() looked for transactional connection for personnel: (connection is null)
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 119
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLTransaction - getConnection() looked for transactional connection for personnel: (connection is null)
          === 2017-09-11 22:43:02,909 [c-11] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 119
          === 2017-09-11 22:43:02,909 [c-11] INFO Compression - /isomorphic/IDACall: 154 -> 140 bytes


          Comment

            #6
            There's a SQL select there being executed to select the data of the last record inserted. It looks like it's failing because there's a join over to the supervisorTree table, where there is no record with the same ID yet. Presumably you have some logic to add that later (or you'd never be able to use this fetch operation successfully..). There are a few possible fixes - one would be to specify an operationBinding.cacheSyncOperation so that this fetch isn't used to retrieve the record right after insertion.

            Comment

              #7
              Thanks once a again.

              In this instance the join to supervisorTree was for a custom fetch operation binding to populate a comboBox pickList. I can do that by other means and just remove the join from the people ds. Without the join dsResponse contains the data as expected.

              File this one under schemaScrewUP.

              RP

              Comment

              Previous template Next
              Working...
              X