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
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
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
- Posts: 35
- Joined: Mon Dec 01, 2014 10:35 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.
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
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.<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 '%name%'</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>
Hello Tim,
I took your project and modified it to connect to my db.
This is what I changed the project to:
Here is the job log:
This is not returning the data from the columns in the db like it should be.
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 '%COMPANY%'</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.
- Posts: 35
- Joined: Mon Dec 01, 2014 10:35 am
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]}
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]}
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:
-------------------------------------------------
Error log:
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, '\..*$', '')}" 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 '%${name_new}%' </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
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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, '\..*$', '')}" 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 '%${name_new}%' </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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
7 posts
Page 1 of 1