Chapter 8 - Pulse SQL Development

AFCON Software and Electronics Ltd
Pulse SQL Development

In this example you will learn how to run SQL queries in pulse.
The example below demonstrate 3 machine lines 1,2,3.
By clicking insert data you will add the "part count" to the production table and
by clicking a row you can update a value or delete a row.
1
Exercise 1: create a connection to a sql table , display the data on a

display and extract the row index from the table.
Step 1: create the SQL Table
1. Create a new table
2. Add the following columns
Enable the identity property to the Inx column (for auto incrementing
number)
2
Type getdate() in the Stamp Column Default Value field (it will add the
current timestamp to the Stamp column on every row insert)
3. Save the table as tblProduction
Step 2 - Create an OLEDB Datasheet Tag Connected to the tblProduction

MS SQL SERVER table
In this exercise, you will create an OLEDB datasheet tag connected to the
tblProduction table and display the table data in a Pulse datasheet instrument.
Finally, you will connect the index column to Pulse addresses in order Select a
column for update or delete.
1 Open the Datasheet Manager.
2 Select Add a Datasheet Tag and click Next.
3
3 Select OLEDB and click Next.
4 click New Connection.
5 In the Data Link Properties Provider tab, choose Microsoft OLE DB Provider
for SQL Server.
4
6 Switch to the connection tab and edit the sql server connection properties.
then click Test Connection and note if the connection succeeded. Click OK.
7 In the Datasheet Manager, click Next.
8 In the SQL Query page, click Query Builder.
5
9 Choose a table and design a query.
10 Click the data tab to execute and check the query. Click OK.
6
11 In the Datasheet Manager, click Next, select Read And Write access rights
and name the tag tblProduction.
12 Click Next and then click Finish.
7
Step 3 - Insert a Pulse Datasheet Instrument to a Display
In this exercise, you will add a Pulse Datasheet instrument to the Production
display, which will retrieve data from the OLEDB database during runtime.
1. From the Insert ribbon, insert a Pulse Datasheet instrument to the display.
2. Double-click the instrument to open its Settings.
3. In the Item field, enter the ProdDB datasheet item using the Item Manager.
8
Step 4: create an alias and a datasheet tag to store the row index
1. Create an Alias
Token = Temp_Index
Step 5: Create the Temp_Index ini Datasheet tag

1 Open the Datasheet Manager.
2 From the first page of the Datasheet Manager, click Next. Then, choose to
add/edit an INI tag and click Next again.
9
3 Enter the path and name of the alias INI data source file and choose the
Temp_data Alias.
4 Select Read And Write access rights and name the tag Temp_Index.
10
5 Click Next to finish.
Step 6: attach the temp_index datasheet tag to the inx Column.
1. Return to datasheet instrument and switch to the column tab.

In the Item field, enter the Temp_index datasheet tag . These item value will
be set whenever the operator double-clicks the appropriate row during
runtime.
11
When you click a row during runtime, Pulse transfers the table row index data
to the alias file and later you can use it for the row update and delete
Quaries.
2. Click OK.
3. Save the Production display.
4. Switch to runtime and observe the table data. You can also modify the
data and click the save button save to update the table.
5. You can type in the upper row in order to filter the data
12
Exercise 2: create an insert SQL query, to log the part count , machine id
and time stamp.
Step 1: create 2 datasheet tags that hold the machine ID and Parts
count.
2. Create 2 Aliases Machine_ID and Machine_counter

Token1 = Machine_ID
Token2 = Machine_Counter
Step 2: Create Datasheet tags

1. Open the Datasheet Manager.
2. From the first page of the Datasheet Manager, click Next. Then, choose to
13
3. Enter the path and name of the alias INI data source file and choose the
Machine_ID Alias.
4. Select Read And Write access rights and name the tag Machine_ID.
5. Click Next to finish.
14
6. Create Machine_counter datasheet tag . Repeat steps 1-5 for the

Machine_Counter Alias.
Step 2 - Create an Insert OLEDB Datasheet Tag to insert data into the
tblProduction MS SQL SERVER table.
2. Select Add a Datasheet Tag and click Next.
3. Select OLEDB and click Next.
4. click New Connection.
5. In the Data Link Properties Provider tab, choose Microsoft OLE DB Provider
for SQL Server.
6 Switch to the connection tab and edit the sql server connection properties.
15
6. In the Datasheet Manager, click next.
7. In the SQL Query page, type the Query. And use the aliases as parameters.
16
8. In the Datasheet Manager, click Next, name the tag Insert_Production.
9. Click Next and then click Finish.
17
Step 3 - Create Button on the display that runs the Insert Query.
1. Create 3 text boxes that represents machine 1 ,2 ,3 part count.
Description Type item

Macine1 counter textbox Simulator|pulse!tag1
2. Create 3 pulse buttons for machine 1 ,2 ,3.
Clicking the button will set the machine id and the current part counts
into the machine_id and machine_counter datasheet tag.
(Those tags are the insert query parameters) and run the insert query.
Machine 1 Action Button

Description item
Action down set DATASHEET|INI!Machine_ID 1;
set DATASHEET|INI!Machine_Counter ?getvalue(simulator|pulse!tag1)?
Action up set DATASHEET|OLEDB!insert_Production 1

