Writing ISQL In ASP
January 10th, 2008 | by programming |When first testing a database, nothing is more useful than iSql, and to be able to display and modify your data quickly and simply. This software is given with almost all sql databases and is used to execute queries.
In this article we will see how to build our own iSql with ASP and ADO 2.0. Using ASP we can build a database administration page that will allow you to modify your database from your browser. It’s both easy to implement and very useful, and it’s a good way to see how to work on a database with ASP.
First you will have to enter your DSN and your query. Then you can execute a Data Definition Language type of query (like Create Table, Alter table), a transaction (Insert into, Update or Delete) or a selection query (select * from mytable) and see the result.
Because it’s supposed to be used on the Internet, I choose to open a connection at the beginning of the page and to close it at the end of the page (or before when an error occurs).
We will see with more details:
1. Two external functions
2. How to deal with a syntax error
3. How to check the result of the query
4. How to display �quickly� the recordset
Two External Functions
When you have portions of code appearing more than once in your program, it’s a good habit to put these portions of code into functions or subs and in a specific file. After, you just have to add an include file at the beginning of your program in order to be able to call these functions.
The content of this file:
<%
Sub dbClose()
rsIsql.Close
set rsIsql=Nothing
cnIsql.Close
set cnIsql=Nothing
End Sub
Sub doRedirect(sUrl)
Response.expires=0
Response.Buffer=true
Response.redirect sUrl
Response.Flush
End Sub
%>
The first sub will be used for closing the recordset and the connection to the database. And the second sub is used to redirect a page.
How To Deal with a Syntax Error
The purpose is to detect the error (with Err object) and then to redirect to a specific page to display all the information on the error and to log the error in a log file. If sSql is empty, it means that it�s the first time you have opened the page and the program does nothing.
Here is the portion of code :
On Error Resume Next
if sSql <>”" then
Session(”SQLDebug”) = sSql
Set cnIsql = CreateObject(”ADODB.Connection”)
cnIsql.Open(sDsn)
cnIsql.BeginTrans
Set rsIsql = cnIsql.Execute(sSql)
‘Check Error
if Err.Number<>0 then
cnIsql.RollbackTrans
dbClose()
doRedirect(”feedback_error.asp?ErrNo=” & Err.Number &_
“&Source=” & Server.urlencode(Err.Source) &_
“&Msg=” & Server.urlencode(Err.Description))
End if
When an error occurs we roll back the transaction. (Normally you only have to do that if you have more than one query, but it’s a good habit to add it.) When an error occurs we also close the connection to the database. Then we can redirect to the page that is specifically design for the process of logging an error. The advantage of having a specific page for this purpose is that you can share it with all your programs. Here the error is logged in a file, but we can also send an e-mail message to the database administrator
The page logs the error in a file in the log folder and then displays the error message on the screen. Here is the code for saving the error message in a file:
‘Read Parameters
sErrNumber = Request.QueryString(”ErrNo”)
sSource = Request.QueryString(”Source”)
sMessage = Request.QueryString(”Msg”)
sSql = Session(”SQLDebug”)
‘The Log File
Set oFile = CreateObject(”Scripting.FileSystemObject”)
sLogPath = Server.mapPath(”log/sqlerrors.log”)
Set oLogFile = oFile.OpenTextFile(sLogPath, 8 ,true)
‘What time is it, please?
sDate=Date()
sTime=time()
‘Feed the log file
oLogFile.WriteLine(sDate & ” ” & sTime & ” Error No :” & sErrNumber )
oLogFile.WriteLine(”Source : “)
oLogFile.WriteLine(sSource)
oLogFile.WriteLine(”Message : “)
oLogFile.WriteLine(sMessage)
oLogFile.WriteLine(”SQL query : “)
oLogFile.WriteLine(sSql)
oLogFile.WriteLine(”————————————-” &_
“——————————–”)
oLogFile.Close %>
How To Check the Result of the Query
You can have three different types of queries. Only in the selection query will we have to display a result. In this case, we will also check if there are records.
With ADO, when executing an query without a result, like an insert, the recordset is closed and the test ‘is Nothing’ generates an error. So we will use this test to know if there is a recordset or not. We also check if there are records (because your table may be empty). In the first part of the program, all the Boolean are initialized as false.
if Not (rsIsql is Nothing) then
if (rsIsql.EOF) then fEmpty=true
if Err.Number=0 then fShow=true
End if
How To Display ‘Quickly’ the Recordset
Now we know which type of result we have, so we can prepare the string to display. If there is no result, it means that the transaction is done. If there is a result, we display first the names of the Fields as the header of our displaying table. And then if there are records, we use the getstring function to generate the result table.
‘Display the recordset if fShow then sResult=“<table border=0 cellspacing=1 cellpadding=1><tr>” & sCR ‘Header with name of fields For cFields=0 to rsIsql.Fields.count-1 sResult = sResult & “<th bgcolor=silver> “ & Trim(rsIsql.Fields(cFields).Name) & “</th>” & sCR Next sResult = sResult & “</tr>” & sCR if Not fEmpty then sResult = sResult & “<tr bgcolor=’#eeeecc’><td>” & sCR sResult = sResult & rsIsql.getstring(,,“</td><td>”,“</td></tr>” & sCR & “<tr bgcolor=’#eeeecc’><td>”,” “) ‘Cut the last row delimiter sResult = Mid(sResult,1,Len(sResult)-Len(“<tr bgcolor=’#eeeecc’><td>”)) end if sResult = sResult & “</table>” else sResult = “Your Transaction is done.” end if ‘We choose to close the connection at the end of the script cnIsql.CommitTrans dbClose() End if %>
At the end of the page you see the commit of the transaction. We close the database and it’s done!!
You may have noticed that we have to cut the last row delimiter. Run the program without it and if you use a background color for each row, you will see a little empty line at the end of your table. You may also have noticed that I used chr(13) to add a carriage return at each line. I use that only to have an HTML response page that is more readable (when using ‘View source’ on the browser). It’s sometimes useful for debugging.
Now we just have the HTML code of our iSql Page.
<HTML><HEAD> <META HTTP-EQUIV=”Cache-Control” CONTENT=”no cache”> <META HTTP-EQUIV=”Pragma” CONTENT=”no cache”> <META HTTP-EQUIV=”EXPIRES” CONTENT=”0″> <TITLE>My iSql</TITLE> <STYLE> <!– BODY { font size:10pt; font-family:Verdana,ARIAL,Helvetica; } TH { font size:8pt; font-family:Verdana, ARIAL,Helvetica; } TD { font size:10pt; font-family:Verdana, ARIAL,Helvetica; } INPUT { font size:10pt; font-family:Verdana, ARIAL,Helvetica; } –> </STYLE> </HEAD> <BODY TEXT=”#000000″ BGCOLOR=”#99cdff” LINK=”#000000″ VLINK=”#A9A9A9″> <CENTER> <TABLE ALIGN=CENTER VALIGN=CENTER WIDTH=640> <TR><TD ALIGN=CENTER> <!– Title –> <TABLE WIDTH=100%><TR> <TD ALIGN=LEFT>iSql</TR><TR> <th bgcolor=black><p style=”font-size:2pt;”> </p></th> </TR></TABLE> </TD></TR> <!�- Form Query Area –> <TR><TD ALIGN=CENTER> <TABLE WIDTH=100%> <TR><TD> <FORM ACTION=”isql.asp” METHOD=GET> Your ODBC DSN : <INPUT TYPE=”text” NAME=”alias” VALUE=”<%=sDsn%>” SIZE=30><br> Your Query : <INPUT TYPE=”text” NAME=”sql” VALUE=”<%=sSql%>” SIZE=80> <INPUT TYPE=”submit” VALUE=”DO”> </FORM> </TD></TR> </TABLE></TD></TR> <!– Result Area –> <TR><TD ALIGN=CENTER> <%=sResult%> </TD></TR> </TABLE> </CENTER> </BODY></HTML>
We use the META tags to be sure that our page will be refreshed after every submit. We use the TABLE tag to center the text. We just have to add sResult in the right place.)
Conclusion
This simple example shows a different part of an ASP script when your are working with SQL databases. I also use it for ACCESS, because I’m more used to the SQL syntax than the specific functions of ACCESS. By choosing to only use SQL syntax, you will have less problems moving from one database engine to another. If you put it on line, be aware that everybody can access it and then modify your database structure. So it may be important to add a secured access to your page (with a little include file that will check in the session if the user has entered his login and his password) using a table of users or the security functions of NT (NTFS). If you use an SQL table, your table of users may look like the following:
create table USERS
(
USER_ID int not null,
USER_LOGIN char(10) null ,
USER_PWD char(10) null ,
USER_FULLNAME char(32) null ,
USER_SURNAME char(32) null ,
USER_LEVEL int null ,
constraint PK_USERS primary key (USER_ID)
)
And your checking query is:
“select user_id, user_fullname, user_surname, user_level from ” &_
“users where (user_login = ‘” & sUserLogin &_
“‘ and user_pwd = ‘” & sUserPwd & “‘)”
I use the level field to be able to add different types of rights like only viewing data, updating data, and deleting data. I give the access to iSql only for users who are on level 3 because it�s an administration tool.
About the Author
Christophe Berg who lives in Paris, is a freelance project manager on the Internet and Intranet . He builds flexible and robust database-driven Web sites with Active Server Pages and SQL databases. His company, Toodoo (http://www.toodoo.net), plans to create on-line adventure games.