Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: SQL Query action not trapping SQL errors inside transaction
Topic Summary:
Created On: 12/19/2011 09:42 AM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
 12/19/2011 09:42 AM
User is offline View Users Profile Print this message

Author Icon
MatthewHamm
Student (5-19 posts)

Posts: 12
Joined: 10/26/2007

We use AutoMate to open text files which contain multiple SQL statements and loop through the statements one at a time and submit each statement to the database one at a time.  Currently each statement is auto-committed to the database.  We have a bunch of AutoMate logic built around the SQL Query action to trap errors via the GetLastError() and GetLastErrorText() built-in functions, and handle them accordingly as far as logging the errors to text files, sending email notifications, then stop the task.

We now are trying to configure it so that all SQL statements run in a single SQL session that persists for the length of the task.  If an error occurs, it should trap the error as before and stop the task, then rollback the transaction.  To do this, I opened a SQL Session at the beginning of the task and then use this session in the SQL Query task.  The first SQL statement that is submitted is a 'BEGIN' statement to open a database transaction, followed by delete, insert, and update statements, then finally a 'COMMIT' statement.

This seems to work great except in the situation where an error is encountered by one of the SQL statements.  If there is no error, everything works fine and the transaction is committed successfully.  If there is an error in one of the SQL statements, the SQL Query task does not recognize that an error has occured and continues on running the task as if nothing bad happened.  If I remove the BEGIN statement so that the statements aren't executing inside a database transaction, AutoMate traps the SQL error with no problem. 

I've examined the database logs and the error message generated by the statement that failed looks exactly the same whether it's inside a transaction or not.  Also, it's interesting to note that as AutoMate continues to submit SQL statements after the one that failed, they are failing on the database because it recognizes that there is an aborted transaction, but AutoMate does not trap these errors either.  Below is a snippet of the Netezza log where this occurred:

2011-12-18 20:36:11.902254 EST [31494]  DEBUG:  QUERY: BEGIN
2011-12-18 20:36:21.236981 EST [31494]  DEBUG:  QUERY: DELETE FROM MH_TEST1
2011-12-18 20:36:28.576136 EST [31494]  DEBUG:  QUERY: INSERT INTO MH_TEST1 VALUES (1,'A')
2011-12-18 20:36:34.024064 EST [31494]  DEBUG:  QUERY: INSERT INTO MH_TEST1 VALUES (2,'B')
2011-12-18 20:36:42.829461 EST [31494]  DEBUG:  QUERY: INSERT INTO MH_TEST1 VALUES ('e',3,'C')
2011-12-18 20:36:42.829790 EST [31494]  ERROR:  INSERT has more expressions than target columns
2011-12-18 20:36:42.836017 EST [31494]  DEBUG:  QUERY: INSERT INTO MH_TEST1 VALUES ('e',3,'C')
2011-12-18 20:36:42.836086 EST [31494]  NOTICE:  current transaction is aborted, queries ignored until end of transaction block
2011-12-18 20:36:51.990893 EST [31494]  DEBUG:  QUERY: INSERT INTO MH_TEST1 VALUES (4,'D')
2011-12-18 20:36:51.990969 EST [31494]  NOTICE:  current transaction is aborted, queries ignored until end of transaction block
2011-12-18 20:36:57.437959 EST [31494]  DEBUG:  QUERY: COMMIT
2011-12-18 20:36:57.438073 EST [31494]  NOTICE:  transaction is aborted, COMMIT ignored; now accepting queries
2011-12-18 20:37:03.457524 EST [31494]  DEBUG:  disconnect: host=10.191.7.88 user=QSADMIN database=QS_DW_PROD

Any help or suggestions would be most welcome.  Task used is attached.  Is there a chance this is fixed in v9?  If we could get this to work it will save us weeks of time to come up with a new solution that does not involve AutoMate.  Thanks a bunch in advance!



 Category Survey



 12/21/2011 04:50 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Hi Matthew,

We are currently testing the On Error functionality to determine if there is indeed a problem. If you are able to provide a smaller sampling the reproduces the issue and provide that as well, that would help us tremendously. We will keep you updated.






 Category Survey


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

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 12/22/2011 09:22 AM
User is offline View Users Profile Print this message

Author Icon
MatthewHamm
Student (5-19 posts)

Posts: 12
Joined: 10/26/2007

The attached zip file contains a slimmed-down version of the AutoMate task for which I was able to reproduce the condition.  Also included is the ETL file that contains the SQL statements being run, which should be modified to try different test cases that do or do not include the BEGIN/END, along with forcing one of the SQL statements to fail, such as one of the insert statements by including an extra value in the value list.  In that case, AutoMate will trap the error when there is no BEGIN statement to open a transaction, but will not trap the error when there is a BEGIN statement.

Please note that I have not tried this on any other database platform except Netezza, but the concept should apply to other platforms and I'd be interested to know nonetheless if it occurs on other platforms.

Thanks!



 Category Survey



 06/05/2013 11:07 AM
User is offline View Users Profile Print this message

Author Icon
AndyTilley
Student (5-19 posts)

Posts: 6
Joined: 06/05/2013

Did this get resolved? I have exactly the same issue as Matt with Automate 8.0.9.0



 Category Survey
 06/06/2013 09:54 AM
User is offline View Users Profile Print this message

Author Icon
Liz Casale
AUTOMATE EMPLOYEE

Posts: 940
Joined: 05/23/2011

Hi Andy,

