Question Re: JDBC URL string in Technology Q&A

JDBC URL string

huber1 — Wed, 24 Oct 2012 10:27:03 GMT

Hi All

By installing with an installer utility I made an error and got a JDBC database server connection with an URL like the following as a result:

URL: jdbc:sybase:Tds:10.0.1.2:2638/<database-file>

Now I am wondering where this database file is located in the (OS X) file system, I just can't find it nowhere, but obviously it must be somewhere (I also can connect to it). To what location is /<database-file> pointing? A temporary or cache folder?

My usual URLs look like this: jdbc:sybase:Tds:10.0.1.2:2638?ServiceName=<database-file>&CHARSET=utf8

As I would like to delete it, I have to know where it is. Anyone having an idea?

Re: JDBC URL string

jeff_albion — Wed, 24 Oct 2012 12:52:35 GMT

To what location is /<database-file> pointing?

"ServiceName" is referring to the database's name ("alias") on the SQL Anywhere database server:

e.g.

Launch the database server: dbsrv12 -n MyServer /Applications/SQL Anywhere 12/database.db -n MyDatabase

Use the JDBC (for jConnect) URL: jdbc:sybase:Tds:10.0.1.2:2628?ServiceName=MyDatabase

Now I am wondering where this database file is located in the (OS X) file system... (I also can connect to it)

You can just query the database for this information if you can connect to it:

SELECT db_property('File');

Re: JDBC URL string

huber1 — Thu, 25 Oct 2012 05:09:33 GMT

Jeff, thanks a lot. The SELECT db_property('File'); works for all database files except for my strange/broken database file which exists somewhere in the file system as ghost and where I funnily can connect to.

Re: JDBC URL string

VolkerBarth — Thu, 25 Oct 2012 07:54:54 GMT

So what does it return then? NULL?

Re: JDBC URL string

huber1 — Thu, 25 Oct 2012 14:00:08 GMT

Strangely, it returns one of the other database file names, not NULL. The given database file name is not possible, I checked the content with Sybase Central. If I new where the slash (/) after the port number points to I would find the database file. I only can think of it's in some temp or cache folder or similar, otherwise I would have found it. It's a repository database, so I do not want them to be just somewhere without my knowledge.

Re: JDBC URL string

jeff_albion — Thu, 25 Oct 2012 14:48:28 GMT

Do you know what the command-line for starting the database server is?

 SELECT PROPERTY('CommandLine');

As I mentioned, the ServiceName is just a database alias passed in as a command-line parameter after the database file.

(Aside: There is also the "special" utitity_db which has no physical database file representation (e.g. ServiceName=utility_db) - but the above SELECT will just return the empty string in that case.)

Re: JDBC URL string

huber1 — Fri, 26 Oct 2012 08:47:35 GMT

I get following:

'-n SkyServer -x tcpip{dobroadcast=no;port=2638} -gd DBA -ti 0 -c 6144m -ca 0 -qw -o /Library/SQLAnywhere12/sybase_log.txt /Library/SQLAnywhere12/databases/Hades.db /Library/SQLAnywhere12/databases/Magazine.db /Library/SQLAnywhere12/databases/log_data.db /Library/SQLAnywhere12/databases/servoy_repository.db -hn 5 '

Re: JDBC URL string

jeff_albion — Fri, 26 Oct 2012 11:22:04 GMT

Based on the command-line, all four of your databases are stored in /Library/SQLAnywhere12/databases/ on the OS X file system (and are named "Hades", "Magazine", "log_data", and "servoy_repository" for their "ServiceNames" respectively.)

Re: JDBC URL string

huber1 — Tue, 30 Oct 2012 06:14:07 GMT

These are the db files I intentionally created and put there. But there is somewhere a ghost file which contains the Servoy (our IDE) repository, where I can connect to via the URL: jdbc:sybase:Tds:10.0.1.2:2638/servoy_repository and this is the database file I can't find, not even with the Unix find cmd, but it is somewhere and the only information I have is that / in the URL, but don't know where a / is pointing to!?

Re: JDBC URL string

VolkerBarth — Tue, 30 Oct 2012 06:30:18 GMT

