SQL Query is not returning results

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
7 posts Page 1 of 1

abarry81

Posts: 7
Joined: Mon Feb 16, 2015 9:41 pm

Post by abarry81 » Wed Sep 23, 2015 10:19 am
I have a project that is not returning results from SQL Query.

Project queries the database, then based on the results I set 2 variables. The problem is that I keep getting an error saying: [9002 - setVariable] No data was found in rowset 'location'.

When I query in oracle sql developer I get the following results:

\\ipaddress\production\305\ftp\SII_HR_DW\ UNC

In the project, this is the query I am using:

SELECT DESTINATION,METHOD FROM EFT.FUSION_FILES_MOVE WHERE FILENAME LIKE '%name%'

I then set 2 variables:

Variable Name: location_output
Value: ${location[1]} = should return: \\ipaddress\production\305\ftp\SII_HR_DW\

Variable Name: method
Value: ${location[2]} = should return: UNC

How can I resolve this, and also how is it possible to see what goanywhere has in the rowset?

Thanks,
Adam

Support_Tim

Posts: 35
Joined: Mon Dec 01, 2014 10:35 am

Post by Support_Tim » Thu Sep 24, 2015 10:53 am
Hello abarry,

An easy way to see what is in a rowset variable, is to use the Print task in a For-Each loop. This loop is needed to separate the rows to print in the log. Below is an example of this, where rowSet is the rowSet variable.

If you would rather use the Set Variable task to capture the first row only, use the Set Variable task version 1.0. It sets the pointer to the beginning of the rowset where Set Variable version 2.0 does not. You can access this task version in Director by checking the box at the top of the task folders: “Include Prior Versions”. This will enable the previous version and you can select it in the Miscellaneous folder. In GoAnywhere MFT, there is a subfolder in Miscellaneous that contains these versions.

Code: Select all
<project name="Print Rowset Content" mainModule="Main" version="2.0">
                <description>Print Rowset Content</description>

                <module name="Main">

                                <sql resourceId="MyDataBase" version="1.0">
                                                <query outputVariable="rowSet">
                                                                <statement>SELECT DESTINATION,METHOD FROM EFT.FUSION_FILES_MOVE WHERE FILENAME LIKE &apos;%name%&apos;</statement>
                                                </query>
                                </sql>

                                <forEachLoop itemsVariable="rowSet" currentItemVariable="curItem">

                                                <print label="Print Current Item" version="1.0">
                                                                <![CDATA[** This is the current item value:  ${curItem}]]>
                                                </print>

                                </forEachLoop>
                </module>

</project>
If you still need assistance, it would be helpful to see your project. Please post the project XML and the log with debug detail. For debug info, click on the Main module on the upper left, then click on the Control tab on the right and select “Debug” in the Log Detail box.

abarry81

Posts: 7
Joined: Mon Feb 16, 2015 9:41 pm

Post by abarry81 » Thu Sep 24, 2015 2:41 pm
Hello Tim,

I took your project and modified it to connect to my db.

This is what I changed the project to:
Code: Select all
<project name="Print Rowset Content" mainModule="Main" version="2.0">
	<description>Print Rowset Content</description>

	<module name="Main">

		<sql resourceId="EFT_APPS" version="1.0">
			<query outputVariable="rowSet">
				<statement>SELECT DESTINATION,METHOD FROM EFT.FUSION_FILES_MOVE WHERE FILENAME LIKE &apos;%COMPANY%&apos;</statement>
			</query>
		</sql>

		<forEachLoop itemsVariable="${rowSet}" currentItemVariable="curItem">

			<print label="Print Current Item" version="1.0">
				<![CDATA[** This is the current item value: ${curItem}]]>
			</print>

		</forEachLoop>
	</module>

</project>


