Query Results Limited To 100 Rows

[PhilRobert]

Hi, I've read on this forum that query results are limited to a 100 rows... In my app, I have a list of 200 client codes that I have to retrieve in order to append 200 new records in another table, 1 record per client... I read that this could be done with a vb script, but I'm no vb expert so I would appreciate if I could get an example of how connecting and querying a database using vb scripts. Thanks to all.

[SupportTeam]

The default row retrieval limit is set to 100 as it was thought unlikely that any application would want to read out more then 100 retrieved entries to the caller, or manipulate more rows then that within VoiceGuide, and some limit had to be set on the number of retrieved rows to protect against huge amounts of data being retrieved unintentionally...

 

It's all configurable in VG.INI file anyway - so you can change it to whatever value you need. See section [moduleDBQuery] of VG.INI file (needs v5.1.8 and higher).

 

If you need to do database specific work then you should use the right tools for the job - maybe have a look at setting up a stored procedure within your database or using Microsoft's ADO from within VBScript - I'd recommend buying some books on the subject.

 

Some examples of ADO programming have been posted on forum before, plz see:

 

http://voiceguide.com/forums/index.php?showtopic=19

http://voiceguide.com/forums/index.php?showtopic=619

http://voiceguide.com/forums/index.php?showtopic=819

http://voiceguide.com/forums/index.php?showtopic=959

 

also I'd recommend searching for:

 

CreateObject("ADODB.Connection")

 

on Google Groups ( www.google.com/groups ) and on msdn.microsoft.com/scripting

 

 

 

PS: What approach were you going to use in VoiceGuide after increasing the row retrieval limit to 200? Sounds like you would need to use a loop which goes through a DB Query module 200 times as you can only do one row insert operation per SQL statement... - the need to do 200 SQL commands to do 200 inserts of data into the database will be needed regardless of what programming language you use...

 

Maybe you should look again at your database design in general - it sounds like what you are doing is a lot of work to do for every caller...

[SupportTeam]

In the currently available v5.2.1 download the default entry RecordsetGetRowsMax (in VG.INI section [moduleDBQuery]) was set to 0, which means that VG will not limit the number of rows read in.

 

If users would like to enable the "max rows" protection then they can change this entry to a setting of their choice.

 

from the current VG.INI file:

[moduleDBQuery]

;Maximum number of rows which VG will enumerate and Result Variables, making them

;available to other parts of the script. 100 is usually enough - few applications

;woudl require to play back more then 100 items of information back to the caller.

;Set to 0 if you do not want VG to limit number of returned rows.

RecordsetGetRowsMax=0

[PhilRobert]

Thanks for the help, I managed to do it using a "Select TOP 99 field FROM TABLE WHERE field>$RV[Last_field]" where Last_field was the last value returned by the query if the query had been run before... It worked but your solution is WAY easier = ) Thanks again!