Upsizing issue

Hi

I'm trying to upsize a large Jet-based ASP application to use MS SQL
Server.

I am experiencing a slightly different behaviour when getting results
of a SQL join within a Recordset.

For example, a query such as:

SELECT *
FROM Accounts
INNER JOIN Categories
ON Categories.CategoryId = Accounts.CategoryId

will return a Recordset with (amongts others) two CategoryId fields,
one in
Accounts and another in Categories.

When using Jet, the fields will be called

Accounts.CategoryId
Categories.CategoryId

while when using SQL Server, there will be two objects in
the Recordset, each called CategoryId and each obviously having
the same value.

The problem is that the existing ASP application heavily uses
expressions such as rsQuery("Accounts.CategoryId") while this
would not work with SQL Server database - you'd have to
simply say rsQuery("CategoryId").

So the question is: is it possible to change a setting on the
ADODB connection or recordset to the SQL Server so that Recordset
behaves like when using JET, i.e. returing a full field
name (Table.Column) when necessary (when there are two fields
with the same name but in different tables).

Any ideas very much appreciated. Thank you.

Re: Upsizing issue

FOr starters, you shouldn't be using SELECT *. Name the columns you want.

SELECT Categories.CategoryID,Accounts.CategoryID...

But why do you need to bring them both back? Can they ever not be identical
since you're joining on their equality?

Ray at work

"MIMF" wrote in message
news:1113400522.157117.159140@z14g2000cwz.googlegroups.com.. .
> Hi
>
> I'm trying to upsize a large Jet-based ASP application to use MS SQL
> Server.
>
> I am experiencing a slightly different behaviour when getting results
> of a SQL join within a Recordset.
>
> For example, a query such as:
>
> SELECT *
> FROM Accounts
> INNER JOIN Categories
> ON Categories.CategoryId = Accounts.CategoryId
>
> will return a Recordset with (amongts others) two CategoryId fields,
> one in
> Accounts and another in Categories.
>
> When using Jet, the fields will be called
>
> Accounts.CategoryId
> Categories.CategoryId
>
> while when using SQL Server, there will be two objects in
> the Recordset, each called CategoryId and each obviously having
> the same value.
>
> The problem is that the existing ASP application heavily uses
> expressions such as rsQuery("Accounts.CategoryId") while this
> would not work with SQL Server database - you'd have to
> simply say rsQuery("CategoryId").
>
> So the question is: is it possible to change a setting on the
> ADODB connection or recordset to the SQL Server so that Recordset
> behaves like when using JET, i.e. returing a full field
> name (Table.Column) when necessary (when there are two fields
> with the same name but in different tables).
>
> Any ideas very much appreciated. Thank you.
>

Re: Upsizing issue

