Federation of Small Businesses

SOHO IT Solutions have opted to join the ranks of other like minded small business by become a member of the Federation of Small Businesses [FSB]. We hope that this will be the start of a healthy business relationship and look forward to meeting some of the local business representatives at the forthcoming networking events in the region.

Guild of Professional & Commercial Web Designers

Today I meet Stuart Davies of the Guild of Professional & Commercial Web Designers to discuss SOHO IT Solutions becoming an associate member of the newly established Guild.

Stuart’s explanation of what the Guild was about was very interesting and fitted in well with what I was expecting and looking for. I am sure that in due course my membership will be approved and look forward to a long and professional relationship with the Guild and Stuart.

North East England Service Provider Register

Today we applied to join the ranks of other like minded regional business by applying to join the North East England Service Provider Register.

New Entrepreneur Transatlantic Scholarships (NETS-UK)

Today I received official confirmation that I had made the final selection for the New Entrepreneur Transatlantic Scholarships (NETS-UK) entrepreneurial trip to Babson College, Massachusetts. I will be attending the selection weekend in Liverpool over the weekend of 29th June - 1 July 2007. This is the final hurdle for this years selection process, from the 40 entrepreneurial candidates that have been selected from all across the UK only 20 will be chosen. I am very honoured to have made this final selection weekend, regardless of the outcome I am proud that I have made it this far. I wish the other 39 candidates all the best and look forward to meeting them on the 29th June in Liverpool.

One Northeast Approval

The administrators of The North East England Service Provider Register, One Northeast gave SOHO IT Solutions official approved to be listed as a supplier today.

New Entrepreneur Scholarships (NES)

Tonight is the our New Entrepreneur Scholarship graduation. The event is being held at the Ramside Hall Hotel in Durham. I have been elected by my fellow entrepreneurs to give a short speech. I am looking forward to the whole evening and would like to thank those who were involved in the programme, namely Eric Smalley, Neil Hannah, Katy Rain and Julia Gilliam from the University of Teesside.

Optimising your Data

Week 4 refered us to the massive and extensive MySQL Reference Manual. In particular, we were drawn to Chapter 12: Getting Maximum Performance from MySQL.

A small and very useful section entitled Get Your Data as Small as Possible (section 12.3), listed various techniques for optimising your tables and minimsing storage space.

Optimisation Recommendations

Use the most efficient (smallest) types possible. MySQL has many specialised types that save disk space and memory.

Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is ofthen better than INT.

Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application you should definitely use it. Just avoid it on all columns by default.

If you dont have any variable length column (VARCHAR, TEXT or BLOB columns), a fixed size record format is used. This is faster but unfortunately may waste some space.

The primary key index of a table should be as short as possible. This makes identification of one row easy and efficient.

Only create the indexes that you really need. Indexes are good for retrieval but bad when you need to store things fast. If you mostly access a table by searching on a combination of columns, make an index on them. The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index.

If it's very likely that a column has a unique prefix on the first number of characters, its better ti only index this prefix, MySQL supports an index on a part of a character column. Shorter indexes are faster not only because they take less disk space but also because they will give you more hits in the index cache and thus fewer disk seeks.

In some circumstances it can be beneficial to split into two tables that is scanned very often, This is especially true if it is a dynamic format table and it is possible to use a smaller staic format table that can be used to find the relevant rows when scanning the table.

MySQL Reference Manual v3.23.33 (2001)

My Conclusion

I guess that the above recommendations will become easier to implement the more you use MySQL. Like they say practice makes perfect, and what better way to learn.

All the best Jayson

A Special Mention

I just wanted to make a very special mention to my lovely financee Donna, who is supporting me 110% in my studies. Without her support it would be impossible for me to study and maintain this blog site. So from the bottom of my heart Donna,

THANK YOU FOR YOUR KIND AND LOVING SUPPORT

Jayson
xxx

TT380 : Week 4 - Summary Notes

Okay it's now week 4 of the course and it's time to read hours 9 to 14 of the MySQL course book. So what are these 6 hours about?

