SQLData Systems, Inc.

Server Scripting Guide

 

 

 

Server Scripting Guide

Creating Dynamic Contents

Introduction

In a web-based application, it is often desirable to be able to create HTML file at run-time based on user inputs; to be able to create dynamic query statements and retrieve data based on user selected criteria and to be able to create custom forms for database operations. The SQLData scripting language is designed exactly for such purposes. The script language is a tool for web developers to create dynamic contents with least effort. The language has the following key features:

    1. The language allows you to send any HTML element to user browser directly, and use conditional statements to generate dynamic HTML pages.
    2. The language provides powerful functions for creating forms and formatting database results.
    3. The language provides a function library that allows you to perform direct database operations and create dynamic contents based on the information in the database tables.

The script language is pretty much like the regular C language with the following simplifications:

The procedures written using scripts can be embedded anywhere in an HTML file. The script procedures can also be in a separated file and using server side include (SSI) in HTML files. The coding is enclosed by a beginning marker and ending marker, which indicate the start and end point of the script. The markers can be any valid strings that do not cause any confusion. For instance, you can choose

<!--- and --!>

as the beginning marker and ending marker. The default marker is [ and ]. The markers can be set in the server configuration file. A string "Code" indicates the strings within the marker are SQLData scripts. For example, a simple hello world HTML using the scripts would look like the following:

<html>

[Code Print("Hello World");]

</html>

Scripts in an HTML are executed sequentially by the SQLData server. The code is replaced by the result of the execution before sending to the client.

Basic Language Elements

The script language uses almost the same syntax as the C language.

 

Control Statements

The server script support the following control statements:

If Statement

If (condition)

{statement 1;}

else{

statement2;

}

The server evaluates the condition and executes the first statement if the condition is true. The condition must be a Boolean expression. Multiple condition can be put together using the Boolean operator && and || as defined in the standard C language.

For Loop

for (InitializeStatement; ConditionStatement; EncrementStatement)

{ Statements; }

For example, the following Print statement will be executed 10 times:

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

Print(i);

The break statement is not supported inside the For loop.

While Loop

while(condition)

{ Statements;}

The server executes the statements in the statement body until the condition is evaluated to 0.

Processing SQL statements

The server provides a group of functions that make database processing easy. To process a query, following these steps:

    1. Connect to an ODBC data source.
    2. Open the SQL statement using OpenSQL function.
    3. Fetch data from database server using the Fetch function.
    4. Processing fetched records.
    5. Go back to step 3 until all records are processed.
    6. Close the SQL statement.
    7. Disconnect from the database.

Here is a simple example that shows how to process a query statement and send the result to user's browser.

[Code

// display results as plain text

Connect("TestDbase", "", "");

OpenSQL("Select * from customer where country ='UK'");

Print("<pre>");

Print(Header());

Print("<br>");

nRows=Fetch();

for(j=0; j<nRows; j++)

{

Print(Record(j));

Print("<br>")

}

CloseSQL();

Print("</pre>");

]

Inserting and Updating Records

One of the main tasks in developing a web application is to interact with user and to perform database operations as needed. This is often accomplished by using HTML forms. The SQLData script language provides function for creating forms that can be used for database operations. A general procedure is as follows:

    1. Connect to the ODBC database and open the table using a query statement.
    2. For record updating, move to the record to be modified.
    3. Call the PrepareForm function to create a form.
    4. Using PrintField function to format each field in the database table.
    5. Close the SQL statement and disconnect from the database.

Here is an example that creates a form for updating a record in the customer table:

[Code

// Connect and prepare the result

Connect("TestDbase", "", "");

OpenSQL("Select * from customer where country = 'UK'");

RowId= 3;

Move(RowId);

PrepareForm(0);

FieldCount=FieldCount();

// format each field and put the data in the HTML element.

Print("<pre>");

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

{

Print("<p>");

PrintField(i, 1);

}

Print("</pre>");

CloseSQL();

Disconnect();

// we need buttons here.

Print("<P><INPUT TYPE=submit value=\"Submit Change\"> <INPUT TYPE=reset>");

// close the form.

Print("</form>");

]

 

 

Getting Server Context Information

The SQLData server parses and processes HTTP requests from user and keeps the request information in the server context. You can use the GetRequest function to retrieve the request parameters. For example, an HTTP request

http://www.myserver.com/goSQL.html?DataSource=Dbase&Operaion=Query

