Jayson Gurney of SOHO IT Solutions

Sunday, February 27, 2005

MySQL version 4.x Quick Reference Guide

There are loads of Quick Refernce Guides available on the WWW for MySQL, so I have create mine with a slight twist in that I have also included notes on where they have been used in course book/weekly study guides. That way hopefully it will help you see exactly how each command/function can be used.

COLUMN TYPE

[NATIONAL] CHAR (M) [BINARY]
county CHAR (75)
pg 80
[NATIONAL] VARCHAR (M) [BINARY]
pg 79
BIGINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
BIT
not used as yet
BLOB
not used as yet
CHAR
not used as yet
DATE
not used as yet
DATETIME
not used as yet
DECIMAL [ (M [,D] ) ] [ZEROFILL]
not used as yet
DOUBLE PRECISION [ (M,D) ] [ZEROFILL]
not used as yet
DOUBLE [ (M,D) ] [ZEROFILL]
not used as yet
ENUM ('value1' , 'value2' , 'value3', and so on)
pg 80
FLOAT [ (M,D) ] [ZEROFILL]
not used as yet
INT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
INTEGER [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
LONGBLOB
not used as yet
LONGTEXT
not used as yet
MEDIUMBLOB
not used as yet
MEDIUMINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
MEDIUMTEXT
not used as yet
NUMERIC (M,D) [ZEROFILL]
not used as yet
REAL [ (M,D) ] [ZEROFILL]
not used as yet
SET ('value1', 'value2', 'value3' and so on)
not used as yet
SMALLINT [ (M) ] [UNSIGNED] [ZEROFILL]
pg 79
TEXT
pg 81
TIME
not used as yet
TIMESTAMP [ (M) ]
not used as yet
TINYBLOB
TINYINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
TINYTEXT
not used as yet
YEAR [ (2|4) ]
not used as yet

COMPARISON OPERATOR

=
not used as yet
< >
not used as yet
!=
not used as yet
<= not used as yet
< not used as yet
>=
not used as yet
>
not used as yet
<=>
not used as yet
COALESCE (list)
not used as yet
expr BETWEEN min AND max
not used as yet
expr IN (value, and so on)
not used as yet
expr NOT IN (value, and so on)
not used as yet
INTERVAL (N,N1,N2,N3)
not used as yet
IS NOT NULL
not used as yet
IS NULL
not used as yet
ISNULL (expr)
not used as yet

LOGICAL OPERATORS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CONTROL FLOW FUNCTIONS

COMMAND HERE
not used as yet

STRING FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

STRING COMPARISON FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

MATHEMATICAL FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

ARITHMETIC OPERATORS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DATE AND TIME FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CAST FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

BIT FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

MISCELLANEOUS FUNCTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

FUNCTIONS FOR USE WITH GROUP BY CLAUSES

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

INSERT

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

SELECT

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

JOIN

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

HANDLER

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

UPDATE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DELETE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

TRUNCATE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

REPLACE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

UNION

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

LOAD DATA INFILE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE DATABASE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP DATABASE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE TABLE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP TABLE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

ALTER TABLE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

RENAME TABLE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE INDEX

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP INDEX

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

USE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DESCRIBE

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

BEGIN/COMMIT/ROLLBACK

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

SET TRANSACTIONS

COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

A

Installing ColdFusion MX 7 on a Windows XP Pro SP2 Platform

The aim of this installation guide is to give you a clear and easy to understand guide for installing a local version of ColdFusion MX 7 Developer Edition onto a Windows XP Pro SP2 platform for use during the study of TT380: Databases within Website Design.

Step 1: Download the required installation files
Step 2: Prepare system for installation
Step 3: Install ColdFusion MX7 Developer Edition
Step 4: Install and Configure MySQL ODBC data source
Step 5: Test your installation and configuration

STEP 1: OBTAIN THE REQUIRED INSTALL FILES

The ColdFusion Express version 4.0 that is on the Cert in Web Apps Development CD-ROM has caused me numerous nightmares when it came to configuring my MySQL ODBC data source. My answer was to install a copy of the latest release ColdFusion MX 7 Developer Edition. This version is as far as I am currently aware FREE for development use so there is no time limit on its usage.

Okay let’s get the download! Using you web-browser, enter the following URL:

http://www.macromedia.com/cfusion/tdrc/index.cfm?product=coldfusion

If you are not already registered with Macromedia, you will need to do this and then revisit the above URL. Registration is FREE, simple and rather painless. When you return to the download page select the option to download ColdFusion MX 7 Developer Edition, which should be at the top of the page, then select the English Windows 275.79 MB option using the select button, then click on download. Save the file a suitable location on your system, so that you can easier retrieve it later.

BE WARNED that this download can take some time depending on you particular internet connection.

STEP 2: PREPARE SYSTEM FOR INSTALLATION

Whilst the installation file is downloading, it’s a good idea to gather the necessary information ready for your installation. Please remember that this installation brief is for use with an OpenSA web-server (Apache), which is distributed on the Cert in Web Apps Development CD-ROM. Note the following information which is what I personally used for my installation, I also used the default installation for OpenSA, so if you decided to install in any other manner you will need to alter the information below to mirror your own installations:

What is the serial number for ColdFusion MX 7?
Use the developer edition option
What is the type of installation?
Server Configuration
ColdFusion MX 7 installation directory?
C:\CFusionMX7
Configure web server or use built-in web server?
Configure
Choose web server to configure?
Apache
Apache configuration directory?
C:\OpenSA\Apache\Conf
Apache path to server binary?
C:\OpenSA\Apache
ColdFusion MX Administrator password?
You choose this but keep it a secret
Do you want to enable RDS?
NO

STEP 3: INSTALL COLDFUSION MX7 DEVELOPER EDITION

Okay you have the downloaded installation file and the required installation notes. Let’s get on with the main installation then. Ensure that the Apache web server is running you can do this by checking the service via the Windows XP Administrators control panel. Shut down all previous ColdFusion services if you have previously installed them – again this can be performed via the Windows XP Administrators control panel. Now double click on your downloaded installation file and follow the on screen instructions. Be sure to use the information that we gathered in Step 2, to ensure that the correct information is given to the installation routine. Hopefully all went well for you and you now have an installed ColdFusion MX 7 server.

STEP 4: INSTALL AND CONFIGURE MYSQL ODBC DATA SOURCE

Using the ColdFusion Administrator which is accessed via your web-browser and is delivered to you via your local web-server we are now going to configure your MySQL data source. Open your web-browser and enter the following address:

http://localhost/cfide/administrator/index.cfm

Enter the password that you set during the setup routine then click on the login button. You will then be in the ColdFusion Administrator, so now on the left you will see a link to Data & Services, click on this and then click on the Data Source link. We are now going to add a new data source, so enter the name of the data source, for the purposes of this installation guide enter the following: chelsea then using the selection button below select MySQL (3.x) as the driver. Then click the add button.

The next page is where we are going to configure this new data source. Enter the following information into their respective boxes:

Database - contactDB
Server - localhost
Username - root
Password - your MySQL root password

Then click the submit button, if all was ok with the data that you have just entered, the screen will change back to the original Data Source screen, BUT the newly created data source named chelsea will be verified as OK.

STEP 5: TEST YOUR INSTALLATION AND CONFIGURATION

Using your HTML editor or Notepad, enter the following test code and then save then save it as odbctest.cfm Sample test script is as follows:
<>html<>
<>head<>
<>title<>ColdFusion ODBC Test< /title >
< /head >
<>body<>
<>cfquery name="databases" datasource="chelsea" debug="yes">
show databases;
< /cfquery >
<>cfoutput<>
<>p<>Today is #DateFormat(now())#< /p >
< /cfoutput >
< /body >
< /html >
Once the above has been created and save it to your root web directory, open up your web-browser and request the page via you local web-server. Calling the test file is simple - enter the following into your web-browsers address bar:

http://localhost/odbctest.cfm

If all is working correctly then the page will display the current date in your browser. Please note that the script only attempts to make a connection to your data source, and that no database data will be displayed. If the current date is not displayed then unfortunately, something is a miss. But if all goes well this will not be the case.

My Conclusion

If you managed to get the current date displayed in your web-browser, then it looks like you have successfully installed and configured ColdFusion MX 7 Developer Edition to connect and access your MySQL data source. WELL DONE!

So what next I hear you say – well the world is your oyster as they say. Now that this has been configured and tested, you can start to develop your own ColdFusion pages. I hope that this BLOG helps some of you with you local installation of ColdFusion MX 7, if you have any comments feel free to post them.

BTW - this was written from memory so I have my fingers crossed and hope that there are no errors, but I am certain that you will be the first to tell me if there is - any errors will be investigated and subsequent amendments will be made.

All the best Jayson

Subscribe to: Posts (Atom)