Salesforce

SQL Functions (Magic xpa 4.x)

« Go Back

Information

 
Created BySalesforce Service User
Approval Process StatusPublished
Objective
Description

SQL Functions (Magic xpa 4.x)

The ODBC Check Driver uses the existing connection to the selected data source and sends the function SQLGetFunction, with SQL_API_ALL_FUNCTIONS in the fFunction field.

This returns information about whether a driver supports the list of ODBC functions. Those functions are implemented in the Driver Manager and can also be implemented in drivers.

If a driver implements SQLGetFunctions, the Driver Manager calls the function in the driver. Otherwise, it executes the function itself.

In order to retrieve the same information you connect to the data source with Connect\Full Connect, and select Misc\SQLGetFunctions All. The result window will show a list of ODBC function names with a True\False value identifying whether they exist in the data source.

Information in the utility log file is listed in the following three columns:

  1. Function – The name of the ODBC function.

  2. Supported by driver – Supported\Not Supported according to the True\False value returned from the SQLGetFunction results.

  3. Used by Magic xpa – Required\Not Required by Magic xpa

Functions required by but unsupported by the data source may cause further problems when working with Magic xpa and ODBC with this data source.

The SQL functions are as follows:

Function

Description

Supported by Driver

Used by Magic xpa

SQLAllocConnect

Allocates memory for a connection handle within the environment identified by the handle.

Supported

Required

SQLAllocEnv

Allocates memory for an environment handle and initializes the ODBC call level interface for use by an application.

An application must call SQLAllocEnv prior to calling any other ODBC function.

Supported

Required

SQLAllocStmt

Allocates memory for a statement handle and associates the statement handle with the connection specified by a connection handle.

An application must call SQLAllocStmt prior to submitting SQL statements.

Supported

Required

SQLBindCol

Assigns the storage and data type for a column in a result set, including:

  • A storage buffer that will receive the contents of a column of data

  • The length of the storage buffer

  • A storage location that will receive the actual length of the column of data returned by the fetch operation

  • Data type conversion

Supported

Required

SQLBindParameter

Binds a buffer to a parameter marker in an SQL statement.

Note: This function replaces the ODBC 1.0 function SQLSetParam.

Supported

Not Required

SQLBrowseConnect

Supports an iterative method of discovering and enumerating the attributes and attribute values required to connect to a data source. Each call to SQLBrowseConnect returns successive levels of attributes and attribute values.

When all levels have been enumerated, a connection to the data source is completed and a complete connection string is returned by SQLBrowseConnect. A return code of SQL_SUCCESS or SQL_SUCCESS_WITH_INFO indicates that all connection information has been specified and the application is now connected to the data source.

Not Supported

Not Required

SQLCancel

Cancels the processing of a statement’s handle.

Supported

Required

SQLColAttributes

Returns descriptor information for a column in a result set. It cannot be used to return information about the bookmark column (column 0).

Descriptor information is returned as a character string, a 32-bit descriptor-dependent value, or an integer value.

Supported

Not Required

SQLColumnPrivileges

Returns a list of columns and associated privileges for the specified table. The driver returns the information as a result set on the specified statement’s handle.

Not Supported

Not Required

SQLColumns

Returns the list of column names in specified tables. The driver returns this information as a result set on the specified statement’s handle.

Supported

Required

SQLConnect

Loads a driver and establishes a connection to a data source. The connection handle references storage of all information about the connection, including status, transaction state, and error information.

Supported

Required

SQLDataSources

Lists data source names. This function is implemented solely by the Driver Manager.

Supported

Not Required

SQLDescribeCol

Returns the result descriptor column name, type, precision, scale, and nullability for one column in the result set. It cannot be used to return information about the bookmark column (column 0).

Supported

Required

SQLDescribeParam

Returns the description of a parameter marker associated with a prepared SQL statement.

Not Supported

Not Required

SQLDisconnect

Closes the connection associated with a specific connection handle.

Supported

Required

SQLDriverConnect

SQLDriverConnect is an alternative to SQLConnect. It supports data sources that require more connection information than the three arguments in SQLConnect, dialog boxes to prompt the user for all connection information and data sources that are not defined in the ODBC.INI file or registry.

