Problem with SQL band in Reports

florin.tanasa · February 20, 2025, 8:40am

Hi,

I have problems with Reports where the band is SQL, when I run the report I have next in log:

2025-02-20T10:18:41.297+02:00  INFO 33804 --- [nio-8080-exec-7] i.j.r.libintegration.JmixReporting       : Started report [List of Deposit (2)] with parameters [
entities:[com.genebank.genedatabank.entity.Deposit-8a436c24-4c94-5d25-04b1-769430ef7749 [detached], com.genebank.genedatabank.entity.Deposit-d5d22dd1-2db0-0318-6c2a-d574a93e28b3 [detached]]]
2025-02-20T10:18:41.584+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.utils.ResourceUtils           : Couldn't get resource: docx4j.properties
2025-02-20T10:18:41.584+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.Docx4jProperties              : Couldn't find/read docx4j.properties; docx4j.properties not found via classloader.
2025-02-20T10:18:41.668+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.jaxb.Context                  : java.vendor=Ubuntu
2025-02-20T10:18:41.668+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.jaxb.Context                  : java.version=21.0.6
2025-02-20T10:18:41.668+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.jaxb.Context                  : java.vm.name=OpenJDK 64-Bit Server VM
2025-02-20T10:18:42.458+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.jaxb.Context                  : JAXB Reference Implementation is in use.
2025-02-20T10:18:42.510+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.XmlUtils                      : default SAXParserFactory property : null
 Please consider using Xerces.
2025-02-20T10:18:42.510+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.XmlUtils                      : actual SAXParserFactory: org.apache.xerces.jaxp.SAXParserFactoryImpl
2025-02-20T10:18:42.510+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.XmlUtils                      : default DocumentBuilderFactory property: null
 Please consider using Xerces.