• Hour 9 - Populating your database tables.
• Hour 10 - Selecting data from your tables.
• Hour 11 - Advanced usage of SELECT statements.
• Hour 12 - Modifying and deleting data.
• Hour 13 - More about DELETE.
• Hour 14 - Modifying table structure.

Hour 9 Populating your Database Tables

In this hour we will learn how to insert data into out database tables using a variety of methods.

The INSERT command
This MySQL command is the first that we will use to insert data into our database tables. To use this command we need to be in MySQL monitor. So open up a connection to your database and login as normal.

A detailed account has already been written for using this command, and can be accessed via the following link, click here.

It should be noted that although you do not need to specify the names of the column within your table when using the INSERT command. However if you don't then you must ensure that each field within your table has an entry in your INSERT statement even if its empty or is going to use the default value. For example surpose you have a table called employees and you want to add a new record. The employees table has the following fields;

employee_id, employee_startdate, employee_moddate, firstname, lastname, nickname

You are wanting to add the following data into a new record in this table:

James Hummersknott, start date: today. James does not have a nickname. The moddate is obviously also going to be today as thats when you lasted modified the record and the employee_id is your primary key and therefore auto increments.

The command we would use to insert this data is as follows:

INSERT INTO employees
(employee_id, employee_startdate, employee_moddate,
firstname, lastname, nickname)
VALUES
('0', now(), now(), 'James', 'Hummersknott', '')
;

Alternatively if we wanted to simplifiy this and reduce the amount of typing we could use:

INSERT INTO employees
VALUES
('0', now(), now(), 'James', 'Hummersknott', '')
;


The LOAD DATA INFILE method

This is the second method of importing data into your database and again this has been documenetd previously, click here to view the detail blog entry about how to use the LOAD DATA method.

Using mysqlimport

This is our 3rd method of importing data into our database. the mysqlimport must be run from a command prompt and uses a plain text file that contains the data that you want inserting. This command has various switches that you can alter to suit you own personal needs. The following is the basic summary of the syntax that is used for this method of data insertion:

mysqlimport [options] databasename textfile

Okay let's look at that command, the first bit is calling the mysqlimport function. Then we have the chance to enter any options that we wish to use, common options here are:

-L to use a local file
--fields-enclosed-by
--fields-escaped-by
--fields-terminated-by

I have only used 3 of these along with a couple of other ones when I perform my own data insertion using this method. This is the complete command syntax that I used to successfull insert some data from a text file:

mysqlimport -L -v -u jg3723 -pmypassword
--fields-terminated-by=::: --fields-enclosed-by=\"
jg3723 master_name.txt

This successfully entered the contents of master_name.txt into my database called jg3723. It should be noted that this method of data insertion will import the data into the table of the same name as the text file, so in the case of the above example it inserted the data into a table called master_name.

My master_name.txt file consisted of the following data:

"0":::now():::now():::"Jayson":::"Doe"
"0":::now():::now():::"Donna":::"Smith"
"0":::now():::now():::"Tabitha":::"Carr"
"0":::now():::now():::"Naomi":::"Bell"

Hour 10 - Selecting data from your tables

Still to be documeneted - sorry!

Hour 11 - Advanced usage of SELECT statements

Still to be documeneted - sorry!

Hour 12 - Modifying and deleting data

Still to be documeneted - sorry!

Hour 13 - More about DELETE

Still to be documeneted - sorry!

Hour 14 - Modifying table structure

Still to be documeneted - sorry!

My Conclusion

Well that's the end of this particular blog entry and I hope that some of the information is of help to you. Please feel free to comment, all questions will be answered to the best of my ability.

All the best Jayson

Importing data into your TTCFM database

This blog will hopefully help those of you that are experiencing problems when trying to upload/import data into the various tables of your database that is located on the OU server. I have tested each of the methods listed here. The data was successfully uploaded and imported into the relevant tables, with no records being skipped and no warning.

