How to solve your port 80 is actually used by Microsoft HTTPAPI/2.0 serv...

DCCN Labe Assessment 

Labe Assessment 01 in DCCN

Labe Assessment 02 in DCCN

Labe Assessment 03 in DCCN

Labe Assessment 04 in DCCN

Labe Assessment 05 in DCCN

How to connecting mysql to netbens step by step

Contents: Creating a Database Using MySQL Making a Connection to MySQL Database Creating a Table Inserting a Sample Data Related references Before we proceed to the next tutorial, upgrading our skill level, we would like to recap the NetBeans and MySQL database related thingy. We start learning to develop Java GUI application and then make a connection to a database using Java DB (Derby). Then we will try to change the database to MySQL while retaining other aspects of the Java GUI application.  During the process in completing the Java GUI application, we have learned a lot of things regarding the Java technologies and at the same time got familiar with NetBeans 6.x.x. We will try to developed Java application up to the web applications while experiencing all the related Java technologies. Stay tune! Creating a Database Firstly let create a new database by using the following SQL statement through MySQL Command Line Client Tool. In this exercise, our database name will beMycompany. Take note that MySQL is not case sensitive. If you want to retain the case sensitiveness, try using the double quotes. We found that using the double quotes also not working all the times.

CREATE DATABASE Mycompany;

Making a Connection

For the next steps, we are using the NetBeans. Launch NetBeans. Click Services tab > expand Database node > expand Drivers node > select MySQL (Connector/J driver) > right click mouse > select Connect Using.

Use the following connection string and key in the username as root and its password. Click OK.

jdbc:mysql://localhost:3306/mycompany

If the connection to the database was established successfully, it will be confirmed as shown in the following Figure. Click OK.

Next, expand the connection node as shown below. You can see the Tables, Views and Procedures folders. These are common database objects that act as containers for other database objects that will be created later.

Creating a Table

Next, execute the following SQL script to create a table. Select the database connection node > right click mouse > select Execute Command to launch SQL query editor.

The following table summarized the table that we want to create. You can create the table shown in the Table using the following SQL script. Copy, paste and execute the following SQL script.

The structure for a Employees table
Column name	Data type	Constraints
userID	INT UNSIGNED	NOT NULL PRIMARY KEY
name	CHAR(10)	NOT NULL UNIQUE INDEX
lastName	VARCHAR(30)	-
firstName	VARCHAR(30)	-
office	CHAR(2)	NOT NULL

CREATE TABLE IF NOT EXISTS Employees(

   empl_id INT,

   empl_first_name VARCHAR(32) NOT NULL,

   empl_last_name VARCHAR(32) NOT NULL,

   empl_addr_street VARCHAR(32) NOT NULL,

   empl_addr_city VARCHAR(32) NOT NULL,

   empl_addr_zip VARCHAR(10) NOT NULL,

   empl_addr_state VARCHAR(2) NOT NULL,

   empl_dept_id VARCHAR(10) NOT NULL,

   empl_start_date DATE NOT NULL,

   empl_position VARCHAR(5) NOT NULL,

   empl_birth_date DATE NOT NULL,

   PRIMARY KEY (empl_id)

) ENGINE=innodb;

The following Figure shows the SQL script in the SQL query editor/SQL Command editor.

Make sure the connection to the correct database as shown in the following Connection: text field.

Next, click the Run SQL button () or Ctrl+Shift+E to execute the SQL script and notice the progress in the Output window at the bottom.

Next, expand the Tables > employees. MySQL is not case sensitive. If you want to retain the case sensitiveness, use double quotes.

The Employees table contains the following columns:

• empl_id:  The employee identifier number, which uniquely identifies each employee and is the primary key for these records
• empl_first_name and empl_last_name:  The employee's first and last names
• empl_addr_street, empl_addr_city, empl_addr_zip, and empl_addr_state:  The employee's complete address
• empl_dept_id:  The identifier, a foreign key reference to a row in the Departments table which will be used for the department in which the employee works
• empl_start_date:  The employee's start date with the company
• empl_position:  The identifier, a foreign key reference to a Positions table record which will be used for the employee's current job or position
• empl_birth_date:  The employee's date of birth

Verify our task by issuing the following SQL statement (describe a table).

DESC Employees;

The following Tables list MySQL numerical and character data types information. A complete information can be found in MySQL documentation and you can also download it for offline reading.

Numeric types in MySQL
TINYINT	Whole 7-bit numbers in the range -128 to 127.
SMALLINT	Whole 8-bit numbers in the range -32758 to 32,757.
MEDIUMINT	Whole 16-bit numbers in the range -8,388,608 to 8,388,607.
INT	Whole 32-bit numbers in the range -2,147,483,548 to 2,147,483,547.
BIGINT	Whole 64-bit numbers in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
DECIMAL(p,s)	Decimal values with s as the scale and p as the precision.
DOUBLE(p,s)	Double-precision values with s as the scale and p as the precision.
FLOAT(p)	Floating point numbers with a precision of 8 or less.

