The main characteristic (and limitation) of the FetchRec method is that the cursor can be moved only forwards in a sequential fashion. To position the cursor on an arbitrary record of the current set, we can use the Seek method with prototype:

void Seek(int index, int cursorID)
     

The index parameter represents the numerical index of the record that we want to position the cursor on. If we write:

dbconn.Seek(1)
     

we will position the current cursor on the first record of the set. If the specified index is greater than the number of records in the current set, the cursor is positioned after the last record and a successive invocation of the FetchRec method returns an empty array. The index of the record currently under the cursor can be obtained using the Tell method with prototype:

int Tell(int cursorID)
     

An example is:

curRecIndex = dbconn.Tell()
     

When a script uses more than one cursor, it is sometimes necessary to delete a cursor that is no longer necessary, because there is a limit in Biferno on the number of cursors that can be simultaneously used on the same db variable. Cursor deletion also optimizes database performance by freeing resources.

To eliminate a cursor the Free method is available with prototype:

void Free(int cursorID)
     

There are cases when the cursor is no longer necessary after the Exec method was called. In such cases a value of true can be passed to the fourth argument of the Exec (freeCurs), which causes the cursor to be automatically deleted and eliminates the need for a subsequent call to Free. Usually the cursor is necessary when executing SELECT-like SQL statements to be able to subsequently invoke the GetCurRecs, FetchRec etc. methods. Sometimes the cursor is ignored for SQL statements such as UPDATE, DELETE, INSERT (nonetheless the cursor might still be necessary, e.g. to be able to execute the GetAffectedRecs method).

All cursors associated to a database connection are automatically eliminated when the corresponding db class variable is deleted. This is because cursors associated to database connections stored in variables (local or global) having the current script as their scope are deleted when script execution terminates. If we use cursors associated to connections stored in variables with wider scope (e.g. application), these cursors will have to be explicitly eliminated using the Free method when they are no longer necessary or when the script is exited.

Considering the previous example, assume that the string corresponding to the surname to retrieve from the users table is obtained from user input (e.g. from a text field within a HTML form). If we call lname the variable associated to the value inserted by the user, we can dynamically build our SQL string as follows:

<?
	lname = db.Escape(lname)
	query = "SELECT * FROM users WHERE last_name = '" + lname + "'"
?>
     

The static method Escape of the db class transforms a literal string into the appropriate format for insertion in an SQL statement. The prototype is as follows:

static string Escape(string str)
     

This method escapes single quotes that might be present in the string by doubling them (see also the RealEscape method of class db in "Biferno: Reference Guide").

A parametric SQL query contains internally substitution markers (a question mark "?"), which may be substituted by values before query execution.

SQL markers can be used in the WHERE clause of a SELECT statement, in the SET clause of an UPDATE statement, and in the VALUES clause of an INSERT statement. Markers can not be used to make table or column names parametric.

We will now modify the script used as an example of preparation and execution of an SQL query to use a parametric query. The first step is to prepare the query, e.g. by inserting the following code rows in the "Biferno.config.bfr" configuration file of our application:

<?
	// Database connection
	application aDBconn = db("DSN=MYDB;UID=mydbuser;PWD=enterprise", "odbc")
	// Query preparation
	local query = "SELECT * FROM users WHERE last_name = ?"
	application aQuery_selname = aDBconn.Prepare(query)
?>
     

Since the database connection must be established before a query can be prepared, the connection must be established in the "Biferno.config.bfr" file.

This technique allows to open a single database connection at application startup. By associating the connection to a variable with application scope, the connection will be visible to and usable by all application scripts, avoiding reconnection at every request.

The following script assigns the value "Kirk" to the lname variable (in practice this variable is likely to be associated to an input value) and, after associating the variable to the query marker using the Bind method, executes the query and processes its results.

<?
	lname = "Kirk"

	// Selection of prepared query
	curs_id = aDBconn.GetPrepared(aQuery_selname)
	// Association of the lname variable to query marker
	aDBconn.Bind(1, "lname", lname.length, inputBindMode, curs_id)
	// Execution of prepared query
	aDBconn.ExecPrepared(curs_id)
	// Results processing
	if (aDBconn.GetCurRecs(curs_id))
		{
			recArray = aDBconn.FetchRec(curs_id)
		}
	// Cursor deletion
	aDBconn.Free(curs_id)
?>
     

The Bind method allows to associate a variable to a marker in an SQL query and has the following prototype:

void Bind(int pos, string variableName, int bytes, int mode, int cursorID)
     

