October 18, 2012

SQLBase

Filed under: Uncategorized — Tags: , — Arne Joris @ 12:10 pm

SQLTalkIconOne of the perks of doing software development for small- and medium sized organisations is that you get involved in all aspects of IT.  In our projects at we have done backup, workstation and router configuration, printer troubleshooting, mail merge support and, of course, database maintenance.
Database administration skills are a must when you develop line-of-business applications, and most of us know our way around the usual suspects: Orace, SQL Server, MySql and sometimes even DB2, Informix or PostgreSQL.

When you specialize in legacy application renewal, you sometimes come across more exotic database varieties. In this article, I’ll share my knowledge of SQLBase , a variety I came across earlier this year.

SQL what 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.

I 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 interfaceSqlTalk

The 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%’;

Dumping 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

I 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. I don’t find comma delimiting very useful as many strings may already contain commas. I 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 me via – we’d love to help.

Other SQLBase Links

  1. Horshack’s SQLBase tips

2 Comments »

  1. Very good article, I recently started working in a small company using SQLBase their customers. After working with MSSQL, Oracle in large enterprises is a nice challenge to keep learning different technologies.

    Comment by Sebastian — March 8, 2013 @ 12:33 pm

  2. Thanks Sebastian! Not everything needs Oracle or MSSQL and sometimes these lesser known databases are just right for the job. If you already know your basic DBA stuff, learning more about a database you had not worked with before can indeed be a nice challenge and keep the job interesting.

    Comment by Arne — March 9, 2013 @ 3:30 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress

Switch to our mobile site