Solved

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

Posted on 2004-10-12
15
790 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now