Rock U - Lava - SQL Command

Transcribed Video Content

Sometimes the data you want is not sitting in a lava variable and no amount of filters will get you there. SQL is quite possibly the most powerful command in Rock, but also the one to handle with the most care. You can get back a set of rows, loop over them, and really get any sort of information you need from your database. Let's get into how to use it right now. Here are some simple examples of using the SQL command. So right here, we're doing a basic select and a couple of things I want you to notice is first of all, we have an opening tag here and it's pretty simple, just says SQL. Then we put in the exact SQL that we wanna use and then an in SQL tag. And Rock will essentially run the SQL that's here and turn it into an array that we can use with Lava. Then we're simply using a for statement. We have another video that shows you how to use for statements. You can also read about it in the Lava documentation, but we're taking what's in that array and outputting it. So here's an example. If we really ran this select statement that's getting the people with the last name Decker in our database, here's an example of what it would look . We got the nickname and last name for all of them. We used this for to output all that data, and this is what the output looks . It's really simple with these select statements because we're not updating any data, but it's actually a bit more powerful than you think. With the SQL commands, you can actually use Lava within your SQL. So we're doing the same thing as we did before, but we're utilizing an assign and this makes what we're passing in more a variable than we're using the merge field. Last name that we got here, that variable we created, and we actually put it right inside of our SQL, meaning that we can pass back and forth from SQL to Lava to SQL. And this allows you to really extend what you can do with the SQL command. Then we're simply doing the same thing we did before. We've got that array of people and we've got our output. Now here's an example of something that's a bit more dangerous, but you can do with the SQL command, and that's anything that isn't a select. If you're doing something an update or deleting data, then you're actually gonna have to pass in this parameter statement command. That's because we assume that the SQL command you're using is actually a select. And just so that you're safe and we're safe, we have an extra parameter to add if you're gonna do any kind of data updating or adding or editing. And here's what it looks . So we've put in this parameter saying we're not doing a select, we're doing something else, we're messing with data. We have an update statement here, and it's going, getting workflows and its output, all the information that we've asked for here. And the output isn't going to be what you expect, instead, we're being told what was updated. So in this case, the change that we asked to make is just gonna end up affecting three different workflows And the output we get here says three workflows were updated, so you'll know exactly what your commands affected. If you wanna learn more about the SQL commands, please go to our documentation that's on our community website under the Lava section, and you will find all you need to know about the SQL command and some other parameters that you can use in the command. The SQL command is the sharpest tool in Lava. When you can't get the data you need through other means, use this, just be careful and you can get any amount of data that you need.