4D offers 3 different-but-related ways to use SQL.

• High-level SQL Theme commands


• 4D ODBC Pro


• SQL Pass-through

This Tech Tip briefly covers why the three offerings exist and what tasks they should be used for.

High-level SQL Theme commands

These commands are documented under the SQL theme in the 4D Language Reference.

The high-level SQL commands of 4D can be used to implement simple solutions allowing 4D applications to communicate with ODBC data sources. If your applications require more extensive support of ODBC standards, you will need to have the “low level” ODBC plug-in for 4D, 4D ODBC Pro.

These commands are most advantageous for making simple requests to external, non-4D data sources. It is true they can be used against the internal database as well as external 4D databases but this is not preferred.

4D ODBC Pro

The 4D ODBC Pro plug-in provides a low-level mapping between ODBC specification C functions and 4D. Keep in mind that the ODBC specification is not maintained by 4D (it is maintained by Microsoft). The important thing to understand here is that the plug-in is only wrapping the C functions defined by Microsoft. You may notice, for example, that the 4D ODBC Pro documentation does not extensively cover each function but, rather, refers to the Microsoft documentation.

This plug-in is useful for making external connections to ODBC data sources (it cannot be used against the internal SQL database or for "direct" connections to other 4D databases). It is especially useful for those wishing to implement advanced ODBC features not covered by the basic high-level commands.

Note: though this plug-in could be use to connect to other 4D databases via the 4D ODBC Driver, this is not preferred because SQL Pass-through offers a superior experience.

SQL Pass-through

The SQL Pass-through feature allows SQL statements, written between Begin SQL/End SQL tags, to be passed directly to external data sources. Whether via connections opened using ODBC, or direct connections made to 4D Servers, this feature offers an elegant way to integrate SQL calls directly into the database in a more advanced fashion than the high-level SQL Theme commands, while avoiding the programming overhead of the low-level 4D OBDC Pro plug-in. With 4D's built-in support for SQL, it is even possible to use the same 4D code to talk to more than one database by simply changing the connection. See Tech Tip "Multi-System Data Access - SQL is the Key" for more details (linked in the "See Also" section below).

Important Note: SQL Pass-through must be used for Replication. Neither the SQL Theme commands nor 4D ODBC Pro can be used for Replication.