MIMF wrote:
> Hi
>
> I'm trying to upsize a large Jet-based ASP application to use MS SQL
> Server.
>
> I am experiencing a slightly different behaviour when getting results
> of a SQL join within a Recordset.
>
> For example, a query such as:
>
> SELECT *
> FROM Accounts
> INNER JOIN Categories
> ON Categories.CategoryId = Accounts.CategoryId
>
> will return a Recordset with (amongts others) two CategoryId fields,
> one in
> Accounts and another in Categories.
>
> When using Jet, the fields will be called
>
> Accounts.CategoryId
> Categories.CategoryId
>
> while when using SQL Server, there will be two objects in
> the Recordset, each called CategoryId and each obviously having
> the same value.
>
> The problem is that the existing ASP application heavily uses
> expressions such as rsQuery("Accounts.CategoryId") while this
> would not work with SQL Server database - you'd have to
> simply say rsQuery("CategoryId").
>
> So the question is: is it possible to change a setting on the
> ADODB connection or recordset to the SQL Server so that Recordset
> behaves like when using JET, i.e. returing a full field
> name (Table.Column) when necessary (when there are two fields
> with the same name but in different tables).
>
Nope. You will have to stop using selstar, which is a bad practice anyways
(http://www.aspfaq.com/show.asp?id=2096)

The biggest question is: why waste resources and increase network traffic by
returning the same data twice?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Upsizing issue

You didn't read the question carefully, otherwise you wouldn't be
asking a stupid question and giving a stupid recommendation that
doesn't apply to this problem.

Re: Upsizing issue

If you can't answer a question, don't bother giving useless lectures.

The application was NOT developed by me, therefore I have no choice. I
wish to migrate it to SQL server without recoding the whole thing. I
specified that clearly in my original post.

Re: Upsizing issue

MIMF wrote:
> If you can't answer a question, don't bother giving useless lectures.
>
> The application was NOT developed by me, therefore I have no choice. I
> wish to migrate it to SQL server without recoding the whole thing. I
> specified that clearly in my original post.

There is no way to avoid some recoding. There is a way to minimize it, but
your attitude towards both me and Ray has made me reluctant to have any
further correspondence with you.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Upsizing issue

Alright.

Ray at work

"MIMF" wrote in message
news:1114091242.006746.153560@o13g2000cwo.googlegroups.com.. .
> You didn't read the question carefully, otherwise you wouldn't be
> asking a stupid question and giving a stupid recommendation that
> doesn't apply to this problem.
>

Re: Upsizing issue

There's recoding and recoding. Doing a simple search and replace is one
thing, but getting into the logic of the application is a totally
different problem when the application is several megabytes in size and
very complex.

How about implementing a MyADODB.Recordset which takes the
ADODB.Recordset object and makes sure the data returned is in the
format the application expects? Or implementing a function call that
would rearrange ADODB.Recordset accordingly? That would be much simpler
than going through each and every query, seeing which columns the query
consumer REALLY needs, making sure you're right and then repeat that
for 20 months. I was hoping things would be as simple as setting the
right property on the recordset. If there is, you obviously don't know
anything about it.

So, you see, there could be ways to make this simple, but your reply
was just a generic "i don't know how to help you, but let me just make
sure everyone thinks i'm smart" crap. I can't see how's that helpful in
any way either. And I see that in Usenet groups quite often. I'd rather
have one meaningful reply to my message than a hundred people
misunderstanding the question, answering a question that no one asked,
giving lectures about unrelated issues, showing how smart they think
they are and so on. Just try to understand that problem, if you don't,
leave the problem to few others who might actually do.

Re: Upsizing issue

"MIMF" wrote in message
news:1114157202.797687.230970@o13g2000cwo.googlegroups.com.. .
: There's recoding and recoding.

There's a grammar checker too. Jes' sayin'... Better add me to the list.

: Doing a simple search and replace is one
: thing, but getting into the logic of the application is a totally
: different problem when the application is several megabytes in size and
: very complex.

Ya', it requires effort. Currently it appears you're trying to manipulate a
bad situation and biting the hands of those that can help you the most.
You're not the sharpest crayon in the box, are ya'?

: How about implementing a MyADODB.Recordset which takes the
: ADODB.Recordset object and makes sure the data returned is in the
: format the application expects? Or implementing a function call that
: would rearrange ADODB.Recordset accordingly? That would be much simpler
: than going through each and every query, seeing which columns the query
: consumer REALLY needs, making sure you're right and then repeat that
: for 20 months.

Well, ok, get to it. You're wasting time bitching out anyone offering you
help. This code needs more help than what you're asking for and you need
counseling.

: I was hoping things would be as simple as setting the
: right property on the recordset.

Well, I guess that idea is all shot to hell. I was hoping to know the
winning lottery ticket numbers BEFORE they were announced. I guess we're
both screwed.

: If there is, you obviously don't know anything about it.

If there is, you don't either Einstein.

If there was, he would have mentioned it. Bob will forget more than you AND
I will ever know about SQL and databases. There are a few others here that
are very good too but I doubt they're willing to offer any suggestions now.
Currently you're batting 1000. Two up, two down. It's a baseball metaphor
so if you're baseball challenged too, let me know and I'll clarify.

: So, you see, there could be ways to make this simple,

So, you're saying you already have an answer and yet you're taken time out
of your busy day to tell us all how fricken stupid we are?

: but your reply
: was just a generic "i don't know how to help you, but let me just make
: sure everyone thinks i'm smart" crap.

Nah, we already know Bob's smart. We're just not sure if his IQ level is an
int or a long. I guess that makes yours a signed int.

: I can't see how's that helpful in any way either.

Perhaps one has to be smart to see it? You could have always chosen blind
faith. After all you accepted this project without knowing how to solve it.
At least you would be getting more responses. You could have waited until
you got all the help you needed before deciding to tear everyone a new one
but then I guess you're just not that smart now are ya, Willy?

: And I see that in Usenet groups quite often.

Well then hire a real programmer to fix your issue and quit depending on
free advice which you so quickly ridicule.

: I'd rather have one meaningful reply to my message than a hundred people
: misunderstanding the question,

You get what you pay for. So, pay them to blow smoke up your ass because
that's the only way to achieve what you expect on any subject unless of
course they're gay so then your ass might be the pivotal point of their
focus.

: answering a question that no one asked,

Most answers given are ones that people do not ask because they try to fully
help them, not pacify them and send them on their way.

: giving lectures about unrelated issues,

It's related. Face it, you're blind.

: showing how smart they think they are and so on.

How smart can anyone be giving away free advice for something they do to
earn their living? You're obviously here because you don't want to spend
any money for help. It would also explain your choice for mySQL vs MS SQL
server. You're the one that needs the help and you're burning bridges
faster than you're building them. It sounds like you thought it was an easy
task and quoted a small fee to fix it and now you're looking at a negative
ROI, not to be confused with RIO. One is a vacation and one isn't. (O:=

: Just try to understand that problem, if you don't, leave the problem to
few others who might actually do.

I think we all understand the problem but none of us are psychiatrists so
your problem may not get solved today.

I guess those anger-management classes haven't taken affect yet. So, until
they do, perhaps you should consider replacing this part of your original
request:

"Any ideas very much appreciated. Thank you."

....with this...

"Any ideas different than what I'm thinking are not welcome. While I'm
asking for help, I already know what I need because I'm smarter than you are
you loser so just tell me how to do it the way I want to do it even if it
doesn't work, you moron. Don't lecture me about the crappy code I'm
displaying, just give me the help I need no matter how ridiculous my design
or the design I inherited might be because I'm a lazy SOB and I don't have
time to do it right the first time and also because I type with two fingers.
I'm being paid to make it work, not work well and not with reasonable
performance. If my customer wanted it done well, they would have hired Bob
or Ray. If you answer with anything I don't deem to be helpful, I'll limit
myself to using just one finger to respond, you low-life mother."

Who knows? It might just get you the help you need. If you hear sirens
approaching, you'll know I'm right.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */

Re: Upsizing issue

TNTL!!!!! Failed horribly. :]

