Troubleshooting SQL queries

Web applications generated by PHPRunner, ASPRunner.NET or ASPRunnerPro communicate with databases via means of SQL queries. Whenever you search, edit or delete data your web application issues a series of SQL queries, gets results back and displays it on the web page. Understanding the basics of SQL will help you build better apps and find errors faster.

Our code generators come will handy option to display all SQL queries application executes. For this purpose you can add the following line of code to AfterApplicationInitialized event:

In PHPRunner

$dDebug = true;

In ASPRunner.NET (C#)

GlobalVars.dDebug = true;

In ASPRunnerPro

dDebug = true

Lets see how this option can help us troubleshoot your web applications.

1. Troubleshooting Advanced Security mode.

Consider a real life example. Customer opens a ticket that Advanced Security mode “Users can see and edit their own data only” doesn’t work. Customers are supposed to see their own orders only but see all of them.

Turning on SQL debugging shows us the following:

select count(*) FROM `orders` 
SELECT `OrderID`,`CustomerID`,`EmployeeID`,`OrderDate` FROM `orders` ORDER BY 1 ASC

First query calculates number of records that matches the current search criteria. Second query actually retrieves the data. As you can see there is no WHERE clause that restricts data to the current logged user. All orders are shown.

After checking project settings we figured out that all users were accidentally added to the Admin group (admin users can see all data). Once fixed, correct SQL query is produced and only orders that belong to ANATR customer are displayed.

select count(*) FROM `orders` where (CustomerID='ANATR')
SELECT `OrderID`,`CustomerID`,`EmployeeID`,`OrderDate` FROM `orders` where (CustomerID='ANATR') ORDER BY 1 ASC

2. Troubleshooting Master-Details in AJAX mode

Master-details drill-down functionality is implemented via AJAX. AJAX requests are send to the server behind the scene and usually response is expected in very specific format (JSON). Adding anything to the output like SQL queries will break master-details functionality however we still can see SQL queries that retrieve details.

For instance, we link Orders and ‘Order Details’ as Master-Details. If we enable SQL query debugging mode and try to expand details we get error message as follows.

However if we click ‘See details’ link we will see the same two SQL queries that retrieve data from ‘Order Details’ table.

3. Troubleshooting buttons

Troubleshooting button’s code that executes SQL queries is a bit more trickier. We are going to dig a little deeper using Developers Tools in Chrome web browser.

For instance, we have added a button to Orders grid that copies selected order to OrdersArchive table. Here is our Server code (PHP).

$record = $button->getCurrentRecord();
$sql = "insert into OrdersArchive (OrderID, OrderDate, CustomerID) 
values (".$record["OrderID"].",'".$record["OrderDate"]."',".$record["CustomerID"].")";
CustomQuery($sql);

For some reason this button doesn’t work, no records appear in OrdersArchive table when we click it. We need to make sure that our SQL query is correct.

First step is to open Chrome Developer Tools by clicking F12. Similar tools also exist in Firefox and Internet Explorer and F12 is the common hot key to open developers console.

Go to ‘Network’ tab. Click ‘Copy to archive’ button. You can see a request being sent to buttonhandler.php file.

Click ‘buttonhandler.php’ and go to ‘Preview’ tab. You can see the error message there (Unknown column ‘ANATR’ in ‘field list’) along with complete SQL Query.

Probably you have spotted the error already. The text value of ANATR must be wrapped by single quotes like this: ‘ANATR’.

Here is how we need to fix our code:

$record = $button->getCurrentRecord();
$sql = "insert into OrdersArchive (OrderID, OrderDate, CustomerID) 
values (".$record["OrderID"].",'".$record["OrderDate"]."','".$record["CustomerID"]."')";
CustomQuery($sql);

Better yet, we can use DAL which gives us cleaner code and also takes care of quoting automatically:

global $dal;
$record = $button->getCurrentRecord();

$tblArchive = $dal->Table("OrdersArchive");
$tblArchive->Value["OrderID"]=$record["OrderID"];
$tblArchive->Value["OrderDate"]=$record["OrderDate"];
$tblArchive->Value["CustomerID"]=$record["CustomerID"];

$tblArchive->Add();

And the same code for ASPRunner.NET:

dynamic record = button.getCurrentRecord();
dynamic tblArchive = GlobalVars.dal.Table("OrdersArchive");
tblArchive.Value["OrderID"] = record["OrderID"];
tblArchive.Value["OrderDate"] = record["OrderDate"];
tblArchive.Value["CustomerID"] = record["CustomerID"];
tblArchive.Add();

And for ASPRunnerPro:

DoAssignment record, button.getCurrentRecord()

dal.Table("OrdersArchive").Value("OrderID")=record("OrderID")
dal.Table("OrdersArchive").Value("OrderDate")=record("OrderDate")
dal.Table("OrdersArchive").Value("CustomerID")=record("CustomerID")
dal.Table("OrdersArchive").Add()