Working With Large Result Sets

Three new attributes are provided for large result set support. They are defined in the file verticaodbc.h.

1. The connection attribute ATTR_VERTICA_LRS_PATH specifies the location in which the ODBC driver keeps temporary files for large result sets. For example:

   char * lrs_path="/my_disk/tmp";

   ret = SQLSetConnectAttr(conn.dbc, SQL_ATTR_VERTICA_LRS_PATH,

   (PTR)lrs_path, strlen(lrs_path));

   Unix/Linux default values:

   • If the environment variable TMPDIR exists and contains the name of an appropriate directory, that is used.
   • Otherwise, if the dir argument is non-NULL and appropriate, it is used.
   • Otherwise, "/tmp" is used.

   Windows default values:

   • If the TMP environment variable is defined and set to a valid directory name, that name is used.
   • Otherwise, the dir parameter is used as the path.
   • If the dir parameter is NULL or set to the name of a directory that does not exist, the current working directory is used.
2. The statement attribute SQL_ATTR_VERTICA_MAX_MEM_CACHE defines the maximum memory for the client storage of a large result set. If the result set size exceeds this value, the ODBC driver uses a temporary file to keep the large result set or uses streaming mode for fetching data from the database server. For example:

   SQLUINTEGER mem_cache_size=256*1024*1024; // 256 MB

   SQLSetStmtAttr(hstmt, SQL_ATTR_VERTICA_MAX_MEM_CACHE, (PTR)mem_cache_size, 0);

   The default value is 64MB.

3. The statement attribute SQL_ATTR_VERTICA_LRS_STREAMING specifies that the ODBC driver should use a temporary file to keep the large result set, or use streaming mode to fetch the large result set from the database server. If the value is TRUE, the ODBC driver pauses the query execution when the memory cache on the client is full and resumes execution of the query after the memory cache rows are retrieved by the ODBC application using SQLFetch. For example:

   SQLUINTEGER lrs_streaming=1;

   SQLSetStmtAttr(hstmt, SQL_ATTR_VERTICA_LRS_STREAMING, (PTR)lrs_streaming, 0);

   The default value is FALSE.