Not updating values of FK columns

From: André Luiz do Nascimento Sousa (andresous..aixaseguros.com.br)
Date: Fri Nov 28 2003 - 09:04:51 EST

  • Next message: Tore Halset: "define relations both ways?"

    Hi, All,

            I'm having a trouble when updating values of FK columns on a table. The FK columns on table are not updated, but any errors happen. See the following scenario to say if I'm doing something wrong:

            I'm using struts 1.1, Cayenne 1.0.3 and SQL Server 2000.

            I have a JSP page showing a form with all the table fields. This form will be showed with a record loaded from the database. For the FK columns, I build some comboBox (list) with rows loaded from lookup tables. When I submit the form, I should update the respective row in database. For all not FK fields, the new values are updated normally, but for the FK's the update doesn't work if the respective column values are not null in database.

      // Sample code
      // ***************
      wParamsDB.put("IDE_ESCOTISTA", new Integer(pForm.getIdeEscotista()));
      wResultList = this.buscarListaEscotistas(pSessao,
                                               wParamsDB,
                                               null);
      if (wResultList != null && wResultList.size() > 0) {
          LogManager.debug("ControladorDadosEscotista.manterIdentificacaoEscotista()", "ATUALIZAR - Dentro do If");
          wObjPersist = (Escotista) wResultList.get(0);
          
          // Setting the FK values - Doesn't update if they are not null
          wObjPersist.setCodCboProfissaoFk09(pForm.getCodCBOProfissaoFk09());
          wObjPersist.setCodNumeralGrupoFk27(new Short(wNumeralGrupo));
          wObjPersist.setSigUfGrupoFk27(wUfGrupo);
          wObjPersist.setCodRamoAtualFk31( pForm.getCodRamoAtualFk31());
          wObjPersist.setIdeEscolaridadeFk10((new Integer(pForm.getIdeEscolaridadeFk10())));
          wObjPersist.setIdeEscotistaMaeFk01((new Integer(pForm.getIdeEscotistaMaeFk01())));
          wObjPersist.setIdeEscotistaPaiFk01((new Integer(pForm.getIdeEscotistaPaiFk01())));

          // Setting the Non-FK values - Works fine - The update happens
          wObjPersist.setCodStatusPrincipal(pForm.getCodStatusPrincipal());
          wObjPersist.setCodSubstatusPrincipal(pForm.getCodSubstatusPrincipal());
          wObjPersist.setDthCadastro( DateManager.obtemData (pForm.getDthCadastro(), "dd/MM/yyyy"));
          wObjPersist.setDthNascimento( DateManager.obtemData(pForm.getDthNascimento(), "dd/MM/yyyy"));
          wObjPersist.setNomEscotista(pForm.getNomEscotista());
          ...
          ctxt.commitChanges(Level.ALL);
          ctxt.invalidateObject(wObjPersist);
          ctxt.refetchObject(wObjPersist.getObjectId());
      }
      ...

            I've modified the code, bringing from the database the respective record of the lookup table, and mounting the related DataObject. This way, the update works fine, but it's not too much efficient query all the related objects, set them to the update record and then commit.
      ...
      // Query the related object
      ControladorTopicosEscoteiros wTop = ControladorTopicosEscoteiros.getContrTopicosEscoteiros();
      List wList = wTop.buscarListaRamos(pSessao, pForm.getCodRamoAtualFk31());
      Ramo wRamo = (Ramo) wList.get(0);
      wObjPersist.setToRamo(wRamo);
      ...
      ctxt.commitChanges(Level.ALL);
      // The update works

            My question is: Is that right and should I query all the FK related objects? Or is there another way to set values in FK columns when updating?

            Thanks in advance.

            Att.

    André Luiz
    Brasil

    This archive was generated by hypermail 2.0.0 : Fri Nov 28 2003 - 08:05:04 EST