Here is the job log:
Code: Select all
9/24/15 3:40:29 PM            INFO      Start Date and Time: 9/24/15 3:40:29 PM
9/24/15 3:40:29 PM            INFO      Job Number: 1000000028367 
9/24/15 3:40:29 PM            INFO      Project Name: /USERS/Adam Barry/Print Rowset Content
9/24/15 3:40:29 PM            INFO      Submitted By: z003bmhp99
9/24/15 3:40:29 PM            INFO      GoAnywhere Director 4.7.0 running on Windows Server 2008 R2 6.1 (amd64)
9/24/15 3:40:29 PM            INFO      Executing project 'Print Rowset Content' 
9/24/15 3:40:29 PM            INFO      Project location: C:\Program Files\Linoma Software\GoAnywhere\userdata\projects\USERS\Adam Barry\Print Rowset Content.xml
9/24/15 3:40:29 PM            INFO      Executing module 'Main'
9/24/15 3:40:29 PM            INFO      Executing task 'sql 1.0'
9/24/15 3:40:29 PM            INFO      Executing sub-task 'query'
9/24/15 3:40:29 PM            INFO      Executing statement - SELECT DESTINATION,METHOD FROM EFT.FUSION_FILES_MOVE WHERE FILENAME LIKE '%COMPANY%'
9/24/15 3:40:29 PM            INFO      Query completed successfully and the rowset 'rowSet' was created
9/24/15 3:40:29 PM            INFO      Finished sub-task 'query'
9/24/15 3:40:29 PM            INFO      Finished task 'sql 1.0'
9/24/15 3:40:29 PM            INFO      Entering loop 'forEachLoop'
9/24/15 3:40:29 PM            INFO      Executing task 'print 1.0 (Print Current Item)'
9/24/15 3:40:29 PM            INFO      ** This is the current item value: com.linoma.dpa.rowset.DatabaseRowSet@11cc6c74
9/24/15 3:40:29 PM            INFO      Finished task 'print 1.0 (Print Current Item)'
9/24/15 3:40:29 PM            INFO      Exiting loop 'forEachLoop'
9/24/15 3:40:29 PM            INFO      Finished module 'Main'
9/24/15 3:40:29 PM            INFO      Finished project 'Print Rowset Content'
9/24/15 3:40:29 PM            INFO      End Date and Time: 9/24/15 3:40:29 PM

This is not returning the data from the columns in the db like it should be.

Support_Tim

Posts: 35
Joined: Mon Dec 01, 2014 10:35 am

Post by Support_Tim » Thu Sep 24, 2015 2:55 pm
Abarry81,

When you access the current item variable of a rowset (version 2.0) you have to indicate the column index, even if there only one column. I meant to add it in the code but let it slip. Make your print statement look like this and it should work.

** This is the current item value: ${curItem[1]}

abarry81

Posts: 7
Joined: Mon Feb 16, 2015 9:41 pm

Post by abarry81 » Mon Sep 28, 2015 8:41 am
Tim thanks for your help so much, but when I try your solution it only works for the first file found in the filelist and does not continue with the 2nd,3rd..... files.

I am trying a different way, and I am getting an error again: Exhausted Resultset. I searched the forums and I go not get any results on that error.


Here is the copy of the project I am using:
Code: Select all
<project name="FUSION_MOVE_TEST_TROUBLESHOOTING" mainModule="Main" version="2.0" logLevel="verbose">

	<module name="Main">

		<createWorkspace version="1.0" disabled="false" />


		<sftp resourceId="sftp.cloud.oracle.com-HTTP Proxyy" version="1.0">
			<list fileListVariable="files" numFilesFoundVariable="filecount">
				<fileset dir="/upload/download/custom_reports/">
					<wildcardFilter>
						<include pattern="*.*" />
					</wildcardFilter>
				</fileset>
			</list>
		</sftp>

		<if condition="${filecount gt 0}">
			<forEachLoop itemsVariable="${files}" currentItemVariable="current_file">

				<setVariable name="name" value="${current_file:nameWithoutextension}" version="2.0" />


				<setVariable name="name_new" value="${Replace(name, &apos;\..*$&apos;, &apos;&apos;)}" version="2.0" disabled="false" />


				<sql resourceId="EFT_APPS" version="1.0">
					<query outputVariable="location" whenNoDataFound="continue">
						<statement>SELECT DESTINATION, METHOD FROM EFT.FUSION_FILES_MOVE WHERE FILENAME LIKE &apos;%${name_new}%&apos; </statement>
					</query>
				</sql>

				<if condition="${IsNotEmpty(location[1])}">

					<print version="1.0">
						<![CDATA[Destination : ${location[1]}
Method: ${location[2]}]]>
					</print>

				</if>
				<if condition="${IsEmpty(location[1])}">

					<print version="1.0">
						<![CDATA[Destination : ${location[1]}
Method: ${location[2]}]]>
					</print>

				</if>
			</forEachLoop>
		</if>

		<deleteWorkspace version="1.0" disabled="false" />

	</module>

</project>

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

