Genesys CTI User Forum

Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: anoredis on September 23, 2013, 09:14:07 AM

Title: INSERT/UPDATE sql statements in composer's DB Data block gives an error [SOLVED]
Post by: anoredis on September 23, 2013, 09:14:07 AM

Hello there!

I am building an application with genesys composer and at some point I need to update records in a MSSQL server database. Till now I was able to use SELECT commands in sql files defined in DB Data block but this has no luck when using INSERT/UPDATE sql statements.
I have the rights to make changes to the database and I have tested the statements I am using through a db management tool and work fine.
My query is something like:
[code]UPDATE policies SET agentID = '1' WHERE tNo = {setTNo} AND tPersNo = {setTPersNo}[/code]


Thanks in advance!

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: Kubig on September 23, 2013, 09:33:32 AM

And what is the problem, any error message or something else?

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: GMG on September 23, 2013, 10:03:21 AM

Hi User,

You need to use Procedure for this.

Create a procedure for "insert/Update" and call it from composer, by default you can use "select" statement from composer.

--
GMG

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: anoredis on September 23, 2013, 10:18:42 AM

Thank you both for the quick response!
@ Kubig The error I get is the general error I have defined in the Start block's exceptions.
@ GMG Is that the case? cause in the manual it doesn't say so. I will try it though...

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: GMG on September 23, 2013, 12:46:46 PM

Best way you have to do, you can use the file option given.

Save this/below query into file and call the file from DB block. (Procedure also not the best way)

"UPDATE policies SET agentID = '1' WHERE tNo = {setTNo} AND tPersNo = {setTPersNo}"

No that thing not mentioned in doc. i guess.

--
GMG

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: anoredis on September 23, 2013, 01:36:27 PM

Thank you GMG, this is the way I am implementing it. I am storing this query inside a .sql file under the db folder in the file structure of my application. Still gives me an error.. :/ (Do I have to include the double quotes or you just entered them to distinguish the statement from the rest of your response?)

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: cavagnaro on September 23, 2013, 02:16:29 PM

Why don't create a backend file and call it? More flexible and you have control...

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: anoredis on September 24, 2013, 10:31:05 AM

Thanks for the suggestion cavagnaro. Could you please elaborate or provide simple example because I have never used a Backend Block before?
Thank you for your time!

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: cavagnaro on September 24, 2013, 05:10:25 PM

It is just a JSP or ASP functions page. When you create one on Composer you have a small sample.
Basically you input and output parameters. Input could be an array with the data you want to insert then you create a function which on JSP or ASP will do the insert and return maybe a OK if it was inserted or KO if something wrong happened.
There is also some help on the CHM file of composer

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: anoredis on October 01, 2013, 01:20:28 PM

Thanks cavagnaro, i will try to do it this way and come back with the results. Thanks again!

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error
Post by: anoredis on October 16, 2013, 12:09:46 PM

There was a huge error from my point, I didn't set the suppressed empty result to true... PROBLEM SOLVED

Title: Re: INSERT/UPDATE sql statements in composer's DB Data block gives an error [SOLVED]
Post by: pantm on March 20, 2017, 09:35:13 PM

Hi Anoredis,

I was going through this thread. How did you eventually solved the issue. I am also looking to update IXN DB properties from Composer. However, don't find any specific block for this as we had in IRD? Thank You.

Regards
Maneesh