Page 1 of 1
SQL Query is not returning results
Posted: Wed Sep 23, 2015 10:19 am
by abarry81
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
Re: SQL Query is not returning results
Posted: Thu Sep 24, 2015 10:53 am
by Support_Tim
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 '%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>
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.
Re: SQL Query is not returning results
Posted: Thu Sep 24, 2015 2:41 pm
by abarry81
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 '%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 all9/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.
Re: SQL Query is not returning results
Posted: Thu Sep 24, 2015 2:55 pm
by Support_Tim
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]}
Re: SQL Query is not returning results
Posted: Mon Sep 28, 2015 8:41 am
by abarry81
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, '\..*$', '')}" 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 all9/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
Re: SQL Query is not returning results
Posted: Mon Sep 28, 2015 2:45 pm
by abarry81
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.
Re: SQL Query is not returning results
Posted: Mon Sep 28, 2015 3:14 pm
by Support_Rick
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>