The primary changes in 1.4 were to add support for prepared statements, multiple result sets, nested opens and closes, and tracing. The CMDHDR structure layout has changed and thus all programs using ASQL have to be recompiled.
Connector status/availability:
LIBASHMYSQL.DLL (Windows native MySQL connector): 1.4.138
Libashmysql.so.1.4.138 (Linux native MySQL connector)
|
1.4.120 |
17-Aug-10 |
Support 16 result sets per connection. Added field reshdl to the CMDHDR structure to allow applicatio to specify which result set to use. |
|
1.4.123 |
10-Sep-10 |
Maintain separate sets of field definitions for each result set
and remove the 32 field-per-result-set limitation. Support
prepared statements via new opcodes: SQLOP_PST_PREPARE (20)
Add field psthdl to the CMDHDR structure to allow application to create and reference individual prepared statements. Maximum number of result set handles (default 16) and prepared statements (default 32) can now be set by putting the desired maximum into the reshdl and/or psthdl fields of the CMDHDR structure on the SQLOP_INIT_LIB call. |
|
1.4.128 |
28-Oct-10 |
Support reshdl = -1 in SQLOP_QUERY to request a new result set. |
|
1.4.129 |
08-Nov-10 |
Minor correction of mismatch between certain error codes in the connector and in SQL.DEF. (SQL.DEF was correct, so no change or recompile required on the application side.) |
|
1.4.132 |
20-Jan-12 |
Return ASQLERR_CLOSELIB after SQLOP_CLOSE_LIB so caller can free the lib. Implement tracing (to ashlog.log) via SQLCONF_TRACE flag in SQLOP_INIT_LIB. Return SQLERR_NESTIGNORE on nested close. Return SQLERR_TOOMANYRESHDLS if limit exceeded when requesting new handle. |
|
1.4.138 |
09-May-13 |
Several improvements to the tracing logic. Work around corruption issue with certain SQLOP_FETCH_COLUMNS scenarios under Linux. Fix invalid handles issue related to closing/reopening the library. |
No changes to the connectors. Several minor changes/improvements to the sample files and test programs released in tstsql16.zip. See notes in sqldevnote.txt (within zip).
The changes in version 1.3 were almost exclusively for the purpose of supporting dynamic variables in A-Shell. The introduction of dynamic variables (around 5.1.1159 and more or less stabilized around 5.1.1161) required a change to the XCALL interface, rendering the 1.2 and earlier connectors incompatible. (So you must use the 1.3 connectors with A-Shell 5.1.1159.5+, and the 1.2 connectors with earlier A-Shell.) Dynamic variables are now supported in all the expected places, but are most useful in conjunction with the SQLOP_FETCH_ROW operation (where you often don’t know at compile time how big the returned fields are going to be.) The test programs SQLTEST2 and SQLTEST4 have been enhanced to allow compilation either with fixed variables or dynamic variables (for the latter, specify the /C:DYNVARS=1 compiler switch).
Connector status/availability:
LIBASHMYSQL.DLL (Windows native MySQL connector): 1.3.118
Libashmysql.so.1.3.118 (Linux native MySQL connector)
|
1.1.109 |
04-Mar-09 |
SQLINFO_ROWCOUNT was returning row count in info1 instead of info2 |
|
1.1.110 |
08-Mar-09 |
Add SQLINFO_CONID, SQLINFO_CONVER, SQLINFO_CONDESCR to return A-Shell connector info; rename connector file to LIBASHMYSQL |
|
1.1.111 |
08-Apr-09 |
Change SQLOP_FETCH_COLUMNS to SQLOP_GET_COLDEFS |
|
1.1.112 |
09-Apr-09 |
Fix GPF if SQLOP_FETCH_ROWS used w/o prior SQLOP_GET_COLDEFS |
|
1.2.113 |
13-Apr-09 |
Changes to cmdhdr structure (change b,2 and b,4 variables to I,2 and I,4); adjust version format; clean up SQLOP_OPTIONS (now _SET_CONNATR); support other field bindings besides string (in _FETCH_ROW), support opflags in _CONNECT |
|
1.2.114 |
26-Apr-09 |
Clear field defs on each new query (might have resulted in using the wrong field defs in subsequent query if result set structure changed and SQLOP_GET_COLDEFS not used first.) |
|
1.2.115 |
16-May-09 |
Minor correction of mismatch between certain error codes in the connector and in SQL.DEF. (SQL.DEF was correct, so no change or recompile required on the application side.) |
|
1.3.117 |
10-Oct-09 |
Adjust connector interface to support changes in A-Shell 5.1.1160 XCALL interface, required for dynamic variables. Dynamic variables now supported by connectors (particularly useful for SQLOP_FETCH_ROW) |
|
1.3.118 |
14-Oct-09 |
Connection parameters (host, user, password) may now (optionally) be encrypted (using MX_PWCRYPT) before passing to SQLOP_CONNECT |
LIBASHODBC.DLL (Windows ODBC connector): 1.3.105
Libashodbc.so.1.3.107 (Linux ODBC connector)
|
1.1.100 |
09-Mar-09 |
Initial build |
|
1.1.101 |
09-Apr-09 |
Fix GPF if SQLOP_FETCH_ROWS used w/o prior SQLOP_GET_COLDEFS |
|
1.2.102 |
10-Apr-09 |
Changes to cmdhdr structure (change b,2 and b,4 variables to I,2 and I,4); adjust version format; clean up SQLOP_OPTIONS (now _SET_CONNATR); support other field bindings besides string (in _FETCH_ROW) |
|
1.2.103 |
12-Apr-09 |
Implement SQLOP_GET_CONNATR,_SET_CONNATR, _GET_STMATR, _SET_STMATR |
|
1.2.104 |
26-Apr-09 |
Avoid segfault in MySQL/ODBC connector when hitting end of list of data sources using SQLOP_DATA_SOURCES; clear field definitions on new query to avoid re-using incompatible definitions if result set structure changed. |
|
1.2.105 |
16-May-09 |
Minor correction of mismatch between certain error codes in the connector and in SQL.DEF. (SQL.DEF was correct, so no change or recompile required on the application side.) |
|
1.3.107 |
10-Oct-09 |
Adjust connector interface to support changes in A-Shell 5.1.1160 XCALL interface, required for dynamic variables. Dynamic variables now supported by connectors (particularly useful for SQLOP_FETCH_ROW) |
|
1.3.108 |
14-Oct-09 |
Connection parameters (host, user, password) may now (optionally) be encrypted (using MX_PWCRYPT) before passing to SQLOP_CONNECT |
Notes on the ODBC connector implementation:
· In general, the current implementation should be considered “preliminary”. It seems to be approximately as capable as the MySQL native connector, but probably only uses/supports 40% of the ODBC specification. The ODBC specification is quite complex, then made even more so by the fact that each driver, database, and data source implements some subset (or possibly even a superset) of the specification. So operations that work in one environment, say, SQL Server using the Microsoft Windows data source, might not work in another, say, SQL Server using a third-party Linux data source, or MySQL using the MySQL ODBC connector for Windows. Further differences show up even in the same environment when dealing with data sources of fundamentally different capabilities (ranging from “real databases” such as SQL Server, to personal databases like Access, to non-databases like Excel). So, we expect (and you should expect) a considerable period of experimentation and refinement as we figure out what works and how best to deal with the data sources of interest. (The SQRY1 utility will probably be one of the most useful for comparing capabilities and behavior between data sources.)
· We decided to use the ODBC interface for SQL Server, rather than some more “native” interface (like the TDS protocol), mostly on Microsoft’s recommendation. As we understand it, the ODBC interface is the only one which they are fully committed to supporting going forward, and as for concerns about it being inefficient (compared to native interfaces), virtually all of the higher-level interfaces offered by Microsoft (such as ADO and OLEDB) are actually wrappers around ODBC. So, while the ODBC specification does suffer from trying to be all things to all data, Microsoft appears to be committed to making it work as “the” interface to SQL Server. The fact that it also provides a way to talk to many other kinds of databases is just a bonus. That said, from a licensing and support standpoint, we are not yet clear about whether we are going to make any distinctions between the target data bases linked to via the A-Shell ODBC connector(s).
· We are targeting the 3.x ODBC standard. (Therefore it might not work at all with older ODBC drivers and data sources.)
· Data sources we have tested (at least minimally): SQL Server (sqlncli10.dll 10.0.1600), Access (odbcjt32.dll 6.0.6001), Excel (odbcjt32.dll 6.0.6001), MySQL (myodbc5.dll 5.1.5). Under Linux, we have only tested the MySQL ODBC connector libmyodbc5-5.1.5.so connecting to a local database.
Noteworthy specific changes in version 1.3 that application developers should review are as follows:
· SQLTEST1 updated to check connector version and to support ODBC.
· SQLTEST2 and SQLTEST4 updated to support dynamic variables (via compilation with /C:DYNVARS=1). In particular, see the SQLOP_FETCH_ROW options in SQLTEST2. (The code doesn’t look any different, but all three modes – array, discrete fields, and entire row now adjust to whatever the size of the data is.
· SQLTEST4 has been tinkered with to illustrate escaping of troublesome characters in text fields.
The changes in version 1.2 were directed at bringing the ODBC and native MySQL connector interfaces into better alignment. These include some minor changes to the cmdhdr structure (to support negative integer values), renaming of some of the symbols in SQL.DEF, and renumbering of a couple of the opcodes. (In general, we intend to at least try to avoid changes that affect compatibility, but since the connector interface is still in its initial beta phase, we figured it was better to make ”improvements” for the long run, rather than “hacks”.) In order to minimize confusion, we’ve recompiled all the utility programs and subroutines and given them all versions starting with 1.2. In addition, most of them now check their own version against the connector version and warn you if they are not up to date. (A standard utility function, Fn’SQL’Check’Ver$, has been provided in the ++include module fnsqlckver.bsi for this purpose.)
At this point, the A-Shell ODBC connector is approximately as capable as the original A-Shell MySQL connector, which is to say that it can be used to execute SQL statements to query, insert, modify, and delete data in compatible databases for which you have the appropriate privileges. Some of the weaknesses remaining to be addressed in subsequent releases are: improved data binding, record-level operations, improved programming and runtime efficiencies, improved record-set handling and updating, improved consideration of concurrency issue, etc.
Noteworthy specific changes in version 1.2 that application developers should review are as follows:
· Rename the SQL_TYPE_xxx (data type) symbols (in SQL.DEF) to ASQL_TYPE_xxxx. The prefix “SQL_” is now reserved for symbols whose values match the Windows ODBC definitions. This is perhaps not of consequence to Basic applications, but is important to the interface, since those values can be passed directly through to the client API.
· New opcodes ASQLOP_GET_CONNATR and ASQLOP_SET_CONNATR (get/set connection attributes), ASQLOP_GET_STMATR and ASQLOP_SET_STMATR (get/set statement attributes), and ASQLOP_DATA_SOURCES (display available data sources) added.
· Opcode ASQLOP_OPTIONS (5) has been discontinued and replaced with ASQLOP_SET_CONNATR (15). The functionality for the MySQL connector is unchanged, but since the operation is equivalent to what ODBC refers to as setting “Connection Attributes”, and since we already added ASQLOP_SET_STMATR for setting “Statement Attributes”, we decided to adopt the ODBC semantics here.
· Change the B,2 and B,4 fields in the cmdhdr (ST_SQL_CMDHDR) structure to I,2 and I,4 to allow for negative values.
· ASQLOP_FETCH_COLUMNS (10) renamed to ASQLOP_GET_COLDEFS. The new name is slightly more clear (since it retrieves column definition information, not column data), and parallels the new ASQLOP_SET_COLDEFS (11).
· SQLCON.SBX (standard connection dialog) enhanced to support ODBC and offer a dialog to display and select from the available data sources.
· XTRSQ1.SBX (Xtree Standard Query utility) now saves queries in a file SQRY1.TXT and a “History” button has been added to allow you to review / edit / copy previous queries. (Extremely handy when experimenting and learning as working queries can be hard to remember.)
· SQLTEST1 updated to check connector version and to support ODBC.
· SQLTEST2 updated to support ODBC.
· New sample program SQLTEST4 illustrates setting statement attributes, creating a new table, inserting, modifying, deleting records.
· New Fn’SQL’State$() function (fnsqlstate.bsi) to display the text associated with the 5 character standard SQLSTATE (cmdhdr.sqlstate). Its usage is illustrated in SQLTEST2 and SQLTEST4.
· New symbol ASQL_CUR_CONNECTOR_VER in SQL.DEF defined to specify the current major.minor version of the A-Shell/SQL connector developer tools. New function Fn’SQL’Check’Ver() (fnsqlckver.bsi) simplifies checking if the compiled connector is current relative to the SQL.DEF.
· New Fn’SQL’StmAtr$() function (fnsqlatr.bsi) (work in progress) helps debugging by displaying the symbolic name of a value associated with a particular attribute.
· New sample program SQLCUSTST.RUN (wrapper for SQLCUS.SBX) illustrates looking up a customer in the sakila database starting with a wildcard search and finishing with and XTREE display to select the desired one. Sample provided by Stephen Funkhouser.
Version 1.1 introduces support for ODBC via a new connector, LIBASHODBC. Initially, it is only available for Windows clients, although we will soon investigate a Linux version. The ODBC interface is considerably more complex than the native MySQL interface (since it essentially tries to allow for all possible kinds of databases and has evolved over decades), and our initial ODBC connector only begins to scratch the surface, but roughly provides a level of capability similar to the existing MySQL connector. The main difference is that while MySQL by default returns the entire result set to the client and is thus able to inform you how many rows were selected, under ODBC, by default the result set remains on the server (similar to the SQLOP_QUERY operation in MySQL with the QRYF_NOSTORE flag), so the number of rows is unknown until they are fetched.
In order to deal with the problem of displaying a result set without knowing in advance how big it is (needed in the XTRSQ1.SBX module within the SQRY.RUN utility), we implemented a new A-Shell/Basic capability to re-dimension a dynamic array created with DIMX. Although this capability is probably not important to more typical database application programs, version 1.1 of the A-Shell/SQL connectors require at least version 5.1.1145 of A-Shell.
At this point, the interface is capable of supporting the typical kinds of queries you can perform in the SQRY1 utility, but will probably not be made available to the public until another round of changes are made (probably resulting in another version).
Initial release of the A-Shell/MySQL connectors for A-Shell/Windows and A-Shell/Linux. Developer package includes:
· LIBASHMYSQL.DLL 1.0.110.0 – connector for Windows
· libashmysql.so – connector (dynamic library) for Linux
· SQL: ersatz directory containing sample/development files.
· SQLTEST1 – sample program to test ability to load connector
· SQLTEST2 – sample program illustrating simple operations
· SQRY1 – sample GUI program allows generalized queries and displays results in XTREE
· XTRSQ1.SBX – utility routine used by SQRY1
· SQLCON.SBX – utility routine to prompt for connection credentials and connect to a database.