Please Note: That if you are using these methods on your own local installation you may well get warnings, I currently have a local setup using the version of MySQL that was distributed on the Certificate of Web Application Development CD-ROM, namely version 4.0.17 and after numerous hours of testing it appears that for some reason, unbeknown to me at present warnings are given when I used the methods detailed in this blog entry. But hey this is for the OU server, and thats the main concern at the moment afterall.

Using the INSERT method

Okay this is the exact procedure that I used to perform this data insert. Using SSH secure shell open a terminal window to the OU server. Change to your web directory using the following command syntax:

cd web

Log into the MySQL monitor using the following syntax, changing the jg3723 to your OU username:

mysql -u jg3723 -p

When prompted enter you OU PI number remember letters should be in CAPITALS. Select the database to be used, in this case it should be the same name as you OU username, using this command syntax:

USE jg3723;

Now its time to write the INSERT statement, for test purposes you could use the following, which is what I entered:

INSERT INTO master_name
VALUES ('',now(),now(),'Amanda','Gurney');

Hopefully, you should see the following message after pressing the ENTER key.

Query OK, 1 row affected (0.00 sec)

Well that's the INSERT method successfull used, so it's now onto the LOAD DATA method next.

Using the LOAD DATA Method

With this method I pre wrote a DDL script and a plain text file that contained the data to be imported, these two files were then uploaded to my OU web directory If you would like to use my DDL script and data file click here to down load them.

Hopefully you will still have your SSH terminal window open and logged into your MySQL database. If not please connect and login to your database again.

You now need to change the DDL file to point to your own database; using Notepad open the loaddata.sql file and change the first line so it reads the name of your database. Then save the file and close it.

Now upload the DDL script and data file to your OU web directory. To check that they are there use the following syntax:

ls

Hopefully you will see the a list similar to mine below:

[jg3723@ttcfm web]$ ls
 all_names.cfm  createtables.sql  loaddata.sql     mytidy.sql
 contactDB.sql  eca               master_name.txt
 [jg3723@ttcfm web]$ 

Okay the files are there so let's import the data. This is done by using the following syntax:

source loaddata.sql

Hopefully you will see the following messages displayed after pressing the ENTER key.

Reading table information for completion of table and column names

 You can turn off this feature to get a quicker startup with -A


 Database changed

 Query OK, 4 rows affected (0.00 sec)

 Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

My Conclusion

Well that's the end of my LOAD DATA and INSERT summary, you should now be able to alter the various commands and files to import any data that you wish to insert into your database. Have fun!

All the best Jayson

8th Kyu - Kamishin Ryu

In order to undertake my 8th Kyu grading I required a minimum of 44 hours traning and was required to demonstate the following to an acceptable high standard:

Kamae
Fudo Dachi
Sanchin Dachi
Zenkutsu Dachi

Uchi Waza
Chudan Tsuki

Keri Waza
Kin Geri

Uke Waza
Jodan Uke

Kata
Taikyoku sona Ich

Kumite
Not required

Renzoku Waza
Mae Geri Gyaku Tsuki

Personal Satisfaction and Acheivement

I was pleased to accept this grade in July 2004. I say please because I feel that if I did not want to accept the belt, grade and responsibilty I could have declined the grade/belt when it was offered to me by my Sensei.

What responsibility I hear you say.
Well okay maybe at this low grade it may look to on-lookers that you have none, but in reality you do. You have the responsibility to yourself, your sensei and others to continue to demonstrate the levels of standard that have been achived by yourself whilst grading. Admitted, once one progresses up throught the grades, more responsiblity is emposes on you, but I feel that at all levels you have a responsibility to demonstate those techniques that you have been accessed on to to all newcomers. Doing this as an early stage, I feel will make you a better dan grade once you achieve that level of experience.

Martial Arts

Another blog entry that is away from the subject of OU courses, but one that is close to my heart. I have always been keen on all forms of martial arts since an early age. As a child I tried various styles of Karate and even Judo, but like most children I did not stick at it, which I deeply regret now! So any youngers that read this who are training in any style of marital arts - my advise to you is STICK AT IT!

