Ok,
Thanks to Federico (so much!), I turned on the Tracing in ODBC Data Source Administrator.
I see this error
DIAG [HY000] [TopSpeed Corp.][TopSpeed ODBC Driver]Table not found (25)
Now, the only names I see are
(1) Database directory
(2) TPS file name (which I assumed was the table name)
(3) ODBC data source name
So, my question is: How do I correctly name the table for my SQL INSERT statement?
Thx to all,
Amnon
Great
compare with that image
note
on the DSN the “Database” has the full path, I think it is important to end with the slash
“Extension”: tps without leading dot
The table name on your query would be the name of the tps file without extension. The TPS files have one table each or it is a Superfile (one TPS with multiple tables)?
First, be sure you actually know the table name.
TPS files are sometimes “superfiles” containing more than one table. Do you know whether that’s the case?
This is from the help, but I don’t know how it may relate to access via ODBC:
Storing Multiple Tables in a single .TPS File
By using the characters '\!' in the NAME() attribute of a TopSpeed file declaration, you can specify that a single .TPS file will hold more than one table. For example, to declare a single .TPS file 's&p.tps' that contains 3 tables called supp, part and ship:
Supp FILE,DRIVER('TopSpeed'),PRE(Supp),CREATE,NAME('S&P\!Supp')
...
Part FILE,DRIVER('TopSpeed'),PRE(Part),CREATE,NAME('S&P\!Part')
...
Ship FILE,DRIVER('TopSpeed'),PRE(Ship),CREATE,NAME('S&P\!Ship')
jf
Try without the quotes on the table and field names
Thanks for various responses.
This is not a superfile, it has only one table per Topspeed file.
Configuration looks good, with backslash at end of file path, no dot in tps.
I’ve tried insert with and without quotes, many ways and with a reasonable set of fields and values.
I can only think there is some kind of permission issue that isn’t letting the insert “see” the table.
I’ve checked permissions on the file and the folder it’s in, nothing stands out as inhibiting.
Maybe some required field isn’t being supplied, but I would think that would cause a different error message.
Amnon
Hi,
One other item is that the machine is 64-bit, but the programs involved are 32-bit, as is the Topspeed ODBC driver.
Should be ok, I assume.
Amnon
You could try SELECTs instead of INSERTs to see if they work. With different tables.
Another thing you could try is running Sysinternal Process Explorer to see which file it tries to open.
No problem with 64-bit CPU/OS
It worth to try also using other tools to access the table through the same DSN, then if it works, look on the trace how queries are sent. Tools like Clarion IDE, Dictionary/Import/Browse table. Or maybe ETL Tools mentioned in the first linked thread above.
Thanks, I’ll pass this on to the Clarion guy I’m working with.
So Rick showed pics using Access.
I just set up a test DSN and was able to insert into the TPS file from Excel.
This is 64-bit Windows 10 machine. 32-bit DSN (“test2”) set up (windows\syswow64\odbcad32) and using 32-bit Excel 2013. Simple TPS file (not superfile) called wallboardDepts.tps.
As Federico says, probably worth trying something simple like this (or Access or some other tool) to make sure your basic setup is OK.
→ In the DCT you have to point the tables to drive ODBC
→ In the owner place a Global variable that will receive the MySql connection data
!Glo:PathServer
→ Install MySql ODBC 5.1
→ String for connection
Glo:PathServer=‘driver={{MySQL ODBC 5.1 Driver};’|
&‘Server=IP;’|
&‘Database=database name;’|
&‘User=xxx;’|
&‘Pwd=yyy;’|
&‘Port=3306;’|
&‘Option=3;’
→ Generate app with a browse using DCT table
→ Put the connection data in Global, in an embed like Program Setup
→ compile
Thx, I’ve passed this along to the Clarion experts, hopefully will help. - Amnon
There is a Special Flag that makes TPS files Read Only under ODBC. You could check if that has been turned ON.
Excerpt from Help:
FLAGS
[ Flags = ] SEND(file,'FLAGS [ = bitmap ]')
Sets and returns the configuration flags for the file. Use the following EQUATEs declared in EQUATES.CLW to control the behavior of the target TopSpeed file:
!TopSpeed File Flags
TPSREADONLY EQUATE(1)
For example, the following code makes the file read-only for ODBC access while preserving any other flags:
TpsFlags = SEND(MyFile, 'FLAGS')
SEND(MyFile, 'FLAGS =' & BOR(TpsFlags,TPSREADONLY) )
https://clarion.help/doku.php?id=topspeed_driver_strings.htm&s[]=tpsreadonly
Thanks to all the submitters of solutions.
Problem has been solved.
The empty Topspeed databases used for testing were corrupt in an unknown fashion.
The “Table not found” error message did not address the issue as explicitly as we would like.
Amnon Meyers