Server Scripting Guide
Server Scripting Guide
Creating Dynamic Contents
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:
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:
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:
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 out a string.
OutputString :
The string to be printed.
The number of characters printed.
Print"Hello World");
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.
The returned value is 1 if the function is successful. It is 0 otherwise.
if(Connect("MyDSN", "", ""))
{
OpenSQL("select * from customer");
…….
}
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.
Open a SQL query statement.
The returned value is 1 if the SQL statement is opened successful. It is 0 otherwise.
Example
if(OpenSQL("select * from customer"))
{
RowCount = Fetch();
}
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.
None.
The function returns the number of rows fetched. 0 indicates the end of the result set.
RowCount = Fetch();
while(RowCount>0)
{
for (I=0; I<RowCount; I++)
{
Print(GetRecord(I));
}
RowCount=Fetch();
}
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
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");
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.
Get a complete record in the current fetched row set.
The complete record as a string.
The following example returns the third field name in the result set.
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.
Get the header of the record set. The header of a record set contains all formatted field names.
The function returns a string that contains all field names.
Print(GetHeader());
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)
The function returns the field value as a string.
The following example prints all field values returned from a query.
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.
The function returns an integer value of the specified attribute.
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.
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.
The name of the field is returned as a string.
To get the name of the third field,
MyFieldName = GetFieldName(2);
The returned value is empty if the field id is out of range.
Get the length of a field.
The length of the specified field.
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.
The function returns 1 if the field date type is numeric. It returns 0 otherwise.
You can also use the GetFieldType function to determine if the field is numeric.
GetFieldCount()
None.
Return the number of fields in the result set.
Example
FieldCount = GetFieldCount();
for(I=0; I<FieldCount; I++)
Print(GetFieldValue(I));
None.
The function returns the length of the formatted record in the current result set.
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("
}
Redirect("
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.