01 Dec

Legacy data in SQLBase

When you specialize in legacy application renewal, you sometimes come across more exotic database varieties. In this article,  we will share our knowledge of SQLBase , a variety we came across a couple of years ago.

SQL SQLTalkLower-01what now?

Gupta SQLBase was one of the first relational databases for the PC in the 80s. It has changed owners several times in its 30 years, but currently it seems to target embedded platforms, claiming to be a ‘no maintenance’ RDBMS. But back in the mid to late 90s, SqlBase was still a very popular relational database technology for PC platforms, which is why some of the older legacy apps still use them.

We have been helping a client out with DBA services for an aging 7.6.1 release of SQLBase. To give you an idea of how old that is: the hardware requirements state that you need at least 24MB of RAM available on Windows 95, 98, NT or windows 2000 :-)

The DBA interface

SqlBaseThe interface for DBA activities is called SQLTalk. It features an input section at the top and an output section below it, and has both menu commands and icons to execute either one command at a time (the push pin), a script consisting of all commands in the input section (the document with a push pin) and commands for saving both sections.

Discovery

First you want to find a file named SQL.INI. It contains client configuration settings for SQLBase. Once you have found it, open it in Notepad and look for the “serverpath” string. It contains the logical name of the server hosting SQLBase databases.

The following commands all execute in SQLTalk:

  • Show all database names: SHOW DATABASES ON SERVER <server-name>;
  • Now you can connect to one of these databases: connect <dbname> <login> <password> Default login/password is sysadm/sysadm
  • Show log parameters: SHOW LOG;
  • Show stored procedures: SELECT * FROM SYSADM.SYSCOMMANDS;
  • show all relationships: SELECT * FROM SYSADM.SYSFKCONSTRAINTS;
  • Show all tables: SELECT * FROM SYSADM.SYSTABLES;
  • Finding all columns with %DISP% in their name: SELECT * from SYSADM.SYSCOLUMNS where NAME like ‘%DISP%’;
  • Counting number of records and size in kB in the database: UPDATE STATISTICS ON DATABASE; SELECT SUM(ROWCOUNT), SUM(USEDSPACE) FROM SYSADM.SYSTABLES WHERE NAME NOT LIKE ‘SYS%’;

Exporting an existing database and importing it into a test database

Dumping the data and schema from the existing database:

 set server myserver/password;
 connect mydatabase sysadm sysadm;
 set bulk on;
 unload database c:\tmp\mydatabase.uld;
 disconnect all;
 

Loading the data into a new test database:

 set server myserver/password;
 show databases on server myserver;
 create database test;
 connect test sysadm sysadm;
 set recovery off;
 SET ERRORLEVEL 3;
 LOCK DATABASE;
 load SQL c:\tmp\mydatabase.uld log c:\tmp\load.txt;
 set recovery on;
 disconnect all;
 

Loading data into a table

We usually get emailed links to spreadsheets with data that must be imported into a table and then processed. To use the data in a spreadsheet, you’ll first have to create to create an Excel macro to export selected data as comma separated (and quoted) data.

In excel, hit ALT+F11. Right click on ThisWorkBook, Insert, Module.

Copy/paste the following code (from support.microsoft.com/kb/291296):

Sub QuoteCommaExport()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer

' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" & Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum

' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End

End If
' Turn error checking on.
On Error GoTo 0

' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count

' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotation marks.
Print #FileNum, """" & Selection.Cells(RowCount, ColumnCount).Text & """";

' Check if cell is in last column.
If ColumnCount = Selection.Columns.Count Then

' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If

' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount

' Close destination file.
Close #FileNum

End Sub

Now in your Excel sheet, select the cell range you want to export.Then in the ‘View’ ribbon tab, select macros, View macros. Select QuoteCommaExport and run it. Make it put quote delimited data into a file in a temporary directory. Open this file in a text edit and copy all the data (CTRL-C)

In SQLTalk, the following command will put the data into a table named VENDORS:

insert into VENDORS values(
:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9,
:10,
:11,
:12,
:13,
:14)
\
$DATATYPES DATE, CHARACTER, NUMERIC, CHARACTER, CHARACTER, CHARACTER, CHARACTER, CHARACTER, CHARACTER, CHARACTER, CHARACTER, CHARACTER, CHARACTER, CHARACTER
"2011-08-11-04.09.47.950000","EGAD","17","Foobar Promotional Group Inc.","1111 - 11 Street","","","Edmonton","AB","T5S 2T5","Canada","(780) 111-443","(780)1-444","Neil"
"2012-05-10-12.12.10.934000","EGAD","18","BarFoo Soap Company","2222 - 22 Street","","","Edmonton","AB","T5S 2T5","Canada","(780) 222-443","(780) 222-444","Marc"
/

These last two lines are the pasted (CTRL-V) text from the file.

Outputing data to excel

To get data from SQLbase into a spreadsheet, we’ll first print it in a delimited format. Comma delimiting is not very useful since many strings may already contain commas, so we prefer a | (stick) delimiter:

set heading off;
set spool c:\tmp\members.txt;
ttitle off date off page off;
select FIRST_NAME || '|' || MIDDLE_NAME || '|' || LAST_NAME from MEMBERS order by LAST_NAME, FIRST_NAME;
set spool off;

To import this data into excel:

  • Create a new spreadsheet
  • The Data ribbon in the Get External Data command group, select ‘From text’.
  • Open C:\tmp\members.txt
  • Select Delimited file type, chose ‘Other: |’ as the Delimiter (uncheck Tab).

Do you have a legacy system that is costing your organization time and money?  If so, and  you would like some help with that system – please contact us – we’d love to help.

Other SQLBase Links

  1. Horshack’s SQLBase tips
  2. SQLBase language reference
  3. SQLTalk command reference