Khan Academy: Back to School

Advanced Placement & college prep
AP Calculus AB AP US history
AP Calculus BC AP Art history
AP Physics 1 SAT
AP Physics 2 College admissions
AP Chemistry
Math
K-2nd grade Algebra I
3rd grade Geometry
4th grade Algebra II
5th grade Trigonometry
6th grade Statistics & probability
7th grade Calculus
8th grade Differential equations
Arithmetic Linear algebra
Pre-algebra
Science & engineering
Biology Health & medicine
Physics Electrical engineering
Chemistry Cosmology & astronomy
Organic chemistry
Computing
Computer programming Computer animation
Computer science
Arts & humanities
Art history US history
Grammar World history
Music
Economics & finance
Microeconomics Finance & capital markets
Macroeconomics Entrepreneurship

Convert MAKEINTRESOURCE to C#

Ran into a small issue. There’s a C++ Macro called MAKEINTRESOURCE that is used heavily in Win32 programming. I’d be very interested in knowing if anyone has ported the macro to C#. Here is an [Article] that explains the problem in detail.

Actually the problem is simplier than I thought. Anything that uses MAKEINTRESOURCE, just pass a UInt16 in the field instead of a LPSTR and it works just fine.

Marshaling Data Types With Platform Invoke in C#

This is worth studying for you game designers/programmers. With DotNet and CSharp a lot of you are going the Windows.Forms route. I just wanted to present to you an alternative. The grunt work is done, so all you need to do is include the CSharp*.cs files and you are ready to go.

