Thursday, July 14, 2016

How to detect if Jasper Reports output was generated by a scheduled run

Sometimes it is necessary to have some conditional logic in case a report is being run by a scheduler. In this case it is possible to use _ScheduledTime parameter from the built-in REPORT_PARAMETERS_MAP map.

Example expression:

$P{REPORT_PARAMETERS_MAP}.get("_ScheduledTime") == null ? "Normal" : "Scheduled"

Friday, May 20, 2016

Concatenate full name in reverse order with comma and optional title


Sometimes it is preferrable to output full name as a single field/column like
<last_name>, <title> <first_name>
where title and first name are both optional (as occurs in the real world). Because of the different combinations where title and/or first name are null there should be some conditional logic with concatenation. But you can do correct concatenation in a single expression using the fact that coalesce(right(title,0),', ') will return empty string when title is not null and  separator', ' otherwise:

select 
trim(
upper(family_name) || coalesce(', ' || title || ' ', '') || coalesce(coalesce(right(title,0),', ') || given_names, '')
) as person_name 
from person

Thursday, May 19, 2016

Move cursor to specific text in Word VBA




Sometimes it's necessary to replace placeholders (e.g. <content_placeholder1>) in a Word document with some generated or imported content using VBA macro. Just replacing the placeholder text via Selection.Find.Execute Replace:=wdReplaceAll  will not move a cursor, you need to do it as a separate step like below:

Sub moveToPlaceholder(tableName As String)

    Dim placeholderText As String

    placeholderText = "<" & tableName & ">"
    
    ' first find
    With Selection.Find
      .Text = placeholderText
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .Execute
    End With
    
    ' now move cursor at the
    Selection.EndKey Unit:=wdLine
    
    ' now replace
    With Selection.Find
      .Text = placeholderText
      .Replacement.Text = ""
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .Execute Replace:=wdReplaceAll
    End With
  
End Sub

Wednesday, July 16, 2014

ERROR: character with byte sequence <...> in encoding "UTF8" has no equivalent in encoding "WIN1252";

I've encountered this error "ERROR: character with byte sequence <...> in encoding "UTF8" has no equivalent in encoding "WIN1252";" while using MySQL Workbench to migrate data from PostgreSQL to MySQL. I was not sure why this was happening because the target MySQL database had UTF8 encoding and I thought that everything can be mapped to it.

The real root cause of this problem turned out to be the PostgreSQL driver I used to connect to the source DB and specified in the Workbench which was PostgreSQL ODBC Driver (ANSI) and after I changed it to PostgreSQL ODBC Driver (UNICODE) everything worked fine.

Monday, May 26, 2014

How to view .oxps (Open XPS) files on Windows 7

I didn't expect this simple thing to be so tricky, mostly because the utility name is not XpsConverter.exe but OxpsConverter.exe instead.  So here's a solution for you for when you cannot find an XpsConverter.exe:

In order to convert and view .oxps files on Windows 7 you need to use OxpsConverter.exe which should be located in C:\Windows\System32 folder, just try to run C:\Windows\System32\OxpsConverter.exe from command line.

If you don't have it, download and install a Windows update for you version from here:
http://support.microsoft.com/kb/2732059

Using the converter is very simple, just run
C:\Windows\System32\OxpsConverter.exe "file_to_convert.oxps"
and it will open a small GUI asking you where to convert the file to .xps format which can be opened by default in Windows 7.

NOTE: If you don't have then try looking for XpsConverter.exe
in %programfiles%\Windows Kits\8.0\bin\ or %programfiles(86)%\Windows Kits\8.0\bin\
if you happen to have Windows Driver Kit 8 (WDK) installed.

Wednesday, May 22, 2013

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter

I was getting this error from Jasper server when leaving datetime input control blank:


Error Message

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3

Error Trace

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at 

Where clause in the query for java.sql.Timestamp column was like this:
and (time_col > $P{inputStartCreatedDate} or $P{inputStartCreatedDate} is null)

The solution is to add explicit type conversion like this:
and (time_col > $P{inputStartCreatedDate} or $P{inputStartCreatedDate}::timestamp is null)


Thursday, February 21, 2013

Calculation type issue in iReport

I was creating a variable in iReport and requested calculation type didn't work, I was just getting the value of the field. The cause of this turned out to be the wrong variable class for the variable. I.e. java.lang.String instead of java.lang.Double.