ClarionLive show notes for June 1 2012

Today's ClarionLive webinar was a panel discussion on porting to SQL, with Mike Hanson, Bruce Johnson and JP Gutsatz.

This seems to be my day for getting to webinars late. I didn't arrive until an hour and a half into the show, and I may not get a chance to view the recording today. I'll post a note if I get around to updating this info. 

Michael Tabakin reminded everyone of www.connectionstrings.com, an excellent resource for database connection strings. 

Evidently there was some discussion of Windows vs SQL authentication/authorization. 

At roughly the 1:35 mark there was a spirited discussion of the portability of filter code. You want to be very careful about how you write your filters, because not all statements will be converted to SQL code on the server. And if your code can't be converted by the driver you're going to be doing all of your filtering on the client, which could make your browse, process or report very slow. 

Bruce referred to PROP:SQL as the "nuclear weapon" - once you go down that road you have to deal with all kinds of potential incompatibilities between SQL flavors, plus your code won't work with non-SQL databases. 

Also realize that the range (if you have one) becomes part of the filter, so if your filter isn't SQL-compatible then your range won't be either. If you're having performance problems, rip out the filter and put it back piece by piece until you find the part that's slowing everything down. 

Mike is a fan of Match, which gets translated to the appropriate back end SQL code. 

Bruce brought up the SQL Advanced tab, which nobody on the panel had ever used. It appears to be mainly useful for SQL performance optimizations. 

Lisa commented on the scope of the webinar being "too broad" - perhaps too many topics. What about the user who's just trying to get into SQL? 

Mike commented that total fields on large data sets will kill performance. He also showed his Build TransactionView extension template (part of his Super templates).

Some discussion of hot fields, and how in a non-SQL database the entire record is returned but in SQL you need to have any fields you want data for included in the view. You can ReGet the view which retrieves all relevant file records (not a good idea, as this defeats the benefit of using views in the first place). You can use the Hot Fields option in the templates. Be aware that the Bind option for views includes a "not in view" option which is a now-obsolete way to bind non-view procedures. 

Mike also showed a SuperStuff template that will bind all fields for one or all files in the view. 

In the chat Arnor pointed out that you can use the /TURBOSQL to pass non-validated SQL to the back end. For instance, you can construct the SQL in a class. 

At over three hours this is going to be one long recording. 

If you want to get the most out of this webinar, and you're at all fuzzy on BIND, read up on the help first. It'll save you some confusion.