In March 2003, I located a Karate Dojo that interested me. I found a suitable Dojo that was situated in Darlington, County Durham. They offered a good choice of classes, 2 styles of Karate, Kick-Boxing and Kobudo. I promptly joined the club and started training in Kamishin-Ryu karate several times a week and also Kobudo once a month. Due to my OU commitments I have had to stop attending Kobudo, classes but plan to start up again.

So what is Kamashin-Ryu karate?

Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

My Sensei's

Sensei Phil Snewin

Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

Official Website: kamishin ryu karate do

Sensei Fred Bateman

Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

Official Website: kodokan martial arts

What is Kobudo?

Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

My Sensei

Sensei Fred Bateman

Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.

My Kamishin-Ryu Gradings

Although obtaining a blackbelt is one of my life long ambitions, I have decided that this should not be the main aim of my martial arts training. Continuous training, personal develpment and commitment to training will I am certain leaded me to my life long ambition. But unlike some people, training and development and learning will not stop there, to me that will be the start of my next level of marital arts training.

• 8th Kyu grading syallabus
• 7th Kyu grading syallabus
• 6th Kyu grading syallabus

All the best Jayson

A Little More About Me

I thought I would include a little section devoted to me. This section is where I am going to publish information relating directly to me. It's probably not going to be that interesting but I thought it would make a change from all the course related material that is going to be published here.

1970

On the 24th January 1970 I was born to my parents, Susan Water and Barry Gurney in Chelsea, London.

1986

I left secondary school with only my Certificate of Secondary Education qualifications (8 in total) and I decided that it was a case of going to 6th Form to try and get a few General Certificate of Secondary Education under my belt.

1987

I left 6th Form with a few better grades for some of my Certificates of Secondary Education (9 in total), newly aquired General Certificate of Secondary Education (3 in total) plus a couple of other qualifications; one by the Royal Society of Arts and a newly form qualification which I was one of the first in the county to take, namely a Certificate of Pre-Vocational Education.

1988

4 th January 1988, I waved good-bye to my mum at the train station in my home town of Ashford and set off on my journey to join the Royal Corps of Transport, whos training depot was based in sunny Aldershot.

After completing my basic military training I embarked on trade training where I gained my Heavy Goods Vehicle License. It was then time to join my first active unit, 66 Squadron Royal Corps of Transport, based in Tidworth, Hampshire. After a few month at the unit it was time to head of to Germany for a NATO training exercise, which unbeknown to me was going to be the changing point of my military career. After just 3 days in Germany, I substained a serious injury to my spine, which took several hospitals and doctors many hours to decide what needed to be done.

1990

Finally, the British Military decided that I needed a spinal fusion which they performed on my in the March. After spending some time off to recover, it was time for intense rehabilitation, which lasted several months. I was unhappy at this as it meant I was unable to go to Cyprus with the main unit, instead I was left behind on reserve party duties. Things turnt out ok in the end as when I returned to my unit I was promoted to Lance Corporal.

1991 - 1993

Various posting around the UK, and in January 1993 I was medically discharged from the Army.

1994 - 1997

Time spent recoverying form ongoing medical problems realting to my spinal injury. Mid 1997, I went to college to gain qualifications in Information Technology and started to do some teacher training related courses.

1988

Gained an NVQ Level 3 in Implementing IT Solutions and part of my NVQ Level 4 TDLB. Started work as an on-site maintenance engineer.

1999

After careful consideration, I started my own IT consultancy company and my first major client was Orange PCS. Went to Germany and Italy to do consultancy work for two International Telecom companies.

2000

Started work for an International Internet hosting company - Senior Network Administrator and Project Manager. Designed and implemented a corporate wide Microsoft messaging platform. Obtained Microsoft certification status.

2003

Started to study with the Open University, with the aim of gaining various IT and Computing qualifications whilst on-route to acheiving a BSc Honours degree.

2004

Obtained 8th & 7th Kyu in Kamishin Ryu Karate. Reunited with my father after 28 years at Darlington Railway Station. Meet my 3 sisters, Elizabeth, Nicola and Lauren for teh first time in December 2004. Also meet about 35-40 other relatives from my fathers side of the family.

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

