search

daust / JasperReportsIntegration

JasperReportsIntegration provides an interface to use the JasperReports reporting engine in an Oracle database application, specifically with Oracle Application Express (Oracle APEX).
BSD 3-Clause "New" or "Revised" License
55 stars 23 forks source link

error connection database on 2.5.0.1 #28

Closed rortin closed 4 years ago

rortin commented 4 years ago

hi, i have install and configure on centos 7 with the las oracle XE. Jasperreport server is online, but when i go to do a test its say this:

java.lang.RuntimeException: Could not connect via JDBC: Excepción de E/S: Invalid connection string format, a valid format is: "host:port:sid"

in application.properties i have:

url=jdbc:oracle:thin:@127.0.0.1:1539\XEPDB1

where XEPDB1 is my service i cant connect with this configuration on Ireport and sqldeveloper.

Can you help me??

Thanks

rortin commented 4 years ago

i have tried whit ojdbc8 and ojdbc10, the result its the same.

PeterPSSE commented 4 years ago

Have you tried: url=jdbc:oracle:thin:@127.0.0.1:1539:XEPDB1 Mind the last colon instead of slash!

rortin commented 4 years ago

Hi Peter:

I tried it... the result:

java.lang.RuntimeException: Could not connect via JDBC: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was: localhost:1539:xepdb1

This is my tnsnames.ora

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

LISTENER_XE = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))

PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1) ) )

I trie with PDB1 too... but always its the same result with colons.

sb20200428 commented 4 years ago

As long as you have the port number correct, the answer is the direction of the slash in your connection string. May not be the only issue, but definitely should be as below.

url=jdbc:oracle:thin:@127.0.0.1:1539\XEPDB1

Should be

url=jdbc:oracle:thin:@127.0.0.1:1539/XEPDB1

On Jun 10, 2020, at 12:25 PM, rortin notifications@github.com wrote:

 Hi Peter:

I tried it... the result:

java.lang.RuntimeException: Could not connect via JDBC: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was: localhost:1539:xepdb1

This is my tnsnames.ora

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

LISTENER_XE = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))

PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1) ) )

I trie with PDB1 too... but always its the same result with colons.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

rortin commented 4 years ago

Thanks @sb20200428 but when i put the /, the result is:

java.lang.RuntimeException: Could not connect via JDBC: Excepción de E/S: Invalid connection string format, a valid format is: "host:port:sid"

rortin commented 4 years ago

i tried to do this in listener.ora USE_SID_AS_SERVICE_listener=on

and in application.properties:

url=jdbc:oracle:thin:@127.0.0.1:1539:XEPDB1

but the error is :

java.lang.RuntimeException: Could not connect via JDBC: ORA-01017: nombre de usuario/contraseña no válidos; conexión denegada

user and password not valid??

so it dont works. Some ideas?? this user and password its using on ireport and sqldeveloper, so its correct.

sb20200428 commented 4 years ago

If you have any special characters in the PW, you may need to enclose in quotes within the config file

rortin commented 4 years ago

not its the same error

PeterPSSE commented 4 years ago

There is a difference between SID and SERVICE_NAME, see here:
https://stackoverflow.com/questions/43866/how-sid-is-different-from-service-name-in-oracle-tnsnames-ora According to your tnsnames.ora "XEPDB1" is your service name. How is your SID ? There are various valid formats for the jdbc connect string: https://stackoverflow.com/questions/4832056/java-jdbc-how-to-connect-to-oracle-using-service-name-instead-of-sid Good luck.

sb20200428 commented 4 years ago

Your tnsnames.ora should look like the following. Need to restart the listener and java server after making changes, too. If the below doesn't work, there is something more going on.

I've seen issues in past w/ using 'localhost' in the tnsnames.ora file - but those would not be specific to JasperReportsIntegration - if your 'lsnrctl status' output looks good, you should be good to connect.

Don't know how else to help. Good luck.

tnsnames.ora

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

XEPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1) ) )

LISTENER_XE = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))

Jasper Connection String

[datasource:sample] type=jdbc name=sample url=jdbc:oracle:thin:@127.0.0.1:1539/XEPDB1 username=sample_user password="sample_pw"

rortin commented 4 years ago

impossible, i have to intall a new server with oracle 18c xe, apex 20.1, ords and Jasperreportintegration.

If i write: url=jdbc:oracle:thin:@127.0.0.1:1539/XEPDB1 in my config, its said the same...

java.lang.RuntimeException: Could not connect via JDBC: Excepción de E/S: Invalid connection string format, a valid format is: "host:port:sid"

And if i put the correct string: url=jdbc:oracle:thin:@127.0.0.1:1539:XEPDB1, its sais:

"ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"

if i put de XE SID: url=jdbc:oracle:thin:@127.0.0.1:1539:XE

"Could not connect via JDBC: ORA-28040: No coincide ningún protocolo de autenticación"

The service is valid, because i start with it sqldeveloper and ireport, so ... i havent no ideas.... some one more????

rlljorge commented 4 years ago

impossible, i have to intall a new server with oracle 18c xe, apex 20.1, ords and Jasperreportintegration.

If i write: url=jdbc:oracle:thin:@127.0.0.1:1539/XEPDB1 in my config, its said the same...

java.lang.RuntimeException: Could not connect via JDBC: Excepción de E/S: Invalid connection string format, a valid format is: "host:port:sid"

And if i put the correct string: url=jdbc:oracle:thin:@127.0.0.1:1539:XEPDB1, its sais:

"ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"

if i put de XE SID: url=jdbc:oracle:thin:@127.0.0.1:1539:XE

"Could not connect via JDBC: ORA-28040: No coincide ningún protocolo de autenticación"

The service is valid, because i start with it sqldeveloper and ireport, so ... i havent no ideas.... some one more????

I have the same issue . @rortin do you can resolve this ?

ViktorAfnt commented 4 years ago

Try to delete the ojdbc14.jar file. Use ojdbc6.jar or ojdbc8.jar instead it. See What are the Oracle JDBC releases Vs JDK versions?

rlljorge commented 4 years ago

@ViktorAfnt perfect using only ojdbc8.jar works fine ! Thank you !

daust commented 4 years ago

The upcoming release will include updated libraries for ojdbc8 supporting Oracle 19+ databases.