Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

INSERTING DYNAMIC DATA INTO SQL USING CRA DB WRITE

I'm trying to insert dynamic data in an MS SQL database. It s a new database and all I want to do is to insert data into via the CRA Editor DB write feature. Basically, I want to capture a Customer C.E.D entry which is meant to be their telephone number. I'm using a variable to capture this in the IP-IVR already and it works and I can see the captured data under the custom variables in the CRA Historical report, but I'm unable to write this info dynamically into the database. I can only write static data to it, like 'insert into table (TelNum, CLID) values (02089997766, 5544). Any help to resolve this will be highly appreciated.

4 REPLIES

Re: INSERTING DYNAMIC DATA INTO SQL USING CRA DB WRITE

When you are using variables in the sql statement, use the $ before the variable name.

For example, If your telnum variable name is varTelNum and CallerID is in variable varCLID and your table name is TestTable with fields TelNum and ClID, then your insert statement will be as follows:

Insert into TestTable(TelNum, CLID) values ($varTelNum, $varCallerID)

So, the format is

Insert into (

,
,....,
) values ($, $,....,$)

Hope this helps. You can dummy some data into the variables and use the test button in the DBWrite node to test the insert.

Good luck. Please rate posts

New Member

Re: INSERTING DYNAMIC DATA INTO SQL USING CRA DB WRITE

Thanks Sanjeev for your prompt reply. I'd tried that already but it didnt work. It comes up with a 'null java pointer exception'.

This is what I have in the DB write at the moment: insert into CustDetail (TelNum, CLID)

execute dbo.custNum '@telephoneNumber', '@CLID'

Then I have a procedure in MS SQL (custNum) that it calls:

CREATE procedure dbo.custNum

@telephoneNumber char(50),

@CLID char(50)

as

insert into CustDetail (TelNum, CLID)

values (@telephoneNumber, @CLID)

GO

This just writes @telephoneNumber ... to the database. I have tried different query combinations but to no avail, and so I've just paused here even though its not working as it should.

Another procedure I'd tried was

CREATE procedure dbo.custNum

(@telephoneNumber char(11), @CLID char(11))

as

declare @cust nvarchar(50)

set @cust = 'insert into CustDetail (TelNum, CLID) values (@telephoneNumber, @CLID)';

GO

Any assistance would be greatly appreciated.

I will continue to toy with your suggestion however.

Thanks,

Lola

Re: INSERTING DYNAMIC DATA INTO SQL USING CRA DB WRITE

You cannot combine a insert statement with the stored procedure statement. That sql statement is wrong. You need to just use the "execute ...statement". Also you cannot use @telephonenumber, @clid etc, For an ivr script to pass values, you need to say $telephonenumber, $CLID.

I will try to look for an example with stored proc.

Please rate posts

New Member

Re: INSERTING DYNAMIC DATA INTO SQL USING CRA DB WRITE

Hi Sanjeev,

I've tried all the query combinations you've suggested but to no avail. The DB write SQL and Test tabs doesnt allow any other SQL statements to start with anyword other than insert, update and delete. So I cant run the execute statement from the 'db write cra editor'. Any working examples will be highly appreciated.

Many thanks,

Lola

251
Views
5
Helpful
4
Replies
CreatePlease login to create content