Netsuite error - The driver could not open a JDBC connection. Check the URL

[KristiLogos]

I'm trying to connect to Netsuite2 with the JDBC driver I added to my cluster. I'm testing this in my Sandbox Netsuite and I have the below code but it keeps saying:

requirement failed: The driver could not open a JDBC connection. Check the URL: jdbc:netsuite://xxxxx-sb1.connect.api.netsuite.com:1708;ServerDataSource=NetSuite2.com;Encrypted=1;NegotiateSSLClose=false ;

The role I created has the following Permissions -> Setup: Access Token Management, Log in using Access Tokens, REST Web Services, SOAP Web Services, SuiteAnalytics Connect (All set to FULL)

account_id = "xxx_SB1"

role_id = "1174" 

consumer_key = "xxx"

consumer_secret = "xxx"

token_id = "xxx"

token_secret = "xxx"

host = "xxx-sb1.connect.api.netsuite.com"

port = 1708

driver = "com.netsuite.jdbc.openaccess.OpenAccessDriver"

jdbc_url = (f"jdbc:netsuite://{host}:{port};"

            f"ServerDataSource=NetSuite2.com;"

            f"Encrypted=1;"

            f"NegotiateSSLClose=false;")

connection_properties = {

    "driver": driver,

    "CustomProperties": (

        f"AccountID={account_id};"

        f"RoleID={role_id};"

        f"AuthType=TOKEN;"

        f"TokenID={token_id};"

        f"TokenSecret={token_secret};"

        f"ConsumerKey={consumer_key};"

        f"ConsumerSecret={consumer_secret}"

    )

}

# testing on oa_tables..

df = spark.read.jdbc(

    url=jdbc_url,

    table="OA_TABLES",

    properties=connection_properties

)

display(df)

[TheOC]

Hey @KristiLogos 

I had a little search online and found this which may be useful:

https://stackoverflow.com/questions/79236996/pyspark-jdbc-connection-to-netsuite2-com-fails-with-fai...

in short it seems that a token based connection is not possible with spark/JDBC as the connection made is single use: and spark is effectively making two connections - one for metadata and one for data.

The author managed to solve their issue by swapping over to username/password authentication. Even if it’s not viable long-term, could you try making a connection this way instead, to confirm that is the issue?

[KristiLogos]

I tried the below and I see the same error:

account_id = "xxx_SB1"
role_id = "3"
email = "email"
password = "xxx"

host = "xxx-sb1.connect.api.netsuite.com"
port = 1708
driver = "com.netsuite.jdbc.openaccess.OpenAccessDriver"

jdbc_url = (f"jdbc:netsuite://{host}:{port};"
f"ServerDataSource=NetSuite2.com;"
f"Encrypted=1;"
f"NegotiateSSLClose=false;")

connection_properties = {
"user": email,
"password": password,
"driver": driver,
"CustomProperties": (
f"AccountID={account_id};"
f"RoleID={role_id};"
)
}

try:
df_tables = (spark.read
.jdbc(url=jdbc_url,
table="OA_TABLES", 
properties=connection_properties))

# Show the tables your role has access to
print("Successfully connected and retrieved list of available tables:")
df_tables.select("TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE").show(200, truncate=False)

except Exception as e:
print("Failed to connect and query OA_TABLES..")
print("Error details:")
raise e