SQLData Systems, Inc.

Server User Guide





SQLData Server User Guide

Getting Started

Setting up ODBC Data Sources

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:

Browsing Database Tables

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:

    1. Start your web browser and type in
      where localhost can be your machine name and 8732 is the default port for the service. You should see the welcome page displayed in your browser with Service Indices in the left frame window.
    2. Click on the Database under the Service Indices to see all your databases and tables displayed in the left frame window. Database tables are grouped together under the name of the data source. An error will be displayed if the server could not connect to a data source. The server generates a form for you to make a manual connection, type in your user name and password for the data source and click the Connect button.
    3. Click on the name of the table you want to see. The content of the table will be displayed in the right frame window.
    4. Now click on any record in the result set, the record detail will be displayed in an HTML form. You can now make changes in the record and click the Update button or click the Delete button to remove it from the database.

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 support@sqldata.com if the problem persist.

Server Administration

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.

Administrating User Account

There are two ways to store user information. One is using a database table in the SQLDataSystem database and another is using a text file stored in the CONFIG directory. If AuthTable is specified in the server configuration file, the server will use the SQLStatement defined for accessing user information, otherwise it uses the sqldata.usr file under the CONFIG directory.

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:

    1. Access the server using the following URL in your browser:
      where localhost should be the name of your machine if proxy is used. You should see the welcome page and the service indices in your browser.
    2. Under Server Admin in the left panel, click Add New User. The form for adding new user should be displayed.
    3. Type in the user name and password for the user and assign a user group. The password must be at least 6 character long and the user group can be:
      s : The system administrator group.
      o : The regular user group.
      m : The operator group.
      You also need to enter the administrator's name and password in the form. Click the Add button.

Removing a user Account

To remove a user account, follow the these steps:

    1. Access the server with the following URL in your browser:
      to get the server home page.
    2. Under Server Admin in the left panel, click Revoke User. The form for removing a user should be displayed.
    3. Type in the name of the user to be removed, the administrator name and password. Click the Delete Button.

Changing Password

Any user can change his/her password using the Change Password form.

    1. Under the Server Admin in the service indices, click Change Password. The form for changing password will be displayed.
    2. Type in your user name, old password and the new password, then click the Change button.