can be examined using:

DataSource = GetRequest("DataSource");

Operation = GetRequest("Operation");

The GetRequest function can also be used for retrieving HTTP request headers as defined by the protocol. You need to prefix the name of the header with "header:". For instance, to determine what kind of browser a user is using, you would call

BrowserName = GetRequest("header:User-Agent");

where User-Agent is an HTTP request header name.

You can use the GetRequest function to get the Cookies sent from a browser using "cookie:CookieName" as the parameter.:

SessionId = GetRequest("cookie:SessionId");

gets the value of a cookie named SessionId and assign it to the SessionId variable.

Getting Server Configuration Information

It is sometime necessary to find out server settings before processing a request. This is done by using the GetServerSetting function. You can retrieve all settings, including your custom settings from the server configuration file. For instance, you can get the default data source if the data source is not present in the request:

SystemDSN = GetServerSetting("DataSource");

A Complete Sample - Record Browser

Let's create a procedure that can be used for processing query statements against an arbitrary ODBC data source. We will also format the result set so that when user clicks on the record, the record detail is displayed.

Assume that the data source, SQL statement and the HTML file for the record detail are passed to us as parameters. The HTTP request may be something like:

http://youhost/record_browser.html?DataSource=Dbase?SQLStatement=select * from customer&DetailPage=record_editor.html

Here is the procedure using the SQLData script language:

[Code

szDataSource = GetParam("DataSource");

szSQL=GetParam("SQLStatement");

szTemplate=GetParam("DetailPage");

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 procedure is very straightforward and similar to the previous example. Notice that we now have a hyper-link for each record and we pass the data source, SQL statement and row id information to the record detail page.

To use the procedure in your HTML page is very easy. Suppose the procedure is stored in a file named resultset.cod. Our record_brower.html would be as simple as the following:

<html>

[include resultset.cod]

</html>

This might be the simplest HTML you've ever seen before, Yet it is so powerful that it can process all queries to any databases installed on you machine.

 

Function Reference

This section contains detailed description of the functions implemented in the SQLData scripting language..

Print(OutputString)

Print out a string.

Parameters

OutputString : The string to be printed.

 

Return

The number of characters printed.

Example

Print"Hello World");

Remarks

The SQLData server sends the string to user browser as part of the HTML file. This is the only function that can be used to construct a HTML file inside the script language.


GetErrorString(DataSource, UserName, Password)

Get the last error string.

Parameters

 

Return

The string that contains error messages returned from the ODBC layer.

Example

if(!Connect("MyDSN", "", ""))

{

Print("Unable to connect to the database, ");

Print(GetErrorString());

}

 

Remarks

 

 


Connect(DataSource, UserName, Password)

Connect to an ODBC data source.

Parameters

 

Return

The returned value is 1 if the function is successful. It is 0 otherwise.

Example

if(Connect("MyDSN", "", ""))

{

OpenSQL("select * from customer");

.

}

 

Remarks

You must connect to an ODBC data source before perform any database operations. The ODBC data source must be a system data source.

The server may cache database connections based on the server settings. So, even though the Connect function is called for each request, the server may simply return a cached connection.

 


Disconnect

Disconnect from the current Database.

Parameters

 

Return

None.

Example

if(Connect("MyDSN", "", ""))

{

OpenSQL("select * from customer");

.

}

Disconnect();

 

Remarks

A database connection is closed automatically when (1) a new connection is established; (2) The process of the HTML is complete; (3). The request is redirected.

The function removes the database object associated with the script interpreter. The server may elect to cache a database connection for a longer period of time. Therefore, frequent use of Connect and Disconnect may not necessarily introduce performance problem.

It is recommended that a database connection is closed immediately if it is no longer in need.

 

 

 


OpenSQL(SQLStatement)

Open a SQL query statement.

Parameters

 

Return

The returned value is 1 if the SQL statement is opened successful. It is 0 otherwise.

Example

if(OpenSQL("select * from customer"))

{

RowCount = Fetch();

}

 

Remarks

A query statement must be opened before any fetch operation can be performed. The server performs syntax checking on the SQL statement and prepares buffer for result sets.

This function is needed only for SQL query. All other statement should be executed immediately using the ExecuteSQL function.


CloseSQL()

Close the current SQL statement.

Parameters

 

Return

None.

Example

if(OpenSQL("select * from customer"))

