cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1311
Views
0
Helpful
2
Replies

UCCx 7.0 SQL Insert Into Error

cary.chapman
Level 1
Level 1

I've written a script and need to execute a DB Write to insert some information into a table in the customer SQL database. Here is the statement format for the DB Write:

INSERT INTO [CustAcctInfo] (Tele, pamt1, CCNO, CCSEC, CCExpMM, CCExpYY, CCBillZip) VALUES ($str_telephoneNumber, $str_getPaymentAmt, $str_ccNumber, $str_ccSecCode, $str_ccMonth, $str_ccYear, $str_ccZipCode)

Pretty basic, all the values are string values in UCCx, VarChar in SQL. I get the following error in the MIVR logs in UCCx when I try to execute this step and the script drops to the SQL logic. The strange thing is that the data is put into the table, it is there, but the script never seems to know that it executed correctly. Any ideas/help is appreciated, I've battled this one for a week now... thanks!

MIVR Error (from log):

391026: Mar 02 10:28:48.046 EDT %MIVR-SS_DB-7-UNK: Exception: java.sql.SQLException: No row count was produced ExecuteUpdate Error Occurred

391027: Mar 02 10:28:48.046 EDT %MIVR-SS_DB-3-EXECUTE_SQL_UPDATE_EXCEPTION:Exception occured while executing SQL Update: SQL State=null,SQL Error code=null,Connection #=null

Here are the SQL and Script variable values:

SQL Variable Defined As Comment

--------------- ------------ ---------------------

Tele varchar(010) str_telephoneNumber

pamt1 varchar(010) str_getPaymentAmt

CCNO varchar(016) str_ccNumber

CCSEC varchar(003) str_ccSecCode

CCExpMM varchar(002) str_ccMonth

CCExpYY varchar(002) str_ccYear

CCBillZip varchar(005) str_ccZipCode

2 Replies 2

geoff
Level 10
Level 10

I don't know the interface you are working with that well (I don't recall using any INSERT or UPDATE statements when I coded to the DB steps - just SELECT) but is it possible you are not providing a variable to hold the number of rows affected?

In the Java SQL API, INSERT and UPDATE methods do that. For example:

String connectionUrl = "jdbc:sqlserver://host:1433;databaseName=Db;user=foo;password=pwd";

Connection con = null;

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

con = DriverManager.getConnection(connectionUrl);

Statement stmt = null;

String SQL = "INSERT INTO Table1 (Col1, Col2) VALUES ('123', '456')";

stmt = con.createStatement();

int rowsAffected = stmt.executeUpdate(SQL);

if (rowsAffected == 1) { /* all ok */}

Regards,

Geoff

cary.chapman
Level 1
Level 1

Seems the UCCx didn't like the fact that there was some data already in the table from testing with SQL. Once I executed a DELETE from [tablename] on the table in question and ran my script the INSERT INTO executed without any issues and is working great. Go figure... I couldn't see anything different in the table data, but it is working so life is back on track after burning a week on this one... sigh!

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: