Friday, June 20, 2008

Integrating Google Maps

Following up on a discussion on the APEX OTN forum today, here's some additional information on integrating Google Maps. There are different techniques for integrating Google maps, and we actually have a Whitepaper available online, that explains working with Google maps in much detail:

http://www.oracle.com/technology/products/database/application_express/html/doc.html

This Whitepaper explains how to do the geo-coding, i.e. converting an address (Street/City/State/ZIP/Country) into coordinates that can be used to position a Google map. In this example, the PL/SQL package utl_http is used to connect to Google's geocoding service.

An alternative to this approach is to connect to that service from the client. Both techniques have their advantages and disadvantages. One of the advantages of doing it on the client is that the server doesn't need to be able to connect to Google, i.e. there's no need to define any proxies on the server, and as a result, this approach would work on apex.oracle.com, where external network connections are currently not supported.


The first step for any kind of Google map integration is to register for a Google maps key, which can be done here:

http://code.google.com/apis/maps/

After that, it's just a matter of adding some Java Script to your page that communicates with Google and refreshes the area on your APEX page that you have allocated for the map. Here's a link to my sample page:

http://apex.oracle.com/pls/otn/f?p=52790

You can download this application here:

http://www.sewtz.com/GoogleMaps.zip

I added an address region, where you can enter a city or a full address and then click go and have the map move to the address specified. Here's the JavaScript I added to my page. It's pointing the map to my office on first load, and then re-positions the map based on the address you enter:



var geocoder;
var map;
var bounds = new GLatLngBounds();

var myStreet = "540 Madison Ave";
var myCity = "New York";
var myState = "NY";
var myZIP = "10022";
var myCountry = "USA";

var address = myStreet + "," + myCity + "," + myState + "," + myZIP + "," + myCountry;
var addressMarker = myStreet + "<br />" + myCity + ", " + myState + ", " + myZIP + "<br />" + myCountry;

// On page load, call this function

function load()
{
// Create new map object
map = new GMap2(document.getElementById("map"));

// Create new geocoding object
geocoder = new GClientGeocoder();

// Retrieve location information, pass it to addToMap()
geocoder.getLocations(address, addToMap);

}

// This function adds the point to the map

function addToMap(response)
{
// Retrieve the object
place = response.Placemark[0];

// Retrieve the latitude and longitude
point = new GLatLng(place.Point.coordinates[1],
place.Point.coordinates[0]);

map.addControl(new GSmallMapControl());
map.addControl(new GMapTypeControl());

// Center the map on this point
bounds.extend(point);
map.setCenter(point, 13);
map.setZoom(14);

// Create our "tiny" marker icon
var oIcon = new GIcon(G_DEFAULT_ICON);
oIcon.image = "#WORKSPACE_IMAGES#opin.png";
oIcon.iconSize = new GSize(32, 40);
oIcon.shadowSize = new GSize(38, 46);

// Set up our GMarkerOptions object
markerOptions = { icon:oIcon };

marker = new GMarker(point, markerOptions);

// Add the marker to map
map.addOverlay(marker);

// Add address information to marker
marker.openInfoWindowHtml(addressMarker);
}

function updateMap() {
myStreet = $v('P1_STREET');
myCity = $v('P1_CITY');
myState = $v('P1_STATE');
myZIP = $v('P1_ZIP');
myCountry = $v('P1_COUNTRY');

address = myStreet + "," + myCity + "," + myState + "," + myZIP + "," + myCountry;
addressMarker = myStreet + "<br />" + myCity + ", " + myState + ", " + myZIP + "<br />" + myCountry;

load();
}

Wednesday, June 18, 2008

Verifying that the PDF print server works

Here's another posting on PDF printing in Application Express. Sometimes it's just a bit difficult to figure out what exactly is wrong if a report doesn't print properly. Sometimes all a customer gets in an empty / zero-length PDF file and no other indication on where it failed. A good place to look at are the log files of your OC4J server or J2EE container. Often BI Publisher or Apache FOP leave some clues there. But at other times there's just nothing helpful to find in these logs. And this could be due to your APEX instance not getting through to your print server which could be caused by not having configured the print server settings properly in Application Express.

One way to make sure your print server is up and running and configured properly is setting up a static HTML form that simulates what APEX is doing internally, i.e posting some XML data along with an XSL-FO stylesheet to a print server via HTTP and receiving back a PDF document as the response. If this works properly, you can at least be sure that your print server works. So then the next step would be to double-check the print server settings in APEX, make sure that network services are enabled when running 11g and ensure that you can reach your print server from your database server.

