Connecting to SQL Server - "database name component of the object qualifier"

[Diana LaScala-Gruenewald]

Hello everyone,

I am experimenting with serving MS SQL Server database views using ERDDAP. The ultimate goal of this project is to explore whether ERDDAP might help my institution (MBARI) make a large proportion of its data publicly available in a variety of formats.

At the moment, I think ERDDAP is having trouble reading my test view. If I enter the following into GenerateDatasetsXml.sh (note, not real username and password):

EDDTableFromDatabase.generateDatasetsXml
url=jdbc:jtds:sqlserver://perseus.shore.mbari.org:1433/EXPD
driver=net.sourceforge.jtds.jdbc.Driver
connectionProperties=user,user,password,password
catalog=

schema=dbo

table=DocRickettsRovctdData

orderBy=

reloadEveryNMinutes=10080
infoUrl=https://www.mbari.org/at-sea/expeditions/
institution=MBARI
summary=CTD data from all Doc Ricketts dives.
title=ROV CTD data, Doc Ricketts, 2009-2021

I get this output:

EDDTableFromDatabase.makeConnection via DriverManager + datasets.xml info
  Success! time=188ms
getting primaryKey list
java.sql.SQLException: The database name component of the object qualifier must be the name of the current database.
 at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
 at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
 at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
 at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:677)
 at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1032)
 at net.sourceforge.jtds.jdbc.JtdsDatabaseMetaData.getPrimaryKeys(JtdsDatabaseMetaData.java:1301)
 at gov.noaa.pfel.erddap.dataset.EDDTableFromDatabase.generateDatasetsXml(EDDTableFromDatabase.java:1301)
 at gov.noaa.pfel.erddap.GenerateDatasetsXml.doIt(GenerateDatasetsXml.java:510)
 at gov.noaa.pfel.erddap.GenerateDatasetsXml.main(GenerateDatasetsXml.java:990)

This suggests to me that ERDDAP was able to establish a connection, but that the database name is incorrect. It seemed like this might be related to capitalization/lack of capitalization as suggested in the ERDDAP docs, so I've tried several options with no luck (all caps, no caps, etc).

I wanted to share this issue and ask two questions:

1. Do you agree that the connection information is probably OK, and that the problem is likely related to the actual schema/table name?

2. Has anyone encountered this before? Do you have suggestions on what else I might try?

Many thanks,

Diana

[Bob Simons]

I'm not the best person to answer because I've only worked with PostgreSQL (not MySql) and only infrequently, but I'll say:

1) I agree that the connection information is probably okay because it looks like the connection was successful. So the problem is likely with the table's full (qualified) name.

2) I would read https://dev.mysql.com/doc/refman/8.0/en/identifier-qualifiers.html and ask your database admin (you?) if there are any other identifiers (notably a database or catalog name) that should also be included in the dataset's setup information.

Someone else may have a better answer.

[Diana LaScala-Gruenewald]

Thanks, Bob. That was actually really helpful and got me pointed in the right direction.

In my experience "database" and "catalog" are often synonymous. In this case (for Microsoft SQL Server), GenerateDatasetsXml.sh completes successfully if I put the database name in for both.

Cheers!

Diana