When the query is executed via the ExecPrepared the current value of the variable specified by the variableName parameter is substituted to the marker corresponding to the position identified by the pos parameter and the resulting SQL statement is submitted to the database (more precisely, the pos parameter specifies the index of the occurrence of the ? character within the SQL string).

The bytes parameter denotes the length of the variable called variableName. The mode parameter is the kind of Bind that we are executing. In this case we are passing an input variable and therefore we use the db.inputBindMode value. The Bind method can be also used to obtain return values, e.g. from a stored procedure. In this case db.outputBindMode or db.inputOutputBindMode should be used for the mode parameter.

The cursorID parameter supplies the cursor identifier returned by the GetPrepared method.

The Bind method can associate a variable that has not been defined. The important point is that this variable must be defined when the ExecPrepared method is called, or an error will be generated.

The usefulness of the Bind method is apparent when the value of the variable associated to the SQL parametric string is modified within a loop. In the following example we see how the value corresponding to an array element with index varying within a for loop can be associated to a parametric query.

<html>
<body>
<?
namesArray = array("John", "James", "Andrew", "Leonard")
names = namesArray.dim

dbconn = db("DSN=MYDB;UID=mydbuser;PWD=enterprise", "odbc")
query = "SELECT first_name, last_name, sex, age FROM users WHERE first_name = ?"
prep_id = dbconn.Prepare(query)
curs_id = dbconn.GetPrepared(prep_id)
dbconn.Bind(1, "namesArray[i]", namesArray[i].length, 
				 inputBindMode, curs_id)
for (i = 1; i <= names; i++)
	{
		dbconn.ExecPrepared(curs_id)
		nrec = dbconn.GetCurRecs(curs_id)
		if (nrec)
			{
?>
	<table border="0" cellpadding="4" cellspacing="0">
<?
			while (recArray = dbconn.FetchRec(curs_id))
				{
?>
		<tr>
			<td>$recArray["first_name"]$</td>
			<td>$recArray["last_name"]$</td>
			<td>$recArray["sex"]$</td>
			<td>$recArray["age"]$</td>
		</tr>
<?
				}
			}
?>
			</table>
<?
	}
dbconn.Free(curs_id)
?>
</body>
</html>
     

Internally to the for cycle the ExecPrepared method is called. The method executes the parametric query, where the value of the element of the namesArray array corresponding to the current value of the loop index (i) is substituted to the ? marker. Notice how the array name followed by the index between square brackets is passed to the Bind method. Of course both the array and the index must be defined when the ExecPrepared method is called. With this technique, the search string is changed automatically at every loop iteration.

The BindAll method is similar to the Bind method, and allows to associate a variable to all cursors associated to a prepared query (the Bind method associates the variable to a single cursor). The BindAll method has the following prototype:

void BindAll(int pos, string variableName, int bytes, int mode, int poolID)
     

Let's see an example of the use of this method. In the "Biferno.config.bfr" configuration file of our application we write the following code lines:

<?
	application aDBconn = db("DSN=MYDB;UID=mydbuser;PWD=enterprise", "odbc")
	local query = "SELECT * FROM users WHERE last_name = ?"
	application aQuery_selname = aDBconn.Prepare(query, 16)
	aDBconn.BindAll(1, "lname", 255, inputBindMode, aQuery_selname)
?>
     

Using the Prepare method 16 identical parametric queries are sent to the database and the database generates 16 cursors associated to the parametric query. On the next line, the lname variable (which is not yet defined when the "Biferno.config.bfr" is executed) is associated to all 16 cursors associated to the prepared query by calling the BindAll method and passing the query identifier returned by the Prepare method. The BindAll method must be called right after preparation of the query and before the GetPrepared method is invoked. Notice that the length of the lname variable is not known when the BindAll method is called, and thus the value 255 is passed for the bytes parameter. When executing the prepared query (ExecPrepared call), the code should check that the length of lname does not exceed 255 bytes.

Using this technique we avoid calling the Bind method multiple times to associate the lname variable to the individual cursors returned by the GetPrepared method in the application scripts where the prepared query will be executed.

The search class has a number of properties that allow to obtain information on the associated search string and to modify the meaning of the different substrings. Substrings are single words (or groups of words separated by spaces) and limited by logical operators and parentheses.

The tot read-only property contains the total number of substrings in the search string. The mode property contains the implicit concatenation logical operator for substrings of the search string consisting in space-separated words. This property is used at writing time, when the kind of SQL query that will be generated by the ToSQL method can be defined by the concatenation of strings contained in a search class variable and by assigning one of the logical operators and or or (search class constants) to the mode property. We will show some examples in the following.

