Can't Get More Than 100 Results

[Guest KB]

Whenever i am using a query which returns more than a 100 results it fails and on the check log it appears:

113236.40 5 db [GetResults] row count=-1

as if no rows were returned.

I've tried to change the row limit on the vg.ini file to both 9999 and 0 and it still doesn't work.

When i am using the same query in Microsoft Query using the same DB and the same ODBC it works. I am using Progress DB.

 

Any idea?

 

Thanks.

[SupportTeam]

Which version of VG are you using?

 

In current version the VG.INI entry RecordsetGetRowsMax in section [moduleDBQuery] is set to limit the number of returned rows. If set to -1 then no limit is applied.

 

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

 

BTW:

It's easy to modify an SQL statement to only ask the database to return a certain number of rows using the TOP command - use the TOP statement to see if it is in fact the number of returned rows that is causing the problem here...

[Guest KB]

The version i am using is 5.2.3063.

 

I've set it to -1 but it still fails and sadly Progress SQL doesn't support the TOP command.

Here's the printout:

100835.57 5 linedevstate 2048 0 0

100835.59 5 callstate OFFERING 66446 0 4

100835.59 5 rv clear

100835.59 5 Answer the call at 23/03/2005 10:08:35

100835.61 5 lineAnswer(66446) => 66020

100835.61 5 callinfo CALLEDID

100835.61 5 callinfo ORIGIN

100835.61 5 ring 0

100835.75 0 dial start any summary:|5:hc>0|6:idx=0|7:idx=0|8:idx=0|

100836.15 5 callstate CONNECTED 66446 1 0

100836.15 5 callstate CONNECTED 66446,1,0

100836.15 5 WorkingModeTAPI@Connected=

100836.15 5 WorkingModeScript@Connected=

100836.17 5 Inband detection not enabled

100836.17 5 StartLoadedVgs at 23/03/2005 10:08:36, v5.2.3063

100836.17 5 AddRVns [scriptsPath]{C:\Program Files\VoiceGuide\Scripts\BonhamsIVR\}

100836.17 5 AddRVns [scriptPath]{C:\Program Files\VoiceGuide\Scripts\BonhamsIVR}

100836.18 5 rv add [$RV_STARTTIME]{23/03/2005 10:08:36}

100836.18 5 rv add [$RV_DEVICEID]{5}

100836.18 5 rv add [$RV_CIDNAME]{}

100836.18 5 AddRVns [PathApp]{C:\Program Files\VoiceGuide\}

100836.20 5 rv add [$RV_CIDNUMBER]{}

100836.20 5 tapi Reply (LineEvReply) ok 66020 0

100836.20 5 timer clear

100836.21 5 [Welcome] Playing

100836.21 5 [Welcome] Playing (C:\Documents and Settings\lahmyk\Desktop\SaleResults.wav)

100836.21 5 play set playid=248843

100836.28 5 PlaySoundStart ok [C:\Documents and Settings\lahmyk\Desktop\SaleResults.wav]

100836.28 5 timer clear

100836.28 5 RunModule PLAY end

100836.29 5 wa(6508,24884300)

100836.29 5 callinfo MONITORMODES

100836.29 5 callinfo MONITORMODES

100837.70 5 dtmf 1 (66446,49,2)

100837.70 5 ScriptEventCode 49 iLineState=1100

100837.70 5 LsPlayMsg 1

100837.76 5 PlaySoundStop err=0

100837.78 5 rv add [Welcome]{1}

100837.81 5 timer clear

100837.81 5 [select Sale] Number Input

100837.81 5 [select Sale] Playing (C:\Documents and Settings\lahmyk\Desktop\SelectSale.wav)

100837.82 5 play set playid=250453

100837.86 5 PlaySoundStart ok [C:\Documents and Settings\lahmyk\Desktop\SelectSale.wav]

100837.86 5 timer clear

100837.87 5 wa(5206,25045300)

100837.87 5 callinfo MONITORMODES

100839.36 5 dtmf 1 (66446,49,2)

100839.36 5 ScriptEventCode 49 iLineState=1300

100839.36 5 LsGetNbrsPlayWelcMsg 1

100839.42 5 PlaySoundStop err=0

100839.43 5 [select Sale] Number Input 1

100839.43 5 path {1} not found

100839.43 5 timer set 6 EV_TIMEOUT_ENTERDATA

100839.65 5 dtmf 2 (66446,50,2)

100839.65 5 ScriptEventCode 50 iLineState=1301

100839.67 5 LsGetNbrsRxDigits 2

100839.67 5 [select Sale] Number Input 12

100839.67 5 path {12} not found

