update or insert queries in BIRT

Hi all,

can any one tell where can i have update/insert queries in BIRT..

I have a requirement where in i have to fire insert queries to the data base after the report is run.

can i write insert queries in afterClose method..?

please suggest me...

thank you in advance

Find more posts tagged with

Comments

bappoo

Hi - Are you in London by any chance? We will be covering this in detail at the BIRT User Group meeting in March.

If you cant make it then, the simple answer is that you can place these insert/update statements in the same SQL as you use to generate the data set in the first instance.

You can even capture user input with parameters, which can then be used to amend the SQL statements.

Of course this happens before the report is run rather than after, but maybe it will help.

Warm regards

Paul

vijay_kota

Hi Paul,

Thank you for the reply...

my requirement is something like this..

I have to save the report o/p in perticular .doc format.

based on the output i have insert few rows in database...

I am trying to achieve this through beforeClose method.

When i hardcode the query its working fine.

But i am not getting how to insert values dynamically...

depending on the reports...

my query is some thing like this..

myStmt1.setQuery("INSERT INTO MAXIMO.DOCINFO (DOCUMENT, DOCTYPE, URLTYPE, URLNAME, PRINTTHRULINKDFLT, USEDEFAULTFILEPATH, SHOW, DOCINFOID, LANGCODE, HASLD, CONTENTUID) VALUES ('TEST', 'Attachments', 'FILE','D:\\doclinks\\attachments\\"+row["ponum"]+".doc', '0', '0', '1', docinfoseq.nextval, 'EN', '0', docinfoseq.nextval)");

i am not able to pass the value "+row["ponum"]+".

please help

vijay_kota

Hi all this is got resolved..

I just declared a global variable....

and used it in beforeCLose method...That just worked..

Thank you...

Abhi.shah

I am developing something on the same line, but unfortunately it is not working for me even when I hard code the query.
What I am doing is, based on the user selections froms checkboxes I want to insert the selected values into the database and then present the user with the updated selections.

Thanks
Abhi

vijay_kota

Hi Abhi.shah,

Can you post your query here..??'

Regards,

Vijay

Abhi.shah

sure, here it goes:

I use the following query to create my dataset:

"select * from dual;"

because I really want the dataset to just insert into the database.
Then in my beforeClose method of the dataset I say:

this.queryText = "Insert into....";

this does not work.

When I do the same thing in the beforeOpen Method, it does insert data into the database provided there is a table in my report that gets populated using that dataset. But there is a problem with this as well; it gives an error on the table as the query changes at runtime and is not a "select" query anymore.

Thanks
Abhi

vijay_kota

You are using this in maximo..

Because i worked on some thing similar it worked great for me(In beforeFactory)...

this is my peice of code..

myTxn =MXReportTxnProvider.create(this.getDataSource().getName());
myStmt1 = myTxn.createStatement();
myStmt1.setQuery("INSERT INTO...) VALUES ( )");
myTxn.save();

Still i m not sure what exactly you are trying to achieve.

Regards,

Vijay

Abhi.shah

I dont have maximo. I am just using raw BIRT. What I am really trying to do is, get the values from the checkboxes in my report and enter the information into my database using "INSERT INTO".

Thanks
Abhi

vijay_kota

can you attah your reportdesign here..??

Abhi.shah

I am sorry, as much as I would like to, I cant as I will have to have approvals for that. But as I said, all I have to do is take the user input from the checkboxes and inser the data into the database.

ABhi

vijay_kota

fine...

can you store that user checkbox value in some variable.

And pass that varible in your select query statement..??

Just a thought...

Abhi.shah

yes I definately can, but as I said that data gets inserted into the dataset and gets read by the other items in the report.

micajblock

<blockquote class='ipsBlockquote' data-author="'Abhi.shah'" data-cid="107149" data-time="1342104862" data-date="12 July 2012 - 07:54 AM"><p>
yes I definately can, but as I said that data gets inserted into the dataset and gets read by the other items in the report.<br /></p></blockquote>
Can you explain WHY you are inserting data in the database? Can't you use variables to be read by other items in the report?

Tarangini

hi,
i am able to insert records into db from birt but not able to fetch the same records in maximo....

please help me...

Thank u in advance...

micajblock

<blockquote class='ipsBlockquote' data-author="'Tarangini'" data-cid="108356" data-time="1344494395" data-date="08 August 2012 - 11:39 PM"><p>
i am able to insert records into db from birt but not able to fetch the same records in maximo....<br /></p></blockquote>
Please provide more details. How are you inserting? How are you fetching? You might want to open a new topic, as this topic was not using Maximo.

Tarangini

I have a requirement where i need to fire insert statement after the report is run.

i have written dis in beforeClose();
myTxn =MXReportTxnProvider.create(this.getDataSource().getName());
myStmt1 = myTxn.createStatement();
myStmt1.setQuery("INSERT INTO...) VALUES ( )");
myTxn.save();

wen i am running report in browser view/preview its inserting data properly...and the same o/p i am exporting to pdf or doc also its inserting data again ....

micajblock

beforeClose of what? How and where are you trying to display those records?

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="bappoo" data-cid="73273" data-time="1297633701">
<div>
<p>Hi - Are you in London by any chance? We will be covering this in detail at the BIRT User Group meeting in March. If you cant make it then, the simple answer is that you can place these insert/update statements in the same SQL as you use to generate the data set in the first instance. You can even capture user input with parameters, which can then be used to amend the SQL statements. Of course this happens before the report is run rather than after, but maybe it will help. Warm regards Paul</p>
</div>
</blockquote>
<p>Hi Paul ,</p>
<p>i followed you the record is inserted when i comment out the select Query in second line.But when i uncomment, it does shows only the old records which were inserted by first statement. also i get two rows inserted(i.e. 2 u's) with your Insert statement.</p>
<p>i am using this </p>
<p>this.queryText = "Insert into REPORTTRACKINGTABLE (VOLUMENAME) VALUES ('u') " ;</p>
<div>this.queryText = " select * from REPORTTRACKINGTABLE ";</div>
<div> </div>
<div>i had to remove + from second line becuse then it means insert + select.</div>
<div>Lastly i can not see any output with Packages.java.lang.System.out.println("Insert stmt is :" + this.queryText);</div>
<div>(is my console shut off)</div>
<div>Please reply</div>

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="Abhi.shah" data-cid="107138" data-time="1342099323">
<div>
<p>sure, here it goes: I use the following query to create my dataset: "select * from dual;" because I really want the dataset to just insert into the database. Then in my beforeClose method of the dataset I say: this.queryText = "Insert into...."; this does not work. When I do the same thing in the beforeOpen Method, it does insert data into the database provided there is a table in my report that gets populated using that dataset. But there is a problem with this as well; it gives an error on the table as the query changes at runtime and is not a "select" query anymore. Thanks Abhi</p>
</div>
</blockquote>
<p>U are right.</p>

yoovrajkhullar