Form Applications Adds onto WHERE clause

Code Examples Form
,
#1

Here is the sql generated after turning on the DEBUG for a forms application.
(mysqlt): SELECT iniid, cuiid, loiid, ldiid, lmiid, sriid, ltiid, sriSecondary, infPrimaryCommission, inbPrimaryGross, infSecondaryCommission, inbSecondaryGross, insDescription, iniOrderNumber, insPONumber, indDateOfOrder, indDateOrderDue, indCompletionDate, indInvoiceDate, inbRushOrder, inbDiscount, infDiscountPercent, inbTaxExempt, infOrderTotal, infSalesTax, infSalesTaxOther, infShipping, infAmountDue, infPayments, infBalanceDue, insQuickBooksID, indRoyaltyUploadDate, indQuoteChangeDate, indCreateDate, indUpdateDate, indUpdateUser, insMachineName, infRushOrderPercent, inbReadyToPost, inbManualInvoiceDate from invoice WHERE (iniid = 439) AND (iniid = 0) order by iniid

NOTE: at the end the application is adding AND (iniid = 0)

So I checked the application settings:
Form Settings (Left nav) -> SQL -> within the WHERE CONDITION text box I have this defined iniid = [iniid]

This generates the above shown SQL: … WHERE (iniid = 439) problem is it always adds AND (iniid = 0)

Troubleshooting:

  1. Global is set for [iniid] and for local variable {iniid}
  2. Form Settings (Left nav) -> Unique Key -> iniid is select as the UK1: iniid

This happens in a lot of my form applications.
Austin, TX (user)

#2

Found Issue

Went into the code and found the issue the WHERE clause is not being found to be empty but it is within the application.

     $_SESSION['sc_session'][$this->Ini->sc_page]['form_invoice']['parms'] = ""; 
      $nmgp_select = "SELECT iniid, cuiid, loiid, ldiid, lmiid, sriid, ltiid, sriSecondary, infPrimaryCommission, inbPrimaryGross, infSecondaryCommission, inbSecondaryGross, insDescription, iniOrderNumber, insPONumber, indDateOfOrder, indDateOrderDue, indCompletionDate, indInvoiceDate, inbRushOrder, inbDiscount, infDiscountPercent, inbTaxExempt, infOrderTotal, infSalesTax, infSalesTaxOther, infShipping, infAmountDue, infPayments, infBalanceDue, insQuickBooksID, indRoyaltyUploadDate, indQuoteChangeDate, indCreateDate, indUpdateDate, indUpdateUser, insMachineName, infRushOrderPercent, inbReadyToPost, inbManualInvoiceDate from " . $this->Ini->nm_tabela ; 
      $aWhere = array();
      $aWhere[] = "iniid = " . $_SESSION['iniid'] . "";
      $aWhere[] = $sc_where_filter;
      if ($this->nmgp_opcao == "igual") 
      {

if (!empty($sc_where))
{
$aWhere[] = “iniid = $this->iniid”;
}
}
$nmgp_select .= $this->returnWhere($aWhere) . ’ ';
$sc_order_by = “”;
$sc_order_by = “iniid”;
$sc_order_by = str_replace("order by ", “”, $sc_order_by);
$sc_order_by = str_replace("ORDER BY ", “”, trim($sc_order_by));
if (!empty($sc_order_by))

Within the above code if I comment out the line $aWhere[] = “iniid = $this->iniid”; The application will not add the extra part to the WHERE clause. I believe this to be a Scriptcase bug.

#3

Hello austin,

Wether if it is a bug or not, I recommend you to create a SQL View for your Statament, and just call this View on SC.

SELECT * FROM MyView WHERE iniid = 439

It is very odd that SC is adding AND (iniid = 0) at the end of the where clause.

regards,
Bernhard Bernsmann

#4

Hello Austin,

In addition, could you remove the WHERE clause so we can make sure that the issue is/isn’t being cause by it?

regards,
Bernhard Bernsmann