Railo. Using MS SQL Server 2008 JDBC Driver, version 3.0

While running various performance tests (load balancing, DB drivers, etc.) on an app we're porting to Railo from ACF 8.0.1, I decided to try out the newest JDBC driver from Microsoft for SQL Server 2008 (version 3.0 of this driver was released in April 2010), as the built-in MS SQL server driver notes that it is for SQL Server 2005 (or so say my error logs).

I had yet to use the "Other" option in the Railo Web Administrator for a datasource and wanted to record what steps I took for later (or in the event someone else might be interested in the same). The process itself is pretty simple:

Download the newest driver from Microsoft: http://www.microsoft.com/downloads/details.aspx?FamilyID=%20a737000d-68d0-453...
Unpack/unzip the downloaded file. For me, expanding the archive resulted in a folder called sqljdbc_3.0
Inside the expanded folder is another, called enu (or whatever language was selected with the download from step 1), which contains, among other items, a file called sqljdbc4.jar
Copy or move sqljdbc4.jar to your Railo 'install' folder (I use Railo on Tomcat and, for me, this is at /Developer/tomcat/railo) ... it'll be the folder that contains a railo.jar file :)
Restart Railo
Open the Railo web administrator for your app
Go to the Datasource page and enter your datasource name, selecting "Other - JDBC Driver" from the Type drop-down
Enter your username and password as required by your DB on the ensuing page ('Create new datasource connection Other - JDBC Driver')
For the Class field, enter: com.microsoft.jdbc.sqlserver.SQLServerDriver
For the DNS field, enter: jdbc:microsoft:sqlserver://SERVERNAMEORIP:1433;DatabaseName=DBNAME

That's it. Not much to it, I know. However, I was not sure of what the right class name was for the MS driver and how to create the connection string for the DNS ... and, as I mentioned above, wanted to note it for future reference.

For anyone wondering, the performance of Railo's built-in Microsoft driver (vendor Microsoft) was nearly identical to the newer JDBC 3.0 driver. I used a pretty intensive query (lots of calculations, etc.) as my unofficial test case. The connection via the 3.0 driver was typically, but not always, a few milliseconds faster. When it was faster, it truly was negligible ... we're talking differences such as 437 ms for the 3.0 driver to 454 ms for the built-in driver.

Even though there wasn't any true performance gain, it's good to know how to deploy and use other JDBC drivers!

Posted via email from Craig Kaminsky's posterous

Comments

Anonymous

FWIW Class should be: com.microsoft.sqlserver.jdbc.SQLServerDriver
and DNS should be jdbc:sqlserver://SERVERNAMEORIP:1433;DatabaseName=DBNAME

Craig Kaminsky

@Ciaran: Thanks for your comment and glad that the post was able to help you in some way!

Anonymous

Thanks, I was looking at the 'Vendor Microsoft' and wondering about performance - this has given me the answer I was looking for.

Cheers, Ciaran

ColdFusion to Railo. Interesting Issue with Flex Remoting

I am in the process of converting a Flex application currently backed by Adobe ColdFusion 8 and LiveCycle Data Services to one that is backed by Railo 3.1.2 and BlazeDS. In so doing, I ran into an interesting issue that I thought might be helpful to record in case anyone else runs up against it.

The conversion from ColdFusion to Railo was, mostly, seamless. However, when I began connecting the Flex UI to Railo/BlazeDS, I hit a snag.

The problem I encountered was with remote calls that passed one or more parameters to the server. In every such instance, Railo responded with an error that went a little something like this:

FaultEvent fault=[RPC Fault faultString="flex.messaging.MessageException: invalid call of the function MYFUNCTIONNAME (/path/to/my/remote/cfc/remoteservice.cfc), first Argument (ARG) is of invalid type, can't cast Object type
[Struct] to a value of type [numeric|string|etc.]." faultCode="Server.Processing" faultDetail="null"]
messageId="DDCBCF4C-90AC-E431-6195-3020C9C24765" type="fault" bubbles=false cancelable=true eventPhase=2]

The issue turned out to be a key difference in how ColdFusion and Railo (or LCDS and BlazeDS) handle named parameters passed to the remote method as an object. Consider the following remote method invocation from our Flex application:

remoteBlogObject.getBlog(   {     blog_id:_blogID,      start:_start,      end:_end   } );

Our Flex developers create an object with a key/value pair for each parameter the remote CFC method requires and send that along with the call.

ColdFusion 8/LCDS takes this object and converts it, in the case of the example above, to three distinct parameters (blog_id, start, end) which then get passed to the CFC's method.

Railo, on the other hand, receives this same object and converts it to a java.util.Map; the Java object on which a CFML struct is based. As a result, Railo sends a single parameter, typed as a struct, to the CFC's method. The struct sent to the method by Railo/BlazeDS does, in fact, contain the right keys (blog_id, start, and end) and values, just not as separate parameters.

While there are certainly more options, we opted to tackle this issue in one of two ways: (1) Change all the remote CFC methods to expect a single parameter (type="struct") or (2) Update the remote calls from Flex to not pass an object; rather, just pass in the parameters.

Thankfully, our team decided to go with option 2 as this approach required less overall code refactoring than option 1 and, more importantly, makes our Flex remoting calls consistent and usable with ColdFusion 8, ColdFusion 9 and Railo (maybe OpenBD, too?).

Side note: While the automagic conversion employed by ColdFusion seems helpful on the surface, I can't help but think it's more detrimental than good. If I'm passing a generic object ( {giggity:_giggity} )from a remote method, I would expect this same object to be an object (or struct in CFML) when it gets to my CFML server. I'd certainly be interested to know what other people think of this sort of "assistance", especially given the increasing interest in both a standardized CFML and open-source CFML engines.

Posted via web from Craig Kaminsky's posterous

Comments

cpt_pete

Thanks for posting this! I'm trying to convert from CF to railo and have hit the same problem (albeit with flashremoting service and NetConnection).

Think it's also worth noting that railo (and maybe CF, havn't tried) requires the cfargument tag to be able to parse your values when your passing paramaters like this.

It might be obvious but I was just passing the argumentsCollection to a subsequent function and was getting fustrated to see my arguments were not present.

Cheers!

jason olmsted

It seems that the problem of pre-setting up the object/struct in Flex is that you lose typing information on the actionscript side of things anyway. So, since you aren't strongly typed in the strongly-typed environment, what's wrong with a bit of duck-typing on the CF side? If you are ok with that, the way to do it, I would think, is to just get rid of named parameter passing in the cfc function definition and then the singular object call ought to be referenced by the "arguments" object. The behavior ought to be consistent between CF and Railo (though I don't have any flex-railo as of yet; rectifying that very shortly)

For what it's worth, when calling cfc from Flex, I usually skip explicitly referencing the named parameters and just specify them by position. It seems to do the trick and ought to not suffer from compatibility iddues.

Railo. Using MS SQL Server 2008 JDBC Driver, version 3.0

Comments

Disclaimer

Author

Craig Kaminsky

Railo. Using MS SQL Server 2008 JDBC Driver, version 3.0

Comments

Disclaimer

Older · View Archive (109)

ColdFusion to Railo. Interesting Issue with Flex Remoting

Comments

Newer

New Projects S3 Bucket

Comments

Author

Craig Kaminsky