Replace TopScan with ODBC


#1

Shown on ClarionLive Sept/16/2016 by Greg Bailey

If you’re using TPS files and TopScan here is a way to get a much nicer database browser.
Note: use of the ODBC driver is free for developers, but is a $249 for a 5-user license for end-users

Steps:

  1. Download, Install and Run: http://www.icetips.com/downloadfile.php?FileID=262
    written by @ArnorBld
    it adds some registry entries
    and helps you open the correct ODBC Data Source Administrator (the 32 bit one)

  2. Point to your ODBC driver for TopSpeed %cwroot%\bin\c4otSDX.dll

  3. Create a DSN for the folder of TPS or SuperFile
    Run the ODBC Data Source Administrator (32-bit)
    On the User DSN tab, click on Add
    Select the TopSpeed ODBC Driver and click finish
    You are now on the Topspeed Data Source Name Configuration dialog
    Give this connection a name and description that are meaningful to you
    Type in the folder where your .TPS appear
    (there are more fields, but I haven’t used them)
    click OK to save this new DSN

  4. Download, Install and Run: www.etl-tools.com/database-browser/overview.html
    (this is just one such tool that you can use)

  5. In the database browser installed in step 4, add a connection
    Set the Type to ODBC
    click on the ODBC-DSN down arrow, and pick the DSN you just created in step 3
    make sure you check the box, ANSI DRIVER otherwise your table names will appear as boxes
    click OK

  6. see a list of all of the TPS tables in that folder now appear in the database browser

  7. click on a table
    see all of the columns nicely formatted, ex: dates and time appear correctly

  8. type a SQL command in the top window etc.


#2

Hi Mark,

I just want to stress the “ANSI DRIVER” checkbox in step 5! Without it being checked, the table names will all show in Chinese in the table list! The Database Browser people were kind enough to fix that right away for me and also fixed a problem where you would get a syntax error if your SQL statement spanned multiple lines and you were not on line 1 when you hit F4 to execute the query - couple of other ODBC browser I tried also seem to have this problem.

Some tips:

  1. I found that I had to run the Database Browser elevated for it to work properly with the TPS ODBC driver. Not sure why.

  2. I use the read-only version of the ODBC driver as I don’t want to accidentally change the data I’m working with.

  3. Dates in filters need to be formatted correctly for the ODBC driver:

WHERE myDate = {d ‘2015-01-01’}

Note the curly braces.

  1. Syntax error are pretty much all the same and give you little or no information about where and what the problem is.

  2. Some additional ODBC resouces:
    System Functions
    https://msdn.microsoft.com/en-us/library/ms713608(v=vs.85).aspx

Data Conversions:
https://msdn.microsoft.com/en-us/library/ms715381(v=vs.85).aspx

CONVERT:
https://msdn.microsoft.com/en-us/library/ms713608(v=vs.85).aspx

  1. You can have a single JOIN in the SQL statement as far as I could find. But you can SELECT from multiple tables, using WHERE to relate:

SELECT * FROM MyTable MYT, OtherTable OTH
WHERE OTH.ID = MYT.OthID

etc. The WHERE is used to relate the tables together.

Most of the system functions do not seem to be supported by the TPS driver or I have not figured out the syntax for them! Needed to do modulus calculation on a value and had to abandon it as MOD() is not supported and casting any part of an expression to integer seems to cast everything to integer.

The Database Browser has been a huge help for me. I took on a project that is using TPS files (will be converted to SQL later) and I was completely lost when validating data. With it you can select from multiple tables, apply complex filters, do aggregates etc.

Hope this helps:)

Best regards,
Arnor


#3

Hi Mark

I finally worked out what the problem was when I was getting the “Operation must use an updateable query” when adding the Connections in the browser. The ETL ODBC browser must run elevated at “Administrator” level. I changed the properties of the executable to allow this.

Thanks for all your help

Paul


#4

@Paul_Mainstone - which version of the browser did you install?

Mine worked fine without any (UAC) elevation

maybe it matters where you install the browser. I installed the portable version into a OneDrive folder.

Is it possible that the need for elevation stems from the folder being pointed to by the DSN (for the .TPS files) ?