Actually the question is, what is the mechanism for executing queries to the DBMS (for example, let it be MySQL) and what is the role of such components as the language SQL and ODBC in this mechanism? It is not clear that if there is a universal query execution language, which eliminates the differences between the DBMS, why do we also need a universal driver?

  • sql (language) - not universal. There are dialects. odbc is one of the standards (libraries), which allows both to make a request, and to find out the list of procedure field tables without using queries. In addition to odbc, there are jdbc, oledb and others. - nick_n_a
  • @nick_n_a, the presence of dialects does not negate its universality, otherwise it would not be necessary. Most queries written in accordance with the standard will be equally interpreted on most relational databases. And if you understand something like T-SQL or PL / SQL as dialects , well, this is more of a procedural extension of universal SQL, which, I note, was out of the question. - aryndin
  • You correctly answered. sql = language. odbc = library (interface for drivers) to the DBMS. odbc can execute sql, and sql can execute odbc (sql is not associated with odbc). By versatility, I meant that sql(mysql):show tables will not be equal to sql(mssql):select * from sys.tables , and odbc provides a function like gettables that always returns the same list of tables for any DBMS. - nick_n_a

2 answers 2

SQL is a query language. ODBC is a way to connect to a specific server specific database. All databases have different protocols for accessing the database server. ODBC provides a unified interface and hides a specific implementation of the access protocol.

    The task of the driver is not only to transfer the SQL query to the server, such a transfer protocol could be standardized if it were just text. But there is such a thing as the parameters passed to the request and, most importantly, the result of the request. The task of the driver is to package the request, parameters and results into packets transmitted over the network. And the format of the packaging language does not specify and each database implements it in its own way. Moreover, in many DBMS there are not only different data types, but also complex types, for example, as PostgreSQL arrays, which are not in general in SQL standards. And how to transfer these types of data over the network, again, each database decides on its own.

    Therefore, to work with a DBMS, programs are linked with libraries (drivers) of specific DBMS that are able to communicate with a specific DB. The ODBC driver is designed so that the program can be linked with a single library and through it get access to different databases, it is clear that the program will not be able to access non-standard data types and will need to be limited to standardized, more or less the same in different DBMS capabilities of the SQL language.