Ray at work


"Roland Hall" wrote in message
news:uPaKCa3RFHA.3560@TK2MSFTNGP14.phx.gbl...
> "MIMF" wrote in message
> news:1114157202.797687.230970@o13g2000cwo.googlegroups.com.. .
> : There's recoding and recoding.
>
> There's a grammar checker too. Jes' sayin'... Better add me to the list.
>
> : Doing a simple search and replace is one
> : thing, but getting into the logic of the

Re: Upsizing issue

"Ray Costanzo [MVP]" wrote in
message news:OPzj%2323RFHA.580@TK2MSFTNGP15.phx.gbl...
: TNTL!!!!! Failed horribly. :]

It's an exercise in futility but just like that great line in Risky
Business, "sometimes you just gotta' say...WTF!"

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Upsizing issue

You got too much time on your hands, retard. I don't have time to waste
on your overlong boring rants. And stop deluding yourself you're funny.

I asked a question. If someone knows the answer, I'll be very thankful
for that. On the other hands, if he doesn't, but pretends he's helping
by giving an answer to a modified, different question that IGNORES a
crucial condition, I'm going to tell him he's doing NO ONE a favor.
Their suggestion was absolutely USELESS, and I'd love to see you
explain in what way exactly have those two HELPED. Stick to the point.
Thank you.

Re: Upsizing issue

I believe the answer to your original question was "no, you can't do it that
way, here's what you can do".

If you think that you can come up with an alternative that works then do it.
Then you can come back and call people idiots if it makes you feel better.
At this point I don't think anyone who had an "easier" solution would give
you the time of day.

"MIMF" wrote in message
news:1114385666.806036.121840@o13g2000cwo.googlegroups.com.. .
> You got too much time on your hands, retard. I don't have time to waste
> on your overlong boring rants. And stop deluding yourself you're funny.
>
> I asked a question. If someone knows the answer, I'll be very thankful
> for that. On the other hands, if he doesn't, but pretends he's helping
> by giving an answer to a modified, different question that IGNORES a
> crucial condition, I'm going to tell him he's doing NO ONE a favor.
> Their suggestion was absolutely USELESS, and I'd love to see you
> explain in what way exactly have those two HELPED. Stick to the point.
> Thank you.
>

Re: Upsizing issue

Correction, they've BOTH told me what someone SHOULDN'T have done, not
what I CAN do now. Their suggestion is basically to manually recode
half the application because of stupid Recordset problem. Thanks a lot,
that's REALLY helpful. With help like that...

Re: Upsizing issue

"MIMF" wrote in message
news:1114448011.544151.290990@o13g2000cwo.googlegroups.com.. .
> Correction, they've BOTH told me what someone SHOULDN'T have done, not
> what I CAN do now. Their suggestion is basically to manually recode
> half the application because of stupid Recordset problem. Thanks a lot,
> that's REALLY helpful. With help like that...
>

That is the "correct" solution to the problem.

Had you not offended everyone by calling them stupid you might have been
able to get some ideas for a "work-around" that didn't involve fixing every
SQL statement and every recordset field reference. Or you might have gotten
some pointers about how to more easily replace the offending code using
pattern matching and global search and replace tools. As it stands now you
probably could not BUY assistance from anyone who has read this thread.

