Skip to content

14. Appendices

14.1. The SGBD SQL Server Express 2005

14.1.1. Installation

The SGBD SQL Server Express 2005 is available at url [http://msdn.microsoft.com/vstudio/express/sql/download/] :

  • in [1]: first download and install platform .NET 2.0
  • in [2]: then install and download SQL Server Express 2005
  • in [3]: then install and download SQL Server Management Studio Express to administer SQL Server

Installation of SQL Server Express creates a folder in [Start / Programs ] :

  • in [1]: the SQL Server configuration application. Also allows you to start/stop the server
  • in [2]: the server administration application

14.1.2. Start / Stop SQL Server

As with the previous SGBD, SQL server Express has been installed as an autostart Windows service. We now change this configuration:

[Start / Control Panel / Performance and maintenance / Administrative tools / Services ] :

  • in [1]: we double-click on [Services]
  • in [2]: we see that a service called [SQL Server] is present, that it is started [3] and that it starts automatically [4].
  • in [5]: another service linked to SQL Server, called "SQL Server Browser", is also active and self-starting.

To change this, we double-click on the [SQL Server] service:

  • in [1]: set the service to manual start-up
  • en [2]: we stop it
  • in [3]: the new service configuration is validated

The same applies to the [SQL Server Browser] service (see [5] above). To start and stop the service SQL Server 2005, you can use application [1] in folder [SQL server] :

  • in [1]: check that the TCP/IP protocol is active (enabled), then go to protocol properties.
  • in [2]: in the [IP Addresses] tab, option [IPAll] :
    • field [TCP Dynamic ports] is left empty
    • the server listening port is set to 1433 in [TCP Port]
  • in [3]: right-click on the [SQL Server] service to access server start/stop options. right-click on [Ici Server] to start it.
  • in [4]: SQL Server is launched

14.1.3. Creating a jpa user and a jpa database

Let's launch the SGBD as shown above, then the administration application [1] via the menu below:

  • in [1]: connect to SQL Server as adirector Windows
  • in [2]: configure connection properties
  • in [3]: a mixed mode of connection to the server is authorized: either with a windows login (a windows user), or with a SQL Server login (an account defined within SQL Server, independent of any windows account).
  • in [3b]: create a user SQL Server
  • in [4]: option [General]
  • in [5]: the login
  • in [6]: the password (jpa ici)
  • in [7]: option [Server Roles]
  • in [8]: the user jpa will have the right to create databases

This configuration is validated:

  • in [9]: the user jpa was created
  • en [10]: disconnect
  • in [11]: we reconnect
  • in [12]: connect as user jpa/jpa
  • in [13]: once logged in, the user jpa creates a database
  • in [14]: the base will be called jpa
  • in [15]: and will belong to the user jpa
  • in [16]: the base jpa was created

14.1.4. Creation of table [ARTICLES] in the jpa database

We create a table [ARTICLES] from the following SQL script:


/* table creation */
 
CREATE TABLE ARTICLES (
    ID            INTEGER NOT NULL,
    NOM           VARCHAR(20) NOT NULL,
    PRIX          DOUBLE PRECISION NOT NULL,
    STOCKACTUEL   INTEGER NOT NULL,
    STOCKMINIMUM  INTEGER NOT NULL
);
 
INSERT INTO ARTICLES (ID, NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES (1, 'article1', 100, 10, 1);
INSERT INTO ARTICLES (ID, NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES (2, 'article2', 200, 20, 2);
INSERT INTO ARTICLES (ID, NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES (3, 'article3', 300, 30, 3);
 
/* integrity constraints */
 
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_ID check (ID>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_PRIX check (PRIX>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKACTUEL check (STOCKACTUEL>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKMINIMUM check (STOCKMINIMUM>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_NOM check (NOM<>'');
ALTER TABLE ARTICLES ADD CONSTRAINT UNQ_NOM UNIQUE (NOM);
 
 
/* primary key */
 
ALTER TABLE ARTICLES ADD CONSTRAINT PK_ARTICLES PRIMARY KEY (ID);
  • in [1]: open a SQL script
  • in [2]: we designate the SQL script
  • in [3]: you must re-identify yourself (jpa/jpa)
  • in [4]: the script to be executed
  • in [5]: select the database in which the script will be executed
  • in [6]: execute it
  • in [7]: the result of execution: table [ARTICLES] has been created.
  • in [8]: we ask to see its contents
  • in [9]: table contents.

14.1.5. The ADO.NET connector of SQL Server Express

The ADO.NET connector is the set of classes that enable a .NET application to use the SGBD SQL Server Express 2005. The connector classes are in the [System.Data] namespace, natively available on all .NET platforms.

14.2. The SGBD MySQL5

14.2.1. Installation

The SGBD MySQL5 is available at url [http://dev.mysql.com/downloads/] :

  • in [1]: select the desired version
  • en [2]: choose a Windows version
  • in [3]: select the desired Windows version
  • in [4]: the downloaded zip contains an executable [Setup.exe] [4b] which must be extracted and run to install MySQL5
  • en [5]: select a typical installation
  • in [6]: once installation is complete, you can configure the MySQL5 server
  • in [7]: choose a standard configuration, the one that raises the fewest questions
  • in [8]: the MySQL5 server will be a Windows service
  • in [9]: by default, the server administrator is root without password. You can keep this configuration or give a new password to root. If the installation of MySQL5 comes after the uninstallation of a previous version, this operation may fail. There are fewer ways of getting back to it.
  • in [10]: server configuration is requested

Installing MySQL5 creates a folder in [Start / Programs ] :

Image

You can use [MySQL Server Instance Config Wizard] to reconfigure the server:

  • in [3]: we change the root password (ici root/root)

14.2.2. Start / Stop MySQL5

The MySQL5 server has been installed as an autostart windows service, with c.a.d launched as soon as windows starts up. This mode of operation is not very practical. We're going to change it:

[Start / Control Panel / Performance and maintenance / Administrative tools / Services ] :

  • in [1]: we double-click on [Services]
  • in [2]: we see that a service called [MySQL] is present, that it is started [3] and that it starts automatically [4].

To change this, we double-click on the [MySQL] service:

  • in [1]: set the service to manual start-up
  • en [2]: we stop it
  • in [3]: the new service configuration is validated

To manually start and stop the MySQL service, you can create two shortcuts:

  • in [1]: the shortcut to launch MySQL5
  • in [2]: the shortcut to stop it

14.2.3. Customers administration MySQL

On the MySQL website, you can find administration clients for SGBD :

  • in [1]: select [MySQL GUI Tools], which brings together various graphical clients for administering and operating the SGBD
  • in [2]: select the appropriate Windows version
  • in [3]: an .msi file is retrieved for execution
  • in [4]: once installation is complete, new shortcuts appear in the [Start menu / Programs / mySQL] folder.

Let's run MySQL (via the shortcuts you've created), then run [MySQL Administrator] via the menu above:

  • in [1]: enter the root user password (root ici)
  • in [2]: you are logged in and MySQL is active

14.2.4. Creating a jpa user and a jpa database

We now create a database called jpa and a user with the same name. First the user :

  • in [1]: select [User Administration]
  • in [2]: right-click in the [User accounts] section to create a new user
  • in [3]: the user's name is jpa and its password is jpa
  • in [4]: validate creation
  • in [5]: user [jpa] appears in the [User Accounts] window

The database now :

  • in [1]: select option [Catalogs]
  • in [2]: right-click on the [Schemata] window to create a new schema (designating a database)
  • in [3]: we name the new diagram
  • in [4]: it appears in the [Schemata] window
  • in [5]: select the [jpa] scheme
  • in [6]: objects from the [jpa] schema appear, including tables. There are none yet. Right-click to create one. We leave that to the reader.

Let's go back to the user [jpa] and give him full rights to the [jpa] schema:

  • in [1], then [2]: user [jpa] is selected
  • in [3]: select the [Schema Privileges] tab
  • in [4]: select the [jpa] scheme
  • in [5]: we'll give the user [jpa] full privileges over the schema [jpa]
  • in [6]: validate the changes made

To check that user [jpa] can work with schema [jpa], we close administrator MySQL. Restart it and connect this time under the name [jpa/jpa]:

  • en [1]: we identify ourselves (jpa/jpa)
  • in [2]: the connection was successful and in [Schemata], we see the schemas to which we have rights. We see the [jpa] schema.

We're now going to create a table [ARTICLES] using a SQL script.

  • in [1]: use application [MySQL Query Browser]
  • in [2], [3], [4]: identify yourself (jpa / jpa / jpa)
  • in [5]: open a SQL script for execution
  • in [6]: designate the following script [schema-articles.sql]: 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE ARTICLES (
    ID            INTEGER NOT NULL,
    NOM           VARCHAR(20) NOT NULL,
    PRIX          DOUBLE PRECISION NOT NULL,
    STOCKACTUEL   INTEGER NOT NULL,
    STOCKMINIMUM  INTEGER NOT NULL
);

INSERT INTO ARTICLES (ID, NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES (1,'article1', 100, 10, 1);
INSERT INTO ARTICLES (ID, NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES (2,'article2', 200, 20, 2);
INSERT INTO ARTICLES (ID, NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES (3,'article3', 300, 30, 3);

COMMIT WORK;



/* Check constraints definition */

ALTER TABLE ARTICLES ADD CONSTRAINT CHK_ID check (ID>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_PRIX check (PRIX>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKACTUEL check (STOCKACTUEL>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKMINIMUM check (STOCKMINIMUM>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_NOM check (NOM<>'');


/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE ARTICLES ADD CONSTRAINT UNQ_NOM UNIQUE (NOM);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE ARTICLES ADD CONSTRAINT PK_ARTICLES PRIMARY KEY (ID);
  • in [7]: the loaded script
  • in [8]: it is executed
  • in [9]: table [ARTICLES] has been created

14.2.5. Connector installation ADO.NET from MySQL5

The ADO.NET connector for MySQL5 is available (April 2008) at [http://dev.mysql.com/downloads/connector/net/5.1.html] :

Installing this connector adds a namespace to the .NET platform:

14.2.6. Driver installation ODBC from MySQL5

The ODBC connector (Open DataBase Connectivity) from MySQL5 is available (April 2008) at [http://dev.mysql.com/downloads/connector/odbc/3.51.html] :

After installation, the presence of the ODBC connector can be checked as follows:

  • in [1], select [Administrative tools] (on XP Pro: Start menu / Control panel / Performance and maintenance / Administration tools)
  • in [2], double-click on [Data sources (ODBC)]
  • in [3], select the [Drivers ODBC] tab
  • in [4], the ODBC driver of MySQL
https://stahe.github.io/
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.