** ERROR: java.sql.SQLException: IO Error: Invalid connection string format, a valid format is: “host:port:sid”

by ·0 Comments

Product:

IBM Cognos Controller 10.3

Microsoft Windows 2008 R2 server

Oracle 12c database

 

Symptom:

When click on connect inside DBCONV program to upgrade a database you get an error.

On the windows server, you have installed both 64 bit and 32 bit oracle 12c admin clients.

 

You get same error if you start DBCONV.EXE direct from here C:\Program Files\ibm\cognos\ccr_64\ and try to connect to a database.

 

Error message:

** ERROR: java.sql.SQLException: IO Error: Invalid connection string format, a valid format is: “host:port:sid”

 

Possible Cause:

The jar file in folder C:\Program Files\ibm\cognos\ccr_64 is wrong.

You may be using a ojdbc5.jar file.

or change to use only SID name as data source.

Solution:

Copy the ojdbc6.jar file to get it to work.

Then you will get this error;

** ERROR: java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier  “pp1140.companyx.com”

 

That is because your TNSNAME.ORA file is in wrong format.

 

Maybe like this, that does not work with DBCONV.EXE

 

PP1140.companyx.com =

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lx2814.companyx.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PP1140.COMPANYX.COM)))

 

Below format is the valid one to use for Cognos Controller.

 

PP1140.companyx.com =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=lx2814.companyx.com)(PORT=1521))

(CONNECT_DATA=(SERVICE_NAME=PP1140.companyx.com))

)

 

But if you change Cognos Controller to call the database with name only and not FQDN it will work fine with the OJDBC5.JAR file.

In Cognos Controller configuration for the database connection go to the last line for Data Source and change from pp1140.companyx.com to pp1140 only and click save.

Then the DBCONV will also work with the ojdbc5.jar file (that is 2043 Kb in size).

You need to update the TNSNAMES.ORA to contain

 

PP1140 =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=lx2814.companyx.com)(PORT=1521))

(CONNECT_DATA=(SERVICE_NAME=PP1140.companyx.com))

)

 

The JAVA menus in Cognos Controller 10.3 can only handle data source of a name, no periods are allowed in the connection string to the oracle database.