Solved

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

Posted on 2004-10-12
15
800 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
simple mysql statement 3 43
Trigger usage 2 75
Problem with Simple PHP/mySQL Query 3 74
MYSQL database problem within Coldfusion 2016 environment 12 49
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

791 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