Errors inserting call variable data into a SQL DB using CVP database element

[dsemmler]

Hi,  I am having issues with inserying call variable data into a SQL 2005 database.  I can successfully write the values from my "Digits" element and the ANI to the DB, but call variables are throwing VXML errors, complaining about the “column name”.  It seems to be trying to access a column with the name of the actual call variable data field..

Below is the config information:

VXML App overview

  ICM Routing script

User.microapp.ToExtVXML

  concatenate("application=PCS;","ani=",Call.CallingLineID,";","pv7=",Call.PeripheralVariable7,";")

*** PV7 contains the enterprise name of the CallType through an ICM DB lookup

Store PV7 as element data

I am converting the PV7 session data to Element data in the output of the first Digits element.  I have tried both session data and Element data, they both return the same VXML error.

  SQL Query

INSERT INTO pcs (ani,q1,q2,pv7) VALUES ({CallData.ANI},{Data.Element.Question_1.value},{Data.Element.Question_2.value},{Data.Element.Question_1.CallType})

VXML App Activity Log

Attached

VXML App error log

Attached

Currently this is a POC, so the final scripts might look very different, but I need to get the call variable data into a database..

Any help greatly appreciated…  I am fairly new to CVP call studio application writing..

Darren

[janinegraves]

Hi

1) I see from your Activity Log that the value of the Session Variable pv7 is "PostCallSurvey"

start,parameter,pv7=PostCallSurvey

- is that what you expect? Is that a valid value to insert into your SQL table?

What are your column names in the pcs table -  are they ani, q1, q2, pv7?

And what types are these columns? If they are character, you should embed single quotes around each Studio variable name '{CallData.ANI}'  for example.

2) It's not clear why it thinks you're looking for a column named PostCallSurvey though!

Why don't you assign your query into a Session Variable and then use the AddToLog box to add it to the activity log. Then you can see what query you're actually executing.

NOTE -

3) You can simplify your ICM concatenate string to this

concatenate("application=PCS;ani=",Call.CallingLineID,";pv7=",Call.PeripheralVariable7,";")

4) There's no need to assign the Studio Session.pv7 variable into Element data. When you go into the Substitution window, and select Session Data, just enter the name pv7 from your keyboard, then press AddTag.

The you can modify your SQL command to be this:

INSERT INTO pcs (ani,q1,q2,pv7) VALUES ({CallData.ANI},{Data.Element.Question_1.value},{Data.Element.Question_2.value},{Data.Session.pv7})

[dsemmler]

Hi Janine,  thanks for the prompt response.

1.     Yes, "PostCallSurvey" is what I am expecting in PV7, and yes the column names are ani, q1, q2, pv7

ani, q1, q2 are integers, but PV7 is a char(40), I have tried varchar as well..

I have tried '{Data.Session.pv7}' but it trows a different error now..

"A built-in element encountered an exception of type com.audium.server.AudiumException. The error was: String or binary data would be truncated. The root cause was: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated. "

2.     ok, I have done this, and it looks OK to me..

audio,data,sql1,INSERT INTO pcs (aniq1q2pv7) VALUES (7001341PostCallSurvey)   (70013 is the ANI, 4 and 1 are the answers to my questions...)

3.     Done..  thanks for that

4.     ok, noted, have changed the app to use session data now..

so net result, is that I still have the issue at hand..  are there any debug levels I should turn up to see why the SQL driver is tryng to reference the wrong column name ??

tks,  Darren

[janinegraves]

What happens if you just 'hard code' some values to enter into the DB and see what happens.

INSERT INTO pcs (ani,q1,q2,pv7) VALUES (70013,4,4,'Test') 

[janinegraves]

If you're using the Studio database element, did you select Insert for

the first setting?

[janinegraves · Accepted solution]

OK, I just tried this using a MySQL database and was able to come close to replicating your problem.

I did an Insert statement and omitted the quotes around the variable writing to a varchar column of the DB Table.

It gave me an error that said 'Invalid column 'test' in field name'   (where test was the value of the variable).

When I used single quotes around the variable reference, then it inserted the word 'test' into the DB just fine.

So, I think you must have 2 problems. One is that you need single quotes around '{Data.Session.pv7}'

The second is a truncation error - possibly due to your DB column sizes

[dsemmler]

It works...    ..  Thanks Janine, appreciate the help.

Darren