Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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 Go to next message
Anthony Farrell is currently offline Anthony FarrellFriend
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 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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 #754192 is a reply to message #753734] Tue, 01 November 2011 09:59 Go to previous messageGo to next message
Anthony Farrell is currently offline Anthony FarrellFriend
Messages: 25
Registered: April 2011
Location: Ireland
Junior Member
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 #754193 is a reply to message #754192] Tue, 01 November 2011 10:15 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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 #754200 is a reply to message #754193] Tue, 01 November 2011 10:49 Go to previous messageGo to next message
Anthony Farrell is currently offline Anthony FarrellFriend
Messages: 25
Registered: April 2011
Location: Ireland
Junior Member
I have modified the beforeOpen DataSet with the following script (as you suggested Jason), I also enclosed where clause parameter in quotes.
The output to the text file is in green below, it seems to work, I need to bottom this out to make sure I dont have a false positive.

importPackage(Packages.java.math);
importPackage( Packages.java.io );

var tst = BigDecimal.valueOf(params["concernroleid"].value);
theInt = tst.unscaledValue();
//byte array
arr = theInt.toByteArray();

out = new PrintWriter( new FileWriter( "c:\dsevents.txt", true ) );
out.println( "dataset beforeOpen:" + params["concernroleid"].value);
out.println( "dataset beforeOpen tst:" + tst);
out.println( "dataset beforeOpen theInt:" + theInt);
out.println( "dataset beforeOpen arr:" + arr);

out.flush();

this.queryText = this.queryText + " where concernroleid=" + "'" + arr + "'"
out.println( "dataset beforeOpen this.queryText:" + this.queryText);

out.close();



The log file output is :
dataset beforeOpen:1096
dataset beforeOpen tst:1096.0
dataset beforeOpen theInt:10960
dataset beforeOpen arr:[B@1e0980c
dataset beforeOpen this.queryText:select 25 as casecount, 'open' as status from employertest
union
select 45 as casecount, 'closed' as status from employertest
union
select 75 as casecount, 'open active' as status from employertest
unnion
union
select 75 as casecount, 'employertest' as status from employertest where concernroleid='[B@1e0980c'
Re: Error with dataset reading from a BIT DATA column [message #757006 is a reply to message #754193] Tue, 15 November 2011 23:51 Go to previous messageGo to next message
Anthony Farrell is currently offline Anthony FarrellFriend
Messages: 25
Registered: April 2011
Location: Ireland
Junior Member
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.
Re: Error with dataset reading from a BIT DATA column [message #757821 is a reply to message #757006] Wed, 16 November 2011 16:48 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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.
Previous Topic:How to get a Chart from a ReportDesignHandle?
Next Topic:How do you protect rptdesign?
Goto Forum:
  


Current Time: Sat Nov 29 00:38:59 GMT 2014

Powered by FUDForum. Page generated in 0.02900 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software