Included with ASQL is a set of sample programs, header files, and some utility routines. To get them, download the latest version of sqltst##.zip from the ASQL folder on our server. and unzip it into your SQL: ersatz directory (normally DSK0:[909,60]).
The main header file, SQL.DEF, should be included into every program using SQL. (You can copy it to ASHINC: where most DEF files reside, but given how often it is likely to change in the near term, it will be easier to just leave it in the SQL: directory, which is where the sample programs will expect to find it.
SQLTEST1.RUN: This is the first test program to run when installing or updating, to quickly determine whether the connector and client library is installed properly. A normal, successful execution of the program looks something like this:
.LOG SQL:
.RUN SQLTEST1
SQLTEST1 - Verify existence of ASQL connector and database client library
Select database connector (1=MySQL) [1]
Using connector MySQL (native)
Loading connector and client library... [OK]
Initializing connection handle... [OK]
Client database library version: 5.1.30
ASQL connector and DBMS client library successfully loaded
Connector ID (lib name) LIBASHMYSQL
Connector Version 1011100 (1.1.110.0)
Connector Description: MySQL 5.1 (native)
See Troubleshooting for assistance with errors reported by SQLTEST1.
Assuming success, you can now proceed to more comprehensive testing, involving actually connecting to a database and issuing queries. If your environment supports the A-Shell GUI (i.e. either A-Shell/Windows or ATE with a “G” driver), then the best program to work with is SQRY1 (see below). Otherwise, see SQLTEST2 (also below).
SQRY1.RUN: This GUI-based sample program connects to an arbitrary MySQL database (using SQLCON.SBX) and then allows you to enter arbitrary SQL commands, displaying the low-level results in a scrolling status window, and the any query result data in an XTREE. (The program does require the A-Shell GUI environment, either A-Shell/Windows with the PCTDVG driver, or ATE with “G” driver.) It also uses XTRSQ1.SBX (to process the query, retrieve the results, and display them in XTREE), and SCRSTS.SBX (for the status window.) Sample SQRY1 screen:
SQLCON.SBX: Standard SQL connection dialog utility. It prompts for parameters necessary to establish a connection to a database, and then attempts to establish the connection, returning the connection handle to the caller of successful. It is used by SQRY1, but may just as easily be used by you in other programs. The connection dialog appears as follows:
Notes:
The button to the right of the Host/Server or Data Source Name field can be used with ODBC connectors to display and select from the list of available data sources.
For ODBC, it is common for the data source definition to include one or more of the user, password, and schema fields, in which case the ones in the dialog will be ignored.
Consult the source code, SQLCON.BAS for details on usage, and the sample program SQRY1 for an example of using it.
SQLTEST2.RUN: This text-based sample program executes several fixed XCALL SQL operations, providing both a demo and a test that the interface is working. After a few preliminary operations (loading the library, etc.), it will prompt you to enter a valid connection string, in order to connection to a database. Once you establish a connection, it submits a SHOW TABLES query, then retrieves the first 3 rows of the result set, and a few other information requests before prompting you to enter your own query, after which it allows you to specify one of the FETCH_ROW options to fetch one row at a time. After each operation, the program displays the opcode and the cmdhdr result fields, so as to reveal pretty much everything about the operations. Between running the program, looking at its source, and looking at the SQL.SBR documentation, you should be able to see all the necessary details.
.LOG SQL:
.RUN SQLTEST2
SQLOP_INIT_LIB (Init library) ----------------------
cmdhdr.lastop = 1
cmdhdr.handle = 0
cmdhdr.rc = 0 [OK]
cmdhdr.rcext = 0
cmdhdr.info1: 0 (n/a) info2: 0 (n/a)
Hit any key to proceed: --------------------------------------
SQLOP_INIT_CONN (Init a connection resource) -------
cmdhdr.lastop = 4
cmdhdr.handle = 4
cmdhdr.rc = 0 [OK]
cmdhdr.rcext = 0
cmdhdr.info1: 0 (n/a) info2: 0 (n/a)
Hit any key to proceed: --------------------------------------
SQLOP_CONNECT (Establish a database connection) ----
Ex: -host=localhost -user=jack -pw=secret -db=sakila
Connect string: -host=localhost -user=jack -pw=opensesame
(etc)
SQLTEST4.RUN: This text-based sample program illustrates creating a table, inserting and updating records, and scrolling backwards through a result set (which requires use of the SQLOP_SET_STMATR operation under ODBC).
.LOG SQL:
.RUN SQLTEST4
SQLCUSTST.RUN/SQLCUS.SBX: GUI sample program (contributed by Stephen Funkhouser) which first uses SQLCON.SBX to request the parameters needed to connect to the sakila database, and then prompts you to enter a customer partial name, which is uses to select matching records for display. Double-clicking on one returns the customer ID to the calling application.
