I discovered how to call stored procedures with less than 5 lines of code. I
recently had to display a webpage that was based on a table that contained several
calculated totals. The reason I had this summary/reporting table is a lot of data
was calculated and allowed for almost anyway of displaying stat's about the data.
Here is a brief summary of what I did. I created a table in SQL 7 that held the
data.
Every 4 hours the table was refreshed with new data using a Stored
Procedure. Then I created another Stored procedure that was being called
from the ASP page. Without writing a book on what I did here is a 10,000 ft
level overview. Hope this demo shows another way of using Stored Procedures to
retrieve data a Data Source using ASP. Stored Procedures are no more than an SQL
statement retreving data. Why hold that Query inside an ASP page when you can use
the Database server to run your Query's
Step 1
Create a Reporting Table that will be populated with the totals. This is
the SQL Generated Script that will created the table. This table holds all the
calculated data that is based on Raw numbers that are inputted.
<p>CREATE TABLE [dbo].[test2] (
[NickName] [char] (50) NULL ,
[TotalPoints] [int] NULL ,
[Count_NickName] [char] (50) NULL ,
[AvgScore] [decimal](18, 1) NULL ,
[NumOfStrikes] [decimal](18, 0) NULL ,
[WinLossPercentage] [decimal](18, 2) NULL ,
[Wins] [int] NULL ,
[Loses] [int] NULL
) ON [PRIMARY]
GO
</p>
Step 1 1/2 Here is some data to load in the db.
| NickName | TotalPoints | Count_NickName | AvgScore | NumOfStrikes | Winning_Percentage | Wins | Loses | | NickName1 | 611 | 138 | 4.4 | 28 | 52.9 | 73 | 65 | | NickName2 | 445 | 94 | 4.7 | 28 | 57.45 | 54 | 40 | | NickName3 | 419 | 97 | 4.3 | 35 | 49.48 | 48 | 49 | | NickName4 | 383 | 83 | 4.6 | 24 | 51.81 | 43 | 40 | | NickName5 | 337 | 74 | 4.6 | 20 | 54.05 | 40 | 34 | | NickName6 | 329 | 79 | 4.2 | 14 | 55.7 | 44 | 35 | | NickName7 | 324 | 81 | 4 | 16 | 45.68 | 37 | 44 |
Step 2
Create Stored Procedure that will be called from an ASP page. Ok this is
just a normal select statement but I'm convinced that put code where it will run the
fastest. On the database server, you only have to call the stored Procedure and
return the data. Instead of parsing the query and then running the statement.
CREATE PROCEDURE [TestStoredProcedure] AS
SELECT test2.NickName, test2.TotalPoints, test2.Count_NickName, test2.AvgScore,
test2.NumOfStrikes, test2.WinLossPercentage, test2.Wins,test2.Loses
FROM test2
ORDER BY test2.NickName;
GO
Step 3
Create an ASP page. The normal stuff, connection string, recordset object
etc.. here is the code
<% @language="vbscript" %>
<%
dim conn
dim strconn
dim rs
dim strql
'Normal Connection String
strconn= "Driver={SQL
Server};SERVER=127.0.0.1;UID=LoginID;PWD=password;DATABASE=some_db"
set conn = server.createobject("adodb.connection")
conn.open strconn
'This is a local variable that holds the variable the execute the stored proc.
<font color="#FF0000">strsql = "Exec TestStoredProcedure"
set rs = server.createobject("adodb.recordset")
rs.open strsql, conn
%>
<html>
<head>
<title>Example</title>
</head>
<body>
<% rs.movefirst %>
<table BORDER="1" width="80%">
<tr></p>
<p><td colspan="8"><center><b><strong>This section writes out
the Names of the fields in the Recordse</strong>t</center></b></td></tr>
<tr>
<% For Each Field In RS.Fields %>
<th>
<%
response.write "<b>" & Field.name & "</b>"
%>
</th>
<% Next %>
</tr>
<tr></p>
<td colspan="8"><center>This section writes out
the DATA of the fields in the Recordset</strong></center></b></td></tr>
<% Do While Not RS.EOF %>
<tr>
<% For Each Field In RS.Fields %>
<td ALIGN="center">
<%
If IsNull(Field) Then
Response.Write ""
Else
Response.Write Field.Value
End If %>
</td>
<% Next
RS.MoveNext %>
<% Loop %>
</table>
</body>
</html>
<%
'Close all objects and set to nothing to keep resources free!
rs.close
conn.close
set rs = nothing
set conn = nothing
%></p>
|
|
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By aspfree  developerWorks - FREE Tools!
|
Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br />
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of WebSphere Extended Deployment Compute Grid, which lets you schedule, execute, and monitor batch jobs. Because online transaction processing and batch jobs execute simultaneously on the same server resources, you can avoid costly duplication of resources. Compute Grid supports job types of Java transactional batch, compute-intensive and a new type called "native execution", which enables non-Java workloads to run on distributed end points.
FREE! Go There Now!
| | | |
Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
FREE! Go There Now!
| | | |
Analysts, architects, and developers who have existing COBOL or PL/I skills and want to extend those skills to deploy new workloads on the mainframe can use the IBM Enterprise Modernization Sandbox for System z to find hands-on walkthroughs of common real world scenarios. The scenarios provide examples of how to rapidly design, create, assemble, test, and deploy high-quality Web, Web services, portal, and SOA applications for IBM CICS, IBM IMS, and IBM WebSphere Application Server.
FREE! Go There Now!
| | | |
Learn the basics of the IBM Customer Information Control System (CICS). With a hands-on exercise, learn how to get your first CICS application up and running on your desktop using TXSeries V6.1 for Windows. The tutorial shows you how to download and install a free trial version of TXSeries V6.1.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components.
FREE! Go There Now!
| | | |
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
FREE! Go There Now!
| | | |
Explore how Rational and WebSphere software enable enterprise documentation in SOA environments. Specifically, a new integration between IBM WebSphere® Business Modeler and IBM Rational® Method Composer software can help technical writers more easily keep enterprise operations manuals in sync with changes that are made to business processes, resulting in more accurate and timely documentation that benefits the entire enterprise.
FREE! Go There Now!
| | | |
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
|
|
