DB SELECT action result

I am working on a IVR script, capable of writing into database and reading from it. As a first step I went over IVR tutorial (section 9 in particular). I created a sample MS SQL Server (Express) database, created a database user, gave him all permissions, downloaded jdbc driver (sqljdbc.jar). I was using ivr_database_integration_sample script as an example. Section 9.6 of the tutorial states that there is no need to write a Java program just for database access. And I would prefer not to use java program if possible.

I updated check_password element and added new database action UpdateLoginTime to the Protected Info element as it is de-pictured in the tutorial. If I manually set the ‘passed’ variable to ‘true’, the update statement works and I can see new/updated records in Last_Login field of the AP table. However I can not get the SELECT statement in ‘Check Password’ element to return a password value. I tried to assign a value to a variable as: passed=check_password.result, Where check_password is a database action within ‘Check Password’ element.
I included a couple of screenshoots to illustrate what I am trying to accomplish. I also included log files as it is suggested on your web site.

If SELECT statement would return a recordset is there a way to loop through the recordset and check for some conditions during each iteration?

In general, the first place to check is the log files. Each db action should be listed:

Exec form item: check_password
17:52:49.393 Db jarfile: C:\Program Files\Voicent\IVRStudio\samples\sqljdbc.jar
17:52:49.393 Db driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
17:52:49.440 Db get connection: jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=test_DB1;
17:52:49.440 Db user: IVR / aXZy
17:52:49.518 Db execute SQL: SELECT Account_Password FROM AccountPassword WHERE Account_ID = '999'
17:52:49.565 Rows returned: 1

...
Exec form item: UpdateLoginTime
17:52:49.628 Db jarfile: C:\Program Files\Voicent\IVRStudio\samples\sqljdbc.jar
17:52:49.628 Db driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
17:52:49.643 Db get connection: jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=test_DB1;
17:52:49.643 Db user: IVR / aXZy
17:52:49.659 Db execute SQL: UPDATE AccountPassword SET Last_Login = GETDATE() WHERE Account_ID = '999'
17:52:49.659 Total updated: 1


Looks like your SQL statements are executed correctly.

The problem is the return variable: check_password.result

The default “result” is used for actions that returns primitive types, such as boolean. For example, you can use that for a boolean return from a Java action. For db action, the select returns multiple name value pairs. So you need to access it using the dot notation check_password.Account_Password. Since a SELECT statement may return multiple rows, you also need to specify the row number (starting from 1) in the dot notation, like check_password.Account_Password.1.

For DB SELECT action, the total number of rows are returned in the result variable .rows, like check_password.rows