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

$intNum = db.RunSQL ( "<connectionString>","<query>")

Establishes connection with the database and executes the query.

Parameters

<connectionString>

ADO connection string or ODBC DSN.

<query>

Any standard SQL query.

Return Value

Returns 1 if successful, else 0.

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.
RunSQL() is a stand-alone function that triggers the following actions:
- Control returns to the main function.
To traverse through the recordset, use db.Skip() along with db.Open() instead of db.Runsql().

It is recommended that each RunSQL() returns the recordset to a separate alias name. If you are using the same variable for two different database operations, then the former one will be automatically closed for the latter. For instance, let us consider the following statements:

............
............
$db= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Student.mdb;Persist Security Info=False"
$alias = db.RunSQL($db,"Select * from EMP")
MsgBox($alias)
//$alias, at this point, forms a group variable for EMP and returns 1 
$alias = db.RunSQL($db,"Select * from PAYROLL")
MsgBox($alias)
//$alias, at this point, forms a group variable for PAYROLL and returns 1 (EMP is closed and the variables will be blank)
............
............

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=Student.mdb;Persist Security Info=False"
	$sql = "INSERT INTO Mark(Rollno, Name, Mark) VALUES (105,'Raj',89)"
	
	$num = db.RunSQL($Provider,$sql)
	MsgBox(concat($num," Records added"))
	
	hangup
	goto MAIN
	
:ONSYSTEMERROR
	hangup
	MsgBox($error)
	log $error
	goto MAIN

In the script, an ADO connection is established with the database "Student.mdb". Using RunSQL(), one record is inserted in to the table "Mark", which returns 1 into $num.