User supplier SQL queries for various purposes. In the classical version of ASP-db, there were many 'guesses' in constructing the SQL to retrieve the records under various circumstances. In the Tornado version, user are given an opportunity to take control and specify the exact SQL statements. If none is supplied, the 'guessing' effort will take place. The most expensive 'guessing' operation is the dbCountSQL to obtain the 'Recordset Count'. Specify this property as much as possible.

 

dbCountSQL

Obj.dbCountSQL = "Query Statement | none"
This is the SQL to obtain the record count of the recordset which is a very expensive operation. Specify this as much as possible. There are only 3 conditions that the bottom of the recordset is fetched and displayed as [x - y : total]. 

• The dbRecordCount is specified (Fast!)
• dbRecordCount = -1
• dbCountSQL is specified

Othewise, the records will be counted one at a time by advancing the record pointer. When manipulating a large database, it is most undesirable. 

In case like MS-SQL queries that includes "DISTINCT".  Count (*) or Count(1) seize to work and you must specify dbCountSQL="none" to do a straight count or an error will occur when Tornado tries to build the CountSQL. Recent ORACLE versions has ROWCOUNT to work with.

The worst case would be to query the DB and count one record at a time. In that case do not use the original SQL like SELECT F1, F2, F3.... FROM TABLE, WHERE ....ORDER BY ....

Use - SELECT F1 FROM TABLE WHERE...... (The minimum) to obtain the record count.

• X.dbCountSQL="SELECT COUNT(1) FROM TABLE"
• Obj.dbCountSQL="SELECT COUNT(*) FROM (SELECT DEPT.DEPTNO, DNAME, SUM(SAL) FROM EMP, DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO GROUP BY DEPT.DEPTNO, DNAME)"
• X.dbCountSQL = "SELECT count(*) FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID"
• IF DISTINCT, ALL or DISTINCTROW is int he SQL then the SQL will not be touched and (SELECT COUNT(1) (Original SQL - ORDER BY) will be used. ORDER BY will always be discarded in CountSQL.

 

dbColSortSQL

X.dbColSortSQL = "Select .... from ..... Where .... OREDR BY [[Col]]"

• The purpose of this property is to allow user to input a complex Query and leave a tag [[Col]] there to be filled in. The alternative is to let Tornado 'guess' by appending ORDER BY at the end of of the master SQL.
• This is the SQL to perform the column sort. If blank then ORDER BY will be appended to the dbSQL.
• The [[Col]] tag will be filled with the Column ordinals including the sort order.
• Do not specify the Order ASC/DESC as it is automatically set by the up and down arrows click.
• For example, if you clicked 1+ and 2- [[Col]] will be stuffed with CustomerID, OrderID DESC.

 

dbEditAddSQL

Obj.dbEditAddSQL = "Query Statement"

• Upon a complex query, you can eliminate the guessing work of ASPdb and specify how the Edit SQL should be. The field name and it's ordinal can be used interchangeably.
• X.dbEditAddSQL="INSERT INTO Table (Fld1,Fld2,fld3,GUID) VALUES ('[[text]]',#[[2]]#,[[num]],[[NewID()]])"
  Obj.dbEditUpdateSQL = "Query Statement"
  
  User defined Update query. There are two type of tags in this query - [[Input]] and {{Original}}. {{Input}} is the updated value and {{original}} is the original value used in the criteria.
  
  dbEditUpdateSQL="UPDATE FRANK SET YEAR='[[0]]', MONTH='[[MONTH]]', CITY='[[City]]',NAME='[[NAME]]',ADDRESS='[[ADDRESS]]',TAX=[[TAX]] WHERE CITY='{{City}}' and NAME='{{Name}}' and Address='{{Address}}' and TAX={{Tax}}"

 

dbFilterSQL

Obj.dbFilterSQL = "Query Statement"

• User defined filter query. Must include the [[Filter]] criteria tag to be stuffed by the user input data.
• X..dbFilterSQL = "Select * From products where [[Filter]]"
  Note: 'where' is optional. If missing, it'll be filled in

 

---

Send comments on this topic.
Copyright 1998-2006 ASP-db