Description item
18

Description item
3. Switch to runtime and click the insert button
Every click will cause an insert to the production table
Exercise 3: create an Update SQL query, the user will click a row type
the new value and click the update button.
Step 1: create a datasheet tags that holds the New_Value.
3. Create an Aliases
Token = New_Value
Step 2: Create Datasheet tags

8. From the first page of the Datasheet Manager, click Next. Then, choose to
19
9. Enter the path and name of the alias INI data source file and choose the
New_Value Alias.
20
10. Select Read And Write access rights and name the tag New_Value.
11. Click Next to finish.
12. Create Machine_counter datasheet tag . Repeat steps 1-5 for the
Machine_Counter Alias.
Step 3 - Create an Update OLEDB Datasheet Tag to modify data in the

21
5. In the Data Link Properties Provider tab, choose Microsoft OLE DB Provider
for SQL Server.
6. Switch to the connection tab and edit the sql server connection properties.
22
8. In the SQL Query page, type the Query. And use the aliases as parameters.
23
9. In the Datasheet Manager, click Next, name the tag Update_Production.
24
25
Step 3 - Create Button on the display that runs the update Query.
1. Create a text boxes that represents the new value to update.
Description Type item

New Value textbox DATASHEET|INI!New_Value
4. Create the update button
Clicking the button will run the update query.

Description item
Action up
26
5. Test the update button
1. Switch to runtime double click on a row in the datasheet instrument (to

select a row and update the temp_index tag).
2. Type a new value in the new value text box.
3. Click the update button.
4. See the value change in the datasheet instrument.
Exercise 4: create a Delete SQL query, that deletes the selected row ,
the user will click a row and click the delete button.
Step 1 - Create an delete OLEDB Datasheet Tag to delete a row in the

27
5. In the Data Link Properties Provider tab, choose Microsoft OLE DB

Provider for SQL Server.
6. Switch to the connection tab and edit the sql server connection properties.
then click Test Connection and note if the connection succeeded. Click
OK.
8. In the SQL Query page, type the Query. And use the aliases as
parameters.
28
9. In the Datasheet Manager, click Next, name the tag

Delete_From_Production.
29
Step 2 - Create Button on the display that runs the update Query.
1. Create the delete button.
2. Create the update button
Clicking the button will run the Delete query.

Description item
Action up
3. Test the update button
1. Switch to runtime double click on a row in the datasheet instrument (to

select a row and update the temp_index tag).
2. Click the Delete button.
3. See the value change in the datasheet instrument.
30

Chapter 8 - Pulse SQL Development

Uploaded by

Copyright:

Available Formats

Chapter 8 - Pulse SQL Development

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chapter 8 - Pulse SQL Development

Uploaded by

Copyright:

Available Formats

AFCON Software and Electronics Ltd

Pulse SQL Development

Exercise 1: create a connection to a sql table , display the data on a

Step 1: create the SQL Table

1. Create a new table

2. Add the following columns

3. Save the table as tblProduction

Step 2 - Create an OLEDB Datasheet Tag Connected to the tblProduction

1 Open the Datasheet Manager.

2 Select Add a Datasheet Tag and click Next.

3 Select OLEDB and click Next.

4 click New Connection.

7 In the Datasheet Manager, click Next.

8 In the SQL Query page, click Query Builder.

9 Choose a table and design a query.

12 Click Next and then click Finish.

Step 3 - Insert a Pulse Datasheet Instrument to a Display

2. Double-click the instrument to open its Settings.

Step 5: Create the Temp_Index ini Datasheet tag

5 Click Next to finish.

Step 6: attach the temp_index datasheet tag to the inx Column.

1. Return to datasheet instrument and switch to the column tab.

3. Save the Production display.

2. Create 2 Aliases Machine_ID and Machine_counter

Step 2: Create Datasheet tags

5. Click Next to finish.

6. Create Machine_counter datasheet tag . Repeat steps 1-5 for the

1. Open the Datasheet Manager.

2. Select Add a Datasheet Tag and click Next.

3. Select OLEDB and click Next.

4. click New Connection.

6. In the Datasheet Manager, click next.

8. In the Datasheet Manager, click Next, name the tag Insert_Production.

9. Click Next and then click Finish.

1. Create 3 text boxes that represents machine 1 ,2 ,3 part count.

Description Type item

2. Create 3 pulse buttons for machine 1 ,2 ,3.

Machine 1 Action Button

Machine 2 Action Button

Machine 3 Action Button

3. Switch to runtime and click the insert button

Every click will cause an insert to the production table

Step 1: create a datasheet tags that holds the New_Value.

Step 2: Create Datasheet tags

11. Click Next to finish.

Step 3 - Create an Update OLEDB Datasheet Tag to modify data in the

1. Open the Datasheet Manager.

2. Select Add a Datasheet Tag and click Next.

3. Select OLEDB and click Next.

4. click New Connection.

7. In the Datasheet Manager, click next.

9. In the Datasheet Manager, click Next, name the tag Update_Production.

10. Click Next and then click Finish.

1. Create a text boxes that represents the new value to update.

Description Type item

4. Create the update button

Clicking the button will run the update query.

Machine 1 Action Button

5. Test the update button