open JSON data from an url

Martial Wilbert · September 9, 2014

Hi,
 
I need to create a report that using Json formatted data as source.
For my tests, I used a parameters called jSonData where I just copied a jSon Data sample.
 
I was a able to retrieve all the data I needed and create the report design by using the eval() old school style.
 
Now, in production, JSon data will come from a server. Means that I have to use a specific url. This url sent me back the Json Data as content.
 
I thought that it will be simple, In fact, it's just stream an url content to a string and make an eval on this string.
But it seems that inputstream is not so easy on BIRT scripting cause I do not found how to do that.
 
 
Spend couple of hours to looking for a little piece of code that will save me

I found several nice information to use url for XML DataSource. 
 
Found that I may can use jsonparser but my "dear" customer explicitly explained that "no additionnal jar files can be deployed on his server".
Why doing simple when it can be complex ? :P Enjoy !!
 
Did someone can explain me how can I get the content from an URL to a string ?
 
Thanks for your help.

kclark · September 9, 2014

Try doing something like this
<pre class="_prettyXprint _lang-">
importPackage(Packages.java.io);
importPackage(Packages.java.lang);
importPackage(Packages.java.net);

url = new URL("http://localhost:8080/birt420/test.json");
conn = url.openConnection();
br = new BufferedReader(new InputStreamReader(conn.getInputStream()));

response = new StringBuilder();
inputLine = new String();

while ((inputLine = br.readLine()) != null)
response.append(inputLine);
br.close();

response.toString(); // Eval this
</pre>
I stopped at the eval but this should work for you

Edit:
FWIW I've been told that using eval is insecure even though I haven't looked into it much. I know your client doesn't want to use additional jars but it's worth bringing that point up to him. there are a few JSON parsing lib's out there that made my life a whole lot easier when using them over eval.

Martial Wilbert · September 10, 2014

Thanks Kristopher.
Seems not able to use your solution.
 
What I've done to be as clear as possible: (quite difficult for a french guy :P)
 
Initial state:
one parameter where I put my JSON formatted data.
 
On initalize of my report, I put this code:
<pre class="_prettyXprint">
jsonObject = eval( '(' + params["jsondata"].value + ')' ) ;</pre>
On fetch of my Dataset:
<div>
<pre class="_prettyXprint">
if (cnt < jsonObject.length)
{
row["Symbole"] = jsonObject[cnt].Symb;
row["TempMin"] = jsonObject[cnt].TempMin;
row["TempMax"] = jsonObject[cnt].TempMax;
row["Label"] = jsonObject[cnt].fr;
row["Date"] = jsonObject[cnt].DateFromXML;

cnt++;
return (true);
}

return (false);</pre>
Preview of my dataset is correct.
 
 
Integrating your code:
As for climbing stairs, best way is up step by step. :P
I decide then to test first that I can read content from the url.
I commented all the script lines for the data set. 
I create another parameter called jsonurl.
Use your code and just modified the last line:
<pre class="_prettyXprint">
params["jsonurl"]=response.toString();</pre>
Then display the parameters on the layout.
One step more cause the parameter include the JSON from the url. Champagne were exit from the fridge ready to be open and drink :rolleyes: ...
 
Last step:
 
Uncomment my dataset script to have same script then initial state.
 
Modify the code on initialize by addind this simple line:
 
jsonObject = eval( '(' + params["jsonurl"].value + ')' ) ;
Dataset preview returns 0 line... :huh:
Preview of the report:
The parameters is correctly completed but I have the following error message:
 
<div>
<div>
<div>
<pre class="_prettyXprint">
"Java class "java.lang.Object" has no public instance field or method named "date". (/report/method[@name="initialize"]#18(eval)#1) "</pre>
</div>
 
</div>
</div>
I suspected than may the "date" word on Json data create conflict. I just modified the code by:
<pre class="_prettyXprint">
params["jsonurl"]=response.toString().replace("date","etrange");</pre>
Same error. Champagne gone back to the fridge

I will try other tests on my side. If you have any idea, let me know.
 
 
 
</div>

kclark · September 12, 2014

Thanks for the update, can you post your rptdesign? In the meantime, one of my colleagues emailed me this JSON example that might be helpful. It doesn't use an additional jars but dose include on JS file.