Here’s some HTML code that can be used to simulate or test your print server. Just replace the [host] and [port] values in the form tag with your actual values and then load the file into your browser and give it a try using e.g. the sample XML data and sample XSL-FO stylesheet below (just copy and paste in your form). This assumes you’re using Apache FOP, when using BI Publisher, also replace /fop/apex_fop.jsp with /xmlpserver/convert.

Static HTML form to test print server:
<html>
<body>
<form action="http://[host]:[port]/fop/apex_fop.jsp" method="post" name="foptest">
<textarea name="xml" cols="80" rows="10">
</textarea><br />
<textarea name="template" cols="80" rows="10">
</textarea><br />
<input type="test" name="_xtype" value="xsl-fo" /><br />
<input type="test" name="_xf" value="pdf" /><br />
<input type="submit" />
<form>
</body>
</html>

Sample XML Data:
<?xml version="1.0" encoding="UTF-8"?>
<ROWSET>
<ROW>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Doe</LAST_NAME>
</ROW>
<ROW>
<FIRST_NAME>Jane</FIRST_NAME>
<LAST_NAME>Doe</LAST_NAME>
</ROW>
</ROWSET>

Sample XSL-FO style sheet:
<?xml version = '1.0' encoding = 'utf-8'?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xlink="http://www.w3.org/1999/xlink">
<xsl:template match="/">
<fo:root>
<fo:layout-master-set>
<fo:simple-page-master master-name="master0" margin-left="84.6pt" margin-right="84.6pt" page-height="792.0pt" page-width="612.0pt" margin-top="36.0pt" margin-bottom="36.0pt">
<fo:region-before region-name="region-header" extent="36.0pt"/>
<fo:region-body region-name="region-body" margin-top="36.0pt" margin-bottom="36.0pt"/>
<fo:region-after region-name="region-footer" extent="36.0pt" display-align="after"/>
</fo:simple-page-master>
</fo:layout-master-set>
<fo:page-sequence master-reference="master0">
<fo:title>Report</fo:title>
<fo:static-content flow-name="region-header"/>
<fo:static-content flow-name="region-footer"/>
<fo:flow flow-name="region-body">
<fo:block padding-bottom="0.25pt" padding-top="0.25pt">
<fo:table start-indent="0.0pt" xdofo:table-summary="Template Table 1" xdofo:row-header-count="0">
<fo:table-column column-width="147.6pt"/>
<fo:table-column column-width="147.6pt"/>
<fo:table-header>
<fo:table-row keep-with-next="always">
<fo:table-cell padding-start="5.15pt" vertical-align="top" border-bottom="0.5pt solid #000000" border-end-color="#000000" padding-top="0.0pt" border-end-style="solid" border-start-color="#000000" padding-end="5.15pt" number-columns-spanned="1" border-top="0.5pt solid #000000" border-start-style="solid" height="0.0pt" border-end-width="0.5pt" padding-bottom="0.0pt" border-start-width="0.5pt" background-color="#ff4040">
<fo:block orphans="2" widows="2" linefeed-treatment="preserve" start-indent="0.0pt" text-align="start" padding-bottom="0.0pt" end-indent="0.0pt" padding-top="0.0pt">
<fo:inline height="12.0pt" font-family="Arial" white-space-collapse="false" font-size="12.0pt" font-weight="bold">First Name</fo:inline>
</fo:block>
</fo:table-cell>
<fo:table-cell padding-start="5.15pt" vertical-align="top" border-bottom="0.5pt solid #000000" border-end-color="#000000" padding-top="0.0pt" border-end-style="solid" border-start-color="#000000" padding-end="5.15pt" number-columns-spanned="1" border-top="0.5pt solid #000000" border-start-style="solid" height="0.0pt" border-end-width="0.5pt" padding-bottom="0.0pt" border-start-width="0.5pt" background-color="#ff4040">
<fo:block orphans="2" widows="2" linefeed-treatment="preserve" start-indent="0.0pt" text-align="start" padding-bottom="0.0pt" end-indent="0.0pt" padding-top="0.0pt">
<fo:inline height="12.0pt" font-family="Arial" white-space-collapse="false" font-size="12.0pt" font-weight="bold">Last Name</fo:inline>
</fo:block>
</fo:table-cell>
</fo:table-row>
</fo:table-header>
<fo:table-body>
<xsl:for-each select=".//ROW" xdofo:ctx="3">
<fo:table-row xdofo:repeat="R">
<fo:table-cell padding-start="5.15pt" vertical-align="top" border-bottom="0.5pt solid #000000" border-end-color="#000000" padding-top="0.0pt" border-end-style="solid" border-start-color="#000000" padding-end="5.15pt" number-columns-spanned="1" border-top="0.5pt solid #000000" border-start-style="solid" height="0.0pt" border-end-width="0.5pt" padding-bottom="0.0pt" border-start-width="0.5pt">
<fo:block xdofo:xliff-note="F , FIRST_NAME" orphans="2" widows="2" linefeed-treatment="preserve" start-indent="0.0pt" text-align="start" padding-bottom="0.0pt" end-indent="0.0pt" padding-top="0.0pt" height="0pt">
<fo:inline height="12.0pt" font-family="Arial" white-space-collapse="false" font-size="12.0pt">
<xsl:value-of select=".//FIRST_NAME" xdofo:field-name="FIRST_NAME"/>
</fo:inline>
</fo:block>
</fo:table-cell>
<fo:table-cell padding-start="5.15pt" vertical-align="top" border-bottom="0.5pt solid #000000" border-end-color="#000000" padding-top="0.0pt" border-end-style="solid" border-start-color="#000000" padding-end="5.15pt" number-columns-spanned="1" border-top="0.5pt solid #000000" border-start-style="solid" height="0.0pt" border-end-width="0.5pt" padding-bottom="0.0pt" border-start-width="0.5pt">
<fo:block xdofo:xliff-note="LAST_NAME" orphans="2" widows="2" linefeed-treatment="preserve" start-indent="0.0pt" text-align="start" padding-bottom="0.0pt" end-indent="0.0pt" padding-top="0.0pt" height="0.0pt">
<fo:inline height="12.0pt" font-family="Arial" white-space-collapse="false" font-size="12.0pt">
<xsl:value-of select=".//LAST_NAME" xdofo:field-name="LAST_NAME"/>
</fo:inline>
</fo:block>
</fo:table-cell>
</fo:table-row>
</xsl:for-each>
</fo:table-body>
</fo:table>
</fo:block>
</fo:flow>
</fo:page-sequence>
</fo:root>
</xsl:template>
</xsl:stylesheet>