Character types for MySQL
CHAR(n)	Fixed-length character type that holds exactly n characters. Shorter strings are padded with spaces to n characters.
NCHAR(n)	Same as CHAR, except for Unicode strings.
VARCHAR(n)	Variable-length strings that may store up to n characters. Any excess characters are discarded.
NVARCHAR(n)	Same as VARCHAR, except for Unicode strings.

Inserting a Sample Data

Next, let insert a record by running the following SQL statement.

INSERT INTO Employees VALUES(

'1234','Jodie','Foster','45th Street, Level 3','New York','32000',

'NY','IT','2008-07-04','Dir','1970-08-05');

Then, verify our task by issuing the following SQL statement.

SELECT * FROM Employees;

How to Embed a SQL Query in Microsoft Excel

This article will help users to embed SQL Query in Excel 2010 and create a dynamic connection in Excel.

1. 
   1
   Go to Data tab and select Find Other Sources as shown in the screen shot below.
2. 
3. 2
   Select “From Data Connection Wizard” in the drop down.
4. 
   3
   Data Connection Wizard will open. Select “ODBC DSN” from the available option and click “Next”.
5. 
   4
   Connect to ODBC Data Source window appears. Where a list of database available in our organization will be displayed. Select the appropriate database and click on “Next”.
6. 
   5
   Select Database and Table window appears. We can select the database and the table from where we wish to pull the data. So, select the database and table as appropriate in our case.
7. 
   6
   Select Finish in “Save Data Connection File and Finish” window. This window will pull up the File Name based on our selection in previous screens.
8. 
   7
   Import Data window appears where we can select options as per our need and click OK.
9. 
   8
   Select “From Data Connection Wizard” in the drop down.
10. 
    9
    Go to Data tab and Click on Connections. Click on Properties in the following window.
11. 
    10
    Go to Definitions tab in the following window and write SQL query in “Command Text” and Click OK. Excel will display the result as per the query.
12. 
    11
    Now go to Microsoft Excel and validate if the results are as per the SQL Query written.

How to install MySQL & other tools

Introduction

Download MySQL

Uninstall any previous versions through Control Panel prior to install.

MySQL is 'Open Source' software that is undergoing constant development.

At the time of writing this guide there are two later versions available

1. MySQL 5.5 is the current development release series

2. MySQL 5.1 is the current General Availability (Production) release series

I recommend you dowload the latest General Availability (GA) version as it has undergone extensive testing and is considered 'stable'.

To check which is the latest GA version click here - http://dev.mysql.com/doc/refman/5.1/en/choosing-version.html

The download you need for Windows is 'MSI Installer' for your system

mysql-essential-X.XX.XX-win32.msi - for 32 bit Windows OR

mysql-essential-X.XX.XX-winx64.msi - for 64 bit Windows

Download link - http://dev.mysql.com/downloads/mysql/#downloads

You must exit all other programs, in particular Anti Virus programs.

The install outlines here was on a 64bit system and is identical to 32 bit install.

Should you experience errors with the install - see NOTES below

Step 1

Navigate to the folder you downloaded to and click on install package.

Step 2

You may receive this security warning - click Run

Step 3

Click Next >

Step 4

You may select Typical if you are have a limited hard disk capacity.

I recommend Complete install.

Click Next >

Step 5

You are now ready to install - click Install

Step 6

Click Next >

Step 7

Click Next >

Step 8

Tick Configure the MySQL Server now

Registration is optional.

Click Finish

Step 9

Click Next >

Step 10

Your will only see this screen if you have previously installed or patrially installed this version of MySQL

If not previously installed go to Step 11

Select Reconfigure Instance then click Next >

Step 11

Select Detailed Configuration then click Next >

Step 12

Select Developer Machine then click Next >

Step 13

Select Non-Transactional Database Only then click Next >

Step 14

Select Decision Support (DSS)/OLAP then click Next >

Step 15

Select both (default) options: Enable TCP/IP Networking and Enable Strict Mode then click Next >

Step 16

Select (Default) Standard Character Set then click Next >

Step 17

Select both options: Install As Windows Service and Include Bin Directory in Windows Path then click Next >

Note: if you have installed an earlier version of MySQL the default Service Name: MySQL will not be available as it is already in use.

You MUST uninstall previous version to use the default Service Name

Step 18

If you have a previous install you must enter Current root password - if no previous install you will not see this

Enter New root password and Confirm

Make sure you record the Root Password

Note: The default username is root.

It is recommended to change this setting up PHPMyAdmin or MySQL Command Line Client (advanced)

