Suggestion - Error if DBF is defined when Server and Database already are running

[former_SQLA_member1694882]

Product Suggestion - If DBF is defined in the connection string but there is already a running Server and Database that matches the calculated names from the DBF path AND the running Database has a different path than the DBF the command should throw an error.

For example, if I have a database already running with the following startup parms:

-xTCPIP(Port=2638)
-nExample
😧\\Databases\\db\\example.db
-nExample

And I then want to validate that a backup of that database is valid by using the following command:

dbvalid.exe -c "DBF=E:\\Backups\\Example\\example.db;uid=validator;pwd=validator"

DBValid will actually connect to the already running Database (located at D:\\Databases\\db\\example.db instead of the actual database file I specified in the connection parameter.

Refer to dbvalid valid giving different errors? for an example of where this would've been really useful.

[graeme_perrow]

One simple solution would be to add "DBN=ValidateExample" to your connection string.

[VolkerBarth]

I fully second Graeme's suggestion to avoid this situation by using a particular DBN value - and would further recommend to specify a ServerName when starting a database which is expected to be not already running.

Nevertheless, I think Nick has showed a case where the docs seem not really clear - at least I have not found a description that DBF (without also specifying DBN) would be treated as "use the database name from the DBF value and try to find a running database with that name and connect to it - otherwise start the according database file".

IMHO the DBF connection parameter is explicitly meant for non-running databases, and if a database is already running (but from a different path), using that one would be counter-intuitive...

Note: My discussion is only focussed on specifying DBF without specifying DBN. If both are given, there's no ambiguity here - if DBN does match a running database, you are going to connect to this one, and otherwise you certainly are wanting to load the given file under the desired name...

---

Aside: An error (or warning?) seems the only possible reaction (besides the silent connect, as currently implemented), as there is no way to load the desired database when its file name (without extension) is identical to an already running database. In Nick's sample, apparently the same database engine could not load a second database named as "example"....

[MarkCulp]

Background: Since your connection string does not specify a server name nor a database name the client attempts to derive this information from what it does have - it uses the DBF parameter to determine that you want to connect to the database named "example" on the "default" server. Since there is a server (running on the local computer) that has a database named "example" (note case insensitive comparisons) then it connects to that database.

To address your suggestion: I'm not sure that this request is feasible. Some thought experiments would need to be done first. For example, what if the database was started using simply "example.db" and the client specified dbf=somepath\\example.db then (if this feature was implemented) should the connection succeed or fail? Hmmm, we don't know for sure purely based on the pathnames. We would need to use some other method (e.g. inode check) to make the determination.

Now what should happen in the case when the client(s) have been deployed and the system admin (DBA) needs to move the database to a new location - e.g. to a larger disk or a new computer - and the pathname to the database changes. If the clients used DBF=path\\dbname.db to connect to the database then suddenly the connections would fail.

And then there is the issue of backward compatibility!

So the short answer is that you should always be sure to specify the server and the (logical) database name to which you want to connect to ensure that you are connecting to the correct server and database. This recommendation is made several times in the documentation - e.g. in the Remarks section for DBF

... but thank you for your suggestion. The SA development team will need to consider this further and perhaps some change can be made in a future release to address this issue?

In the meantime, I would recommend using Graeme's suggestion (adding DBN=SomeOtherDBName) or adding ServerName=MyValidationServer (or some other random name).

Another suggestion would be to always use -xd (do not become default server) on ALL of your server command lines to ensure that there is not a default server running on the computer - doing so will ensure that you do not connect to the wrong database server (presuming you are controlling all of the server start lines on the computer & no OEM apps have been installed that use SA which have not specified -xd) - i.e. it will force you (and all apps) to always specify the server name in the client connection string ... Which BTW is why we recommend to OEMs (and any application developer that is going to deploy their software into unknown/uncontrolled situations) to also always use -xd and to specify the server name in their applications.

[former_SQLA_member1694882]

Thanks for the answer Mark. Yes, we've learnt our lesson and now specifically define ServerName.

My biggest concern with this is there was nothing indicating that dbvalid had connected to another database (Yes, I know RTM and all). At the very least could dbvalid output what database it is connecting to? Something like:

Connecting to Example on Server Example ...

Or if the database isn't running, output something like:

Starting Server Example ...
Connecting to Example on Example ...

Lastly, is there any place where I can find a list of recommendations for OEMs because after over 10 years of working with SQL Anywhere no-one in our company had heard of the recommendation of using -xd (though that might just show that no-one in our company RTM properly).

[VolkerBarth]

Well, the following doc page may be of help (and it evens mentions -xd, though that was not on my radar, either):

Embedded database connections

[MarkCulp]

Your suggestion about showing what the tool is actually connected to is reasonable - I will take that back to the team and discuss it.

In addition to the section that Volker indicated, I would recommend browsing through the section on Database and Application Deployment.

[Breck_Carter]

Here's my vote of support: http://sqlanywhere.blogspot.com/2009/03/which-database-am-i-connected-to.html

[justin_willey]

If you are looking at this area, Volkers suggestion from a few years ago might also be relevant: http://sqlanywhere-forum.sap.com/questions/1580/make-dbisqldbisqlc-ask-for-credentials-before-trying...

[VolkerBarth]

Another attempt might be to introduce a connection parameter to express that the desired database is not expected to be already running (as that seems the basic point of Nick's solved problem) and therefore MUST be freshly started, some kind of "anti-ASTART=No" but for databases, not database engines, say a "MUSTLOAD=Yes"...

I'm aware that the current set of connection parameters will usually allow that already, say by specifying a value for ServerName that is not used by other connections... as has been recommended here and there.