Complicated args and sql

[Sparticuz]

I've got a fairly complicated (in my mind at least) sql command. Here it is:

SELECT count(*) FROM ( 
SELECT caller_fk,`end` FROM `tampa`.tm_log WHERE caller_fk = ':u' AND `end` BETWEEN ':from 00:00:00' AND ':to 23:59:59' AND call_outcome = :outcome 
UNION 
SELECT caller_fk,`end` FROM `orlando`.tm_log WHERE caller_fk = ':u' AND `end` BETWEEN ':from 00:00:00' AND ':to 23:59:59' AND call_outcome = :outcome 
UNION 
SELECT caller_fk,`end` FROM `baltimore`.tm_log WHERE caller_fk = ':u' AND `end` BETWEEN ':from 00:00:00' AND ':to 23:59:59' AND call_outcome = :outcome
UNION 
SELECT caller_fk,`end` FROM `birmingham`.tm_log WHERE caller_fk = ':u' AND `end` BETWEEN ':from 00:00:00' AND ':to 23:59:59' AND call_outcome = :outcome 
UNION 
SELECT caller_fk,`end` FROM `charlotte`.tm_log WHERE caller_fk = ':u' AND `end` BETWEEN ':from 00:00:00' AND ':to 23:59:59' AND call_outcome = :outcome ) 
as u

When I add in the args it doesn't work. For example, running this select as just a normal select works. I get a correct result back. When I add in the args it's always giving me a result of 0.

'Set up args
dim args as SQL::Arguments
args.add("u",rs.data("ulink"))
args.add("from",from)
args.add("to",to)
    
'Number of calls
args.add("outcome",1)
flag2 = cn.Execute(sqlCommand,args)
numbers = cn.ResultSet
calls = numbers.toString()

Am I doing something wrong?

[Davidk]

Re: Complicated args and sql

No idea... but I'm going to hazard a guess. Instead of using the same set of arguments in each select, try using a different set of arguments for each select... u1, u2, u3, u4, u5, u6, from1, etc.

Again, I've no idea why I would do this, but I would change then ending part of your statment... "as u" to something else... You're already using "u" - it just gets messy.

Finally, does the statement get resolved properly with the time you're putting in? I would check that too. I would construct the "from" and "to" fully and pass that in as an arg.

[Pat Bremkamp]

Re: Complicated args and sql

One of the advantages of using an argument is you don't need to put in the apostrophes around them, so instead of caller_fk = ':u', try caller_fk = :u

Also, you are using variable names that could be considered reserved words. You've already found that you need backticks around end. Might be safer to use words you are sure won't cause an issue, like time_to, argTo or from_time and argFrom instead of To and From.

[Sparticuz]

Re: Complicated args and sql

Yeah, I know about end, i'm also using start. I didn't realize they were reserved words until after I had already created the tables and the alpha components. I've switched to using date_from and date_to, and i changed the 'as' clause to be 'as num', but still isn't working. Is there a way to find out what alpha is resolving my sqlCommand with args to? I tried looking through cn, but didn't see anything.

[Sparticuz]

Re: Complicated args and sql

Is there a way to find out what alpha is resolving my sqlCommand with args to? I tried looking through cn, but didn't see anything.

Answering my own question, yes there is, http://wiki.alphasoftware.com/replac...ing%20Function, and looking at what is returning from that it looks like the problem is with the date fields, it's putting

'"2012-01-01" 00:00:00' AND '"2012-01-31" 23:59:59'

which is not correct sql.

[Sparticuz]

Re: Complicated args and sql

Yep, that was it. Once I fixed up the sql statement removing the ' ' around all the arguments, and also changed the args to be

	args.add("date_from",from+" 00:00:00")
	args.add("date_to",to+" 23:59:59")

It's working now :) Thanks.