MySQL Installation Cleanup

Okay so you have just installed your MySQL database onto your server. It's now an ideal time to perform a cleanup on it to delete the un-necessary users, privileges and databases. This BLOG details serveral steps that I recommend you perform on you newly onstalled MySQL sever. All of which I have performed on my own installation to check that they work correctly.

Here the simple list of tasks that this blog entry will aid you in performing:

• Task 1 - Set the 'root' user password
• Task 2 - Delete anonymous users that are installed
• Task 3 - Delete the remote 'root' user
• Task 4 - Delete the test database
• Task 5 - Remove unwanted privileges
• Task 6 - Flush the servers privileges

Task 1 - Set the 'root' user password

The first task that I would recommend you do is create a root password, as by default the installation of MySQL leaves this all important user account unprotected as the password is left blank.

So who do you create/set the password for the root user? Well we use the MySQL monitor, so open a command prompt window and navigate to your MySQL directory. This will depend on where you installed MySQL to and also on your operating system. For the purpose of this BLOG I will assume you have installed your system the same as I have done locally using a Windows XP Pro system. I installed MySQL to C:\mysql so when I open a command prompt I enter the following syntax to access the mysqladmin utility:

cd c:\mysql\bin    

Now enter the following command at the command prompt:

mysql -u root -p

If no password has been set, which should be the case for you if this is performed directly after a fresh installation of MySQL, you can just press the ENTER key. The following command will allow you to set a password for your super-duper root user:

SET PASSWORD FOR
'root'@'localhost'=PASSWORD('yourpassword');

Now that you have set the new password its advised to flush your privileges, this is performed using the following syntax:

FLUSH PRIVILEGES;

Task 2 - Delete anonymous users

This step will delete any anonymous users that are installed Note that the following command uses two single ' characters and not a "

DELETE FROM mysql.user WHERE User=''

Task 3 - Delete the remote root user

Now its time to removes the remote root user using the following command syntax:

DELETE FROM mysql.user
WHERE User='root'
AND Host !='localhost';

Task 4 - Delete the test database

Now we are going to delete the test database that the installation process creates. Use the following command syntax:

DROP DATABASE test;

Task 5 - Remove un-necessary privileges

Now its time to remove any un-necessary privileges from the mysql database using the following command syntax:

DELETE FROM mysql.db
WHERE Db ='test'
or Db = 'test\\_%';

Task 6 - Flush the privileges on the Server

Let's now flush the server's privileges and we are then ready to quit the MySQL monitor and start to use our lovely new MySQL server. Flushing is performed using the following command syntax:

FLUSH PRIVILEGES;

My Conclusion

Well that now concludes this BLOG and hopefully has helped you cleanup your newly installed MySQL server. If you have any comments please feel free to post them and I will try and answer them to the best of my ability.

All the best Jayson

DBManager Professional v3.0.2

After attempting to install and use the version of DBManager that was distributed on the Cert Web Application Development CD-ROM, I encountered a few issues and decided that I would be better off using the latest version of the product.

This was support by a comment in one of the TT380 user conferences, where a fellow student mentioned they had successfully installed and could recommend the lastest version, namely version 3.0.2.

I waited no longer and opened my web-browser and navigated to the applications web-site to download the newest version. I was please to see that they had a FREE version available. You too can download this excellent Database Management Tool from:

http://www.dbtools.com.br/EN/downloads.php

The download was reasonably quick and very easy after first registering. Once it downloaded I proceded to install it, which again was an easy task to do.

After the initial installation the application loaded and I was presented with a very nice and professional looking interface. I was given the option on the right of the screen to Add a New Server. This needs to be setup before you can start using this excellent utility. Clicking on the Add new Server displays a server properties window. In this you need to select the various options, in my case I only needed to enter the root users password for my MySQL database. I then used the 'test button' and it confirmed it could connect to my database, I then click on the 'OK button' and my setup was finished.

I returned to the main display page and was presented with my MySQL server and all its databases, tables and users. It was that simple - and I highly recommend it to everyone who connects to any type of database.