2025-02-20T10:18:42.511+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.XmlUtils                      : actual DocumentBuilderFactory: org.apache.xerces.jaxp.DocumentBuilderFactoryImpl
2025-02-20T10:18:42.517+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.contenttype.ContentTypeManager     : Detected WordProcessingML package 
2025-02-20T10:18:42.521+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.contenttype.ContentTypeManager     : Detected WordProcessingML package 
2025-02-20T10:18:42.521+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.openpackaging.io3.Load3       : Instantiated package of type org.docx4j.openpackaging.packages.WordprocessingMLPackage
2025-02-20T10:18:42.523+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.utils.XPathFactoryUtil        : xpath implementation: org.apache.xpath.jaxp.XPathFactoryImpl
2025-02-20T10:18:42.525+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.contenttype.ContentTypeManager     : Using DocPropsCustomPart ...
2025-02-20T10:18:42.530+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.openpackaging.io3.Load3       : package read;  elapsed time: 887 ms
2025-02-20T10:18:42.537+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"start"). Expected element
2025-02-20T10:18:42.537+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : Column is 1034 at line number 2
2025-02-20T10:18:42.538+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : shouldContinue is set to false
2025-02-20T10:18:42.538+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.o.parts.JaxbXmlPartXPathAware        : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"start"). Expected elements are <{http://schemas.openxmlformats.org/wordprocessingml/2006/main}bottom>,<{http://schemas.openxmlformats.org/wordprocessingml/2006/main}left>,<{http://schemas.openxmlformats.org/wordprocessingml/2006/main}top>,<{http://schemas.openxmlformats.org/wordprocessingml/2006/main}right>
2025-02-20T10:18:42.538+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.parts.JaxbXmlPartXPathAware        : encountered unexpected content in /word/document.xml; pre-processing
2025-02-20T10:18:42.618+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.XmlUtils                      : Using org.docx4j.org.apache.xalan.transformer.TransformerImpl on source javax.xml.transform.dom.DOMSource
2025-02-20T10:18:42.666+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"start"). Expected element
2025-02-20T10:18:42.672+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : troublesome node: <w:start xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" w:type="dxa" w:w="55"/>
2025-02-20T10:18:42.672+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : in parent node: <w:tblCellMar xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:top w:type="dxa" w:w="55"/><w:start w:type="dxa" w:w="55"/><w:bottom w:type="dxa" w:w="55"/><w:end w:type="dxa" w:w="55"/></w:tblCellMar>
2025-02-20T10:18:42.672+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : #document/document/body/tbl/tblPr/tblCellMar/start
2025-02-20T10:18:42.672+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : continuing (with possible element/attribute loss)
2025-02-20T10:18:42.673+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"end"). Expected elements 
2025-02-20T10:18:42.673+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : troublesome node: <w:end xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" w:type="dxa" w:w="55"/>
2025-02-20T10:18:42.673+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : in parent node: <w:tblCellMar xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:top w:type="dxa" w:w="55"/><w:start w:type="dxa" w:w="55"/><w:bottom w:type="dxa" w:w="55"/><w:end w:type="dxa" w:w="55"/></w:tblCellMar>
2025-02-20T10:18:42.673+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : #document/document/body/tbl/tblPr/tblCellMar/end
2025-02-20T10:18:42.673+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : continuing (with possible element/attribute loss)
2025-02-20T10:18:42.674+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"start"). Expected element
2025-02-20T10:18:42.674+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : troublesome node: <w:start xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" w:color="000000" w:space="0" w:sz="4" w:val="single"/>
2025-02-20T10:18:42.674+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : in parent node: <w:tcBorders xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:top w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:start w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:bottom w:color="000000" w:space="0" w:sz="4" w:val="single"/></w:tcBorders>
2025-02-20T10:18:42.675+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : #document/document/body/tbl/tr/tc/tcPr/tcBorders/start
2025-02-20T10:18:42.675+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : continuing (with possible element/attribute loss)
2025-02-20T10:18:42.677+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"start"). Expected element
2025-02-20T10:18:42.677+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : troublesome node: <w:start xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" w:color="000000" w:space="0" w:sz="4" w:val="single"/>
2025-02-20T10:18:42.677+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : in parent node: <w:tcBorders xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:top w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:start w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:bottom w:color="000000" w:space="0" w:sz="4" w:val="single"/></w:tcBorders>
2025-02-20T10:18:42.677+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : #document/document/body/tbl/tr/tc/tcPr/tcBorders/start
2025-02-20T10:18:42.677+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : continuing (with possible element/attribute loss)
2025-02-20T10:18:42.678+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"start"). Expected element
2025-02-20T10:18:42.678+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : troublesome node: <w:start xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" w:color="000000" w:space="0" w:sz="4" w:val="single"/>
2025-02-20T10:18:42.678+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : in parent node: <w:tcBorders xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:top w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:start w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:bottom w:color="000000" w:space="0" w:sz="4" w:val="single"/></w:tcBorders>
2025-02-20T10:18:42.678+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : #document/document/body/tbl/tr/tc/tcPr/tcBorders/start
2025-02-20T10:18:42.678+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : continuing (with possible element/attribute loss)
2025-02-20T10:18:42.680+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"start"). Expected element
2025-02-20T10:18:42.680+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : troublesome node: <w:start xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" w:color="000000" w:space="0" w:sz="4" w:val="single"/>
2025-02-20T10:18:42.680+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : in parent node: <w:tcBorders xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:top w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:start w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:bottom w:color="000000" w:space="0" w:sz="4" w:val="single"/></w:tcBorders>
2025-02-20T10:18:42.680+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : #document/document/body/tbl/tr/tc/tcPr/tcBorders/start
2025-02-20T10:18:42.680+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : continuing (with possible element/attribute loss)
2025-02-20T10:18:42.681+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"start"). Expected element
2025-02-20T10:18:42.681+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : troublesome node: <w:start xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" w:color="000000" w:space="0" w:sz="4" w:val="single"/>
2025-02-20T10:18:42.681+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : in parent node: <w:tcBorders xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:top w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:start w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:bottom w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:end w:color="000000" w:space="0" w:sz="4" w:val="single"/></w:tcBorders>
2025-02-20T10:18:42.681+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : #document/document/body/tbl/tr/tc/tcPr/tcBorders/start
2025-02-20T10:18:42.682+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : continuing (with possible element/attribute loss)
2025-02-20T10:18:42.682+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : [ERROR] : unexpected element (uri:"http://schemas.openxmlformats.org/wordprocessingml/2006/main", local:"end"). Expected elements 
2025-02-20T10:18:42.682+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : troublesome node: <w:end xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" w:color="000000" w:space="0" w:sz="4" w:val="single"/>
2025-02-20T10:18:42.682+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : in parent node: <w:tcBorders xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:top w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:start w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:bottom w:color="000000" w:space="0" w:sz="4" w:val="single"/><w:end w:color="000000" w:space="0" w:sz="4" w:val="single"/></w:tcBorders>
2025-02-20T10:18:42.682+02:00  WARN 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : #document/document/body/tbl/tr/tc/tcPr/tcBorders/end
2025-02-20T10:18:42.682+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.JaxbValidationEventHandler      : continuing (with possible element/attribute loss)
2025-02-20T10:18:42.686+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.jaxb.NamespacePrefixMapperUtils      : Using ri.NamespacePrefixMapper, which is suitable for the JAXB RI
2025-02-20T10:18:42.699+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : Parent of org.docx4j.wml.Tbl is currently org.docx4j.openpackaging.parts.WordprocessingML.MainDocumentPart not org.docx4j.wml.Body
2025-02-20T10:18:42.699+02:00  INFO 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : setting to  org.docx4j.wml.Body
2025-02-20T10:18:42.700+02:00  INFO 33804 --- [nio-8080-exec-7] o.docx4j.model.structure.PageDimensions  : No cols in this section; defaulting.

