Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-10-12
15
Medium Priority
?
814 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 225 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 150 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

705 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