When we test the above task, there is a Begin statement and the error code is trapped (value of 1). Are you also testing on a Netezza platform or a different database platform?



 Category Survey


-------------------------
Liz Casale
Technical Support Representative
(213) 738-6966
Network Automation, Inc.

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

AM5 5.5.5.1; AM6 6.2.8.0; AM7 7.1.3.0; AM8 8.0.9.0; AM9 9.0.3.4
BPA7 7.1.3.0; BPA8 8.0.9.0; BPA9 9.0.3.4
 06/06/2013 01:01 PM
User is offline View Users Profile Print this message

Author Icon
AndyTilley
Student (5-19 posts)

Posts: 6
Joined: 06/05/2013

 

This is Netezza also.

I spoke to Matt and the issue was never resolved - he didn't hear back.

I'm also establishing a database connection with a Session, issuing a "BEGIN TRANSACTION", then issuing DML. If I deliberately execute a SQL statement that is erroneous, then call GetLastError(), it returns "0 - the step was OK".

I have a really simple table and have attached an AML to demonstrate the problem.

CREATE TABLE ZTEST (I INT);

The log (log.txt) contents is:

0 - The step was okay.  

0 - The step was okay.  

0 - The step was okay.  

0 - The step was okay.  

Step 3 actually failed (too many members). But GetLastError() didn't report it.

This is Automate 8.0.9.0 - did you test on that version?

Thx



 Category Survey


ZTest_Task.aml
ZTest_Task.aml  (1 KB)

 06/07/2013 12:41 PM
User is offline View Users Profile Print this message

Author Icon
Liz Casale
AUTOMATE EMPLOYEE

Posts: 940
Joined: 05/23/2011

Hi Andy,

Thank you for your reply. Would it be at all possible for you to install AutoMate v9.0.4.5 along side your v8 install and tell us if the result is the same? We do not have access to a Netezza database.

AutoMate 9

In the mean time, we will continue looking into this internally as well.

 Category Survey


-------------------------
Liz Casale
Technical Support Representative
(213) 738-6966
Network Automation, Inc.

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

AM5 5.5.5.1; AM6 6.2.8.0; AM7 7.1.3.0; AM8 8.0.9.0; AM9 9.0.3.4
BPA7 7.1.3.0; BPA8 8.0.9.0; BPA9 9.0.3.4
 06/07/2013 01:34 PM
User is offline View Users Profile Print this message

Author Icon
AndyTilley
Student (5-19 posts)

Posts: 6
Joined: 06/05/2013

Hi Liz,

Thanks for your reply. I just downloaded and installed 9.0.4, imported my test task and the behavior is the same. That third SQL statement "appears" to have worked, but it actually failed.

Andy



 Category Survey
 06/10/2013 08:48 AM
User is offline View Users Profile Print this message

Author Icon
Liz Casale
AUTOMATE EMPLOYEE

Posts: 940
Joined: 05/23/2011

Hi Andy,

Thank you for your post. Did you test with the same sample task provided early in this forum thread?

 Category Survey


-------------------------
Liz Casale
Technical Support Representative
(213) 738-6966
Network Automation, Inc.

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

AM5 5.5.5.1; AM6 6.2.8.0; AM7 7.1.3.0; AM8 8.0.9.0; AM9 9.0.3.4
BPA7 7.1.3.0; BPA8 8.0.9.0; BPA9 9.0.3.4
 06/10/2013 08:51 AM
User is offline View Users Profile Print this message

Author Icon
AndyTilley
Student (5-19 posts)

Posts: 6
Joined: 06/05/2013

Yes - the same one. ZTest_Task.aml



 Category Survey
 06/13/2013 10:00 AM
User is offline View Users Profile Print this message

Author Icon
AndyTilley
Student (5-19 posts)

Posts: 6
Joined: 06/05/2013

Liz,

Any findings on your side at all? Do you need me to test anything else? I think the AML is quite self-explanatory though.

Thx

Andy 



 Category Survey
 06/14/2013 08:13 AM
User is offline View Users Profile Print this message

Author Icon
Liz Casale
AUTOMATE EMPLOYEE

Posts: 940
Joined: 05/23/2011

Hi Andy,

We are still looking into this. We do not have access to a Netezza database currently. We will update the post with more information as soon as possible.

 Category Survey


-------------------------
Liz Casale
Technical Support Representative
(213) 738-6966
Network Automation, Inc.

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

AM5 5.5.5.1; AM6 6.2.8.0; AM7 7.1.3.0; AM8 8.0.9.0; AM9 9.0.3.4
BPA7 7.1.3.0; BPA8 8.0.9.0; BPA9 9.0.3.4
 07/03/2013 08:59 AM
User is offline View Users Profile Print this message

Author Icon
AndyTilley
Student (5-19 posts)

Posts: 6
Joined: 06/05/2013

Hi - any update at all? 

I'm getting to the point that I'll need to turn to an alternative solution if this doesn't work.

Thanks
Andy

 



 Category Survey
 07/03/2013 10:03 AM
User is offline View Users Profile Print this message

Author Icon
Ricardo Castaneda
HelpSystems

Posts: 3424
Joined: 01/11/2008

Hi Andy,

Would it be possible to setup a GoToAssist session where we can take a closer look at this issue? If this is possible please send an email over to support@networkautomation.com with a list of your availability.

Note: Please reference this forum thread in your email.


 Category Survey
AutoMate 8 version:
Windows version:


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

Ricardo Castaneda | Principal Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1294 guests browsing this forum, which makes a total of 1294 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog