IPCC 7.0 & IVR - Passing Variable in a SQL statement to Oracle DB

[tyeung]

Hello all

I badly need help. My scenario:

I have a query which retrieves a value from a table in a MS SQL 2000 database.

----

SELECT Field_Points

FROM Table_Data

WHERE Field_Card_Id = $MyVar_EnteredID

----

$MyVar_EnteredID is the "passed" value.

This working fine.

Now, we have migrated the Database on Oracle version 10 and have recreated the ODBC connection. The table structure is similar to that in the Ms SQL.

The problem is that the query do not work anymore when passing value.

Anyone, faced similar issue and have a solution for same?

Thxs in advanced for your help

Cheers

[BCOLE2007]

I am having good success with Oracle 10g and CRS 4.0

Are you sure the Query works on Oracle logged in as the ID/Password from the ODBC driver? Use a literal as the Variable

a few things to try....

make sure the ID your ODBC driver is connecting and has the permissions able to access the table in question

1) try this

SELECT Field_Points

FROM SYSADM.Table_Data

WHERE Field_Card_Id = $MyVar_EnteredID

2) log in via TOAD, SQL Developer(download from Oracle) or SQL Plus, with the same ID/Password as the ODBC connection

3) also put this in a simple DB Read and run the test

Select * from dual, this should return 1 row

let me know how it goes.

[tyeung]

Hi

Thxs for yr help.

We realised that we were using a view and the timeout value for the query was too short. We are now referencing the table directly.

Again, thxs for the tips.

Cheers.

[amaia.lesta]

Dear all,

I am experiencing some problems when connecting my IPCCX 5.0(2) to an Oracle DB and from the post above I hope that you can help me.

As you I am able to query a local SQL database but I have problems to query an Oracle DB from a script.

I have checked from the database ODBC definition that the Connection to the ddbb works properly.

But when programming the script if I try to refresh the database schema the CRS Editor gets blocked. This happens since I have addedd the database in the Admin webpage.

For this addition I have configured the user and password for the database connection that where provided by the database adminsitrator.

What is this happening?

Thanks in advance for your kind help.

/Amaia

[BCOLE2007]

Hi Amaia

How did you check the ODBC definition is correct? Does the ODBC Test button return a successful, if not take that to your DBA

Are you or your DBA able to see that the CRS user ID has established a connection into Oracle , and are their as many connection as you have allocated in the CRS subsystem.

Also you have logged onto Oracle with SQL Plus, TOAD or Oracle's SQL Developer using the User ID/password supplied?

Lets start with these.

Blair

[amaia.lesta]

Dear Blair,

Thanks a lot for your answer. First of all I must tell you that I am not an expert in ddbb. I will try to answer to your questions:

1.- Yes, the ODBC test connection button returns OK.

2.- I will try that the DBA checks the Oracle connection matter.

3.- I don't know what do you mean by "logging onto Oracle with SQL Plus, TOAD or Oracle's SQL Developer using the User ID/password supplied". So I imagine that this could be the worng step. How can I do it?

As I told before the CRS editor gets blocked, it doesn't return any error. May it be a matter of firewalling (That the connection from CRS uses more ports than from the ODBC deffinition) or a matter of the ddbb size, that it is very big.

Once more thanks a lot for your kind help.

BR /Amaia

[BCOLE2007]

Hi Amaia,

1) ODBC is connecting, its a start

2) For each connection CRS makes into Oracle, the connection may have one CRSJAVAENGINE.EXE instance with the User ID, defined in the CRS database subsystem

3)

Most Databases can be accessed via a command line or some sort of tool to run SQL and other task. Talk to your Oracle DBA if they can help you install/access Oracle with a tool such as these...

Verify the User ID/Password has the permssions to run the SQL that the CRS is refreshing with. the Uers ID needs to be able to run this amoung others

select * from all_tab_columns

Blair

Please rate useful posts

[etmarcof]

Hi,