Error log:
Code: Select all
9/28/15 9:39:34 AM            INFO      Start Date and Time: 9/28/15 9:39:34 AM
9/28/15 9:39:34 AM            INFO      Job Number: 1000000029824 
9/28/15 9:39:34 AM            INFO      Project Name: /USERS/Adam Barry/FUSION_MOVE_TEST_TROUBLESHOOTING
9/28/15 9:39:34 AM            INFO      Submitted By: z003bmhp99
9/28/15 9:39:34 AM            INFO      GoAnywhere Director 4.7.0 running on Windows Server 2008 R2 6.1 (amd64)
9/28/15 9:39:34 AM            INFO      Executing project 'FUSION_MOVE_TEST_TROUBLESHOOTING' 
9/28/15 9:39:34 AM            INFO      Project location: C:\Program Files\Linoma Software\GoAnywhere\userdata\projects\USERS\Adam Barry\FUSION_MOVE_TEST_TROUBLESHOOTING.xml
9/28/15 9:39:34 AM            INFO      Executing module 'Main'
9/28/15 9:39:34 AM            INFO      Executing task 'createWorkspace 1.0'
9/28/15 9:39:34 AM            INFO      Workspace directory for this job is set to 'C:\Program Files\Linoma Software\GoAnywhere\userdata\workspace\1000000029824'.
9/28/15 9:39:34 AM            INFO      Finished task 'createWorkspace 1.0'
9/28/15 9:39:34 AM            INFO      Executing task 'sftp 1.0'
9/28/15 9:39:34 AM            INFO      Connecting to 'xxxxxxxxxxxxxxxxxxxxxx' at port '2021' as user 'xxxxxxxx' 
9/28/15 9:39:35 AM            INFO      Executing sub-task 'list'
9/28/15 9:39:35 AM            INFO      Remote FileList variable 'files' was created containing 2 file(s)
9/28/15 9:39:35 AM            INFO      Finished sub-task 'list'
9/28/15 9:39:35 AM            INFO      Closed the FTP connection
9/28/15 9:39:35 AM            INFO      Finished task 'sftp 1.0'
9/28/15 9:39:35 AM            INFO      Entering if block as the condition "${filecount gt 0}" was met
9/28/15 9:39:35 AM            INFO      Entering loop 'forEachLoop'
9/28/15 9:39:35 AM            INFO      Executing task 'setVariable 2.0'
9/28/15 9:39:35 AM            INFO      Variable 'name' was created and set to 'COMPANY'.
9/28/15 9:39:35 AM            INFO      Finished task 'setVariable 2.0'
9/28/15 9:39:35 AM            INFO      Executing task 'setVariable 2.0'
9/28/15 9:39:35 AM            INFO      Variable 'name_new' was created and set to 'COMPANY'.
9/28/15 9:39:35 AM            INFO      Finished task 'setVariable 2.0'
9/28/15 9:39:35 AM            INFO      Executing task 'sql 1.0'
9/28/15 9:39:35 AM            INFO      Executing sub-task 'query'
9/28/15 9:39:35 AM            INFO      Executing statement - SELECT DESTINATION, METHOD FROM EFT.FUSION_FILES_MOVE WHERE FILENAME LIKE '%COMPANY%' 
9/28/15 9:39:36 AM            INFO      Query completed successfully and the rowset 'location' was created
9/28/15 9:39:36 AM            INFO      Finished sub-task 'query'
9/28/15 9:39:36 AM            INFO      Finished task 'sql 1.0'
9/28/15 9:39:36 AM            INFO      Finished project 'FUSION_MOVE_TEST_TROUBLESHOOTING'
9/28/15 9:39:36 AM            ERROR     [8099 - sql] An unexpected error occurred. 
                                        Exhausted Resultset
                                         
                                        com.linoma.dpa.runtime.JobFailedException: [8099 - sql] An unexpected error occurred. 
                                        Exhausted Resultset
                                         
                                        	at com.linoma.dpa.runtime.Job.run(Unknown Source)
                                        	at com.linoma.dpa.runtime.Runtime.executeProject(Unknown Source)
                                        	at com.linoma.dpa.j2ee.forms.ConfigureProjectComponentForm.execute(Unknown Source)
                                        	at com.linoma.dpa.j2ee.forms.ConfigureProjectComponentForm.executeProject(Unknown Source)
                                        	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                                        	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
                                        	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
                                        	at java.lang.reflect.Method.invoke(Unknown Source)
                                        	at org.apache.el.parser.AstValue.invoke(AstValue.java:191)
                                        	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
                                        	at org.apache.jasper.el.JspMethodExpression.invoke(JspMethodExpression.java:68)
                                        	at javax.faces.component._MethodExpressionToMethodBinding.invoke(_MethodExpressionToMethodBinding.java:78)
                                        	at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:57)
                                        	at javax.faces.component.UICommand.broadcast(UICommand.java:143)
                                        	at org.ajax4jsf.component.AjaxActionComponent.broadcast(AjaxActionComponent.java:55)
                                        	at org.ajax4jsf.component.AjaxViewRoot.processEvents(AjaxViewRoot.java:329)
                                        	at org.ajax4jsf.component.AjaxViewRoot.broadcastEventsForPhase(AjaxViewRoot.java:304)
                                        	at org.ajax4jsf.component.AjaxViewRoot.processPhase(AjaxViewRoot.java:261)
                                        	at org.ajax4jsf.component.AjaxViewRoot.processApplication(AjaxViewRoot.java:474)
                                        	at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:32)
                                        	at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:103)
                                        	at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:76)
                                        	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:151)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at org.apache.myfaces.webapp.filter.ExtensionsFilter.doFilter(ExtensionsFilter.java:341)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:206)
                                        	at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
                                        	at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:388)
                                        	at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:515)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at com.linoma.dpa.j2ee.IECompatibilityModeFilter.doFilter(Unknown Source)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at com.linoma.dpa.security.SecurityFilter.doFilter(Unknown Source)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
                                        	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
                                        	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
                                        	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
                                        	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
                                        	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
                                        	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
                                        	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
                                        	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
                                        	at java.lang.Thread.run(Unknown Source)
                                        Caused by: com.linoma.commons.expressions.ExpressionException: Exhausted Resultset
                                        	at com.linoma.dpa.expression.tokens.ProjectBracketToken.evaluate(Unknown Source)
                                        	at com.linoma.commons.expressions.Expression.evaluate(Unknown Source)
                                        	at com.linoma.dpa.expressions.ProjectExpression.evaluate(Unknown Source)
                                        	at com.linoma.dpa.If.execute(Unknown Source)
                                        	at com.linoma.dpa.ForEachLoop.processCollection(Unknown Source)
                                        	at com.linoma.dpa.ForEachLoop.execute(Unknown Source)
                                        	at com.linoma.dpa.If.execute(Unknown Source)
                                        	at com.linoma.dpa.ModuleV2.execute(Unknown Source)
                                        	at com.linoma.dpa.Project.execute(Unknown Source)
                                        	at com.linoma.dpa.runtime.Job.executeProject(Unknown Source)
                                        	... 50 more
                                        Caused by: com.linoma.dpa.rowset.RowSetException: Exhausted Resultset
                                        	at com.linoma.dpa.rowset.DatabaseRowSet.getObject(Unknown Source)
                                        	... 60 more
                                        Caused by: java.sql.SQLException: Exhausted Resultset
                                        	at oracle.jdbc.driver.ScrollableResultSet.getOracleObject(ScrollableResultSet.java:623)
                                        	at oracle.jdbc.driver.ScrollableResultSet.getObject(ScrollableResultSet.java:1116)
                                        	at oracle.jdbc.driver.ScrollableResultSet.getObject(ScrollableResultSet.java:1072)
                                        	... 61 more
