iReport – Removing Carriage Returns and Newlines With the replace() Funtion

iReport — Jeff Eske on November 28, 2012 at 4:11 pm

For some time, I’ve been trying to save information out of an MS SQL database that’s going away.  I’m wanting the data in a .csv file and the problem that I’ve been having is that a couple of the columns that I want contain data that includes carriage returns and newline characters within the column.  When the data is saved out, those returns look like the end of a record, essentially breaking every record containing them.  This also makes the data virtually useless for importing into other databases, since the records are broken, column counts end up off, etc.

What ended up solving the problem for me was the replace() function within iReport.  Since I was only concerned with the content and not necessarily the formatting, I used replace() to take out the returns and replace them with a single space.  One additional thing that you’ll notice is that the data is on an old version of MS SQL, and after considerable trial-and-error, I found that I had to actually use cast() function as well.

The complete syntax that I used:

[code]

CAST(REPLACE(CAST(Column_Name as NVarchar(4000)),CHAR(13)+char(10),’ ‘) AS NText) as Some_Name

[/code]

Another issue that I thought I had run into was a problem with some columns only having a newline, not both.  Well, I found out that I can nest the first replace() function and use the first output as the input for the next, so that I could actually make another pass through the data to catch the individual newlines the second time around.  THAT setup starts to look really messy, due needing the cast() function as well…

The syntax for nesting cast() and replace() functions:

[code]

CAST(REPLACE(CAST(CAST(REPLACE(CAST(Column_Name as NVarchar(4000)),CHAR(13)+char(10),’ ‘) AS NText) as NVarchar(4000)),char(10),’ ‘) AS NText) as Some_Name

[/code]

This solved part of my problem.  Within my iReport report, the columns looked right and complete.  At that point, I was ready to save everything out as a .csv file.  That ended up being a minor problem also.

Jeff

iReport – Plain Text Passwords

iReport — Jeff Eske on October 29, 2012 at 3:06 pm

Everyone already (should) know that iReport will save your connection passwords in plain-text.  Depending on your level of paranoia, this may or may not be an issue.  If you’re not paranoid and you save the connection passwords, it can come in handy.  I needed to setup connections on a second computer and didn’t have the connection passwords.  Everything was already setup on my other machine, so I was able to actually just go in and find the password that I couldn’t remember and just copy/paste it into my connection on the other machine.  Voila! Problem solved.

If you need to find the connection password(s), they’re saved in a file named ireport.properties in each user’s own .iReport folder.  In Windows 7, my ireport.properties file was at: C:\Users\my user profile\.ireport\4.x.x\config\Preferences\com\jaspersoft\ireport.properties.  I simply opened it in a text editor and searched for password.  I then copied and pasted it in to my new connection.

Jeff Eske

iReport – Classpath and Importing Settings

iReport — Jeff Eske on October 22, 2012 at 2:47 pm

I found that you need to be careful when updating iReport.  When you install, it picks a new folder by default.  This is kind of nice in that you can have multiple copies of iReport on your machine.  When you open iReport, it will even let you know that if found previous installs and volunteers to import the setting for you.

Well, importing settings sounds good, but you need to be careful.  I went ahead and gave it a try, just out of curiosity.  I have a few extra .jar files that I’ve added, to enable access to various databases.  I covered how to get the jtdc jar file added to the classpath in a previous post.  I wanted to see how importing settings affected those .jar files.  I was curious if importing settings only imported settings, or if it would actually copy over the jar files, etc.  Well, it simply copies over the classpath information, not the resources that are pointed to in the classpath.

I assume that if you create a generic iReport directory and always install new version there, overwriting the previous install, you won’t encounter any issues.  If instead, you create a new folder for each version, you need to be careful about your classpath references.  You can end up with classpath references to previous iReport folders and if you rename/remove those iReport folders, you then break your current install.

Something to think about.

Jeff Eske

iReport – Adding MS SQL driver to iReport

iReport — Jeff Eske on July 27, 2011 at 9:06 am

I’m in the process of creating reports for FrontRange ITSM, here at work.  To this point, I’ve been developing charts with PHP/GD.  I found a very good PHP charting class out there ( http://www.pchart.net ) but am going to try something more suited to reports.  I’m giving Jaspersoft iReport a try.   One problem – by default, iReport doesn’t ship with a driver that allows me to connect to the Microsoft SQL (MS SQL) server that I need to connect to.  In addition, the documentation that I could find sucked.

It actually turned out to be pretty simple to add support for MS SQL.  I went out to the JTDS site (http://jtds.sourceforge.net/ and downloaded the zip file with the necessary jar file in it.  I then copied jtds-xxx.jar in the the libs folder in iReport.  On my machine, that folder was C:Program FilesJaspersoftiReport-4.0.2ireportlibs.  At that point, I had the jar file in the right place, but iReport didn’t know about it.  Once you’ve copied the jar file to the correct place, you need to start iReport and go to Tools > Options > iReport > Classpath and click on “Add JAR”.  Navigate to the jar file that you just added and click “Open”.  OK out of all of that and restart iReport.  At that point, when you go in to add a connection, that driver should show up.

UPDATE 10/24/2012 – You’ll need to be careful when upgrading iReport also.

Jeff Eske

iReport – Jaspersoft iReport and MS SQL

iReport — Jeff Eske on July 27, 2011 at 8:53 am

I’m in the process of creating reports for FrontRange ITSM, here at work.  To this point, I’ve been developing charts with PHP/GD.  I found a very good PHP charting class out there ( http://www.pchart.net ) but am going to try something more suited to reports.  I’m giving Jaspersoft iReport a try.   One problem – by default, iReport doesn’t ship with a driver that allows me to connect to the Microsoft SQL (MS SQL) server that I need to connect to.  In addition, the documentation that I could find sucked.

It actually turned out to be pretty simple to add support for MS SQL.  I went out to the JTDS site (http://jtds.sourceforge.net/ and downloaded the zip file with the necessary jar file in it.  I then copied jtds-xxx.jar in the the libs folder in iReport.  On my Windows machine, that folder was C:\Program Files\Jaspersoft\iReport-4.0.2\ireport\libs.  On my linux machine, I left the iReport folder in my home folder, so the location was /home/my_home_folder/iReport/ireport/libs.  At that point, I had the jar file in the right place, but iReport didn’t know about it.  Once you’ve copied the jar file to the correct place, you need to start iReport and go to Tools > Options > iReport > Classpath and click on “Add JAR”.  Navigate to the jar file that you just added and click “Open”.  OK out of all of that and restart iReport.  At that point, when you go in to add a connection, that driver should show up.

UPDATE 10/24/2012 – You’ll need to be careful when upgrading iReport also.

Jeff Eske

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License. | Jeff's Blog