THEOS Software (GB)
We are the UK distributor for THEOS Corona Operating System and companion products, including the THEOS BASIC programming language and THEO+Net network server software.
Accessing Data in an SQL Server database from THEOS BASIC programs
It is quite simple to write THEOS programs to access data from Microsoft SQL Server databases, using an intermediate product called the ODBCNET Server. This is a service program that runs on a Microsoft Windows PC and provides the interface between THEOS and SQL Server or other database servers.
Once the ODBCNET server has been installed on a suitable Windows PC, the Corona server must have an appropriate number of licenses added to allow it to connect. All that is then required is to configure the data source and write some software.
Confguring the Data Source
In the Windows XP control panel, create a System DSN (Data Source name) which connects to your SQL Server database. You would normally provide the IP address or name of the server, choose the SQL Server instance, and then select a database from that server instance. When the DSN has been created, use the "Test Connection" button to make sure that it is configured correctly.
Accessing it from THEOS
Writing code to access the Data Source is now quite straightforward in the THEOS BASIC language, with some sample code being included in the standard language distribution. To summarise, it is simply a case of connecting to the ODBCNET server, executing the query, and retrieving the results. The function SQL.DESCRIBECOL can be used to get information about each of the table columns that have been returned in the query, including name, type and length.
Sample Code
The following short BASIC program will connect to an ODBCNET server data source and run a basic SQL query. The portions in strong text are the BASIC source code, the remainder is an explanation of its function.
10 INCLUDE "ODBCNET" - add the API calls into the language
20 CALL SQL.SELHOST("odbc.theos-gb.lan") - select an ODBCNet server by host, in this case by name but could be done by IP address
30 CALL SQL.ALLOCHANDLE(SQL.HANDLE.ENV, " ", ADDROF(HENV$)) - allocate a memory handle for the environment
40 CALL SQL.ALLOCHANDLE(SQL.HANDLE.DBC, HENV$, ADDROF(HDBC$)) - allocate a memory handle for the database connection
50 CALL SQL.CONNECT(HDBC$, "DSN-Name", "Username", "Password") - connect to the ODBCNet server data source by name, with the specified username and password
60 CALL SQL.ALLOCHANDLE(SQL.HANDLE.STMT, HDBC$, ADDROF(HSTMT$)) - allocate a memory handle for the SQL query
70 CALL SQL.EXECDIRECT(HSTMT$, "select * from users") - execute the specified query on the data source
80 CALL SQL.NUMRESULTCOLS(HSTMT$, ADDROF(COLS%)) - recover number of columns
90 CALL SQL.BINDCOL(HSTMT$, DIM COLUMN%(I%), DIM MSIZE%(I%))
100 CALL SQL.FETCH(HSTMT$, DIM COLUMN%(I%), DIM MSIZE%(I%), DIM MVALUES$(I%), DIM MLEN$(I%)) - retrieve the next row of data from the data source
That's pretty much it. The program would now simply keep calling SQL.FETCH until the API call returns a value of SQL.NO.DATA, then it would clean up after itself by closing the connection and de-allocating all the memory and handles created earlier.
Although it's reasonably hard to read compared to a standard BASIC program, it's also pretty impressive that it can connect to a data source, execute a query and retrieve data within ten lines of BASIC code.
Of course, the query can be much more complex, or can be used to update or write data rather than simply extracting it. And the API can be combined with others, for example to retrieve data and display it in a dynamic web page using the THEOS HTTP Server.