9/28/15 9:39:36 AM            INFO      End Date and Time: 9/28/15 9:39:36 AM

abarry81

Posts: 7
Joined: Mon Feb 16, 2015 9:41 pm

Post by abarry81 » Mon Sep 28, 2015 2:45 pm
Tim, the problem that we experience doing this is that it only returns information for one file in the filelist, when it goes to the next file in the list it does not work. It seems that it is stuck in the loop.

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Mon Sep 28, 2015 3:14 pm
Try something like this:
Code: Select all
<project name="FUSION_MOVE_TEST_TROUBLESHOOTING" mainModule="Main" version="2.0" logLevel="verbose">

	<module name="Main">

		<createWorkspace version="1.0" disabled="false" />


		<sftp resourceId="sftp.cloud.oracle.com-HTTP Proxyy" version="1.0">
			<list fileListVariable="files" numFilesFoundVariable="filecount">
				<fileset dir="/upload/download/custom_reports/">
					<wildcardFilter>
						<include pattern="*.*" />
					</wildcardFilter>
				</fileset>
			</list>
		</sftp>

		<if condition="${filecount gt 0}">
			<forEachLoop itemsVariable="${files}" currentItemVariable="current_file">

				<setVariable name="name" value="${current_file:nameWithoutextension}" version="2.0" />


				<setVariable name="name_new" value="${Replace(name, &apos;\..*$&apos;, &apos;&apos;)}" version="2.0" disabled="false" />


				<sql resourceId="EFT_APPS" version="1.0">
					<query outputVariable="location" whenNoDataFound="continue">
						<statement>SELECT DESTINATION, METHOD FROM EFT.FUSION_FILES_MOVE WHERE FILENAME LIKE &apos;%${name_new}%&apos; </statement>
					</query>
				</sql>

				[b][i]<forEachLoop itemsVariable="${location}" currentItemVariable="MyLoc">
					<if condition="${IsNotEmpty(MyLoc[1])}">

						<print version="1.0">
							<![CDATA[Destination : ${MyLoc[1]}
Method: ${MyLoc[2]}]]>
						</print>

					</if>
					<if condition="${IsEmpty(MyLoc[1])}">

						<print version="1.0">
							<![CDATA[Destination : ${MyLoc[1]}
Method: ${MyLoc[2]}]]>
						</print>

					</if>
				</forEachLoop>[/i][/b]
			</forEachLoop>
		</if>

		<deleteWorkspace version="1.0" disabled="false" />

	</module>

</project>
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
7 posts Page 1 of 1