--
Mark Schupp

Re: Upsizing issue

"Mark Schupp" wrote in message
news:u8OIFjbSFHA.3144@tk2msftngp13.phx.gbl...
> "MIMF" wrote in message
> news:1114448011.544151.290990@o13g2000cwo.googlegroups.com.. .
>> Correction, they've BOTH told me what someone SHOULDN'T have done, not
>> what I CAN do now. Their suggestion is basically to manually recode
>> half the application because of stupid Recordset problem. Thanks a lot,
>> that's REALLY helpful. With help like that...
>>
>
> That is the "correct" solution to the problem.
>
> Had you not offended everyone by calling them stupid you might have been
> able to get some ideas for a "work-around" that didn't involve fixing
> every
> SQL statement and every recordset field reference. Or you might have
> gotten
> some pointers about how to more easily replace the offending code using
> pattern matching and global search and replace tools. As it stands now you
> probably could not BUY assistance from anyone who has read this thread.
>
> --
> Mark Schupp
>
>
Agreed. I hesitate to suggest the following work-around lest MIMF berate me
for my stupidity, but here it goes. You could link the SQL Server tables in
an Access database and then connect to the Access database from ASP. I
sincerely hope MIMF apologizes for his treatment of Bob and Ray. It was
unwarranted.

Re: Upsizing issue

> That is the "correct" solution to the problem.

No, all they did is patronizingly point out something that I obviously
already know because of type of question I asked. I didn't ask whether
SELECT * is good or not. I already know it's bad because it's making it
very difficult to migrate the database to MS SQL Server. That was very
clear from my initial post.

If they don't think anyone or me in particular deserves an intelligent
advice, then they should refrain from offering a dumb half-assed
patronizing non-answer to a question I did not ask.

Re: Upsizing issue

Now that's an intelligent answer.

Bob and Ray don't get shit because that's exactly what they gave.

Re: Upsizing issue

"MIMF" wrote in message
news:1114561304.302471.187260@z14g2000cwz.googlegroups.com.. .
> Now that's an intelligent answer.

You're welcome.

> Bob and Ray don't get shit because that's exactly what they gave.
>
I gave you the benefit of the doubt and chalked up your attitude to
frustration. My mistake. It won't happen again.

Re: Upsizing issue

"Chris Hohmann" wrote in message
news:uNSRv3rSFHA.1896@TK2MSFTNGP14.phx.gbl...
> "Mark Schupp" wrote in message
> news:u8OIFjbSFHA.3144@tk2msftngp13.phx.gbl...
>> "MIMF" wrote in message
>> news:1114448011.544151.290990@o13g2000cwo.googlegroups.com.. .
>>> Correction, they've BOTH told me what someone SHOULDN'T have done, not
>>> what I CAN do now. Their suggestion is basically to manually recode
>>> half the application because of stupid Recordset problem. Thanks a lot,
>>> that's REALLY helpful. With help like that...
>>>
>>
>> That is the "correct" solution to the problem.
>>
>> Had you not offended everyone by calling them stupid you might have been
>> able to get some ideas for a "work-around" that didn't involve fixing
>> every
>> SQL statement and every recordset field reference. Or you might have
>> gotten
>> some pointers about how to more easily replace the offending code using
>> pattern matching and global search and replace tools. As it stands now
>> you
>> probably could not BUY assistance from anyone who has read this thread.
>>
>> --
>> Mark Schupp
>>
>>
> Agreed. I hesitate to suggest the following work-around lest MIMF berate
> me for my stupidity, but here it goes. You could link the SQL Server
> tables in an Access database and then connect to the Access database from
> ASP. I sincerely hope MIMF apologizes for his treatment of Bob and Ray. It
> was unwarranted.


That occurred to me as well but it would probably negate the advantage of
going to SQL Server in the first place.

His idea of creating a custom object wrapped around the standard recordset
object would probably be doable but the effort might be on the same order of
magnitude as modifying the original ASP pages. You would have to build a
custom "connection" object along with a "recordset" object.

--
--Mark Schupp

Re: Upsizing issue

> You would have to build a custom "connection" object
> along with a "recordset" object.

Not really. You can use aggregation or an object within object. When
your object is created, you internally create an original recordset
object and forward all method calls to it, except the methods that need
modification of behaviour.

I am creating a simple normal VBScript sub that returns a
Scription.Dictionary with fields that mimic behaviour of Jet
recordsets. Between each rs.Open and rs.MoveNext, the call RsToJet(rs,
rsj) is needed, and all references to rs(" will have to be modified to
rsj(". A bit of search+replace, but not even close to analyzing the
logic between each and every SQL query.