I can't retrieve more than 100 rows...

[PhilRobert]

I've changed the value of RecordsetGetRowsMax in the VG.INI file but I still can't retrieve more than 99 rows... I have a database of 200+ rows, and I would have to retrieve all rows... If I execute a query like "SELECT TOP 99 ... FROM ...", it works and VG returns 99 rows... If i write a simple query without a row number restriction ("SELECT ... FROM ..."), the row count is -1 and no data is retrieved.

 

I'm working with version 5.1.8... Is there anything else I need to do to be able to do this? RecordsetGetRowsMax is set to 1000...

[SupportTeam]

RecordsetGetRowsMax changes were only included in v5.2.1

 

Please install v5.2.1 (current download available from our WWW) and then you will be able to work with unlimited number of rows.

 

Note that when you uninstall previous version of VG the entire directory tree will be deleted so you may want to backup any settings/config files first...

 

(why do you need to retrieve and use more then 100 rows in VG? I take it that you are not reading back more then 100 items of data back to the caller...)

[PhilRobert]

I installed version 5.2.1 and I'm still unable to retrieve more than 100 rows... As I recall, to work with unlimited number of rows, I had to put RecordsetGetRowsMax=0 in VG.INI file, which I did. But I'm still unable to get query results... I also tried set RecordsetGetRowsMax to 1000 but it did not work neither. Any idea?

 

I'm working with a dBase 5.0 file and MDAC 2.8...

 

The idea of retrieving more than 100 rows was the following: there's 2 types of possible users calling on the system, supervisors and employees. If you are a supervisor, than you can record a message to a certain amount of people; they can choose to send a message to a specific employee, to all employees under his supervision or to all employees. There's 200+ employees in the dBase file. To assign a message to all those people, I have an SQL table which contains only 2 columns : an employee PIN and a path to a sound file. When the supervisor records a message and decides to send it to all employees, a file.wav is created on the disk, and one record per employee is added to the SQL table. So when the supevisor select "all employees" as the message destination, I have to retrieve all rows from the dBase file, read rows one by one and append a new record to the SQL table...

 

so it does something like this:

 

[Querying database]

[read first row]

[row_number=1]

 

[append record to SQL]

[row_number=row_number+1]

if row_number<=query_RowCount then

GOTO [append record to SQL]

endif

 

That is why I have to retrieve more than 100 rows, even possibly more than 200 (new employees can be added anytime...) If you see any other way to do this plz let me know

[SupportTeam]

Could you please post a copy of VoiceGuide's Debug Printout which captures the retrieve problem, this will allow us to see what is going wrong.

