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]
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
Hi Amnon
have a look at the info on this thread and see if it helps
cheers
Geoff R
Thx, Iāll need to check on which one I have ā hopefully part of a commercial package, since itās from a SoftVelocity user.
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
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
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.
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.
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.
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.
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
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
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
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.
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.
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.
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
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.
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:
- Your C++ program inserts one or more rows into a SQL table.
- Your C++ program runs the Clarion program, which returns a return code.
- The Clarion program copies the rows from SQL table to the tps file.
- Your C++ program checks whether the Clarion program returned an error code.
- 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
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
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
Hi Federico,
Thanks, I will try that!
I know Iām dealing with 32 bit everywhere (including my āC++ programā, actually called VisualText).
Amnon