Are stored procedures used by a CA Gen Windows C/SQL Server application (ODBC) for database calls

Products

• Gen

Issue/Introduction

In trials running a CA Gen application on windows C and SQL Server, the testing team this says that the database calls are converted into stored procedures
Which component is doing this i.e. CA Gen?, ODBC?

Environment

Release : 8.6
Component : CA Gen Construction

Resolution

The generated code for Windows/C Gen applications targeting DBMS(TD)=MSSQL generates ODBC SQL rather than native SQL Server SQL.

Support ran some tests on the sample model Windows Manager GUIMENU generated for WINDOWS/C/MSSQL.
Testing the built application with Tracing enabled in the ODBC 32-bit administrator application (C:\Windows\SysWOW64\odbcad32.exe), the tracing output (SQL.LOG) does not appear to show any stored procedures being used:

GUIMENU         257c-2658 ENTER SQLPrepare 
  HSTMT               0x065022F8
  UCHAR *             0x00C26778 [      -3] "SELECT EMPLOYEE02."FK0DEPARTMENTNUMBER" FROM "EMPLOYEE" EMPLOYEE02 WHERE (EMPLOYEE02."FK0DEPARTMENTNUMBER" IS NOT NULL AND EMPLOYEE02."NUMBER" = ?)\ 0"
  SDWORD                    -3

GUIMENU         257c-2658 EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
  HSTMT               0x065022F8
  UCHAR *             0x00C26778 [      -3] "SELECT EMPLOYEE02."FK0DEPARTMENTNUMBER" FROM "EMPLOYEE" EMPLOYEE02 WHERE (EMPLOYEE02."FK0DEPARTMENTNUMBER" IS NOT NULL AND EMPLOYEE02."NUMBER" = ?)\ 0"
  SDWORD                    -3


However when the SQL Server Profiler is run it can be seen that system stored procedures are being executed e.g. sp_cursorfetch, sp_cursorclose, sp_describe_undeclared_parameters:

So Gen itself is not generating or using stored procedures and it is due to the way the SQL Server ODBC driver is working.

The Microsoft SQL documentation > Reference > System stored procedures confirms that the SQL Server ODBC driver API uses system stored procedures:
+++
API System Stored Procedures
Users that run SQL Server Profiler against ADO, OLE DB, and ODBC applications may notice these applications using system stored procedures that are not covered in the Transact-SQL Reference. These stored procedures are used by the Microsoft SQL Server Native Client OLE DB Provider and the SQL Server Native Client ODBC driver to implement the functionality of a database API. These stored procedures are just the mechanism the provider or driver uses to communicate user requests to an instance of SQL Server. They are intended only for the internal use of the provider or the driver. Calling them explicitly from a SQL Server-based application is not supported.

The sp_createorphan and sp_droporphans stored procedures are used for ODBC ntext, text, and image processing.

The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool.

The system stored procedures in the following tables are used only within an instance of SQL Server or through client APIs and are not intended for general customer use. They are subject to change and compatibility is not guaranteed.

The following stored procedures are documented in SQL Server Books Online:
...
+++