Publishing Database Contents

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:

    1. Cut and past the template files provided by the server. The server comes with many powerful template files that can be used as part of you web pages. They are very simple and straightforward. Take a look at the files record_browser.html and record_editor.html. The two files are responsible for processing most of the database requests in the sample web (see http://www.sqldata.com/screen/screen.htm for pages created by these two files).
    2. Use the Template Token Commands (TTC) directly in your web pages. The server offers a group of powerful commands for creating dynamic HTML contents automatically. These commands are executed at runtime on the server side. You probably need only a few such commands to accomplish the task.
    3. Use the SQLData scripts. SQLData scripts are server side programs that are interpreted and executed by the server when they are embedded in the HTML files. We have created many useful routines and samples to demonstrate how to use the scripts. You can just include these routines in your HTML file in most of the situations.

Connecting to Your Database

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:

Display Database Information

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.

Using Template Token Commands

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:


<pre> [ResultSet(-1, -1, "")] </pre>


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

where TestDbase is a test data source SQLData server installed. You could use any valid ODBC data source in your request. Our simplest HTML file can process all your query statements although the page is not as pretty as it could be.

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:


<form method="POST" action="/update">

<pre> [Fields(0, -1, 1)]</pre>

<p><input type="submit" value="Update" ><input type="Reset" value="Reset"></p>



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.

Simple Server Script

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:


szDataSource = GetParam("DataSource");




Connect(szDataSource, "", "");










for(i=0; i<rows; i++)


Print("<A HREF=\"");


















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:

    1. The tables should all be normalized and there should be only one direct link between two tables.
    2. The relational key (foreign key) should be consistent. The same key must have the same name, type and length.

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:

    1. Generate cascade logic using:
      SQLData -BuildCascade MyCascade.ini
      where MyCascade.ini is the name of the file to be used for the cascade logic.
    2. Add a server setting in the server configuration file:
      CascadeFile = MyCascade.ini
    3. Add the following template token in the HTML page that displays a record:
      [CascadeLink "MyCascade.ini", "/record_browser.html"]
      the record_browser.html is any HTML page that can display result set.

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.


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:


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:

    1. Click on the Administration link in the left panel of your browser. You may be required to log in at this time.
    2. Click on the Resource Management link to show all resources defined.
    3. Press the Add New button on the bottom of the page.
    4. Type in your virtual host in the HostName entry. Use DEFAULT if the directory is not on a virtual host.
    5. Enter the name of your virtual directory.
    6. Specify the physical directory for the virtual host. It can be a relative directory to the server ROOT (where the server is installed), or an absolute directory anywhere on your computer.
    7. Click the Add New button.

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:




The 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:

    1. In the control panel, go to Network and then Protocol. Click the property button and edit the Advance settings.
    2. Add one IP addresses for each virtual host you want to support.
    3. Add the host names and the IP addresses to you DNS.
    4. Enter a DocRoot into the Resource table for each virtual host, with the Location point to the directory where the HTML pages reside.
    5. Create virtual directories for the host if needed, use the name of the virtual host for HostName in the resource table.

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.

Server Security

The SQLData server has many security features, which can be enabled by editing the configuration file.

Tightening up Security

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:


specifies that IP address in a range of to can access the server. All other hosts will be rejected for services. Multiple IP addresses can be specified separated by semicolon:


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

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.

Secure Data Transactions

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.


The Dynamic Server Group

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 to 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 =


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:

    1. Real time synchronization: All partners must have exactly the same contend all the time. A transaction performed on the primary server can not be successful until all the partners do the same thing successfully. Client requests are blocked until the update has been propagated successfully to all partners. This type of synchronization maintains strict consistency at the cost of performance.
    2. Active synchronization: All partners can keep up with the primary in a short period of time (perhaps in minutes). The client request is considered as successful if the update at the primary server is successful. The SQLData server executes the same transactions on the partners immediately in a separate thread. The synchronization, however, could not be guaranteed to be successful. The primary server keeps a backlog of transactions and will retry later. A full replication may be needed when the retries failed.
    3. Delayed synchronization: All partners may not have the same content at times. There will be a significant delay in propagating changes, but they will be brought up-to-date eventually (may be through manual replication).

The SQLData server supports 1 and 2. Type 3 is usually supported by DBMS.

Setting up Partners

To set up partners, you need to add the Partners setting in the server configuration file as follows:


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:


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.

Fine Turning Performance

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:

    1. Use multi-thread. Set the MultiThread parameter in the server configuration to 1. The server will start at one thread for each incoming request. This could boost server throughput under heavily traffic. However, you need to make sure your ODBC driver is thread-safe. The multi-thread feature should be turned off if there are access violations in the driver.
    2. Use multiple processes. You can start multiple server processes by editing the registry entry at,
      \\HKEY_LOCAL_MACHINE\SOFTWARE\SQLData Systems\Web Database Service\2.0\Procs
      The default value is 1. You can change it to a higher value. The number of processes in the range of 3 to 6 usually gives good performance.
    3. Use session management. The server has a built-in session manager, which can caches user information, database connection, and database objects. User login must be enabled for the session manager to work properly because it marks the start of a session. The server automatically cleans up expired sessions. Setting the UseSession parameter in the server configuration file instructs the server to keep active sessions in memory. There are situations where ODBC driver allows only one result set per database connection. Session manager should be disabled.
    4. Use RPC protocol. We found that the RPC protocol is more efficient than the Universal Data Access (UDA) protocol. If all your servers in a DSG are in a single network segment or in an Intranet, enable RPCService in the server configuration file and specify server partners use RPC protocol. If your server spread in a wide area and must use Internet for communication, however, UDA is preferred.

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. 

Trouble Shooting

    1. Q: I can not start the server, a console window flashed once, then disappeared immediately.

      A: A good way to find out what happened is to run the server in verbose mode. Type in the following command line in a DOS prompt under the BIN directory where you installed the server:
      SQLData -debug 4
      Error messages will be displayed if the server encounters problems. You could also look at the log file under the log directory to find out what happened. The server won't start if you have a trial license and the license has expired. Let us know if you wish to extend the trial period and we can issue you an extension.
    2. Q: When I clicked on the Database link under the Service Indices Panel in my browser, I didn't see any of my ODBC data sources.

      A: This often happens in two situations. (1). A database connection failed and the database driver tries to display a dialog box for collecting additional information from you. But the dialog box is displayed behind the active windows on your desktop. Minimize all your front windows and you'll see the dialog box under such a situation. We recommend that you remove the data source if the database driver is not behaving properly. (2) The second situation is a sometimes difficult to find out. One of your database drivers is unable to make a connection but, unfortunately, it doesn't support timeout either. So it is waiting forever for a reply from your databases. To identify which driver is causing the problem, you can run the SQLData server in verbose mode in a command prompt:
      SQLData -debug 4
      The server will display progress if you click on the Database link in the Service Indices. Remove or modify the problematic data source will solve the problem.
    3. Q: I started the server with no problem, but I could not connect to the sever from my browser.

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