Tutorial 2: Connecting to Database

Let's make a connection to a MySQL database through a component of type Code.

Note: The code in this tutorial is only for illustrating the use of SistevProcess, so not recommended for use in production applications, as they can have security problems and other vulnerabilities.

Note: Although a Database component that facilitates this task, we will use a code component, for this tutorial.

For this tutorial we need to create a MySQL database and a sample table. In this sense we will create a database called "examples". Then we create a table called "cars", the following is the SQL table example:

 CREATE TABLE `cars` (  
  `id` int(11) NOT NULL,  
  `name` varchar(40) DEFAULT NULL,  
  `year` varchar(50) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  UNIQUE KEY `id` (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;  

Then insert some test records:

 INSERT INTO `examples`.`cars`  
 (`id`,`name`,`year`)  
 VALUES  
 ('1', 'Mercedes', '2000'),  
 ('2', 'BMW', '2004'),  
 ('3', 'Audi', '2001');  

Now let's create a new project and a process within SistevProcess, we will drag a component code to the workspace. Then double click on the component name to open the configuration window of the component. Here we name the component with the name, "ConexionBD". Now we will write the following code:

 $username = "YOUR_USER";  
 $password = "YOUR_PASSWORD";  
 $hostname = "localhost";  

In the above code you should replace the data user, password and server details of your local connection. Then continue another block of code:

1:  $username = "YOUR_USER";  
2:  $password = "YOUR_PASSWORD";  
3:  $hostname = "localhost";  
4:  $dbhandle = mysql_connect($hostname, $username, $password)   
5:       or die("Unable to connect to MySQL");  
6:  echo "Connected to MySQL<br/>";  

The last three lines of code allow connection to MySQL server otherwise the program terminates with the following message: "Unable to connect to MySQL". Then:

1:  $username = "YOUR_USER";  
2:  $password = "YOUR_PASSWORD";  
3:  $hostname = "localhost";  
4:  $dbhandle = mysql_connect($hostname, $username, $password)   
5:       or die("Unable to connect to MySQL");  
6:  echo "Connected to MySQL<br/>";  
7:  $selected = mysql_select_db("examples",$dbhandle)   
8:   or die("Could not select examples");  

Then select the Database "examples", otherwise the program terminates with the message "Could not select examples". Continued:

1:  $username = "YOUR_USER";  
2:  $password = "YOUR_PASSWORD";  
3:  $hostname = "localhost";  
4:  $dbhandle = mysql_connect($hostname, $username, $password)   
5:       or die("Unable to connect to MySQL");  
6:  echo "Connected to MySQL<br/>";  
7:  $selected = mysql_select_db("examples",$dbhandle)   
8:   or die("Could not select examples");  
9:  $result = mysql_query("SELECT id, name,year FROM cars");  

In the last line we execute an SQL query that allows the records stored in the cars table, this query is stored in the variable "$result". Continued:

1:  $username = "YOUR_USER";  
2:  $password = "YOUR_PASSWORD";  
3:  $hostname = "localhost";  
4:  $dbhandle = mysql_connect($hostname, $username, $password)   
5:       or die("Unable to connect to MySQL");  
6:  echo "Connected to MySQL<br/>";  
7:  $selected = mysql_select_db("examples",$dbhandle)   
8:   or die("Could not select examples");  
9:  $result = mysql_query("SELECT id, name,year FROM cars");  
10:  while ($row = mysql_fetch_array($result)) {  
11:    echo "ID:".$row{'id'}." Name:".$row{'name'}."Year: ".  
12:    $row{'year'}."<br>";  
13:  }  

In the last three lines create a while loop that prints the records we have consulted, and finally:

1:  $username = "YOUR_USER";  
2:  $password = "YOUR_PASSWORD";  
3:  $hostname = "localhost";  
4:  $dbhandle = mysql_connect($hostname, $username, $password)   
5:       or die("Unable to connect to MySQL");  
6:  echo "Connected to MySQL<br/>";  
7:  $selected = mysql_select_db("examples",$dbhandle)   
8:   or die("Could not select examples");  
9:  $result = mysql_query("SELECT id, name,year FROM cars");  
10:  while ($row = mysql_fetch_array($result)) {  
11:    echo "ID:".$row{'id'}." Name:".$row{'name'}."Year: ".  
12:    $row{'year'}."<br>";  
13:  }  
14:  mysql_close($dbhandle);  

The last line closes the connection to the database. Let's preview the process and if all goes well we should see a screen like this:

Congratulations! I successfully created a connection SistevProcess database.