$intNum = db.RunSQL
Establishes connection with the database and executes the query.
<connectionString> ADO connection string or ODBC DSN.
<query> Any standard SQL query.
Returns 1 if successful, else 0.
LHS variable is necessary and compulsory for the proper functioning of all database functions. Hence the following loop statement will generate $error variable:
- 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:
- Opens the database.
- Executes the query.
- Creates dynamic variables, if required.
- Closes the recordset.
- 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")
//$alias, at this point, forms a group variable for EMP and returns 1
$alias = db.RunSQL($db,"Select * from PAYROLL")
//$alias, at this point, forms a group variable for PAYROLL and returns 1 (EMP is closed and the variables will be blank)
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.
display "Waiting for call"
$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"))
- 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.