Mich,
That's right, referring to you guys.
Cheers, Kuassi
On 8/27/20 9:27 AM, Mich Talebzadeh wrote:
Thanks Kuassi,
I presume you mean Spark DEV team by "they are using ... "
cheers,
Mich
LinkedIn
/https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://urldefense.com/v3/__https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw__;!!GqivPVa7Brio!M88sYUJxwSuzuyOSFENThsP9nncvOVkQvjolE69LmD9sUxRhpRDNkuuKPwHsPVs4NQ$>/
*Disclaimer:* Use it at your own risk.Any and all responsibility for
any loss, damage or destruction of data or any other property which
may arise from relying on this email's technical content is explicitly
disclaimed. The author will in no case be liable for any monetary
damages arising from such loss, damage or destruction.
On Thu, 27 Aug 2020 at 17:11, <kuassi.men...@oracle.com
<mailto:kuassi.men...@oracle.com>> wrote:
According to our dev team.
From the error it is evident that they are using a jdbc jar which
does not support setting tns_admin in URL.
They might have some old jar in class-path which is being used
instead of 18.3 jar.
You can ask them to use either full URL or tns alias format URL
with tns_admin path set as either connection property or system
property.
Regards, Kuassi
On 8/26/20 2:11 PM, Mich Talebzadeh wrote:
And this is a test using Oracle supplied JAVA
script DataSourceSample.java with slight amendment for
login/password and table. it connects ok
hduser@rhes76: /home/hduser/dba/bin/ADW/src> javac -classpath
./ojdbc8.jar:. DataSourceSample.java
hduser@rhes76: /home/hduser/dba/bin/ADW/src> java -classpath
./ojdbc8.jar:. DataSourceSample
AArray = [B@57d5872c
AArray = [B@667a738
AArray = [B@2145433b
Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0
Default Row Prefetch Value is: 20
Database Username is: SCRATCHPAD
DATETAKEN WEIGHT
---------------------
2017-09-07 07:22:09 74.7
2017-09-08 07:26:18 74.8
2017-09-09 07:15:53 75
2017-09-10 07:53:30 75.9
2017-09-11 07:21:49 75.8
2017-09-12 07:31:27 75.6
2017-09-26 07:11:26 75.4
2017-09-27 07:22:48 75.6
2017-09-28 07:15:52 75.4
2017-09-29 07:30:40 74.9
Regards,
LinkedIn
/https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://urldefense.com/v3/__https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw__;!!GqivPVa7Brio!K1lIv4Tn9yeWXGcfb2Zru8i7NZguGuAy1VxoSqORVtoQ_AJbkZohU0cXYquoFAJWTA$>/
*Disclaimer:* Use it at your own risk.Any and all responsibility
for any loss, damage or destruction of data or any other property
which may arise from relying on this email's technical content is
explicitly disclaimed. The author will in no case be liable for
any monetary damages arising from such loss, damage or destruction.
On Wed, 26 Aug 2020 at 21:58, Mich Talebzadeh
<mich.talebza...@gmail.com <mailto:mich.talebza...@gmail.com>> wrote:
Hi Kuassi,
This is the error. Only test running on local mode
scala> val driverName = "oracle.jdbc.OracleDriver"
driverName: String = oracle.jdbc.OracleDriver
scala> var url =
"jdbc:oracle:thin:@mydb_high?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess"
url: String =
jdbc:oracle:thin:@mydb_high?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess
scala> var _username = "scratchpad"
_username: String = scratchpad
scala> var _password = "xxxxxxxxxx" -- no special characters
_password: String = xxxxxxxxxxx
scala> var _dbschema = "SCRATCHPAD"
_dbschema: String = SCRATCHPAD
scala> var _dbtable = "LL_18201960"
_dbtable: String = LL_18201960
scala> var e:SQLException = null
e: java.sql.SQLException = null
scala> var connection:Connection = null
connection: java.sql.Connection = null
scala> var metadata:DatabaseMetaData = null
metadata: java.sql.DatabaseMetaData = null
scala> val prop = new java.util.Properties
prop: java.util.Properties = {}
scala> prop.setProperty("user", _username)
res1: Object = null
scala> prop.setProperty("password",_password)
res2: Object = null
scala> // Check Oracle is accessible
*scala> try {
*
* | connection = DriverManager.getConnection(url,
_username, _password)*
* | } catch {*
* | case e: SQLException => e.printStackTrace*
* | connection.close()*
* | }*
*java.sql.SQLRecoverableException: IO Error: Invalid
connection string format, a valid format is: "host:port:sid"*
at
oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
at
oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
at
oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
at
oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at
oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
at
java.sql.DriverManager.getConnection(DriverManager.java:664)
Is this related to Oracle or Spark? Do I need to set
up another connection parameter etc?
Cheers
*Disclaimer:* Use it at your own risk.Any and all
responsibility for any loss, damage or destruction of data or
any other property which may arise from relying on this
email's technical content is explicitly disclaimed. The
author will in no case be liable for any monetary damages
arising from such loss, damage or destruction.
On Wed, 26 Aug 2020 at 21:09, <kuassi.men...@oracle.com
<mailto:kuassi.men...@oracle.com>> wrote:
Mich,
All looks fine.
Perhaps some special chars in username or password?
it is recommended not to use such characters like '@',
'.' in your password.
Best, Kuassi
On 8/26/20 12:52 PM, Mich Talebzadeh wrote:
Thanks Kuassi.
This is the version of jar file that work OK with JDBC
connection via JAVA to ADW
unzip -p ojdbc8.jar META-INF/MANIFEST.MF
Manifest-Version: 1.0
Implementation-Title: JDBC
*Implementation-Version: 18.3.0.0.0*
sealed: true
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Class-Path: oraclepki.jar
Implementation-Vendor: Oracle Corporation
Main-Class: oracle.jdbc.OracleDriver
Ant-Version: Apache Ant 1.7.1
Repository-Id: JAVAVM_18.1.0.0.0_LINUX.X64_180620
Created-By: 25.171-b11 (Oracle Corporation)
Specification-Version: 4.0
And this the setting for TNS_ADMIN
e*cho ${TNS_ADMIN}*
*/home/hduser/dba/bin/ADW/DBAccess*
hduser@rhes76: /home/hduser/dba/bin/ADW/DBAccess> *cat
ojdbc.properties*
*# Connection property while using Oracle wallets.*
*oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))*
*# FOLLOW THESE STEPS FOR USING JKS*
*# (1) Uncomment the following properties to use JKS.*
*# (2) Comment out the oracle.net.wallet_location
property above*
*# (3) Set the correct password for both
trustStorePassword and keyStorePassword.*
*# It's the password you specified when downloading the
wallet from OCI Console or the Service Console.*
*#javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks*
*#javax.net.ssl.trustStorePassword=<password_from_console>*
*#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
*#javax.net.ssl.keyStorePassword=<password_from_console>hduser@rhes76:
/home/hduser/dba/bin/ADW/DBAccess>*
Regards,
Mich
LinkedIn
/https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://urldefense.com/v3/__https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw__;!!GqivPVa7Brio!LxAFleT1w3dN53Njh2o9xm_GtQd-d0NTouqw1mBYLroe4Byzc1nvSN0rb-cnpRttfw$>/
*Disclaimer:* Use it at your own risk.Any and all
responsibility for any loss, damage or destruction of
data or any other property which may arise from relying
on this email's technical content is explicitly
disclaimed. The author will in no case be liable for any
monetary damages arising from such loss, damage or
destruction.
On Wed, 26 Aug 2020 at 20:16, <kuassi.men...@oracle.com
<mailto:kuassi.men...@oracle.com>> wrote:
Hi,
From which release is the ojdbc8.jar from? 12c, 18c
or 19c? I'd recommend ojdbc8.jar from the latest
release.
One more thing to pay attention to is the content of
the ojdbc.properties file (part of the unzipped wallet)
Make sure that ojdbc.properties file has been
configured to use Oracle Wallet, as follows (i.e.,
anything related to JKS commented out)
/oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))//
//#javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks//
//#javax.net.ssl.trustStorePassword=<password_from_console>//
//#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks//
//#javax.net.ssl.keyStorePassword=<password_from_console>/
Alternatively, if you want to use JKS< then you need
to comment out the firts line and un-comment the
other lines and set the values.
Kuassi
On 8/26/20 11:58 AM, Mich Talebzadeh wrote:
Hi,
The connection from Spark to Oracle 12c etc are
well established using ojdb6.jar.
I am attempting to connect to Oracle Autonomous
Data warehouse (ADW) version
*Oracle Database 19c Enterprise Edition Release
19.0.0.0.0*
Oracle document suggest using ojdbc8.jar
<https://urldefense.com/v3/__http://ojdbc8.jar__;!!GqivPVa7Brio!Msuw5mr2YjeHSLbBSlNvs8rqL7T_-eWFfdsamiYduARIsECZqEzUTG8hd-v1x8KwcQ$>
to
connect to the database with the following URL
format using Oracle Wallet
"jdbc:oracle:thin:@mydb_high?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess"
This works fine through JAVA itself but throws an
error with Spark version 2.4.3.
The connection string is defined as follows
val url =
"jdbc:oracle:thin:@mydb_high?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess"
where DBAcess directory is the unzipped wallet for
Wallet_mydb.zip as created by ADW connection.
The thing is that this works through normal
connection via java code.using the same URL
So the question is whether there is a dependency in
Spark JDBC connection to the ojdbc.
The error I am getting is:
java.sql.SQLRecoverableException: IO Error: Invalid
connection string format, a valid format is:
"host:port:sid"
at
oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
at
oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
at
oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
at
oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at
oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
at
java.sql.DriverManager.getConnection(DriverManager.java:664)
This Oracle doc
<https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-jdbc-thin-wallet.html#GUID-5ED3C08C-1A84-4E5A-B07A-A5114951AA9E>
explains the connectivity.
The unzipped wallet has the followiing files
ls DBAccess/
README cwallet.sso ewallet.p12 keystore.jks
ojdbc.properties sqlnet.ora tnsnames.ora truststore.jks
Thanks
Mich
LinkedIn
/https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://urldefense.com/v3/__https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw__;!!GqivPVa7Brio!Msuw5mr2YjeHSLbBSlNvs8rqL7T_-eWFfdsamiYduARIsECZqEzUTG8hd-teislmnw$>/
*Disclaimer:* Use it at your own risk.Any and all
responsibility for any loss, damage or destruction
of data or any other property which may arise from
relying on this email's technical content is
explicitly disclaimed. The author will in no case
be liable for any monetary damages arising from
such loss, damage or destruction.
