>> GreenByte.info By Nick Tong (quiksilv) | Supported by: TalkWebSolutions.co.uk

ORM - what the heck!

Posted At : 20 February 2007 19:17 | Posted By : quiksilv
Related Categories: Transfer, Frameworks, SQL, ColdFusion

Mark Mandel has been interviewed over on cfFrameworks.com about his Object Relationship Model Transfer framework, covering where it sits within the application layer and he talks about some of the functions such as decorators.

If you're tired of creating all the SQL for you data abstraction then you need to check out Transfer. Pop over to the site to check the interview podcast out.

Comments (0) | Print | Send | del.icio.us | Linking Blogs | Digg | 3876 Views

mySQL GUIs

Posted At : 09 October 2006 12:13 | Posted By : quiksilv
Related Categories: SQL

I've been doing work with mySQL for the last week or so and forgot which GUIs I've used in the past to work with it. Apart from notepad here are some that I've looked at:

mySQL Administrator

link

The update from mySQL control center; this is easy to use and has a host of features including query browser, and a command line client. It's also free so this has to go on your download list.

SQL yog

link

This is great - again it's free and easy to use. One of the great things that this has is SQL compare - although this feature is only in the enterprise version.

Navicat

link

This is mySQLs answer to MSSQLs Enterprise Manager. With a nice query builder it a quick query creator.

NB: It's worth noting that i've just glanced at these products but I like what i've seen so far.

Comments (6) | Print | Send | del.icio.us | Linking Blogs | Digg | 8121 Views

Tables, Views and Stored Procedures via MSSQL information_schema

Posted At : 21 August 2006 13:53 | Posted By : quiksilv
Related Categories: SQL

Recently I posted about using the INFORMATION_SCHEMA table to recover data from a a ISP that had shut me down (see link below). I thought I would just post a summary of how to get tables, views and stored procedures:

To get tables use:

select *
FROM INFORMATION_SCHEMA.TABLES

To get views use:

select *
FROM INFORMATION_SCHEMA.VIEWS

To get stored procs use:

select *
FROM INFORMATION_SCHEMA.ROUTINES

To get columns from a table including defaults etc use:

select *
from information_schema.columns
where table_name = 'your_table_name'

Comments (1) | Print | Send | del.icio.us | Linking Blogs | Digg | 5549 Views

Database recovery via Coldfusion SQL console

Posted At : 25 July 2006 15:58 | Posted By : quiksilv
Related Categories: SQL, ColdFusion

Recently one of my machines crashed which was the only local machine that had a certain MSSQL database on it. It's a rather old and unused system, but missed greatly when it's not working.

For whatever reason that I won't get into here I had an issue with an ISP where I couldn't get access to my database to copy it back locally. This really annoyed me but I was determined not to let this beat me.

I still had access to the site where I had created a SQL console (a page where I could enter SQL code and it would get executed). I decided that I could use this to query the database and then with this data I could create a script to create the database locally.

Here is the SQL console:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>SQL Console</title>
<link rel="stylesheet" href="sql_console.css" type="text/css">
</head>

<cfif IsDefined("FORM.SQL")>
   <!--- Execute submitted SQL statement --->
   <cftry>
      <cfquery name="SQL" datasource="#dbdatasource#">
      #PreserveSingleQuotes(FORM.SQL)#
      </cfquery>
   <cfcatch>
      <cfset ErrorMessage = "#CFCATCH.message#<br>#CFCATCH.detail#">
   </cfcatch>
   </cftry>
</cfif>

<body style="margin:5px"><cfoutput>

<form name="console" action="console.cfm" method="post">

<table border="0" cellpadding="4" cellspacing="1" bgcolor="##000000" width="100%">
<!--- SQL input --->
<tr>
   <td bgcolor="##99CCCC" valign="top">SQL</td>
   <td bgcolor="##FFFF99" colspan="2" valign="top"><textarea name="sql" cols="100" rows="10"><cfif IsDefined("FORM.SQL")>#FORM.SQL#</cfif></textarea>&nbsp;<input type="submit" value=" Run > "></td>
</tr>
<!--- SQL messages/errors --->
<tr>
   <td bgcolor="##99CCCC" rowspan="2" valign="top">Messages</td>
   <td bgcolor="##CCFFCC" valign="top">Messages</td>
   <td bgcolor="##CCFFCC" valign="top">Errors</td>
</tr>   
<tr>
   <td bgcolor="##FFFFCC" valign="top" width="50%">
      <cfif IsDefined("FORM.SQL") and not IsDefined("ErrorMessage")>
         Query executed successfully
         <cfif IsDefined("SQL.RecordCount")><br>#SQL.RecordCount# records returned/processed</cfif>
      <cfelse>
         &nbsp;
      </cfif>
   </td>
   <td bgcolor="##FFFFCC" valign="top" width="50%">
      <cfif IsDefined("ErrorMessage")>
         #ErrorMessage#
      <cfelse>
         &nbsp;
      </cfif>
   </td>
