Code formatting - Wish the blogger would stop being helpful

Nothing to see here folks.

Just testing some code formatting. The blogger "preview" option is not always accurate. So this is a test page, if you will.

Test

...Read More

How the heck do you use the FedEx Rate web service?

UPDATE The original code sample uses version 2 of the WSDL.

September 24, 2008: Updated to include a rough example for WSDL version 4

October 19, 2009: Updated to include a rough example for WSDL version 7

The code samples work as of those dates. But no guarantees Fedex will not make further changes. Also, given the complexity of this webservice, consider looking into the old fashioned SOAP/CFHTTP method. See CFFedexRates for a base example. Be sure to check the open Issues List as well. There may be newer versions available (as attachments).

I admit it. I do not get web services. Creating the web service equivalent of a "Hello World" project on my computer was one thing. Trying to figure out how to consume an external web service is a completely different story.

Next stop: Web Service Hell

So I took the plunge and decided to try and figure out how to consume the FedEx Rate Web Service. On what feels like day 212 I finally got it working, somewhat ;)

Three things you will need for your journey..

1. My first step was to register for a developer account

2. Armed with my new account information, I downloaded the "Rate Web Service" WDSL. Looked at it and thought "Gee ...that is great. Now what do I do with it?"

3. So I googled a bit and figured out I could put the wdsl file in a web folder and then try and call it as a webservice from a test .cfm page. On my computer I placed it in this directory

[directory] c:\cfusionmx7\wwwroot\fedex\RateService_v2.wsdl
[url path] http://127.0.0.1:8501/fedex/RateService_v2.wsdl

Abandon hope, all ye who enter here

Before getting started, I checked the code samples section. There were no ColdFusion samples .. of course. So I downloaded the java code samples instead. Let me just say.. thank god. If not for the java samples I would still be struggling with my favorite error message ..and muttering it in my sleep.