I have IPCC express 4.0.5 and i want to write in a SQL 2000 Database in table the vale of CallerID that is a string variable called ANI in IPCC scritp?

I'm tryng to use DB Write step with SQL Statement:

INSERT INTO calls (Phonenumber) values ($ANI)

I'm not having success. How do i reference IPCC variables in SQL statement?

Am i missing something?

Thanks

BEST Regards.

[BCOLE2007]

Hello,

Are you able to run this in SQL Server's Query Analyzer using the same ID/pass CRS is using to connect with SQL Server?

INSERT INTO calls (Phonenumber) values ('8001231234')

*Please rate all posts

Blair

[etmarcof]

Hi Blair,

yes,I'm able too run.

Also in IPCC script if i run your sql stament

INSERT INTO calls (Phonenumber) values ('8001231234')

it works.

The problem is that 8001231234 is stored in a IPCC script variable for exemple ANI="8001231234".

So what yould be the SQL statement to use these ANI variable instead of number?

Regards,

MC

[BCOLE2007]

Hello MC

ok since these inserts with the literal work inside and outside IPCC then lets look at the variable....IPCC script variable for ANI

How is ANI variable defined in your IPCC script?

also can you verify that ANI is populated with data and not null at the SQL execution by doing an reactive-Active Debug.

Your original post looks correct...

INSERT INTO calls (Phonenumber) values ($ANI)

something else to try

INSERT INTO calls values ( $ANI )

If this is a still a problem we will need to isolate the exact SQL server error in the IPCC Logs

Let me know how it goes.

Blair

*please rate all posts

[etmarcof]

Hello Blair,

Thanks for your Help. ANI is a string variable in IPCC. In SQL Phonenumber is varchar(50). In IPCC for troubleshooting purpose i config that variable with fixed value for example "80012345" but final purpose will be that this variable will be populated with Get call config step.

I tried both else SQL statements and nothing. I also tried INSERT INTO Calls (Phonenumber) values ('80012345') in IPCC and didn't work. But i tried from Query analyzer and it works.

Best Regrads,

MC

[BCOLE2007]

Hello MC,

I am understanding nothing is writing to SQL servier.....

When you log into Query Analzyer are you using the same ID and password that the ODBC driver and CRS Database Subsystem are configured to use?

*please rate all posts

Blair

[amaia.lesta]

Dear Blair,

Thanks a lot for your kind answer to my problem. We have been experiencing other issues and we postponed the DDBB integration. Now I'm back with it ;·)

With SQL Plus that I can access the database with the user name and password provided for an specific service. But when I try to perform a SELECT in that connection I can't. It always returns the same message "ORA-00942: table or view does not exist".

From the same computer with other username and password this query works perfectly and returns some values.

I guess that maybe my username and passsword rights are not the correct ones.

Best regards,

Amaia

[amaia.lesta]

Dear all,

Today I have performed some tests with the DB administrator.

The SQL plus SELECT is workign fine, I use a wrong state until know.

We have also checked that when in the CRS Editor I push the "Refresh database schema" in any DB action the user connects to the dabase.

The matter seems to be the following one:

the dabase is extremely big, so when performing a refresh CRS Editor tries to get every column in every table in every schema in the dabase. While this the CRS Editor gets block. In fact, it seems that it affects the whole IPCCX cluster (2 HA servers) and some agents gets disconnected.

It nevers gets all the info, sometimes it returns an "no able to access" error, other times it doesn't seem to finish and I delete the db definition from the admin web page.

The point is that we only need to get 2 columns from 2 tables, not all the other info in the db.

Is it possible to delimit the info that CRS Editor tries to get when pushing "Refresh"? We have tried something similar limitating the user access rights only to the 2 tables, but the behaviour keeps the same.

I also have been suggested to install Access locally in the server and create an Access database that is linked to the oracle one, only to the info needed. But CRS 5.0(2) is not compatible with Access. Does anybody know if something similar can be done with MS SQL?

Thanks a lot for your kind support.

Best regards,

Amaia