100839.67 5 timer set 6 EV_TIMEOUT_ENTERDATA

100839.98 5 dtmf 3 (66446,51,2)

100839.00 5 ScriptEventCode 51 iLineState=1301

100839.00 5 LsGetNbrsRxDigits 3

100839.00 5 [select Sale] Number Input 123

100839.00 5 path {123} not found

100840.01 5 timer set 6 EV_TIMEOUT_ENTERDATA

100840.32 5 dtmf 4 (66446,52,2)

100840.32 5 ScriptEventCode 52 iLineState=1301

100840.34 5 LsGetNbrsRxDigits 4

100840.34 5 [select Sale] Number Input 1234

100840.34 5 path {1234} not found

100840.34 5 timer set 6 EV_TIMEOUT_ENTERDATA

100840.65 5 dtmf 5 (66446,53,2)

100840.67 5 ScriptEventCode 53 iLineState=1301

100840.67 5 LsGetNbrsRxDigits 5

100840.67 5 [select Sale] Number Input 12345

100840.67 5 path {12345} not found

100840.68 5 timer set 6 EV_TIMEOUT_ENTERDATA

100840.00 5 dtmf # (66446,35,2)

100840.00 5 ScriptEventCode 35 iLineState=1301

100841.01 5 LsGetNbrsRxDigits #

100841.01 5 timer clear

100841.01 5 rv add [select Sale]{12345}

100841.01 5 eng run verification

100841.03 5 rv replace start: [set vg = CreateObject("VoiceGuide.CommandLink")

iLen = Len("$RV_ENTEREDNUMBER")

sFirstChar = Right("$RV_ENTEREDNUMBER",1)

if iLen> 5 then

sResult = "verify_failed"

else

sResult = "verify_passed"

end if

vg.Run_ResultReturn $RV_LINEID, sResult

set vg = Nothing

]

100841.03 5 rv ns [PathSysVoice]{}[PathApp]{C:\Program Files\VoiceGuide\}[PathDataVm]{C:\Program Files\VoiceGuide\data\}[PathVgSys]{C:\Program Files\VoiceGuide\system\}[scriptsPath]{C:\Program Files\VoiceGuide\Scripts\BonhamsIVR\}[scriptPath]{C:\Program Files\VoiceGuide\Scripts\BonhamsIVR}[$RV_STARTTIME]{23/03/2005 10:08:36}[$RV_DEVICEID]{5}[$RV_CIDNAME]{}[PathApp]{C:\Program Files\VoiceGuide\}[$RV_CIDNUMBER]{}[Welcome]{1}[select Sale]{12345}

100841.03 5 eng copy of verification script ran: vbs_5_1.vbs

100841.07 5 Run Script waiting. iRunWait=1 (shellid=972, process=600)

100841.07 5 timer set 1 EV_TIMEOUT_CHECKONSTATE

100841.12 5 cl Run_ResultReturn >>verify_passed<<

100841.12 5 ScriptEventCode 9221 iLineState=1301

100841.12 5 LsGetNbrsRxDigits EV_UNKNOWN_9221

100841.12 5 eng GetNbr EV_MODGETNBRS_VERIFY_PASS

100841.14 5 fn PlaySoundStartNumbers [C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav],[C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav],[12345],Digits

100841.14 5 sVbsFunctionToUse=Digits

100841.20 5 Started SayNumber VBScript OK. (C:\Program Files\VoiceGuide\data\vbs_5_SayNumber.vbs)

100841.25 5 cl Play_Start C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav,1.wav,2.wav,3.wav,4.wav,5.wav,C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav

100841.26 5 play set playid=253890

100841.39 5 PlaySoundStart ok [C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav,C:\Program Files\VoiceGuide\system\voice\1.wav,C:\Program Files\VoiceGuide\system\voice\2.wav,C:\Program Files\VoiceGuide\system\voice\3.wav,C:\Program Files\VoiceGuide\system\voice\4.wav,C:\Program Files\VoiceGuide\system\voice\5.wav,C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav]

100841.39 5 timer clear

100841.40 5 wa(11743,25389006)

100841.40 5 callinfo MONITORMODES

100842.86 5 dtmf 1 (66446,49,2)

100842.86 5 ScriptEventCode 49 iLineState=1302

100842.87 5 LsGetNbrsPlayConfirm 1

100842.93 5 PlaySoundStop err=0

100842.93 5 path {12345} not found

100842.96 5 timer clear

100842.96 5 [GetResults] DB Query

100842.98 5 db ODBC mode used

