Tuesday, June 30, 2009

Show Full Database Schema (mysql) via Coldfusion

A lovely gem I just invented:


DATABASE SCHEMA:

<cfquery name="gettables" datasource="#request.pmdb#">
    show tables
</cfquery>

<cfloop query="gettables">
       
    <cfquery name="getcreate" datasource="#request.pmdb#">
        show create table #gettables["#gettables.columnlist#"]#
    </cfquery>
    <br /><br />
    <cfoutput>
        <div style="font-size:16px;font-weight:bold;">#getcreate.table#</div>
        <div style="font-size:12px;">#REPLACE(getcreate["create table"],request.NL,"<br />","ALL")#</div>
    </cfoutput>

</cfloop>


Good wrap!

Sunday, June 28, 2009

Railo - MySQL - Allow Multiple Queries [allowMultiQueries=true]

This has been blogged about by a number of people in the community.


But none that I could find has done it with Railo.... Until now!

Creat a new generic 'Other - JDBC Driver' datasource, then these are the values; Railo's "CLASS" field will be "org.gjt.mm.mysql.Driver", and the "DSN" field will be "jdbc:mysql://{host}:{port}/{database}?allowMultiQueries=true".

Railo's 'CLASS' = ADOBE CF's 'Driver Class'
Railo's 'DSN' =
ADOBE CF's 'JDBC URL'
Ben Nadel has some good screenshots at the first link listed above.


You will first notice that Railo does not have a "Connection String" value for the MySQL datasource as Adobe Coldfusion provides. This was what confused me and sent me hunting and testing until I got the below solution to work successfully.


If you want to modify your existing native MySQL datasource without creating a new generic (
Other - JDBC Driver) datasource, and if your not afraid of getting deep into the Railo back-end config files, this is how to do it in 5 easy steps:


1.a. If your datasource is set up in the Railo Server Admin then navigate to your [RAILO-INSTALL-DIR]/lib/railo-server/context/railo-server.xml
1.b. If your datasource is set up in the Railo Web Admin then navigate to your [WEBSITE-ROOT]/[WEB-INF]/railo/railo-web.xml.cfm

2. Make a back-up copy of the file in case something goes wrong.

3. Open the file in a text editor and locate this xml tag:

<data-sources preserve-single-quote="yes">
(Inside this you will find a tag for each datasource.)

4. Locate the datasource you want to edit.

Find the 'dsn' value and add '?allowMultiQueries=true' to the end of the string, so it is looks somewhat like this:


<data-source allow="###" blob="false" class="org.gjt.mm.mysql.Driver" clob="false" connectionTimeout="20" custom="CharacterEncording=UTF8" database="***DATABASE***" dsn="jdbc:mysql://{host}:{port}/{database}?allowMultiQueries=true" host="***SERVER***" name="***DATASOURCE***" password="***PASSWORD***" port="3306" username="***USERNAME***"/>

5. Then restart Railo and you're in business.


You will notice a change in the Datasource Admin page after completing the above edit.

It will change from "Update datasource connection MySQL" to "Update datasource connection Other - JDBC Driver" as if you had created it it using the method which the above mentioned bloggers have outlined.


That's the wrap.