One of the key factors in building software applications is development time. One of the solutions to reducing development time is to use user friendly software development tools which help the developer complete the job quickly. In any software application there may be many database functions; these functions will be tested several times when they are used in many places in the application. Early testing helps to lay cleaner code and saves time in further stages of the application build. Traditionally, testing the function meant launching SQL Plus then typing the SQL commands. This tool enables testing database functions with mouse clicks; it is very convenient and should save application development/testing time. The tool supports Oracle and SQL Server databases; it can also extend to other databases such as DB2 and Informix with minor modifications.
Presently it works with ORACLE and SQL Server; sample registry entries were shown for Oracle.
Create a DSN for the database using ODBC.
For SQL Server, DATABASE key needs to be changed to "SQLSERVER". Change the value for SCHEMOWNER.
Read Functions from a Database
Read all functions from the database owned by a specified schema.
//////////////////////////////// Sample code ///////////////////////////////////
Dim rsQuery
Set rsQuery = server.CreateObject("ADODB.recordset")
rsQuery.open strQry,ConnStr 'cnn
If CheckForErrors( rsQuery.ActiveConnection, strQry ) = True then
Response.end
End If
%>
<table align="center" cellSpacing="0" cellPadding="1" width="50%"
border="1" bgcolor="white" bordercolor=DarkTurquoise>
<tr bgcolor=AntiqueWhite>
<td ALIGN="CENTER" COLSPAN="3">
<FONT FACE=verdana SIZE=3 color=Red>
<b><%=DATABASE%> Functions Details</b>
</FONT>
</td>
</tr>
<tr>
<%
'Display Table Header
displayheader = True
Do while (displayheader)
%>
<TD ALIGN="CENTER" NOWRAP>
<B><FONT FACE=verdana SIZE=2 color=SaddleBrown>
SchemaOwner
</FONT></B>
</TD>
<%
for each x in rsQuery.Fields
if Ucase(x.name) = nameTag or Ucase(x.name)= statusTag then
%>
<TD ALIGN="CENTER" NOWRAP><B>
<FONT FACE=verdana SIZE=2 color=SaddleBrown>
<%
Response.Write Ucase(x.name)
%>
</FONT>
</B></TD>
<%
End if
next
displayheader=false
loop
%>
</tr>
<%
do until rsQuery.EOF
Response.Write("<TR>")
%>
<TD ALIGN="CENTER" NOWRAP>
<FONT FACE=verdana SIZE=2 color=MediumBlue>
<%=SchemaOwner%>
</FONT>
</TD>
<%
for each x in rsQuery.Fields
if UCase(x.name)= nameTag or Ucase(x.name) = statusTag then
%>
<TD NOWRAP>
<FONT FACE=verdana SIZE=2 color=MediumBlue>
<%
if Ucase(x.name) = nameTag then
%> <a href="./DBFunction.asp?PROCNAME=<% Response.Write(x.value)%>">
<% Response.Write(x.value)%> </a> <%
else
Response.Write Ucase(x.value)
End if
%>
</FONT>
</TD>
<%
End if
next
Response.Write("</TR>")
rsQuery.MoveNext
loop
set rsQuery = nothing
//////////////////////////////// Sample code ///////////////////////////////////
Display Function Parameters
Reads all the input parameters of a selected function and displays the INPUT box. Enter the value and hit the submit button.
//////////////////////////////// Sample code ///////////////////////////////////
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConnStr
set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection = Conn
'Specify the name of the stored procedure you wish to call
cmd.CommandText = ProcName
cmd.CommandType = 4
'Query the server for what the parameters are
cmd.Parameters.Refresh
if CheckForErrors(cmd, Procname) = True then
Response.End
End if
%>
<FORM NAME=frm1 ID=frm1 ONSUBMIT="return Validate();"
ACTION="./FunctionExecute.asp">
<table align="center" cellSpacing="0" cellPadding="1" width="50%"
border="1" bgcolor="white" bordercolor=DarkTurquoise>
<tr bgcolor=AntiqueWhite>
<TD COLSPAN =2 ALIGN=CENTER><B><FONT FACE=verdana
SIZE=3 color=Red>
Function <%=ProcName %></FONT></B></TD>
</TR>
<TR>
<TD ALIGN=CENTER><B>
<FONT FACE=verdana SIZE=2 color=SaddleBrown>
PARAMETER NAME</FONT></B></TD>
<FONT FACE=verdana SIZE=2 color=SaddleBrown>
INPUT</FONT></B></TD>
</TR>
<% For Each param In cmd.Parameters %>
<TR>
<% if param.direction <>4 then %>
<TD ALIGN=right><FONT FACE=verdana SIZE=2 color=MediumBlue>
<%= param.name %></FONT></TD>
<TD><INPUT TYPE=TEXT WIDTH=30 NAME="<%= param.name %>"
ID = "<%= param.name %>"></TD>
<% end if %>
</TR>
<%
Next
%>
<TR><TD ALIGN=CENTER COLSPAN=2><INPUT TYPE=SUBMIT WIDTH=30
VALUE="<%= PROCNAME %>" NAME="PROCNAME" ></TD></TR>
</TABLE>
//////////////////////////////// Sample code ///////////////////////////////////
Execute the Functions with Input Values
Executes the function with the input values from the previous page.
Advantages
Installing Oracle client on individual desktop servers machines is not necessary.
Launching SQL Plus sessions not required.
Accessible from anywhere; only requirement is a browser.
Development/testing time will be reduced; more user friendly.
About Author
Khadarabad Ghouse Mohiuddin presently works as Systems Architect in one of the leading Telecommunications Company in Dallas TX where he leads a team in developing Operations Support Systems. He likes to implement new innovative ideas using the latest technologies. He has in depth knowledge of current and emerging technologies and leverages them to provide better solutions to the organization. He has more than 10 years of software development experience in variety of areas which include Image Processing Satellite Simulators, Embedded Systems, and Operations Support Systems. Ghouse can be reached at ghousem@yahoo.com.
When errors occur on a Web site, they should be handled in a way that helps the user to get back on track. Unfortunately, setting up customized error pages in IIS usually requires something many Web developers lack -- access to and familiarity with the Web server's administrative interface. With CustomError for IIS, developers can add error pages, coded by hand or created in their favorite editor, by simply uploading them to a designated directory. No administrator intervention is required.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