For fun I ported “GameTutorials Triangle(First OpenGL Program” to C# using
Win32. It keeps the existing framework of the original tutorial without using
Windows Forms. Take a look. This will safe others time that might want to
program OpenGL with Win32 calls in C#.

Tutorial: “[Triangle – My First OpenGL Program in C#]”

Update: Full Screen Mode now Works. Default Icon is now working. Figured out the MAKEINTRESOURCE problem. Some things that don’t work perfectly yet are commented out. For example, a memory leak that happens during painting. Anyone is welcome to email bugs or fixes.

I should note that I used the OpenGL ports from Nehe, but all the Win32 ports
are mainly from me. I used Pinvoke as a reference and found several answers in forums that blurred by.

[MSDN] Great mapping table between unmanaged types to C#.
Another great resource: [Pinvoke.net]

Creating .NET Web Clients with Mono

If you are not familar with Web References or created web clients that connect to Web Services, here are detailed step by step screenshots (8 in all) that will get you up to speed. You may have Visual Studio .NET or Visual Studio .NET 2003 or Mono MCS. And with these steps you can get a web service up and running at least on Win2k, WinXP, or Win2003. See story for details…
To start we just need a basic C# Console App. Go into Visual Studio .NET. Click [File] – > [New] -> [Project]. Click [Project Types] -> [Visual C# Projects]. Click [Console Application]. The location can be anywhere on your harddrive, so pick a suitable location. The project name will default to ConsoleApplication1. Then click OK.

From the basic Console App, right-click the project name [ConsoleApplication1] and select [Add Web Reference]. This will initiate the Web Reference dialog.

Enter the URL to the Web Service you plan to connect to. This is the same location from the previous [Web Service Tutorial]. Click [Add Web Reference] and this will add the appropriate source files to the project.

Finishing the [Add Web Service Dialog] added the web reference files to the project as you see here. The WSDL file is what I think the coolest part of Web Services & Web References. The file not only defines what each of the method names are in the Web Service, it defines the type of each argument. So if you code ever gets out of sync with the WSDL, this file will tell the compiler about it.

At some point you may decide to change the Web Service method parameters or even add new methods. If you do, you need to update the Web Reference which will automatically get the new changes. If the changes are serious, you will get compile errors which detect that either the method name changed, the number of method arguments changed, or the parameter types changed. If these were just WebRequests you might have never know that changes occurred. Thanks to Web Services, you’ll know right away.

Open the Class1.cs source file. Web References are accessed in an object oriented way. You have to declare them as an object and the initial connection is created by using new. The web service method is invoked here by using objService1.HelloWorld(). The neat part here is that the method is strongly typed. So if you try and pass the wrong parameters, the application won’t even compile. This makes web development really simple. You can use any parameters you like, I just used the default void parameters to make this example simple. The HelloWorld() method returns a String which Console.WriteLine happily outputs to the Console screen.

Hey look! Upon running the application we see “Hello World” which is the String that was returned by the HelloWorld method of WebService1. This may run and close so fast that you need to run from a console. In this example we used a console app. Web References can be used in all the project types. You can even have Web Services that also use Web References to get their data. Although as always, the more layers you add, the slower the app might function. So keep it in mind.

You can download the [project files] associated with this tutorial. Included in the project files is a script called CompileWithMono.cmd which depends on you having Mono installed. If you don’t have Visual Studio .NET you will need to compile with the Mono script.

Creating .NET WebServices with Mono

If you are not familar with Web Services, here are detailed step by step screenshots (15 in all) that will get you up to speed.

You may have Visual Studio .NET or Visual Studio .NET 2003 or Mono MCS. And with these steps you can get a web service up and running at least on Win2k, WinXP, or Win2003. See story for details…
First of all, a few Windows Services need to be running in order to get the built in IIS 5.0 or IIS 6.0 web server up and running. Start by right-clicking My Computer on the Desktop and then select Manage.

This is the Management Console and you’ll find which services are running by looking under [Computer Management] -> [Services and Applications] -> [Services]. The two important services that need to be running are the IIS Admin Service and World Wide Web Publishing. Either can be set to Automatic or Manual. Both need to be started. IIS Admin Services is a dependency of WWW Publishing. If you have WinXP Home Edition, you will need to install IIS. I’ll refer you to this tutorial [IIS on WinXP Home].

If you have Visual Studio .Net the next several steps are automatically done for you using the ASP .NET wizard. If you don’t have Visual Studio .Net then a virtual directory needs to be setup in IIS that looks like the following. I’ll show you the steps in detail next.

First of all, you need to create a directory which is going to hold your Web Service source files. The ASP .NET Web Service defaults to making a directory in C:/Inetpub/wwwroot/WebService1 for you, but since this is a virtual directory, you can put it anywhere the web server has access to (stick to something on the web server’s harddrive). With your destination directory created, go into the Management Console under [Computer Management] -> [Services and Applications] -> [Internet Information Service] -> [Default Web Site]. Right-click the white space in the right panel and select [New] -> [Virtual Directory]. Note: Default Web Site is created by default, but if it has been deleted you may need to create it.

Just click Next.

The ASP .NET Web Service defaults to using “WebService1” but you can use any name here that describes your web service. Keep in mind that this name will be used in the code of future clients. Then click Next.

Specify the directory that you created to hold you source files. Then click Next.

To be able to run your web service code you only need [Read] and [Run scripts] checked. Consider security risks if you check the others. Then click Next.

Just click Next. And your virtual directory is created. Remember these steps are automatic using the ASP .NET Web Service wizard. So if you are using the wizard, let do the above steps for you. Otherwise, it will complain that the virtual directory already exists.

To run the ASP .NET Web Service wizard go into Visual Studio .NET. Click [File] – > [New] -> [Project]. Click [Project Types] -> [Visual C# Projects]. Click [ASP .NET Web Service]. The location will default to WebService1. Then click OK.

The wizard will complete the setup process and auto-generate source files that are added to the project solution. At this point the web service will compile, although there won’t be any methods until you alter the source. Right-click [Service1.asmx] and select [View Code].

The code below is all the code you’ll need to successfully add your first method to the web service. The [WebMethod] attribute allows the compiler to auto generate the XSDL and XML you’ll need to interface this server with a client later. Add yourself a web method and compile the code.

If you don’t have Visual Studio .NET you can jump to this step. And the bottom of this page is a link to download the source files. Unpack the files in the default directory c:/inetpub/wwwroot as indicated by the earlier steps. Follow the steps to create the virtual directory that will point to these files. Assuming you have installed Mono and the bin directory of Mono is in your path, you will be able to run the included Script labelled [CompileWithMono.cmd]. This will let you compile any modications you make to the [WebMethods] that you plan to add to the web service.

At this point, your web service is created. All you need to do is either hit F5 to step through the code (using break-points) or execute the application which will launch the default browser and bring up the web service main page. This page lists all the [WebMethods] you’ve added to the web service. All the methods here can be called from a web reference in a web client app you may choose to write later.

For example, here is the HelloWorld method we just added. This page describes the input and output for the method using SOAP or HTTP POST. All this information is auto-generated for you by simply using the [WebMethod] attribute from the previous step. Since the web service was called from localhost, the invoke button appears to call the method.

By invoking the method, the parameters (if any) are sent to the method and the results are returned. The resulting page is all nicely defined XML.

You can download the [project files] associated with this tutorial. Included in the project files is a script called CompileWithMono.cmd which depends on you having Mono installed. If you don’t have Visual Studio .NET you will need to compile with the Mono script.

Compiling C# Win32 OpenGL with Mono

[Mono] is an Open Source equivalent to .NET which is also compatible with the existing framework. In theory, the pieces you compile in either .NET or Mono should be interchangeable. Mono was originally created to enable .NET runtimes to run on Linux. And now there are Mono binaries for Windows and Linux. Mono 1.0 was officially released June 30,2004….
Given that if you don’t have a copy of .NET or .NET 2003 you can compile with the “free” Mono compiler called “MCS”. MCS is a command line tool, so a typical project like [Triangle (My First OpenGL Program in C#] would use the following command line options to compile.

mcs Class1.cs CSharp.GLU.cs CSharp.OpenGL.cs CSharp.WGL.cs CSharp.Win32.cs

Just make Sure Mono is part of your path, typically the mono path is set to:
c:/Program Files/Mono-1.0/bin

Can I use the Win32 API from a .NET Framework program?

This is a helpful code snippet I found on MSDN about programming Win32 in C#.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/faq111700.asp

Can I use the Win32 API from a .NET Framework program?

Yes. Using platform invoke, .NET Framework programs can access native code libraries by means of static DLL entry points.

Here is an example of C# calling the Win32 MessageBox function:

using System;
using System.Runtime.InteropServices;

class MainApp
{
[DllImport("user32.dll", EntryPoint="MessageBox")]
public static extern int MessageBox(int hWnd, String strMessage, String strCaption, uint uiType);

public static void Main()
{
MessageBox( 0, "Hello, this is PInvoke in operation!", ".NET", 0 );
}
}

New MySQL Tutorial — & Win2k Install

This new tutorial covers where to get a free mysql database and a free apache/mod perl web server. Along with how to create and add tables to your MySQL database. How to configure the Apache MySQL drivers/wrappers and lastly, a sample perl script to connect to your MySQL database. MySql Tutorial.
Some useful commands when installing a MySQL database on Win2k.

  1. Install MySQL in the default directory c:/mysql
  2. Add c:/mysql/bin to the path
  3. Stop an existing MySQL database Windows Service:
    net stop mysql

  4. Remove an existing MySQL service:
    mysqld-nt –remove

  5. Shutdown MySQL if it’s running:
    mysqladmin -u root shutdown

  6. Test if MySQL is configured properly:
    mysqld-nt –console

  7. Again shutdown the MySQL database:
    mysqladmin -u root shutdown

  8. Install MySQL as a Windows Service:
    mysqld-nt –install

  9. Start the MySQL database Windows Service:
    net start mysql

  10. Connect to the MySQL database
    mysql

Commands successfully tested with Win2k and [MySQL 4.0]

Here is more excellent documentation on how to access MySQL from C# using the ODBC drivers. 7.5 Can I access MySQL from .NET environment using Connector/ODBC ?
Exploring MySQL in the Microsoft .NET Environment
ByteFX.Data – MySQL Native .NET Providers

HowTO IIS

When you start ASPX and ASMX programming, the default IIS setings are commonly messed up. The solution is not well documented. The configuration process requires this command in order to work properly.

This properly configures IIS:
aspnet_regiis.exe -i

aspnet_regiis can normally be found:
C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322

These are common commands for IIS:
iisreset /start
iisreset /status
iisreset /stop

HowTo Check the Image Format in C#

This example C# script is a universal example for how to extract image data from just a byte array. You can extract file type and dimensions, easily.

//check image dimensions 
System.Drawing.Image logoImage = System.Drawing.Image.FromStream(new MemoryStream(imageData)); 
if(logoImage.PhysicalDimension.Height > 100 || logoImage.PhysicalDimension.Width > 80) 
        throw m_ef.CreateException("Incorrect Image Dimensions!");

ImageType imageType; 
//determine file type 
if(logoImage.RawFormat.Equals(System.Drawing.Imaging.ImageFormat.Jpeg)) 
        imageType = ImageType.JPG; 
else if(logoImage.RawFormat.Equals(System.Drawing.Imaging.ImageFormat.Gif)) 
        imageType = ImageType.GIF;

HowTo Setup A MySQL Database

This article shows you how to connect your Perl/PHP scripts to a MySQL database.

BASIC OVERVIEW

    Start by skimming the mysql site at MySQL.com.

    The download section has medium and max grade versions of the my sql database. Free to download.

    You might even want to get a copy of "MySQL Control Center" which let's you administer that database and users with a handy GUI.

    Under Contributed APIs, there is a link to "DBI" which let's you connect to MySQL with Perl. There are other drivers down there to connect to whatever platform you want.

    Indigoperl is a good installation that comes with Apache and ModPerl. It's available at indigostar.com

    It doesn't hurt to get yourself a copy of activeperl from activestate.com.

    Afer installing activeperl, just bring up a command prompt and type: "ppm install DBI". That will install all the DBI perl related drivers for you. "perldoc DBI" at the command prompt will give you a lot of information about how to use it.

    "ppm install DBD::mysql" is supposed to work, but has a conflict. So you need to use: "ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd" which installs the mysql wrapper that you need.

    You have to be in ./indigoperl/perl/bin and run the command "ipm install DBI". That will install the drivers for indigoperl/apache.

    Similarly to install the mysql wrapper for indigoperl/apache type: "ipm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd".

    Use the admin tool to create user "db_user" with password "password" granting permissions to at least do queries on tables.

    You may need to type mysqld.exe to startup the mysql database. Bring up the client using mysql.exe. Paste the following to create a table to work with.

    
    create database qafw01;
    
    use qafw01;
    
    drop table TestCase;
    drop table TestExecution;
    
    /*
     ....
     */
    create table TestCase
    (
    	TestCaseID   INT AUTO_INCREMENT PRIMARY KEY,
    	Description  TEXT,
            RunInterval  INT,
    	QueueWebUrl  TEXT,
    	AppName      TEXT,
    	CommandLine  TEXT,
            TestType     TEXT,
            ScriptOutput TEXT,
            LogType      TEXT,
            CaseStatus   TEXT
    );
    
    /*
     ....
     */
    create table TestExecution
    (
    	TestExecutionID   INT AUTO_INCREMENT PRIMARY KEY,
    	TestCaseID        INT,
            LastTimeRun       TEXT,
            Result            TEXT,
            ResultDescription TEXT
    );
    
    -- Setup the following contraints
    
            alter table TestExecution
            add foreign key(TestCaseID) 
            references TestCase (TestCaseID);
    
    -- Create some dummy data to query.
    
    INSERT INTO TestCase
    (Description, RunInterval, QueueWebUrl, AppName,
    CommandLine, TestType, ScriptOutput, LogType,
    CaseStatus)
    VALUES ('Verify...', '3600', 'http://...',
    'Search O&O', 'idptests00tc0001.pl',
    'Perl', 'PIPE', '|', 'A');
    

    Here's an example of a Perl script that works for me in indigoperl. Just put it in ./indigoperl/apache/cgi-bin/ .

    
    #!perl
    
    # In DBD there is "No close statement".
    #
    # Whenever the scalar that holds a database or statement handle
    # loses its value, Msql chooses the appropriate action (frees the
    # result or closes the database connection). So if you want to free
    # the result or close the connection, choose to do one of the following:
    #
    #         undef the handle 
    #
    #         use the handle for another purpose
    #
    #         let the handle run out of scope 
    #
    #         exit the program.
    
    use strict;
    
    #To connect to the mysql database
    use Mysql;
    
    
    #FOR HTTP IO
    use CGI;
    
    #autoflush
    $| = 1;
    
    #HTML FORMAT OUTPUT VARIABLE
    my $co = new CGI;
    
    #START HTML PAGE
    print $co->header;
    
    my $host     = "localhost";
    my $database = "qafw01";
    my $user     = "db_user";
    my $password = "password";
    
    # Connect to database
    my $dbh = Mysql->connect($host, $database, $user, $password);
    
    # Do a query
    my $sql_statement = "SELECT * FROM TestCase LIMIT 1;";
    my $sth = $dbh->query($sql_statement);
    
    # Fetch the result
    my %result = $sth->fetchhash;
    
    # Print the output
    foreach my $key (keys %result)
    {
       print "$key: $result{$key}" . $co->br . "n";
    }
    
    exit;
    

The DOs and DON’Ts of C++ Pointers

This article outlines some of the common mistakes that happen with C++ Pointers.

BASIC OVERVIEW

  1. Pointers are usually indicated by using the '*' symbol before the
    variable name. To declare a pointer the syntax is,
    [data type] * [variable name];
    int *p;
    as an example.

  2. Pointers need to point to a variable of the same type. So if you declare
    int pointer, it will either need to reference another int pointer or
    the address of an int variable. Here is an example of an int pointer referencing the address of an int
    variable. The '&' symbol means address.
    int v;
    int *p;
    p = &v;

  3. Here is an example of an int pointer
    referencing another int pointer.
    int v = 6;
    int *p1 = &v;
    int *p2;
    p2 = p1;

  4. Dereferencing also uses the '*' symbol. What dereferencing does is if
    your pointer points at a variable, when you dereference the pointer
    you can retrieve the value of the variable or alter the variable.
    Here is an example of dereferencing a pointer so that you can
    retrieve the value of the variable.
    int v = 5;
    int *p = &v;
    if(*p == 5)
       v = 6;

  5. Here is an example of dereferencing a pointer so that you can
    alter the value.
    int v;
    int *p = &v;
    *p = 6; // Actually changes v.

  6. The main importance of pointers is the ability to dynamically
    create new instances of objects. The 'new' keyword is used
    to create a variable in memory. It's really important to make
    pointers point to dynamic copies. If a reference is lost to
    a new variable, it turns into a memory leak in your application.
    Here is an example of the 'new' and 'delete' keywords. 'delete'
    just helps us clean up the new variable so we don't get memory
    leaks. 'NULL' is the same as zero.
    int *p = new int(); // Makes new int in memory.
    delete p; // Removes the new int from memory.
    p = NULL; // Make sure we don't access the old int address.

  7. An advantage to using pointers is the ability to dynamically create
    arrays. With a standard array you are stuck with creating a fixed
    size. With a pointer array, you can dynamically create almost any
    size you want. In the previous example we dynamically created a
    single int with 'new int()'. We can create a pointer array by
    inserting a number between the parenthesis like 'new int(100)'.
    The cleanup is a little different because we use array syntax
    '[100]'. The 100 is optional, so you have the option to just
    use '[]'.

    int *p = new int(100); // Dynamically creates an 100 element int array.
    delete [100]p; // Explicitly removes all 100 elements from the array.
    p = NULL; // Make sure we don't access the old int address.

DON'T

Dereferencing is common with pointers. A mistake that is often
made is setting a value to a dereferenced pointer before that
pointer has been initialized.

int i = 2;
int *p;
*p = i;

DON'T

The compiler can catch a lot of your mistakes, but it won't
catch one's like these. This problem is similar to the one
above. This time the pointer has been initialized, but not
with an address of a variable that can hold its data type.

int i = 2;
int *p = 0; // Initialized, but not pointing to an address
*p = i;

DON'T

The same problem happens if we initialize the pointer to a
non address and try to set a value to that dereferenced pointer.

int i;
int *p = 0; // Initialized, but not pointing to an address
*p = 2;

DON'T

The same problem happens if we initialize the pointer to a
non address and try to set a value to that dereferenced pointer.
Here's another look of the same error.

int i;
int *p = NULL; // Initialized, but not pointing to an address
*p = 2;

DO

If you are going to initialize your pointers, you need to use
a real address. This is how you do it.

int i;
int *p = &i;

DO

Here is an example of how to properly set a value to your
dereferenced pointer, step by step.

int i;
int *p;
p = &i;
*p = 2; // This actually sets i to 2.

DO

Here is an example of how to properly set a value to your
dereferenced pointer, in fewer steps.

int i;
int *p = &i;
*p = 2; // This actually sets i to 2.

DON'T

When you use a static pointer, you can run into memory issues.
This problem results in how static works. The first time the
code is executed is when the static pointer is set. The
second time the code is reached, the local variables will have
different memory addresses causing a memory error.

int i = 2;
static int *p = &i;
*p = 1234; // crashes on second lap because i has a different
           // memory address each time this code executes.
  /*
	There are 3 easy solutions to this problem.
	(1) Don't make p static
	(2) Make i static
	(3) Correct the code by splitting it into two statements:
		int i = 2;
		static int *p;
                p = &i;
		*p = 1234;
  */

DO

When working with pointers, type casting is always good so you
know what kind of objects you are working with.

int i;
int *p = (int*)&i;
*p = 1;

DO

The memset function is commonly used with pointers. It helps us
be sure that memory was allocated when we made our variables.
Here is an example of how to use it.

#include  // let's us use the memset function
//...
int i;
if( memset(&i, 0, sizeof(int)) )
   return -1; // Memory was not allocated

DO

The memcpy function is commonly used with pointers. It helps us
be sure that memory was copied properly when we do an assignment.
Here is an example of how to use it.

#include  // let's us use the memset function
//...
int i = 6;
int j;
if( i != *(int*)(memcpy(&j, &i, sizeof(int))) )
   return -1; // Memory was not allocated

DO

In more advanced concepts of C++, ADTs (Abstract Data Types) are
used. Most implementations require lists of some kind. In order
to construct your own list without using arrays, you will need
to use pointers. To make your own objects you can use struct
or class. Both have data members and methods. For a list
implementation, your object will need a pointer to the same type
of object to link them together. Here is a brief example of that
kind of situation. This is a basic structure object with a Value
data member and a pointer to let us create a list. Elements in
abstract lists are commonly referred to as nodes.

You'll see that Node is a pointer with the same type as the
object that it is part of. This is intentional. We want our
list elements to be able to point to other elements in the
list. It makes sense that all the elements in the list
would be the same type of elements.

 struct aMyArray
 {
 public:
    int Value;
    aMyArray *Node;
 }

HowTo MySQL

The following are example commands common to doing database operations in MySQL. AnhD V Nguyen contributed this helpful guide.

-- Same as sysobjects in MS SQL
show tables;

-- Show all Database in MySQL
show databases;

-- Show table detail same as sp_help tblOrders
show columns from Orders;

-- Create Database
CREATE DATABASE Northwind;

-- Drop Database
Drop DATABASE mycs416;

-- Create table Categories
create table Categories
(
	CategoryID INT AUTO_INCREMENT PRIMARY KEY,
	CategoryName varchar (15),
	Description TEXT,
	Picture TEXT
);

-- Create table Customers
create table Customers
(
	CustomerID varchar (40) PRIMARY KEY,
	CompanyName varchar (40),
	ContactName varchar (30),
	ContactTitle varchar (30),
	Address varchar (60),
	City varchar (15),
	Region varchar (15),
	PostalCode varchar (10),
	Country varchar (15),
	Phone varchar (24),
	Fax varchar (24)
);


-- Create table Employees
create table Employees
(
	EmpoyeeID INT AUTO_INCREMENT PRIMARY KEY,
	LastName varchar (20),
	FirstName varchar (10),
	Title varchar (30),
	TitleOfCourtesy varchar (25),
	BirthDate date,
	HireDate date,
	Address varchar (60),
	City varchar (15),
	Region varchar (15),
	PostalCode varchar (10),
	Country varchar (15),
	HomePhone varchar (24),
	Extension varchar (4),
	Photo TEXT,
	Notes TEXT,
	ReportsTo Int
);

-- Create table Order Details
create table Order Details
(
	OrderID INT PRIMARY KEY,
	ProductID INT,
	UnitPrice Decimal(9,2),
	Quantity Integer,
	Discount INT
);


-- Create table Orders
create table Orders
(
	OrderID INT AUTO_INCREMENT PRIMARY KEY,
	CustomerID Text,	
	EmployeeID Int,
	OrderDate DateTime,
	ReqireDate DateTime,
	ShippedDate DateTime,
	ShipVia Int,
	Freight Decimal (9,2),
	ShipName Text,
	ShipAddress varchar (60),
	ShipCity varchar (15),
	ShipRegion varchar (15),
	ShippPostalCode varchar (10),
	ShipCountry varchar (15)
);


-- Create table Products
create table Products
(
	ProductID INT AUTO_INCREMENT PRIMARY KEY,
	ProductName Text,	
	SupplierID Int,
	CategoryID Int,
	QuantityPerUnit Text,
	UnitPrice Decimal (9,2),
	UnitsInStock Int,
	UnitsOnOrder Int,
	ReorderLevel Int,
	Discontinued varchar (3)
);

-- Create table Shippers
create table Shippers
(
	ShipperID INT AUTO_INCREMENT PRIMARY KEY,
	CompanyName varchar (40),	
	Phone varchar (24)
);


-- Create table Suppliers
create table Suppliers
(
	SupplierID INT AUTO_INCREMENT PRIMARY KEY,
	CompanyName varchar (40),	
	ContactName varchar (30),
	ContactTitile varchar (30),
	Address varchar (60),
	City varchar (15),
	Region varchar (15),
	PostalCode varchar (10),
	Country varchar (15),
	Phone varchar (24),
	Fax varchar (24),
	HomePage varchar (50)
);


-- Create table tblCust
create table tblCust
(
	CustomerID varchar (30) PRIMARY KEY,
	UserName varchar (30),	
	Pass varchar (30)
);

-------------------------------------------------------

/* Use the Alter table statement to add the 
   following constraints to (PK)Categories ---> (FK)Products:         	*/
        alter table Products
        add foreign key(CategoryID) 
        references Categories (CategoryID);

/* Use the Alter table statement to add the 
   following constraints to (PK)Supplies ---> (FK)Products:         	*/
        alter table Products
        add foreign key(SupplierID) 
        references Suppliers (SupplierID);

/* Use the Alter table statement to add the 
   following constraints to (PK)Products ---> (FK)OrderDetails:         */
        alter table OrderDetails
        add foreign key(ProductID) 
        references Products (ProductID);


/* Use the Alter table statement to add the 
   following constraints to (PK)Orders ---> (FK)OrderDetails:         	*/
        alter table OrderDetails
        add foreign key(OrderID) 
        references Orders (OrderID);

/* Use the Alter table statement to add the 
   following constraints to (PK)Orders ---> (FK)Employees:    	 	*/
        alter table Employees
        add foreign key(EmployeeID) 
        references Orders (EmployeeID);

/* Use the Alter table statement to add the 
   following constraints to (PK)Shippers ---> (FK)Orders:    	 	*/
        alter table Orders
        add foreign key(ShipperID) 
        references Shippers (ShipVia);


/* Use the Alter table statement to add the 
   following constraints to (PK)Customers ---> (FK)Orders:    	 	*/
        alter table Orders
        add foreign key(CustomerID) 
        references Customers (CustomerID);


/* Use the Alter table statement to add the 
   following constraints to (PK)tblCust ---> (FK)Customers:    	 	*/
        alter table Customers
        add foreign key(CustomerID) 
        references tblCust (CustomerID);

--=======================Load Data from Text File ========================

load data infile 'c:categories.txt' into table Categories(CategoryID,
CategoryName, Description, Picture);


load data infile 'c:suppliers.txt' into table Suppliers(
	CompanyName,	
	ContactName,
	ContactTitile,
	Address,
	City,
	Region,
	PostalCode,
	Country,
	Phone,
	Fax, HomePage);

load data infile 'c:Products.txt' into table Products(
	ProductName,	
	SupplierID,
	CategoryID,
	QuantityPerUnit,
	UnitPrice,
	UnitsInStock,
	UnitsOnOrder,
	ReorderLevel,
	Discontinued);


load data infile 'c:Employees.txt' into table Employees(
	LastName,
	FirstName,
	Title,
	TitleOfCourtesy,
	BirthDate,
	HireDate,
	Address,
	City,
	Region,
	PostalCode,
	Country,
	HomePhone,
	Extension,
	Photo,
	Notes,
	ReportsTo);

load data infile 'c:Shippers.txt' into table Shippers(
	CompanyName,	
	Phone);


load data infile 'c:Customers.txt' into table Customers(
	CustomerID,
	CompanyName,
	ContactName,
	ContactTitle,
	Address,
	City,
	Region,
	PostalCode,
	Country,
	Phone,
	Fax);


load data infile 'c:tblCust.txt' into table tblCust(
	CustomerID,
	UserName,	
	Pass);



load data infile 'c:Orders.txt' into table Orders(
	CustomerID,
	EmployeeID,
	OrderDate,
	ReqireDate,
	ShippedDate,
	ShipVia,
	Freight,
	ShipName,
	ShipAddress,
	ShipCity,
	ShipRegion,
	ShippPostalCode,
	ShipCountry);

--=============================================================
--Export to text file

select * into outfile 'c:order.txt' from orders;

-- Or

Select * from orders into outfile 'c:orders';

-- Or
select * INTO OUTFILE "c:/result.text" FIELDS TERMINATED BY ',' OPTIONALLY
ENCLOSED BY '"' LINES TERMINATED BY "n" FROM test_table;

-- MS SQL run from Dos-Prompt
bcp pubs..publishers out 'c:publishers.txt' -c -Sglamour -Usa -Ppassword

-- limit row_count OFFSET offset
select * from ordres limit 5,10; -- # Retrieve rows 6-15

select * from orders limit 95,-1; # Retrieve rows 96-last.

select * from orders LIMIT 5;     # Retrieve first 5 rows

--================================================================