sql query containing "+" sign

DonnaMoores

I have a bit of code which has been working for ages where I get the number of rows returned from a database query as shown below

var $length;
_set($length, _table($controlName).rows.length);
var $rowCount;
_set($rowCount, adHocQueryMicrosoft("SELECT COUNT = COUNT(*) FROM TABLE_NAME_LOOKUP", "COUNT"));
_assertEqual($length, $rowCount, "The expected number of entries have not been displayed in the DataGrid");

and in my cmd prompt window from where I ran sahi.bat I see that the following code is executed

SELECT COUNT = COUNT(*) FROM TABLE_NAME_LOOKUP

however I have a scenario where I need to take into account that my "$controlName" has a header row. So I have created another function with

var $length;
_set($length, _table($controlName).rows.length);
var $rowCount;
_set($rowCount, adHocQueryMicrosoft("SELECT COUNT = COUNT(*) + 1 FROM TABLE_NAME_LOOKUP", "COUNT"));
_assertEqual($length, $rowCount, "The expected number of entries have not been displayed in the DataGrid");

however in my cmd prompt window from where I ran sahi.bat I see the following

SELECT COUNT = COUNT(*)   1 FROM TABLE_NAME_LOOKUP

Any suggestions on how I get the "+" to appear in this command.


Just a quick update on this I tried to substitute

_set($rowCount, adHocQueryMicrosoft("SELECT COUNT = COUNT(*) + 1 FROM TABLE_NAME_LOOKUP", "COUNT"));

for

_set($rowCount, adHocQueryMicrosoft("SELECT COUNT = COUNT(*)\u002b1 FROM TABLE_NAME_LOOKUP", "COUNT"));

to insert the unicode equivalent in the string, but this also failed.... the message was successfully displayed on its first iteration in the cmd prompt console as shown:

SQL: SELECT COUNT = COUNT(*)+1 FROM TABLE_NAME_LOOKUP

but on the second iteration through it reverted back to being incorrect as shown:

SQL: SELECT COUNT = COUNT(*) 1 FROM TABLE_NAME_LOOKUP

Cheers
Donna

kevinkole

I don't see anything wrong with just moving +1 to the assertion statement:

_set($rowCount, adHocQueryMicrosoft("SELECT COUNT = COUNT(*) FROM TABLE_NAME_LOOKUP", "COUNT"));
_assertEqual($length, $rowCount + 1, "...");

DonnaMoores

Yeah, i thought that too but it just concatenates 1 on to the string rather than performing the mathmatical function

narayan

Will look at this tonight.

DonnaMoores

I have a work around for this problem. I change my SQL Statement to contain a union, so that I can append a dummy row to the data I return, and then get the row count. The number of rows and the datatype of rows is the same in both parts of the union

My new SQL is now

SELECT COUNT = COUNT(*) FROM (SELECT * FROM TABLE_NAME UNION SELECT -1, 'DUMMY', 'DUMMY', 'DUMMY') AS CountQuery

I would prefer to just be able to add/subtract from a value, but at least I can continue with my work.

Cheers
Donna

kevinkole

Sorry, I forgot about the string to number conversion: (javascripter.net/faq/convert2.htm)

_assertEqual($length, parseInt($rowCount) + 1, "...");