A Review of Remote Database Administer
Several years ago, CGI-BIN modules were used to administer remote databases. But recently, CGI-BIN is used rarely due to the fact that it runs slowly and is hard for maintenance.
These few years, Component Object Model (COM) was widely used and worked well. Unfortunately it is not easy to register COM components on virtual hosting.
Yes, on .NET or on J2EE platform, we can make very well n-Tier Web applications. But for a web site, we have other easy ways to administer remote databases, for example, using XMLHTTP and ADO/ADOX.
How RDBA works
RDBA's working flow is as follows:
- Client asks server to implement a query.
- Server receives the request and implements it, then returns the results to client.
- Client receives the results from server and shows them.
Two important aspects for RDBA are:
- The channel between client and server to send commands and receive results. It is XMLHTTP.
- The middle tier between the front-end of server and database to retrieve data. It is ADO/ADOX.
RDBA's working flow showed as Fig. 1
Using XMLHTTP
Just as its name implies, a XMLHTTP control can be used to send and receive XML information using HTTP requests.
In fact XMLHTTP does more than that. It can send commands to the server with XML, string, stream, or an unsigned array. Commands can also be the parameters of URL. It can send result to the client with XML, string, stream, or an unsigned array.
Using XMLHTTP on client site is quite simple, only 5 steps:
- Create XMLHTTP object.
- Open XMLHTTP to the server with specifying method, URL and authority. As the same with HTTP, open method can be "POST" or "GET".
- Send request information to the server.
- Wait until the result has been received from the server.
- Free XMLHTTP object.
XMLHTTP Methods
Open bstrMethod, bstrUrl, varAsync, bstrUser, bstrPassword
bstrMethod
: HTTP method used to open the connection, such as GET or POST.
bstrUrl
: Requested URL on the server. This must be an absolute URL, such as: http://Myserver/Mypath/Myfile.asp.
varAsync
: Boolean
. Indicator as to whether the call is synchronous. The default is True
(the call returns immediately). But usually set it to False
to wait for results from the server.
bstrUser
: Name of the user for authentication.
bstrPassword
: Password for authentication.
Send varBody
varBody
: The acceptable VARIANT
input types are BSTR
, SAFEARRAY
of UI1 (unsigned bytes), IDispatch
to an XML Document Object Model (DOM) object, and IStream
.
setRequestHeader bstrHeader, bstrValue
bstrHeader
: HTTP header name to set.
bstrValue
: Value of the header.
If you need to POST data you can add a header that tells the receiver you�re sending FROM data: xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
XMLHTTP Properties
onreadystatechange
: Specifies the event handler to be called when the readyState property changes.
responseBody
: Response entity body as an array of unsigned bytes.
responseStream
: Represents the response entity body as an IStream.
responseText
: Response entity body as a string.
responseXML
: Response entity body as an XML document.
Following is the snippet from my downloadable sample source:
Function GetResult(urlStr)
Dim xmlHttp
Dim retStr
Set xmlHttp = CreateObject("Msxml2.XMLHTTP")
On Error Resume Next
xmlHttp.Open "POST", urlStr, False
xmlHttp.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
xmlHttp.Send
If Err.Number = 0 Then
retStr = xmlHttp.responseText
Else
retStr = "Url not found"
End If
Set xmlHttp = nothing
GetResult = retStr
End Function
The parameter in GetResult()
function is an URL string, it is the request page URL on the server. You can add sub-parameters along with it, for example:
urlStr = "server.asp?cmd=" & cmd & "&db=" & db & "table=" & table
cmd
: command, such as query, modify, delete, etc.
db
: the database name on the server.
table
: the table name on the server.
If no error occurred, the only thing that client needs to do now is to sit there and wait until the response come bake from the server (retStr = xmlHttp.responseText
).
At last, GetResult()
returns the response to the caller to show the results.
Which type of data to use?
As told above, the acceptable data types transferring within XMLHTTP pipe are
BSTR
,
SAFEARRAY
of UI1 (unsigned bytes),
IDispatch
to an XML Document Object Model (DOM) object, and
IStream
. The most data type used is XML DOM and STRING.
Which data type you chose to use depends on your application goal.
For retrieving data from the remote server, it is better to use XML data on both server and client sites in order to treat with a great deal data and request the server to add/remove/modify/query records, as well as to sort/filter data on the client. This article does not focus on XML COM.
The sample in this article is for the webmasters who uses virtual hosting. So it needs two basic capabilities:
- Administer remote database: add/remove/modify/query remote databases/tables/fields.
- Administer data in remote database: add/remove/modify/query records.
So this sample focuses on sending/receiving STRING through XMLHTTP.
The advantages are obvious:
- Easy to program like classic ASP (is the same as common ASP on server site, a little addition on client site - add several commands about XMLHTTP),
- On line implement RDBA,
- Refresh page data without reload page.
Due to response via STRING, it is simple to use script command "Response.Write
" to return the results trough XMLHTTP pipe, nothing changed on the server. Once the server finishes to response, the client then uses "xmlHttp.responseText
" method to get the whole results, returns it to the caller for refreshing and display page data.
Some delay, however, will come into being on client site after push the button to send request if you use asynchronous model. Client will sit and wait for results coming back from the server. It can be avoided by using XML DOM object and synchronous model.
Using ADOX
ASP Web services generally uses ADO as a middle tier to retrieve data between the front-end and database. ADO objects implement add/remove/modify/query records by executing the queries. I won't discuss ADO here.
But ADO is not able to administer remote database, such as add/remove/modify/query remote databases/tables/fields. ADOX should be used instead.
ADOX is extended ADO, it provides more functions to deal with database. If you are authorized database administrator you can do anything inside the database, such as add/remove/modify/query the databases/tables/fields/index, add/remove/modify the group/user/password, and more...
This sample shows:
- Produce dynamic SELECT menu, in which exist the databases name according to a given path.
- Produce dynamic SELECT menu, in which exist the table name according to the selected database in the first menu.
- Dynamically list the fields according to the selected database and table.
Changing of the virtual path of database and the OnChange
handler of the SELECT menu called will result in subsequently all different results. Refresh page data without reload page!
Using Scripting.FileSystemObject
to get the database according to a given path. Using ADOX to get the tables name in a database and the fields name in a table. The codes look like:
Get the tables name in a database (parameter DBName
is the database name):
Sub GetTables_Server(DBName)
Dim i
Dim catDB
Set catDB = Server.CreateObject("ADOX.Catalog")
On Error Resume Next
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(DBName)
If Err.Number = 0 Then
Response.Write "<select name='selTable' onChange=ShowResult('fields')>"
With catDB
For i = 0 To .Tables.Count -1
If .Tables(i).Type = "TABLE" then
Response.Write "<option value=" & .Tables(i).Name & ">" &_
.Tables(i).Name &"</option>"
End If
Next
End With
Response.Write "</select>"
Else
Response.Write "Error: Can't open database - " & DBName
End If
Set catDB = Nothing
End Sub
Get the fields name in a table (parameter tableName is the table name):
Sub GetFields_Server(dbName, tableName)
Dim i, j
Dim catDB
Set catDB = Server.CreateObject("ADOX.Catalog")
On Error Resume Next
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(dbName)
If Err.Number = 0 Then
With catDB
For j = 0 To .Tables.Count -1
If (.Tables(j).Type = "TABLE") and (.Tables(j).Name = tableName) Then
Exit For
End If
Next
End With
With catDB.Tables(j)
For i = 0 To .Columns.Count -1
If .Columns(i).Properties("Autoincrement") = True Then
Response.Write "<input type='radio' name='field' value=" &_
.Columns(i).Name & " checked=true>" & _
.Columns(i).Name & ""
Else
Response.Write "<input type='radio' name='field' value=" &_
.Columns(i).Name & ">" & .Columns(i).Name & ""
End If
Next
End With
Else
Response.Write "Error: Can't open database - " & dbName
End If
Set catDB = Nothing
End Sub
NOTE: All the tables in this sample use an Autoincrement
field as the primary key. If the primary key in your tables is not Autoincrement
type or there is not a primary key in your tables, you should rewrite above codes. In my opinion, use a Autoincrement
field as primary key is a good habit.
Use the sample
A screenshot of GOIS RDBA is showed as Fig. 2.
System required:
To run this sample, the follows should be installed on your PC: Windows 98/Me/XP/2000 MSXML 4.0 MS Access 2000,IE 5.5 or above.
Install and execute:
- Download the sample ZIP file - supervisor.zip.
- Unzip the file under a path in your IIS, for example: C:\Inetpub\wwwroot\supervisor
- Input "localhost/supervisor/supervisor.asp" in the url address on your IE, then GO.
- By a little changing, such as change the path, authorize the user, you can upload this sample source to your virtual hosting to administer your own database.
There are several sample databases and two source files in the downloadable ZIP file. The sample databases will automatically unzip to a "database" path under the working path, for example: /supervisor/database
Two source files are:
- A service page codes runs on the server: server.asp
- A client page codes runs on client site: supervisor.asp
The functions of the buttons on the demo:
- Refresh: Refresh database path. Rewrite the path and push this button will result in subsequently all different results.
- List: List the selected field's contents.
- Max: Get the MAX value and display it in the ID editor. It is called automatically after select a table.
- Count: Count the selected field's number.
- Show: Get and show the content specified by ID and selected field.
- Modify: Modify the content specified by ID and selected field.
- Delete: Remove the record specified by ID.
- SQL: Execute SQL query which is input in the text editor(upper). The text editor below will show the result of execution.
- +: Expand/Contract the text editor.
Epilogue
This article describes how to remote administer database via XMLHTTP and ADOX. It is useful to webmaster who uses virtual hosting. I'll add other functions to it. If you have any problems or suggestions, feel free to write me:
lianbo@gois.ws, or leave your message in my site:
http://eng.gois.ws/
Enjoy programming!
Reference