EP 6.0 SP2 JDBC iView and Oracle Database

[oleg_yunets]

Hi,

I created Oracle system in Portal.

Then I creted JDBC iView for the system.

But iView always display the error:

"No response from the backend application".

And from portal log:

"CGT exception: java.lang.RuntimeException: bad execution, SQL Exception:[SAP_Portals][Oracle JDBC Driver][Oracle]ORA-00904: invalid column name"

Can anybody help me?

Thanks.

[Former Member]

Hi,

I'm having EP6.0 with SP2, and am trying to create a JDBC system from the system landscape on the portal and have given the following entries for the url and driver, but am not sure what needs to be given under the Distributed Query Engine catergory where in a user id and password are asked for.

the portal lets me create the system well but when creating the iview it says unable to connect to back end system, check your system settings and user mapping.

could you help.

url : jdbc:sap:oracle://132.12.33.30:1521;DatabaseName=SALES

driver :

com.sap.portals.jdbc.oracle.OracleDriver

we have a oracle 9i dagtabase that i'm trying to link

[AviadRivlin]

Hi,

In the SP2 Portal, the DQE parameters are optional, you are not required to fill them (at least not in your case, as far as I understand) - I would like to stress that this is only for SP2 cases, in more modern versions, these parameters <u>are</u> mandatory.

Rgarding your specific problem I would start from checking the following issues:

1. re-check all system parameters

2. re-check the user mapping

3. check the permissions on the system (at least read + End User)

4. If all of these does not help, try to work with another DB (if possible) and see if it works.

5. check that the JDBC connector is deployed in the J2EE engine (if you need help here, how to do it - let me know)

Hope this will help you,

Aviad

[Former Member]

Hi Aviad,

Ref to point1. the oracle databasse that I'm trying to connect to is installed on unix and within that there is a oracle user login. so what i've done is used the tnsnames entry to get he ip and port of the db server, and also the SId of the system we want to connect ad created the URL that I've pasted above. Hope this is right.

Ref to point 2. the other isues is when you say user mapping, i've assigned the oracle user id and password to the users' group. will this suffice ? what about the unix authentication ?? where do i give that info ?

Ref to point 3. the user id mappped in step 2 has all permissions.

Ref point 4. I shall try and see this option.

Ref point 5. Please do provide me with the help to check if the oracle jdbc connector has been provided. i shall check that also.

I really appreciate your time on this issue.

Thanking you in advance.

Regards,

Neeraj S

[AviadRivlin]

Hi,

1. In your URL, I think you are missing the SID of the Database: jdbc:sap:oracle://132.12.33.30:1521;<b>SID=???</b>DatabaseName=SALES

2. It should be OK (and sufficient); as far it is correct.

3. OK

4. I think that knowing this can help us.

5. Login to the Visual Admin tool -> Application Node (Server) -> Services -> eisconnector. Make sure that in the eisconnector you can find the JDBC connector.

Hope this information will help you,

Aviad

[jochen_spieth]

Hi,

maybe you first need to create a connection pool using the visual administrator or the telnet console.

Regards,

Jochen

[Former Member]

Dear Aviad,

Thnx a lot for your guidance, Incidentally we found the problem. For some reason the Portal was not able to resolve the ip address 132.12.33.30, but was able to resolve the host name "newspd".

This is the final url we created :

jdbc:sap:oracle://newspd:1521;SID=SALES

I'm able to create the iviews with the JDBC connection now, but the look is not so great, as there are a couple of problems :

1. The iview's table output has the raw column names showing as column titles. Atleast in SP2 there is no way to create a alternate column name. Ex: the table row name is say "amt_1", the column name in the iview also shows as "AMT_1". Is there a alternative for this problem ??

2. For passing parameters, the iview allows for only static parameters, but is there a way I can create a dynamic parameter allocation. Ex: In the query parameters I have a employee number as a included column, but I wish to show only his records to him, but in the portal environment, we havent a way to find the logged on employee ? Is there a way we can do that ??

Hope you'll be able to help.

Regards,

Neeraj S

[Former Member]

The log file indicates that the column name is invalid. Did you check your table? Also check if the Database is up and running.

Bhanu

[oleg_yunets]

Ok.

I checked columns names and they are correct.

I see JDBC iView does not enclose field names in double quotation marks.

Correct query must be like

SELECT SCHEMA.TABLE."Field" FROM SCHEMA.TABLE.

How to correct this behavior?