| Author |
 Topic  |
|
|
work mule
Senior Member
USA
1358 Posts |
 Posted - 15 December 2000 : 15:25:26
|
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
|
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]
|
 |
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 15 December 2000 : 15:26:50
|
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>
|
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 15 December 2000 : 20:24:41
|
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 |
|
|
|
HuwR
Forum Admin
United Kingdom
20587 Posts |
Posted - 15 December 2000 : 20:41:21
|
Will it work for all databases supported by Snitz ?
<font color=blue>'Resistance is futile'</font id=blue> |
|
|
|
tilttek
Junior Member
Canada
333 Posts |
Posted - 15 December 2000 : 20:46:34
|
<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 |
|
|
|
tilttek
Junior Member
Canada
333 Posts |
Posted - 15 December 2000 : 20:46:51
|
<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 |
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 15 December 2000 : 20:49:05
|
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>
|
|
|
|
HuwR
Forum Admin
United Kingdom
20587 Posts |
Posted - 15 December 2000 : 20:54:30
|
Cool, I look forward to testing it when you've finished.
Have a safe journey
<font color=blue>'Resistance is futile'</font id=blue> |
|
|
|
tilttek
Junior Member
Canada
333 Posts |
Posted - 15 December 2000 : 20:56:45
|
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 |
|
|
|
HuwR
Forum Admin
United Kingdom
20587 Posts |
Posted - 15 December 2000 : 21:02:17
|
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> |
|
|
|
tilttek
Junior Member
Canada
333 Posts |
Posted - 15 December 2000 : 21:14:14
|
<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 |
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 15 December 2000 : 23:30:24
|
<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.
|
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 15 December 2000 : 23:37:02
|
<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
====== |
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 16 December 2000 : 01:51:25
|
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>
|
|
|
| |
Topic |
|
Topic Locked