What happens when you stop the database engine and re-start it? - The "sybase_log.txt" console log should then contain information on which database file is started and made available with which database name. (At least that's the expected behaviour.)

Of course you might also check the existing console log for any related entries.

Re: JDBC URL string

jeff_albion — Tue, 30 Oct 2012 09:48:09 GMT

I'm not sure I understand. There should be four (4) databases listed in /Library/SQLAnywhere12/databases/. I listed them in my previous response.

One of them is named /Library/SQLAnywhere12/databases/servoy_repository.db - this is the ServiceName "servoy_repository".

(Which is the same name as the file name, and is the default database name if you don't specify one with "-n" after the database filename, which is true by your command-line).

Re: JDBC URL string

huber1 — Wed, 31 Oct 2012 05:10:42 GMT

@Volker: The sybase_log.txt file shows the four databases I start (defined in my SQLAnywher.config file). But it does not show the ghost database (called also servoy_repository) which exists somewhere in the file system as "duplicate", which means the name is the same but the content is not the same. The "real" database servoy_repository has the correct content, the ghost database is empty (that's why I know there are 2 databases with the same name, because I can connect to both of them with the URLs below).

That is why I would like to find the "ghost" database file. I can connect to both of them (as mentioned above). So I think only if someone can tell me where the slash (/) in the URL is pointing to I will be able to find it. Or, if someone could tell me what a slash in the URL means? In other words, what is the difference between these 2 URLs? And to what database file is the second one pointing?: URL: jdbc:sybase:Tds:localhost:2638?ServiceName=servoy_repository&CHARSET=utf8 URL: jdbc:sybase:Tds:localhost:2638/servoy_repository&CHARSET=utf8

@Jeff: I do not use the -n option, so yes, the db name corresponds to the serviceName (which is ok for me)

Re: JDBC URL string

huber1 — Wed, 31 Oct 2012 05:57:50 GMT

@Volker: As the sybase_log.txt shows (only) the 4 databases mentioned as started, there is no reference to a second servoy_repository database file. Confusing why I get different results with the 2 URLs. If it's absolutly shure that only databases are started which are mentioned in sybase_log.txt (in my case), then that means there is no second servoy_repository database file. But then, why can I connect with the URL string containing the slash (/) and why do I get a different result?

Re: JDBC URL string

VolkerBarth — Wed, 31 Oct 2012 07:12:49 GMT

Yes, under normal circumstances the log will also contain entries for databases that are not started during the server start but are started lateron, say, by using a START DATABASE statement or through a connection that starts its own database on the running engine.

However, your server seems to operate under somewhat "unnormal" circumstances...

Re: JDBC URL string

jeff_albion — Wed, 31 Oct 2012 12:29:53 GMT

Confusing why I get different results with the 2 URLs

I think you're going to have to define 'different result' more carefully. What result? What operation are you trying, specifically?

I think we have firmly established there is only one database, named 'servoy_repository' on the database server, located in the location we're expecting on the file system.

As for the difference in URLs, there is a slight difference in the way the jConnect JDBC driver resolves the parameter. I believe the end-result is the same: select the database named /<service-name> or ServiceName=<service-name> on the SQL Anywhere server. However, the / style is documented for use with Sybase ASE databases - there are likely compatibility differences when using this mode with SQL Anywhere. The jConnect documentation recommends to use "ServiceName" parameter when connecting to SQL Anywhere databases (as does the SQL Anywhere documentation).

Re: JDBC URL string

huber1 — Wed, 31 Oct 2012 14:03:19 GMT

You are right about defining different result more carefully. With the URL having the ServiceName parameter, I can connect to the database and I am able to checkout the repository content to a workspace, which is then used by Eclipse. With the URL using the /<service-name> parameter (which I got by installing a new Servoy Developer IDE version, I also can connect to the database (which up to now was proof for me there is one with the name servoy_repository), but I can't checkout anything. And that is what made me nervous, being able to connect to the database, in one case as usual, but in the other case not being able to checkout data (for example). But as you explain, the difference must be in the way that although I can connect with the /<service-name>, it's not the correct way of doing it and therefor I can't access the repository. Thanks a lot for helping, it is very appreciated!

Re: JDBC URL string

jeff_albion — Thu, 01 Nov 2012 08:48:50 GMT

which I got by installing a new Servoy Developer IDE version... but I can't checkout anything

Have you tried asking for help about this issue in the Servoy forums? This sounds like a Servoy issue, not one with SQL Anywhere.

Re: JDBC URL string

huber1 — Mon, 05 Nov 2012 11:34:16 GMT

Yes I asked, but not so much of a hint if there is really somewhere a (hidden) database file. I now have setup a complete new installation and so far it looks good. Thanks for all the help!