This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

Function Prototype:

SQLRETURN SQLFreeStmt(
  SQLHSTMT hstmt,            /* 32-bit input */
  SQLSMALLINT Option;        /* 16-bit input */
  );

Job:

SQLFreeStmt has five different jobs, depending on the value of Option:

  • If Option is 0, SQLFreeStmt's job is to close a Cursor.
  • If Option is 1, SQLFreeStmt's job is to destroy a stmt.
  • If Option is 2, SQLFreeStmt's job is to unbind Columns.
  • If Option is 3, SQLFreeStmt's job is to unbind parameters.
  • If Option is 4, SQLFreeStmt's job is to reallocate.

Algorithm:

If (Option == SQL_CLOSE (0))
  If (there is a Cursor)
    Cancel all information about the result set.
    Close the Cursor.
If (Option == SQL_DROP (1))
  /* SQLFreeStmt(...,SQL_DROP) is the reverse of SQLAllocStmt(...).
  sqlreturn = SQLFreeStmt(hstmt,SQL_DROP);
  is the same as:
  sqlreturn = SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
If (Option == SQL_UNBIND (2))
  /* This affects the application row descriptor (ARD). In
      effect, it cancels out any SQLBindCol calls made on stmt. */
  For (i=1; i<=ARD.SQL_DESC_COUNT;++i)
    Set ARD.IDA[i].SQL_DESC_DATA_POINTER = 0
If (Option == SQL_RESET_PARAMS (3))
  /* This affects the application parameter descriptor (APD). In
      effect, it cancels out any SQLBindParameter calls made on stmt. */
  For (i=1; i<=APD.SQL_DESC_COUNT;++i)
    Set APD.IDA[i].SQL_DESC_DATA_POINTER = 0
If (Option == SQL_REALLOCATE (4))
  Destroy the stmt's statement and Cursor parts
If (Option == none of the above)
  return error: HY092 CLI-specific condition-invalid attribute identifier

Notes:

  • Regrettably, we are forced to mention a few details before their time for full discussion is come. For the immediate purpose, we hope it is enough to say that:
    • Result sets are temporary Tables which result from the execution of an SQL query.
    • Cursors are named interface objects through which result sets can be retrieved one row at a time.
    • Bindings are associations between a host program's host variables (i.e.: pointers to data buffers and indicator variables) and their corresponding SQL Objects (Columns and/or parameter markers). Often Columns are bound with the SQLBindCol function, while parameters are bound with the SQLBindParameter function.
  • Because SQLFreeHandle is now the main function for freeing resources, the name SQLFreeStmt is now a misnomer we don't use SqlFreeStmt for freeing stmts nowadays. However, it maintains a residue of usefulness. It's good for "partial stmt freeing", freeing things associated with stmts that might be taking up space, or whose continued existence might cause conflict with other operations.
  • SQLFreeStmt(...,SQL_CLOSE) does exactly the same thing as the SQLCloseCursor function, except for one detail: if there is no Cursor currently open, then SQLCloseCursor returns an error, while SQLFreeStmt(...,SQL_CLOSE) does not return an error.
  • SQLFreeStmt(hstmt,SQL_DROP) is now just another way of saying: SQLFreeHandle(SQL_HANDLE_STMT,hstmt).
  • SQLFreeStmt(...,SQL_UNBIND) and SQLFreeStmt(...,SQL_RESET_PARAMS) are handy ways of "disassociating" the application's RAM from the DBMS, so that there won't be inadvertent access to memory that's no longer valid. Here's an example:
    x=malloc(1024);                   /* malloc an area */
    SQLBindCol(...,x,...);            /* Now there is a binding to the malloc'd area */
    free(x);                          /* uh oh. x is no longer a valid pointer */
    SQLFreeStmt(...,UNBIND ColumnS);  /* okay, chances of GPF are reduced */
  • However, only the "data pointer" field is cleared not the "indicator pointer" field (this is probably an inadvertent omission from the Standard).
  • SQLFreeStmt(...,SQL_REALLOCATE) can be used to clear parts of stmts:
    • The "statement" (a copy of the SQL statement string which was last prepared or executed for this stmt, including "select" source statements).
    • The "Cursor", including the Cursor's result set. These things take up space, so presumably SQLFreeStmt(...,SQL_REALLOCATE) is useful under low-memory conditions. But normally it's unnecessary: "statement" and "Cursor" are superseded in any case when an SQL statement is re-prepared/re-executed.

Example:

Since SQLFreeStmt(...,SQL_DROP) is the reverse of SQLAllocStmt(...), we show both these obsolescent calls together.

#include "sqlcli.h"
SQLHDBC hdbc;
SQLHSTMT hstmt;
...
SQLAllocStmt(hdbc,&hstmt);        /* allocate a stmt */
SQLBindCol(hstmt,...);            /* bind a stmt Column */
SQLFreeStmt(hstmt,SQL_UNBIND);    /* unbind all stmt Columns */    ...
SQLFreeStmt(hstmt,SQL_DROP);      /* free a stmt */

ODBC: The SQLFreeStmt function has been in ODBC since version 1.0. The ODBC 3.0 manual deprecates the use of SQLFreeStmt(...,SQL_DROP), suggesting that the user should switch to using SQLFreeHandle(SQL_HANDLE_STMT,...). The ODBC action for SQL_UNBIND is different: ARD.SQL_DESC_COUNT is set to 0. The ODBC action for SQL_RESET_PARAMS is different: APD.SQL_DESC_COUNT is set to 0. The SQL_REALLOCATE option is not supported.

And that's it for the stmt functions. In the next chapter, we'll take a look at the statement functions.

Comments

Comments loading...