</tr>
<!--- SQL results --->
<tr>
   <td bgcolor="##99CCCC" valign="top">Results</td>
   <td bgcolor="##006699" colspan="2" valign="top">
   <cfif IsDefined("FORM.SQL") and IsDefined("SQL.ColumnList")>
      <table border="0" cellpadding="3" cellspacing="0">
      <tr>
         <cfloop index="col" from="1" to="#ListLen(SQL.ColumnList)#"><td bgcolor="##006699">#ListGetAt('#SQL.ColumnList#', col)#</td></cfloop>
      </tr>
      <cfset bgcolor="006699">
      <cfloop query="SQL">
      <tr>
         <cfloop index="col" from="1" to="#ListLen(SQL.ColumnList)#"><td bgcolor="###bgcolor#">#Evaluate("SQL.#ListGetAt('#SQL.ColumnList#', col)#")#</td></cfloop>
         </td>
      </tr>
      <cfset bgcolor=Iif(bgcolor eq "006699", DE("003366"), DE("006699"))>
      </cfloop>
      </table>
   </cfif>   
   </td>
</tr>
</table>

</form>

</cfoutput></body>

</html>

With this is place I ran the query to extract the database data for the tables:

[More]

Comments (2) | Print | Send | del.icio.us | Linking Blogs | Digg | 6493 Views

SQL Server 2005 Upgrade Guide

Posted At : 17 July 2006 12:28 | Posted By : quiksilv
Related Categories: SQL

If you're thinking about upgrading to SQL 2005 then this is a must read.

SQL Server 2005 Upgrade Technical Reference Guide

Another good link is for the Upgrade Advisor

Thanks to Robert Sindall for pointing these out to me.

Comments (0) | Print | Send | del.icio.us | Linking Blogs | Digg | 3984 Views

RDS plugin for cfeclipse

Posted At : 25 May 2006 21:58 | Posted By : quiksilv
Related Categories: Technology, SQL, ColdFusion

So I finally decided to install the RDS plug-in for cfeclipse. I have to say that it's great getting all your enviroments in one package and a great kicker to the plug-in is it has a built-in query editor, sweet!

The following 2 link helped me out loads: follow in order
socialpoints.com
labs.macromedia.com

Comments (5) | Print | Send | del.icio.us | Linking Blogs | Digg | 5128 Views

a web developers cheat sheet list

Posted At : 21 March 2006 20:34 | Posted By : quiksilv
Related Categories: Technology, JavaScript, Frameworks, SQL, ColdFusion, Model-Glue, Fusebox

Here is a list of some great cheat sheets. Last updated 15/04/2007.

If you know of any please add them to the comments below. Lets help each other out.

Web Dev Cheat Sheets

Databases / SQL Cheat Sheets

Language Cheat Sheets

Javascript

Version Control Cheat Sheets

Framework Cheat Sheets

Other cheetsheets

Commercially Printed CheatSheets

Apollo

Please do add more to the comments and I will update... need to get more cheat sheets for frameworks!

Comments (18) | Print | Send | del.icio.us | Linking Blogs | Digg | 121559 Views

SQL Injection Attacks

Posted At : 23 December 2005 11:12 | Posted By : quiksilv
Related Categories: SQL, ColdFusion

Ben Forta has written a great artical here:SQL Injection Attacks, Easy To Prevent, But Apparently Still Ignored
If you are not param-ing your variables then i suggest that you stop everything now and take a look!

Comments (0) | Print | Send | del.icio.us | Linking Blogs | Digg | 1837 Views

SQL Server 2005 Setup and Deployment

Posted At : 13 December 2005 09:45 | Posted By : quiksilv
Related Categories: SQL

Robert Beatty has written a nice artical on setting up SQL 2005.

SQL Server 2005 setup is designed to minimize installation failures and improve the overall setup experience. His artical introduces the concepts related to SQL Server 2005 setup and deployment. Topics covered include which edition is right for you, the hardware and software requirements, and walks you through the upgrade process.

Comments (0) | Print | Send | del.icio.us | Linking Blogs | Digg | 1539 Views

Error establishing socket. Connection refused

Posted At : 08 December 2005 11:58 | Posted By : quiksilv
Related Categories: BlogCFC, SQL, ColdFusion

So recently i've been trying to run the blogCFC locally on my laptop so I can update it when i'm not online and then synchronise it with my live machine when I hook back up with the WWW.  I had quite a major time with this as the coldfusion administrator couldn't see the SQL Database.

First of all I kept getting timeout errors connecting to the SQL server locally - I disabled norton firewall locally which relived me of that issue. 

I then ran into a classic issue:

java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused

So what's this all about.. I tried connecting the blog through ODBC like i have done with some other datasources in the past to see if this would work but BlogCFC doesn't support ODBC connections (not to my knowledge anyhow). 

So after searching the net for hours (this appears to be a big issue) I decided to see what service pack I had in SQL server, as i was sure it was not CF related.  To find this out I simple went into query analyser and typed

select @@version Go


This showed me that I had SP2 installed.  After downloading and installing  service pack 4 CF connected fine.  So I suggest if you're having this problem upgrade now... if fact - just upgrade.

Comments (1) | Print | Send | del.icio.us | Linking Blogs | Digg | 3480 Views
BlogCFC was created by Raymond Camden. This blog is running version 5.5.1.