Server User Guide
SQLData Server User Guide
The SQLData Server uses ODBC to access your databases. So, the first thing is to define your ODBC data sources in the Control Panel. Click the ODBC icon in the control panel to bring up the ODBC Data Source Administrator. Then, select the System DSN tabs in the dialog box and add your data sources as the System Data Sources. The SQLData server is running as a Windows NT system service, it can only access data sources defined as System DSN.
While you are in the ODBC Administrator, you should take the opportunity to clean up unused data sources and broken data sources (Data Sources that don't point to a valid database system). This will help SQLData server not to make any unnecessary database connections when it tries to search all your databases and tables in the system.
Starting up the SQLData Server
The SQLData server is installed as a Windows NT system service on a Windows NT system. To start the service:
In a Windows 95/98 system, the server should be started from the Start menu:
After starting the server, you are ready to access all databases defined on the server. The server shipped with a set of HTML pages to give you a jump-start. You should be able to perform all database operations if you follow the following steps:
If you have reached to this point without problem, your server is setup and configured properly.
If, at any of the steps, you encountered a problem, check out the Trouble Shooting Guide. It contains solutions to many common problems. You can also reach our technical support by mailing to
After setting up the server, you may want to configure the server to fit into your own needs. This section shows you how to change the server behavior and make it a powerful and secure database server.
The server configuration file, sqldata.ini, contains important settings about the server. It can be modified using any text editor.
If user account information is stored in a database file, you can use SQLData server to add, delete or update user accounts just like regular database tables.
The following procedures apply only when the sqldata.usr file is used.
When first installed, the server has a default administrator's account created in the user information file, sqldata.usr:
User name: admin
Password: admin
An administrator's privilege is required for adding or deleting user account. User account administration is done through HTML pages shipped with the server. The server must be started for the account administration.
User information is stored in the sqldata.usr as a text file. Although the file can be viewed or verified, it should never be modified using a text editor because it is digitally signed and encrypted. The server will not start if the file is tampered with.
Adding a user account
Follow the following steps to create a new user:
To remove a user account, follow the these steps:
Any user can change his/her password using the Change Password form.
As you can see from the Getting Start section, you can do almost all the database operations with your databases after the server is installed. However, if you need to publish database content to the Internet, you will need to create your own HTML files, and use the capability provided by the server to interface with your database tables.
There are several simple ways to get your data out to the web:
Any database operation requires a database connection so the server knows where to find the database tables. Establishing a database connection is an easy task using the SQLData server. There are different ways for specifying which database to connect:
The SQLData server offers two primary ways for publishing database information online. One way is to use the server template token commands, which requires minimum editing but offers powerful features. Another is to use the server scripts, which is a C like language with build-in database functions that allows you to manipulate database down to the lowest level.
The SQLData Desktop Server shipped with many samples using both the template token and server scripts.
The SQLData server provides many template token commands, and they are documented in the
Template Token User Guide. For most of the applications, you are likely to use only a few of them. The simplest HTML file that can be used for displaying records from an ODBC data source looks like this:<html>
<pre> [ResultSet(-1, -1, "")] </pre>
</html>
We used one template token, ResultSet. It simply formats the result from a query and inserts the output in the HTML file. Suppose you have saved the file in the HTDOC directory as foo.html, If you are wondering what this HTML file can do for you, type in the following request in your web browser:
http://localhost:8732/foo.html?DataSource=TestDbase&SQLStatement=select * from customer
In real situation, you would not ask users to type in the SQL statements in the web browser, You should set up a hyperlink to the foo.html page with necessary connection information and a query statement.
To display a single record and allows user to modify it, you can use a template token: Fields. Suppose you wish to create an HTML file, foo2.html, which can be used for updating database records. The simplest form would look like this:
<html>
<form method="POST" action="/update">
<pre> [Fields(0, -1, 1)]</pre>
<p><input type="submit" value="Update" ><input type="Reset" value="Reset"></p>
</form>
</html>
If you are familiar with HTML, most of the elements in the page are HTML form components except Fields. The Fields template token is smart enough to create a form that contains all the field names and values in the record. You can test the HTML file using the following request from a web browser:
http://localhost:8732/foo.html?DataSource=TestDbase&SQLStatement=select * from customer&RowId=2
We specified row identification number here because we want to edit the third record (Row Id is 0 based).
It is very simple, and it could not be simpler than this. The SQLData template tokens do not offer many formatting capabilities, but they offer powerful database functionality. These tokens can be placed anywhere on your HTML page and you could use your markup tools to make attractive web pages.
If you are a developer and like to have more control over how the database should be accessed, and be able to format the record in your own way. You can use our server scripts. The server script is a C-like language with a lot of built-in database functions. The scripts can be placed anywhere in your HTML file, and they are interpreted and executed when the file is requested.
We have made the script a little simpler compared with the regular C language:
Here is a simple script file, hello.html:
[Code Print("<html> Hello world. </html>")]
The script outputs an HTML file with only Hello world on it. The Print function is used for sending data to the client browser. Perhaps a more interesting example would be implementing the RecordSet template token using scripts:
[Code
szDataSource = GetParam("DataSource");
szSQL=GetParam("SQLStatement");
szTemplate=GetParam("Template");
RowId=GetParam("RowId");
Connect(szDataSource, "", "");
OpenSQL(szSQL);
rows=Move(RowId);
TotalRow=0;
Print("<em>");
Print(Header());
Print("<em>");
Print("<br>");
while(rows!=0)
{
for(i=0; i<rows; i++)
{
Print("<A HREF=\"");
Print(szTemplate);
Print("?DataSource=");
Print(szDataSource);
Print("&SQLStatement=");
Print(szSQL);
Print("&RowID=");
Print(TotalRow);
Print("\">");
Print(Record(i));
Print("</A>\n<br>");
TotalRow++;
}
rows=Fetch();
}
CloseSQL();
Disconnect();
]
The script performs the following tasks:
For further technical details about the functions used in the script, please refer to the Server Scripting Guide. We have many such samples available so that you can cut/copy to your HTML pages.
Building Cascade Logic
A well designed database system links database entities through keys and foreign keys. These keys allow retrieval of related information through queries. The SQLData can build such logic relations (Cascade Logic) among all your database tables automatically, and use such logic to create hyperlinks so that users can perform so called drill-down queries or master-detail queries.
For instance, in a typical electronic commerce system that consists of CUSTOMER, ORDER and PRODUCT tables. You would likely want to know what has been ordered when you see a customer record, and what product the customer bought. The cascade logic allows you to browse such related records very easily.
In order to build the cascade logic successfully, your database should satisfy the following conditions:
The logic that server built may not reflect the true relations if the above conditions are not met. You may edit the cascade logic file using a text editor if needed.
To use the cascade logic in your application, follow these steps:
The SQLData Desktop Server has built-in cascade capability. The cascade link is created inside the record_editor.html file. For those who use the Desktop Server, only step 1 is needed.
Creating Custom Report
There are cases that some database queries are used very frequently in your business environment. These queries can be created as Custom Reports so that they can be executed with a simple button click.
The SQLData server displays available custom reports in the left panel of user browser under the Database service index. It executes the SQL Statement associated with the report and displays result as HTML pages.
A customer report can be added using the MyReport link on the record browser page. The following items are needed for creating a report:
Your report is stored in a SQLData system table named TableLogic. You can manipulate the table directly by clicking on the table name in the left panel. However, we have a Report Editor to simplify the tasks.
Adding a report
You can use the report editor for adding new reports.
Modifying a report
Removing a report
Using Triggers
A trigger is a set of SQL commands that are executed when given conditions are met. The SQLData server supports higher level triggers than that of DBMS, which can be used for any ODBC data sources. Triggers can be placed only on database tables and are fired when INSERT, UPDATE and DELETE operations are requested. A trigger can be fired either before (Pre-Trigger) or after (Post-Trigger) a database operation is carried out. The requested operation will be aborted if the Pre-Trigger failed.
Triggers, similar to reports, are also stored the TableLogic table, and can be edited using the Record Editor tool in the server.
Adding a trigger
The procedure for adding a trigger is no different from adding a record in the TableLogic table:
When INSERT, UPDATE or DELETE operation is requested, the server normally receives field values associated with a record. You can use $V(FieldName) to reference a field value in the SQL statement as demonstrated below.
A Simple Trigger Example
Suppose we need a pre-trigger to remove orders of a customer if the customer is deleted from the database. The definition of the trigger would be:
LogicType: Customer
LogicName: PreDelete
SQLStatement: delete from orders where CustomerId = '$V(CustomerId)'
Where $V(CustomerId) will be replaced by the value of CustomerId field in the HTML form posted to the server.
Using Procedures
You can create HTML files to display database contents dynamically using SQLData template tokens. The server needs more information in order to complete the task, including where to get, how to operate and what to show. You can specify such information as parameters in an HTTP request similar to the following:
http://www.myserver.com/record_browser.html?DataSource=MyData&SQLStatement=select * from customer
This defines not only the HTML page, but also the data source and the SQL statement. There are problems with the URL. First, it is not convenient, user has to type in a long string in order to get the result. Secondly, users may not know anything about SQL and thus could not use it. Procedures are designed to solve these problems.
Different from reports, which is primarily for queries, procedures can contain any SQL statements, and thus can be used for manipulating database records.
Adding a procedure
Procedures are stored in the TableLogic table of the SQLDataSystem data source. Here is how to add a procedure into the system:
The following shows a procedure that retrieves orders of large quantities.
LogicType: PROCEDURE
LogicName: LargeOrder
SQLStatement: select * from orders where quantity > 1000
Referencing Procedures
Procedures are reference by name. For example, once the procedure LargeOrder is defined you can use:
http://www.myserver.com/record_browser.html?ProcedureName=LargeOrder
in your HTML file either as a hyperlink or as an URL in your browser. This instructs the server to execute the procedure named LargeOrder and to use the recod_browser.html to display the result set.
Resource Management
Resources are the things you want to offer through the web server. There are different type of resources you can manage and control using the SQLData server:
Resources are stored in a table named RESOURCE in the SQLDataSystem database. The server loads the resources at startup and caches them in memory for efficiency. This means any changes in the resource table will not be reflected unless the server is restarted.
The columns in the resource table are defined as follows:
Set up virtual directories
A virtual directory is a directory name that can be used in an URL for accessing files under the directory. There must be a physical directory for each virtual directory. To set up a virtual directory:
Create Virtual Data Sources (VDS)
A virtual data source is a data source exists on another machine, but can be accessed just like a local ODBC data source. You can perform distributed data access using VDS.
To set up a virtual directory, select DataSource as the ResourceType and enter the location of the data source in the RESOURCE table. The location of the physical data source is specified using the following format:
uda
://hostname:port/NetServer?DSN=your_dsnor
rpc
://hostname:port?DSN=your_dsnThe first one uses the Universal Data Access (UDA) protocol, which is very similar to the HTTP protocol with some extensions for data access. The second one uses the remote procedure call (RPC). RPC is more efficient in terms of performance, while UDA can access other servers anywhere on the Internet.
For this to work, the remote machine must also run a SQLData server.
Using Virtual Hosts
Virtual hosts are also referred to as Multi-home hosting, while a machine acts like multiple machines hosting contents of many domains. The feature is often used by Internet Service Providers (ISP) for sharing resources.
Suppose you have two different applications using SQLData servers, instead of running two instances of the server, the applications can be supported by a single SQLData server using virtual hosts.
To setup virtual hosts:
Assume you have set up two host names: ROCK and STONE, all requests to http://rock:8732 and http://stone:8732 will be served by the same SQLData server.
The SQLData server has many security features, which can be enabled by editing the configuration file.
In an Intranet environment, or a network environment where you know IP addresses of all the user machines. You can use ValidIP and InvalidIP settings in the configuration file to filter out machines that should not access the SQLData server.
ValidIP is a group of IP addresses that can access the server. Each IP address can be a regular expression (with * represents any number of digits and ? represents a single digit). For example:
ValidIP=205.215.60.*
specifies that IP address in a range of 205.215.60.1 to 205.215.60.255 can access the server. All other hosts will be rejected for services. Multiple IP addresses can be specified separated by semicolon:
ValidIP=205.214.50.12;205.215.50.23
allows only two machines to access the server.
InvalidIP is a group of IP addresses that can not access the server. InvalidIP should contain all known hostile machines.
If the SQLData server is used as a second-tier server that accepts dispatched messages only from the main web server. You can set the ValidIP to the IP address of the main web server. This makes sure that all requests must go through the main web server and no user has direct access to the SQLData server.
User authentication is enabled when Authenticate in the server configuration file is set to 1. A challenge is sent to user's browser if login information is missing, the browser will prompt user for user name and password.
The server uses both the standard UNIX authentication and Windows NT schema for validating user credentials.
Server secret is used in between-server communications. The server supports Dynamic Server Group (DSG), which is a cluster of servers that offers distributed database services. The physical location of the database is transparent to users. In order to prevent data from being intercepted, the servers use a secret key to encrypt data before sending it through the wire. The receiver who knows the secret can then decrypt the data.
For sensitive data, the following should be enforced:
When ServerSecret setting is present in the server configuration file, The secret key encryption will be enabled among the DSG. There is a performance penalty for using encryption. You can disable it by comment out the secret key in the server configuration file.
In a distributed database environment, databases are often installed and operated on different servers. This makes it a challenge to offer information to users through HTTP service. The SQLData database server offers a solution to such an environment. When installed on multiple machines, all the servers communicate with others to form a cluster of database servers. A server registers it when it is started, and unregisters when it is stopped. The group is formed dynamically at run-time. Every server in the DSG knows what other servers can offer and will be able to process database requests even though the database is on another server. The physical location of a database is transparent to users. An SQL statement sent to machine A may be eventually processed by machine B.
Here are some of the advantages of DSG:
Setup a DSG:
A DSG is uniquely identified by its address and port. Members in the DSG multicast messages using the same address and port through User Datagram Protocol (UDP). All members in the same group must use the same address and port. The address is an IP class D address in the range of 224.0.1.0 to 239.255.255.255. Port can be any valid IP port unique to your network.
You may set up multiple DSGs by using either different ports or addresses. Address and port can be specified in the server configuration file Similar to the following:
UDPAddress = 234.7.6.5
UDPPort=6773
Setup a DSG Member:
A server can join a DSG by specify a membership type in the server configuration file. Membership is an integer that represents how the server participates in the DSG communication.
ClusterMember= n
Where n can be
By assigning different DSG membership to your servers, you can create totally different architectures of database services. From DSG ring (in which all members are full membership servers) to DSG hierarchy (in which only one server is a full membership server that serves as the interface to users, the rest of servers are export-only servers).
Partners and Incremental Backup
Synchronization and Synchronization Type
Partners are servers that have the same database contents. It is sometimes understood as primary server and backup servers. We choose Partners because a primary server can become a backup server and a backup server could be promoted to a primary server. When a primary server (the server that is actively providing services) dies or could not be reached. One of its partners becomes a primary server and begins to publish database information through multicasting.
A server can have more than one partners or backup servers. But a backup server can serve only one primary server in the current implementation.
The biggest challenge in such an environment is to keep all partners in synch. In other words, each server should have the same content in a reasonably short period of time. There are three types of synchronization:
The SQLData server supports 1 and 2. Type 3 is usually supported by DBMS.
To set up partners, you need to add the Partners setting in the server configuration file as follows:
Partners=protocol://hostname:port/virtual_directory
where protocol can be either:
The virtual_directory is needed only for the UDA protocol and it should be /NetServer.
There can be multiple partners in the setting, separated by a semicolon. For example:
Partners=rpc://www.foo.com;uda://stone:8732/NetServer
Specifies two partners, www.foo.com and stone. Note that the port number is 8732 for host stone using UDA. This is the default port for the SQLData server.
The SQLData server is a very high performance database server. It has sustained a test of millions of requests per day on a 133MHZ computer with 64M memories for four consecutive days. Here are some tips for further improving the server performance:
Command Line Options
You can start the web database server from the command line. The web database server accepts the following parameter as command line options:
-PORT : The port number the server is listening to, default is 8732.
-STANDALONE : Run in standalone mode (console mode).
-HOMEDIR szDir : The home directory for sqldata server
-DEBUG level : Run server in debug mode, where level is an integer.
level=1 save debug info to file
level=2 display debug info on screen and
save debug info to file
level=4 verbose mode.
-ROUTER : Run the server as an HTTP router.
-CERT szCertFile : Certification file when in secure mode.
The file must be in PEM format with private keys
The server runs in nonsecure mode if the file is not specified.
-VERIFYMODE nMode : this is for ssl operation only.
0 : No certificate verification.
1 : Verify peer's (client host) certificate.
4 : Verify Client (personal) certificate.
A: There is couple of possibilities. Check the proxy settings of your browser. If proxy is turned on, the proxy server doesn't understand localhost. You could either turn of f proxy or use the real name of your machine in the request. You may also want to make sure the port number in the request is 8732.
Related Documents