# Older Development Notes u** Version 1.4.140b — March 2015; Auto-Closing ASQL Connections** 2015 March, A-Shell 6.1.1404: Database connections are now auto-closed when the RUN file terminates (analogous to the auto-closing of ordinary files). Currently this only works for the ASQL/MySQL connector, which must be updated to version 1.4.140. (An ODBC update will follow.) Earlier connectors will just ignore the auto-close request. Note that this is a change in behavior, since database connections were previously left open unless explicitly closed. This behavior was deemed sub-optimal because most applications were not aware of it, with the result that connections would accumulate when programs exited due to other errors. If you want to keep the old behavior, see the new ASFLAG AF\_SQLPERSIST (below). Also note that the auto-close behavior applies to SBXs when the AF\_SBXASRUN flag is set (again, analogous to the auto-closing of regular files). u** Version 1.4.140a — March 2015; ASQL Opcode DISCONNECT\_ALL** 2015 March, A-Shell 6.1.1404: ASQL/MySQL connector version 1.4.140 supports the new opcode SQLOP\_DISCONNECT\_ALL (24). Syntax: **XCALL SQL, SQLOP\_DISCONNECT\_ALL, cmdhdr ** The _cmdhdr_ argument is required as a formality, but the contents are ignored. This opcode is mainly for internal use, to support the auto-close feature described above, but might also be useful in an application error handling routine, particularly if using the AF\_SQLPERSIST flag. If SQLOP\_DISCONNECT\_ALL is passed to a connector that doesn't support the feature, the rc field of the cmdhdr structure will be set to SQLERR\_BADOPCODE (9). Note that the 1.4.140 connector update also fixes a problem parsing the SQLOP\_CONNECT connectstring that was causing connection failure for otherwise valid connection parameters due to multiple spaces between the parameters in the string. u** Tracing Enhancement — January 2013** 2013 January, A-Shell 6.1.1331: You can now set the environment variable ASQLTRACE=1 to activate detailed tracing of SQL.SBR operations. This is equivalent to, but probably more convenient than, setting the SQLCONF\_TRACE flag in the SQLOP\_LIB\_INIT call. u** Version 1.4 — 27 August 2010 ** Add reshdl field to the cmdhdr structure to allow for the manipulation of up to 16 result sets at a time for a single connection. u** Version 1.3 — 28 September 2009** Support dynamic variables (particularly useful when in SQLOP\_FETCH\_ROW). Compatibility note: The 1.2 connectors are only compatible with A-Shell 5.1 versions prior to 1159. Similarly, the 1.3 connectors require A-Shell version 1160 or higher. u** Version 1.2 — 12 April 2009** 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. 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, setc. The noteworthy specific changes in version 1.2 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 SQLOP\_GET\_CONNATR and SQLOP\_SET\_CONNATR (get/set connection attributes), SQLOP\_GET\_STMATR and SQLOP\_SET\_STMATR (get/set statement attributes), and SQLOP\_DATA\_SOURCES (display available data sources) added. • Opcode SQLOP\_OPTIONS (5) has been discontinued and replaced with SQLOP\_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 SQLOP\_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. • SQLOP\_FETCH\_COLUMNS (10) renamed to SQLOP\_GET\_COLDEFS. The new name is slightly more clear (since it retrieves column definition information, not column data), and parallels the new SQLOP\_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_). It’s 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 ASQL 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. u** Version 1.1 — 5 April 2009** 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 ASQL 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). u** Version 1.0 — 11 March 2009** 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.