INSERT/UPDATE sql statements in composer's DB Data block gives an error [SOLVED]

[anoredis]

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!

[Kubig]

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

[GMG]

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

[anoredis]

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...

[GMG]

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

[anoredis]

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?)

[cavagnaro]

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

[anoredis]

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!

[cavagnaro]

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

[anoredis]

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

[anoredis]

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

[pantm]

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