| sqlbase bind parameter results to error: execute(): cannot execute with insufficient bind variables! [message #1455462] |
Wed, 29 October 2014 11:55  |
 Tim Banchi
Messages: 3
Registered: October 2014 |
Junior Member |
|
|
I use Eclipse Luno with a recent BIRT (version 4.4.1.v2014...) and try to execute a select query on a SQLBase database version 8.5. SQLBase is part of the multivers/unit4 bookkeeping program and its not possible to upgrade the SQLBase database to a recent version (>11) without inhibiting the working of the bookkeeping program.
To make the connection I used different versions of the JDBC driver provided by SQLBase (versions 10.0.0, 11.5.4 and the most recent 11.7.3). It all results in the same error message.
Searching for the error message execute(): cannot execute with insufficient bind variables!
I think there are three scenarios which could solve the problem:
===> 1) Getting a "normal" query with an input Parameter to work:
The following configuration is used:
a) Tab Query text: select @coalesce(sum(XXXXX),0) as XXXXX from YYYYY where ZZZZ = 1111 and DDDDD = 732010 and BOEKJAAR = ?
b) Tab Parameters: Name: param_1, DataType: Integer, Direction: Input, Default Value: 2012, no link to report parameter
all other things are left as they where when adding a new Dataset
--> Result:
I get the error message execute(): cannot execute with insufficient bind variables! and on the SQLBase server I see the following query executed:
select @coalesce(sum(XXXXX),0) as XXXXX from YYYYY where ZZZZ = 1111 and DDDDD = 732010 and BOEKJAAR = :1
so "?" is replaced by :1 (thus the first parameter).
But why is JDBC not replacing it with the default value? If I just take another data set (where another datasource is used and the parameters are working) and set the SQLBase datasource and the above configuration, the result is the same. So what do I do wrong? Or is the JDBC driver for SQLBase not capable of this?
If I execute the query without parameters everything works well and I get the proper result.
===> 2) Getting Property Binding with a dynamic values to work (value from another query, not from the report itself)
a) Query Text: Select FROM (automatically filled out)
b) Parameters: empty
c) Property binding:
Query Text: "select @coalesce(sum(XXXXX),0) as XXXXX from YYYYY where ZZZZ = 1111 and DDDDD = 732010 and BOEKJAAR = " + params["jaar"].value
--> Result: while I get an error in BIRT ([select from ]: Error preparing SQL statement), on the SQLBase server side I see the correct query executed (thus params["jaar"].value becomes 2013) and I get the correct result.
Unfortunately I couldn't find out a way to use a property binding for dynamic values (in this case the value: row["AGG_projectcode"].toString() which is a value from another Data Set)
===> 3) writing java (or I guess better javascript) code to do a prepare and execute statement as described in the SQLBase guide (cannot post the url but one can search for SQLBase "Guide to connecting to SQLBase")
Can somebody help me out? I guess I'm doing something silly wrong (esp. in the preferred first scenario). I searched the net up and down but couldn't find any useful information.
|
|
|
|
| [SOLVED]: sqlbase bind parameter error: execute(): cannot execute with insufficient bind variab [message #1486809 is a reply to message #1461406] |
Tue, 25 November 2014 09:55  |
Tim Banchi
Messages: 3
Registered: October 2014 |
Junior Member |
|
|
I ran reply and solve (somehow) my own question.
After hours (or days) I accidentially found out that birt simply has problems with creating a working Data Set if you edit an existing dataset (probably only with the jdbc sqlbase drivers?). Birt writes incomplete and/or superfluous and/or wrong XML code so the dataset does not work and the error execute(): cannot execute with insufficient bind variables!
When editing an existing dataset I get the following (not working) XML code:
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="1_giftentest" id="6860">
<list-property name="columnHints"/>
<list-property name="parameters">
<structure>
<property name="name">param_1</property>
<property name="nativeName"></property>
<property name="dataType">string</property>
<property name="nativeDataType">0</property>
<property name="position">1</property>
<expression name="defaultValue" type="constant">7710</expression>
<property name="isOptional">false</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">param_2</property>
<property name="nativeName"></property>
<property name="dataType">string</property>
<property name="nativeDataType">0</property>
<property name="position">2</property>
<expression name="defaultValue" type="constant">2011</expression>
<property name="isOptional">false</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-property>
<structure name="cachedMetaData"/>
<property name="dataSource">ttt_multivers</property>
<xml-property name="queryText"><![CDATA[select @coalesce(sum(credit-debet),0) as somgiften
from YYYYYYYY
where rrrr = ? and dddd = ? and cdgrbrekening = 345345345]]></xml-property>
</oda-data-set>
If I create a new dataset the data set works (although you have to fiddle with the input parameters when setting it up)
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="1_giftentest" id="10185">
<list-property name="parameters">
<structure>
<property name="name">param_1</property>
<property name="dataType">string</property>
<property name="position">1</property>
<expression name="defaultValue" type="javascript">7710</expression>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">param_2</property>
<property name="dataType">integer</property>
<property name="position">2</property>
<expression name="defaultValue" type="javascript">2013</expression>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-property>
<structure name="cachedMetaData"/>
<property name="dataSource">ttt_multivers</property>
<xml-property name="queryText"><![CDATA[select @coalesce(sum(credit-debet),0) as somgiften
from YYYYYYYY
where rrrr = ? and dddd = ? and cdgrbrekening = 345345345]]></xml-property>
</oda-data-set>
In the course of hunting the problem down I also had differences between a working data set and a non-working data-set in:
1) list-property columnHints
2) structure cachedMetaData
3) list-property result set.
As far as I remember the below XML code was missing in the non-working Data Sets:
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">somlegaten</property>
<property name="dataType">float</property>
</structure>
</list-property>
</structure>
<property name="dataSource">boekh_multivers</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">somlegaten</property>
<property name="nativeName">somlegaten</property>
<property name="dataType">float</property>
</structure>
</list-property>
This is misleading considering the error message refers to insufficient bind parameters.
There is also a way to edit an existing data set to make it work (although afterwards I had troubles editing it - the insufficient bind parameter error comes back up and then):
1) take a dataset which you want to edit and right-click to edit
2) change the datasource to something wrong (not the datasource you want to use and not the datasource which is already set)
3) go to query to change the query
4) go back to the data source and set the correct data source
5) go to the parameters and edit the parameters
6) probably now you can set all the other parameters and still get a working data set (never tried out as I only had to edit the query, the parameters and the datasource)
After that I get the following (working) Data Set XML Code:
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="1_giftentest" id="6886">
<list-property name="columnHints"/>
<list-property name="parameters">
<structure>
<property name="name">param_1</property>
<property name="nativeName"></property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
<property name="position">1</property>
<expression name="defaultValue" type="javascript">"9957"</expression>
<property name="isOptional">true</property>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">param_2</property>
<property name="nativeName"></property>
<property name="dataType">integer</property>
<property name="nativeDataType">5</property>
<property name="position">2</property>
<expression name="defaultValue" type="javascript">2013</expression>
<property name="allowNull">false</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">somgiften</property>
<property name="dataType">float</property>
</structure>
</list-property>
</structure>
<property name="dataSource">ttt_multivers</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">somgiften</property>
<property name="nativeName">somgiften</property>
<property name="dataType">float</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select @coalesce(sum(credit-debet),0) as somgiften
from YYYYYYYY
where rrrr = ? and dddd = ? and cdgrbrekening = 345345345]]></xml-property>
</oda-data-set>
So in this working Data Set the list-property parameters is different to the newly created data set (which in the first example was the reason for the dataset to work vs if you edit an existing data set ....). What is new is the structure cachedMetadata (with the list-property resultSet) and the list property resultSet.
On the serverside I get the following logging information:
2> select @coalesce(sum(credit-debet),0) as somgiften
from YYYYYYYY
where rrrr = :1 and dddd = :2 and cdgrbrekening = 345345345
12> cur=38 snm=100 cfl=1 fcd=3 [compile]
12> cur=38 snm=100 cfl=1 fcd=4 [execute]
12> 0[9957] #--> first parameter
12> 1[2011] #--> second parameter
.....
When the Data Set was not working I never saw the two parameters passed on the serverside.
The used BIRT Version is:
<property name="createdBy">Eclipse BIRT Designer Version 4.4.1.v201408290142 Build <4.4.1.v20140916-1320></property>
This post is not really a solution, more a workaround. But I hope I can help somebody to overcome a similar problem - or just show that in my example it is not the jdbc SQLBase driver which causes the problem but the XML code written by BIRT.
I hope someone with more insight in Birt can clarify this problem?
[Updated on: Tue, 25 November 2014 10:02] Report message to a moderator |
|
|
Powered by
FUDForum. Page generated in 0.01899 seconds
] 
Search
Help
Register
Login
Home
