Extracting data from old Sybase SQL Anywhere

[Former Member]

I have an old Database as archive for my pictures. The pictures are not stored in the database, only Chapter, pictureID and keywords. It is based on Sybase SQL Anywhere Database Engine Version 5.5.01 Build #1333. The application dose not offer to export listings. I would like to creat listings in a csv format or compareable. Or import the data to an open office database. In advance, thanks for help. If necessary I can upload the database (3.8MB)

p.s. I am not a specialist!!

[Breck_Carter]

Are you able to connect to your database using the SQL Anywhere 5 isql.exe utility?

Here is a sample command line:

"%SQLANY%\\win32\\isql.exe" -c "dbn=ddd5;eng=ddd5;uid=dba;pwd=sql"

On my Windows 7 computer, that is equivalent to this:

"C:\\Program Files (x86)\\Sybase\\SQL Anywhere 5.0\\win32\\isql.exe" -c "dbn=ddd5;eng=ddd5;uid=dba;pwd=sql"

Then you can use the SELECT and OUTPUT command to create a CSV file. Here is an end-to-end demo with a new table called tt:

CREATE TABLE tt ( x integer, y integer );
insert tt values ( 1, 1 );
insert tt values ( 2, 2 );
commit;
select * from tt;
output to 'c:\\temp\\tt.txt';

Here is the output file:

1,1
2,2

If your database needs to be started before running isql.exe, here is a sample command for that:

"%SQLANY%\\win32\\dbeng50.exe" ddd5.db

[VolkerBarth]

Is the environment variable called SQLANY or SQLANY5 for v5?

(I have no v5.5 version currently available, but newer versions always include the version number...)

[Breck_Carter]

Back in the day it was SQLANY... what, you think I would post untested code? 🙂

[VolkerBarth]

Oops, now I've put my foot in it:)

I guess my brain memory (possibly the swapped part) told my there was something particular with the environment variable in 5.5 - but I just noticed the "issue" was the fact that 5.5 by default installed in a "C:\\SQLANY50" directory (at least in all our setups...). - That's just another reason to make use of these environment variables - as you have done in your typical exemplary manner:)

[Breck_Carter]

Yes, I remember C:SQLANY50, haven't seen it for a while... it might have dated from Windows 3 or DOS days. For my Windows 7 installation I think I just let the setup do it's thing, which resulted in C:Program Files (x86)SybaseSQL Anywhere 5.0... I'm still grokking over the fact it worked at all. The engine itself crashes a lot, probably because it's not supposed to run on Windows 7.

[VolkerBarth]

That's my impression as well. We have a v5.5 third-party legacy app that runs fine on XP and almost fine on W2K3 but rather instable with Win7. Never mind:)

---

Though Sybase itself is more confident that v5.5 may run well on Win7 though it is not tested and supported at all: http://sqlanywhere-forum.sap.com/questions/6356#6391...

[Breck_Carter]

My experience is that an idle dbeng50.exe just crashes after a few minutes on Windows 7. The client component keeps trying to connect and has to be manually terminated.

[VolkerBarth]

I can confirm that: As we use dbeng5 with a local client and in autostart mode, the application seems to works for some time and then suddenly looses its database connection. - "So hurry up, dear user!" would be a fitting slogan:)

[former_SQLA_member1694880]

As an option, you can unload a database from the command line. I used a demo database for my example, replace it with the actual database name.

Start server.

c:\\sqlany50\\win32\\dbsrv50.exe -c 10M -x tcpip c:\\sqlany50\\sademo.db

Create a directory to unload into.

md c:\\data

Unload a database.

c:\\sqlany50\\win32\\dbunload.exe -c "ENG=sademo;DBN=sademo;UID=dba;PWD=sql" c:\\data\\Unload -r c:\\data\\Sademo.sql

[Former Member]

Ok. guys, this is exactly what I feared. You are the specialists and I have no clue. :-(( I really appreciate your immediate help.

Before I do or tell stupid things, I was trying to upload the "corpus delicti", so you can see, what I'm talking about. Unfortunately I'm not allowed to do that, as I have less than 100 reputation points.

Can I send the database to one of you and ask for transformation. For instance into an open office data base.

Just in case that there is a userID and PW necessary to access the data base, I wrote a mail to the company that has created this data base once. Just hoping they can tell me.

Thank you very much.

[jeff_albion]

Just in case that there is a userID and PW necessary to access the data base, I wrote a mail to the company that has created this data base once. Just hoping they can tell me.

There is always a username and password required to access the database. The default SQL Anywhere login is "dba" with the password "sql" (although we instruct developers to create a new login / change the default password, so this should NOT work, in general).

If the SQL Anywhere database login credentials have not been provided to you by your software vendor, you will have to work with them directly in order to extract your data - we will not be able to assist you until you are able to verify that you are permitted to have access to the data.

[Former Member]

Perfect Jeff! Thanks to the laziness of the developer the standard p/w works. I have tried to connect the data base via open office. But as I have no idea how to do this right I did not succeed. But at least I have the proof now, that user and p/w are still standard. Now that access is granted, can you help me to export the data to a flat file?

[Breck_Carter]

See the first answer, the one that talks about isql.exe and SELECT and OUTPUT... let us know if you have any problems understanding it or running it.

[Former Member]

Dear Breck I found the iqls.exe on c:sqlany50win32. Starting this, the programm asks for User and P/W. Once the program is running, I can see three windows "Data", "Statistics" and "Command". In "Statistics" it says "Conected to database". How to proceed from here if I want to export all information, linked to each picture, to a structured flat file? Wish you a happy new year.

[Breck_Carter]

In the "Command" pane run commands like this:

select * from tt;

output to 'c:\\temp\\tt.txt';

[Former Member]

Ok. I have managed to find out the names of the tables in the data base by using "Edit -> insert table" from the menu. Now I can export table by table to text files and than combine them in access or something like that. Is this the right way, or is there a more convenient one? Thank you very much for your help.

[VolkerBarth]

Glad you got it going:)

To your question: I guess it's hard to tell for us as we do not know the contents of these tables and their relationship (and the number of tables will matter as well). As a result, there won't be a single "right way".

If you can handle these tables as some kind of lists (as you could with, say, Excel), then I think you are fine to use whatever tool you're familiar with.

In contrast, if you are exporting data from a database with a complex data model (with lots of dependent tables), then the question comes up what to do with these contents? - Usually you would need/have a similar application to migrate all this data to, and if you don't have one already, well, that may be beyond the facilites of such a forum...

[Former Member]

Ok guys, thank you very much for your great help. You can be proud on your self. You have managed to bring a "db-dummy" to access and extract data from an unknown data base. Thank you.