Parameter Pass in call studio

[Ritesh Desai]

Hi folks,

Need guidance, how should I pass parameters to an DATABASE element in Unified Call Studio v10.5. Logic is passing parameters will return values from database. (JDBC connectors are configured already).

regards,

Ritesh Desai.

[Ritesh Desai]

Hi team,

Any updates...? Client has shared Store Procedures. I have studied from Call Studio element guide,  it says it MAY NOT support for complex queries and Store Procedures.

On Cisco Communities there is huge thread on this discussion, i read it supports. Has anyone worked on store procedures?

Feedback is appreciated.

thanks and regards,

Ritesh Desai.

[Chintan Gajjar]

Hey Ritesh,

the builtin DB element supports on Basic DB operation.

please share how does your DB query looks like and then we can comment if its going to be supported or not.

[Ritesh Desai]

Thanks Chintan for revert.

Customer has shared the Store Procedures not an SQL query.

Store Procedures looks like usp_get_sr_det_on_rmn_for_ivr_new 'Mobile No'

Executing this will bring necessary details of customer from db. Created JNDI connector as well.

regards,

Ritesh Desai.

[Chintan Gajjar]

You can run stored procedure from CVP DB element,

you have to get the query associated with that stored procedure and use that in your DB element.

Please ask DBA to provide you the query.

[Ritesh Desai]

Chintan,

Thanks for positive revert. I was confused whether I can use DB element for calling Store Procedure or not and YES, I CAN use it!!!

As I said, customer has given store procedure name not the SQL QUERY. I execute Store Procedure from SQL DB and it returns me the data. by far we don't have access to SQL queries .

I just need to see from my eyes whether it successfully executes and return values in DB.

thanks once again

regards,

Ritesh Desai

[Chintan Gajjar]

Hi Ritesh,

were you able to execute stored procedure from CVP DB element?

have checked your application activity logs for the successful operation.

Chintan

[Ritesh Desai]

Hi Chintan,

No, I have not. Customer said they are running IVR currently on Store Procedures itself.

Surely I will update you on this.

regards,

Ritesh Desai.

[Ritesh Desai]

Hi Chintan,

We have used Web-services element which will execute Store Procedure accordingly and return values according to parameters passed.

In the VXML Activity logs, executing Store Procedure by IVR throws substitution error of empty string executed.

regards,

Ritesh Desai

[Chintan Gajjar]

Why are you using webservice element for executing stored procedure?

You are not meant to use Web Service element for SQL operations, it works with SOAP based webservice. is your stored procedure stuff hosted with webservice?

regards

Chintan

[Ritesh Desai]

Hi Chintan,

Sorry for long delay in revert. We are using SOAP based Webservice to execute Store Procedures. We have created 15 webservice methods for 15 store procedures.

Its working smoothly w/o any interuption except, time taken by Store-procedure to return results to webservices and to VXML.

I didn't understood your last question. interested to answer.

Thanks & Regards,

Ritesh Desai

[Kleber Rodrigues]

Hi Ritesh,

Yes, you can use procedures on database element in call studio. I have one working here, where sql queyr is exec procedure;

Maybe sql dba need to give JNDI user write access to database table and configure value returned by procedure to variable name you will use to store this data into call studio session. In my case, this procedure is done in data element Cons_Protocolo and it return variable Protocolo and i have a session data with variable name prot_auto receiving value {Data.Element.Cons_Protocolo.Protocolo} creating this store after sql query is finished. All elements are case sensitive, so be careful when write your code.

Try this, and if you have some trouble, i can send some pictures about my script, so you could see it better.

Best Regards,

Kleber

[Ritesh Desai]

Hi Kleber,

Thanks for guidance. I lost expectations on this. Appreciated Thanks.

One question.... Why do we need JNDI name write permission on user for specific DB. Definately my client will ask same question I have raised. Request I you can share me snapshot for more idea...

Honestly, I have proceed far ahead and am using Web-service for calling Store Procedures and capture return values, hence accordingly used session variables. I have tested this and works perfectly fine.

Looking forward to learn from you. If any questions related to Webservice let me know.

thanks & regards,

Ritesh Desai.

[Kleber Rodrigues]

Hi Ritesh,

I just attached 2 pictures, showing how my Database Element was configured. When database element use exec procedure, result is send using sql variable Protocolo, which i use to save data session in vxml variable Prot_Auto. This vxml variable Prot_Auto will be used later in script.

In my case, procedure has some elements which need to write in table database, so because this we need to JNDI user has write access, otherwise sql did not permit to execute this procedure for JNDI user.

So, you have to ask dba admin to see if your JNDI user has right permissions to all components are used in the procedure, including write access or other tables that procedure use. If you use only one table and don't have anything to write in any database, you will not write access.

Best Regards,

Kleber

[Ritesh Desai]

Hi Kleber,

OMG.... Kleber ur punished of being late. ;)

I was near to goal friend, i did most of the thing as per last post. Only thing which was missing / pending was assign write access to JNDI user. I wished if you have arrived earlier to take it further.

Anyways thanks a lot for your time and knowledge in sharing and great learning from this post. Will try it in my office Lab environment. This was a great motivation!!!

Cheers Mhann!!!

regards,

Ritesh Desai