(PRO) indicates features available only in SQL Tools Pro
Less Fat, More Meat
SQL Tools Version 3 is leaner than Version 2. In spite of all of the powerful new features, the SQL Tools DLLs have grown by less than 7k*. Many SQL Tools functions are measurably faster, too.
Instead of using the SQL Tools DLL, you have the option of using PowerBASIC Units** and Libraries**. This allows you to link SQL Tools directly into your programs instead of distributing a separate DLL file. Even better, PowerBASIC will link only the SQL Tools functions that your program actually needs, so the final result will be much smaller. For example, the SQL_DUMP sample program and Pro DLL require 201k*. Using the PBLIB version you can create a single, self-contained EXE file of just 82k.
The three new SQL_ResultSet functions can be used to retrieve all of the rows of a result set in a single operation. The result set can be returned to your program as...
- a two-dimensional PowerBASIC string array
- a PARSE$-compatible CSV (Comma Separated Values) string
- a CSV disk file (PRO)
- a PowerBASIC PowerArray Object**
- a packed string or
- a packed file (PRO).
The "packed" options are compatible with PowerBASIC's JOIN$ and PARSE functions.
Retrieving values from individual Result Columns has been simplified too. The new functions SQL_ResColString and SQL_ResColNumeric replace ten Version 2 functions (SQL_ResColSInt, SQL_ResColUInt, SQL_ResColBInt, SQL_ResColDate, SQL_ResColTime, SQL_ResColDateTime, SQL_ResColDateTimePart, SQL_ResColFloat, SQL_ResColStr, and SQL_ResColText).
- SQL_ResColNumeric and SQL_ResColString can return PowerBASIC QUAD Integer values, which were not directly supported by Version 2.
- SQL_ResColNumeric automatically returns numbers for result columns that contain strings, such as 9.87 for the string "09.87.654".
- SQL_ResColString automatically returns strings for all numeric values. For example if SQL_ResColNumeric returns 1234, SQL_ResColString will return "1234".
- SQL_ResColString supports SQL_GUID (Globally Unique Identifier) columns, which are compatible with the PowerBASIC GUID functions.
- For very unusual circumstances, the new SQL_ResColRaw (PRO) and SQL_ResColBuffer (PRO) functions can be used to obtain unprocessed data. Examples include user-defined SQL_DECIMAL, SQL_NUMERIC, and SQL_FLOAT columns which do not correspond to a standard SQL data type; Signed Bytes; Unsigned Quad Integers; and virtually any proprietary data format (as long as you know the format).
The new SQL_ResColMemo function makes retrieving Long String data much easier. SQL_ResColBLOB (PRO) does the same thing for Binary Large OBjects, making it simple to retrieve images, sounds, entire documents, and even executable programs that are stored a database. A "Direct To File" option (PRO) is available in both functions, so your program doesn't have to handle the cumbersome data directly; just give SQL Tools the name of the disk file you want to create.
The new SQL_UpdateMemo function greatly simplifies the process of storing Long Strings in a database, and SQL_UpdateBLOB (PRO) stores Binary Large OBjects. A "Direct From File" option is available, so your program doesn't have to handle the data directly. Supply the name of a disk file, and SQL Tools will store the file in your database.
The new SQL_DateTimePart and SQL_DateTimePartStr functions are far more flexible than the old (Version 2) SQL_ResColDateTimePart function.
- They can be used to format virtually any date/time value, not just values from Result Columns.
- They can be used to obtain many useful numeric values such as the Quarter, the Day Of Year, and six varieties of Julian Dates including the Unix/Linux, NASA, and Microsoft Excel standards (PRO).
- SQL_DateTimePartStr returns many different non-numeric values, including Day/Month names and abbreviations, multi-number values such as "12:34:56 pm", and century names like "21st".
- Both functions are fully compatible with PowerBASIC's new PowerTime** Object.
Access doesn't support some important SQL features like Primary Keys (PRO), Foreign Keys (PRO), and Stored Procedures (PRO) in the normal, ODBC-standard way, so SQL Tools Version 3 includes comprehensive work-arounds for those missing features.
All of the SQL_Info and SQL_Attrib functions now return label and formatting strings as well as values. For example...
- SQL_TblInfoStr(%INFO_LABEL, %TABLE_NAME) returns the label string "TABLE_NAME"
- SQL_TblInfoStr(%INFO_FORMAT, %TABLE_NAME) returns "STR" to indicate that the return value of %TABLE_NAME is a string.
- These features have been used internally to enhance the SQL Tools Trace functions (see below) and they can be very useful in your programs too. Check out the new SQL_INVENTORY.BAS sample program for an example.
100% of the SQL_Info functions now support Driver-Defined fields.
SQL Statement Auditing is provided by the new SQL_Audit (PRO) and SQL_AuditStr (PRO) functions. SQL Tools can now create Audit Files that record all of the SQL Statements that your program executes, including workstation, username, and date/time stamps. SQL Tools Error Messages are automatically saved in the same file, and your programs can easily add additional information such as the number of rows affected by each statement.
The new SQL_DBMS function returns numeric values like %DBMS_ACCESS and %DBMS_MYSQL that identify the type of database that your program is using, and the SQL_DBMSName function returns strings like "Microsoft Access (MS Corp)" and "MySQL (Oracle)". Over 50 drivers are currently recognized.
The SQL Tools Trace Files have been greatly enhanced. Six different levels of tracing are now available, and most of the numeric values in the Trace File are translated into words (for example SQL_SUCCESS instead of 0). ODBC-level tracing, while rarely necessary, has also been made easier to use.
Several convenience features have been added to Version 3, like SQL_Fail (a complement to SQL_Okay); SQL_ToolsVersionStr, SQL_DataTypeStr, SQL_CurrentTrace, SQL_EnvironAttribStr, SQL_StatementAttribStr, SQL_TblStatInfoStr, SQL_ParamInfoStr (PRO), SQL_CurrentThread (PRO), SQL_SaveFile (PRO) (for creating BLOB files, among other things); and SQL_TableRowCount (PRO).
Some SQL Tools functions have new parameters to make them more flexible. For example, SQL_OpenDB now has a parameter that controls the Prompt Type.
Many SQL Tools functions are now easier to use because they have OPTIONAL parameters for rarely-used features. Other functions have OPTIONAL parameters for the most commonly used operations, for example SQL_Fetch (with no parameter) now does the same thing as SQL_Fetch %NEXT_ROW.
Several frequently-used SQL Tools functions now have an OPTIONAL parameter called sIgnoreErrors$ which makes it even easier to handle predictable errors and advisories.
Many function name have been simplified, and many equates have been renamed to make them easier to remember. For example, all of the Version 2 UInt and SInt suffixes have been eliminated. But don't worry, SQL Tools Version 3 provides an extra #INCLUDE file that recognizes all of the old names, so updating an existing program to Version 3 is usually very simple.
Like the newest PowerBASIC compilers, SQL Tools Version 3 has significantly enhanced support for Unicode and the databases that use it.
SQL Tools Version 3 can handle larger, more complex database applications than Version 2.The Pro version can manage up to 1,024 concurrent statements -- perfect for threaded, server/client, and web site applications -- and the Standard version can have up to 4 concurrent statements. Version 2 was limited to 256 and 2, respectively. (Your actual runtime capabilities are of course dependent on the runtime hardware.)
The SQL Tools documentation is now provided in HTML/CHM, PDF, HLP, and online formats. The docs have grown by almost 25% compared to Version 2.
* Refers to the "No Trace" Pro DLL.
** Indicates PowerBASIC features that are available only when using PB/Win 10 or PB/CC 6 and above.
