Sunday, August 05, 2007

ColdFusion 8 and Apache Derby - Create and Drop Tables and Views

So today I decided to mess with the Embedded Derby Database in ColdFusion 8. I wanted to figure out how to check for table and view existence and how to create tables and views. So below I will explain how I did it along with my test code at then end.

OK, so first we have to create the new database, which is very easy as explained by Ben Forta in his Blog. The only thing it is actually easier because the final release does have the create checkbox so you do not have to add create=true to the advanced settings string.(Pictured Below)


Once you complete the steps above, your database is ready to use. So now we create some tables and views. First, I wanted to check the existence of tables and views so I can drop items if they exists. This is easy now with the new <cfdbinfo> tag, which lets you retrieve information about a data source, including details about the database, tables, queries, procedures, foreign keys, indexes, and version information about the database, driver, and JDBC. (info on cfdocs).

I then use the results to check if the tables I want to create currently exists, good practice as you are building your DB, because if you try to create a table that already exists the process will break. So therefore, I save the results to a list that I later use to check for existence (I wanted to just use ListFind rather then write a query of a query everytime I wanted to check). After that, I just used SQL code to create my tables and views. To find out more about Apache Derby go to Apache Derby Docs, I used the manuals to see how the CREATE STATEMENTS had to be formed.

I believe the code below explains it all. Now there is no more need for Access Databases for small projects, Woo Hoo!!!!! I included an image of the output after the code ....

