03-22-2009 08:04 PM - edited 03-14-2019 03:50 AM
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
03-23-2009 12:48 AM
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
03-23-2009 04:25 PM
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!
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: