Why does SQL work differently between phpMyAdmin and report?

Why does SQL work differently between phpMyAdmin and report?

Postby JohnKlassen » Wed Jul 10, 2013 1:40 pm

I need some help trying to figure out why a SELECT statement works differently in phpMyAdmin than it does in the NuBuilder Report Writer. I have a property management application where the user enters information about property violations. For example, the tenant may have broken a window and needs to get it fixed. Based on the information in the violations table, a SELECT statement is used to get information from the violations table and 4 other tables that are joined in the query. The results of the query are put into #dataTable# and a letter is created to be sent to the tenant with information about the violation. The same violation can generate up to five letters with each letter becoming more serious about the violation. I have a ‘letter’ table that contains five different versions of the letter for that violation. (The letter table has a total of 15 different letters. Violations are divided into 3 categories with 5 letters for each category.) Based on the “notice number” the proper letter is selected from the table. (‘Notice number’ and ‘letter number’ are the same thing.)

Here is the select statement used in the report.
Code: Select all
// Logic for Notice Letters

  $unit_fk = $formValue['vio_unit_id'];

  $rules_fk = $formValue['vio_rules_id'];

// Create dataTable

nuRunQuery(
<<<EOSQL
 
CREATE TABLE #dataTable#

SELECT
  violations.violations_id,
  violations.vio_unit_id,
  violations.vio_rules_id,
  unit.unit_id,
  unit.unit_building_unit,
  violations.vio_violation_number,
  violations.vio_printable_information,
  people.people_id,
  people.peo_type,
  people.peo_building_unit,
  people.peo_name,
  people.peo_company,
  people.peo_address_1,
  people.peo_city,
  people.peo_state,
  people.peo_zip,
  rules.rules_id,
  rules.rule_key_phrase,
  rules.rule_section,
  rules.rule_frequency,
  letter.let_frequency,
  letter.let_notice_number,
  letter.let_base,
  DATE_FORMAT(CURDATE(),'%M %d, %Y') AS today_date
FROM violations

INNER JOIN (unit, rules, people, letter)
ON (
unit.unit_id = violations.vio_unit_id AND
rules.rules_id = violations.vio_rules_id AND
people.peo_building_unit = unit.unit_building_unit AND
letter.let_frequency = rules.rule_frequency)

WHERE violations.vio_unit_id = '$unit_fk'
AND violations.vio_rules_id = '$rules_fk'
AND let_notice_number = violations.vio_violation_number

EOSQL
);


In order to do my testing, I commented out any additional code in the report. I ran a test in the report writer where the only thing I changed was the ‘notice number’. When I used ‘notice number’ ‘1’ or ‘2’, the letter was generated in about 20 seconds. When I used ‘notice numbers’ ‘3’, ‘4’ or ‘5 ‘, the letter was never generated. It either ran out of memory or I cancelled it after about 5 minutes.

I took the same select statement, hard coded the foreign keys, ‘vio_unit_id’ and ‘vio_rules_id’ to match the same violation and hard coded the ‘notice number’. I ran the query five times in phpMyAdmin and each time changed ‘let_notice_number’ from 1 to 5. Each time the query took about 2 seconds to return the expected results.

What I don’t understand is why the query runs fine in phpMyAdmin but when I run it through the Report Writer, it only works when I select letters ‘1’ or ‘2’.

I have tried a number of different things to try to determine why there is this problem. I have tried ‘inner joins’ and ‘left joins’ and get the same results. I have checked the data in the tables and don't see any problems. I can use the same violation for different units and get different results. For example, I have 7 units with the violation of ‘windows – screens’. Six of the units cannot generate a letter with a notice number of ‘2’ but the seventh unit has no problems with any notice letter for this violation. I have a unit that has 4 different violations. One violation has no problems with generating any of the 5 letters. Of the remaining 3 violations, 2 of the violations have problems with notice numbers (letters) ‘3’ and ‘4’ while the last one has problems with ‘3’, ‘4’ and ‘5’.

There seems to be no pattern to which letters work and which ones don’t. I also don’t understand why the same query works in phpMyAdmin but it has problems with some letters in the Report Writer.

I am looking for suggestions on how I can debug this problem.

Thanks,

John
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Why does SQL work differently between phpMyAdmin and rep

Postby massiws » Fri Jul 12, 2013 6:49 am

John, to get the real SQL sent to db I suggest to put your SQL in a variable and print out with nuDebug() function just before the nuRunQuery():
Code: Select all
$s = "CREATE TABLE #dataTable# ...";
nuDebug($s);
nuRunQuery($s);


After this, try to start report with different notice number: when the procedure fails, you can see the SQL in Custom code > Debug (or in zzsys_trap table in phpmyadmin).

Then copy that sql in phpmyadmin: maybe this can give you more informations.

Hope this helps,
Max
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Why does SQL work differently between phpMyAdmin and rep

Postby JohnKlassen » Mon Jul 15, 2013 2:01 pm

Max,

Thanks for the suggestion. I set up my SQL script as you suggested and got it working. Unfortunately, the SQL in the zzsys_trap table is exactly the same as my SQL in the report.

I am trying a different approach now. I am creating a report that displays the contents of each field in the SELECT statement. I am then adding the original code, one line at a time. After I add a line of code, I run a test with the 5 options to see if any of them fails. I have not completed the testing yet. I will let you know what I find out.

Meanwhile, I appreciate the debugging tip and will use it in other situations.

John
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Why does SQL work differently between phpMyAdmin and rep

Postby JohnKlassen » Tue Jul 23, 2013 12:48 pm

Max,

After a lot of time spent checking my code and the data, I have finally determined that the problem was in the report writer. I have a field, 'let_base', in the report that contains the contents of a letter. There are 15 versions of the letter. Since 'let_base' is defined as text field and since the different letters are different sizes, I set the 'Can Grow' parameter for that field to 'True' in the report. For some reason, when the report selected certain letters, if would just 'spin' for 5 minutes and then time-out without generating the letter or an error. At first I thought it had to do with the length of certain letters but there were other letters that were 1 line longer and still worked.

The bottom line, is that I reduced the space between the lines of the letter and moved some other fields in the report so the total length of the detail section was reduced. I am happy to say that the report works fine now.

Thanks for all of your suggestions for debugging this issue.

John
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Why does SQL work differently between phpMyAdmin and rep

Postby massiws » Wed Jul 24, 2013 7:49 am

.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to Report Writer