Using SQL with Topspeed ODBC

questions
1

Looking for beginner help using SQL with Topspeed ODBC.
Trying a simple SQL Insert statement, but nothing happens.
How to see error messages from the ODBC driver, etc.
Thx,
Amnon Meyers
[email protected]

2

Hi Amnon,
I think I have the answer for you. I might be way off-base.
Just to clarify, because ODBC works in both directions.
One way is to write a Clarion program, using Clarion statements obviously, to access a SQL database. This is ODBC but does not use the TopSpeed ODBC driver.
The second way is the inverse: you want to write SQL statements to access a TopSpeed file. This way uses the TopSpeed ODBC driver.
It sounds like you want to use the second way, that is, use SQL statements to access a .tps file.
Also, you may not know, there are two flavors of the TopSpeed ODBC driver. The first one is free and it can read a .tps file. That is, it is read-only.
The other is not free. You need to pay for it. I believe you need to buy a 20-pack for $699 from SoftVelocity.com. It allows you do a “simple SQL Insert statement” and of course whatever SQL you want.
It’s been awhile, but I think the free version doesn’t provide an errorcode or an error message if you try to insert a record.
Bob

3

Hi Amnon

have a look at the info on this thread and see if it helps

cheers

Geoff R

4

Thx, I’ll need to check on which one I have – hopefully part of a commercial package, since it’s from a SoftVelocity user.

5

Thx to all,
More generally, how would you insert a record in a TPS file from a C++ program. SQL or otherwise.
Would would the command look like, and how would it be executed, if not via an SQL-like language.
Amnon

6

Thanks, but this sounds like a manual interface.
I need a programmatic solution. My C++ program needs to update a TPS file automatically (that is, not by a person typing commands.)
Amnon

7

So, I’ve never used the ODBC driver against Topspeed, but my instinct is that you are reading into it something that doesn’t exist.

Given that TPS is not a SQL engine (ie, it has no way to parse SQL statements), and given that I’m not aware of the ODBC driver including a SQL-parsing engine, I feel like your initial premise (that the TPS ODBC somehow “supports SQL”) seems “unlikely”.

I might be wrong, so others can feel free to chip in, but I’ve not though of the ODBC driver as a SQL interface up to now.

8

As I understand it, the ODBC interface is a standard. So you would need to investigate calling ODBC from C++, and use whatever tools and languages exist in that environment to make ODBC API calls.

9

not sure if this will help but the tps ODBC supported SQL syntax can be found at

note that I understand the tps ODBC driver is built using “Dr. DeeBee ODBC Driver Kit”.

Also perhaps this MS link re C++ use of ODBC will help.

10

A couple more things that may be useful for you…
There is a Clarion UltimateSQL" class that you can find on github (UltimateSQL/libsrc at master · ClarionLive/UltimateSQL · GitHub). That’s basically a comprehensive clarion way to wrap around the low level ODBC calls that are similar to what is being done in the sample code that vitesse pointed you to. May give you a conceptual understanding of how to access data via ODBC.
Secondly, in Clarion, in addition to using Clarion Adds and Puts to insert an update records, we can also to that using direct ODBC calls, e.g. myfile{PROP:SQL} = ‘insert into myfile values(’ & myf:id & ‘,<39>’ & some_string & ‘<39>)’

Both those are possible because clarion, specifically the ODBC driver, translates in adds & puts and regular SQL into calls to SQLODBC commands like SQLColAtrtibute and SQLExecDirect etc. that then get passed to whatever specific ODBC back end you are trying to target, in this case the TPS ODBC driver.

You may find it easier to write a Clarion DLL that actually talks to the data files (either using ODBC or native TPS access) and call those access functions in the DLL from C/C++. I only used the TPS ODBC driver a little, a long time ago, and my recollection is that the types of SQL statements that it would accept were very limited, they were designed to handle things similar to Clarion Adds/puts/deletes and not much more.

11

Thx,
I can update eg MySQL databases with SQL commands via ODBC.
What I can’t do right now is get the same to work for a Topspeed database file.
With the same commands and methods I’ve used before in a C++ program.

Presumably Clarion has precedents for doing this.

And I can’t even figure out how to view any error messages, maybe someone can help with that?

Best regards,
Amnon

12

Thanks for this,
I’ve seen the grammar and I have working code for example for a MySQL database.

The same type of code is not working with a TPS file.

Is there a way to view the error messages produced by Topspeed ODBC?

Best regards,
Amnon

13

Thanks for this,
My needs are that simple, merely to add records to an existing TPS database file.
But SQL commands that work with other databases aren’t working so far with Topspeed ODBC.
(Is there a way to see the error messages?)
The idea of a Clarion DLL is something I’ll try to get “my Clarion guy” to try!
Amnon

14

If you do go down the Clarion DLL route, this might help:

https://www.icetips.com/showarticle.php?articleid=355

Jim Kane was a very clever programmer, who is unfortunately no longer around the newsgroups.

15

Amnon, I’m not sure what problems you are running into, but you should be able to insert rows into a TPS table via the ODBC driver.
Here is view of a System DSN I have setup for a folder containing TPS files.
image

In MS Access I added the files from that folder as external linked data to the Access database.
Then I can use a query like below to insert a row in one of the tables. This definitely works. I just tested it.

image
image739×278 19 KB

Clearly this is using MS Access, not whatever development environment you are using. But you can definitely insert a row using a SQL command.

2 Likes
16

It could be possible that certain columns need a value to satisfy one or more keys. And some of them could need to be unique.

17

Hi Amnon,
I assume your C++ program is using a SQL database. This is good.
You can have a Clarion developer write a little Clarion program for you to copy rows from a SQL table to the tps file. No ODBC is involved.
The Clarion programmer needs to know the columns of the table and the tps file, and anything special like dates or time columns.
So, here are the steps:

  1. Your C++ program inserts one or more rows into a SQL table.
  2. Your C++ program runs the Clarion program, which returns a return code.
  3. The Clarion program copies the rows from SQL table to the tps file.
  4. Your C++ program checks whether the Clarion program returned an error code.
  5. If no error is returned, your C++ program deletes the rows from the SQL table.
    This is what ODBC would do, but it isn’t “open”. The Clarion program is specific.
    If you don’t have a Clarion programmer associate, I can write it for you in a few minutes.
    Bob
18

Thanks, Bob,
I’m looking for something even simpler.
Just need to automatically populate a TPS database directly from a C++ function, with or without SQL syntax (that is, whatever works).
Eg, a hardwired insert statement that adds a row to the TPS database.
(I’ve been trying this, and it hasn’t worked.)

Someone has responded that I may need to be filling required fields, and that’s why things are not populating.
(I’ll check that out, else get back to you!)
With appreciation,
Amnon

19

Hi Amnon
Have you tried turning on ODBC trace and looking at SQL.LOG to see if there is some information there? (old screen capture here 5.10.1 Enabling ODBC Tracing on Windows)

Also note your C++ code should be 32bits as the ODBC Driver is.

Federico

20

Hi Federico,
Thanks, I will try that!
I know I’m dealing with 32 bit everywhere (including my “C++ program”, actually called VisualText).
Amnon