SQLDriverConnect provides the following connection options:

  • Establish a connection using a connection string that contains the data source name, one or more user IDs, one or more passwords, and other information required by the data source.

  • Establish a connection using a partial connection string or no additional information; in this case, the driver Manager and the driver can each prompt the user for connection information.

  • Establish a connection to a data source that is not defined in the ODBC.INI file or registry. If the application supplies a partial connection string, the driver can prompt the user for connection information.

Once a connection is established, SQLDriverConnect returns the completed connection string. The application can use this string for subsequent connection requests.

Supported

Required

SQLDrivers

Lists driver descriptions and driver attribute keywords.

This function is implemented solely by the Driver Manager.

Supported

Not Required

SQLError

Returns error or status information.

Supported

Required

SQLExecDirect

Executes a preparable statement, using the current values of the parameter marker variables if any parameters exist in the statement. SQLExecDirect is the fastest way to submit an SQL statement for one-time execution.

Supported

Required

SQLExecute

Executes a prepared statement, using the current values of the parameter marker variables if any parameter markers exist in the statement.

Supported

Required

SQLExtendedFetch

Extends the functionality of SQLFetch in the following ways:

  • It returns rowset data (one or more rows), in the form of an array, for each bound column.

  • It scrolls through the result set according to the setting of a scroll-type argument.

SQLExtendedFetch works in conjunction with SQLSetStmtOption. To fetch one row of data at a time in a forward direction, an application should call SQLFetch.

Supported

Required

SQLFetch

Fetches a row of data from a result set. The driver returns data for all columns that were bound to storage locations with SQLBindCol.

Supported

Required

SQLForeignKeys

SQLForeignKeys can return:

  • A list of foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables).

  • A list of foreign keys in other tables that refer to the primary key in the specified table.

The driver returns each list as a result set on the specified statement handle.

Not Supported

Not Supported

SQLFreeConnect

Releases a connection handle and frees all memory associated with the handle.

Supported

Required

SQLFreeEnv

Frees the environment handle and releases all memory associated with the environment handle.

Supported

Required

SQLFreeStmt

Stops processing associated with a specific statement handle, closes any open cursors associated with the statement handle, discards pending results, and, optionally, frees all resources associated with the statement handle.

Supported

Required

SQLGetConnectOption

Returns the current setting of a connection option.

Supported

Required

SQLGetCursorName

Returns the cursor name associated with a specified statement handle.

Supported

Not Required

SQLGetData

Returns result data for a single unbound column in the current row. The application must call SQLFetch, or SQLExtendedFetch and (optionally) SQLSetPos to position the cursor on a row of data before it calls SQLGetData. It is possible to use SQLBindCol for some columns and use SQLGetData for others within the same row.

This function can be used to retrieve character or binary data values in parts from a column with a character, binary, or data source-specific data type (for example, data from SQL_LONGVARBINARY or SQL_LONGVARCHAR columns).

Supported

Required

SQLGetFunctions

Returns information about whether a driver supports a specific ODBC function. This function is implemented in the Driver Manager; it can also be implemented in drivers. If a driver implements SQLGetFunctions, the Driver Manager calls the function in the driver. Otherwise, it executes the function itself.

Supported

Required

SQLGetInfo

Returns general information about the driver and data source associated with a connection handle.

Supported

Required

SQLGetStmtOption

Returns the current setting of a statement option.

Supported

Required

SQLGetTypeInfo

Returns information about data types supported by the data source. The driver returns the information in the form of an SQL result set.

Important: Applications must use the type names returned in the TYPE_NAME column in ALTER TABLE and CREATE TABLE statements. SQLGetTypeInfo may return more than one row with the same value in the DATA_TYPE column.

Supported

Required

SQLMoreResults

Determines whether there are more results available on a statement handle containing SELECT, UPDATE, INSERT, or DELETE statements and, if so, initializes processing for those results.

Supported

Not Required

SQLNativeSql

Returns the SQL string as translated by the driver.

Supported

Not Required

SQLNumParams

Returns the number of parameters in an SQL statement.

Supported

Not Required

