Topspeed ODBC Select query with pagination

questions
1

Hi All,

Is it possible to perform pagination queries with the Topspeed ODBC driver? I’ve tried various combinations of FETCH, LIMIT and OFFSET but none appear to work as expected.

Any help would be appreciated.

Thanks,

Joe

2

Hi Joe,

Could you expand a bit on what are you trying to do? Eg. issuing a PROP:SQL, trying that in an ABC Browse generate queries including those clauses, etc
What is your target database engine, which version?
What have you tried? How? What error do you received?

Federico

3

Hi Federico,

Thanks for your quick reply. Here’s a little more background:

I have a .NET core application and I’m querying the topspeed files directly with the Clarion Topspeed ODBC driver. I’m using the System.Data.Odbc and associated libraries and forming queries in the ODBC style sql that Topspeed requires. I’m having great luck with it overall. The only thing I haven’t figured out yet is if there is the possibility to perform an ODBC Select query that allows me to batch the rows I’m returning. For example SQL Server has queries like:

SELECT name, price
FROM toy
ORDER BY price
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

I’m not immediately targeting a database system with the Topspeed file. I’m querying from it in an ETL style workflow.

Here’s an example query I’m trying with topspeed:

SELECT * FROM "file.tps\&Customers"
LIMIT 10;

error:

Message:  ERROR [42000] [SoftVelocity Inc.][TopSpeed ODBC Driver]Unexpected extra token: 10

It seems to know the LIMIT keyword but whatever I try after that is an error. I’ve tried parenthesis, brackets, etc.

Some queries actually crash the application and it’s totally unrecoverable in a managed app:

select count (*) from "file.tps\&Customers"

That throws a System.AccessViolationException : 'Attempted to read or write protected memory...

I’m not familiar with PROP:SQL.

I don’t know what topspeed version the database is and there’s a good chance I could be working with different versions at the same time. I’m happy to find that out if you can tell me how to identify it.

I hope that detail helps.

Thanks in advance,

Joe

4

Hi Joe,
Thanks for the details.
I first thought you were asking about something inside Clarion (PROP:SQL, ABC Browses) and using Clarion ODBC Driver accessing SQL Database Engines.

You could find information about TopSpeed ODBC driver Sintax following the links in this post from Geoff

It doesn’t appear to have LIMIT OFFSET on the syntax. It does mention some related to TOP but you would need to try it to see if it works as SELECT TOP.

On that thread there are other post with useful links too that worth following, and some comments about ODBC driver tracing to find errors when the output is not the expected.

Federico

5

I don’t think your query shows that the topspeed odbc knows anything about the LIMIT keyword. I think in your query if you stopped at “LIMIT” then limit would just be the alias for the table.

I looked at the link Federico posted and I don’t think it suggests that SELECT TOP might work. It has SELECTOP (select operator), which is one of All or ANY.

Bottom line, I don’t think any of the language extensions you hope would be there are actually there.

6

I searched for TOP and looked fastly but you seems right. Anyway it won’t hurt testing that combination if he didn’t do that already.

The offset part can be simulated by issuing selection filters > and >= with the order by fields if they are unique.

The driver implementation is a black box, but perhaps, depending on the indexes available and the query itself, records were processed for each ODBC FETCH NEXT and stopping them when a “page” is filled perhaps stops driver working, if performance is the issue.

But looking at the sample query , ORDER BY price, a full table scan would be needed if lacking index by price, so in that case it would be better to not paginate else a full table scan would be done in each new select unless it implements some smart cache. TOPSCAN would show index availables.

7

Hi Jon and Federico.

Jon: That makes total sense that LIMIT is being treated as an alias. I’ve come to the conclusion that these ODBC features are just not implemented. For my workflow it’s not critical but it would be a nice to have feature.

selectop isn’t a keyword, rather it’s a clause that can be ANY | ALL but it’s not clear to me how that would work. Here’s that part of the Supported SQL grammar from the TopSpeed ODBC chm file:

comparison ::= ( boolean ) | colref IS NULL | colref IS NOT NULL | expression LIKE pattern | expression NOT LIKE pattern | 
expression IN ( valuelist ) | expression NOT IN ( valuelist ) | 
expression op expression | 

EXISTS ( SELECT select ) | expression op selectop ( SELECT select ) | 
expression IN ( SELECT select ) | expression NOT IN ( SELECT select ) | 
expression BETWEEN expression AND expression) | 
expression NOT BETWEEN expression AND expression) 

selectop ::= | ALL | ANY

Federico: I tried SELECT TOP and that doesn’t work either for me.

I have a workaround using the Odbc DataReader to skip and take the rows I want to populate to a DataTable. It won’t be as performant as a pure query but that’s fine for my workflow.

Thanks for all your help,

Joe