Hi there, I have an issue when automating specific queries in Toad Automation. The test automation seems to work fine for basic Select statement queries, but I noticed if you add a Group By statement at the end it errors out. Also I tried to run a simple “Delete from tablexyz” statement and it doesn’t like that either. These queries work fine when executed through the basic query section, why are they erroring out now?
If you want to execute a series of sql use the Script execution activity.
Also, what connection are you using? If it is SQL Server, I did some work recently to handle script execution for that database platform. It is in the Beta but not TDA 2.7.
If these two scenarios do not cover your situation or you want to know more about your specific errors please run your automation script with verbose logging and post the log file and I will take a look. (Turn on verbose logging in the script settings activity at the top of the designer.)
Debbie
Hi Debbie, Thank you for your prompt response.
Yes it is an SQL database.
Have you seen issues like this before where a query will execute successfully in the query developer section but then it fails when ran through the automation?
I have attached the Verbose log and a screenshot of the setup, it is very basic, the only code in the script is “Delete from Table123” which just does a mass delete of all the data in that table.
Error.log (2.87 KB)
Hi Debbie, Thank you for your prompt response.
Yes it is an SQL database.
Have you seen issues like this before where a query will execute successfully in the query developer section but then it fails when ran through the automation?
I have attached the Verbose log and a screenshot of the setup, it is very basic, the only code in the script is “Delete from Table123” which just does a mass delete of all the data in that table.
What statement delimiter are you using? Semi colon at the end of every statement is the safest. My first hunch is there is a parser error. Try separating the two statements into two sql scripts and execute in two activities to confirm the issue.
Debbie
I am using a semi-colon at the end of the statement. See attached here is the code I am using, there isn't anything that I can split out any further. Any ideas?
I need to confirm. You are saying that the delete query executes fine in the editor? Or does it not work there, either?
We are not familiar with the ODBC driver you are using and have certified with a different driver for iSeries.
Debbie
Yes this query will work in the editor, but once automated it errors out. The only queries I have been able to get to work in the automation are select statements (Select * From Table123)
But if you add on a “group by” or “order by” statement to the end it will fail. In addition Delete and Insert statements don’t seem to work either.
One thing that may be different is the encoding of the file. The editor pays attention to encoding where Automation does not. Also, your log file only has basic logging turned on. If you turn on Verbose you will see what the statement is that it is trying to execute.
Let’s try two things.
-
Double click on the Script Settings activity. (Grey box at the top of the automation designer) Scroll to the logging are and change the logging level from Basic to Verbose.
-
Take out using a file for the delete statement. In the drop down of the SQL attribute(execute script activity) you can choose to enter the SQL directly instead of using a file. Choose this and type in your delete statement.
Rerun and if still errors post the log file.
Debbie
Still no luck when I entered in the SQL directly. Here is the Verbose logging.
– 6/7/2011 8:36:54 AM: TEST2.log: Setting up environment
– 6/7/2011 8:36:54 AM: TEST2.log: Script run by Toad for Data Analysts 2.7.0.348
– 6/7/2011 8:36:54 AM: TEST2.log: Build started
– 6/7/2011 8:36:54 AM: TEST2.log: Compiling Script
– 6/7/2011 8:36:57 AM: TEST2.log: Warning: No Exception Handler. To send an Email when there is an error, change the value of Email on Error to true and enter email values.
– 6/7/2011 8:36:57 AM: TEST2.log: Build Completed
– 6/7/2011 8:36:57 AM: TEST2.log: Begin execution script activties
– 6/7/2011 8:36:57 AM: TEST2.log: Connecting to prodsys (kg0ohd), fdwfil
– 6/7/2011 8:36:57 AM: TEST2.log: Connection To: prodsys (kg0ohd), fdwfil
– 6/7/2011 8:36:57 AM: TEST2.log: Connection successful
– 6/7/2011 8:36:57 AM: TEST2.log: Connection logon: kg0ohd
– 6/7/2011 8:36:57 AM: TEST2.log: Last schema: FDWFIL
– 6/7/2011 8:36:57 AM: TEST2.log: Default schema: fdwfil
– 6/7/2011 8:36:58 AM: TEST2.log: Auto Commit: True
– 6/7/2011 8:36:58 AM: TEST2.log: Connection successful.
– 6/7/2011 8:36:58 AM: TEST2.log: Executing script: C:\Documents and Settings\kg0ohd\Desktop\DELETE FROM BENBOND.NEX_OPEN
– 6/7/2011 8:36:59 AM: TEST2.log: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token C was not valid. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN.
– 6/7/2011 8:36:59 AM: TEST2.log: at Quest.Toad.NativeOdbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
– at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
– at Quest.Toad.Db.ToadDataAdapter.InternalFill(List`1 ret)
– at Quest.Toad.Db.ToadDataAdapter.Fill(CommandResult commandResult)
– at Quest.Toad.Db.ScriptEngine.executeQuery(IDbConnection currentConnection, String sql, Object[] param)
– 6/7/2011 8:36:59 AM: TEST2.log: Execution SQL: C:\Documents and Settings\kg0ohd\Desktop\DELETE FROM BENBOND.NEX_OPEN
– 6/7/2011 8:36:59 AM: TEST2.log: Executing rollback
– 6/7/2011 8:36:59 AM: TEST2.log: Terminating execution due to error. To continue, change On Error value to continue after error.
– 6/7/2011 8:36:59 AM: TEST2.log: at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
– at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
– at Quest.Toad.Util.Global.Invoke(Delegate handler, Object[] args)
– at Quest.Toad.Db.ScriptEngine.PostProcessOne(CommandResult result, Object data)
– at Quest.Toad.Db.ScriptEngine.InternalCommandExecuted(CommandResult result, IDbConnection localConnection)
– at Quest.Toad.Db.ScriptEngine.ExecuteScript(Boolean oneStatement, String sql, Object[] param)
– at Quest.Toad.Db.ScriptEngine.ExecuteScript(String sql, Object[] param)
– at Quest.Toad.Workflow.Activities.Database.SelectDataActivity.Execute(ActivityExecutionContext executionContext)
– 6/7/2011 8:36:59 AM: TEST2.log: Failed
From log file:
Executing script: C:\Documents and Settings\kg0ohd\Desktop\DELETE FROM BENBOND.NEX_OPEN
The activity is still trying to read from the file. Delete the activity and add a new one with SQL entered.
Debbie
The strange thing is when I enter in the script to run it says it will run only that, but once I hit “test automation” it throws in the C:\Documents and Settings… as you found in the log. I don’t actually have a file on the desktop, it seems to just be defaulting to that location, maybe because the software is located there.
Okay. I will get someone here to check this scenario on iSeries with the ODBC driver we use.
Debbie
P.S. If you can send install media of the driver you are using that would be very helpful or link to where I can download.
Debbie, the driver details we are using are as follows, is this all you need or do you actually need the setup? That may be a little more difficult to get across.
AS400 Client Access Express for Windows
Version 5, release 1
2004
I can’t find that driver media. I have tested with the ODBC driver we bundle and that works fine.
Please change to using that driver. When you go to the ODBC manager it is listed as IBM DB2 ODBC Driver - TACOM27. Give that a try.
Debbie
Unfortunately I don’t have the ability to change/create a new connection like this. I do see that driver there. I have highlighted the one that we currently use.
Unfortunately I don’t have the ability to change/create a new connection like this. I do see that driver there. I have highlighted the one that we currently use.
The driver you need to use is 7 rows down from the one you have highlighted or the second from the bottom. IBM DB2 ODBC driver TACOM27
I don’t see how you can be restricted from defining a new ODBC connection or TDA connection. Give it a try or can your IT department or someone in your division walk you through it?
If you go into edit the connection you already have you should have the properties you need to define the new conneciton.
Debbie