Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Snitz Forums 2000 MOD-Group
 MOD Add-On Forum (W/Code)
 topic.asp - code mod - combine sql statements?
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

work mule
Senior Member

USA
1358 Posts

Posted - 15 December 2000 :  15:25:26  Show Profile
I posted the following in another topic, but it was off topic and may have been overlooked.

http://forum.snitz.com/forum/link.asp?TOPIC_ID=2820

[The external link is to a copy of my development version.]

- - - - - - - - - - - - - - - - - - - - -
<b>In SUMMARY</b>
With a slight modification like this, it reduces the need to pass all the title information in the URL. Since the database is being queried anyways, we can squeeze a reference to the title(s) and then we'll have the titles. It also combines multiple queries into one, thus reducing server overhead (small in the grand scheme) and it would probably eliminate the need for link.asp - at least in it's role of populating the titles in the redirect to the specific topic.
<img src=icon_smile.gif border=0 align=middle>

- - - - - - - - - - - - - - - - - - - - -

One thing I'm attempting to do is eliminate the titles for the forums and topics out of the query string. I found that on the topic.asp page it's not really necessary to pass that information since there are three sql calls against the database. One for the topic, one for the forum, and one for the category. Since the database is already being hit, why not retrieve the titles from there?

Try it:
http://www.writermag.com/wrt/devforum/topic.asp?TOPIC_ID=5&FORUM_ID=4&CAT_ID=1

Thinking about what I saw, I thought about it some more and came up with the following idea. [cont in the next post] <img src=icon_smile.gif border=0 align=middle>

Will this work? It seems to work okay, but did I overlook something?

One thing I do need to add is verifying the datatype/value of the variable prior to the command object. No biggie....

Edited by - work mule on 15 December 2000 20:33:37

work mule
Senior Member

USA
1358 Posts

Posted - 15 December 2000 :  15:26:29  Show Profile
Okay...currently on topic.asp, here's lines 88 through 111

<pre id=code><font face=courier size=2 id=code>
'## Forum_SQL - Find out if the Category is Locked or Un-Locked and if it Exists
strSql = "SELECT " & strTablePrefix & "CATEGORY.CAT_STATUS "
strSql = strSql & " FROM " & strTablePrefix & "CATEGORY "
strSql = strSql & " WHERE " & strTablePrefix & "CATEGORY.CAT_ID = " & Request.QueryString("CAT_ID")

set rsCStatus = my_Conn.Execute (StrSql)

'## Forum_SQL - Find out if the Forum is Locked or Un-Locked and if it Exists
strSql = "SELECT " & strTablePrefix & "FORUM.F_STATUS "
strSql = strSql & " FROM " & strTablePrefix & "FORUM "
strSql = strSql & " WHERE " & strTablePrefix & "FORUM.FORUM_ID = " & Request.QueryString("FORUM_ID")

set rsFStatus = my_Conn.Execute (StrSql)

'## Forum_SQL - Find out if the Topic is Locked or Un-Locked and if it Exists
strSql = "SELECT " & strTablePrefix & "TOPICS.T_STATUS "
strSql = strSql & " FROM " & strTablePrefix & "TOPICS "
strSql = strSql & " WHERE " & strTablePrefix & "TOPICS.TOPIC_ID = " & Request.QueryString("TOPIC_ID")

set rsTStatus = my_Conn.Execute (StrSql)

if rsCStatus.EOF or rsCStatus.BOF or rsFStatus.EOF or rsFStatus.BOF or rsTStatus.EOF or rsTStatus.BOF then
Response.Redirect(strForumURL)
else
........
</font id=code></pre id=code>

The three recordsets are created and stay open until about line 220 where the sub Topic_nav is called. A couple additional lines are executed and then the recordsets are closed and set to nothing.

Line 109 is important...

Hopefully my modification is doing the same thing

[continued in next post]

Go to Top of Page

work mule
Senior Member

USA
1358 Posts

Posted - 15 December 2000 :  15:26:50  Show Profile
So here's my modification:

<pre id=code><font face=courier size=2 id=code>
' ## Build Command Object for Topic Status Information
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = my_Conn
objCmd.CommandText = "up_getStatus_Topic"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@TOPICID",adInteger,adParamInput,,intTOPIC_ID)

Set objRS_Status = objCmd.Execute

If objRS_Status.EOF or objRS_Status.BOF Then
Response.Redirect(strForumURL)
else
strdisplay_CAT_TITLE = objRS_Status.Fields("CAT_NAME").Value
strdisplay_FORUM_TITLE = objRS_Status.Fields("F_SUBJECT").Value
strdisplay_TOPIC_TITLE = objRS_Status.Fields("T_SUBJECT").Value
intCAT_STATUS = objRS_Status.Fields("CAT_STATUS").Value
intFORUM_STATUS = objRS_Status.Fields("F_STATUS").Value
intTOPIC_STATUS = objRS_Status.Fields("T_STATUS").Value
end if

