Home » Archived » BIRT » Error with dataset reading from a BIT DATA column
Error with dataset reading from a BIT DATA column [message #753734] |
Fri, 28 October 2011 14:33 |
Anthony Farrell Messages: 25 Registered: April 2011 Location: Ireland |
Junior Member |
|
|
Apologies in advance for the length of this post.
Let me layout my issue first. I am showing a simple example of the issue I have.
Let's say I have a table like so on DB2 (it will be on a zos platform, but for now Db2 on windows is my test environment).
1. create table employertest (concernroleid varchar(100) for bit data, name varchar(100));
2. insert into employertest values (x'4f33ca8b90', 'test name');
3. select concernroleid, name from employertest;
The concernroleid column has a type of BIT DATA (its what I have to work with end of story)
and used to store the primary key on zos (on other DB2 systems we use the data type BIGINT).
I have a BIRT report that must have the following key elements:
1. Parameter. a primary key passed into the report as a number, "a long data type", e.g. concernroleid (I have set the data type of the BIRT report parameter to Decimal), default value of e.g. 109.
2. DataSet (to keep it simple). The SQL is (select 75 as casecount, 'employertest' as status from employertest where concernroleid=?)
3. Within the dataset I create a parameter and bind it to the report parameter.
Issues:
1. Within the dataset editor I select "preview results" and I get the following illegal conversion, the error message varies with the parameter type as listed in the data set wizard, parameters section(the parameter type for the report level parameter has no bearing as far as I can see)
dataset parameter type errors
String
com.ibm.db2.jcc.am.SqlException: [jcc][1083][10403][3.61.75] Illegal conversion: can not convert from "java.lang.String" to "byte[]" ERRORCODE=-4474, SQLSTATE=null
Float
com.ibm.db2.jcc.am.SqlException: [jcc][1083][10398][3.61.75] Illegal conversion: can not convert from "java.lang.double" to "byte[]" ERRORCODE=-4474, SQLSTATE=null
Integer
com.ibm.db2.jcc.am.SqlException: [jcc][1083][10395][3.61.75] Illegal conversion: can not convert from "java.lang.int" to "byte[]" ERRORCODE=-4474, SQLSTATE=null
Decimal
com.ibm.db2.jcc.am.SqlException: [jcc][1083][10399][3.61.75] Illegal conversion: can not convert from "java.math.BigDecimal" to "byte[]" ERRORCODE=-4474, SQLSTATE=null
JavaObject
com.ibm.db2.jcc.am.SqlException: [jcc][1083][10399][3.61.75] Illegal conversion: can not convert from "java.math.BigDecimal" to "byte[]" ERRORCODE=-4474, SQLSTATE=null[/size]
2. The data set is hooked up to a chart, when I run the report I get the same error (obviously).
Questions:
My overriding question is how can I get this to work !, breaking it down I have the following questions:
1. Report Parmeters. The only data types I can choose from the drop down are (boolean, string, date, number(varying types). I presume I must use Decimal ?, yes
2) DataSet Parameter. Within the dataset wizard what data type should the parameter be ?(bearing in mind its bound to the report parameter and the data type in the DB is BIT DATA).
I can think of two solutions:
1) Could I write an DataSetEventHandler set the parameter in code to a type of "byte[]" or something that would work ?, is one of the allowable parameter types suitable
or are there no suitable column types defined that I can use in a Java event handler ?
2) write a stored procedure in the db2 database and modify the sql in the report to use it thus hopefully avoiding the issue (e.g. select 75 as casecount, 'employertest' as status from employertest where concernroleid=convertBITDataToNumber(?))
Any help is appreciated,
Anthony Farrell.
|
|
|
Re: Error with dataset reading from a BIT DATA column [message #753777 is a reply to message #753734] |
Fri, 28 October 2011 17:12 |
|
Anthony,
I am not sure this will work but may be worth a try. Set the type to
Decimal in the parameter. Remove the parameter from the dataset
ie change
select 75 as casecount, 'employertest' as status from employertest where
concernroleid=?
to
select 75 as casecount, 'employertest' as status from employertest
Then in the dataset beforeOpen script add this
importPackage(Packages.java.math);
var tst = BigDecimal.valueOf(params["myDecimal"].value);
theInt = tst.unscaledValue();
//byte array
arr = theInt.toByteArray();
this.queryText = this.queryText + " where concernroleid="+arr
Jason
On 10/28/2011 10:33 AM, Anthony Farrell wrote:
> Apologies in advance for the length of this post.
>
> Let me layout my issue first. I am showing a simple example of the issue
> I have.
>
> Let's say I have a table like so on DB2 (it will be on a zos platform,
> but for now Db2 on windows is my test environment).
>
>
> 1. create table employertest (concernroleid varchar(100) for bit data,
> name varchar(100));
> 2. insert into employertest values (x'4f33ca8b90', 'test name');
> 3. select concernroleid, name from employertest;
>
> The concernroleid column has a type of BIT DATA (its what I have to work
> with end of story) and used to store the primary key on zos (on other
> DB2 systems we use the data type BIGINT).
>
> I have a BIRT report that must have the following key elements:
>
> 1. Parameter. a primary key passed into the report as a number, "a long
> data type", e.g. concernroleid (I have set the data type of the BIRT
> report parameter to Decimal), default value of e.g. 109. 2. DataSet (to
> keep it simple). The SQL is (select 75 as casecount, 'employertest' as
> status from employertest where concernroleid=?)
> 3. Within the dataset I create a parameter and bind it to the report
> parameter.
>
> Issues:
> 1. Within the dataset editor I select "preview results" and I get the
> following illegal conversion, the error message varies with the
> parameter type as listed in the data set wizard, parameters section(the
> parameter type for the report level parameter has no bearing as far as I
> can see)
>
> dataset parameter type errors
> String
> com.ibm.db2.jcc.am.SqlException: [jcc][1083][10403][3.61.75] Illegal
> conversion: can not convert from "java.lang.String" to "byte[]"
> ERRORCODE=-4474, SQLSTATE=null
> Float
> com.ibm.db2.jcc.am.SqlException: [jcc][1083][10398][3.61.75] Illegal
> conversion: can not convert from "java.lang.double" to "byte[]"
> ERRORCODE=-4474, SQLSTATE=null
> Integer
> com.ibm.db2.jcc.am.SqlException: [jcc][1083][10395][3.61.75] Illegal
> conversion: can not convert from "java.lang.int" to "byte[]"
> ERRORCODE=-4474, SQLSTATE=null
> Decimal
> com.ibm.db2.jcc.am.SqlException: [jcc][1083][10399][3.61.75] Illegal
> conversion: can not convert from "java.math.BigDecimal" to "byte[]"
> ERRORCODE=-4474, SQLSTATE=null
> JavaObject
> com.ibm.db2.jcc.am.SqlException: [jcc][1083][10399][3.61.75] Illegal
> conversion: can not convert from "java.math.BigDecimal" to "byte[]"
> ERRORCODE=-4474, SQLSTATE=null[/size]
>
>
> 2. The data set is hooked up to a chart, when I run the report I get the
> same error (obviously).
>
>
> Questions:
> My overriding question is how can I get this to work !, breaking it down
> I have the following questions:
>
> 1. Report Parmeters. The only data types I can choose from the drop down
> are (boolean, string, date, number(varying types). I presume I must use
> Decimal ?, yes
>
> 2) DataSet Parameter. Within the dataset wizard what data type should
> the parameter be ?(bearing in mind its bound to the report parameter and
> the data type in the DB is BIT DATA).
>
> I can think of two solutions:
>
> 1) Could I write an DataSetEventHandler set the parameter in code to a
> type of "byte[]" or something that would work ?, is one of the allowable
> parameter types suitable or are there no suitable column types defined
> that I can use in a Java event handler ?
>
> 2) write a stored procedure in the db2 database and modify the sql in
> the report to use it thus hopefully avoiding the issue (e.g. select 75
> as casecount, 'employertest' as status from employertest where
> concernroleid=convertBITDataToNumber(?))
>
> Any help is appreciated,
> Anthony Farrell.
|
|
| |
Re: Error with dataset reading from a BIT DATA column [message #754193 is a reply to message #754192] |
Tue, 01 November 2011 10:15 |
|
I assume you have the right report parameter name in the script?
Log out the values using the following script snippet in addition to the
script you already have:
importPackage( Packages.java.io );
out = new PrintWriter( new FileWriter( "c:/temp/dsevents.txt", true ) );
out.println( "dataset beforeOpen " + params["myDecimal"].value);
out.close();
Jason
On 11/1/2011 5:59 AM, Anthony Farrell wrote:
> Apologies for this terse post, I will debug this a little later today as
> I have some urgent tasks for the next 2-3 hours.
>
> After pasting in your suggestion I get the following error :
>
> com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104,
> SQLSTATE=42601, SQLERRMC=[;where concernroleid=;<space>, DRIVER=3.61.75
>
> I guess the "+arr" in the statement is not returning a value, if you
> have any futher idea's all help is welcome.
>
> this.queryText = this.queryText + " where concernroleid="+arr
>
>
> I will be coming back to this very shortly as it is very urgent, as I
> say I will debug later today.
>
|
|
| | |
Re: Error with dataset reading from a BIT DATA column [message #757821 is a reply to message #757006] |
Wed, 16 November 2011 16:48 |
|
Anthony,
Log enhancement request through bugzilla.
http://www.eclipse.org/birt/phoenix/community.php
Not sure this will help but BIRT supports Java Objects for column types
already.
http://birtworld.blogspot.com/2010/10/birt-java-object-data-type.html?showComment=1313634823785
Jason
On 11/15/2011 6:51 PM, Anthony Farrell wrote:
> Is this the right forum to request a new feture ?
>
>
> Is it possible to extend the stand ODA data set design widget to provide
> another data type, i.e. allow an oda parameter to have a data type of
> type of byte[] or object.
>
>
> Basically the facility to define a data set parameter and its type with
> the ability bind the data set value a report variable.
>
> I guess at some stage the oda uses the JDBC api setXXX methods, so the
> ability to define a report variable which would have a data type that
> could map thought the ODA widget (and its data type) through to the JDBC
> setXXX methods.
>
> so in my example above I could define a report variable of type byte[],
> I will assign it a value from a long data type being passed as a report
> parameter, the oda would be amended in future release (ASAP) tob e
> clever enought to call the right JDBC setXXX method, e.g. setBytes, or
> setObject.
|
|
|
Goto Forum:
Current Time: Fri Apr 26 19:34:38 GMT 2024
Powered by FUDForum. Page generated in 0.04275 seconds
|