SQLNumResultCols

Returns the number of columns in a result set.

Supported

Required

SQLParamData

Is used in conjunction with SQLPutData to supply parameter data at statement execution time.

Supported

Not Required

SQLParamOptions

Allows an application to specify multiple values for the set of parameters assigned by SQLBindParameter.

The ability to specify multiple values for a set of parameters is useful for bulk inserts and other work that requires the data source to process the same SQL statement multiple times with various parameter values.

An application can, for example, specify three sets of values for the set of parameters associated with an INSERT statement, and then execute the INSERT statement once to perform the three insert operations.

Supported

Not Required

SQLPrepare

Prepares an SQL string for execution.

Supported

Required

SQLPrimaryKeys

Returns the column names that comprise the primary key for a table.

The driver returns the information as a result set. This function does not support returning primary keys from multiple tables in a single call.

Not Supported

Not Required

SQLProcedureColumns

Returns the list of input and output parameters, as well as the columns that make up the result set for the specified procedures.

The driver returns the information as a result set on the specified statement handle.

Supported

Not Required

SQLProcedures

Procedure is a generic term used to describe an executable object, or a named entity that can be invoked using input and output parameters, and which can return result sets similar to the results returned by SQL SELECT expressions.

Supported

Not Required

SQLPutData

Allows an application to send data for a parameter or column to the driver at statement execution time. This function can be used to send character or binary data values in parts to a column with a character, binary, or data source-specific data type (for example, parameters of the SQL_LONGVARBINARY or SQL_LONGVARCHAR types).

Supported

Not Required

SQLRowCount

Returns the number of rows affected by an UPDATE, INSERT, or DELETE statement or by a SQL_UPDATE, SQL_ADD, or SQL_DELETE operation in SQLSetPos.

Supported

Required

SQLSetConnectOption

Sets options that govern aspects of connections.

Supported

Required

SQLSetCursorName

Associates a cursor name with an active statement handle.

If an application does not call SQLSetCursorName, the driver generates cursor names as needed for SQL statement processing.

Supported

Required

SQLSetParam

In ODBC 2.0, the ODBC 1.0 function SQLSetParam has been replaced by SQLBindParameter.

Supported

Not Required

SQLSetpos

Sets the cursor position in a rowset and allows an application to refresh, update, delete, or add data to the rowset.

Supported

Not Required

SQLSetScrollOptions

Sets options that control the behavior of cursors associated with a statement handle. SQLSetScrollOptions allows the application to specify the type of cursor behavior desired in three areas: concurrency control, sensitivity to changes made by other transactions, and rowset size.

Note: In ODBC 2.0, SQLSetScrollOptions has been superceded by the SQL_CURSOR_TYPE, SQL_CONCURRENCY, SQL_KEYSET_SIZE, and SQL_ROWSET_SIZE statement options. ODBC 2.0 drivers must support this function for backwards compatibility; ODBC 2.0 applications should only call this function in ODBC 1.0 drivers.

If an application calls SQLSetScrollOptions, a driver must be able to return the values of the aforementioned statement options with SQLGetStmtOption.

Supported

Not Required

SQLSetStmtOption

Sets options related to a statement handle. To set an option for all statements associated with a specific connection handle, an application can call SQLSetConnectOption.

Supported

Required

SQLSpecialColumns

Retrieves the following information about columns within a specified table:

  • The optimal set of columns that uniquely identifies a row in the table.

  • Columns that are automatically updated when any value in the row is updated by a transaction.

Supported

Required

SQLStatistics

Retrieves a list of statistics about a single table and the indexes associated with the table. The driver returns the information as a result set.

Supported

Required

SQLTablePrivileges

Returns a list of tables and the privileges associated with each table. The driver returns the information as a result set on the specified statement handle.

Not Supported

Not Required

SQLTables

Returns the list of table names stored in a specific data source. The driver returns the information as a result set.

Supported

Required

SQLTransact

Requests a commit or rollback operation for all active operations on all handle statements associated with a connection.

SQLTransact can also request that a commit or rollback operation be performed for all connections associated with the environment handle.

Supported

Required

Reference
Attachment 
Attachment