Database Functions
Each SQL Tools function has been assigned to a "family", to make it easier to find related functions. Each page of the SQL Tools Help File's Reference Guide lists the function's family, so you can easily look up related functions.
(For information about all of the "duplicate" functions like SQL_Initialize and SQL_Init below, see Why Are There Two Of Everything?)
Configuration Family
SQL Tools Initialization and Shutdown functions, plus functions that allow you to set and get various "option" values, which are used to configure SQL Tools.
Program startup and shutdown:
SQL_Initialize, SQL_Init
SQL_ShutdownSQL Tools Options:
SQL_OptionSInt, SQL_OptionStr
SQL_SetOptionSInt, SQL_SetOptionStr
SQL_OptionResetAllInfo Export/Import
SQL Tools Pro Only...
SQL_InfoExport
SQL_InfoImportThread startup and shutdown:
SQL Tools Pro Only...
SQL_Thread
Environment Family
Functions for setting and getting attributes and information about the overall ODBC environment that your program operates in. These values include the ODBC version, the names of the various ODBC drivers and datasources that are available to your program, and information about things like "connection pooling", which affect all of the databases in the environment.
ODBC Environment Attributes:
SQL_SetEnvironAttribSInt
SQL_EnvironAttribSIntAvailable ODBC Drivers:
SQL Tools Pro Only...
SQL_DriverCount
SQL_DriverInfoStr
SQL_DriverNumberAvailable ODBC Datasources:
SQL Tools Pro Only...
SQL_DataSourceCount
SQL_DataSourceInfoStr
SQL_DataSourceNumber
Use Family
Function that allow you to set and get the Current Database and Current Statement numbers, which are used by all of the SQL Tools abbreviated functions.
SQL_UseDB
SQL_UseStmt
SQL_UseDBStmt
SQL_CurrentDB
SQL_CurrentStmt
Database Open/Close Family
Functions related to the opening and closing of Databases.
SQL_NewDatabaseNumber, SQL_NewDBNumber
SQL_OpenDatabase, SQL_OpenDB
SQL_OpenDatabase1, SQL_OpenDatabase2
SQL_DatabaseIsOpen, SQL_DBIsOpen
SQL_CloseDatabase, SQL_CloseDB
Database Info/Attribute Family
Functions that allow you to obtain various Database Attribute and Information values, and to set Database Attribute values. (Generally speaking, SQL Tools "Attribute" settings can be changed, and "Information" settings cannot be changed.)
General Database Information:
SQL_DatabaseInfoStr, SQL_DBInfoStr
SQL_DatabaseInfoUInt, SQL_DBInfoUIntInformation about a database's basic ODBC capabilities:
SQL_FunctionAvailable, SQL_FuncAvail
Database Attributes:
SQL_DatabaseAttribStr, SQL_DBAttribStr
SQL_DatabaseAttribUInt, SQL_DBAttribUIntMost sub-function are limited to SQL Tools Pro Only...
SQL_SetDatabaseAttribUInt, SQL_SetDBAttribUInt
SQL Tools Pro Only...
SQL_SetDatabaseAttribStr, SQL_SetDBAttribStr
Information about the Data Types that are supported by a database:
SQL Tools Pro Only...
SQL_DatabaseDataTypeCount, SQL_DBDataTypeCount
SQL_DatabaseDataTypeInfoSInt, SQL_DBDataTypeInfoSInt
SQL_DatabaseDataTypeInfoStr, SQL_DBDataTypeInfoStr
SQL_DatabaseDataTypeNumber, SQL_DBDataTypeNumberDatabase Transaction Mode:
SQL Tools Pro Only...
SQL_DatabaseAutoCommit, SQL_DBAutoCommit
SQL_EndTransaction, SQL_EndTrans
Table Info Family
Functions that allow you to obtain information about the tables in a database, such as the number of tables, their names, their Table Types, and any remarks that the table's creator included in the database.
General Table Information:
SQL_TableCount, SQL_TblCount
SQL_TableInfoSInt, SQL_TblInfoSInt
SQL_TableInfoStr, SQL_TblInfoStr
SQL_TableNumber, SQL_TblNumberTable Statistics:
SQL Tools Pro Only...
SQL_TableStatisticSInt, SQL_TblStatSInt
Table Privileges:
SQL Tools Pro Only...
SQL_TablePrivilegeCount, SQL_TblPrivCount
SQL_TablePrivilegeInfoStr, SQL_TblPrivInfoStr
Table Column Info Family
(For functions related to Result Columns,
see the Result Column family below.)
Functions that allow you to obtain information about the columns in a table, such as how many columns there are, their names and types, and whether or not they are nullable.
General Table Column Information:
SQL_TableColumnCount, SQL_TblColCount
SQL_TableColumnInfoSInt, SQL_TblColInfoStr
SQL_TableColumnInfoStr, SQL_TblColInfoSInt
SQL_TableColumnNumber, SQL_TblColNumberColumn Privileges:
SQL Tools Pro Only...
SQL_ColumnPrivilegeCount, SQL_ColPrivCount
SQL_ColumnPrivilegeInfoStr, SQL_ColPrivInfoStrUnique Columns:
SQL Tools Pro Only...
SQL_UniqueColumnCount, SQL_UniqColCount
SQL_UniqueColumnInfoStr, SQL_UniqColInfoStr
SQL_UniqueColumnInfoSInt, SQL_UniqColInfoSIntAutoColumns:
SQL Tools Pro Only...
SQL_AutoColumnCount, SQL_AutoColCount
SQL_AutoColumnInfoStr, SQL_AutoColInfoStr
SQL_AutoColumnInfoSInt, SQL_AutoColInfoSIntColumns which are indexed:
SQL Tools Pro Only...
SQL_IndexCount, SQL_IndxCount
SQL_IndexInfoStr, SQL_IndxInfoStr
SQL_IndexInfoSInt, SQL_IndxInfoSIntColumns that are used as Primary Keys:
SQL Tools Pro Only...
SQL_PrimaryKeyCount, SQL_PrimKeyCount
SQL_PrimaryKeyInfoStr, SQL_PrimKeyInfoStr
SQL_PrimaryKeyInfoSInt, SQL_PrimKeyInfoSIntColumns in other tables that are linked to this table:
SQL Tools Pro Only...
SQL_ForeignKeyCount, SQL_FornKeyCount
SQL_ForeignKeyInfoStr, SQL_FornKeyInfoStr
SQL_ForeignKeyInfoSInt, SQL_FornKeyInfoSInt
Statement Open/Close Family
Functions related to the opening and closing of Statements. (SQL Tools handles most statement open/close operations automatically. These functions allow you to take control of the process, for special circumstances.)
SQL_NewStatementNumber, SQL_NewStmtNumber
SQL_OpenStatement, SQL_OpenStmt
SQL_StatementIsOpen, SQL_StmtIsOpen
SQL_CloseStatement, SQL_CloseStmt
Statement Family
Functions related to SQL statements.
SQL_Statement, SQL_Stmt
SQL_FetchResult, SQL_Fetch
SQL_EndOfData, SQL_EODSQL Tools Pro Only...
SQL_AsyncStatement, SQL_AsyncStmt
SQL_AsyncStatus
SQL_FetchRelative, SQL_FetchRel
SQL_Bookmark, SQL_Bkmk
SQL_StatementCancel, SQL_StmtCancel
SQL_MoreResults, SQL_MoreRes
SQL_BulkOperation, SQL_BulkOp
SQL_SetPosition, SQL_SetPos
Statement Info/Attrib Family
Functions that allow you to obtain SQL statement Attribute and Information values, and to set statement Attributes. (Generally speaking, SQL Tools "Attribute" settings can be changed, and "Information" settings cannot be changed.)
General Information about a statement:
SQL_StatementInfoStr, SQL_StmtInfoStr
SQL_StatementNativeSyntax, SQL_StmtNativeSyntaxStatement Attributes:
SQL_StatementMode, SQL_StmtMode
SQL_ResetStatementMode, SQL_ResetStmtMode
SQL_StatementAttrib, SQL_StmtAttribSQL Tools Pro Only...
SQL_SetStatementAttrib, SQL_SetStmtAttrib
Named Cursors:
SQL Tools Pro Only...
SQL_NameCursor, SQL_NameCur
SQL_CursorName, SQL_CurName
Statement Binding Family
Functions related to the Bound Parameters of SQL statements:
SQL Tools Pro Only...
SQL_StatementParameterCount, SQL_StmtParamCount
SQL_ParameterInfoUInt, SQL_ParamInfoUInt
SQL_BindParameter, SQL_BindParam
SQL_NextParameter, SQL_NextParam
SQL_LongParameter, SQL_LongParam
Stored Procedure Family
Functions related to Stored Procedures, which are pre-compiled SQL Statements that are stored in a database:
SQL Tools Pro Only...
SQL_ProcedureCount, SQL_ProcCount
SQL_ProcedureInfoStr, SQL_ProcInfoStr
SQL_ProcedureInfoSInt, SQL_ProcInfoSIntInformation about the parameters that a Procedure requires, and the result columns that it produces:
SQL Tools Pro Only...
SQL_ProcedureColumnCount, SQL_ProcColCount
SQL_ProcedureColumnInfoStr, SQL_ProcColInfoStr
SQL_ProcedureColumnInfoSInt, SQL_ProcColInfoSInt
Result Column Binding Family
Functions related to the binding of result columns. (This family is rarely used because of the SQL Tools "Autobind" function, which handles most binding operations.)
SQL_AutoBindColumn, SQL_AutoBindCol
SQL_ManualBindColumn, SQL_ManualBindCol
SQL_UnbindColumn, SQL_UnbindColSQL Tools Pro Only...
SQL_DirectBindColumn, SQL_DirectBindCol
SQL_ResultColumnBufferPtr, SQL_ResColBufferPtr
SQL_ResultColumnIndPtr, SQL_ResColIndPtr
Result Count Family
Functions that provide general information about a statement's Result Set, such as the number of Rows and Columns in the set.
SQL_ResultRowCount, SQL_ResRowCount
SQL_ResultColumnCount, SQL_ResColCount
Result Column Family
Functions that provide actual values (i.e. data) from the columns of a result set, provide information about a column's Indicator value, and provide information about the columns themselves (type, name, etc.).
Result Column Values:
SQL_ResultColumnSInt, SQL_ResColSInt
SQL_ResultColumnUInt, SQL_ResColUInt
SQL_ResultColumnBInt, SQL_ResColBInt
SQL_ResultColumnFloat, SQL_ResColFloat
SQL_ResultColumnStr, SQL_ResColStr
SQL_ResultColumnText, SQL_ResColText
SQL_LongResultColumn, SQL_LongResColInformation about Result Columns:
SQL_ResultColumnInfoSInt, SQL_ResColInfoSInt
SQL_ResultColumnInfoStr, SQL_ResColInfoStr
SQL_ResultColumnType, SQL_ResColType
SQL_ResultColumnSize, SQL_ResColSize
SQL_ResultColumnLen, SQL_ResColLen
SQL_ResultColumnNumber, SQL_ResColNumberResult Column Indicator values:
SQL_ResultColumnNull, SQL_ResColNull
SQL_ResultColumnMore, SQL_ResColMore
SQL_ResultColumnInd, SQL_ResColInd
Error/Trace Family
Various functions related to error handling and tracing.
SQL_Error
SQL_ErrorClearOne
SQL_ErrorClearAll
SQL_ErrorColumnNumber
SQL_ErrorDatabaseNumber
SQL_ErrorCount
SQL_ErrorFunction
SQL_ErrorIgnore
SQL_ErrorText
SQL_ErrorNativeCode
SQL_ErrorNumber
SQL_ErrorQuickOne
SQL_ErrorQuickAll
SQL_ErrorSimulate
SQL_ErrorStatementNumber
SQL_ErrorTime
SQL_State
SQL_Trace
SQL_TraceSInt, SQL_TraceStrSQL Tools Pro Only...
SQL_ErrorStr
SQL_Diagnostic
SQL_AsyncErrors
SQL_OnErrorCall
Utility Family
Various utility functions, such as text-to-binary and binary-to-text conversions, a "string interpreter" that simplifies the use of certain characters in strings (such as quotation marks), and a simple Message Box function.
SQL_Binary
SQL_Text
SQL_TextDateTime
SQL_TextDate
SQL_TextTime
SQL_MsgBox
SQL_MsgBoxButton
SQL_IString
SQL_LimitTextLength
SQL_Okay
SQL_SelectFile
SQL_ToolsVersion
Get Info Family
SQL Tools Internal "Get" Functions. These functions are rarely used in programs because SQL Tools automatically uses these functions (internally) whenever an Info function is used. When an Info function is first used, SQL Tools caches all of the information that is related to the function, for faster access in the future. The Get functions can be used to force SQL Tools to "refresh" the Info data, if you have reason to believe that, while your program is running, a table has been added, a column has been deleted, etc.
SQL_GetTblCols, SQL_GetTableColumns
SQL_GetTblInfo, SQL_GetTableInfoSQL Tools Pro Only...
SQL_GetDataSources, SQL_GetDrivers
SQL_GetAutoCols, SQL_GetAutoColumns
SQL_GetColPrivs, SQL_GetColumnPrivileges
SQL_GetDBDataTypes, SQL_GetDatabaseDataTypes
SQL_GetFornKeys, SQL_GetForeignKeys
SQL_GetIndxes, SQL_GetIndexes
SQL_GetPrimKeys, SQL_GetPrimaryKeys
SQL_GetProcCols, SQL_GetProcedureColumns
SQL_GetProcs, SQL_GetProcedures
SQL_GetTblPrivs, SQL_GetTablePrivileges
SQL_GetUniqCols, SQL_GetUniqueColumns
Handle Family
These functions can be used to obtain certain window handles, plus the actual ODBC handles of the ODBC Environment, each ODBC database connection, and each ODBC statement.
SQL_hParentWindow
SQL Tools Pro Only...
It should not be necessary to use these functions unless you wish to write API-level functions that SQL Tools does not provide. (Of which there are very, very few.):
SQL_hDatabase, SQL_hDB
SQL_hStatement, SQL_hStmt
SQL_hEnvironment
Why Are There Two Of Everything?
When you look at the list of SQL Tools functions, you will probably notice that there are two of just about everything. Here’s why…
SQL Tools is capable of handling extremely complex programs. In fact, SQL Tools Pro could theoretically be used to write a program that uses 256 different databases at the same time, and where each database has 256 SQL statements that are active, all at the same time. (A much more likely scenario would be a program that uses several databases with one active statement at a time, or one database with several active statements, but anything is possible.)
But most of the time, most programs will use a single database and a single statement at a time.
Here is an example of "two of everything"…
One of the most commonly used SQL Tools functions is called SQL_Statement. It is used to execute SQL statements, to tell a database what to do. To use the SQL_Statement function, you need to specify a Database Number (from 1-256), a Statement Number (from 1-256), a parameter like PREPARE or EXECUTE, and a string that contains the SQL statement.
Since most of the time you will be dealing with Database #1 and Statement #1, it can be very tedious to type 1,1 at the beginning of every single function's parameter list, so SQL Tools provides a complete set of "abbreviated" functions that use default values for the database number and statement number.
If a function name contains the word "Database", "Statement", 'Table", "Column", or "Result" it is a verbose function that requires you to specify a Database number and/or a Statement Number.
On the other hand, if a function name contains the abbreviation "DB", "Stmt", "Tbl", "Col", or "Res" it is an abbreviated function that does not allow the Database Number and Statement Number to be specified as parameters. (Please note that certain words like "Info" are never spelled out in function names and do not indicate an abbreviated function.)
Here is a specific example of a verbose function…
SQL_Statement 1, 1, EXECUTE, "SELECT * FROM MYTABLE"
And here is the abbreviated function that would perform precisely the same operation…
SQL_Stmt EXECUTE, "SELECT * FROM MYTABLE"
The SQL_Statement and SQL_Stmt functions are called "twins" and they share a page in the documentation.
If you are writing a program that uses one database at a time, with one statement at a time, we recommend that you use the abbreviated functions. It will save you a lot of typing, and help reduce errors.
If you are writing a more complex program, you have a choice:
Use the verbose functions for everything,
Use the SQL_UseDB and SQL_UseStmt functions to specify which database and statement you want the abbreviated functions to handle.
For example, a program could use Database 1, Statement 3 followed by Database 2, Statement 9 in this way…
SQL_Statement 1,3, EXECUTE, "SELECT * FROM MYTABLE"
SQL_Statement 2,9, EXECUTE, "SELECT * FROM YOURTABLE"
…or...
Use the abbreviated functions and the "Use" functions like this…
SQL_UseDB 1
SQL_UseStmt 3
SQL_Stmt EXECUTE, "SELECT * FROM MYTABLE"SQL_UseDB 2
SQL_UseStmt 9
SQL_Stmt EXECUTE, "SELECT * FROM YOURTABLE"If you often switch the default Database number and Statement number at the same time, you can also use this function…
SQL_UseDBStmt 2,9
…to change both at once.
The advantage of using the SQL_Use functions is that they are "sticky". In other words, once you use SQL_UseDB 2, all of the abbreviated functions will continue to use Database 2 until you use SQL_UseDB again to change the default. In that way, you can use the SQL_Use functions to specify a database or statement, and then perform a large number of abbreviated functions.
It is also possible to mix the verbose and abbreviated functions. For example if a program did 90% of its work with one database and 10% with a handful of others, you could use the abbreviated functions to handle Database 1, Statement 1, and use the verbose functions for the other 10%. The use of verbose functions does not affect the use of the SQL Use functions.
