SELECT SUM(MyColumn) not supported by .NET OleDB objects?

I am using MySQL 4.1 Gamma on WinXP. I am experiencing a very weird problem where a command which works fine under the mysql command-line tool fails when issued within my program (table has two columns, a DateTime called time and an INT called total):

catCMD.CommandText = "SELECT sum(total) from MyTable WHERE time>"2004-10-02 09:30:00" AND time<"2004-10-02 16:00:00";
myReader = catCMD.ExecuteReader();

int g=0;
while (myReader.Read())
{
if (myReader.FieldCount > 0) g++;
}
myReader.Close();

Under mysql, I get the correct sum of 'total' values in the set matching the condition:
+-----------+
| SUM(total) |
+-----------+
| 72 |
+-----------+

But from the program I just get one row with no fields (.FieldCount is 0) !
If I change the select to, for example:
SELECT * from MyTable WHERE time>"2004-10-02 09:30:00" AND time<"2004-10-02 16:00:00";

all is well and I get what I expect (many rows, none empty). Same with "SELECT total", etc

From experimentation, it seems the problem element is the 'sum' function of the top-level SELECT.
Could there be an issue with one of the layers of software (such as the .NET OleDb objects or the OLE DB driver) to explain this? I has assumed that nobody really looks at the command string except the server itself but I could be mistaken.

Thanks!
Patrick
patrickqAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JakobAConnect With a Mentor Commented:
Your string is not proper syntax. the "-quotes are wrong

I do not know ASP so the below string may be incorrect too:

"SELECT sum(total) from MyTable WHERE time>'2004-10-02 09:30:00' AND time<'2004-10-02 16:00:00' "

but I rather expect it to work :-)

I have changed so there are only 2 "-quotes (one at each end to delimit the string)
and placed '-quotes around the date constants

regards JakobA
0
 
JakobACommented:
But no. wasent ' (single quote) the character mbasic used for comments. then it probably still is. And the way to plave a quote inside a string would then be to write it twice:

"SELECT sum(total) from MyTable WHERE time>""2004-10-02 09:30:00"" AND time<""2004-10-02 16:00:00"" "

try that if the string in the first post do not work.
0
 
patrickqAuthor Commented:
Hi Jakob,

It's not the quotes ... your single quotes work as well as the double quotes, but the statement still doesn't work from within my C# .NET program using the OleDb objects: it's not that the statement fails in terms of generating an OleDbException, but it simply returns one row with FieldCount=0 (i.e. nothing), instead of the single value I expect.

All is well if I just change "sum(total)" to "total" (but then I have to sum-up the numbers in my program).

In fact, I have unnecessarily complicated the question:
SELECT sum(total) FROM MyTable;
(where 'total' is a field in the table)

FAILS from within my program (returns one single row, but with nothing in it - FieldCount=0), works fine under mysql.

It's as if something with the OleDB support simply chokes on "sum(x)" ...

Very strange - any idea?

Thanks,
Patrick
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
JakobACommented:
farfetched ones only I fear, it just seems impossible.

thy this command:
    SELECT sum(total) AS totalsum FROM MyTable;
on the assumption that the resultset generator chokes on a columnname like 'sum(total)'  (parentheses are usually illegal characters in a name)
0
 
patrickqAuthor Commented:
Same exact outcome: one row with FieldCount=0 when using sum(x), even if I add a "AS y" ...

As a result, I am having give up on sum() altogether and am simply looping in my code to add the numbers.

Should I give up?

Thanks,
Patrick
0
 
jdlambert1Connect With a Mentor Commented:
Don't give up. ".FieldCount" is the number of column returned, and it's zero-based, so a FieldCount of 0 mean you got one column returned. Change the interior quotes to singles (VB uses a single quote to start comments, but it's not read that way when it's in a double-quoted string) (and you were missing a quote in your origianl post), and give your column an alias. Then refer to the column by it's aliased name to get the count. This will be much faster than looping.

catCMD.CommandText = "SELECT Sum(total) AS Cnt FROM MyTable WHERE time > '2004-10-02 09:30:00' AND time < '2004-10-02 16:00:00'";
myReader = catCMD.ExecuteReader();

int g=0;
while (myReader.Read())
{
if (myReader("Cnt") > 0) g++;
0
 
jdlambert1Commented:
Oops. Left out .tostring, assuming this is C#:

if (myReader("Cnt").ToString() > 0) g++;
0
 
JakobACommented:
You gotta be kidding, why is it nessesarry to convert to string in order to compare with a number ?
0
 
jdlambert1Commented:
Sorry, just tired. My first post had it right -- no need to convert to compare with zero.
0
 
patrickqAuthor Commented:
Thanks guys, I closed the question and split the points.
Note however that in spite of your attempts, something still chockes on sum() in any top-level SELECT for me. So I ended-up doing:

SELECT t1.mysum FROM (SELECT SUM(total) AS mysum FROM Yahoo_MSFT WHERE date(time)='2004-06-14' AS t1;

Seems crazy to go around it like that, but this does work, anything else failed.

Two other comments:

1. FieldCount is not 0-based: if I treat it as 0-based, then anything I do with the reader object & an index of 0 (eg myReader.GetFieldType(0))  generates an exception (index outside of bounds). So FieldCount == 0 does mean no data in row.

2. I think it makes sense, as you mentioned, to specifically select the field based on its name, but the reader object didn't let me invoke it as in myReader("Cnt"). So I am just reading the first column without addressing it by name (which is less robust coding, if something changes in the query).

Thanks,
Patrick
0
 
JakobACommented:
Thank you.

But I dont really think I should get points when I did not prowide a usefull answer.

If you like I will post a Question in the C# topic area. I suspect the question will get a better answer there.

regards JakobA
0
 
patrickqAuthor Commented:
It would be splendid if you could - I'd love to get to the bottom of this, if only out of curiosity.
The bottom-line is that:
1. The OleDBCommand object accept the "SELECT sum(x)" statement
2. Successfully returns an OleDBDateReader object
3. However, this OleDBDataReader contains a single row with no data in it (.FieldCount = 0)
4. For some reason, modifying the select to "SELECT t1.mysum FROM (SELECT (sum(x) as mysum ...) AS t1;" works

Thanks,
Patrick
0
 
jdlambert1Commented:
An error in my first post... C# uses brackets (it's VB that uses parentheses):

if (myReader["Cnt"] > 0) g++;
0
 
patrickqAuthor Commented:
I understand what you are saying: the OleDBReader object supports indexing by way of the column name - that's useful to know and I will certainly use that technique when facing a row with more than one column, or even with a single column (as a way to validate that I am getting what I am expecting).

BUT: please note that it doesn't help in this particular case, as all attempts to index the reader (whether with 0 or using the colum name) all result in an "out of bounds" error. The row is certifiably empty, as if SQL didn't support sum(x) (although it does support it when use in an inside SELECT).
0
 
JakobACommented:
>> patrickq
see: http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21168517.html

(remember you wont get notiefies from there unless you post something.)

regards JakobA
0
All Courses

From novice to tech pro — start learning today.