and them many lines like:

...
2025-02-20T10:20:43.019+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.p.relationships.RelationshipsPart  : adding part with proposed name: /word/media/document_image_rId2572.jpeg
2025-02-20T10:20:43.050+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.050+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.050+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.050+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.050+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.050+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.052+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.parts.WordprocessingML.BinaryPart  : .. closed.
2025-02-20T10:20:43.052+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.p.relationships.RelationshipsPart  : adding part with proposed name: /word/media/document_image_rId2573.jpeg
2025-02-20T10:20:43.079+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.079+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.079+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.079+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.079+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.079+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.082+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.parts.WordprocessingML.BinaryPart  : .. closed.
2025-02-20T10:20:43.082+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.p.relationships.RelationshipsPart  : adding part with proposed name: /word/media/document_image_rId2574.jpeg
2025-02-20T10:20:43.112+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.112+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.112+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.112+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.112+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.112+02:00  WARN 33804 --- [nio-8080-exec-7] org.docx4j.TraversalUtil                 : null passed to getChildrenImpl
2025-02-20T10:20:43.113+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.parts.WordprocessingML.BinaryPart  : .. closed.
2025-02-20T10:20:43.113+02:00  INFO 33804 --- [nio-8080-exec-7] o.d.o.p.relationships.RelationshipsPart  : adding part with proposed name: /word/media/document_image_rId2575.jpeg

and after this my application not respond.

My report defined:
Report details
SQL_Groovy_1

and in Bands tab when choose SQL for Dataset type, appear Groovy script
SQL_Groovy_2

Parameters
SQL_Groovy_3

Value formats
SQL_Groovy_4

Templates
SQL_Groovy_5

Roles and views
SQL_Groovy_6

and the Template docx:
SQL_Groovy_7

LE. Jmix 2.4.4

florin.tanasa · February 20, 2025, 8:58am

Sorry, I found my mistake
I not put parameters in sql script

select 
d.qrcode, 
d.deposit_code, 
t.genus, 
t.species,  
p.accenumb  
from deposit d
inner join pasaport p on p.id=d.id_accenumb_id 
inner join taxonomy t on t.id=p.id_taxonomy_id 
where d.id in ${entities}
order by NULLIF(regexp_replace(p.accenumb, '\D','','g'), '')::numeric;

but very nice errors for this mistake and also is usefully this post for who not remember to put the parameters.