Click Next >

Step 19

Click Execute to configure your database server.

NOTES

Should you receive any error messages when installing then cancel the install

Go to 'Control Panel' and remove MySQL

Before reinstalling shut down & restart then close all programs, including Anti Virus and reinstall.

If problem persists, more likely with 64 bit install, try downloading the current development release and installing.

Help can also be found at http://www.mysql.com/

NOTE: This drove me nuts for a while creating all sorts of errors in php code I was using

For some reason Microsoft thought we all required Military grade security and enabled 'Strict Mode' by default in MySQL.

This would only be required by securty & government agencies, eg my ISP disable on their server.

You can surf around on Google to read up on it & the various complex methods to disable it or keep reading.

To disable Strict Mode the easy way

Open your my.ini file

located at C:\Program Files\MySQL\MySQL Server 5.5\my.ini in your text editor (eg notepad)

Use control F (push Ctrl & F keys on keyboard together) and search for 'Strict'

locate this

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Change to this

# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

or you can delete these lines altogether

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

by placing the hash (#) in front it disables Strict Mode

I recommend the (#) method so the lines are still there if you ever need enable 'Strict Mode' again (if you become a high security programmer!)

Then simply save the file in it's original location (eg click File then 'Save').

Simple way to import data into SQL Server

Overview

SQL Server Management Studio (SSMS) provides the Import Wizard task which you can use to copy data from one data source to another.  You can choose from a variety of source and destination data source types, select tables to copy or specify your own query to extract data, and save your work as an SSIS package.  In this section we will go through the Import Wizard and import data from an Excel spreadsheet into a table in a SQL Server database. 

1. Enter the data into an Excel spreadsheet

• First, enter the data into an Excel spreadsheet.
• In this example, the default spreadsheet name, Sheet1, has been retained, but if you rename the sheet (to the name you want for the table in the database, for example), that name will be automatically used in the import process (at Step 6).

Small demonstration Excel spreadsheet ready for transfer to a SQL database

2. Start the SQL Import and Export Wizard

• Next, in Windows, start the Import and Export Wizard at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.
• The Welcome page appears. Click Next.

SQL Import and Export Wizard in the Windows Start menu

OR  Right click on the you needed database in the Object Explorer, select Tasks, then Import Data from the context menu to launch the Import Wizard. 

3. Chose your Excel spreadsheet as the Data Source

• In the Data Source dropdown of the Choose a Data Source page, select Microsoft Excel.
• In the Excel file path box, specify the file path to the Excel spreadsheet.
• Select the the version in the Excel version dropdown.
• Make sure that First row has column names is checked.
• Click Next.

4. Chose your SQL database as the destination

• In the Destination dropdown list, accept the default setting of SQL Server Native Client 10.0.
• In the Server name dropdown list, enter the name of the server. The example is a remote server, so the IP address and port of the server were specified.
• Chose the Authentication type. The example is a remote server, so SQL Server authentication, with a user name and password, is required.
• In the Database dropdown list, select or type the name of the database.
• Click Next.

5. Specify how to copy the data

• The default option, Copy data from one or more tables or views, works for this example.
• If you want to try the second option, Write a query to specify the data to transfer, the followinglolcode snippet may be instructive:

CAN HAS SQL?
DBASE IZ GETDB('db/demo.db')
FUNNAHS IZ DBUCKET(&DBASE&,"CAN I PLZ GET * ALL UP IN lollit")
IM IN UR FUNNAHS ITZA TITLE
    VOTEZ IZ &TITLE#ups& - &TITLE#downs&

• Click Next.

 

6. Select the source tables and views

• The default settings as shown work in this example.
• In the Destination column, you can specify a different name for the table in the SQL database if you choose.
• Click Preview to see how your data will appear in the destination table.
• You can click Edit Mappings to change how your data is assigned at the destination table, but it shouldn't be necessary in this example since you entered the data into the Excel spreadsheet yourself.
• Click Next.

7. Run the "Package"

The following message appears at the bottom of the Run Package page: In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation. A "package" is all of the settings that your have configured so far. In the commercial versions of SQL Server, you can save the package for reuse so you don't have to enter all of the settings the next time you run the wizard. In the Express (free) version of Microsoft SQL Server 2008 Management Studio, you must re-enter all of the settings every time you run the SQL Server Import and Export wizard.

• Click Next.

8. Verify that the package executed successfuly

• Click Report to view useful information about the data transfer process.
• Click Close.

9. View the new table in SQL Server Management Studio

• View your new table by opening Microsoft SQL Server 2008 Management Studio at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.

The new table, dbo.Sheet1$, in Microsoft SQL Server Management Studio

You can also use the Import and Export Wizard to export data from a table in SQL Server to an Excel spreadsheet.