objRS_Status.Close
Set objRS_Status = Nothing
Set objCmd = Nothing
</font id=code></pre id=code>

I consolidated the three recordsets into one, well in this case a command object because I'm using a stored procedure. I retrieve the recordset, set the six values I'll need later to variables and then dispose of the object. Anyplace that made reference to the old recordset was replaced with the variable.

The following is the stored procedure which has the SQL statement.

<pre id=code><font face=courier size=2 id=code>
SQL - Stored Proc - up_getStatus_Topic:

CREATE PROCEDURE up_getStatus_Topic
(@TOPICID as int)
AS

SELECT FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_STATUS,
FORUM_TOPICS.TOPIC_ID, FORUM_FORUM.F_STATUS,
FORUM_FORUM.F_SUBJECT,
FORUM_CATEGORY.CAT_STATUS,
FORUM_CATEGORY.CAT_NAME
FROM FORUM_TOPICS INNER JOIN
FORUM_FORUM ON
FORUM_TOPICS.CAT_ID = FORUM_FORUM.CAT_ID AND
FORUM_TOPICS.FORUM_ID = FORUM_FORUM.FORUM_ID INNER
JOIN
FORUM_CATEGORY ON
FORUM_TOPICS.CAT_ID = FORUM_CATEGORY.CAT_ID
WHERE (FORUM_TOPICS.TOPIC_ID = @TOPICID)
</font id=code></pre id=code>

Go to Top of Page

work mule
Senior Member

USA
1358 Posts

Posted - 15 December 2000 :  20:24:41  Show Profile
This will also work for the forum.asp page as well.

<pre id=code><font face=courier size=2 id=code>
'## Build Command Object for Forum Status Information
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = my_Conn
objCmd.CommandText = "up_getStatus_Forum"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@FORUMID",adInteger,adParamInput,,intFORUM_ID)

Set objRS_Status = objCmd.Execute

If objRS_Status.EOF or objRS_Status.BOF Then
Response.Redirect(strForumURL)
else
strdisplay_CAT_TITLE = objRS_Status.Fields("CAT_NAME").Value
strdisplay_FORUM_TITLE = objRS_Status.Fields("F_SUBJECT").Value
intCAT_STATUS = objRS_Status.Fields("CAT_STATUS").Value
intFORUM_STATUS = objRS_Status.Fields("F_STATUS").Value
end if

objRS_Status.Close
Set objRS_Status = Nothing
Set objCmd = Nothing
</font id=code></pre id=code>

The contents of the stored proc looks like this:

<pre id=code><font face=courier size=2 id=code>
CREATE PROCEDURE up_getStatus_Forum
(@FORUMID as int)
AS

SELECT FORUM_FORUM.F_STATUS, FORUM_FORUM.F_SUBJECT,
FORUM_CATEGORY.CAT_STATUS,
FORUM_CATEGORY.CAT_NAME,
FORUM_FORUM.FORUM_ID
FROM FORUM_FORUM INNER JOIN
FORUM_CATEGORY ON
FORUM_FORUM.CAT_ID = FORUM_CATEGORY.CAT_ID
WHERE (FORUM_FORUM.FORUM_ID = @FORUMID)
</font id=code></pre id=code>





Edited by - work mule on 15 December 2000 20:26:53
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 15 December 2000 :  20:41:21  Show Profile  Visit HuwR's Homepage
Will it work for all databases supported by Snitz ?

<font color=blue>'Resistance is futile'</font id=blue>
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 15 December 2000 :  20:46:34  Show Profile  Visit tilttek's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Will it work for all databases supported by Snitz ?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

No, usualy Store Proc are VERY Database Oriented... Work only with one. In this Case SQL Server. Access doen't have SP if I remember correctly.


Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 15 December 2000 :  20:46:51  Show Profile  Visit tilttek's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Will it work for all databases supported by Snitz ?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

No, usualy Store Proc are VERY Database Oriented... Work only with one. In this Case SQL Server. Access doen't have SP if I remember correctly.


Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page

work mule
Senior Member

USA
1358 Posts

Posted - 15 December 2000 :  20:49:05  Show Profile
The stored proc may not, but the sql inside the stored proc wasn't anything special. I would just build the SQL statement on the page and then set rsStatus = my_Conn.Execute (StrSql).

I'll go back to the original files and make the changes for this specifically, test them, and if everything is okay, I'll post them here so that if anyone's interested, they can check it out for themselves. <img src=icon_smile.gif border=0 align=middle>

But for now...it's time to do the 'ol commute home. <img src=icon_smile_dead.gif border=0 align=middle>

Go to Top of Page

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 15 December 2000 :  20:54:30  Show Profile  Visit HuwR's Homepage
Cool, I look forward to testing it when you've finished.

Have a safe journey

