Recently my dad retired from running his own business as master wood worker. He ran an old business software specializing in supporting invoiving, creation of offers, address database and so on for small businesses.
This software happened to be based on an older version of SAP SQL Anywhere.
Now my dad asked me to help him in backing up the address data as well as his old invoices, since he is required by law to keep them for up to 10 years.
Exporting the addresses was easy, since the software offered a CSV export.
The invoices however could not be printed into several PDFs in a batch job. I digged into the files and found some .db and .log files in a directory named database.
Well, since I had to go home I backed these two files up into my office cloud and started looking into reading these files.
At that point I did not even know yet what file type I was dealing with, but I was hoping I could connect to it using Java.
After checking the file contents and some google search I found out that the database server I would need is SQL Anywhere.
I kind of lost hope, when I realized that SAP had bought the company, which developed the initial version of the database a long time ago. SAP is usually very badly documented, even if you have a license.
Hours later I found some helpful resources and set out to fix my fathers issue. Not that it was necessary since he had all invoices on paper, but I have fun solving little things like this and it would be nice to be able to perform searches.
Of course, I went way overboard again, but more details later.
Downloading and installing SQL Anywhere database server
Here is an URL, which provides a few URLs for different versions of the SQL Anywhere client software and, hidden in the text, a link to a trial version of the database server. The software listed in the table in this blog post only gives you the client software.
https://wiki.scn.sap.com/wiki/display/SQLANY/SAP+SQL+Anywhere+Database+Client+Download
Here is the URL, which requires you to register with SAP for a trial verison of the database server. The installer also includes a database administration UI called Sybase Database Central and Interactive SQL for querying databases as well as the needed client components like drivers JDBC, ODBC, ADO.NET and so on.
https://learn.sapdigital.com/SQLA-Trial-Registration-Page.html
The installation process for the database server and client software is more or less self explanatory and I did not have any issues.
After the installation you can start the Sybase Database Central administration software.
Migrating an old database into a new database
So, next thing was connecting to the database, which I backed up into my Office 365 cloud.
This did not work as expected, since the database file was created with another / older version of the software.
Some more research showed, that I would have to unload the old database first and then re-load it into a new database with the current version.
Mmmmh, I was using version 16 installed as database server on my client. As far as I knew, this old database file could have been created with any older version of SQL Anywhere.
The newest version 17, could logically be excluded. Luckily Database Central ships with a function to unload databases from older SQL Anywhere versions and it does not require you to know the older version.
Plus it is available for free in the 60 day trial version ;0).
Open Sybase Database Central and in the Sybase Central context, double click on SQL Anywhere 16 or 17
The Extras menu and some other buttons will become available in the menu bar below the context bar.
Go to Extras (probably Tools in English) and select the option to unload database.
A wizard opens, which will perform the database unload for you. Click on Next.
Select the option, which reflects unloading of an SQL Anywhere Database, which was created for an older version of the database engine than the one running on your machine.
Provide a user id, which has access to all tables. I was lucky since I could guess the DBA user and his password when I copied the database file into my office cloud.
If you do not know the password for a user with vast access rights, the whole task could become more complex.
Last but not least, select a database file, which you want to unload. Click Next.
Now you have to choose a target you want an older version to load into. For my case I chose a reload file, since I knew that the software that utilized the database was written by Germans.
I knew I would face encoding issues, due to some weird code page they would have chosen. I mean, why do developers not simply take UTF-8?
Anyhow, a reload file gives you most control, since you can actually review the automatically generated SQL code before loading it into a new database.
Click Next.
Provide the target dir and file name for your reload SQL file and click Next.
Now select, what you want to unload from the old database. I went for structure and data, the first option.
Click Next.
In the next screen you have to specify how the structure and data will be unloaded from the old database and how you intend to reload structure and data.
I went with the OUTPUT commands to unload and the INPUT commands to reload, since both happened on my local machine (the db engine was running there).
Click Next.
The final wizard screen shows you a summary of your selections in form of a small script.
Click on finish.
You will see a little log window, which displays also the current job status.
Once the unload job is completed, click on close and you are finished.
Reviewing the reload.sql file, I found some encoding that I did not hear of before and that was also not available on the database server.
So I replaced it with a proper code page, which includes German special characters like Ä and reloading it into a new database worked like a charm.
Create a new database
In the below article I am describing in detail how to create a new database in SQL Anywhere 16 using Sybase Database Central.
After you completed the database creation wizard you can immediately start administering your new database.
It is running on a local SQL Anywhere server, which has been started by the assistant automatically. Once you shutdown your computer however, this local server will also be shut down and not be running automatically at next start up. Therefore in the next section I will show you how you can define a databsae service to have your new database running locally.
Create an SQL Anywhere Database Service
In the below article I documented in detail how to setup an SQL Anywhere 16 Database Server as a service.
Create an SQL Anywhere Database Service
Once you completed these steps you can see your newly created SQL Anywhere service and its status in service list pane.
You can always change a service definition. Right click and select Properties from the context menu.
You can now connect to your new database using Tools > Open Interactive SQL to query it.
Hope it helps!