Web service operation "getRate" with parameters {PAYMENT={{PAYMENTTYPE={SENDER}}}, .... could not be found.

Paradiso

After much trial and error my test page is finally working. I ended up using createObject() to create the webservice, because of errors when using cfinvoke. I suspect it might be due to the optional parameters. Anyway, the code is rough, but its a start. Sorry in advance for the terrible code formatting. Figuring out the blog formatting is next on my list (..sure, sure ;)

As always comments/suggestions/corrections are welcome.

WSDL Version 2 Code

<!--- setting used later on in the code to set shipment details --->
<cfset PassRateRequestPackageSummary = true />

<!--- java objects used for paramters later on in the code --->
<cfset objPosInteger = createObject("java", "org.apache.axis.types.PositiveInteger") />
<cfset objNonNegInteger = createObject("java", "org.apache.axis.types.NonNegativeInteger") />

<!---
Populate a structure with all rate request settings
--->
<cfset data = structNew() />
<cfset data.ClientDetail.AccountNumber="My Test Account" />
<cfset data.ClientDetail.MeterNumber="My Test Meter" />
<cfset data.TransactionDetail.CustomerTransactionId = "Rate Request v2" />
<cfset data.WebAuthenticationDetail.UserCredential.key ="My Developer Test Key" />
<cfset data.WebAuthenticationDetail.UserCredential.password ="My Developer Test Password" />

<cfset data.Version.serviceId = "crs" />
<cfset data.Version.major = "2" />
<cfset data.Version.intermediate =  "0" />
<cfset data.Version.minor =  "0" />
<cfset data.Origin.CountryCode= "US" />
<!--- web service complained if this was not an array --->
<cfset streetArray = arrayNew(1) />
<cfset arrayAppend(streetArray, "Address Line 1") />
<cfset data.Origin.StreetLines = streetArray />
<cfset data.Origin.City = "City Name" />
<cfset data.Origin.StateOrProvinceCode = "TN" />
<cfset data.Origin.PostalCode = "38115" />
<cfset data.Origin.UrbanizationCode = "" />
<cfset data.Origin.Residential = "false" />

<cfset data.Destination = structNew() />
<cfset data.Destination.CountryCode= "US" />
<!--- web service complained if this was not an array --->
<cfset streetArray = arrayNew(1) />
<cfset arrayAppend(streetArray, "Address Line 1") />
<cfset data.Destination.StreetLines= streetArray />
<cfset data.Destination.City= "City Name" />
<cfset data.Destination.StateOrProvinceCode= "QC" />
<cfset data.Destination.PostalCode= "H1E1A1" />
<cfset data.Destination.CountryCode= "CA" />

<cfset data.DropoffType = "REGULAR_PICKUP" />
<cfset data.ServiceType = "INTERNATIONAL_PRIORITY" />
<cfset data.PackagingType = "YOUR_PACKAGING" />

<cfset data.Payment.PaymentType = "SENDER" />
<cfset data.ShipDate = "2007-11-30" />
<cfset RateRequestType = structNew() />
<cfset RateRequestType.VALUE = "ACCOUNT" />
<!--- web service complained if this was not an array --->
<cfset rateRequestArray = ArrayNew(1) />
<cfset arrayAppend(rateRequestArray, "ACCOUNT") />
<cfset data.RateRequestTypes = rateRequestArray />


<!--- Not passing one of these choices will result in "Schema Validation Failure", as one of these objects is required. --->
<cfif PassRateRequestPackageSummary >
<cfset data.RateRequestPackageSummary.TotalInsuredValue.Amount = "100">
<cfset data.RateRequestPackageSummary.TotalInsuredValue.Currency = "USD">
<cfset data.RateRequestPackageSummary.TotalWeight.Value = "20.0">
<cfset data.RateRequestPackageSummary.TotalWeight.Units = "LB">
<!--- could not get this to work without using java objects --->
<cfset data.RateRequestPackageSummary.PieceCount = objPosInteger.init(1) />
<!--- web service complained if this was not an array --->
<cfset specialServiceArray = ArrayNew(1)>
<cfset arrayAppend(specialServiceArray, "APPOINTMENT_DELIVERY")>
<cfset data.RateRequestPackageSummary.SpecialServicesRequested.SpecialServiceTypes = specialServiceArray>
<cfelse>
<cfset data.PackageCount = objNonNegInteger.init(1) >
<!--- web service complained if this was not an array --->
<cfset packagesArray = ArrayNew(1)>
<cfset newPackage = structNew()>
<cfset newPackage.Weight.Value = "10.00">
<cfset newPackage.Weight.Units = "LB">
<cfset newPackage.InsuredValue.Amount = "10.00">
<cfset newPackage.InsuredValue.Currency = "USD">
<!--- could not get this to work without using java objects --->
<cfset newPackage.Dimensions.Length = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Width = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Height = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Units = "IN">
<cfset arrayAppend(packagesArray, newPackage)>
<cfset data.Packages = packagesArray>
</cfif>

<!---
Invoke the webservice

Notes, Using cfinvoke did not work. I think
it may be related to the optional parameters
--->
<cfscript>
ws = createObject("webservice", "http://127.0.0.1:8501/FedEx/RateService_v2.wsdl");
rateReply = ws.getRate(data);
</cfscript>

<!---
Process the response
--->
<cfprocessingdirective suppressWhiteSpace="true" />
<cfoutput>
<h1>Response: #rateReply.getHighestSeverity().toString()#</h1>

<cfif rateReply.getHighestSeverity().toString() is "SUCCESS">
<!--- This is weight, and charge per package --->
<cfset shipment = rateReply.getRatedShipmentDetails() />
<cfloop    from="1" to="#arrayLen(shipment)#" index="i">
<cfset shipmentDetails =  shipment[i].getShipmentRateDetail()>
<cfset packages = shipment[i].getRatedPackages() />
<cfloop from="1" to="#arrayLen(packages)#" index="j">
<cfset packageDetail = packages[j].getPackageRateDetail() />
<cfset surcharge = packageDetail.getSurcharges() />

<!--- display package info --->
Package: #i#<br />
Billing weight: #packageDetail.getBillingWeight().getValue()#
#packageDetail.getBillingWeight().getUnits().toString()#<br />
Base charge:     #packageDetail.getBaseCharge().getAmount()#
#packageDetail.getBaseCharge().getCurrency()#<br />

<!--- display surcharges --->
<cfloop from="1" to="#ArrayLen(surcharge)#" index="k">
#surcharge[k].getSurchargeType().toString()#  <b>surcharge</b>
#surcharge[k].getAmount().getAmount()#
#surcharge[k].getAmount().getCurrency()#<br />
</cfloop>
<hr>

<!--- display net charges --->
Net charge:     #packageDetail.getNetCharge().getAmount()#
#packageDetail.getNetCharge().getCurrency()#
<hr>

<!--- display totals. these values may be null ? --->
<cfset TotalBillingWeight = shipmentDetails.getTotalBillingWeight() />
<cfset TotalSurcharges = shipmentDetails.getTotalSurcharges() />
<cfset TotalNetCharge = shipmentDetails.getTotalNetCharge() />
<cfif IsDefined("totalBillingWeight")>
Total billing weight:     #TotalBillingWeight.getValue()#
#TotalBillingWeight.getUnits().toString()#<br />
</cfif>
<cfif IsDefined("TotalSurcharges")>
Total surcharge: #TotalSurcharges.getAmount()# #TotalSurcharges.getCurrency()#<br />
</cfif>
<cfif IsDefined("TotalNetCharge")>
Total net charge: #TotalNetCharge.getAmount()# #TotalNetCharge.getCurrency()#<br />
</cfif>
<hr>
</cfloop>
</cfloop>
<cfelse>
<cfset Notifications  = rateReply.getNotifications() />
RateReply Notifications:  #Notifications[1].getMessage()#
</cfif>
</cfoutput>
</cfprocessingdirective>

WSDL Version 4 Code (Rough)

<!--- java objects used for paramters later on in the code --->
<cfset objPosInteger = createObject("java", "org.apache.axis.types.PositiveInteger") />
<cfset objNonNegInteger = createObject("java", "org.apache.axis.types.NonNegativeInteger") />

<!---
Populate a structure with all rate request settings
--->
<cfset data = structNew() />

<!---- set to true to get the rates for different service types --->
<cfset getAllRatesFlag = false>

<!--- credential information --->
<cfset data.ClientDetail.AccountNumber = "My Test Account" />
<cfset data.ClientDetail.MeterNumber = "My Test Meter" />
<cfset data.TransactionDetail.CustomerTransactionId = "java sample - Rate Request" />
<cfset data.WebAuthenticationDetail.UserCredential.key ="My Developer Test Key" />
<cfset data.WebAuthenticationDetail.UserCredential.password ="My Developer Test Password" />

<!--- webservice version --->
<cfset data.Version.serviceId = "crs" />
<cfset data.Version.major = "4" />
<cfset data.Version.intermediate =  "0" />
<cfset data.Version.minor =  "0" />

<!--- shipment characteristics --->
<cfset data.requestedShipment.DropoffType = "REGULAR_PICKUP" />
<cfset data.requestedShipment.ShipTimestamp = now()>
<cfif NOT getAllRatesFlag>
<cfset data.requestedShipment.ServiceType = "INTERNATIONAL_PRIORITY">
<cfset data.requestedShipment.PackagingType = "YOUR_PACKAGING">
</cfif>

<!--- shipper information --->
<cfset streetArray = arrayNew(1) />
<cfset arrayAppend(streetArray, "Address Line 1") />
<cfset shipper.address.StreetLines = streetArray />
<cfset shipper.address.City = "City Name" />
<cfset shipper.address.StateOrProvinceCode = "TN" />
<cfset shipper.address.PostalCode = "38115" />
<cfset shipper.address.CountryCode = "US" />
<cfset data.requestedShipment.shipper = shipper>

<!--- receiver information --->
<cfset streetArray = arrayNew(1) />
<cfset arrayAppend(streetArray, "Address Line 1") />
<cfset recipient.address.StreetLines= streetArray />
<cfset recipient.address.City= "City Name" />
<cfset recipient.address.StateOrProvinceCode= "QC" />
<cfset recipient.address.PostalCode= "H1E1A1" />
<cfset recipient.address.CountryCode= "CA" />
<cfset data.requestedShipment.recipient = recipient>


<cfset Payment.PaymentType = "SENDER">
<cfset data.requestedShipment.shippingChargesPayment = Payment>

<!--- package information --->
<cfset packagesArray = ArrayNew(1)>
<cfset newPackage = structNew()>
<cfset newPackage.Weight.Value = "15.00">
<cfset newPackage.Weight.Units = "LB">
<cfset newPackage.InsuredValue.Amount = "10.00">
<cfset newPackage.InsuredValue.Currency = "USD">

<!--- package dimensions --->
<!--- could not get this to work without using java objects --->
<cfset newPackage.Dimensions.Length = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Width = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Height = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Units = "IN">
<cfset arrayAppend(packagesArray, newPackage)>

<cfset data.requestedShipment.RequestedPackages = packagesArray>
<cfset data.requestedShipment.packageCount = objNonNegInteger.init("1")>
<cfset data.requestedShipment.packageDetail = "INDIVIDUAL_PACKAGES">
<cfset RateRequestTypeArray = arrayNew(1)>
<cfset arrayAppend(RateRequestTypeArray, "ACCOUNT")>
<cfset data.requestedShipment.RateRequestTypes = RateRequestTypeArray>

<!---
Invoke the webservice

Notes, Using cfinvoke did not work. I think
it may be related to the optional parameters
--->
<cfscript>
ws = createObject("webservice", "http://127.0.0.1:8501/FedEx/RateService_v4.wsdl");
rateReply = ws.getRates(data);
</cfscript>

<!---
Process the response
--->
<cfoutput>
<h1>Response: Highest Severity = #rateReply.getHighestSeverity().toString()#</h1>
<cfset notifications = getNotifications(rateReply.getNotifications())>
<cfdump var="#notifications#" label="Notifications">

<cfif isResponseOK(rateReply.getHighestSeverity().toString())>
<cfset rrds = rateReply.getRateReplyDetails() />

<cfloop from="1" to="#arrayLen(rrds)#" index="i">
<cfset rrd =  rrds[i]>

Service type = #rrd.getServiceType()#<br>
Packaging type = #rrd.getPackagingType()#<br>

<cfset rsds = rrd.getRatedShipmentDetails()>
<cfloop from="1" to="#arrayLen(rsds)#" index="j">
<cfset rsd = rsds[j] />
<cfset srd = rsd.getShipmentRateDetail() />

RatedShipmentDetail #j#<br>
<!--- using functions because these values may be null --->
Rate type = #getValue( srd.getRateType() )#<br>
Total Billing weight = #getWeightValue( srd.getTotalBillingWeight() )# <br>
Total surcharges = #getMoneyValue( srd.getTotalSurcharges() )# <br>
Total net charge #getMoneyValue( srd.getTotalNetCharge() )#<br>
RatedPackageDetails:<hr>

<cfset rpds = rsd.getRatedPackages()>
<cfif not IsDefined("rpds")>
<cfset rpds = arrayNew(1)>
</cfif>

<cfloop from="1" to="#arrayLen(rpds)#" index="k">
RatedPackageDetail #k#<br>
<cfset rpd = rpds[k]>
<cfset prd = rpd.getPackageRateDetail()>
<cfif IsDefined("prd")>
Billing weight = #getWeightValue( prd.getBillingWeight() )# <br>
Base charge = #getMoneyValue( prd.getBaseCharge() )# <br>
<cfset surcharges = prd.getSurcharges()>
<cfif not IsDefined("surcharges")>
<cfset surcharges = arrayNew(1)>
</cfif> 
<cfloop from="1" to="#arrayLen(surcharges)#" index=",">
#surcharge.getDescription()# surcharge = #getMoneyValue( surcharge.getAmount() )#<br>
</cfloop>
</cfif>
</cfloop>
</cfloop>
</cfloop>
</cfif>
</cfoutput>

<cffunction name="getMoneyValue" returntype="string">
<cfargument name="obj" type="any" required="false">
<cfif structKeyExists(arguments, "obj")>
<cfreturn arguments.obj.getAmount().toString() &" "& arguments.obj.getCurrency().toString() />
</cfif>

<cfreturn "" />
</cffunction>

<cffunction name="getWeightValue" returntype="string">
<cfargument name="obj" type="any" required="false">
<cfif structKeyExists(arguments, "obj")>
<cfreturn arguments.obj.getValue().toString() &" "& arguments.obj.getUnits().toString() />
</cfif>

<cfreturn "" />
</cffunction>

<cffunction name="getValue" returntype="string">
<cfargument name="obj" type="any" required="false">
<cfargument name="defaultValue" type="string" required="false" default="">
<cfif structKeyExists(arguments, "obj")>
<cfreturn arguments.obj.toString()/>
<cfelse>
<cfreturn arguments.defaultValue/>
</cfif>
</cffunction>

<cffunction name="getNotifications" returntype="array" hint="Converts the notifications object into an array of structures">
<cfargument name="notifications" type="array" required="false">

<cfset var Local = structNew()>
<cfset Local.results = arrayNew(1)>

<cfif structKeyExists(arguments, "notifications") and arrayLen(arguments.notifications)>
<cfloop from="1" to="#arrayLen(arguments.notifications)#" index="Local.x">
<cfset Local.n = arguments.notifications[Local.x]>
<cfif structKeyExists(Local, "n")>
<cfset Local.ns = Local.n.getSeverity()>

<cfset Local.note = structNew()>
<cfset Local.note.severity = "">
<cfif NOT structKeyExists(Local, "ns")>
<cfset Local.severity = Local.ns.getValue()>
</cfif>
<cfset Local.note.code = Local.n.getCode()>
<cfset Local.note.message = Local.n.getMessage()>
<cfset Local.note.source = Local.n.getSource()>
<cfset arrayAppend( Local.results, Local.note) >
</cfif>
</cfloop>
</cfif>
<cfreturn Local.results>
</cffunction>

<cffunction name="isResponseOK" returntype="boolean" hint="Returns true if the severity type is WARNIGN,NOTE or SUCCESS">
<cfargument name="notificationSeverityType" required="false">
<cfset var isOK = false>
<cfif structKeyExists(arguments, "notificationSeverityType")>
<cfswitch expression="#arguments.notificationSeverityType#">
<cfcase value="WARNING,NOTE,SUCCESS">
<cfset isOK = true>
</cfcase>
<cfdefaultcase>
<cfset isOK = false>
</cfdefaultcase>
</cfswitch>
</cfif>

<cfreturn isOK>
</cffunction>

WSDL Version 7 Code (Rough)

<!--- java objects used for paramters later on in the code --->
<cfset objPosInteger = createObject("java", "org.apache.axis.types.PositiveInteger") />
<cfset objNonNegInteger = createObject("java", "org.apache.axis.types.NonNegativeInteger") />

<!---
Populate a structure with all rate request settings
--->
<cfset data = structNew() />

<!---- set to true to get the rates for different service types --->
<cfset getAllRatesFlag = false>

<!--- credential information --->
<cfset data.ClientDetail.AccountNumber= "My Test Account" />
<cfset data.ClientDetail.MeterNumber= "My Meter Number" />
<cfset data.TransactionDetail.CustomerTransactionId = "Example - Rate Request" />
<cfset data.WebAuthenticationDetail.UserCredential.key = "My Developer Test Key" />
<cfset data.WebAuthenticationDetail.UserCredential.password = "My Developer Test Password" />

<!--- webservice version --->
<cfset data.Version.serviceId = "crs" />
<cfset data.Version.major = "7" />
<cfset data.Version.intermediate =  "0" />
<cfset data.Version.minor =  "0" />

<!--- shipment characteristics --->
<cfset data.requestedShipment.DropoffType = "REGULAR_PICKUP" />
<cfset data.requestedShipment.ShipTimestamp = now()>
<cfif NOT getAllRatesFlag>
<cfset data.requestedShipment.ServiceType = "INTERNATIONAL_PRIORITY">
<cfset data.requestedShipment.PackagingType = "YOUR_PACKAGING">
</cfif>

<!--- shipper information --->
<cfset streetArray = arrayNew(1) />
<cfset arrayAppend(streetArray, "Address Line 1") />
<cfset shipper.address.StreetLines = streetArray />
<cfset shipper.address.City = "City Name" />
<cfset shipper.address.StateOrProvinceCode = "TN" />
<cfset shipper.address.PostalCode = "38115" />
<cfset shipper.address.CountryCode = "US" />
<cfset data.requestedShipment.shipper = shipper>

<!--- receiver information --->
<cfset streetArray = arrayNew(1) />
<cfset arrayAppend(streetArray, "Address Line 1") />
<cfset recipient.address.StreetLines= streetArray />
<cfset recipient.address.City= "City Name" />
<cfset recipient.address.StateOrProvinceCode= "QC" />
<cfset recipient.address.PostalCode= "H1E1A1" />
<cfset recipient.address.CountryCode= "CA" />
<cfset data.requestedShipment.recipient = recipient>


<cfset Payment.PaymentType = "SENDER">
<cfset data.requestedShipment.shippingChargesPayment = Payment>

<!--- package information --->
<cfset packagesArray = ArrayNew(1)>
<cfset newPackage = structNew()>
<cfset newPackage.Weight.Value = "15.00">
<cfset newPackage.Weight.Units = "LB">
<cfset newPackage.InsuredValue.Amount = "10.00">
<cfset newPackage.InsuredValue.Currency = "USD">

<!--- package dimensions --->
<!--- could not get this to work without using java objects --->
<cfset newPackage.Dimensions.Length = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Width = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Height = objNonNegInteger.init(1)>
<cfset newPackage.Dimensions.Units = "IN">
<cfset arrayAppend(packagesArray, newPackage)>

<cfset data.requestedShipment.RequestedPackageLineItems = packagesArray>
<cfset data.requestedShipment.packageCount = objNonNegInteger.init("1")>
<cfset data.requestedShipment.packageDetail = "INDIVIDUAL_PACKAGES">
<cfset RateRequestTypeArray = arrayNew(1)>
<cfset arrayAppend(RateRequestTypeArray, "ACCOUNT")>
<cfset data.requestedShipment.RateRequestTypes = RateRequestTypeArray>

<!---
Invoke the webservice

Notes, Using cfinvoke did not work. I think
it may be related to the optional parameters
--->
<cfscript>
ws = createObject("webservice", "http://127.0.0.1:8501/FedEx/RateService_v7.wsdl");
rateReply = ws.getRates(data);
</cfscript>

<!---
Process the response
--->
<cfoutput>
<h1>Response: Highest Severity = #rateReply.getHighestSeverity().toString()#</h1>
<cfset notifications = getNotifications(rateReply.getNotifications())>
<cfdump var="#notifications#" label="Notifications">

<cfif isResponseOK(rateReply.getHighestSeverity().toString())>
<cfset rrds = rateReply.getRateReplyDetails() />

<cfloop from="1" to="#arrayLen(rrds)#" index="i">
<cfset rrd =  rrds[i]>

Service type = #rrd.getServiceType()#<br>
Packaging type = #rrd.getPackagingType()#<br>

<cfset rsds = rrd.getRatedShipmentDetails()>
<cfloop from="1" to="#arrayLen(rsds)#" index="j">
<cfset rsd = rsds[j] />
<cfset srd = rsd.getShipmentRateDetail() />

RatedShipmentDetail #j#<br>
<!--- using functions because these values may be null --->
Rate type = #getValue( srd.getRateType() )#<br>
Total Billing weight = #getWeightValue( srd.getTotalBillingWeight() )# <br>
Total surcharges = #getMoneyValue( srd.getTotalSurcharges() )# <br>
Total net charge #getMoneyValue( srd.getTotalNetCharge() )#<br>
RatedPackageDetails:<hr>

<cfset rpds = rsd.getRatedPackages()>
<cfif not IsDefined("rpds")>
<cfset rpds = arrayNew(1)>
</cfif>

<cfloop from="1" to="#arrayLen(rpds)#" index="k">
RatedPackageDetail #k#<br>
<cfset rpd = rpds[k]>
<cfset prd = rpd.getPackageRateDetail()>
<cfif IsDefined("prd")>
Billing weight = #getWeightValue( prd.getBillingWeight() )# <br>
Base charge = #getMoneyValue( prd.getBaseCharge() )# <br>
<cfset surcharges = prd.getSurcharges()>
<cfif not IsDefined("surcharges")>
<cfset surcharges = arrayNew(1)>
</cfif> 
<cfloop from="1" to="#arrayLen(surcharges)#" index=",">
#surcharge.getDescription()# surcharge = #getMoneyValue( surcharge.getAmount() )#<br>
</cfloop>
</cfif>
</cfloop>
</cfloop>
</cfloop>
</cfif>
</cfoutput>

<cffunction name="getMoneyValue" returntype="string">
<cfargument name="obj" type="any" required="false">
<cfif structKeyExists(arguments, "obj")>
<cfreturn arguments.obj.getAmount().toString() &" "& arguments.obj.getCurrency().toString() />
</cfif>

<cfreturn "" />
</cffunction>

<cffunction name="getWeightValue" returntype="string">
<cfargument name="obj" type="any" required="false">
<cfif structKeyExists(arguments, "obj")>
<cfreturn arguments.obj.getValue().toString() &" "& arguments.obj.getUnits().toString() />
</cfif>

<cfreturn "" />
</cffunction>

<cffunction name="getValue" returntype="string">
<cfargument name="obj" type="any" required="false">
<cfargument name="defaultValue" type="string" required="false" default="">
<cfif structKeyExists(arguments, "obj")>
<cfreturn arguments.obj.toString()/>
<cfelse>
<cfreturn arguments.defaultValue/>
</cfif>
</cffunction>

...Read More

CFDBINFO - How did they do that?

I have been hearing about the impressive new cfdbinfo tag in ColdFusion 8 and got to wondering, how did they do that? As I had not installed CF8 yet, I was curious how the tag obtains the information from varying database types and more importantly if something similar would work with MX7.

I set about trying to figure it all out and in my searching came upon java's DatabaseMetaData interface. It seemed likely this is what cfdbinfo uses to obtain metadata information. So I decided to create a small test CFC and run it against an Access database. The reason I chose Access is because of all the databases I have used, MS Access seems the most reluctant to divulge metadata information.

As you can see below the first draft of the CFC yielded all kinds of good information. Not only table and column names, but even column data types and procedure names. (Yes, apparently my jdbc driver reports MS Access stored queries as procedures)

<cfset dbUtil = createObject("component", "AccessDBUtil").init("MyAccessUnicodeDSN") />
<cfset qVersion = dbUtil.getVersionInfo() />
<cfset qCatalogs = dbUtil.getCatologs() />
<cfset qProcedures = dbUtil.getProcedures() />
<cfset qTableTypes = dbUtil.getTableTypes() />
<cfset qSchemas = dbUtil.getSchemas() />
<cfset qTables = dbUtil.getTables(typeList = "Table") />
<cfset qColumns = dbUtil.getColumns(tablePattern = "Table1") />


<cfdump var="#qProcedures#"/>
<cfdump var="#qColumns#"/>
<cfdump var="#qTables#"/>
<cfdump var="#qSchemas#"/>
<cfdump var="#qTableTypes#"/>
<cfdump var="#qVersion#"/>
<cfdump var="#qCatalogs#"/>

I ran some basic tests and overall the results were impressive. Though predictably schema and catalog filters had no effect, because MS Access does not have schemas or catologs. Unfortunately the jdbc driver I am using does not allow wildcard searches on table or column names. This was disappointing since it is a nice feature, but cest la vie.

I've included the CFC code below for anyone interested. Keep in mind it is a rough draft. Now if I could find the time I would love to create a version of Ray Camden's Database Explorer that works with MX7, but that is for another day.

As always comments/corrections/suggestion are welcome

AccessDBUtil.cfc

<cfcomponent>

    <cffunction name="init" returntype="AccessDBUtil" access="public" >
        <cfargument name="jdbcDatasourceName" type="string" required="true" />
        <cfset setDatasourceName(arguments.jdbcDatasourceName)  />
        <cfreturn this  />
    </cffunction>
    
    <cffunction name="setDatasourceName" returntype="void" access="private" >
        <cfargument name="jdbcDatasourceName" type="string" required="true" />
        <cfset variables.instance.datasource = arguments.jdbcDatasourceName  />
    </cffunction>
    <cffunction name="getDatasourceName" returntype="string" access="public" >
        <cfreturn variables.instance.datasource  />
    </cffunction>
    
    <cffunction name="getCatologs" returntype="query" access="public" >
        <cfset var Local = structNew() />

        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getCatalogs()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />

        <cfreturn Local.query   />
    </cffunction>
    
    <cffunction name="getSchemas" returntype="query" access="public" output="true" >
        <cfset var Local = structNew() />

        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getSchemas()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />

        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getTableTypes" returntype="query" access="public" >
        <cfset var Local = structNew() />
        
        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getTableTypes()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />
        
        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getProcedures" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" default="%" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. "  />
        <cfargument name="procedurePattern" type="string" required="false" default="%" />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isSchemaNonNull =  structKeyExists(arguments, "schemaPattern");
            Local.connection = getConnection();

            //TODO: check if there is a way to pass java NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.procedurePattern );
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( arguments.catalogName, 
                            javacast("null", ""), 
                            arguments.procedurePattern );
            }        
            else if ( Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.procedurePattern );
            }        
            else {
                Local.resultset = Local.connection.getMetaData().getProcedures( javacast("null", ""), 
                            javacast("null", ""),
                            arguments.procedurePattern );
            }        

            Local.query = resultsetToQuery( resultset = Local.resultset );
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>

    
    <cffunction name="getTables" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" default="%" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. "  />
        <cfargument name="tablePattern" type="string" required="false" default="%" />
        <cfargument name="typeList" type="string" required="false" default="" hint="Comma delimited list of table types to include. An empty string means retrieve all types" />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isTypeNonNull =  len(trim(arguments.typeList));
            Local.typeArray        = listToArray(arguments.typeList);
            Local.connection = getConnection();

            //TODO: check if there is a way to pass java NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isTypeNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            Local.typeArray);
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            javacast("null", ""));
            }        
            else if ( Local.isTypeNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            Local.typeArray); 
            }        
            else {
                Local.resultset = Local.connection.getMetaData().getTables( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            javacast("null", "")); 
            }

            Local.query = resultsetToQuery( resultset = Local.resultset );
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getColumns" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. If not specified, the schema is not used to narrow the search"  />
        <cfargument name="tablePattern" type="string" required="false" default="%" hint="Table name to find. Wildcards are NOT supported by com.inzoom.jdbcado driver"  />
        <cfargument name="columnPattern" type="string" required="false" default="%" hint="Column name to find. Wildcards are NOT supported by com.inzoom.jdbcado driver"  />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isSchemaNonNull =  structKeyExists(arguments, "schemaPattern");
            Local.connection = getConnection();

            //TODO: check if there is a way to pass NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( arguments.catalogName, 
                            javacast("null", ""), 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }        
            else if ( Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }
            else {
                Local.resultset = Local.connection.getMetaData().getColumns( javacast("null", ""), 
                            javacast("null", ""), 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }

            Local.query = resultsetToQuery( resultset = Local.resultset );
            //Local.query2 = createObject("java", "coldfusion.sql.QueryTable").init(Local.resultset);
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>
    
    <cffunction name="getVersionInfo" returntype="query" access="public" >
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.query = queryNew( "DATABASE_VERSION,DATABASE_PRODUCTNAME" & 
                                ",DATABASE_MAJOR_VERSION,DATABASE_MINOR_VERSION," & 
                                ", DRIVER_VERSION,DRIVER_NAME,JDBC_MAJOR_VERSION," &
                                "JDBC_MINOR_VERSION");
                            
            Local.connection = getConnection();
            Local.meta = getConnection().getMetaData();
        
            Local.row = queryAddRow( Local.query, 1);
            Local.query["DATABASE_PRODUCTNAME"][Local.row]         = Local.meta.getDatabaseProductName();
            Local.query["DATABASE_VERSION"][Local.row]             = Local.meta.getDatabaseProductVersion();
            Local.query["DATABASE_MAJOR_VERSION"][Local.row]     = Local.meta.getDatabaseMajorVersion();
            Local.query["DATABASE_MINOR_VERSION"][Local.row]     = Local.meta.getDatabaseMinorVersion();
            Local.query["DRIVER_VERSION"][Local.row]             = Local.meta.getDriverVersion();
            Local.query["DRIVER_NAME"][Local.row]                 = Local.meta.getDriverName();
            Local.query["JDBC_MAJOR_VERSION"][Local.row]         = Local.meta.getDriverMajorVersion();
            Local.query["JDBC_MINOR_VERSION"][Local.row]         = Local.meta.getDriverMinorVersion();
    
            Local.connection.close();
        </cfscript>
        
        <cfreturn Local.query  />
    </cffunction>


    <cffunction name="getConnection" returntype="any" >
        <cfset var dsService = createObject("java", "coldfusion.server.ServiceFactory").getDataSourceService()  />
        <cfset var connection = dsService.getDataSource(getDatasourceName()).getConnection()  />
        <cfreturn connection  />
    </cffunction>
    
    <cffunction name="getResultSetColumnNames" returntype="array" access="private" hint="Returns an array of column names from the resultset metadata" >
        <cfargument name="resultsetMetadata" type="any" required="true" />
        <cfset var Local = structNew()  />
        
        <cfscript>
            Local.columnArray = arrayNew(1);
            //get number of columns in the resulset
            Local.maxColumn = arguments.resultsetMetadata.getColumnCount();
            //get the name of each column in the query and append it to the array
            for (Local.index = 1; Local.index LTE Local.MaxColumn; Local.index = Local.index + 1) {
                arrayAppend( Local.columnArray, arguments.resultsetMetadata.getColumnName( javacast("int", Local.index)) );        
            }
        </cfscript>
        
        <cfreturn Local.columnArray  />
    </cffunction>
    
    <cffunction name="resultsetToQuery" returntype="query" access="private" hint="Converts a resulset to a query object" >
        <cfargument name="resultset" type="any" required="true" />
        <cfset var Local = structNew()  />
        
        <!--- could also use "coldfusion.sql.QueryTable" to create a resulset 
        <cfset Local.query = createObject("java", "coldfusion.sql.QueryTable").init(arguments.resultset) />
        --->
        
        <cfscript>
            Local.columnNames     = getResultSetColumnNames( arguments.resultset.getMetaData() );
            Local.maxColumn     = arrayLen( Local.columnNames );
            Local.query         = queryNew( arrayToList(Local.columnNames) );
            
            while ( arguments.resultset.next() ) {
                //add one row to the query for each row in the resultset
                Local.rowIndex = queryAddRow(Local.query , 1);
                 for ( Local.colIndex = 1; Local.colIndex LTE Local.maxColumn; Local.colIndex = Local.colIndex + 1) {
                    Local.colName = Local.columnNames[Local.colIndex];
                    Local.value = arguments.resultset.getObject( Local.colName );
                    // if the returned value is not NULL
                    if ( structKeyExists(Local, "value") ) {
                        Local.query[Local.colName][Local.rowIndex] = Local.value;
                    }
                }
            }
        </cfscript>
                
        <cfreturn Local.query  />
    </cffunction>
    
</cfcomponent>

...Read More

Displaying multiple queries on a single sheet with POIUtility.cfc

I recently saw an interesting question on the adobe forums about POI and the POIUtility. The POIUtility is a wrapper CFC that can be used to read and write Excel files. Using the built in functions it allows you to display one query per worksheet. The poster asked if it was possible to use the POIUtility to display more than one query on a single worksheet.

I posted an example of how you might modify the POIUtility.cfc to do just that. The queries on each sheet can be displayed either horizontally or vertically. The example is barely tested, but hopefully someone out there might find it useful.

You can download POI from apache.org and the POIUtility.cfc from Ben Nadel's site.

And now a comment from left field ..

Ben Nadel is a talented guy, with some very interesting ideas and projects involving ColdFusion. A lot of good work went into the POIUtility and many programmers have found it extremely useful. But since I am posting a link on my site I have to say that personally I find many of his examples superfluous. Its like being trapped in the mind of an adolescent boy with some bizarre obsession or weird form of OCD or GTS. I will not even visit some areas of his site. But if you can get past all that, there are some interesting articles to be found.. some of which are actually about ColdFusion.

Now I fully expect some of you to disagree with me, but to each his own ;-)

...Read More