The string read-only property is an array containing all substrings of the search string. The oper read/write property is an array containing all logical operators separating substrings of the search string. The possible values of the array elements are the constants all, allNot, and, andNot, or and orNot of the search class.

The group read/write property is an array containing, for each substring of the search string, an identifier indicating the associated type of parenthesis (open, closed, none). In correspondence to an open parenthesis the array contains the value of the openPar constant of the search class. In correspondence to a closed parenthesis the array contains the value of the closePar constant of the search class. The noPar constant denotes the absence of a parenthesis.

The findType read/write property is an array containing, for each substring of the search string, the kind of comparison corresponding to the position of a wildcard character in that string to use during database search. For each substring one of the values specified by the begins, ends and contains constants of the search class will be used.

We will now show some examples of use of the ToSQL method of the search class to construct SQL strings for database search. The ToSQL method has the following prototype:

string ToSQL(string fieldName, boolean isNumeric, boolean lowerSQL)
     

The fieldName parameter is the search column name (if no value is supplied, the method uses the "[field]" default value). The isNumeric Boolean parameter specifies if the column is a numeric column, i.e. if values should be enclosed between single quotes in the SQL string. The default value is false, based on the fact that searches are more commonly performed on text fields. The lowerSQL Boolean parameter specifies that the result string should use lowercase SQL keywords (the default is uppercase keywords).

The ToSQL method returns an SQL string equivalent to the search associated to the search class object on which the method was called.

We assume in the following script that the search string assigned to the src_str variable was input by the user in a HTML form. Based on this string, a search is executed on the last_name field of the users table (see Table 15.3, “Operators supported by the search class in search strings”).

<html>
<body>
<?
src_str = "K* | M*"

dbconn = db("DSN=MYDB;UID=mydbuser;PWD=enterprise", "odbc")
query = "SELECT first_name, last_name FROM users WHERE "
query += search(src_str).ToSQL("last_name")
curs_id = dbconn.Exec(query)
if (dbconn.GetCurRecs(curs_id))
{
while (arr_rec = dbconn.FetchRec(curs_id))
	print(arr_rec["first_name"]+" "+arr_rec["last_name"]+"<br>")
}
?>
</body>
</html>
     

The ToSQL method is used in the above code to generate, starting from a search class object, an SQL string to be inserted after the WHERE clause in the SQL SELECT statement. In the case of the "K* | M*" search string that is contained in the src_str variable, the string is translated into its SQL equivalent: last_name LIKE 'K%' OR last_name LIKE 'M%'. The query resulting from this procedure selects all records in the users table where the last_name field contains a string starting with the letters K or M.

The following example shows the combined use of the ToSQL method and of the value of the mode property of the search class. Assume that an input word sequence separated by spaces should result in a database search for the same words separated by AND operators. Notice that this is not normally the case, as spaces are usually not considered as separators in search strings and a group of words is treated as a single substring. The script shows the technique to use in this case:

<?
	src_str = "movies sport music"
	my_src = search(src_str)
	my_src.mode = search.and // Search for the AND of the words
	query = "SELECT * FROM tv_channels WHERE "
	query += my_src.ToSQL("category")
?>
     

The SQL string contained in the query variable after script execution has terminated has the following value:

SELECT * FROM tv_channels WHERE category = 'movies' AND category = 
'sport' AND category = 'music'.
     

The use of the mode property of the search class can be a valid alternative to the use of operators, because it allows to simplify search strings while maintaining a degree of flexibility.

An obvious limit to the above script is the fact that keywords are compared with the field content using the "equals" operator. If we wish to search for all records containing one or more keywords of the search string without forcing the user to specify wildcard characters at the beginning and at the end of all keywords, we can operate as in the following example:

<?
function SetFindType(search *inSrc, int inFind)
{
	if (inFind == 0 || inFind == search.begins || inFind == search.ends || \
		inFind == search.contains)
	{
		nFind = inSrc.findType.dim
		for (i = 1; i <= nFind; i++)
			inSrc.findType[i] = inFind
	}
}

src_str = "movies sport music"
my_src = search(src_str)
my_src.mode = search.or // Search for the OR of the words
SetFindType(&my_src, search.contains)
query = "SELECT * FROM tv_channels WHERE "
query += my_src.ToSQL("category")
?>
     

The SQL query resulting from script execution is:

SELECT * FROM tv_channels WHERE category LIKE '%movies%' OR category LIKE 
'%sport%' OR category LIKE '%music%'.
     

The SetFindType function modifies the value of the findType property for all substrings of a search class variable passed by reference and it assigns one of the constant values begins, ends or contains to the property (the zero value removes the property).