(When running the script click on VoiceGuide's View menu and select 'Event Trace Log' option - any log information will then appear in this window. You must open the trace window before making the call.)

[PhilRobert]

I wrote a simple script with only 3 modules to reproduce the error. I attached it to this post :

 

Here's the debug printout (with RecordsetGetRowsMax set to 0) :

 

090401,03 9 linedevstate 2048 0 0

090401,03 9 callstate OFFERING 66218 0 4

090401,03 9 Answer the call at 2004-03-08 09:04:01

090401,05 9 lineAnswer(66218) => 66201

090401,05 9 callinfo CALLEDID

090401,05 9 callinfo ORIGIN

090401,05 9 ring 0

090401,59 9 callstate CONNECTED 66218,1,0

090401,59 9 WorkingModeTAPI@Connected=

090401,59 9 WorkingModeScript@Connected=

090401,63 9 Inband detection not enabled

090401,63 9 StartLoadedVgs at 2004-03-08 09:04:01

090401,63 9 rv add [$RV_STARTTIME]{2004-03-08 09:04:01}

090401,63 9 rv add [$RV_DEVICEID]{9}

090401,63 9 rv add [$RV_CIDNAME]{}

090401,63 9 AddRVns [PathApp]{C:\Program Files\VoiceGuide\}

090401,63 9 rv add [$RV_CIDNUMBER]{}

090401,63 9 tapi Reply (LineEvReply) ok 66201 0

090401,63 9 TimeoutClear

090401,63 9 [Query_DB] DB Query

090401,64 9 callinfo MONITORMODES

090401,64 9 db ODBC mode used

090401,64 9 db About to execute a Retrieve type operation [sELECT REP.CODE FROM REP ORDER BY REP.CODE]

090401,64 9 db DBEngine.Error 3021:Aucun enregistrement en cours.:DAO.Recordset

090401,66 9 db [Query_DB] row count=-1

090401,66 9 rv add [Query_DB_RowCount]{-1}

090401,66 9 TimeoutClear

090401,66 9 rv add [Hangup Time]{2004-03-08 09:04:01}

090401,66 9 Hanging up call... [Hangup Module]

090401,66 9 RecSoundStop ok

090401,66 9 PlaySoundStop err=0

090401,66 9 TimeoutSet 2 EV_TIMEOUT_WAITFORIDLEAFTERLINEDROP

090401,67 9 fnHangupCall end

090403,63 9 linedevstate 2048 0 0

090403,63 9 callstate IDLE 66218 0 0

090403,63 9 WorkingMode@Idle=

090403,63 9 TimeoutClear

090403,63 9 TimeoutSet 1 EV_TIMEOUT_TIMETOREINITLINE

090403,63 9 tapi Reply (LineEvReply) ok 66184 0

090404,69 9 Timer fired EV_TIMEOUT_TIMETOREINITLINE

090404,69 9 ScriptEventCode 9008 iLineState=900

090404,69 9 LsAwaitingCalls EV_TIMEOUT_TIMETOREINITLINE

090404,69 9 ReinitTelephony due to IDLE

090404,69 9 tapic lineDeallocateCall(MainCall:66218) 0

090404,84 9 lineOpen(9)=>

090404,84 9 Waiting for a call...

090404,84 9 LineHandle=66167

090404,84 9 TimeoutSet 3 EV_TIMEOUT_ATERIDLE_ALLOWOUT

090408,13 9 Timer fired EV_TIMEOUT_ATERIDLE_ALLOWOUT

090408,13 9 ScriptEventCode 9013 iLineState=900

090427,36 0 sys cleanup Start

090427,36 0 sys cleanup End

[PhilRobert]

And here's the script and database...

Script_and_DB.zip

[SupportTeam]

The trace shows that no rows at all were retrieved...

 

Can you plz try again, setting RecordsetGetRowsMax to a high value (eg: 1000 or similar).

 

BTW: you can disregard the "db DBEngine.Error 3021:Aucun enregistrement en cours.:DAO.Recordset" message - this message is incorrectly reported and right now it's confusing the trace...

[PhilRobert]

OK I did it and here's the log... It seems to be pretty similar to the log when RecorsetGetRowsMax was set to 0...

 

 

 

102209,75 0 callstate OFFERING 66406 0 4

102209,75 0 Answer the call at 12/03/2004 10:22:09

102209,77 0 lineAnswer(66406) => 66389

102209,77 0 ring 1

102209,77 0 callstate ACCEPTED 66406 0 0

102216,86 0 tapi Reply (LineEvReply) ok 66389 0

102216,86 0 callstate CONNECTED 66406,0,0

102216,86 0 WorkingModeTAPI@Connected=

102216,86 0 WorkingModeScript@Connected=

102216,86 0 Inband detection not enabled

102216,88 0 StartLoadedVgs at 12/03/2004 10:22:16

102216,88 0 rv add [$RV_STARTTIME]{12/03/2004 10:22:16}

102216,88 0 rv add [$RV_DEVICEID]{0}

102216,88 0 rv add [$RV_CIDNAME]{}

102216,88 0 AddRVns [PathApp]{C:\Program Files\VoiceGuide\}

102216,89 0 rv add [$RV_CIDNUMBER]{}

102216,89 0 TimeoutClear

102216,89 0 [Query_DB] DB Query

102216,89 0 db ODBC mode used

102216,94 0 db About to execute a Retrieve type operation [sELECT REP.CODE FROM REP ORDER BY REP.CODE]

102216,95 0 db DBEngine.Error 3021:Aucun enregistrement en cours.:DAO.Recordset

102216,95 0 db [Query_DB] row count=-1

102216,95 0 rv add [Query_DB_RowCount]{-1}

102216,97 0 TimeoutClear

102216,98 0 rv add [Hangup Time]{12/03/2004 10:22:16}

102216,98 0 Hanging up call... [Hangup Module]

102217,00 0 RecSoundStop ok

102217,00 0 PlaySoundStop err=0

102217,00 0 TimeoutSet 2 EV_TIMEOUT_WAITFORIDLEAFTERLINEDROP

102217,02 0 fnHangupCall end

102218,67 0 callstate DISCONNECTED 66406,1,0

102218,67 0 ScriptEventCode 9250 iLineState=900

102218,67 0 LsAwaitingCalls EV_REMOTEPARTY_DISCONNECT

102218,67 0 rv add [Hangup Time]{12/03/2004 10:22:18}

102218,67 0 Hanging up call...

102218,67 0 RecSoundStop ok

102218,69 0 PlaySoundStop err=0

102218,69 0 TimeoutSet 2 EV_TIMEOUT_WAITFORIDLEAFTERLINEDROP

102218,69 0 fnHangupCall end

102218,69 0 callstate IDLE 66406 0 0

102218,69 0 WorkingMode@Idle=

102218,69 0 TimeoutClear

102218,73 0 TimeoutSet 1 EV_TIMEOUT_TIMETOREINITLINE

102218,73 0 tapi Reply (LineEvReply) ok 66372 0

102218,73 0 tapi Reply (LineEvReply) ok 66355 0

102219,77 0 Timer fired EV_TIMEOUT_TIMETOREINITLINE

102219,77 0 ScriptEventCode 9008 iLineState=900

102219,77 0 LsAwaitingCalls EV_TIMEOUT_TIMETOREINITLINE

102219,77 0 ReinitTelephony due to IDLE

102219,77 0 tapic lineDeallocateCall(MainCall:66406) 0

102219,78 0 lineOpen(0)=>

102219,78 0 Waiting for a call...

102219,78 0 LineHandle=66338

102219,78 0 TimeoutSet 3 EV_TIMEOUT_ATERIDLE_ALLOWOUT

102223,06 0 Timer fired EV_TIMEOUT_ATERIDLE_ALLOWOUT

102223,06 0 ScriptEventCode 9013 iLineState=900

102236,73 0 sys cleanup Start

102236,73 0 sys cleanup End

[SupportTeam]

In both traces there wasn't any limiting by VG of the number of rows retrieved from the database - no rows at all are returned from the database in the first place...

 

Have you double checked the database itself, the ODBC source and connect string used?

 

You may want to update your v5.2.1 installation with latest path of the vgmulti.exe - current versions have more tracing around the DB Query module - current latest patch is at end of thread: http://voiceguide.com/forums/index.php?showtopic=1258

[PhilRobert]

I tested my script with your new vgmulti.exe... Sadly, it didn't change much. =(

 

I made another test to check out ODBC source and database... I ran 2 queries almost identical. In the second one, the query limits the results to 99 rows (with a SELECT TOP 99 ... FROM ...). The first one, returned (alas) -1 row, but the second one returned effectively 99 rows (as you can see below), so ODBC source and database are above suspicion...

 

Here are the 2 logs:

 

Without rows limitation :

 

165138,42 8 linedevstate 2048 0 0

165138,44 8 callstate OFFERING 66032 0 4

165138,44 8 Answer the call at 2004-03-07 16:51:38

165138,44 8 lineAnswer(66032) => 66066

165138,45 8 callinfo CALLEDID

165138,45 8 callinfo ORIGIN

165138,45 8 ring 0

165138,98 8 callstate CONNECTED 66032,1,0

165138,98 8 WorkingModeTAPI@Connected=

165138,98 8 WorkingModeScript@Connected=

165139,03 8 Inband detection not enabled

165139,03 8 StartLoadedVgs at 2004-03-07 16:51:39

165139,03 8 rv add [$RV_STARTTIME]{2004-03-07 16:51:39}

165139,03 8 rv add [$RV_DEVICEID]{8}

165139,03 8 rv add [$RV_CIDNAME]{}

165139,03 8 AddRVns [PathApp]{C:\Program Files\VoiceGuide\}

165139,03 8 rv add [$RV_CIDNUMBER]{}

165139,03 8 tapi Reply (LineEvReply) ok 66066 0

165139,03 8 callinfo MONITORMODES

165139,03 8 TimeoutClear

165139,03 8 [Query_DB] DB Query

165139,05 8 db ODBC mode used

165139,06 8 db About to execute a Retrieve type operation [sELECT REP.CODE FROM REP ORDER BY REP.CODE]

165139,09 8 db [Query_DB] row count=-1

165139,09 8 rv add [Query_DB_RowCount]{-1}

165139,09 8 TimeoutClear

165139,11 8 rv add [Hangup Time]{2004-03-07 16:51:39}

165139,11 8 Hanging up call... [Hangup Module]

165139,11 8 RecSoundStop ok

165139,13 8 PlaySoundStop err=0

165139,13 8 TimeoutSet 2 EV_TIMEOUT_WAITFORIDLEAFTERLINEDROP

165139,13 8 fnHangupCall end

165141,02 8 linedevstate 2048 0 0

165141,02 8 callstate IDLE 66032 0 0

165141,02 8 WorkingMode@Idle=

165141,02 8 TimeoutClear

165141,05 8 TimeoutSet 1 EV_TIMEOUT_TIMETOREINITLINE

165141,05 8 tapi Reply (LineEvReply) ok 66083 0

165142,09 8 Timer fired EV_TIMEOUT_TIMETOREINITLINE

165142,09 8 ScriptEventCode 9008 iLineState=900

165142,09 8 LsAwaitingCalls EV_TIMEOUT_TIMETOREINITLINE

165142,09 8 ReinitTelephony due to IDLE

165142,09 8 tapic lineDeallocateCall(MainCall:66032) 0

165142,25 8 lineOpen(8)=>

165142,25 8 Waiting for a call...

165142,25 8 LineHandle=65929

165142,25 8 TimeoutSet 3 EV_TIMEOUT_ATERIDLE_ALLOWOUT

165145,53 8 Timer fired EV_TIMEOUT_ATERIDLE_ALLOWOUT

165145,53 8 ScriptEventCode 9013 iLineState=900

165209,44 0 sys cleanup Start

165209,44 0 sys cleanup End

 

 

With rows limitation:

 

165550,97 8 linedevstate 2048 0 0

165550,97 8 callstate OFFERING 66100 0 4

165550,97 8 Answer the call at 2004-03-07 16:55:50

165550,98 8 lineAnswer(66100) => 66117

165551,00 8 callinfo CALLEDID

165551,00 8 callinfo ORIGIN

165551,00 8 ring 0

165551,53 8 callstate CONNECTED 66100,1,0

165551,53 8 WorkingModeTAPI@Connected=

165551,53 8 WorkingModeScript@Connected=

165551,58 8 Inband detection not enabled

165551,58 8 StartLoadedVgs at 2004-03-07 16:55:51

165551,58 8 rv add [$RV_STARTTIME]{2004-03-07 16:55:51}

165551,58 8 rv add [$RV_DEVICEID]{8}

165551,58 8 rv add [$RV_CIDNAME]{}

165551,58 8 AddRVns [PathApp]{C:\Program Files\VoiceGuide\}

165551,58 8 rv add [$RV_CIDNUMBER]{}

165551,58 8 tapi Reply (LineEvReply) ok 66117 0

165551,58 8 TimeoutClear

165551,58 8 [Query_DB] DB Query

165551,58 8 callinfo MONITORMODES

165551,58 8 db ODBC mode used

165551,58 8 db About to execute a Retrieve type operation [sELECT TOP 99 REP.CODE FROM REP ORDER BY REP.CODE]

165551,59 8 db [Query_DB] row count=99

165551,59 8 rv add [Query_DB_RowCount]{99}

165551,59 8 db get RecordsetGetRowsMax

165551,59 8 db set retrieve count to 99 as RecordsetGetRowsMax<=0

165551,59 8 db get 99 records. (row count=99)

165551,59 8 db update RVs

165551,59 8 db about to close dbDbqDatabase

165551,59 8 db going down path [True]

165551,59 8 TimeoutClear

165551,59 8 RVreplace start: [$RV[Query_DB_RowCount]]

165551,59 8 RVreplace end: [99]

165551,61 8 [Play_RowCount] Say numbers: 99 as Number

165551,61 8 fn PlaySoundStartNumbers , , 99, Number

165551,61 8 sVbsFunctionToUse=Number

165551,61 8 Started SayNumber VBScript OK. (C:\Program Files\VoiceGuide\data\vbs_8_SayNumber.vbs)

165551,63 8 cl Play_Start 90.wav,9.wav

165551,70 8 PlaySoundStart ok [C:\Program Files\VoiceGuide\system\voice\90.wav,C:\Program Files\VoiceGuide\system\voice\9.wav]

165551,70 8 TimeoutClear

165551,70 8 wa(1787,5153101)

165551,70 8 callinfo MONITORMODES

165553,50 8 wb(5153101)

165553,56 8 Play End line[8] (id=51531)

165553,56 8 ScriptEventCode 8001 iLineState=1600

165553,56 8 LsSayNbrPlay EV_PLAY_FINISHED

165553,56 8 TimeoutSet 10 EV_TIMEOUT_HANGUP

165553,56 8 TimeoutSet 10 EV_TIMEOUT_HANGUP

165604,39 8 Timer fired EV_TIMEOUT_HANGUP

165604,39 8 ScriptEventCode 9001 iLineState=1600

165604,39 8 LsSayNbrPlay EV_TIMEOUT_HANGUP

165604,39 8 rv add [Hangup Time]{2004-03-07 16:56:04}

165604,39 8 Hanging up call...

165604,39 8 RecSoundStop ok

165604,39 8 PlaySoundStop err=0

165604,39 8 TimeoutSet 2 EV_TIMEOUT_WAITFORIDLEAFTERLINEDROP

165604,39 8 fnHangupCall end

165604,42 8 linedevstate 2048 0 0

165604,42 8 callstate IDLE 66100 0 0

165604,42 8 WorkingMode@Idle=

165604,42 8 TimeoutClear

165604,42 8 TimeoutSet 1 EV_TIMEOUT_TIMETOREINITLINE

165604,44 8 tapi Reply (LineEvReply) ok 65571 0

165605,48 8 Timer fired EV_TIMEOUT_TIMETOREINITLINE

165605,48 8 ScriptEventCode 9008 iLineState=900

165605,48 8 LsAwaitingCalls EV_TIMEOUT_TIMETOREINITLINE

165605,48 8 ReinitTelephony due to IDLE

165605,48 8 tapic lineDeallocateCall(MainCall:66100) 0

165605,64 8 lineOpen(8)=>

165605,64 8 Waiting for a call...

165605,64 8 LineHandle=65554

165605,64 8 TimeoutSet 3 EV_TIMEOUT_ATERIDLE_ALLOWOUT

165608,92 8 Timer fired EV_TIMEOUT_ATERIDLE_ALLOWOUT

165608,92 8 ScriptEventCode 9013 iLineState=900

165614,83 0 sys cleanup Start

165614,83 0 sys cleanup End

[SupportTeam]

... so have you tried setting the SQL query to do a "SELECT TOP 10000 ... FROM ..." ? (or some other very high number...)

 

so ODBC source and database are above suspicion...

... I wouldn't say that... after all it was the SQL query sent to the Database that needed to be changed here to have the Database return a result...

 

 

 

Fuller explanation:

 

in log trace the line:

 

165551,59 8 db set retrieve count to 99 as RecordsetGetRowsMax<=0

 

confirms that VG will read in everything returned by the database as the RecordsetGetRowsMax setting is set to 0 now.

VG then looks at how many rows were returned by the DB (in SQL statement you limited it to 99) and then reads them all in. So looks like all is working just the way it should be on VG's side...

 

 

What is puzzling is why issuing to your DB the SQL command:

 

SELECT REP.CODE FROM REP ORDER BY REP.CODE

 

results in DB not returning any data, yet when you issuing to your DB the SQL command:

 

SELECT TOP 99 REP.CODE FROM REP ORDER BY REP.CODE

 

then data is returned. I'd have a good look at your database manual next...

 

 

BTW: The function which VG calls to run the SQL command on database via ODBC completes without any execution errors from VG's point on view in both cases - otherwise an error message would have been printed in log and we would never get to the "[module] row count=" log message...

[PhilRobert]

Yes I've tried running a query like "SELECT TOP 10000..." and it didn't return any rows...

 

I tested the database with Microsoft Query and it returned all rows, regardless of the query type (limited or not)...

[SupportTeam]

Here is a trace of a simple query to an MS Access database where VoiceGuide asks for all the rows in the database. As you can see 147 rows are returned (that's how many are in that table) and VG reads in all the data. RecordsetGetRowsMax was set to 0 in VG.INI so all returned rows were read in without any limitation.

113946.70  1 [Access Query_2] DB Query

113946.70  1 db    Jet mode used (To use ODBC mode specify ODBC Connect string)

113946.72  1 db    About to execute a Retrieve type operation [sELECT ProductName, Desc FROM Goods]

113946.73  1 db    [Access Query_2] row count=147

113946.73  1 rv    add [Access Query_2_RowCount]{147}

113946.73  1 db    set records to retrieve to 147 as RecordsetGetRowsMax<=0

113946.73  1 db    get 147 records. (row count=147)

113946.84  1 db    rv update

113946.84  1 db    go down path [success]

 

Not too sure why this is not working for your dBase/ODBC setup.

 

Once you set RecordsetGetRowsMax=0 in VG.INI then VoiceGuide itself sets no limits on how many rows it will read into it's result variables...

 

It's pretty strange that both

 

SELECT TOP 10000 REP.CODE FROM REP ORDER BY REP.CODE

 

SELECT REP.CODE FROM REP ORDER BY REP.CODE

 

did not return any rows... VG after all just passes SQL query on to the ODBC driver and then awaits a response...

 

Since SELECT TOP 99 REP.CODE FROM REP ORDER BY REP.CODE works have you tried to see at what "TOP XXXX" setting your ODBC queries to dBase stop working? (be good to see whether "TOP 101" works..)

 

Have you just for a test tried to use MS Access or other databases to see if you can retrieve more then 99 rows from other databases?

As a first step I'd try MS Access and see if the results you get are similar to what the quoted trace shows at the beginning of this post.

 

Also, where did you obtain Microsoft Query tool from? Is it downloadable from Microsoft? Does this tool access your database via the same ODBC source?

[PhilRobert]

have you tried to see at what "TOP XXXX" setting your ODBC queries to dBase stop working? (be good to see whether "TOP 101" works..)

Yes I've tried querying the TOP 100 records and it didn't work: 99 really is the limit

 

Have you just for a test tried to use MS Access or other databases to see if you can retrieve more then 99 rows from other databases?

I tried with MS Access and I could return more than 100 rows... So I tried to attach my dBase files to an Access 2000 database and guess what, it works! Now I can retrieve any number of rows I need = ) Thx to you guys

 

where did you obtain Microsoft Query tool from?

This tool is packed with Microsoft Office 2000 (and probably higher versions), tough I'm not sure really with which part exactely of Office. If you have Microsoft Office installed, you should look for a file named MSQRY32.EXE on you hard drive. This is a pretty useful tool which uses the ODBC data sources defined on your CPU to access databases... Pretty handy when it comes to testing data sources...

 

I'd like to say THANKS TO YOU ALL AT THE SUPPORT TEAM for helping me through this one. Now I can get back to my app = )

[SupportTeam]

Had a look at MS Query (v10.0.2607.0 installed in C:\Program Files\Microsoft Office\Office10).

 

MS Query can connect directly to the database - so it does not have to use the ODBC driver at all.

 

Did you connect to the database directly or did you connect via the same ODBC source which VoiceGuide was set up to use?

[PhilRobert]

Via the same ODBC source that I use in the script...

[SupportTeam]

We'll try to replicate your setup here.

 

Can you please tell us where did you get the ODBC drivers that you were using from?

 

Is it possible for you to email us the database file itself? (email to support@katalinatech.com)