Can't Get Query To Return Data

[Guest Tom Storey]

I've attached a log file for a very simple ODBC query. The query works and brings back data, but the data will not transfer in Eval. Also if I put a WHERE in the statement it alway returns 0 lines.

0318vgm.txt

[SupportTeam]

In trace we see that the DQ Query module name is "Database Query 1" :

 

104359.75 5 [Database Query 1] DB Query

 

but the RV that you are using in in module "Evaluate 8" is "$RV[Get Database Query 1_1_1]" :

 

104359.81 5 [Evaluate 8] Evaluate [$RV[Get Database Query 1_1_1]]

 

The RV format for accessing results from a DB Query module is:

 

ModuleTitle_ColumnIndex_RowIndex

 

so the RV in module "Evaluate 8" should be:

 

$RV[Database Query 1_1_1]

[Guest Guest_Tom Storey]

I've attached 2 files 1 log, 1 PDF. After changing the Eval, the first school number now shows as 51. I then add "Where school_number = 51" to the query as you can see in the log. The query returns 0 lines. The PDF attached shows my schema. School_Number is an integer, can you see why my query is not working? The log I sent the last time shows all the school numbers and 51 is the first one.

Shared_Data_Schema.pdf

[Guest Guest_Tom Storey]

I see you can attach just 1 file at a time here the log.

0318vgm.txt

[SupportTeam]

We can see from the trace files that an SQL query:

 

SELECT School_Number FROM School_Names

 

works OK, returning 149 rows, yet the SQL query:

 

SELECT * FROM School_Names WHERE School_Number = 51

 

does not return any rows.

 

I am not too sure why you are getting problems with the second SQL - it does seem correctly phrased. VoiceGuide just passes the SQL command to the ODBC driver without any changes..

 

Can you try perhaps doing some other queries to give us a better picture of what works and what does not work, eg:

 

SELECT School_Number FROM School_Names WHERE School_Number = 51

 

or try a WHERE clause which tries to match against a school name or some other column...

 

Can you also try using the MS Query tool (connected to the same ODBC source VG is set up to use) and see what SQL queries work when using MS Query...

(look for a file named MSQRY32.EXE on you hard drive, if MS Office 2000 is installed file can be in C:\Program Files\Microsoft Office\Office10 )

 

What Database are you using and what ODBC driver are you using for it?

[Guest Tom Storey]

My database = OpenBase running on a Mac G4 1G

My ODBC Driver = OpenLink ODBC - JDBC Bridge

 

I tried all the field names with & without ''/"" and with & without () nothing works.

I copied the SQL statement out of VG and pasted it into MSQuery and it works great.

I started Excel and went through Import Data and it brings the data into Excel OK too.

 

It seem that for some reason if I don't use the Where clause with VG it works, but when I use the Where clause VG doesn't seem to fail it just doesn't return any data.

[SupportTeam]

Not too sure what can be causing the problems here... can you please try the following and see if any these help out :

 

1. Link the OpenBase to an table in MS Access on same computer as VG and try running queries from VG to the table in MS Access.

 

or

 

2. Use TableName.ColumnName notation when specifying the fields, so query would become:

 

SELECT * FROM School_Names WHERE School_Names.School_Number = 51

 

or

 

3. Add a ";" a the end of the SQL query, so query would become:

 

SELECT * FROM School_Names WHERE School_Number = 51;

 

or

 

SELECT * FROM School_Names WHERE School_Names.School_Number = 51;