ODBC Errors In Query

I normally use MySQL with PHP, but I'm delving into connecting to Access
with ODBC (for a database that I already have set up at work), and I'm
running into a couple of errors. I'm just trying to do a simple query
with two columns - one a name (VARCHAR), and one an integer column.

First, if I add an ORDER BY clause, so the query looks like this:

$sql = "select tblScores.FullName, sum(tblScores.Points) as TotalPoints
from tblScores WHERE Division='MO'";
$sql = $sql." GROUP BY tblScores.FullName";
$sql
= $sql." ORDER BY TotalPoints desc";

I get an ODBC error that says:

"odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 1., SQL state 07001 in SQLExecDirect in
c:\program files\apache group\apache\htdocs\odbc\file.php on line 15"

However, if I remove the ORDER BY clause, the query runs (although the
data isn't sorted the way I would like). I have a feeling it might be
something in my SQL, but I can't see it.

Later, I did discover that if I use

ORDER BY sum(tblScores.Points)

instead of the alias (TotalPoints) the query works fine. That seems odd
to me, because everything I've read is that you should be able to use an
alias in an ORDER BY clause. Why would it behave that way when
connecting via ODBC and not for MySQL?

The second thing I'm noticing is that even though I'm using an integer
field, there is a decimal on the end of each number (i.e. 25.0 instead
of 25). When I run the same query using MySQL, there is no decimal
point, which is as it should be.

Can anyone explain why I am getting these errors?

Thanks.

Steve

re: ODBC Errors In Query

Steve <racquetballer@hotmail.com> wrote:
[color=blue]
>I normally use MySQL with PHP, but I'm delving into connecting to Access
>with ODBC (for a database that I already have set up at work), and I'm
>running into a couple of errors. I'm just trying to do a simple query
>with two columns - one a name (VARCHAR), and one an integer column.
>
>First, if I add an ORDER BY clause, so the query looks like this:
>
>$sql = "select tblScores.FullName, sum(tblScores.Points) as TotalPoints
>from tblScores WHERE Division='MO'";
>$sql = $sql." GROUP BY tblScores.FullName";
>$sql[/color]
= $sql." ORDER BY TotalPoints desc";[color=blue]
>
>I get an ODBC error that says:
>
>"odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too
>few parameters. Expected 1., SQL state 07001 in SQLExecDirect in
>c:\program files\apache group\apache\htdocs\odbc\file.php on line 15"
>
>However, if I remove the ORDER BY clause, the query runs (although the
>data isn't sorted the way I would like). I have a feeling it might be
>something in my SQL, but I can't see it.
>
>Later, I did discover that if I use
>
>ORDER BY sum(tblScores.Points)
>
>instead of the alias (TotalPoints) the query works fine. That seems odd
>to me, because everything I've read is that you should be able to use an
>alias in an ORDER BY clause. Why would it behave that way when
>connecting via ODBC and not for MySQL?[/color]

If the alias works in MySQL, it is an extension. Standard SQL requires
that you use the full text of the field.
[color=blue]
>The second thing I'm noticing is that even though I'm using an integer
>field, there is a decimal on the end of each number (i.e. 25.0 instead
>of 25). When I run the same query using MySQL, there is no decimal
>point, which is as it should be.[/color]

What is the type of the Access field, exactly? Is it Currency?
[color=blue]
>Can anyone explain why I am getting these errors?[/color]

In short, because Access is a piece of crap. You would do better with
SQLite.
--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

re: ODBC Errors In Query

Tim Roberts wrote:
[color=blue][color=green]
>>The second thing I'm noticing is that even though I'm using an integer
>>field, there is a decimal on the end of each number (i.e. 25.0 instead
>>of 25). When I run the same query using MySQL, there is no decimal
>>point, which is as it should be.[/color]
>
>
> What is the type of the Access field, exactly? Is it Currency?[/color]

No it is Integer. That's why I'm confused as to why there would be a
decimal place.
[color=blue]
>
>[color=green]
>>Can anyone explain why I am getting these errors?[/color]
>
>
> In short, because Access is a piece of crap. You would do better with
> SQLite.[/color]

This was set up a few years ago, and changing it isn't an option right now.

Thanks.

Steve