ASQL Reference

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).

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.

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.

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.

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.

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:

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).

Initial release of the A-Shell/MySQL connectors for A-Shell/Windows and A-Shell/Linux. Developer package includes: