nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Inserting into tables from report but it's only inserting one row

amw2326

I am working on a report that reads by line from a text file and parses it for data, and uses that data to insert a new row into a table. the parsing/query set up takes place in a while loop. The hope is that I would set up a correct SQL query using the data from the file, and then run the sql query using maximoDataSet.setQuery(). I tried doing this in the maximoDataSet open script, but it only inserts the last line of the file I am working with. I tried moving it to the fetch statement, but it doesn't insert anything from there. I also considered inserting the data as a flat file source, but I couldn't figure out how to access each row of the data from the onFetch script. Any thoughts or suggestions? Thanks!

Find more posts tagged with

Comments

micajblock

Why are you using a report to bulk load data? Not really the best use of BIRT. A simple Java program would be better. An ETL tool would be best.

amw2326

Thank you for your quick response.

I understand that this is not really the purpose of BIRT, but this is what the customers actuate reports did and this is what they want me to convert the reports in BIRT to do. Any help would be appreciated.

wwilliams

Have you enabled logging and is it the first or last row that gets inserted? in the initialize method (from IBM Reporting manual)mxReportScriptContext.setDefaultLogLevel("DEBUG");
mxReportScriptContext.setDefaultLogFile("c:/temp/My.log")
scriptLogger = mxReportScriptContext.getReportScriptLogger(); then in specific methodssectionsscriptLogger.debug(">>> SQL " + whatever you want to see in log);

amw2326

Hey there! Thanks for the advice. I didn't use logging, but I was writing the sql queries that I created to a file to make sure that as I looped through the flat file it was reading each line and parsing it correctly. The file has all of the sql queries it is supposed to have, but the report only inserts the last item when this is done in the open script. From the fetch script nothing gets inserted. I will enable logging and make sure I'm not missing something.

micajblock

Maybe you need to issue a commit after each row. I am not sure how the Maximo classes does updates. This is an example of SQL Write:<a data-ipb='nomediaparse' href='http://developer.actuate.com/community/devshare/_/designing-birt-reports/717-birt-report-with-database-writeback'>http://developer.actuate.com/community/devshare/_/designing-birt-reports/717-birt-report-with-database-writeback</a> and this is an example of writing SQL in code (not sure if you can use this as you would need to bundle a JDBC driver with Maximo)<a data-ipb='nomediaparse' href='http://developer.actuate.com/community/devshare/_/designing-birt-reports/1495-dynamic-content-from-library'>http://developer.actuate.com/community/devshare/_/designing-birt-reports/1495-dynamic-content-from-library</a>

amw2326

Hi! Both of those examples have been removed, unfortunately. I did try adding commit after I used setQuery, but it didn't fix anything. I think that the report does everything (loops etc) and then at the end it runs the setQuery() command, which is what is causing it to only run the last one. I'm trying to figure out how to place it where it will run every row.

amw2326

Just FYI, I figured out how to do it. I moved the actual sql statement to the before close script, and it worked (along with a few other minor changes).

wwilliams

Excellent, just remember that Maximo runs the report twice if you choose to export to another format from the viewer.