Select to view content in your preferred language

JDBC connector settings for harvester 2.6.5

1149
10
Jump to solution
06-23-2022 08:38 AM
YinShi
by
New Contributor III

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

 

 

YinShi_0-1655998383012.png

 

0 Kudos
1 Solution

Accepted Solutions
Marten
by
Occasional Contributor

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.

View solution in original post

0 Kudos
10 Replies
Marten
by
Occasional Contributor

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

 

DatabaseDriver NameConnection StringSee also:
Microsoft SQL Servercom.microsoft.sqlserver.jdbc.SQLServerDriverjdbc:sqlserver://servername:1433;databaseName=dbnameMicrosoft JDBC Driver for SQL Server - JDBC Driver for SQL Server | Microsoft Docs
PostgreSQLorg.postgresql.Driverjdbc:postgresql://host:port/databaseConnecting to the Database (postgresql.org)
Oracleoracle.jdbc.OracleDriverjdbc:oracle:driver_type:[username/password]@[//]host_name[:port][:oracle]Connecting to Oracle Database 12c Release 1 (12.1)

 

 

 

0 Kudos
YinShi
by
New Contributor III

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! 

YinShi_0-1656096835355.png

 

 

0 Kudos
Marten
by
Occasional Contributor

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?
0 Kudos
YinShi
by
New Contributor III

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. 

YinShi_0-1656098337802.png

 

0 Kudos
Marten
by
Occasional Contributor

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

0 Kudos
YinShi
by
New Contributor III

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

0 Kudos
Marten
by
Occasional Contributor

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.

0 Kudos
YinShi
by
New Contributor III

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

0 Kudos
YinShi
by
New Contributor III

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

0 Kudos