{

RowCount = Fetch();

}

CloseSQL();

 

Remarks

The SQL statement will be closed automatically after the request is processed. But it is always a good idea to close a SQL statement when it is no longer needed.

 


 

Fetch()

Fetch the result set associated with an open query statement.

Parameters

None.

Return

The function returns the number of rows fetched. 0 indicates the end of the result set.

Example

RowCount = Fetch();

while(RowCount>0)

{

for (I=0; I<RowCount; I++)

{

Print(GetRecord(I));

}

RowCount=Fetch();

}

Remarks

The server fetches multiple rows at a time. The server retrieves the next set of rows when the function is called.


 

Move(RowId)

Move the cursor to the specified row.

Parameters

RowId: It is an absolute row number in the result set starting from 0. There is a special row id , ROWID_LAST, defined in the script. It is represented by -33.

Return

The number of rows fetched after the move.

Example

// move to the first row

Move(0);

// move to the last row

Move(-33);

Remarks

 


 

ExecuteSQL(SQLStatement)

Parameters

 

Return

The returned value is 1 if the function is successful. It is 0 otherwise.

Example

The following demonstrate how to create a record editor using HTML form.

if(!ExecuteSQL("Drop table customer"))

Print("oops, the table could not be dropped.\n");

 

Remarks

The function can be used for processing any SQL statements other that query, including Data Manipulation Language (DML), Data Definition Language (DDL) and Data Control Language (DCL).

Prepared SQL statement is not supported in this release, which means that no run-time parameters can be used in the SQL statement.


 

GetRecord(RowID)

Get a complete record in the current fetched row set.

Parameters

Return

The complete record as a string.

Example

The following example returns the third field name in the result set.

RowCount = Fetch();

while(RowCount>0)

{

for (I=0; I<RowCount; I++)

{

Print(GetRecord(I));

}

RowCount=Fetch();

}

Remarks

The record is formatted based on the data type and the size of the fields in the record. All the columns will aligned properly if fix pitch font is used.

 


 

GetHeader()

Get the header of the record set. The header of a record set contains all formatted field names.

Parameters

 

Return

The function returns a string that contains all field names.

Example

Print(GetHeader());

Remarks

The header is formatted in such a way that it is guaranteed it will aligned properly with the database record returned by the GetRecord function if fixed pitch font is used.


GetFieldValue(RowId, FieldId)

Parameters

Return

The function returns the field value as a string.

Example

The following example prints all field values returned from a query.

RowCount = Fetch();

FieldCount = FieldCount();

while(RowCount>0)

{

for (I=0; I<RowCount; I++)

{

for(j=0; I<FieldCount; j++)

{

Print(GetFieldValue(I,,j));

}

}

RowCount=Fetch();

}

Remarks

The function gives you the capability of formatting a record in your own way. You can put a field value anywhere on an HTML page using the function. The function is especially useful for creating HTML forms using values from a table.


 

GetFieldAttibute(FieldId, AttributeType)

Get descriptor information about a column.

Parameters

Return

The function returns an integer value of the specified attribute.

Example

To determine if the first field is nullable, you can use

Nullable = GetFieldAttribute(0, 1008);

where SQL_DESC_NULLABLE is defined as 1008 in ODBC SQL.H file.

 

Remarks

The function can only be used for retrieving attribute with integer value. Some of the attribute types defined in ODBC are listed below:

SQL_DESC_COUNT 1001

SQL_DESC_TYPE 1002

SQL_DESC_LENGTH 1003

SQL_DESC_OCTET_LENGTH_PTR 1004

SQL_DESC_PRECISION 1005

SQL_DESC_SCALE 1006

SQL_DESC_DATETIME_INTERVAL_CODE 1007

SQL_DESC_NULLABLE 1008

SQL_DESC_UNNAMED 1012

SQL_DESC_OCTET_LENGTH 1013

SQL_DESC_ALLOC_TYPE 1099


GetFieldName(FieldId)

Get the name of a field.

Parameters

 

Return

The name of the field is returned as a string.

 

Example

To get the name of the third field,

MyFieldName = GetFieldName(2);

 

Remarks

The returned value is empty if the field id is out of range.


 

GetFieldLen(FieldId)

Get the length of a field.

Parameters

 

Return

The length of the specified field.

Remarks

 


GetFieldType(FieldId)

Get the data type of a field.

