WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
This article describes how to administer a remote database via XMLHTTP and ADOX. It is useful to Webmasters who use virtual hosting.
A Review of Remote Database Administration
Several years ago, CGI-BIN modules were used to administer remote databases. But until recently, CGI-BIN was used rarely due to the fact that it runs slowly and is hard to maintain.
Yet, on the .NET or J2EE platform, we can make good n-Tier Web applications. But for a Web site, we have other easy ways to administer a remote database; for example, by using XMLHTTP and ADO/ADOX.
How RDBA works
RDBA's work 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 the server and database to retrieve data. It is ADO/ADOX.
RDBA's work flow is shown in Figure 1.
Figure 1—RDBA's work flow.
Just as its name implies, an XMLHTTP control can use HTTP requests to send and receive XML information.
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 a URL. It can send results to the client with XML, string, stream, or an unsigned array. For more details, please read the article linked at the end of this article.
Using XMLHTTP on the client site is quite simple; it takes only these five steps:
- Create an XMLHTTP object.
- Open XMLHTTP to the server with the specifying method, URL, and authority. As with HTTP, the 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.
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. Indicates whether the call is synchronous. The default is True (the call returns immediately). It is usually set to False to wait for results from the server.
- bstrUser: User name for authentication.
- bstrPassword: Password for authentication.
- 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".
- 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 a snippet of code from my downloadable sample source:
Function GetResult(urlStr) Dim xmlHttp Dim retStr Set xmlHttp = CreateObject("Msxml2.XMLHTTP") 'create object On Error Resume Next 'error handling xmlHttp.Open "POST", urlStr, False 'open connection 'using "POST" 'method, 'asynchronously xmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 'sending FORM data xmlHttp.Send 'send HTTP request 'to the server If Err.Number = 0 Then 'if no error 'occurred, retStr = xmlHttp.responseText 'wait to receive 'a response from 'the server Else retStr = "Url not found" 'error message End If Set xmlHttp = nothing 'free the object GetResult = retStr 'return the 'response to the 'caller End Function
The parameter in the GetResult() function is a 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
where the commands are as follows:
- cmd: command, such as query, modify, delete, and so forth.
- db: the database name on the server.
- table: the table name on the server.
If no error occurred, the only thing the client needs to do now is to sit there and wait until the response comes back from the server (retStr = xmlHttp.responseText).
Finally, GetResult() returns the response to the caller to show the results.
Which Type of Data Should You Use?
As stated earlier, 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 are XML DOM and STRING.
Which data type you choose to use depends on your application's goal.
To retrieve data from the remote server, it is better to use XML data on both the server and client sites to treat a great deal of 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 use 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. (It is the same as common ASP on the server site, with a little addition on client site—add several commands about XMLHTTP.)
- Online implementation of RDBA.
- Refresh page data without reloading the page.
Due to a response via STRING, it is simple to use the script command "Response.Write" to return the results trough the XMLHTTP pipe; nothing changed on the server. Once the server finishes its response, the client then uses the "xmlHttp.responseText" method to get the whole results; then returns it to the caller for refreshing and displaying page data.
Some delay, however, will be created on the client site after clicking the button to send the request if you use an asynchronous model. The client will sit and wait for results coming back from the server. This can be avoided by using an XML DOM object and a synchronous model.
ASP Web services generally use 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 a 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 databases. If you are an 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 requests:
- Produce a dynamic SELECT menu, in which exists the database's name according to a given path.
- Produce a dynamic SELECT menu, in which exists the table's 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 the database and the OnChange handle of the both SELECT menu called subsequently will result in all different results. Refresh the page data without reloading the page!
Using Scripting.FileSystemObject to Get the Database According to a Given Path
Use ADOX to get the table's name in a database and the field's name in a table. The code looks like the following. Get the table's name in a database (parameter DBName is the database name):
Sub GetTables_Server(DBName) Dim i Dim catDB 'ADODB.Catalog object Set catDB = Server.CreateObject("ADOX.Catalog") 'Create ADODB.Catalog object On Error Resume Next 'Error handle catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath(DBName) 'Open connection 'Don't forget to add your user name 'and password if needed. If Err.Number = 0 Then 'If no error occurred ' Produce dynamic SELECT menu Response.Write "<select name='selTable' _ onChange=ShowResult('fields')>" With catDB For i = 0 To .Tables.Count -1 If .Tables(i).Type = "TABLE" then ' If it is a table ' Add the table name ' into the menu Response.Write "<option value=" & .Tables(i).Name & ">" _ & .Tables(i).Name &"</option>" End If Next End With Response.Write "</select<" ' End of menu product Else ' Error message Response.Write "Error: Can't open database - " & DBName End If Set catDB = Nothing ' Free ADODB.catalog ' object End Sub
Get the field's name in a table (parameter tableName is the table name):
Sub GetFields_Server(dbName, tableName) Dim i, j Dim catDB 'ADODB.Catalog object Set catDB = Server.CreateObject("ADOX.Catalog") 'Create ADODB.catalog object On Error Resume Next 'Error handle catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath(dbName) 'Open connection 'Don't forget to add your user name 'and password if needed. If Err.Number = 0 Then ' If no error occurred ' Find the table matched the table name With catDB For j = 0 To .Tables.Count -1 If (.Tables(j).Type = "TABLE") and _ (.Tables(j).Name = tableName) Then Exit For Next End With ' List the field's name With catDB.Tables(j) For i = 0 To .Columns.Count -1 If .Columns(i).Properties("Autoincrement") = True Then 'If it is a primary key, check it. Response.Write "<input type='radio' name='field' _ value=" & .Columns(i).Name & _ " checked=true>" & .Columns(i).Name & _ "<br>" 'Display the field name Else Response.Write "<input type='radio' name='field' value=" _ & .Columns(i).Name & ">" & .Columns(i).Name & _ "<br>" 'Display the field name End If Next End With Else ' Error message Response.Write "Error: Can't open database - " & dbName End If Set catDB = Nothing ' Free the ADODB.catalog object 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 the Autoincrement type or there is not a primary key in your tables, you should rewrite the preceding code. In my opinion, using an Autoincrement field as primary key is a good habit.
Use the sample. A screen shot of GOIS RDBA is shown in Figure 2.
Figure 2—The GOIS RDBA.
To run this sample, the following should be installed on your PC: Windows 98/Me/XP/2000#, MSXML 4.0#, MS Access 2000#, and IE 5.5 or higher.
Install and execute:
- Download the sample ZIP file—RemoteDatabase.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 changing a little, such as changing the path or authorizing 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 with code that runs on the server: server.asp
- A client page with code that runs on the client site: supervisor.asp
The functions of the buttons in the demo are:
- Refresh: Refreshes database path. Rewriting the path and clicking this button will result in subsequently all different results.
- List: Lists the selected field's contents.
- Max: Gets the MAX value and displays it in the ID editor. It is called automatically after selecting a table.
- Count: Counts the selected field's number.
- Show: Gets and shows the content specified by the ID and selected field.
- Modify: Modifies the content specified by the ID and selected field.
- Delete: Removes the record specified by the ID.
- SQL: Executes a SQL query that is input in the text editor (upper). The text editor below will show the result of execution.
- +/-: Expand/Contract the text editor.
This article described how to remote administer database via XMLHTTP and ADOX. It is useful to any Webmaster who uses virtual hosting. I'll add other functions to it.