db.Open() - IVR Database Function | Xtend IVR Tutorial | Database Plugins

$alias = db.Open ( "<connectionString>", "<query>")

Establishes connection with the database and executes the query with the values populated into the dynamic variables.

Parameters

<connectionString>

ADO connection string or ODBC DSN

<query>

A valid SELECT query only

Return Value

Returns a group variable.

Remarks

If your database connection insists on USERNAME and PASSWORD, then you have to mention "User Id=...;Password=...;" in the Connection string; no matter whatever connection you have, let it be ADO/ODBC.
Open() triggers the following actions:
- Control returns to the main function.
The recordset object is a forwardOnlyCursor.
Open() is often clubbed with and followed by Skip().
It is recommended that each Open() returns the recordset to a separate alias name. For instance, let us consider the following statements:
Here, the recordset returned by $sql1 will close itself when it comes across the second Open() returned by $sql2, because the recordset returned by $sql2 is stored in the same $ret group variable.
LHS variable is necessary and compulsory for the proper functioning of all database functions. Hence, the following loop statement will generate $error variable:
It is recommended that you keep the connection string in a global variable at the beginning of the script to improve efficiency. Xtend IVR keeps a pool of connection objects and recordset objects to minimize resource usage.

Example

:MAIN 
	display "Waiting for call" 
	answer  
	
	$Provider= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Product.mdb;Persist Security Info=False"  
	
	display "Displaying records from the table"
	$alias = db.Open($Provider,"SELECT * FROM Product") 
	
	while $alias.eof = FALSE 
	    display $alias.id $alias.product $alias.productName 
		sleep 1000
	    $alias = db.Skip() 
	endwhile
	
	Display "End of the table"
	
	$alias = db.Close()
	hangup 
	goto MAIN 
	
	:ONSYSTEMERROR 
		hangup 
		MsgBox($error) 
		log $error
		$alias = db.close() 
		goto MAIN

In the script, an ADO connection is established with the database "Product.mdb". The records are displayed by executing the Select query in Open(). The first record appropriate for the query given, is returned and stored in dynamic variables through $alias. Values in the record are accessed as $alias.id, $alias.product and $alias.productName where id, product and productName are the fields in the table "Product". To move forward and populate the next record, we use Skip(). This continues until End of file is reached.