Parameters

 

Return

The data type of the specified field.

Remarks

The data type returned is an integer value of the ODBC SQL data type. Some of the commonly used SQL data types are listed below:

SQL_UNKNOWN_TYPE 0

SQL_CHAR 1

SQL_NUMERIC 2

SQL_DECIMAL 3

SQL_INTEGER 4

SQL_SMALLINT 5

SQL_FLOAT 6

SQL_REAL 7

SQL_DOUBLE 8

SQL_DATETIME 9

SQL_VARCHAR 12

SQL_TYPE_DATE 91

SQL_TYPE_TIME 92

SQL_TYPE_TIMESTAMP 93


 

IsFieldNumeric(FieldId)

Determine if a field has numeric value.

Parameters

 

Return

The function returns 1 if the field date type is numeric. It returns 0 otherwise.

Remarks

You can also use the GetFieldType function to determine if the field is numeric.


 

GetFieldCount()

Parameters

None.

 

Return

Return the number of fields in the result set.

Example

FieldCount = GetFieldCount();

for(I=0; I<FieldCount; I++)

Print(GetFieldValue(I));

 

Remarks

 


 

GetRecordSize()

Parameters

None.

 

Return

The function returns the length of the formatted record in the current result set.

Remarks


PrepareForm(FormType)

Prepare an HTML form.

Parameters

FormType: The type of the form. 0 for record update form and 1 for record insertion form.

 

Return

None.

Example

PrepareForm(0);

FieldCount=FieldCount();

// format each field and put the data in the HTML element.

Print("<pre>");

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

{

Print("<p>");

PrintField(i, 1);

}

 

Remarks

The PrepareForm function should be called after the query statement has been opened successfully.


PrintField(FieldId, Format)

Print out the field name and value as HTML elements in a form.

Parameters

 

Return

None.

Example

PrepareForm(0);

FieldCount=FieldCount();

// format each field and put the data in the HTML element.

Print("<pre>");

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

{

Print("<p>");

PrintField(i, 1);

}

 

Remarks

The PrintField function puts field name and value on a single line. If you need to format the field differently, you can use the Print function to format the field.

The PrintField function creates HTML text box based on the data type and length of the field. It automatically limit the number of characters to be less than the field length.


 

ShowDataSources(StyleString)

Display a selectable list of available ODBC data sources.

Parameters

StyleString: A string that specifies the type of the HTML selection. It can be either "radio" for radio button, or "select" for dropdown menu.

 

Return

None.

Example

// create a form to include an ODBC data source selection.

Print("<Form action=DataSourceExample method=Post>");

ShowDataSources("select");

 

Remarks

The ShowDataSource function adds an HTML selection list to the HTML forms. The name of the field is DataSource, and it is value is the user selected ODBC data source name. The function should always be used inside an HTML form scope.

The function constructs the list using the system data source name.


 

ShowTables(StyleString)

Display a selectable list of available tables in the current ODBC data source.

Parameters

StyleString: A string that specifies the type of the HTML selection. It can be either "radio" for radio button, or "select" for dropdown menu.

 

Return

None.

Example

// create a form to include an ODBC data source selection.

if(Connect("MyDbase", "", ""))

{

Print("<Form action=TableListExample method=Post>");

ShowTables("select");

}

 

Remarks

The ShowTable function adds an HTML selection list to the HTML forms. The name of the field is TableName, and it is value is the user selected table name. The function should always be used inside an HTML form scope.

The function constructs the list using the current database connection. It should be called after the Connect function.


 

Redirect(URL)

Redirect a request to anther URL.

Parameters

URL: The new URL to redirect to.

 

Return

None.

Example

if(!Connect("MyDatabase", "", ""))

{

// may be you want to read our document if the connection failed J .

Redirect("http://www.sqldata.com/docs.html");

}

Redirect("http://www.sqldata.com");

 

Remarks

The function can be used anywhere in the HTML to switch to another host or HTML.


 

PrepareForm(FormType)

Prepare an HTML form.

Parameters

FormType: The type of the form. 0 for record update form and 1 for record insertion form.

 

Return

None.

Example

PrepareForm(0);

FieldCount=FieldCount();

// format each field and put the data in the HTML element.

Print("<pre>");

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

{

Print("<p>");

PrintField(i, 1);

}

 

Remarks

The PrepareForm function should be called after the query statement has been opened successfully.