Connecting CFMX Pro to Oracle

This has been convered in a few places, but people ask this alot, so I figured I would drop my version of the answer somewhere that may be a little easier to find. I've done this a few times here and there.

There are a variety of ways that you can connect Oracle, but most of them provide limited functionality.

JDBC Thin driver

Details can be seen here:
ColdFusion Technote
CFGuru Blog (Yup, that comment at the bottom is me).

Pros - This is by far the most lightweight installation to get Oracle connected with CFMX.

Cons - You cannot get Result Sets to return from Stored Procedures using this driver. (There may be other stored proc issues I may not be aware of, but I hit this one and gave up.)

Oracle ODBC Driver

Pros - You have the full functionality of Oracle (i.e you can get result sets out of stored procs).

Cons - It's a longer and slightly more tricky install process as you have to install the Oracle Client.

Personally, I like the ODBC version the best, simply because getting result sets out of Stored Procs is of high priority to me.

The steps for installing the ODBC version are the following:

1. Download the Oracle Client (It's big. Be Warned.)
2. Download the Oracle ODBC driver.
3. Install the ODBC Client (create an 'Oracle Home' in any dir. Default should be fine).
4. Install the ODBC drivers (You may have to do this with the Oracle Universal Installer, which comes with the Client).
5. Open Oracle - oraHome > Configuration and Migration Tools > Net Manager
6. Click Service Naming in the tree.
7. Click Edit > Create from the menu at the top. You will get a new window.
8. Enter the Net Service Name. I tend to use the same name as the Oracle SID/Service Name. Click Next
9. Leave at TCP/IP and click Next.
10. Enter the Host Name of the Oracle server (i.e. oracle.mysite.com), and the port number. The default Oracle port is displayed. Click Next.
11. Enter the SID/Service name of the Oracle database you are connecting to. Leave as Database Default for connection type. Click Next.
12. Click Test to test the Oracle connection. First test will probably fail, as it will have the wrong login details. Enter the right details and try again.
13. Once the connection works, click Finish.
14. Exit the Net Manager and click Save on the dialog.
15. Goto Control Panel > Administrative Tools > Data Sources.
16. Click System DSN.
17. Click Add.
18. Choose Oracle in OraHomeand click Finish. (Why finish? you're not done!)
19. Enter the name in DataSource Name. Doesn't really matter what. I tend to stick to the SID / Service Name.
20. Choose your TNS Service Name from the drop down provided.
21. Test the connection. The first test will probably fail, as the username and password will be wrong. Enter the correct details until it works.
22. Click OK.
23. Now go into the CF Administrator > Data Sources
24. Add a ODBC Socket datasource.
25. Choose the Oracle ODBC DSN from the drop down.
26. Click Show Advanced Settings and enter the username and password (if required).
27. Click Submit.
28. The datasource should now validate (if it doesn't, try restarting the machine, as sometimes a restart is required for the new odbc connection to take full effect).

I don't think I've missed anything. Like I said, it's a tad long and convoluted, but it does work in a production environment.

Once it's set up once, adding a new datasource is a 5 minute job.

I hope that solves someone's problem, because it took me a few weeks to work that one out.

XHTML and 3 Column Layout

For a while now the CSS markup on Compound Theory has caused some interesting display bugs to occur on IE, and particularly on Mac IE.

I've redone the whole CSS structure, and I think this is a much better (less bugs?) way than the previous.

The originial incarnation was based on the principle of floating one nav bar on the left, one nav bar on the right, and keeping a middle section for the content.

E.g.

div.left {
    float: left;
    width: 200px;
}

div.right {
    float: right;
    width: 200px;
}

div.middle {
    margin-left: 210px;
    margin-right: 210px;
}

However, apon shrinking the size of the browser window, often the middle section would drop below the left, or really wide content would shift the middle section down the page.

I tried everything known to mankind to stop it from shifting down, to no avail.

Finally my friend Adrian shot me over to www.glish.com and I discovered a xhtml layout that used absolute positioning for the outer columns.

Simply put:

div.left {
    position: absolute;
    left: 5px;
    top: 50px;
    width: 200px;
}

div.right {
    position: absolute;
    right: 5px;
    top: 50px;
    width: 200px;
}

The full details can be found here, however by using absolute positioning to line up both of the left and right columns for my nav bars, the issues inherent with using a float no longer applied.

Definatley the way to go.

Now I still need to sort out the form display issue on a Mac...

If you are going to use OOP, study an OOP language

This is something that tends to get my goat a little in some instances, so I apologise if I get a tad ranty.

We had a recent discussion on cfaussie about the this scope in CFCs, and whether or not it was an appropriate place to store data. Regardless of what the outcome of that conversation (and we all have varying opinions on the topic), there was one thing that became very prevelent to me:

There seems to be a bunch of people out there trying to write OOP code, without a full understanding of OOP concepts.

Unfortunatley I think that this is one of the pitfalls of CF - because it is so easy to write code, people can often think that they can just 'work it out on the fly' and they will be just fine.

Sometimes this is true - and sometimes it isn't. I know quite a few proficient coders that have come to many of OO's basic concepts through trial and error, but I don't think it is the most efficient way of doing things.

The simple fact of the matter is - OO concepts have been around for many years, so why would you try and guess them when you can find them out by studying a OOP language?

If you want to do OOP Programming with ColdFusion, go and study an OOP Language, and bring the theory of it back to ColdFusion.

I cannot possibly encourage this enough. Doesn't matter if it's Java, C++, Eiffel, SmallTalk, whatever. What you are looking for is the concepts and theories.

Basic software design concepts and terms such as:

• Coupling
• Cohesion
• Encapsulation
• Inheritence
• Polymorphism

I feel should be understood before really useful OOP Coldfusion code can be developed.

By undertaking some Object Oriented training of some kind, I can assure you that many pieces of software design will make so much more sense to you, and will become a far greater ColdFusion coder because of it.

I just want to make clear here as well - I am NOT saying that if you don't understand these terms or concepts, you are a bad coder, or that you write bad software. Far from it. People have different backgrounds, and different skills and come at things in different ways. So I apologise if I have offended anyone.

I just want to make sure that all of us that talk about software (and web development is software development) use the language, terms and vocabulary that have been around for many years.

Safe-sex education replaced with programs that teach abstinence as the only way to prevent STDs

I don't usually post of things of this nature - but it scared me so badly, I thought I had to share it with everyone.

Quote from: http://www.alternet.org/envirohealth/19045/

"Lethal new regulations from President Bush's Centers for Disease Control and Prevention (CDC) in Atlanta, quietly issued with no fanfare last week, complete the right-wing Republicans' goal of gutting HIV-prevention education in the United States. In place of effective, disease-preventing safe-sex education, little will soon remain except failed programs that denounce condom use, while teaching abstinence as the only way to prevent the spread of AIDS. And those abstinence-only programs, researchers say, actually increase the risk of contracting AIDS and other sexually transmitted diseases (STDs)."

Can someone please tell me this isn't real?

The thought of removing safe sex programs and replacing them with abstinence only programs is recipe for disaster, regardless of the nation that it belongs to.

I used to live in Atlanta, so this does hit a little close to home.

If you do live in the area - please take action -

"There's only a tiny window of opportunity to try to get the new CDC censorship rules changed before they go into effect (the deadline for public comments is August 16 -- they may be e-mailed to HIVComments@cdc.gov or faxed to 404-639-3125.)"

Oracle 9i Database Documentation

If anyone is wondering why I poste the 9i documentation, is because it always seems to me that most people have trouble finding it in the Oracle site. It's buried pretty deep.

(Or maybe they don't look - either way, it's right here)

Oracle9i Database Online Documentation

I must say, as far as documentation goes - it is very thorough.

Enjoy. :o)

Will we ever get an API for ColdFusion's Java Code?

This was one question I was hitting myself for not asking after the recent MXDU in Sydney.

Point: ColdFusion sits on top of a J2EE engine

Point: This is very neat, because it means we have the power of Java at our hands to do low level work we would not otherwise be able to do in CF.

Point: Any time you want to do Java code that integrates with already existing ColdFusion code, there is a whole lot of guesswork and introspection into undocumented features of CF so that we can leverage it's power in ways that we need. (i.e like my post on attempting to add a HttpSessionBindingListener to a CF Session).

Question: Why doesn't CF ship with Javadoc API documentation of the Java classes it is built on? It would make all of our lives a helleva lot easier.

It's not like I'm asking for CF to become open source (which I don't even want), but simply make available the information we can already get through Java Reflection in an easy to use format.

I recently unzipped the cfusion.war archive from the J2EE deployment and made it so I could compile a copy in my Jave IDE (which was a tad trickier than it seemed).  I did this so I could compile some servlets against the already existing codebase, and make sure there were no conflicts.  Admitedly I also did it so that I could grasp a much finer notion of the underpinnings of what ColdFusion really is, and get to put my hands in as many of its internals as I could.  For those of you who do Java/J2EE work as well, I would say try this out, it gives you a whole slew of new ideas in terms of extending ColdFusion.

There is a small part of me (and for the record, I am in no way advocating this) that is tempted to take a decompiler to the codebase of CF, just to see what makes it tick.  Just to be clear on this point - I am quite aware this would be quite illegal, and I have not done so.  That being said, the only reason I would do so, is simply so that I can understand CF at a much lower level, and simply become a better ColdFusion Programmer.  The purpose of this discussion is not for whether or not decompilation is ethically/legally good or bad.

So to end on a positive note - while this does sound like a gripe at heart, really I'm just asking for something that in some ways already exists, and I think would be a very large resource for all the developers who work with ColdFusion today.  It would mean we would all stop grasping at things we think are there in the code base, and can concentrate on really building on what we know is there.

So in case any MM guys happen to read this - Can we have it?

J2EE Sessions and OnSessionEnd (HttpSessionBindingListener)

Here I was thinking I was really clever - but I guess I'm not as clever as I had thought.

I wanted to create a way for CF'ers to have a way to determine when a Session had ended - I could do it in J2EE, so I could do it in CF? Right? CF just sits on top of Java, so it should be easy.

So I created a Java class that implemented the HttpSessionBindingListener interface and set it up to call a series of URLs when a session was either timed out, or the user logged off.

I turned on 'Use J2EE Session Variables' within the cfadmin app and wrote the below CF test code:

<cfscript>
 obj = createObject("Java", "com.compoundtheory.URLCallOnSessionUnbind").init();
 
 session.obj = obj;
 
 StructDelete(session, "obj");
</cfscript>

Using this and subsequent test code, I found that neither valuBound() or  valueUnBound() was called on the my Java object.

This led me to believe that CF only implements a mechanism for sharing data between J2EE sessions and itself, and does not actually utilise the actual J2EE session objects themselves.

Apon further examination (thanks to getJavaMetaData), I discovered that the CF session object doesn't even come close to the javax.servlet.http.HttpSession interface, so there was no way this was going to work in the first place.  (It is in face a coldusion.runtime.j2eeSessionScope object).

It's a pity that this hasn't worked, it would have been very handy - and I know how long CF'ers have wanted the capability to know when sessions had ended.

So I guess in conclusion - we now all know, that when it says in the CF admin 'Use J2EE session variables' - it's really not telling the entire truth.

Naughty Naughty. ;o)

Just for fun these guys over at IBM talk about some ways of sharing J2EE and CF session information.  An interesting read never the less.

My apologies to Mac Users

I just wanted to extend my apologies to Mac users, I know full well none of my forms display correctly on Macs (particularly Safari).

It's a known issue, but since I don't have a Mac at hand, it's very hard to debug :oP.  I realised that everything was all skew-iff when I did some screen grabs on the site at Dan Vine's iCapture site to see what things look like.

Not sure when I am going to fix the problem (or how at this stage), but I will get to it.

This is one place where XHTML Strict kicks me right up the....

Make it look like Outlook...

I can't count the number of times I've heard lines like 'Can you make it look like explorer?' or 'Make it work like Outlook', or even proactively looking for ways I can make my web application work like a Microsoft windows application.

It makes me wonder if (a) MS just managed to get User Interface down pat or (b) everyone is so used to MS interfaces that even without thinking it has almost come across as a 'standard' for UI development.

I've yet to have someone ask me to develop a UI like Max OSX (although I'm sure it's happened to some people) - or worse yet, 'Can you develop a DOS prompt for the web please?' (Although, for those more technically minded, it a command prompt could be an efficient way of doing things).

I can never decide if this is actually really frustrating, as it's hard to be truly 'innovative' in terms of UI, or actually rather handy, in that I know that a user is going to have no trouble with a explorer like tree, or a drop down header, and icon displays that mimic Microsoft displays.

So maybe all in all, the more I work with what people already use (i.e. MS Apps), the less I need to worry about Users looking for that dreaded 'support' ;o).

Random thought for the day.

I don't believe URLs anymore

Had an interesting problem float past me the other day, that had me thinking a little outside the CF square.

Situation: We have an existing HTML website, and we are moving it to a CF website.

Problem: We don't want to break any of our existing HTML links. I.e. if you we have http://www.mysite.com/page.html it should stay the same - so no sneaky .cfm extensions.

Interesting Point: It's a standalone CF Instance running on a J2EE server. Hmnnn...

So the obvious starting point is to map the CF Servlet to *.html - much like you could already do within a IIS configuration, nothing all that interesting there.

Obviously there is now some new CF magick, that allows for the pulling in of content (basically through a single CF Custom tag, that does some processing dependent on the URL).

But given the standard CF setup, that would mean we have to create a CF page for every old html page. That is seriously gonna suck, and is a lot of hard work, for not much payoff.

So of course, I got a' thinking and said - 'wait a minute, why can't we use a servlet to fake the html page, and then pass the URL information to a single CF page via the request scope?'

I.e. do something like this:

public class CFForward extends HttpServlet
{

private static final String FORWARD = "forward.cfm";

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{

//grabs the servlet path
String servletPath = request.getServletPath();

/*
Sets the request scope parameter called 'servletpath' that is accessable from the
Coldfusion page. Key must be LOWERCASE!!!
*/
request.setAttribute("servletpath", servletPath);

//forward on to the coldfusion page.
this.getServletContext().getRequestDispatcher(FORWARD).forward(request, response);
}
}

Then add this Servlet mapping to *.html (and get rid of the CF one) - and presto, seamlessly drives all *.html requests over to "forward.cfm", along with the relevent details in it's request scope.

That AND all the URL and FORM scope data is still there as you would need it.

(Note: my version pulls in details from the web.xml, and does some other stuff but I figured I'd keep it simple)

So now you have what looks like a static HTML page, which is really a servlet running a ColdFusion page behind the scenes.

Yup, I don't believe what a URL tells me anymore. Where have the days gone when .html was static, .cgi was perl, and .exe was crazy ;o) You always knew where you stood with a webpage, just by looking at it's extension. Now it's all smoke and mirrors.

The only problem I've hit so far - on the website, the web stats package picks up 404 errors, however, with a servlet mapped to *.html, ANY page with ends with a .html will get picked up by the servlet. If relevent (i.e. no content for that URL), I need to be able to push a 404 error to the Server (getting it to the client isn't hard, you don't even have to use a real 404). Not sure how I'm going to do that yet.

Thought it was a nifty idea just the same.

I've been Googed!

Woah!

3 Posts and I've been added to Full as a Goog, I'm kinda excited ;o)

That and I get one of those spiffy Goog images to display on the right - and it goes with my colour scheme!

Reasons I hate XMLTransform()

Okay, maybe hate is a bit of a strong word, but seriously, having done some (serious?) XSL development under Java, the XMLTransform() of Coldfusion does leave a little to be desired.

Inability to pass in xsl:param values externally
This one is my primary gripe. I don't understand why you can't do this natively in ColdFusion. In Java, when you create your XML transformation via XSL you can pass in values that are defined in the top of the XSL document like so:

<xsl:param name="root"/>

Which means I can use that variable later on in my XSL stylesheet - the primary example being being able to pass in the root URL of your application, so you can build links from it, so regardless of where you run this XSL stylesheet, you know images and links will still be relative to the root. (Admitedly there are other ways to do this, but this is just an example).

Within the current power of CF, you would have to build your XSL at runtime, and add in these elements yourself. Personally I see this as kind of cludgy, as I prefer to keep my XSL files in a flat file somewhere.

XSL Files must be read in to a variable before they can be used
This means that before you use an XSL file, you have to <CFFILE> it in before you use it. 'No big deal' I hear you say, cache it in a scope somewhere and then reuse it later... yes yes, this is all valid, except for one small thing

<xsl:import href="modularXSL.xsl"/>

If you do this - you can no longer do relative XSL imports, which pretty much destroys any chance of doing modular XSL development. Considering that you can't use a <xsl:param> to dynamically pass through the root path, that does mean you are left with developing your XSL at compile time... (which means it's not a file anymore anyway, and can't be used by other xsl stylesheets), or hardcoding your logical path into the XSL stylesheet itself (yuck!).

But what do we do now?
Okay, so I've had my major gripe, and I did have a good winge for a little while about this before I got mad enough to actually look for a solution. There is a solution to one of the issues, there is a xslt() function that can be found at cflib.org.

This runs natively from CF, however does not handle my issue with using native <xsl:import>.

So of course, I didn't get even more mad - I decided to get down and dirty with some Java and came up with my own XSLT() funtion that uses the underlying Java engine (CF uses Xalan it seems under the hood for anyone that cares) that can take either (a) a XML / XSL string, or (b) a file path to a XML / XSL file.

This means it can do BOTH parameters, AND relative xsl importing!

The documentation looks like this:

Syntax:
XSLT(xmlsource, xslsource [, stParameters])

Arguments:
xmlSource - either a valid xml document as a string, or a absolute file path to a XML file.
xslSource - either a valid XSL document as a string, or a absolute file path to a XSL file.
stParameters (optional) - a structure of xsl:param elements to pass through where the key is the name of the param, and the value is the value of the param being passed through. Do note that StructInsert() will need to be used as param names are case sensitive, and otherwise the struct key value will be in uppercase.

Example:
This can be run a variety of ways:

OR

<cfoutput>#xslt("c:\xmlFile.xml", xsl, stParams)#</cfoutput>

OR

<cfoutput>#xslt(xml, "c:\xslFile.xsl", stParams)#</cfoutput>

OR

<cfoutput>#xslt(("c:\xmlFile.xml", "c:\xslFile.xsl", stParams)#</cfoutput>

Code:
<cffunction name="xslt" returntype="string" output="No">
<cfargument name="xmlSource" type="string" required="yes">
<cfargument name="xslSource" type="string" required="yes">
<cfargument name="stParameters" type="struct" default="#StructNew()#" required="No">

<cfscript>
var source = ""; var transformer = ""; var aParamKeys = ""; var pKey = "";
var xmlReader = ""; var xslReader = ""; var pLen = 0;
var xmlWriter = ""; var xmlResult = ""; var pCounter = 0;
var tFactory = createObject("java", "javax.xml.transform.TransformerFactory").newInstance();

//if xml use the StringReader - otherwise, just assume it is a file source.
if(Find("<", arguments.xslSource) neq 0)
{
xslReader = createObject("java", "java.io.StringReader").init(arguments.xslSource);
source = createObject("java", "javax.xml.transform.stream.StreamSource").init(xslReader);
}
else
{
source = createObject("java", "javax.xml.transform.stream.StreamSource").init("file:///#arguments.xslSource#");
}

transformer = tFactory.newTransformer(source);

//if xml use the StringReader - otherwise, just assume it is a file source.
if(Find("<", arguments.xmlSource) neq 0)
{
xmlReader = createObject("java", "java.io.StringReader").init(arguments.xmlSource);
source = createObject("java", "javax.xml.transform.stream.StreamSource").init(xmlReader);
}
else
{
source = createObject("java", "javax.xml.transform.stream.StreamSource").init("file:///#arguments.xmlSource#");
}

//use a StringWriter to allow us to grab the String out after.
xmlWriter = createObject("java", "java.io.StringWriter").init();

xmlResult = createObject("java", "javax.xml.transform.stream.StreamResult").init(xmlWriter);

if(StructCount(arguments.stParameters) gt 0)
{
aParamKeys = structKeyArray(arguments.stParameters);
pLen = ArrayLen(aParamKeys);
for(pCounter = 1; pCounter LTE pLen; pCounter = pCounter + 1)
{
//set params
pKey = aParamKeys[pCounter];
transformer.setParameter(pKey, arguments.stParameters[pKey]);
}
}

transformer.transform(source, xmlResult);

return xmlWriter.toString();
</cfscript>
</cffunction>

There you go - copy paste that funtion, and now you have every all funtionality you could probably ever want when doing an XSL transforamtion.

I will probably shoot this off to cflib.org at some point soon, so you can search for it there as well.

I must say that I am loving the Java integration with CF, it has definately enabled me to do many more interesting things with CF I was previously never able to do before.

If you have any questions / comments / bugs, drop me a line, or post a comment.

Mishoo's Website

I just posted a link for Mishoo's website, simply because of the fact that he probably comes up with some of the coolest DHTML driven, cross browser free software out there.

I personally use his HTMLArea WYSIWIG editor, which is great, and is backed by a large developer community.

I recently came back to the site, as Flex-Mx started talking about his great DHTML Calendar.  It is a seriously nice piece of work, and will probably replace the DHTML Calendar I've been using in some other places.

I also love his business model for free software - feel free to make a suggestion on the product, however, add a donation to the suggestion, and you get more influence.  The more donations given to a particular suggestion, the more likely it is to be added into a product.  In some circles that may be called bribery, but in this case I think it's just smart ;o)

XHTML and '

It took me about 2 hours to work this one out, but it I ended up getting there at the end.

I had ''' in comments on this blog, and they were written as ''' in the source, yet they didn't want to come up as apostraphes.

So I'm scratching my head, and I'm thinking -

1. ''' is a valid XML element.
2. XHTML is valid XML
3. Hence - ''' should display correctly.

That would all be well and good (according to my deductive reasoning) except for the fact that - ' IS NOT VALID HTML.

So - on that note, if you are displaying something on an HTML page - don't use XMLFormat(), as it will escape your apostraphes into a very annoying '''. Use HTMLEditFormat() instead.

That also being said, if you are pulling in text from XML feeds, you may wish to filter out the ''' and replace them with real apostraphes, as quite obviously they won't display correctly.

Just thought I would share this one.

You can see some more at: XHTML 1.0 - HTML Compatibility Guidelines

EDIT :::
I just discovered, that ' actually works as intended in Mozilla based browsers, however not in IE.  I'm not totally surprised mind you.  Just something else to factor into the mix.

Welcome

Hey!

I've finally done it! I've created my own blog.

Welcome to my blog, where I will attempt to discuss ColdFusion, Java, some XML / XSL and probably whatever I am working on at the time.

I'm sure there will be some teething problems, so please bear with me as I sort them all out.

:o)