|
Author
|
Topic: Good Database, Good Query, Bad Data With ASP
|
Stereo
Solid Nitrozanium SuperFan!
Member # 748
Member Rated:
|
posted April 27, 2004 08:19
Hello, me again. Got another crazy problem. (Is that a sigh I hear?)
I have those ASP report scripts I take care of. We updated the database they refer to from Access to SQL Server, so I tweaked the scripts so they would run again (date formats and some keywords changed). They returned some data, so I thought all was fine.
Wrong.
Last Friday, I was told the data returned in one script was bad. I do a check, and Hell's gates open before me. The very same query generated with ASP, when feeded directly to SQL server, has good results, but consistently returns bad data in the scripted page.
Anyone has an idea of what's going on? I think it's a SQLServer driver problem. OTOH, our sysadmin found out the servers' are missing a couple of SP. Could the answer be that easy?
I'm at lost on this problem. I'm a programmer, not a sysadmin! ![[shake head]](graemlins/shakehead.gif)
--------------------
Eppur, si muove!
Galileo Galilei
Posts: 2286 | From: Gatineau, Quebec, Canada | Registered: Apr 2001
| IP: Logged
|
|
Allan
SuperFan!
Member # 1717
Member Rated:
|
posted April 27, 2004 13:17
Yeah, I remember the last time, that was pretty good fun... OK not for you but I enjoyed it.
Oracle is my thing and I have fortunately been able to become less and less familiar with MSSQL but some things to check:
The asp script is accessing the same user schema as the running the script direct.
The asp script is not converting datatypes on the fly?
if you are comparing date values make sure you truncate the days back to equal units: for example 02-may-2004 00:00:00 = 02-may-2004 00:00:00 but 02-may-2004 09:00:00 != 02-may-2004 17:00:00
check for mixed cases, you may have different settings. If you need to do string comparisons force both expressions to uppercase or use upper case constants against forced expressions.
watch out for nulls they may be treated differently in ASP?
hope this helps
Posts: 1280 | From: Edinburgh, Scotland / Frankfurt, Germany | Registered: Oct 2002
| IP: Logged
|
|
Jace Raven
Solid Nitrozanium SuperFan!
Member # 2444
Member Rated:
|
posted April 27, 2004 13:25
<obligatory line of advise>Never expect to have sex with a donkey and not get kicked</advise>
Posts: 1791 | From: Seoul, Korea | Registered: Nov 2003
| IP: Logged
|
|
Allan
SuperFan!
Member # 1717
Member Rated:
|
posted April 27, 2004 13:54
quote:
Originally posted by Jace Raven:
<obligatory line of advise>Never expect to have sex with a donkey and not get kicked</advise>
Jace, did you get the threads mixed up or have you just been smoking some unfeasably strong weed?
Posts: 1280 | From: Edinburgh, Scotland / Frankfurt, Germany | Registered: Oct 2002
| IP: Logged
|
|
Stereo
Solid Nitrozanium SuperFan!
Member # 748
Member Rated:
|
posted April 27, 2004 14:17
(It could be that Jace was referring to the first line of your answer. Or that I am using MS product and having problem.)
A couple of things I know for sure: it's not the dates - I send them in format yyyymmdd, and they are interpreted as yyyymmdd 00:00:00 (and they are not compared for equatlity). Also, there are no comparisons with strings, so that's not the problem.
I'm pretty much sure it's not a schema or owner/permission problem, as the query via ASP returns data following the type of what's asked, and from the good tables, but it doesn't fit the filtering (people in the wrong division, etc.)
I'll check about nulls.
I've checked a few other scripts; they are all fine. Maybe comparing how they differ will give me a hint.
Keep trhowing ideas around. It could very well be something very stupid. Or something I have no control over. I'll tell you as soon as I find the culprit. Thanks.
--------------------
Eppur, si muove!
Galileo Galilei
Posts: 2286 | From: Gatineau, Quebec, Canada | Registered: Apr 2001
| IP: Logged
|
|
Allan
SuperFan!
Member # 1717
Member Rated:
|
posted April 27, 2004 14:44
If I've got a really tricky problem I use a count(*) for the return set and remove the where clause. Then build it back item by item, that way you can find what item makes the results different
Posts: 1280 | From: Edinburgh, Scotland / Frankfurt, Germany | Registered: Oct 2002
| IP: Logged
|
|
Stereo
Solid Nitrozanium SuperFan!
Member # 748
Member Rated:
|
posted April 28, 2004 09:40
Well, guess what? I looked beyond the query, and found out the results were compared to another query... on a string field. I've uppercased every occurence of that field, and now the results make more sense (I still have to double check in order to put the official 'bug squashed' stamp on the case).
Crazy little things one find out from someone else's scripts... Was it really that hard to use a nested query instead? Bah! ![[devil wand]](graemlins/devilwand.gif) ![[crazy]](graemlins/crazy.gif) ![[Embarrassed]](redface.gif)
Thanks for the help anyway.
Posts: 2286 | From: Gatineau, Quebec, Canada | Registered: Apr 2001
| IP: Logged
|
|
maven
Geek-in-Training
Member # 2714
Member Rated:
|
posted May 18, 2004 10:47
problem: you are using ASP and MSSQL on a Windows platform.
solution: use PHP and MySQL on a linux platform. (or windows, when you throw in that whole cross-compatibility thing...)
done.
(oh yeah, PHP5 is supposed to come with a compiler (like ASP's .NET) and is supposed to be fully object-oriented.
--------------------
"Never offend people with style when you can offend them with substance."
Posts: 32 | From: East Peoria, IL | Registered: May 2004
| IP: Logged
|
|
dragonman97
SuperFan!
Member # 780
Member Rated:
|
posted May 18, 2004 10:51
quote:
Originally posted by maven:
problem: you are using ASP and MSSQL on a Windows platform.
solution: use PHP and MySQL on a linux platform. (or windows, when you throw in that whole cross-compatibility thing...)
done.
(oh yeah, PHP5 is supposed to come with a compiler (like ASP's .NET) and is supposed to be fully object-oriented.
Unacceptable answer. It may be a technically superior solution (though I'll disagree about PHP), but many times, one does not the power or authority to change an existing setup, and must make the current system work.
--------------------
There are three things you can be sure of in life: Death, taxes, and reading about fake illnesses online...
Posts: 9038 | From: Westchester County, New York | Registered: May 2001
| IP: Logged
|
|
maven
Geek-in-Training
Member # 2714
Member Rated:
|
posted May 18, 2004 10:58
unfortunately enough.
just wait until the fifth installment of PHP is done. it may just change your mind.
--------------------
"Never offend people with style when you can offend them with substance."
Posts: 32 | From: East Peoria, IL | Registered: May 2004
| IP: Logged
|
|
Allan
SuperFan!
Member # 1717
Member Rated:
|
posted May 18, 2004 11:11
quote:
Originally posted by maven:
unfortunately enough.
just wait until the fifth installment of PHP is done. it may just change your mind.
No, it won't. You don't pay consultants 500 USD + per day to reprogram your system with all related change management because a scripting language is better.
Posts: 1280 | From: Edinburgh, Scotland / Frankfurt, Germany | Registered: Oct 2002
| IP: Logged
|
|
maven
Geek-in-Training
Member # 2714
Member Rated:
|
posted May 18, 2004 11:19
pff. i'd do it for free.
although... if you know anyone who wants to pay me 500 bucks a day for this, tell 'em to email me.
--------------------
"Never offend people with style when you can offend them with substance."
Posts: 32 | From: East Peoria, IL | Registered: May 2004
| IP: Logged
|
|
Stereo
Solid Nitrozanium SuperFan!
Member # 748
Member Rated:
|
posted May 18, 2004 12:48
Well, we'll be redoing the website over the summer, and we're changing to <drumroll>ASP.net!</drumroll> Yeah! ... Um, sorry, I mean: Bleah!
Give me OSX Server and WebObject anytime, and for database, mySQL would do fine, while there are other options.
But, working for the government and all, some stupid decisions are made, like the Uniform Platform Policy - everyone: Windows only! (Ok, not true. That's more like if you use something else, you're on your own. Which wouldn't be that bad. But as dman guessed, I don't have decision power over those things, plus I'd have to take into account the one who'll take care after me may not* know a thing about other options.)
All in all, just a another reason to go on with my master degree, so I can easily get out of here if worse comes to worst.
P.S.: For those who may care, the results are in, and the conditional admission is now definitive! (A- on the course I needed a B to go on. And A on the other.) Woohoo!
*End of afternoon language musing: if "can not" can be shortened into "can't", why not use "main't" fo "may not"?
--------------------
Eppur, si muove!
Galileo Galilei
Posts: 2286 | From: Gatineau, Quebec, Canada | Registered: Apr 2001
| IP: Logged
|
|
nekomatic
SuperFan!
Member # 376
Member Rated:
|
posted May 19, 2004 02:00
quote:
Originally posted by Stereo:
if "can not" can be shortened into "can't", why not use "main't" fo "may not"?
If you look back a few years in literature (as a scientist, I really had to fight the instinct to type "the literature" ![[Wink]](wink.gif) ) I think you'll find "mayn't" in common use. Try Alice's Adventures in Wonderland for a start (can't swear it's in there, but you'll enjoy re-reading it in any case ![[Big Grin]](biggrin.gif) ) Incidentally, what you'll also find in Alice's... (unless you have a modern edition where they've tidied it up) is the technically correct (if awkward) approach to apostrophisation in abbreviations like "sha'n't", "ca'n't", etc.
Personally I find all these forms quite endearing, and perhaps we should start a campaign for their reintroduction. Who's with me? Callipygous? ![[Smile]](smile.gif)
Posts: 822 | From: Manchester, UK | Registered: Mar 2000
| IP: Logged
|
|
Stereo
Solid Nitrozanium SuperFan!
Member # 748
Member Rated:
|
posted May 19, 2004 03:59
Thanks Neko. I always thought I'd someday get myself some British and US classicals in their original language. Maybe it's time for me to do so!
--------------------
Eppur, si muove!
Galileo Galilei
Posts: 2286 | From: Gatineau, Quebec, Canada | Registered: Apr 2001
| IP: Logged
|
|
nekomatic
SuperFan!
Member # 376
Member Rated:
|
posted May 21, 2004 02:38
Lewis Carroll would surely be a good place to start...
...save Tristram Shandy for later on... maybe when you have about a month with nothing to do
Posts: 822 | From: Manchester, UK | Registered: Mar 2000
| IP: Logged
|
|
|
UBBFriend: Email this page to someone!
Printer-friendly view of this topic
