ClarionLive show notes for May 11 2012

Webinar #158 continued the SQL theme. 

Clarion Word of the Day

The word of the day isn't a word, it's an assignment operator: &=

This is used to assign references, and is especially useful with objects. This is a potentially huge topic that could be a webinar in itself. 

WebGem

Arnold brought up Everything from VoidTools, which is a very fast search tool for Windows. 

Next week

Import/export, but that may get put off to a later date. 

Ultimate SQL Class

John posted a new build of his Ultimate SQL class. The class is now using Rick Martin's ODBC Direct which makes for much faster queries than the dummy file approach. It also has support for extended properties, which lets you document your SQL with information such as you find in your dictionary. 

Also in the utilities is the new Ultimate Class Template, a work in progress. This template makes it easier to manage classes and/or their usage. 

Data Conversion Panel

Featuring Mike Hanson, Bruce Johnson, JP Gutsatz, Jim Morgan, and Mike Gorman.

Mike Gorman, who is the secretary ANSI NCITS H2 Technical Committee on Database, explained how he used the Dct2SQL templates to convert to Mimer and MS SQL. The templates were started by Roberto Artigas and have had many contributors. They can generate a variety of scripts, including create tables, grant permissions, create keys and relations, and drop constraints. 

Mike uses NVARCHAR instead of VARCHAR for unicode support, but this does double the number of characters needed for storage. 

Jim Morgan talked about his Isam2SQL templates, released as DonateWare. You will need to place a zero dollar order, after which you'll get the email with the link. 

He's not entirely sure where the original template code came from, but there is some commonality between these and the Dct2SQL templates. 

Jim demonstrated importing a gig of TPS data into a SQL database in under five minutes. There were eight files, two of which were quite large with about two million records each. 

He did in fact put himself on the clock.

Step one: Create a new application with the dictionary in question.

Step two: Run the utility template. This created some SQL scripts and a Clarion program. 

Step three: Open the project created by the utility. These have some intentional compile errors. 

Step four: Run the program which extracts the TPS data into a sort-of CSV file. While that's happening...

Step five: Run the scripts to create the database and define the tables. 

Step six: After the export is done, run the bulk import script

Jim did run a little overtime - it took six minutes in total. It takes the utility about a minute to export a gig of data and another minute to import a gig of data. He also went into some tips and techniques for converting data such as dealing with nulls vs empty strings, real precision, date fields, etc. 

The template doesn't handle binary data, so you need to go back and update blobs using a regular conversion approach. 

The templates use the prefix RSVD_ for field and table names that are reserved SQL words. Just add a Name() attribute to your field/file to match. 

Jim recommends SQL Server 2008 or later as you can use Date types and avoid potential filter issues as you can get with DateTime. For example, if you have a browse filtered on a datetime, and someone adds a time value to the field, a date filter on sqldate = clariondate will fail because the sqldate will not be an exact date value. 

The templates convert any string types over 30 characters to varchar. 

FM3

Bruce Johnson showed CapeSoft's File Manager 3. FM3 can generate an SQL version of your app while still letting you continue on with the TPS version. 

Bruce walked through the kinds of prep work needed before creating a SQL version, including the need for primary keys on all files. Also remove the Exclude Empty Keys option from your definitions, since this is not an SQL concept. He likes to use external names for indexes prepended with the prefix and an underscore. 

Consider changing your dates to Date fields (esp for MS SQL 2008 or later). 

Bruce talked a bit about arrays. Few SQL databases support arrays as a type directly, so , and Clarion doesn't let you use them with SQL databases. But the driver ignores Groups and OVERed fields, so you can still use arrays by declaring them OVER a string in your SQL table. Jim also pointed this out. 

JP pointed out that putting global vars used for file names in the dictionary keeps everything nicely together.

Bruce used the Invoice app and dropped in a the FM3 extension. That needs to be the first thing before any dictionary changes. 

Bruce went through a number of dictionary changes to make it more SQL-friendly. He explained that the application is still live, still available for use by customers, but also available for SQL testing once you generate the SQL version. 

The FM3 engine does its own CREATE TABLE (it's been said for years in the community that relying on Clarion's CREATE attribute is not a good idea). 

Lots of good information in this webinar for anyone using FM3 to migrate from TPS to SQL. 

Mike pointed out the disconnect between Clarion's key definitions and the indexes on the back end. All the keys do when you use them in file access statements is tell the driver how to create ORDER BY clauses - it's up to the server to decide  what indexes (if any) to use. 

JP Gutzatz

Bruce ran until the 2:12 mark, so JP was left with a limited amount of time. He demonstrated his well-known Data Management Center v3 which quite a few Clarion devs use to migrate data between platforms. 

Unfortunately I ran out of time and wasn't able to see the end of the webinar, so perhaps someone else can add a few more notes here.