Variables are not getting passed from db plugin to scripts

Hi, I am trying to use arraylist return by DB Plugin to write to excel, seems following scripts are not seeing the return array by db plugin. below is the sample script.

Config line 45: script block
groovy.lang.MissingPropertyException: No such property: ids for class: Script29
org.webharvest.exception.ScriptException:
Config line 45: script block
groovy.lang.MissingPropertyException: No such property: ids for class: Script29

<?xml version="1.0" encoding="UTF-8"?>
<config
	xmlns="http://web-harvest.sourceforge.net/schema/1.0/config"
	scriptlang="groovy">
	<script-var name="nList">
    </script-var>	
	<var-def name="certIds">
	<database connection="jdbc:sqlserver://NYLICdfdfd:1433;databaseName=sfdfApp;user=qdfdser;password=dfder$"
		        jdbcclass="com.microsoft.sqlserver.jdbc.SQLServerDriver">	
		        <template>	        
				 Select A.CertID FROM [CertificationApp].[dbo].[CertificationDetail] A
				  inner join [CertificationApp].[dbo].[Exception] B
				  on A.CertID = B.CertID
				  where queueid = 3
				  and A.WithholdingYTDAmount is NULL
				  and A.CountryOfTaxResidency != 'USA'
				  and A.AssignedTo is NULL
				  and B.ExceptionJSON like '%Member not Declined%'
				</template>
			  </database>
	</var-def> 	
	
	<script><![CDATA[
	 List ids = new ArrayList();
	 def lst = certIds.toList();	
	 def nlist = lst.size();
     // println(lst.size)
       println("this is first one --" + lst.get(0));
      ]]></script>	
      
      
			 <loop item="qID">
			  <list> <var name="certIds"/> </list> 			  
			  	<body> 			  
					<script><![CDATA[
					   //List ids = new ArrayList();	
					    ids.add("${qID.get("CertID")}");
						println	"the id is -- ${qID.get("CertID")}";			
					 ]]></script>			 	
 				</body>
	      </loop>	
	      
	<script><![CDATA[	
	       println(ids.size)
	       println("this is first one --" + ids.get(0));
      ]]></script>	 
	
</config>

Use the following to define your variable (without List):

ids = new ArrayList()

================================================================
Explanation:

You have two ways to define a variable in a tag

  1. Define it using its type. it will define the variable locally only (ie: within its scope)

List ids = new ArrayList()

  1. Define it without specifying its type. This will define the variable globally and it will be accessible in other script scope.

ids = new ArrayList()

Also you could define it using

sys.defineVariable(“myVariable”,myValue)

and get it using

myValue= sys.getVar(“myVariable”)

Be careful though that you will get a WebHarvestObject that you will have to unwrap. It is not recommanded to use this in your case.

1 Like

@alasbleis, thanks for the reply that helped. I encountered one more issue when I am trying to write the list variable to excel column with setCells(filePath, ‘A2’, values, true) as below it gives the exception

			List values = ids //this is arraylist returned from db column.
		//	 List values = ['xan', 'gan', 'fan','aan', 'ban']
		     def filePath = 'C://Users//mpuri//DNA.xls'					
				openExcel(filePath)
				switchSheet(filePath, 'CertApp')
				//setRange(filePath, 'A2', values)	
				setCells(filePath, 'A2', values, true)				
			    saveExcel(filePath)  
			    closeExcel(filePath)

Exception:

java.lang.IllegalArgumentException: Argument is of an illegal type: org.codehaus.groovy.runtime.GStringImpl
org.webharvest.exception.PluginException: org.webharvest.exception.ScriptException:
Config line 44: script block
java.lang.IllegalArgumentException: Argument is of an illegal type: org.codehaus.groovy.runtime.GStringImpl
at com.freedomoss.crowdcontrol.webharvest.plugin.selenium.RobotPlugin.executePlugin(RobotPlugin.java:185)

I have to do very primitive way as below…
int j = 2

			 for (int i=0; i<ids.size(); i++) {
       			System.out.println("---" + ids.get(i)); 
       			
       			def filePath = 'C://Users//mpuri//DNA.xls'					
				openExcel(filePath)
				switchSheet(filePath, 'CertApp')
				//setRange(filePath, 'A2', values)	
				//setCells(filePath, 'A2', values, true)	
				setCell(filePath, 'A'+ j, ids.get(i))			
			    saveExcel(filePath)  
			    closeExcel(filePath)
       			
       			j= j+ 1
       			
              }

Hi,

You should rely use Automation Cloud recorder as much as you can before moving to the code.
Using its high level “set range” function, it generated this code for me :

setRange(filepath, “A2”, “B5”, myTableVar.getRows().stream().map({ it.asList() }).collect(java.util.stream.Collectors.toList()))

@alasbleis, alright thanks for looking into this.

Best–

Manoj