Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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!!

asked 18 Dec '11, 08:11

koogabara's gravatar image

koogabara
46125
accept rate: 0%

edited 18 Dec '11, 10:38

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822


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
permanent link

answered 18 Dec '11, 10:04

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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...)

(18 Dec '11, 10:37) Volker Barth
Replies hidden
2

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

(18 Dec '11, 11:51) Breck Carter

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:)

(19 Dec '11, 03:16) Volker Barth

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.

(19 Dec '11, 07:43) Breck Carter

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

(19 Dec '11, 07:50) Volker Barth

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.

(19 Dec '11, 08:33) Breck Carter

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:)

(19 Dec '11, 08:52) Volker Barth
showing 1 of 7 show all flat view

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

permanent link

answered 19 Dec '11, 05:27

Dmitri's gravatar image

Dmitri
1.6k41133
accept rate: 11%

edited 19 Dec '11, 05:28

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.

permanent link

answered 20 Dec '11, 14:38

koogabara's gravatar image

koogabara
46125
accept rate: 0%

1

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.

(22 Dec '11, 16:16) Jeff Albion
Replies hidden

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?

(29 Dec '11, 14:12) koogabara

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.

(29 Dec '11, 16:02) Breck Carter

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.

(31 Dec '11, 09:31) koogabara

In the "Command" pane run commands like this:

select * from tt;

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

(31 Dec '11, 15:34) Breck Carter

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.

(03 Jan '12, 12:10) koogabara

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

(03 Jan '12, 12:22) Volker Barth
1

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.

(03 Jan '12, 12:32) koogabara
showing 1 of 8 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×17
×17
×8
×7

question asked: 18 Dec '11, 08:11

question was seen: 11,070 times

last updated: 03 Jan '12, 12:41