ODBC not sending SQL statement correct.

[Guest Tom Storey]

I'm working with a Jonathan Monroe at Actual Technologies, LLC tring to use their ODBC driver to connect to an sql database (OpenBase). Excell works great, but when I tried to connect with VG it failed. I sent him the SQL.Log file of the test run and this is what he said was happening:

 

"Tom,

 

According to the log, the following is getting passed from the application

to the driver:

 

SELECT * FROM SELECT First, Second FROM TestTable

 

This isn't valid SQL - there's an extra SELECT keyword. Do you have any

control over the SQL that gets generated?

 

Thanks,

 

Jonathan ".

 

I've attached the VG script and the SQL.Lof file. How do I fix this.

SQL_Info.zip

[SupportTeam]

SQL trace shows the VG is first sending the SQL as specified in the script:

VgMulti        650-644 ENTER SQLExecDirect

  HSTMT              0A9728E0

  UCHAR *            0x0AA81ECC [      -3] "SELECT First, Second  FROM TestTable\ 0"

  SDWORD                    -3

 

VgMulti        650-644 EXIT  SQLExecDirect  with return code 0 (SQL_SUCCESS)

and then the SQL shows another SQL command being issued (apparently by VG as well):

VgMulti        650-644 ENTER SQLExecDirect

  HSTMT              0A9728E0

  UCHAR *            0x0AA8249C [      -3] "SELECT * FROM SELECT First, Second  FROM TestTable\ 0"

  SDWORD                    -3

 

VgMulti        650-644 EXIT  SQLExecDirect  with return code -1 (SQL_ERROR)

 

I'm pretty sure that VG is not actually issuing the second SQL - VG does not alter the SQL specified in the DB Query module in any way.

 

You can easily verify correct operation of 'DB Query' module by running a query against any other database (eg: MS-SQL Server, Oracle, MySQL etc).

 

Could you please also post a copy of VoiceGuide's Debug Printout which captures the problem, this may allow us to see what is happening - but I'll suspect that the trace will show that the SQL query as specified in module (SELECT First, Second FROM TestTable) is issued and that it's the only query issued....

[Guest Tom Storey]

I'm not sure I know what you mean by "VoiceGuide's Debug Printout" so I've attached every log I could find in a .zip file. I don't have other SQL DB to query, all our records are held on OpenBase. I did try to use an ODBC Driver from OpenLink, a month or so back, and had the same problem. The only differance was it would use the SELECT * and bring in records, but when ever I tried to use SELECT with col names it fail or if I tried to use WHERE it failed. After reviewing the logs OpenLink said there was a problem in my application software and I just dropped the ODBC idea at that time, but now I need to get ODBC working.

Logs_9_21.zip

[SupportTeam]

VG trace log shows (as expected) that the same SQL query specified in the DB Query module is issued in a call to the database - and then we see the ODBC driver report that the call has failed.

 

092028.86 5 [Look For Data] DB Query

092028.86 5 db ODBC mode used

092034.50 5 db About to execute a Retrieve type operation [sELECT First, Second FROM TestTable]

092036.70 5 OpenRecordset: 3146:ODBC--call failed.

 

Has anyone form openBase explained why we are seeing two SQL calls in the ODBC trace log (as I quoted in previous post)?

 

Are you using OpenBase from www.openbase.com or some other program?

 

We have many systems out there using ODBC connections with no problems so maybe it's some incompatibility between Microsoft's DAO engine which VG is using and that particular ODBC driver...

 

PS.

Has the other problem you posted about before been resolved?

[Guest Guest]

I know that your log shows the SQL statement being send and then the error. What your log does not show is what was happening between the two entries, thats where the SQL.Log comes in. As you stated in one of your post: "SQL trace shows the VG is first sending the SQL as specified in the script and then the SQL shows another SQL command being issued (apparently by VG as well)".

 

As for OpenBase, it is only an SQL Database is doesn't send SQL it just receive then and sends the answer to the SQL back. As I stated in my first post the ODBC drive is from Actual Technologies, LLC. As I also stated I started working this problem with them and they were the ones that pointed out, as you did, the bad SQL is coming from the applcation. As I also stated OpenLink ODBC drive had the same problem. As you said it may be something to do with Windows, if so tell me what I should be checking for and I'll check it and tell you what I found.

 

Let not keep pointing fingers, lets just get this problem worked out, OK.

 

Yes I'm useing OpenBase from www.openbase.com.

[SupportTeam]

VoiceGuide uses the standard Windows' DAO data access API as provided by "Microsoft Data Access Components" (see: http://msdn.microsoft.com/data/default.aspx)

 

It works OK for just about every other database but it looks like in combination with this ODBC driver from Actual Technologies something is going wrong.

 

What I'd recommend trying is running a VBScript using ADO - there are examples on how to do all this here: http://www.voiceguide.com/vghelp/html/modVbs.htm

 

And you will be able to use the VBScript examples either from within VG's "Ruin VBScript" module or just by themselves by placing it in a .vbs file and just running that file outside VG (just double click on the file and Windows will run the VBScript.

[TomStorey]

Sorry for not getting back to you sooner, but we just got though our 4 hurricanes in the last 6 weeks. Our county still has over 300,00 people with out power.

 

I tried the VBScript you asked me to run, but it failed also. I sent the script to Jonathan Monroe at Actual Technologies, LLC to test and the following is his last email to me:

 

I've looked into this a little bit. It's going to take me a few days to address this. They're using ADO for their data access method, which is great for you as a programmer, but bad for me as an ODBC driver developer. ADO can be very particular about what it gets back from the driver. I just need to figure out what it doesn't like.

 

I'll let you know when I know more. Hang in there!

 

Jonathan

 

In your last post you stated “VoiceGuide uses the standard Windows' DAO data access API as provided by "Microsoft Data Access Components"”, but the test you asked me to run uses ADO, is that going to made a difference in Jonathan testing and fixing?

[SupportTeam]

DAO cannot be used from within VBScript.

 

The ADO test was an easy way to demonstrate (outside of VG) that the ODBC driver is not working correctly. Once the ODBC driver works with ADO you can just use the ADO based VBScript from within VG's "Run VBScript" module.

 

Maybe once they fix the ADO support in their driver they will fix DAO at the same time... it is not hard to set up a simple DAO test program to test their driver - but you will need to write a program to test it - please refer to DAO documentation from Microsoft.