Problems using dynamic query with javascript variables

<p>Hi,</p>
<p> </p>
<p>I am designing a Birt Report which query depends on the value of several parameters.</p>
<p>In my DataSet I have this query defined:</p>
<p> </p>
<div>
<blockquote class="ipsBlockquote">
<div>select </div>
<div> o.NAME as DocName,</div>
<div>e.name as Engine,</div>
<div> count(a.ID) as numExec,</div>
<div> round(sum(a.EXECUTION_TIME) / Count(a.ID), 1) as TemExcMedio,</div>
<div> a.ID as id,</div>
<div> a.DOC_TYPE as DocType,</div>
<div> a.DOC_LABEL as DocLabel,</div>
<div> d.value_cd as Role,</div>
<div> a.username as UserName,</div>
<div> to_char(a.EXECUTION_START, 'YYYY-MM-DD') as execData,</div>
<div> to_char(a.EXECUTION_START, 'HH24:MI:SS') as oraData,</div>
<div> a.EXECUTION_TIME as execTime,</div>
<div> sum(a.error) as error</div>
<div>from</div>
<div> SBI_ENGINES e,</div>
<div> SBI_OBJECTS o ,</div>
<div>SBI_AUDIT a LEFT OUTER JOIN SBI_USER u on a.username = u.user_id </div>
<div>LEFT OUTER JOIN SBI_EXT_USER_ROLES ur on ur.id = u.id</div>
<div>LEFT OUTER JOIN SBI_EXT_ROLES r on ur.ext_role_id = r.ext_role_id</div>
<div>LEFT OUTER JOIN SBI_DOMAINS d on (d.value_id = r.role_type_id and d.domain_cd = 'role_type')</div>
<div>where</div>
<div>e.engine_id = o.engine_id and </div>
<div>o.biobj_id = a.doc_ref </div>
<div>USERNAME_PH</div>
<div>DOCUM_TYPE</div>
<div>DOCUM_NAME</div>
<div>DOC_PAR<span> </span></div>
<div>DATE_PH</div>
<div>Group BY o.NAME, e.name, a.ID , o.NAME , a.DOC_LABEL, a.DOC_TYPE , a.USERNAME , a.EXECUTION_START , a.EXECUTION_TIME,d.value_cd, a.EXECUTION_START,</div>
<div> <span> </span> a.EXECUTION_START</div>
<div> order by a.EXECUTION_START ,</div>
<div> a.EXECUTION_START, </div>
<div> d.value_cd</div>
<p> </p>
</blockquote>
<p> </p>
</div>
<div> </div>
<div> </div>
<div>Then I defined the following javascript code under the BeforeOpen Script section of the DataSet:</div>
<div> </div>
<div>
<div>
<blockquote class="ipsBlockquote">
<div>var condition =""; </div>
<div> </div>
<div>if (params["UserName"].value) {</div>
<div>condition = " and a.USERNAME like '"+ params["UserName"].value+"'";</div>
<div>this.queryText = this.queryText.replace("USERNAME_PH", condition);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("USERNAME_PH", "");</div>
<div> </div>
<div>if (params["DocumentType"].value) {</div>
<div>condition = " and a.DOC_TYPE like '"+ params["DocumentType"].value+"'";</div>
<div>this.queryText = this.queryText.replace("DOCUM_TYPE", condition);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("DOCUM_TYPE", "");</div>
<div> </div>
<div>if (params["DocumentName"].value) {</div>
<div>condition = " and o.NAME = '"+ params["DocumentName"].value+"'";</div>
<div>this.queryText = this.queryText.replace("DOCUM_NAME", condition);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("DOCUM_NAME", "");</div>
<div> </div>
<div>if (params["ParName"].value) {</div>
<div>condition = " and a.DOC_PARAMETERS like '%"+params["ParName"].value+"="+params["ParValue"].value+"%'";</div>
<div>this.queryText = this.queryText.replace("DOC_PAR", condition);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("DOC_PAR", "");<span> </span></div>
<div> </div>
<div> </div>
<div>if (((params["TimeFrom"].value != null) && (params["TimeFrom"].value != "")) && ((params["TimeTo"].value != null) && (params["TimeTo"].value != "")) && ((params["DataTo"].value != null) && (params["DataTo"].value != "")) ) {</div>
<div>condition = " AND (to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') and to_char(EXECUTION_START,'HH24:MI:SS') >= to_char('"+params["TimeFrom"].value+"','HH24:MI:SS')) and (to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char('"+params["DataTo"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') <= to_char('"+params["TimeTo"].value+"','HH24:MI:SS'))";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div>} </div>
<div>else if (((params["TimeFrom"].value != null) && (params["TimeFrom"].value != "")) && ((params["TimeTo"].value != null) && (params["TimeTo"].value != "")) && ((params["DataTo"].value == null) || (params["DataTo"].value == "")) ) {</div>
<div>condition = " AND (to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'H24H:MI:00') >= to_char('"+params["TimeFrom"].value+"','HH24:MI:SS')) <span> </span>and (to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') <= to_char('"+params["TimeTo"].value+"','HH24:MI:SS'))";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div>}</div>
<div> </div>
<div> </div>
<div>else if (((params["TimeFrom"].value != null) && (params["TimeFrom"].value != "")) && ((params["TimeTo"].value == null) || (params["TimeTo"].value == "")) && ((params["DataTo"].value != null) && (params["DataTo"].value != "")) ) {</div>
<div>condition = " AND (to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') >= to_char('"+params["TimeFrom"].value+"','HH24:MI:SS')) <span> </span>and (to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char('"+params["DataTo"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') <= to_char('23:59:00','HH24:MI:SS'))";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div>} </div>
<div>else if (((params["TimeFrom"].value != null) && (params["TimeFrom"].value != "")) && ((params["TimeTo"].value == null) || (params["TimeTo"].value == "")) && ((params["DataTo"].value == null) || (params["DataTo"].value == "")) ) {</div>
<div>condition = " AND (to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') >= to_char('"+params["TimeFrom"].value+"','HH24:MI:SS')) <span> </span>and (to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') <= to_char('23:59:00','HH24:MI:SS'))";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div>} </div>
<div> </div>
<div> </div>
<div>else if (((params["TimeFrom"].value == null) || (params["TimeFrom"].value == "")) && ((params["TimeTo"].value != null) && (params["TimeTo"].value != "")) && ((params["DataTo"].value != null) && (params["DataTo"].value != ""))) {</div>
<div>condition = " AND (to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span> and to_char(EXECUTION_START,'HH24:MI:SS') >= to_char('00:00:00','HH24:MI:SS')) <span> </span>and (to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char('"+params["DataTo"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') <= to_char('"+params["TimeTo"].value+"','HH24:MI:SS'))";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div>}</div>
<div>else if (((params["TimeFrom"].value == null) || (params["TimeFrom"].value == "")) && ((params["TimeTo"].value != null) && (params["TimeTo"].value != "")) && ((params["DataTo"].value == null) || (params["DataTo"].value == ""))) {</div>
<div>condition = " AND (to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:00') >= to_char('00:00:00','HH24:MI:SS')) <span> </span>and (to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') <= to_char('"+params["TimeTo"].value+"','HH24:MI:SS'))";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div>}</div>
<div> </div>
<div> </div>
<div> <span> </span>else if (((params["TimeFrom"].value == null) || (params["TimeFrom"].value == "")) && ((params["TimeTo"].value == null) || (params["TimeTo"].value == "")) && ((params["DataTo"].value != null) && (params["DataTo"].value != ""))){</div>
<div>condition =" AND (to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') >= to_char('00:00:00','HH24:MI:SS')) <span> </span>and (to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char('"+params["DataTo"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') <= to_char('23:59:00','HH24:MI:SS'))";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div>}</div>
<div>else if (((params["TimeFrom"].value == null) || (params["TimeFrom"].value == "")) && ((params["TimeTo"].value == null) || (params["TimeTo"].value == "")) && ((params["DataTo"].value == null) || (params["DataTo"].value == ""))){</div>
<div>condition =" AND (to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') >= to_char('00:00:00','HH24:MI:SS')) <span> </span>and (to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char('"+params["DataFrom"].value+"','YYYY-MM-DD') <span> </span>and to_char(EXECUTION_START,'HH24:MI:SS') <= to_char('23:59:00','HH24:MI:SS'))";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div>}</div>
<div> </div>
<div>myquery = this.queryText;</div>
<p> </p>
</blockquote>
<p> </p>
</div>
<div> </div>
<div> </div>
<div>The problem is that these returns an error when trying to load the output columns, the error is:</div>
<div> </div>
<div>
<div>
<blockquote class="ipsBlockquote">
<div>A BIRT exception occurred.</div>
<div> Plug-in Provider:Eclipse BIRT Project</div>
<div> Plug-in Name:BIRT Data Engine</div>
<div> Plug-in ID:org.eclipse.birt.data</div>
<div> Version:4.2.1.v201209121213</div>
<div> Error Code:data.engine.fail.prepareExecution</div>
<div> Error Message:Failed to prepare the query execution for the data set: DataSetReporte1</div>
<div>Cannot set the string value (null) to parameter 1.</div>
<div> org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.</div>
<div>SQL error #1:Ãndice de columna no vÃ¡lido</div>
<div> ;</div>
<div> java.sql.SQLException: Ãndice de columna no vÃ¡lido</div>
<p> </p>
</blockquote>
<p> </p>
</div>
<div> </div>
<div>I am new with BIRT Reports so I may be doing something wrong but I couldnÂ´t find the answer on the internet.</div>
<div>If someone coult help me I woulr really appreciate it.</div>
</div>
</div>
<p> </p>

Find more posts tagged with

Comments

JFreeman

<p>A good place to start is to log out the query you are building in beforeOpen to examine the query that is built.</p>
<p> </p>
<p>Looking over your code, the first thing that jumps out at me is your null checks. I do not believe these are going to catch undefined values from parameters the way you want.</p>
<p> </p>
<p>The way you have them setup currently (params["DataTo"].value == null) would work fine in java but in javascript null works a bit different. Try changing this check to:</p>
<pre class="_prettyXprint _lang-js">
if(!params["DataTo"].value) </pre>
<p>You will also want to change (params["DataTo"].value != null) to:</p>
<pre class="_prettyXprint _lang-js">
if(params["DataTo"].value)
</pre>

earrua

<p>Hello again,</p>
<p> </p>
<p>I made some changes to my javascript and my query to try to solve the issue but I keep having problems.</p>
<p> </p>
<p>This is my javascript:</p>
<p> </p>
<div>
<blockquote class="ipsBlockquote">
<div>var condition =""; </div>
<div>condition = " AND to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char(to_date('"+params["DataFrom"].value+")','YYYY-MM-DD') and to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char(to_date('"+params["DataTo"].value+"'),'YYYY-MM-DD') ";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div> </div>
<div>var phUName="";</div>
<div>if (params["UserName"].value) {</div>
<div>phUName = " and a.USERNAME like '"+ params["UserName"].value+"'";</div>
<div>this.queryText = this.queryText.replace("USERNAME_PH", phUName);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("USERNAME_PH", "");</div>
<div> </div>
<div>var phDocName="";</div>
<div>if (params["DocumentName"].value) {</div>
<div>phDocName = " and o.NAME = '"+ params["DocumentName"].value+"'";</div>
<div>this.queryText = this.queryText.replace("DOCUM_NAME", phDocName);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("DOCUM_NAME", "");<span> </span></div>
<div> </div>
<div>var phDType="";</div>
<div>if (params["DocumentType"].value) {</div>
<div>phDType = " and a.DOC_TYPE like '"+ params["DocumentType"].value+"'";</div>
<div>this.queryText = this.queryText.replace("DOCUM_TYPE", phDType);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("DOCUM_TYPE", "");</div>
<div> </div>
<div>myquery = this.queryText;</div>
<p> </p>
</blockquote>
<p> </p>
</div>
<p>I get two different errors, the first one is the same I described in the first question:</p>
<p> </p>
<div>
<blockquote class="ipsBlockquote">
<div>A BIRT exception occurred.</div>
<div> Plug-in Provider:Eclipse BIRT Project</div>
<div> Plug-in Name:BIRT Data Engine</div>
<div> Plug-in ID:org.eclipse.birt.data</div>
<div> Version:4.2.1.v201209121213</div>
<div> Error Code:data.engine.fail.prepareExecution</div>
<div> Error Message:Failed to prepare the query execution for the data set: DataSetReporte1</div>
<div>Cannot set the string value (null) to parameter 1.</div>
<div> org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.</div>
<div>SQL error #1:Ãndice de columna no vÃ¡lido</div>
<div> ;</div>
<div> java.sql.SQLException: Ãndice de columna no vÃ¡lido</div>
<p> </p>
</blockquote>
<p> </p>
<p>Then I get another error, it seems like the javascript code is not ok, but I do not see any error:</p>
<p> </p>
<div> </div>
<blockquote class="ipsBlockquote">
<div>
<div>A BIRT exception occurred.</div>
<div> Plug-in Provider:Eclipse BIRT Project</div>
<div> Plug-in Name:BIRT Data Engine</div>
<div> Plug-in ID:org.eclipse.birt.data</div>
<div> Version:4.2.1.v201209121213</div>
<div> Error Code:data.engine.FailToExecuteScript</div>
<div> Error Message:Fail to execute script in function __bm_beforeOpen(). Source:</div>
<div>

</div>
<div>" + var condition =""; </div>
<div>condition = " AND to_char(EXECUTION_START,'YYYY-MM-DD') >= to_char(to_date('"+params["DataFrom"].value+")','YYYY-MM-DD') and to_char(EXECUTION_START,'YYYY-MM-DD') <= to_char(to_date('"+params["DataTo"].value+"'),'YYYY-MM-DD') ";</div>
<div>this.queryText = this.queryText.replace("DATE_PH", condition);</div>
<div> </div>
<div>var phUName="";</div>
<div>if (params["UserName"].value) {</div>
<div>phUName = " and a.USERNAME like '"+ params["UserName"].value+"'";</div>
<div>this.queryText = this.queryText.replace("USERNAME_PH", phUName);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("USERNAME_PH", "");</div>
<div> </div>
<div>var phDocName="";</div>
<div>if (params["DocumentName"].value) {</div>
<div>phDocName = " and o.NAME = '"+ params["DocumentName"].value+"'";</div>
<div>this.queryText = this.queryText.replace("DOCUM_NAME", phDocName);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("DOCUM_NAME", "");<span> </span></div>
<div> </div>
<div>var phDType="";</div>
<div>if (params["DocumentType"].value) {</div>
<div>phDType = " and a.DOC_TYPE like '"+ params["DocumentType"].value+"'";</div>
<div>this.queryText = this.queryText.replace("DOCUM_TYPE", phDType);</div>
<div>} else</div>
<div>this.queryText = this.queryText.replace("DOCUM_TYPE", "");</div>
<div> </div>
<div>myquery = this.queryText; + "</div>
<div>

</div>
<div>A BIRT exception occurred. See next exception for more information.</div>
<div>Error evaluating Javascript expression. Script engine error: TypeError: Cannot read property "value" from undefined (<inline>#2)</div>
<div> Script source: <inline>, line: 0, text:</div>
<div>__bm_beforeOpen()</div>
</div>
<div> </div>
<p> </p>
</blockquote>
<p> </p>
</div>
<p>Am I doing something wrong in the Javascript that I canÂ´t see?</p>
<p> </p>
<p>Thanks a lot for your help!</p>

JFreeman

<p>Based on the error, it looks like it may be having issues with one of your parameters you are trying to read on line #2 of the beforeOpen script.</p>
<p> </p>
<p>Can you attach your report design?</p>

earrua

<p>Yes, sure. </p>
<p>Thank you.</p>

JFreeman

<p>Nothing is jumping out at me looking at the report design.</p>
<p> </p>
<p>Can you replace the params["DataFrom"].value and params["DataTo"].value on line #2 in the beforeOpen with hard coded values and test it again?</p>

earrua

<p>I did as you said and the javascript error is not appearing anymore, now I get only this one:</p>
<p> </p>
<div>
<blockquote class="ipsBlockquote">
<div>A BIRT exception occurred.</div>
<div> Plug-in Provider:Eclipse BIRT Project</div>
<div> Plug-in Name:BIRT Data Engine</div>
<div> Plug-in ID:org.eclipse.birt.data</div>
<div> Version:4.2.1.v201209121213</div>
<div> Error Code:data.engine.fail.prepareExecution</div>
<div> Error Message:Failed to prepare the query execution for the data set: DataSetReporte1</div>
<div>Cannot set the string value (null) to parameter 1.</div>
<div> org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.</div>
<div>SQL error #1:Ãndice de columna no vÃ¡lido</div>
<div> ;</div>
<div> java.sql.SQLException: Ãndice de columna no vÃ¡lido</div>
<p> </p>
</blockquote>
<p> </p>
</div>

JFreeman

<p>I think this may have to do with the parameters you have configured on the data set.</p>
<p>You have 6 parameters configured on the data set that are input parameters and I think those are what is getting the null for that error.</p>
<p> </p>
<p>It doesn't look like you are actually using those parameters either as you are manually replacing the values in beforeOpen with this.queryText.</p>
<p> </p>
<p>Can you remove the parameters from the data set and try it again?</p>

earrua

<p>I removed the parameters and now it seems to be working fine.</p>
<p>At least I can get the Output Columns result. </p>
<p>Thank you very much for your help! </p>

JFreeman

<p>You're welcome.</p>
<p> </p>
<p>Let us know if you have additional questions.</p>