Please feel free to leave comments on this Blog, I will try to answer any questions that arise to the best of my ability.

All the best Jayson

Securing a MySQL Server on Windows

Most of my fellow TT380 collegues are most likely using a Windows based platform and if you are not lucky enought or even too lazy to build a suitable linux based server for the duration of this course you may well be interested in know how to secure a MySQL server based on a Windows Platform. I must also hold my hands high and say that I have not been bothered to install a suitable linux server.

I have not had the time to personally check each stage of this process yet - it's on my 'To-do-list', but due to the source of where I have located the information I would be willing to trust it. Mind you can't hold me to that should something go wrong - LOL.

The full and detailed article can be found at the following address:

http://dev.mysql.com/tech-resources/articles/securing_mysql_windows.html

But like most things on the WWW, it is subject to change, move and even deletion. For this reason I am going to summarise the steps to be taken in securing a Windows based MySQL Server. In-time I will recreate the entire article in my own words - thus avoiding plararism etc, and also include any additional information that I might have picked up along the way. This re-write is obviously also on my 'To-do-list'.


How to secure your Windows MySQL Server

The overal securing of a MySQL server installation requires various uniquie steps to be performed. It should also be noted now that once this procedure has been performed it still requires monitoring to ensure any new security breaches and back-doors etc are quickly identified and secured. I therefore recommend that you subscribe to a couple of the various security bulletins that are available on the WWW.

Okay that being said - lets identify the areas to be addressed:

• Step 1: Install MySQL on a Recent, NT-based version of Windows
• Step 2: Install MySQL on an NTFS File System
• Step 3: Install MySQL on a Standalone Machine
• Step 4: Install the Latest Production Version of MySQL
• Step 5: Secure the MySQL User Accounts
• Step 6: Disable TCP/IP Access
• Step 7: Bind the TCP/IP Address
• Step 8: Firewall the Server
• Step 9: Run The MySQL Service as a Limited User
• Step 10: Encrypt the Data Folder
• Step 11: GRANT the Minimum Privileges Necessary
• Step 12: Change the Name of the Root User

As previously mentioned, at this stage of my blog development, I have not had the opportunity to document or actually follow the above staged in fine detail. Given time I shall carry out a detailed investigation of these stages.

My Conclusion

Some of these steps have already been documented in my previous blog titled 'MySQL Installation Cleanup'.

As you can see with a few simple step you can easierly secure your newly installed MySQL server. Obviously, security is an on-going task and is also a specialised area. There are however, a vast amount of detailed articles, guides etc on the WWW to aid you in your quest to keep your server secure - Good luck and please feel free in sharing your comments.

All the best Jayson

TT380 - Useful References and Resources

As with any courses the amount of references and resources used is enourmous. Just looking at the proposed reading guides contained in courses main weekly study guides, I now I am going to end up with loads of potential important links. If you are anything like me after a while your web-browsers favourites or bookmarks tend to get some what messy and unorganised.

I thought that whilst I was using my personal blog for making course notes, comments and summaries, why not use it to manage my resources and references as well. So here we are at my resource and reference blog entry for TT380.

• Web Articles and Sites
  
• Official TT380 Course Web-site
• Normalising your database - First Normal Form (1NF)
• Normalising your database - Second Normal Form (2NF)
• Normalising your database - Third Normal Form (3NF)
• Microsoft Consulting Case Studies
• Unisys Case Studies
• Transaction Processing Performance Council
• Database Server Performance and Tuning
• Background SQL
• Ocelot Computer Service Inc.
• Scripts
• no entries yet
• Other
• no entries yet

If you have any suitable links that you feel could be added to the list, please feel free to use the comments section, which I will review and using the given information update this blog entry.

I hope that this page is as much use to you as it is too me! Feel free to leave comments via the comments section on this blog entry.

All the best Jayson

TT380 : Week 3 - Summary Notes

Okay it's now week 3 of the course and it's time to read hours 6 to 8 of the MySQL course book. So whats these 3 hours about?

• Hour 6 - Planning and creating your database
• Hour 7 - Creating your database tables - Part I
• Hour 8 - Creating your database tables - Part II

