[00:00]
In this lesson, we'll demonstrate the use of DB browse binding which is functionally equivalent to SQL query binding in that you can bring back specified data from a database. However, DB browse binding lets you visually select the database info of interest. You can pick the table you want to pull information from and you can see the query which gets dynamically generated this binding type can be really helpful. If you are trying to learn how to write SQL queries. So starting with this label component, I can select it's text property binding and select the SQL DB browse binding type this lets us see all Ignition database connections that exist. Selecting the db_iu database, I can see all tables that exist and I can select the customers table to preview. What it's data looks like for starters, let's display one specific table value back in our label. How about this first name in row 1, Matteo. If I select that DB element, you can see that the DB browse binding created the needed query for us select first name from customers where customer_id equals one in the first row. We'll go ahead and press OK and we see that Matteo is displayed on our label.
[01:27]
Next, let's do likewise for a table. This time we'll select the table, go to its data property binding once again select the DB browse binding type The db_iu database and the customers table again here. I can select one or more values that I want to bring back and notice that in all cases. The auto-created query is updated accordingly. However notice that any data selection is limited to one particular row or DB record, I can't select multiple rows of data. This is because of this key column field here, which is like a filter on one specific customer ID. If we expand the customer's table, we see this little key icon on the customer_id to remove this as a key column. We simply select the customer ID field then press the icon with a key and tiny slash to remove the key icon since now we have no key columns.
[02:30]
Now, we can retrieve multiple records for multiple columns of data. So suppose we want to return all customers the first name the last name and the email address. We can simply select all records and notice that the needed query is auto-generated as always: select first name, last name, email from customers. If we then press OK we see that all the specified data is returned to our table. DB browse binding also allows us to do ordering of data.
[03:02]
Let's select the table once more then select its data binding. And right now we have three fields selected but let's say now we want to sort by first name A to Z. We'll select the first name then on the right. We have this sort icon where we can sort off ascending, descending, and no sort at all. So note that the generated query is updated each time to reflect our selection. We'll click this one more time to sort ascending A to Z. Now, we can also select the first name, last name, and email columns again. Click OK and we see the same data as before only this time sorted A to Z on the first name. So, wrapping up, it's quite easy to use the DB browse binding type to visually pick and choose data to return from database tables as well as dynamically create the needed SQL queries.