How does SF handle queries with commands that differ between providers such as SUBSTR vs SUBSTRING, etc.  Is that something that needs to be handled on the developer side or is there any kind of translation built into the data access layer?

There is one option through framework to have a "non-provider" specific query created and this is the same approach we take on the BrowseDialog control.  If you'll notice the overloads on the FillDataTable method on any business object, it has an overload with a QueryInformation parameter.  This QueryInfo class allows a query to be forumulated that is not provider specific and it is automatically converted by the data access layer.  This allows the DAL to swap and the code to remain the same.  There are obviously a few restrictions when taking this approach and it is not a solution for a 100% non-provider specific environment, but it will get you a long way down the road.

Generally this is handled on the side of the developer.  There have been some developers run all of their queries through a third-party product that allows the "from" and "to" database languages to be specified and it converts on the fly.  Others know that they will either be dealing with one or two langauges and program the logic accordingly in the BO.

Hope this helps.

What third-party product?

This is the 3rd party product we have researched that will convert the SQL statements from one provider type to another:

http://www.swissql.com/products/sqlone-apidotnet/sqlone-apidotnet.html

Thanks.

No problem