<!--- Use cfdbinfo to get the tables of the database --->
<cfdbinfo type="tables" datasource="myDerbyDB" name="dbdata">
<!--- Check if USERS table exists --->
<cfquery name="tableExists" dbtype="query">SELECT * FROM dbdata WHERE TABLE_TYPE='TABLE'</cfquery>
<cfquery name="viewExists" dbtype="query">SELECT * FROM dbdata WHERE TABLE_TYPE='VIEW'</cfquery>
<cfscript>
// Save results into list values to check for existance
tables=valueList(tableExists.TABLE_NAME);
views=valueList(viewExists.TABLE_NAME);
</cfscript>
<!--- Drop Views First Due to Dependency Check --->
<cfif listFindNoCase(views,'VIEW_CARS_RELATED')>
<cfquery name="dropView1" datasource="myDerbyDB">DROP VIEW VIEW_CARS_RELATED</cfquery>
</cfif>
<cfif listFindNoCase(views,'VIEW_CARS_ALL')> <cfquery name="dropView1" datasource="myDerbyDB">DROP VIEW VIEW_CARS_ALL</cfquery>
</cfif>
<!--- Drop Tables if exists --->
<cfif listFindNoCase(tables,'CARS')>
<cfquery name="dropTables" datasource="myDerbyDB">DROP TABLE CARS</cfquery>
</cfif>
<cfif listFindNoCase(tables,'COMPANY')>
<cfquery name="dropTables" datasource="myDerbyDB">DROP TABLE COMPANY</cfquery>
</cfif>
<!--- Create Tables --->
<cfquery name="createTables1" datasource="myDerbyDB">
CREATE TABLE CARS
(
CARID INT NOT NULL GENERATED ALWAYS AS IDENTITY( START WITH 1,INCREMENT BY 1),
COMPANYID INT NOT NULL,
CARNAME VARCHAR(100),
PRIMARY KEY (CARID)
)
</cfquery>
<cfquery name="createTables2" datasource="myDerbyDB">
CREATE TABLE COMPANY
(
COMPANYID INT NOT NULL GENERATED ALWAYS AS IDENTITY( START WITH 1,INCREMENT BY 1),
COMPANYNAME VARCHAR(100),
PRIMARY KEY (COMPANYID)
)
</cfquery>
<!--- Create Views --->
<cfquery name="createView1" datasource="myDerbyDB">
CREATE VIEW VIEW_CARS_RELATED
AS
SELECT CARS.CARID,CARS.CARNAME,COMPANY.COMPANYNAME
FROM CARS
INNER JOIN COMPANY ON COMPANY.COMPANYID=CARS.COMPANYID
</cfquery>
<cfquery name="createView2" datasource="myDerbyDB">
CREATE VIEW VIEW_CARS_ALL
AS
SELECT CARS.CARID,CARS.CARNAME,COMPANY.COMPANYNAME
FROM CARS
LEFT OUTER JOIN COMPANY ON COMPANY.COMPANYID=CARS.COMPANYID
</cfquery>
<!--- Loop to create the first 10 cars --->
<cfloop from="1" to="10" index="i">
<cfquery name="insertCars" datasource="myDerbyDB">
INSERT INTO CARS (COMPANYID,CARNAME) VALUES (#i#,'CAR_#i#')
</cfquery>
</cfloop>
<!--- Loop to create the first 10 companies --->
<cfloop from="1" to="10" index="i">
<cfquery name="insertCompanies" datasource="myDerbyDB">
INSERT INTO COMPANY (COMPANYNAME) VALUES ('COMPANY_#i#')
</cfquery>
</cfloop>
<!--- Add one more record without relation to a company --->
<cfquery name="insertCar" datasource="myDerbyDB">
INSERT INTO CARS (COMPANYID,CARNAME) VALUES (0,'CAR_11')
</cfquery>
<!--- Get Cars with Companies --->
<cfquery name="getCars1" datasource="myDerbyDB">SELECT * FROM VIEW_CARS_RELATED</cfquery>
<cfquery name="getCars2" datasource="myDerbyDB">SELECT * FROM VIEW_CARS_ALL</cfquery>
<!--- Dump the queries --->
<cfdump var="#getCars1#">
<cfdump var="#getCars2#">

Saturday, July 21, 2007

Sirius Player For Mac!

So today I was browsing thru the apple downloads section and found SiriusMac, which is a slick easy to use Radio Interface that streams Sirius Radio to your mac and it is Free! This is great because normally, I have to open up a browser, navigate to the sirius site, select the player (I have a bookmark but still), login then select the station and there you go. I know it is not much but it is still tedious. I wrote a blog entry on this too because they stream using windows media on their site that didn't work well with my Mac which required me to get Flip4Mac WMV (another great application) and firefox would always send me an alert about a missing plug-in - drove me crazy. As far as Safari, it didn't work great either, sometimes the player would reposition itself - crazy. So lately, I started bringing up Sirius on my PC via parallels, as I always have it on while developing (so imaging the steps just to hear Sirius on my Mac).

This player is great, just go thru the simple set up and bam! You got Sirius radio on your mac (Sirius Subscription Required). I love it! I suggest if you use this app and really appreciate what they've done, that a small donation is made, they deserve it!

The application, I believe was built using python.

To get it just go to the following links...

Apple Downloads

or

SiriusMac Site

Until next time ...

UPDATE: SiriusMac is programmed entirely in AppleScript Studio.

Thursday, June 28, 2007

iTunes Store Album Browser Window function in HTML

Ok so I had a hard time figuring what to call this entry, so if I confuse many of you I apologize, but I did warn before that I am not a great writer. The reason for this entry is for a feature I was asked to do not to long ago and failed miserably at. The feature was to create a scroll area that was similar to iTunes album browse interface (pictured below).

Basically you have a content area that displays a certain amount of items and when you click on either the left or right arrow all the contents shift to show the new ones. This is easy to do in Flash, but I needed an HTML equivalent of it as this site was not in Flash. So first I set out to define the divs that would encompass this feature (picture below).

So what exactly is this above? Let me explain. The entire viewable area is what I defined as "frame" this will hold all the child elements such as the left and right buttons, the scrollable area ("scroller") and the content area ("scrollcontent"). Once this was set up I decided to use my favorite animation library (script.aculo.us) and javascript framework (prototype).

My first attempt I used was with the "Photoshop Masking" mentality. Basically, the scroller div would be a defined width div with overflow set as hidden and the content area would be wider child div displaying the content. The scroller div would act as the mask as I move the content area x position. This first attempt worked great on all browsers expect for GUESS WHO!? Yeap, IE. Every time I would call the animation the child div would go to the top level and the masking of the parent div would no longer be active. This only happened in IE and at the time I could not figure out why so I decided to go with another way to display the data. Of course, this drove me crazy and I needed to make it work, so eventually I ran into the "Panic - Coda" website, where they had implemented this exact feature. I needed to know how they did this, so I decided to inspect their javascript files.

I am not going to explain exactly how they did it all, instead I decided to take it to the simplest form. The example I created will allow you to go left and right with the arrows until you reach the beginning or end of which at that point the arrow that represents further scrolling will disappear (this is not available in theirs). Inspecting their files taught me the following.

  • ScrollLeft: gets or sets the number of pixels that an element's content is scrolled to the left. This is what makes this possible as it can be used in divs where there is an overflow, even if it is set to hidden. There is also a ScrollTop property if you want to scroll vertically.
  • The use of Robert Penner's Easing Equations, the source is in actionScript but is easily implemented as JavaScript functions/
  • The use of setInterval in javascript - I had no clue, just like actionScript

Compared to my first draft, this new one actually never moves the position of the scroll content div, instead it changes the scroll position of the scroller div. This now works in all browsers! Yeah!

In the end I created 2 examples, one implemented by using part of Panic's Coda Website javascript functions (again simplified) and the second one using the Yahoo UI Library. I know I said I favored script.aculo.us but in the end I am a Coldfusion Developer and by what I heard some of the new ajax functions are using this library, so why not take it for a spin. I have to say the Yahoo UI Library is pretty freaking slick. Anyways, click below to view the example, the source files do good of explaining how everything works but if anyone needs help understanding just let me know and I will clarify.

Until next time ...

Monday, June 18, 2007

URL Rewrite FOR IIS with RegEx support and it isFREE!!!

Well, as I was learning how to do SES (Search Engine Safe) URLs with Coldfusion and IIS , I learned that CF had a built in feature that allowed you to do this. The only problem was that it still required you to have the *.cfm file in your address and your variables/values following separated by "/" rather than the default url patterns.

ie. http://mysite.com/index.cfm/myVariable/itsValue/
translates to
http://mysite.com/index.cfm?myVariable=itsValue

Honestly, this was fine for me, but I wanted to have the ability to rewrite URLs outside of Coldfusion, like Apache's mod_rewrite, which in turn would allow me to create true pretty URLS.

ie. http://mysite.com/theValue/
translates to
http://mysite.com/index.cfm?theVariable=theValue

I did some research online regarding ISAPI filters for IIS but most lacked Regular Expression Support unless you were willing to fork out some extra money for paid for versions. Then, Ray Camden introduced me to IIRF (Ionic's ISAPI Rewrite Filter), a small, cheap, easy to use, URL rewriting ISAPI filter that combines a good price (free!) with good features.

This product is amazing, not only because of price (I did mention it was free right?) but implementation is easy. Their directions are thorough and supply enough examples that you will be up and running in minutes. Thanks for the info Ray!

Click here to go to Ionic's Site

Until next time ...

SES Not Enabled in CF8 by Default

This is a follow up for my previous post regarding the ability to create SES (Search Engine Safe) URLs with Coldfusion. If you are using the beta version of Coldfusion 8 (aka: Scorpio), these setting are not on by default. You will have to open up your web.xml file and make uncomment the following lines;
<!-- begin SES --->
  <servlet-mapping id="coldfusion_mapping_6">
     <servlet-name>CfmServlet</servlet-name>
     <url-pattern>*.cfml/*</url-pattern>
  </servlet-mapping>
  <servlet-mapping id="coldfusion_mapping_7">
    <servlet-name>CfmServlet</servlet-name>
    <url-pattern>*.cfm/*</url-pattern>
  </servlet-mapping>
  <servlet-mapping id="coldfusion_mapping_8">
    <servlet-name>CFCServlet</servlet-name>
    <url-pattern>*.cfc/*</url-pattern>
  </servlet-mapping>
<!--- end SES -->
You will also notice that the mappings are now called "coldfusion_mapping_#" rather than "macromedia_mapping_#" as noted in the adobe tech note : ColdFusion MX 7 and Search Engine Safe (SES) URLs You can find the web.xml in one of the two places:
  • Standalone Install:cf_root\wwwroot\WEB-INF
  • EAR\WAR installation:application root\cfusion-ear\cfusion-war\WEB-INF
Remember you must restart your server to have the changes take place.

Until next time ...

Saturday, June 16, 2007

Changing From CF8 to CF7 on the Fly with IIS

Hello again, this is a follow up to my previous post on installing CF8 on an Existing CF7 Multi Server Setup. After you implement your CF8 site and CF7 site with the "Web Server Configuration Tool", open up your IIS Manager and right click on each of the sites to open up their properties (click on sample picture below).

Once you have both sites up click on the "Home Directory" tab, go to the bottom of the panel and click on the "Configuration" button to open up the Application Settings for each site (click on sample picture below).

As you will notice in the pictures (or your own properties panel - if following along), in the Application extensions on both sites all file extensions will point to the jrun_iis6.dll in your JRun\lib\wsconfig folder. But if you notice in the bottom section for Wildcard application maps, each site will point to a separate subfolder inside your JRun\lib\wsconfig folder. Basing this on my images, my CF7 server points to 1\jrun_iis6_wildcard.dll and my CF8 server points to 2\jrun_iis6_wildcard.dll, yours may look different, if you have configured more sites.

This was something I figured out just by trying it, but if you click on Edit and change this values from 1 to 2 or vice-versa on these servers, you will automatically change what CF engine it is using. One more thing, you don't even have to restart the webserver, just make the change, OK your way out and refresh your browser and Bam! Change Done! I tested this using a simple <cfdump var="#server#" /> to see the changes.

Anyways, I though this was pretty cool if you want to jump back and forth between versions. Alternatively, you can just create 2 IIS sites that point to the same directory, with one using 7 and the other using 8 and either use host headers or different IPs to differentiate. That is how I have it setup but I still wanted to share this little simple trick I figured out.

Until next time...

SES (Search Engine Safe) URLs w/ Coldfusion

Another one of my ventures today was finally learning how to implement search engine safe URLs.  If you are a Coldfusion Developer like me, I am glad to let you know that this is an easy task to overcome. While taking some time to read Raymond Camden's BlogCFC documentation, since I saw it was using SES URLs, I took notice of the following Adobe Tech Note : "http://www.adobe.com/go/2addd247". It basically tells you how to make changes to your web.xml file to allow this. He makes note of this in case your server had problems with the BlogCFC SES URLs and needed to do the change as noted by Adobe. When I inspected my web.xml file, the required change was already done.

Now that I knew how to enable it, I needed to know how to do it. One way is to inspect Ray's work inside his parseses.cfm file or another great blog entry I found was Clint's Blog : Coldfusion SES URL. This entry pretty much sums it all up and allows you to begin your way to developing with SES URLs.

There are other ways to do this with the webserver itself. Apache has mod_rewrite, but alas I use IIS6. For IIS 3rd party ISAPI filters are available and you can find out more by clicking here (Some IIS URL Rewriting Links).  For me, I think the built in solution with CF is sufficient. I love Coldfusion!!!!

Until the next time ....

Compression in IIS6.0

So today, I decided to begin messing around with a couple of things of which I will post about. This first one was compression in IIS 6.0, I know apache has built in modules for this, even some more that I will reference in the next entries, but I don't use apache, I use IIS, so here goes.
As I was searching for any reference to this subject I found an excellent one from Scott Forsyth, labeled "IIS Compression in IIS6.0". Anyway, since it is his teachings and not mine, I will not go into detail on how to do this process, I believe he deserves the credit and your attention so just click on the link to go to his blog entry. 
Just to prove that his walk thru works and that you can see for yourself, below are two images using Firebug. The page is a pretty simple page that I was using to test SES (Search Engine Safe) URL's (My next entry). You will notice the first one was originally 7k and the compressed version came thru at 2k. I know it is a small example but with a compression of over 60%, I can only assume great results are to come to all files of all sizes.
One more note, his final 2 lines show you how to enable and disable this feature to a specific site via the command line. It is instant no restart of the webserver required, the test I show below was done one after the other with that simple command. So in case it is giving you problems, you have the assurance that you can remove it on the fly.
NO COMPRESSION EXAMPLE
COMPRESSION EXAMPLE
Until the next time ....

Friday, June 15, 2007

Installing CF8 on Existing CF7 Multi Server Setup and use JRE 1.5

So tonight, I found myself searching for a little help on this subject and came up with one blog referencing a side-by-side CF7/CF8 stand alone installs using Apache. It seems pretty easy but the problem I faced was that I am using IIS and I have my CF setup as Multi-Server JRun configuration. If the first scenario I gave seems like what you need to do, then you can click here to go to that blog entry, if not read on. So let's start this little mission. First, I started by creating a new instance on my JRun server. I've read instructions from Adobe (Macromedia actually back then) on how to do this, but instead of trying to find that article I will guide you thru my steps.
STEP 1: CREATE NEW SERVER INSTANCE IN JRUN
  1. Login to JRun Administrator
  2. Click on "Create New Server"
  3. When the window pops-up enter your JRun Server Name. I named mine cfusion8.
  4. After you type your server name and either click on tab or click on "JRun Server Directory", you will notice it pre-populate. Leave this as is, as it will create your named directory in your servers folder inside your JRun folder.
  5. Click on create server and you will get a confirmation page with all your ports for your server. At this point there is no need to do anything else and click on Finish.
DO NOT START THE SERVER YET!

Ok, so now that we created our server, navigate to your JRun Servers folder (i.e. C:\Jrun\Servers\). Inside that folder you will notice a new folder named cfusion8. Go into the folder and delete the default-ear folder. You will still have one folder inside named SERVER-INF, leave that folder alone. For now, this completes creating the server where your CF8 installation will go, now we have to go create the EAR file from the CF8 install. The steps are as follows;

STEP 2: INSTALL CF8 AS EAR

  1. Download CF8 Beta from Adobe Labs
  2. Run Installation and Select Deployment option (3rd option) and select EAR file.
  3. Some important notes on your installation. The first option is where the installer will place your ear file (so remember the location) and when selecting your context root, the default in the installer is cfusion, but if you want it at the root specify it when it asks you "\". Basically make sure to read everything as you are going step by step.
  4. Once the installer finishes navigate to the folder where the ear file is. You should see a file named cfusion.ear. You will need to extract this file, there are some instructions on how to in the included read me file.
  5. When you extract the file make sure to change the name of the extracted folder to cfusion-ear. Then go into the folder and extract the cfusion.war file. The same applies here, when the extraction is complete make sure the name of the folder is cfusion-war.
  6. Navigate into the WEB-INF folder, open the application.xml file and change the web-uri from cfusion.war to cfusion-war.
  7. Navigate out and copy the entire cfusion-ear folder to the %JRun%\Server\cfusion8\ folder. (The folder we deleted the default-ear file from)

At this point you can go to your JRun Admin and start the server. Once you start the server, connect to it using the specified HTTP port for the server in the JRun settings (i.e. http://locahost:8301/CFIDE/administrator). This should get you to the initial CF page that finishes the installation. Once the installation is complete, login to your CF8 server.

OK, so at this point I was excited but then I navigated to my Server Settings and noticed the Java Version in use was 1.4.2_09 (pictured below), same as my CF7 install. This happened because my CF8 install was using the same JVM as my CF7 install and the 1.4 JRE in JRun. So there are a couple of things I had to do here, but before that. I am assuming that you are using IIS and using more than one website on your server. So open up the"Web Server Configuration Tool" (pictured below) and select the IIS sites that will use your new CF8 install and leave the CF7 sites alone. Once new JRE is selected I kept getting an error when trying to config, but there is a work around for it as well, but for config your sites at this point.

SERVER SETTINGS
Web Server Configuration Tool
At this point I needed to figure out how to use a different JVM and how to install the new 1.5 JRE on my server. So first install JRE by going to sun and selecting the JDK 5.0 Update 12 version (click here to go to download page). You will need this version as the JRE includes the server folder that is required, the SE version only has the client folder. I am sorry if this makes no sense but trust me I went thru it already. Now the next steps I used some information from another blog entry to create what I needed. I will summarize my steps below but the blog entry I referenced was Mark Kruger's "Coldfusion Muse - Including Custom JVM Settings "
  1. Create a new JVM file - I called mine jvm.config_five
  2. Inside the JVM file change the java.home value to the folder where the new jre is installed (i.e. java.home=C:\\Program Files\\Java\\jre1.5.0_12\\jre)
  3. Create a new windows service using the command line jrunsvc utility in the JRun/bin/ folder - use the following command.
  4. C:\JRun4\bin>jrunsvc -install cfusion "Adobe JRUN CFusion Server" "Adobe JRUN CFusion Server" -config jvm.config_five
At this point go back to your JRun Admin and STOP THE CFUSION8 SERVER. Once it has stopped, start your server via the windows service. If you get an error go look in the jrun4/logs directory for the cfusion8-out.log and the cfusion8-err.log. If everything goes perfect you should be able to login into your CF8 admin and see the new JRE version referenced (pictured below). At the same time you can log into your CF7 server and notice it is still running the 1.4 version as it is using the default JVM.
CF8 WITH JRE 1.5
Now, some final notes. In order for your CF8 install to use the 1.5 JRE, you must start the server via the windows services. Starting your server via the JRun Admin will make it use the default JVM and will load using the 1.4 JRE. If you need to work with the "Web Server Config Tool" to add more sites to your CF8 install, then this is something you want to do. For some reason when I attempted to work with the tool while the CF8 instance was using the 1.5 JRE I would get an mBean error. Honestly, I do not know what it means.
I did a simple cfdump on the website I configured for CF8 just to test and it worked showing version 8.  I hope this helps who ever reads this post, I know I am not great at explaining and that may be the reason I don't post much, but I will definitely try to make this good practice.
Now that I have my beta install on my dev server I will begin playing around and post any bugs I may run into with this config and maybe some CF8 posts of my own.  I am also working on Flash a lot lately and trying to get my hands around Flex 2 and well now Flex 3. So you may notice different entry types in this blog, but as it states, this is a Chronicle of a Developer.

Until next time .....

Thursday, January 25, 2007

Remove that wretched "Missing Plug-In" Alert from Firefox

Well, this is probably more of an issue for Mac Users (like me) using the Flip4Mac WMV plugin to play windows media. Apparently, every time I wanted to listen to Sirius online, it worked with no problem but that wretched alert kept popping up.

So what did I do? I went to mozilla.com and went to support and found a simple and great solution I did not know about. This may be no news to some but for those like me check out the next link.

Disable Plug-In Article

Basically, you type about:config in your address bar and a whole bunch of configuration options pop up that you can set. Look for one called plugin.default_plugin_disabled and click on it to set it to false. That is it, restart firefox and voila! Wretched alert is gone! Remember though, if you do this you will not get that alert for any plug-in, but what the hell, you should know what you are opening up anyway.

Hope this helps someone. Firefox Rules, Safari is Great, IE needs an execution.