Can you use DSUM in CFC query?

Report · Jan 18, 2010

I am trying to create a query that returns,

Today, MTD, and YTD columns. It can be done in Access like this:

SELECT G.TranDate, G.Desc
, DSum("amount","GLTest","trandate =DateSerial(2009, 2, 1)") AS Today
, DSum("amount","GLTest","trandate >=DateSerial(2009, 2, 1) AND trandate <= DateSerial(2009, 2, 15)") AS MTD
, DSum("amount","GLTest","trandate >=DateSerial(2009, 1, 1) AND trandate <= DateSerial(2009, 2, 15)") AS YTD
, DSum("amount","GLTest","trandate =DateSerial(2008, 2, 1)") AS LY_Today
, DSum("amount","GLTest","trandate >=DateSerial(2008, 2, 1) AND trandate <= DateSerial(2008, 2, 15)") AS LY_MTD
, DSum("amount","GLTest","trandate >=DateSerial(2008, 1, 1) AND trandate <= DateSerial(2008, 2, 15)") AS LY_YTD

FROM GLTest AS G
WHERE (((G.[TranDate])=#2/15/2009#))
ORDER BY 1;

But, this is the result in an actual CFC test:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft]
[ODBC Microsoft Access Driver] Too few parameters. Expected 2.

The error occurred in C:\Inetpub\wwwroot\Daily2006NoForms\CFCS\Test.cfc:
Called from C:\Inetpub\wwwroot\Daily2006NoForms\cfcTester.cfm: line 124
Called from C:\Inetpub\wwwroot\Daily2006NoForms\CFCS\Test.cfc: line 169
Called from C:\Inetpub\wwwroot\Daily2006NoForms\cfcTester.cfm: line 124

<cfquery>
SELECT glt_trandate, glt_desc, glt_amount
, DSUM("glt_amount", "GLTrans", "glt_trandate =#createODBCDate(toDate)#") AS MTD
FROM GLTrans
WHERE ((glt_trandate) = #createODBCDate(toDate)#)
</cfquery>

--------------------------------------------------------------------------------
This is what is actually being sent.
SELECT glt_trandate, glt_desc, glt_amount
, DSUM("glt_amount", "GLTrans", "glt_trandate ={d '2009-12-02'}") AS MTD FROM GLTrans
WHERE ((glt_trandate) = {d '2009-12-02'})

Report · Jan 18, 2010

You can - for all intents and purposes - do any SQL you like in a <cfquery>, provided your DB can understand it. CF doesn't do anything to the SQL other than pass it to the DB drive (having first resolved any CFML expressions to generate the resultant SQL string.

Your problem is that you're not passing your date values in a format that Access can understand: as you demonstrate, the format of the dates you're passing isn't the same as Access accepts. The way CF casts a date to a string (with the curly brackets) is not the exact same format that Access accepts (with the pound signs).

To avoid this sort of thing, whenever you want to pass a dynamic value to the DB, it's best to pass it as a parameter, not hard-coded into the SQL string. That way the DB driver will work as an intermediary between CF and the DB and make sure the correct data ends up in the correct format.

Look up <cfqueryparam> in the docs.

--

Adam

Report · Jan 18, 2010

Adam, here is the entire function. I have to admit I have used the CreateDate and createODBCDate like this in my CFC's for years. Certainly doesn't mean it's right and I will check into using a QueryParam. I think there's something wrong in the format.

<cffunction name="getTestDSum" access="public" returntype="struct" output="false">
<cfargument name="Args" type="struct" required="yes">
<cfset var getDsum = "" />
<cfset var to_TY_Date = "" />
<cfset var from_TY_MTD = "" />
<cfset INIT(Args) />

<cfset to_TY_Date = CreateDate(arguments.Args.INYEAR,arguments.Args.INMONTH,arguments.Args.INDAY) />
<cfset from_TY_MTD = CreateDate(arguments.Args.INYEAR,arguments.Args.INMONTH,1) />

<cfquery name="getDsum" datasource="#arguments.Args.INDSN#" dbtype="odbc">
   SELECT glt_trandate, glt_desc, glt_amount
   , DSUM("glt_amount", "GLTrans", "glt_trandate >=#createODBCDate(from_TY_MTD)#
    AND glt_trandate >=#createODBCDate(to_TY_Date)#") AS MTD
   FROM GLTrans
   WHERE ((glt_trandate) = #createODBCDate(to_TY_Date)#)
</cfquery>

Report · Jan 18, 2010

I'll first-up concede that I have't used Access since I was at polytech. So anything I offer up here is going to be vague, and based on recollections of reading about other people's problems with it.

I'd usually strum up an Access DB and have a mess around with it, but I don't have MS Office installed on this machine, and I can't be arsed downloading a trial of the thing just to sort out one query. I'm sure you understand 😉

Firstly, one recollection I have is that whilst the query tool in Access is quite forgiving of non-standard SQL, JDBC drivers are not. One thing that could cause gyp is using double-quotes instead of single-quotes. I know the Acces query tool will accept double quotes, but they're not actually valid in SQL. So perhaps try single quotes instead.

Also I am really surprised that you have success using CF's "ODBC date format" - eg: {d '2009-12-02'} - when Access expects dates like this: #2/12/2009#. I know this has not worked for people in the past. I think just '2009-12-02' might work, but don't quote me on that. Or just do it "properly" my using a <cfqueryparam> (which as I touched on, you should be doing as a matter of course anyhow).

That "Too few parameters" error Access spits out is a fairly ubiquitous one: it seems to be what it says whenever it sees an expression it doesn't understand. So I would not put too much stock in that actually being the problem.

Have you tried taking the SQL that CF is passing to the driver, and running that straight in Access's query tool? It might give a better error.

Of course no post about Access problems would be complete without the caveat that Access is not intended to be used as a server-based DB solution, it's a desktop app, so it's completely inappropriate to be used as a DB for a multi-user environment such as a website back-end. So if there's any way you could possibly stop using Access and use a proper DB system, that would be the best thing to do: if, for example, you are only just starting the project and you would not take too much of a hit from swapping Access out. It might not be an option, or it might be outwith your control, I guess.

--

Adam

Report · Jan 18, 2010

Adam,

I can't thank you enough for your time, but I will respect it and take your advice. Let me do some homework this evening and I"ll let you know what happens.

Jamie

Report · Jan 18, 2010

No probs. There are other people around here who know a lot more about Access than me (and if that's not damning with faint praise, I don't know what is ;-), so hopefully they'll pitch in with some better-informed advice.

--

Adam

Report · Jan 18, 2010

I don't use Access either, but I'm not sure Adam's guess was correct. It's easy enough to check though. In this query,

SELECT glt_trandate, glt_desc, glt_amount
, DSUM("glt_amount", "GLTrans", "glt_trandate =#createODBCDate(toDate)#") AS MTD
FROM GLTrans
WHERE ((glt_trandate) = #createODBCDate(toDate)#)

take out this part

, DSUM("glt_amount", "GLTrans", "glt_trandate =#createODBCDate(toDate)#") AS MTD

If it runs successfully, the problem is with the dsum function.