If I have a record and a form and in that form I have a field which I connect to a sequence, then the generated code is WRONG.
When I make a form and put (using oracle) SEQ_NR as my sequence (an existing sequence) then the following is a partial dump of the generated code:
if ($this->nmgp_opcao == "incluir")
{
$NM_cmp_auto = "";
$NM_seq_auto = "";
....
if (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_db2))
{
$NM_seq_auto = "NEXT VALUE FOR SEQ_MUTFPERS, ";
$NM_cmp_auto = "NR, ";
}
if (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_oracle))
{
$NM_seq_auto = "SEQ_MUTFPERS.NEXTVAL, ";
$NM_cmp_auto = "NR, ";
}
....
if ($bInsertOk)
{
if (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_access))
{
$comando = "INSERT INTO " . $this->Ini->nm_tabela . " (STATUS, EMPLID, EMPL_RCD, NAME_FORMAL, BIRTHDATE, RUG_EMPLID_LEIDING, RUG_NAAM_LEIDING, DEPTID, RUG_DEPTNAME5 .......>rug_emplid_supervisor')";
}
elseif (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_mssql))
{
... }
elseif (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_oracle))
{
$comando = "INSERT INTO " . $this->Ini->nm_tabela . " (" . $NM_cmp_auto . "STATUS, ...) VALUES (" . $NM_seq_auto . "'$this->status',...)";
}
elseif (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_informix))
{
$comando = "INSERT INTO " . $this->Ini->nm_tabela . " (" . $NM_cmp_auto . "STATUS, ....RUG_EMPLID_SUPERVISOR) VALUES (" . $NM_seq_auto . rug_dist_pct_2, ....., '$this->rug_emplid_supervisor')";
}
else
{
$comando = "INSERT INTO " . $this->Ini->nm_tabela . " (" . $NM_cmp_auto . "STATUS, ...RUG_EMPLID_SUPERVISOR) VALUES (" . $NM_seq_auto . "'$this->status', ..., '$this->rug_emplid_supervisor')";
}
… etc…
The code is quite similar for oracle and other datbases.
BUT the code is WRONG!!
For example for oracle:
$NM_seq_auto = "SEQ_MUTFPERS.NEXTVAL, ";
$NM_cmp_auto = "NR, ";
Later on you find :
if (in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_oracle))
{
$_SESSION[‘scriptcase’][‘sc_sql_ult_comando’] = “select SEQ_MUTFPERS.currval from dual”;
$rsy = $this->Db->Execute($_SESSION[‘scriptcase’][‘sc_sql_ult_comando’]);
if ($rsy === false && !$rsy->EOF)
{
$this->Erro->mensagem (FILE, LINE, “banco”, $this->Ini->Nm_lang[‘lang_errm_dbas’], $this->Db->ErrorMsg());
exit;
}
$this->nr = $rsy->fields[0];
$rsy->Close();
So here the error should be obvious!
You can NOT use currval.
Why not? I can hear you ask.
Simple if I do a SEQ_MUTFPERS.NEXTVAL I always get the next value. When I do SEQ_MUTFPERS.CURRVAL I get the last value.
So this scenario fails:
Person 1 does an insert AND person 2 does an insert.
Person 1 gets a nextval of 25
Person 2 gets a nextval of 26
Person 1 gets then a currval of 26!!! which is WRONG!!
Person 2 also gets 26.
The generated code is thus bad.
How SHOULD it be done?
nm_comando=‘select SEQ_MUTFPERS.NEXTVAL from DUAL’;
if ($rs = $this->Db->Execute($nm_comando))
{
$currval = $rs->fields[0];
$rs->Close() ;
}
Then later on where you use the .currval
e.g. i this line
$_SESSION[‘scriptcase’][‘sc_sql_ult_comando’] = “select SEQ_MUTFPERS.currval from dual”;
$rsy = $this->Db->Execute($_SESSION[‘scriptcase’][‘sc_sql_ult_comando’]);
There you should NOT get currval on that way but use $currval instead.
This code gets generated in the FORMNAME_apl.php code and it is a bug in SC7 and sc6.
It will appear on high traffic sites with lots of simultaeneous inserts.
Please fix this asap. I have given enough detail to fix it, I wont contact support since this is clear enough.