JDBC connector settings for harvester 2.6.5

[YinShi]

Trying to set up JDBC connector to our enterprise SDE with oracle backend. Would anyone know what I need to put down for Driver class name? 

Also how does this connector work? Would get the metadata for all featureclassess in SDE? 

Lastly, why does one need to provide Table name, primary key column? What table is the connector talking about? 

Thanks a lot in advance, 

yin

[Marten]

hi. The JDBC driver name and connection string depend on the make of the database. See Brokers · Esri/geoportal-server-harvester Wiki (github.com).

Database	Driver Name	Connection String	See also:
Microsoft SQL Server	com.microsoft.sqlserver.jdbc.SQLServerDriver	jdbc:sqlserver://servername:1433;databaseName=dbname	Microsoft JDBC Driver for SQL Server - JDBC Driver for SQL Server | Microsoft Docs
PostgreSQL™	org.postgresql.Driver	jdbc:postgresql://host:port/database	Connecting to the Database (postgresql.org)
Oracle	oracle.jdbc.OracleDriver	jdbc:oracle:driver_type:[username/password]@[//]host_name[:port][:oracle]	Connecting to Oracle Database 12c Release 1 (12.1)

[YinShi]

Thanks Marten. 

We have a SDE with oracle backend. I downloaded ojdbc11.jar and put it in ..\Tomcat 9.0\lib. I then set up the broker using the following parameters. When I ran the task, 0 items were imported. Did I make any blatant mistakes in setting the parameters? Thanks! 

[Marten]

hi. two questions:

1. did you put the JDBC driver in the harvester/WEB-INF/lib folder? or otherwise in Tomcat/lib folder? this will be necessary
2. does the query you specify return results when run directly in the database? could you share maybe the top 5 rows from the result?

[YinShi]

I put ojdbc11.jar in Tomcat/lib folder and restarted Tomcat. I downloaded the driver from https://www.oracle.com/ca-en/database/technologies/appdev/jdbc-downloads.html. Not sure if the driver is the correct version for 12g backend. 

When I checked the error msg in harvester, I got the following error. 

Error opening JDBC connection to: jdbc:oracle:thin:@oracledevdb.ncc-ccn.ca:1521/sde11d

There are records in gdb_items table but I don't think we got so far. 

[Marten]

what database client app are you using? perhaps it can show you the specific connection string. 

[YinShi]

I used Oracle direct connection clients and I have both 32 bit and 64 bit clients. Can you confirm the syntax of my connection string? I tried both and neither worked.

jdbc:oracle:thin:@oracledevdb.ncc-ccn.ca:1521/sde11d

jdbc:oracle:thin:@oracledevdb:1521/sde11d

[Marten · Accepted solution]

I notice you have /sde11d in your connection string. Can you use :sde11d? all documentation I'm seeing specifies host:port:sid.

See: Features Specific to JDBC Thin (oracle.com)

I would also suggest testing the connection from an Oracle client from the harvester machine first from outside the harvester, to make sure port 1521 between the harvester machine and the Oracle server is open.

[YinShi]

Thanks a lot Marten. It was supposed to jdbc:oracle:thin:@oracledevdb:1521:sde11d as opposed to jdbc:oracle:thin:@oracledevdb:1521/sde11d

[YinShi]

Marten, 

Also I am wondering if we were buying some support hours on Geoportal/Harvester, should I buy it directly from you? Or do we need to go through our local ESRI distributor? 

Thanks,

yin