SQL Tutorial Step-By-Step Model Construction

Building SQL Model

Begin a new model by clicking the button on the toolbar. Click OK on the Model Units window, we will use the default units for our model.

If at any time you encounter difficulties while building this model, a fully functional tutorial model can be found at http://www.flexsim.com/tutorials

Step 1: Create the Objects

Drag objects from your Library Icon Grid onto the 3D View to create the model shown below.

Connect all of the objects as shown:

Connect Source1 to Queue2.
Connect Queue2 to Processor3, Processor4, Processor5 and Processor6.
Connect Processor3, Processor4, Processor5 and Processor6 to Sink7.

Step 2: Setup the SQL Database

We will create an SQL database that we can both read from and write to from our FlexSim model. First, we'll create a table that stores the process times for each processor based on itemtype.

Create an SQL database called "flexsimdata"
Create a table within "flexsimdata" called "processtimes" with 4 columns named "Proc1", "Proc2", "Proc3", and "Proc4". Each of these columns should contain FLOAT number data.
Fill the columns as shown in the following figure:

Step 3: Setup the Source

We will have the Source create 4 different itemtypes.

Change the Source'sInter-Arrivaltime to exponential(0, 3, 0)

Go to the Triggers tab.
Click the add button for the OnCreation trigger.
Select the Set ItemType and Color picklist option.
Change Item Type to duniform(1, 4)

Click OK to apply and close the Properties window.

Step 4: Setup the Processors

Each of the 4 processor's will access the SQL database we just created in order to set their processing time.

Open one of the Processor's properties window.
Click the code edit button next to Process Time.
Enter the following code:

The dbopen() command will access your database and open it up for reading or writing. The first parameter in the name of the database as named in your Windows ODBC Data Source Administrator (not necessarily the name shown in phpMyAdmin). The second parameter in this case is the table you want to read from or write to. The third parameter toggles between table mode (1) and SQL mode (0). We use 1 in this case because we aren't using direct SQL queries, we are declaring a table to work with in the database.
We use a special command called dbgettablenum() to get information from a table in a database. In our case, we want the row to match the item type, and we want each column to represent each processor. You will change the 1 to 2, 3, and 4 for the other processors.
You need to use dbclose() to close the database so other databases can be accessed later on.

Click OK to apply and close the Properties window.

Write the same code on the other three processors, replacing each consecutive processor's second parameter in dbgettablenum() to 2, 3, and 4, respectively.

Reset and Run the model. If you look at the staytime statistic, you should notice that they correspond to the values in the flexsimdata database's processtimes table.

Step 5: Add Staytimes Table to Database

We will now add another table to our SQL database to store the stay times of all the items going through our model.

Create a table within "flexsimdata" called "staytimes" with 2 columns named "ID" and "Staytime". The first column should contain integer data and should autoincrement, and the second column should contain FLOAT number data.

Create one initial row as shown in the following figure with ID = 1 and Staytime = 0.

Step 6: Write to the SQL Database

Each time an item enters the Sink, we will record that item's time in the system (staytime) and write that value to the SQL database.

Open the Sink's properties window.
Go to the Triggers tab.
Click the code edit button button for the OnEntry trigger.
Enter the following code:

string staytime = numtostring(time() - get(stats_creationtime(item)),2, 3);  string query = concat("INSERT INTO  `flexsimdata`.`staytimes` (`Staytime`)VALUES ('", staytime, "');");  string altquery = concat("UPDATE staytimes SET Staytime =", staytime, " WHERE ID =", numtostring(getinput(current)));  dbopen("flexsimdata", "SELECT * FROM staytimes", 0);  int rows = dbgetnumrows();  if (getinput(current) < rows)  {  dbsqlquery(altquery);  }  else  {  dbsqlquery(query);  }  dbclose();

Once we get the stay time into a variable called staytime, we make a query for adding data into the Staytime column of the staytimes table. The alternate query is made as well in case the table already contains data and needs to be over-written. Therefore, we have the if statement requiring us to use the alternate query for as long as there are previously existing rows.

Click OK to apply and close the Properties window.

Reset and Run the model. You should notice the staytimes table of your database filling up with the information gathered at the Sink, similar to the following figure.

This completes the SQL tutorial. Congratulations!

article