Accessing A Dbase File From Query Module

[PhilRobert]

Hi to all,

 

I'm a newbie using VG and i'm trying to access a dBase file but i'm unable to retrieve rows... I'm using an ODBC data source which works well when i'm using it with Microsoft Query, and my connect string looks like this:

 

ODBC;DRIVER={Microsoft dBase Driver (*.dbf)};UID=;PWD=;

 

The script i'm using for testing purposes is quite simple: when the program answers the call, it immediately tries to query the database, then it plays a .Wav if the Query module returned true and another if it returned false. Here's the event trace log:

 

103413,73 0 callstate OFFERING 65945 0 4

103413,73 0 ring 1

103419,94 0 ring 2

103419,94 0 Answer the call at 23/02/2004 10:34:19

103419,94 0 lineAnswer(65945) => 65928

103419,94 0 callstate ACCEPTED 65945 0 0

103422,73 0 tapi Reply (LineEvReply) ok 65928 0

103422,73 0 callstate CONNECTED 65945,0,0

103422,73 0 WorkingModeTAPI@Connected=

103422,73 0 WorkingModeScript@Connected=

103422,73 0 Inband detection not enabled

103422,73 0 StartLoadedVgs at 23/02/2004 10:34:22

103422,73 0 [Database Query] DB Query

103422,73 0 ODBC mode used

103422,75 0 About to execute Retrieve Operation [select NO from EMPLOYE]

103422,77 0 [Database Query] row count=-1

103422,77 0 [Login Not OK] Playing

103422,77 0 [Login Not OK] Playing (C:\Documents and Settings\phirob.CIS-GROUP\Bureau\VoiceGuide\PCM\Code_invalide.wav)

103422,94 0 PlaySoundStart ok [C:\Documents and Settings\phirob.CIS-GROUP\Bureau\VoiceGuide\PCM\Code_invalide.wav]

103422,94 0 RunModule PLAY end

103426,16 0 Play End line[0] (id=672328)

103426,16 0 LsPlayMsg EV_PLAY_FINISHED

103436,98 0 LsPlayMsgFinished EV_TIMEOUT_HANGUP

103436,98 0 Hanging up call...

103436,98 0 RecSoundStop ok

103436,98 0 PlaySoundStop err=0

103436,98 0 fnHangupCall end

103438,64 0 callstate DISCONNECTED 65945,1,0

103438,64 0 LsAwaitingCalls EV_REMOTEPARTY_DISCONNECT

103438,64 0 Hanging up call...

103438,64 0 RecSoundStop ok

103438,64 0 PlaySoundStop err=0

103438,64 0 fnHangupCall end

103438,64 0 callstate IDLE 65945 0 0

103438,64 0 WorkingMode@Idle=

103438,64 0 tapi Reply (LineEvReply) ok 65911 0

103438,64 0 tapi Reply (LineEvReply) ok 65894 0

103439,16 0 sys cleanup Start

103439,16 0 sys cleanup End

103439,72 0 LsAwaitingCalls EV_TIMEOUT_TIMETOREINITLINE

103439,72 0 ReinitTelephony due to IDLE

103439,72 0 tapic lineDeallocateCall(MainCall:65945) 0

103439,72 0 lineOpen(0) => 0

103439,72 0 Waiting for a call...

103439,73 0 LineHandle=65877

 

Now I know there are some records in the database but the RowCount result is always -1... Any idea what's wrong?

[Guest jpl]

Um, i may be completely off base here, but i'm guessing the problem is that you have no CONDITION defined in your statement: you have:

 

---About to execute Retrieve Operation [select NO from EMPLOYE]---

 

Try somthing like this: Select No from Employe WHERE No=$RV[YOURVARIABLEHERE]

 

Or somthing like that.

 

What exactly are you trying to do by querying the database; select an employee number via their caller id name? or via their number, or what? As it is, you still need to specifiy a CONDITION so that the database can return a VARIABLE back to VG.

[Guest jpl]

Another Thing:

 

It looks like you are querying your database to see if the Login Is correct or Invalid (as per your wave files). Perhaps you should have a user enter their pin or employee number using a Get Number Module name GetPIN. Then your statement would look like this:

 

Select No from Employe Where No=$RV[GetPIN]

 

If the data is returned; ie if the database matches the $RV[GetPIN] with No in the employe Database, then the path is TRUE; however if it does not the path is FALSE; or you can use an Expression Module to say:

 

$RV[GetPIN] = ''

 

the above would tell the system to see if the Database returned a NULL string, ie IT COULDN'T FIND THE PIN; if that is the case it could ask them to reenter the pin again. However, if it did find the pin it would have already taken the true path from your database query.

 

One more thing about PIN's; just becuase they have the PIN doens't mean they have acess to the system; on our system, we have them enter their PIN, it looks up the pin AND THE CALLER ID NUMBER from the database; if there is no caller id number, or their number is INCORRECT, it asks them to enter the PHONE NUMBER they used to register; once both peices of information are correct, it then proceeds down the TRUE path to the rest of our script.

 

Just remember, YOU SHOULD ALWAYS put a condition when doning a query.

 

Sorry for the tangent; i just love this program! lol

[PhilRobert]

As you have noticed, the main idea was effectively to ask for a PIN (and a password) then give access to the main menu, which i tried first (with a query like : select NO from EMPLOYE where NO=$RV[GetPIN]). But it did not work correctly so I removed everything that I did not need to test the database connectivity, including the GetPIN and GetPassword modules and the condition of the query... If I could just get a number of rows returned, than I would know the connectivity is OK and try to concentrate more on the query... I downloaded the last version of MDAC, and it did not fix the problem... Any clue?

[PhilRobert]

I finally found the reason why it didn't work... It seems like the syntax "Select No from Employe where no=1000" caused the problem... I tried with "Select Employe.No from Employe where Employe.No=1000" and it worked! Thanks to all who took time to read and answer my post, I'll try to make it up to you in the future = )