Hour 6 - Planning and creating your database

Okay, lets see about creating our database. One way to do this is via the mysqladmin using the following command:

mysqladmin -u username -p create datebasename

I have not added the password for the user, instead it will be prompted once the command has been entered. Alternatively, if you want to include the password within the command use the following syntax:

mysqladmin -u username -ppassword create datebasename

So lets see the whole command with dummy username, password and database we want to create:

mysqladmin -u root -pA51gA23 create chelseafc

Once the database has been created it needs to have its permissions and privileges set. Its advised to create a new user other than the main root user account who can administer your newly created database. We can do this using the MySql monitor using a command similar to the following:

mysql -h localhost -u root -p

You will be prompted for the root user password, enter it and press enter - Remember that its case sensetive. We the need to use the mysql database which holds all the servers permission and accounts etc.

use mysql;

Now we are ready to enter the command to create our new database admin user who we will call supercontact for the chelseafc database.

GRANT ALL ON chelseafc.*
TO 'supercontact'@'localhost'
IDENTIFIED BY 'password';

Hour 7 - Creating your database tables - Part I

This hour of the book is about the common data types that you can define within your MySQL tables and creating tables. So lets create our table:

CREATE TABLE players (
player_id SMALLINT UNSIGNED,
player_dateadded DATETIME,
player_datemodified DATETIME,
firstname VARCHAR (75),
lastname VARCHAR (75)
);

The above statement would create our table but we can improve this which is what we will do in the next hour. To issue the command to create the above table as it stands you would issue it via the MySQL monitor, remember you need to login to the monitor first.

mysql -u supercontact -p

Enter the password when prompted. Now we need to tell MySQL what database we wish to create the tables in:

USE chelseafc;

We can now enter the above statment into the monitor. Alternatively, we could use an external file, that contained the statement. This method is my prefered method of issuing commands, as it allows me to create them in advanced or via E-R diagram applications (E-R diagrams will be a subject for a later blog entry). To create the external file I use Notepad and enter the command(s) that I want to issue. The file is then saved to a suitable location with a meaningful name and the following file extension .sql. At the top of the file you should add a line that tells the server what database you want this table to be created in. So following our previous table creation example the new file would read:

USE chelseafc;
CREATE TABLE players (
player_id SMALLINT UNSIGNED,
player_dateadded DATETIME,
player_datemodified DATETIME,
firstname VARCHAR (75),
lastname VARCHAR (75)
);

To use this file to create our database table use the following syntax:

mysql -u supercontact -p

Hour 8 - Creating your database tables - Part II

The aim of this hour is to define our data types for the table fields, add keys, indexes and to create our final table creation statement. First of our table needs a primary key which can not contain a NULL value. This is done by adding the following to the chosen table field:

NOT NULL PRIMARY KEY AUTO_INCREMENT

The next step is to think about what fields in the table should be indexed, indexing helps improve searches BUT care should be given as you can degrade a servers overall performance if you index all data fields. The syntax for indexing a field is as follows:

INDEX idx_fn (firstname);

So lets put this all together and create are full table creation statement ready for use.

USE chelseafc;
CREATE TABLE players (
player_id SMALLINT UNSIGNED NOT NULL
PRIMARY KEY AUTO_INCREMENT,
player_dateadded DATETIME,
player_datemodified DATETIME,firstname VARCHAR (75),
lastname VARCHAR (75),
INDEX idx_fn (firstname)
);

Thats all that needs to be entered in the text file, enter this and save it as myfirsttable.sql The time has arrived to create the table. This is done via the MySQL monitor using the following syntax:

mysql -u supercontact -p 

You will be prompted for the supercontact password, enter this and press ENTER. To check that the table was created we can use the following syntax from within the MySQL monitor:

SHOW tables;

My Conclusion

Well this concludes the main areas for Week 3. Obviously the book goes into more detail, but hopefully you will get the main points from this summary. Please feel free to comment, or post questions relating to any of the areas discussed in this Blog entry.

All the best Jayson