Martial Wilbert · September 16, 2014

Hi Kristopher,
Sorry for the delay, was on my best friend wedding. You know now why I can't think JSON yesterday

I had a look on your collegue report specially the URLFetch. Sorry, but I don't understand how did you link the report to the js file. 
Sorry about that ...
Martial

Clement Wong · September 16, 2014

The JavaScript library is linked via the Report's Properties > Resources > JavaScript Files.
 
For the URLFetch example, there is one file listed under JavaScript Files -- json.js.

Martial Wilbert · September 17, 2014

Well,
I just tried to run the URLfetch example but it does not run properly.
May I make something wrong or my BIRT Designer is not properly configured:
 
 
<div>The following items have errors:</div>
 
<div>
<div>ReportDesign (id = 1):</div>
<div>+ There are errors evaluating script "importPackage(Packages.java.io); 
importPackage(Packages.java.net); 
var inStream = new URL("<a data-ipb='nomediaparse' href='http://www.google.com/calendar/feeds/developer-calendar@google.com/public/full?alt=json&orderby=starttime&max-results=15&singleevents=true&sortorder=ascending&futureevents=true'>http://www.google.com/calendar/feeds/developer-calendar@google.com/public/full?alt=json&orderby=starttime&max-results=15&singleevents=true&sortorder=ascending&futureevents=true").openStream();</a> 
var inStreamReader = new InputStreamReader(inStream); 
var bufferedReader = new BufferedReader(inStreamReader); 
var line; 
var result = ""; 
while ((line = bufferedReader.readLine()) != null) 
result += line; 
inStream.close(); 
var json = JSON.parse(result); 
vars["HTMLJSON"] = json; 
": 
Wrapped java.net.ConnectException: Connection refused: connect (/report/method[@name="beforeFactory"]#4) birt.core.JavascriptCommonError ( 1 time(s) ) 
detail : org.eclipse.birt.report.engine.api.EngineException: There are errors evaluating script "importPackage(Packages.java.io); 
importPackage(Packages.java.net);</div>
<div> 
</div>
</div>

Clement Wong · September 17, 2014

It looks like the sample Google JSON URL doesn't provide the data in the format when the sample was first created.
 
To fix this, in the beforeFactory event, change the URL and remove the "&futureevents=true" option.
 
From:
<pre class="_prettyXprint">
var inStream = new URL("http://www.google.com/calendar/feeds/developer-calendar@google.com/public/full?alt=json&orderby=starttime&max-results=15&singleevents=true&sortorder=ascending&futureevents=true").openStream();</pre>


To:
<pre class="_prettyXprint">
var inStream = new URL("http://www.google.com/calendar/feeds/developer-calendar@google.com/public/full?alt=json&orderby=starttime&max-results=15&singleevents=true&sortorder=ascending").openStream();</pre>

Martial Wilbert · September 18, 2014

Clement,
Still have an error. I'm not sure that it's link to the JSON format. It's seems to me like the java is not really executed. 
I supposed that the report works on your side ? May I have a problem on my BIRT designer configuration ? 
Then I tried to load your report on an apache server where I'm sure that the configuration is correct. But still the error messages... 
 
 :blink: :wacko:

Martial Wilbert · September 18, 2014

ClÃ©ment,
Finally your report with the change of the Sample google URL working fine.
I discover that my lovely company network have some filter on the firewall that prevent it to run. Outside this network, your report runs.
 
Regards,
 
Martial

Martial Wilbert · October 28, 2014

Hi all,
Just to give you a last news. 
I use the Clement source code, adapt it to my JSON flow, managing the proxy settings and everything working well. 
Then, we have a solution to use JSON data

Thanks for your help.
 
Martial

malaythecool · September 22, 2015

hi martial i have same problem and new to BIRT
 
can you help me out with your code?

Martial Wilbert · September 22, 2015

Hi malythecool, 
How can I help you ?
Martial

malaythecool · September 23, 2015

hi martial
 
thanks for your reply
 
i am using api to get json data like localhost:8080/REST/ABCAPI/name from Rest call in application
 
i get data likethis format [{"entityId":"474","RegCategory":"M","Regno":"ex"}]
 
so i want to ask which is bestway to consume that JSON format data
 
as i am new i am not able to do more things i tried via JDBC and worked well from calling it via JAVA class
 
than same i want to do via REST
 
 
give your sugggestion
 
Thanks in advance
 
Malay

Martial Wilbert · September 23, 2015

Malay, 
Use the json.zip file provided by Kristopher one year ago. Inside it, you have a rptdesign called URLFetch.rptdesign. 
On the BIRT Designer Outline, have a look on the scripts part.
 
I'm starting from this rptdesign to design my rptdesign. 
Of course you need to adapt it according your JSON file flow. 
Relating my proxy problem, I have to add the following line code on the beforeFactory part:
 
<div>
<pre class="_prettyXprint">
Packages.java.lang.System.setProperty("http.proxyHost", "yourproxyurlwithouthttpprefix");
Packages.java.lang.System.setProperty("http.proxyPort", "yourproxyport");</pre>
</div>
<div>Hope it's helping as a starting point. If you already tried something, please send us the problem you met.</div>
<div> </div>
<div>Martial</div>

kclark · September 23, 2015

<blockquote class="ipsBlockquote" data-author="wilbemar" data-cid="139205" data-time="1443011384">
<div>
 
Malay, 
Use the json.zip file provided by Kristopher one year ago. Inside it, you have a rptdesign called URLFetch.rptdesign. 
On the BIRT Designer Outline, have a look on the scripts part.
 
I'm starting from this rptdesign to design my rptdesign. 
Of course you need to adapt it according your JSON file flow. 
Relating my proxy problem, I have to add the following line code on the beforeFactory part:
 
<div>
<pre class="_prettyXprint">
Packages.java.lang.System.setProperty("http.proxyHost", "yourproxyurlwithouthttpprefix");
Packages.java.lang.System.setProperty("http.proxyPort", "yourproxyport");</pre>
</div>
<div>Hope it's helping as a starting point. If you already tried something, please send us the problem you met.</div>
<div> </div>
<div>Martial</div>
 
</div>
</blockquote>
 
It's been a while since I've looked at that example and I'm sure it could use a face lift. But wilbemar is right, you'll need to work the sample around your JSON structure. For what it's worth, if you don't want to use JQuery or pure JavaScript then you should be able to import 3rd party libraries and use them in your report (e.g. Gson, Jackson, etc)

malaythecool · September 27, 2015

hey guys,
 
thanks for your responses.
I had done something meaningful from my json data to birt report.
 
Best Regards,
Malay

Nithyasree Selvakumar · February 6, 2016

rafex · May 10, 2017

Hi, i am starting with BIRT and need connect JSON with birt. I using XML before but now need usign a data json, i have a rest service that generate a structure json and need read this strcutured to create a report in birt. PLEASE help me. thant

Clement Wong · May 10, 2017

Have you taken a look at this DevShare example?
<a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/files/file/1128-querying-elasticsearch-with-birt-using-a-scripted-data-set/'>http://developer.actuate.com/community/forum/index.php?/files/file/1128-querying-elasticsearch-with-birt-using-a-scripted-data-set/</a>
 
It demonstrates using a scripted data source / data set to access a REST based source that returns JSON.

rafex · May 10, 2017

Sorry, but i dont undertand this example... please i need any more easy i am starting with eclipse birt i have a versiion 4.6 NEON.
 
This links talk on elasticSearch, i used datasource with XML with out problems but i dnt know how does with json

Clement Wong · May 10, 2017

Open Source BIRT does not have an out of the box REST connection, whereas commercial BIRT (as of iHub 16), it ships with a REST Data Source that allows you to easily retrieve JSON from your REST based data sources.
 
With Open Source BIRT, you have the option to use a scripted data source and data set to connect. The description of the DevShare describes the 3 events that were modified.
 
First, you'll need to identify the REST URL. In the open event of the scripted Data Source of the example, you'll see the REST based data source URL, and the we opening up a connection and retrieving the results in the "result" variable. The scripted data set loops through the JSON results and populated the output columns.
 
Although the DevShare example references ElasticSearch, it can be used for other REST based URL data sources.

open JSON data from an url

Comments

Categories