100843.51 5 db About to execute a Retrieve type operation [sELECT SaleLot.iSaleLotNo, SaleLot.sSaleLotNoA, SaleLot.sLotStatus, SaleLot.sLotDesc, SaleLot.dHammerPrice + SaleLot.dPremAmt AS 'dHammerAndPremium', SaleLot.dHammerPrice, SaleLot.dPremAmt FROM PUB.SaleLot SaleLot WHERE (SaleLot.iSaleNo='11626') AND (SaleLot.sLotStatus='BI' OR SaleLot.sLotStatus='SOLD') ORDER BY SaleLot.iSaleLotNo, SaleLot.sSaleLotNoA]

100843.75 5 db [GetResults] row count=-1

100843.75 5 rv add [GetResults_RowCount]{-1}

100843.76 5 timer clear

100843.76 5 [Fail] Playing

100843.78 5 [Fail] Playing (C:\Documents and Settings\lahmyk\Desktop\Fail.wav)

100843.78 5 play set playid=256406

100843.81 5 PlaySoundStart ok [C:\Documents and Settings\lahmyk\Desktop\Fail.wav]

100843.81 5 timer clear

100843.82 5 RunModule PLAY end

100843.82 5 wa(2499,25640600)

100843.84 5 callinfo MONITORMODES

100845.81 5 dtmf 1 (66446,49,2)

100845.81 5 ScriptEventCode 49 iLineState=1100

100845.82 5 LsPlayMsg 1

100845.89 5 PlaySoundStop err=0

100845.89 5 rv add [Fail]{1}

100845.90 5 timer clear

100845.90 5 rv add [Hangup Time]{23/03/2005 10:08:45}

100845.90 5 Hanging up call... [Hangup Module]

100845.92 5 RecSoundStop ok

100845.93 5 PlaySoundStop err=0

100845.93 5 timer set 2 EV_TIMEOUT_WAITFORIDLEAFTERLINEDROP

100845.95 5 fnHangupCall end

100845.98 5 linedevstate 2048 0 0

100845.00 5 callstate IDLE 66446 0 0

100845.00 5 WorkingMode@Idle=

100845.00 5 timer clear

100846.01 5 timer set 1 EV_TIMEOUT_TIMETOREINITLINE

100846.03 5 tapi Reply (LineEvReply) ok 66003 0

100846.00 5 timer fired EV_TIMEOUT_TIMETOREINITLINE

100846.00 5 ScriptEventCode 9008 iLineState=900

100846.00 5 LsAwaitingCalls EV_TIMEOUT_TIMETOREINITLINE

100846.00 5 ReinitTelephony due to IDLE

100847.01 5 tapic lineDeallocateCall(MainCall:66446) 0

100847.09 5 lineOpen(5)=>

100847.11 5 Waiting for a call...

100847.11 5 LineHandle=65899

100847.11 5 timer set 3 EV_TIMEOUT_ATERIDLE_ALLOWOUT

100847.14 0 dial start any summary:|5:rdy=0|6:idx=0|7:idx=0|8:idx=0|

100849.01 0 sys cleanup Start

100849.01 0 sys cleanup End

100850.40 5 timer fired EV_TIMEOUT_ATERIDLE_ALLOWOUT

100850.40 5 ScriptEventCode 9013 iLineState=900

100851.21 0 dial start any summary:|5:idx=0|6:idx=0|7:idx=0|8:idx=0|

 

 

As you see it seems like it returns no lines from the query but when i use a different SaleLot.iSaleNo which i know has less than 100 results it's working.

[SupportTeam]

It does look from VG's point of view as if the ODBC driver is returning the "-1" in response to VG asking it 'how many rows did you get?'...

 

Another approach would be to try the database retrieval via the Run VBScript module. There are some examples in the Help file on how to do this using ADO,.

See: http://www.voiceguide.com/vghelp/html/modVbs.htm

[Guest KB]

I've just tried this script:

 

Dim Connection

Dim ConnectionStr

Dim RS

Dim SQLStmt

 

Set Connection = CreateObject("ADOB.Connection")

ConnectionStr = "ODBC;DSN=train_priv;UID=RepServer;PWD=;"

Connection.Open(ConnectionStr)

 

SQLStmt = "SELECT Sale.daEndDate FROM PUB.Sale Sale WHERE (Sale.iSaleNo=10031)"

 

Set RS = Connection.Execute(SQLStmt)

 

msgBox(RS.Fields(0).value)

 

Connection.Close

Set Connection=Nothing

Set RS=Nothing

 

But it gives me an error:

ActiveX component can't create object= 'ADOB.Connection'

 

Do i have to install or include anything on this machine apart from the stuff you've recommended on the d/l page?

[Guest KB]

Found the error - ADOB instead of ADODB...sorry about that.