analyse database

[Former Member]

Hi,

I've got a database file (*.db) from a customer who asks me to analyse its content. All what he knows is that it's a SQL Anywhere database, but no version number.

I've already downloaded SQL Anywhere Developer version 12 and 10. But both products reports a connection error, that this database was created with another version.

So how can I detect the database version to download the right developer suite ?

[Former Member]

1) Open db file in text editor 2) in first lines you will see text strings with database version and codepage.

[MarkCulp]

Yep, that is an easy method as well.... make sure you don't attempt to "save" the file using your text editor since this will likely corrupt it.

Note that the issue with this approach is that the text within the file records the version that created the file initially and does not reflect any changes made to the file format due to database upgrades. E.g. I could create a database using v10 and then upgrade the file to v12 - the text in the header page of the file will still say v10 but the database file format will actually be v12.

[Breck_Carter]

"the database file format will actually be v12"... are you suuuure of that? or is it just the catalog stuff that gets upgraded? (go ahead, rock my world 🙂

[MarkCulp]

Perhaps I was not clear saying "file format will actually be v12"? Most of the time when an upgrade is performed on a database it is only the catalog that is modified - add new tables and add new columns to existing tables, update views, etc - but once in a while the upgrade will also set a capability bit in the database that will mean "this database supports X" where X will be some new feature or file format that is only present in the file when the capability bit is set. In fact it is now common practice to always have two bits per release - one saying that the db was created by version Y and another bit saying that the db has been upgraded to version Y (and hence has all of the wonderful new things that Y can have when an upgrade has been done) - the features that can only be supported if the db was created by version Y get both of these two bits but any database that has been upgrade to Y only get the second bit. I cannot think of a specific non-schema change example at the moment... since I don't think we've had one (that I can remember?) since switching to the new major file format in v10. Perhaps someone else on the SA eng team can think of an recent example?

[MarkCulp]

SQL Anywhere is designed to be backward compatible so that newer versions of the software can open and use database files that have been created with older versions of the software. There is one exception to this: Software versions 10 and up cannot open database files created with version 9 and below.

So in your situation one of two cases must be happening:

• the database from your customer is created with versions 9 or lower, or
• the database file is created with a version beyond v12 (which is highly unlikely!).

To determine the version of the database you can use the DBCreatedVersion() function in the dbtools library.

[Former Member]

Bingo, DBA and SQL works well. I can log on and can view all tables and data with Sybase Central.

Anyway, is there any limitation for this user in viewing all data ? When I open the Dbspaces folder and select here the System node, then all tables are listed on the righthand side with a column "Aprx. # Rows", which probably means "aproximated number of rows".

Here a special table is listed with about 130.000 rows. But when I switch to the Tables folder and select "View Data in Interactive Sql" in the context menu for this table, scrolling stops at 500 rows.

[VolkerBarth]

That's the default option independent of the particular users:

DBISQL (also when used from within Sybase Central) just displays the first 500 rows of a result set.

Look here how to change this (somewhat incomfortable) default by means of the "Options/SQL Anywhere options" menu...

[Former Member]

Hi,

thanks so far. Meanwhile I installed version 9 of developer suite and when trying to build up a connection at least no message comes up, that the database version doesn't match with the developer version.

But the next hurdle seems to be UserID and password. All what I got from my customer was "Administrator" and "admin". But this leads to an errormessage, that one of both is wrong.

So is there something like a SuperuserID/password, so that a connection succeeds ?

[MarkCulp]

There is no backdoor "super user" to login to the database... unless, you, the application writer created one when you deployed your application.

If you do not know the userid/password then you will not be able to login to the database.

[justin_willey]

When a SQL Anywhere database is created the default user name and passwords are DBA and SQL, respectively, so try those if you haven't already.