Solved

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

Posted on 2004-10-12
15
808 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:patrickq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
15 Comments
 
LVL 15

Accepted Solution

by:
JakobA earned 75 total points
ID: 12294176
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
 
LVL 15

Expert Comment

by:JakobA
ID: 12294273
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
 

Author Comment

by:patrickq
ID: 12294304
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 15

Expert Comment

by:JakobA
ID: 12294450
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
 

Author Comment

by:patrickq
ID: 12301273
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
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 50 total points
ID: 12301576
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12301660
Oops. Left out .tostring, assuming this is C#:

if (myReader("Cnt").ToString() > 0) g++;
0
 
LVL 15

Expert Comment

by:JakobA
ID: 12301965
You gotta be kidding, why is it nessesarry to convert to string in order to compare with a number ?
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12302062
Sorry, just tired. My first post had it right -- no need to convert to compare with zero.
0
 

Author Comment

by:patrickq
ID: 12302307
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
 
LVL 15

Expert Comment

by:JakobA
ID: 12302410
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
 

Author Comment

by:patrickq
ID: 12308281
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12308678
An error in my first post... C# uses brackets (it's VB that uses parentheses):

if (myReader["Cnt"] > 0) g++;
0
 

Author Comment

by:patrickq
ID: 12309128
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
 
LVL 15

Expert Comment

by:JakobA
ID: 12309556
>> 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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question