<font color=blue>'Resistance is futile'</font id=blue>
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 15 December 2000 :  20:56:45  Show Profile  Visit tilttek's Homepage
Why 2 replay, I click Only once... But the Idea of StoreProc isn't stupid... Maybe using Views... They are in all DB, they are pre-complie... And can add compatibility for others DB you don't support... No more problem with INER/OUTER JOIN, and thing like this. No more problem using SQL Function... Must have there equivalent in others DB.

Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20587 Posts

Posted - 15 December 2000 :  21:02:17  Show Profile  Visit HuwR's Homepage
Unfortunately though a view isn't dynamic, if you cahnage a field name the view won't work. If you add a new field, you would also have to redo the views aswel.

Using simple standard SQL is the best way to write cross platform portable code.

<font color=blue>'Resistance is futile'</font id=blue>
Go to Top of Page

tilttek
Junior Member

Canada
333 Posts

Posted - 15 December 2000 :  21:14:14  Show Profile  Visit tilttek's Homepage
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Unfortunately though a view isn't dynamic, if you cahnage a field name the view won't work. If you add a new field, you would also have to redo the views aswel.
Using simple standard SQL is the best way to write cross platform portable code.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Ya but you can change a View very easely by Script... Drop and Create... That's it!


Philippe Gamache
http://www.tilttek.com
http://www.lapageamelkor.com
Go to Top of Page

work mule
Senior Member

USA
1358 Posts

Posted - 15 December 2000 :  23:30:24  Show Profile
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Access doen't have SP if I remember correctly.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Access does use SP - they are referred to as parameterized queries. It won't support all the cool things you can do with SP in SQL Server, but it would work in this case.

Go to Top of Page

Doug G
Support Moderator

USA
6493 Posts

Posted - 15 December 2000 :  23:37:02  Show Profile
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>Using simple standard SQL is the best way to write cross platform portable code. <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I second this comment. There seems to be interest in supporting more, not fewer, database engines.

======
Doug G
======
Go to Top of Page

work mule
Senior Member

USA
1358 Posts

Posted - 16 December 2000 :  01:51:25  Show Profile
Okay...I'm finally done.

The zip file with the modified files can be found here - but you'll have to do a right click and save as to download!
http://www.geocities.com/redwolf_games/codemod_topicsql.zip

There are four files.
1. forum.asp
2. forum_alt.asp
3. topic.asp
4. modifications.txt

The modifications text file are just notes about what I did. The 2 forum pages are almost the same, the difference is that in the forum_alt, I eliminated the Topic and Forum Titles from the URL's. When using this file to link to topics, the url will look like this: topic.asp?TOPIC_ID=4&FORUM_ID=1&CAT_ID=1 - no Titles included. If you notice, the topics.asp page will display the titles because the sql statement has been modified to include the titles in the select statement.

Here are the notes that I had for the topics.asp modification:

1. Declared Variables

Dim strdisplay_CAT_TITLE, strdisplay_FORUM_TITLE, strdisplay_TOPIC_TITLE
Dim intCAT_STATUS, intFORUM_STATUS, intTOPIC_STATUS
Dim intCAT_ID, intFORUM_ID, intTOPIC_ID

2. Set QueryString Values to Variables & replaced all instances to refer to variable.

Replace Request.QueryString("CAT_ID") with intCAT_ID - 25 occurrences
Replace Request.QueryString("FORUM_ID") with intFORUM_ID - 31 occurrences
Replace Request.QueryString("TOPIC_ID") with intTOPIC_ID - 29 occurrences

3. Commented out old section of code & added the new SQL statement.

4. Set values of recordset to variable and replaced all instances to refer to variable

Replace rsCStatus("CAT_STATUS") with intCAT_STATUS - 10 occurrences
Replace rsFStatus("F_STATUS") with intFORUM_STATUS - 10 occurrences
Replace rsTStatus("T_STATUS") with intTOPIC_STATUS - 9 occurrences

5. Because of the Titles retrieved from the Database I can now do the following:

Replace Request.QueryString("FORUM_Title") with strdisplay_FORUM_TITLE - 21 occurrences
Replace Request.QueryString("TOPIC_Title") with strdisplay_TOPIC_TITLE - 16 occurrences

Add to this the fact that 3 recordset objects were combined into 1. The recordset was created, values retrieved and set to variables, and the recordset is immediately closed, whereas the current method, the recordsets are held open for most of the page. Actually looking at the topic.asp page for sr2 (& even sr3b4), I can't find any instances of the 3 recordsets being closed and set to nothing. <img src=icon_smile_shock.gif border=0 align=middle>

Also, by eliminating the need to pass Titles, this reduces the number of function calls by not having to use ChkString to add/format Titles inside the URL's.

Well, let me know what you think. <img src=icon_smile.gif border=0 align=middle>

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.29 seconds. Powered By: Snitz Forums 2000 Version 3.4.07