Friday, June 13, 2008

Using Dynamic Images in PDF Reports

One question about the PDF printing feature in APEX keeps popping up: how to include images in a PDF report. For static images, that's pretty straightforward. Just place them into your RTF layout using MS Word and BI Publisher Desktop. For dynamic images, that's a little more complicated, so here's how to do that:

When talking about dynamic images, what I mean are images that are stored in BLOB columns in database tables, like for example product images. To include those images in a PDF report, two things are needed: the image needs to be included in the generated XML representation of the report data and the RTF report layout needs to include instructions on what to do with the image information stored in the XML data. To get the BLOB values to be included in the XML data, they need to be base64 encoded. And in the RTF report layout an XSL-FO expression can be used to reference the image:

<fo:instream-foreign-object content-type="image/jpg">
<xsl:value-of select="IMAGE_ELEMENT"/>
</fo:instream-foreign-object>

To enter such a XSL-FO, edit your RTF layout in Word and then use the field-browser of the BI Publisher plug-in to edit the report column attributes.


I setup a sample application that illustrates how this works on apex.oracle.com, you can try it out here:

http://apex.oracle.com/pls/otn/f?p=50930:1 (logon as demo/demo123)

You can also download this application to try it out on your local instance or your own apex.oracle.com workspace. The download is available here:

Download PDF Image Demo Application

The application comes packaged with a table containing a BLOB column and a PL/SQL function that encodes the BLOB to base64. A sample RTF layout with the required XSL-FO snipped for the image transformation is also included. Once the application is installed in your own workspace, just upload a few images and try it out. One issue, which we'll address in the next version of Application Express, is the 32k limit on report columns. For this scenario, this means that only fairly small images are currently supported when using this technique with report queries or report regions. If the XML data is generated by some other way, and the PDF rendering is done using the print API, then the use of larger images would be possible as well.

Wednesday, June 11, 2008

Austrian Competence Center for Oracle APEX

Last week I was invited to be the keynote speaker at the opening event of the Austrian Competence Center for Oracle APEX in Vienna. The competence center is run by Sphinx IT Consulting and managed by Patrick Wolf, who’s well known in the APEX community and who’s the APEX Developer of the year 2007. With this effort Sphinx IT Consulting intends to promote and raise awareness for Oracle APEX and starting in August, they will be conducting APEX events and workshops on a regular basis.

Marc Sewtz, Patrick Wolf, Ingrid Kriegl and Carsten Czarski

More information about the Austrian Competence Center for Oracle APEX can be found at:

http://www.sphinx.at/it-consulting/de/home/