- Imagine we want to insert an employee
into our temporary employee's table.
We could write a parameterized SQL statement like this
insert into employee's temp, and then you specify the names
of the columns that you want to pass in,
the names of the columns, employee id, name, salary, region
and the values you want to pass in.
You can actually pass in named parameters.
These are named parameters,
placeholders that will need to be filled in when
we actually execute the statement.
That's a very common technique.
Having a parameterized SQL statement.
The structure of the statement looks good.
The values of it
I can plug those values in when I actually execute it.
For the ID and the name and the salary and the region.
Okay. That parameter would be passed in for the id.
This parameter would be the name.
This parameter would be the salary
and this parameter would be the region.
Obviously it's up to you what you call these placeholders.
I've given them short names here to make my code look
nice and snappy.
Short and snappy, like a baby crocodile.
Right, so that's the SQL that I'd like to execute
to insert some sample data
into my temporary employee's table.
So as we discussed in the previous demo
the PooledConn structure in Rust
it implements the Queryable trait as we've seen.
And in the previous demo we saw that it had
a bunch of methods like query,
and query_iter, and query_map.
It has similar methods called exec,
which can be used for parameterized statements.
So it has functions such as exec,
and exec_iter and exec_first and exec_map and exec_drop.
Very similar to the Queryable examples
we looked at in the last section,
except that the SQL that you provide has parameters
that you need to fill in when you execute it.
So what we're gonna do in the example is I'm going to
execute a parameterized statement to insert
many employees into the database all at once.
This is a batch statement.
I'm gonna give it a bunch of employees, four in particular,
and that all of them are going to be inserted
into the database.
Okay? So in the main code
we'll obviously have a look at the main function
first of all, the function that does
the interesting bits is insert_employees.
So let's take a look at that.
Okay then, so in main,
again, I've reverted my main code
back to its original starting point.
So in my main function, everything here is uncommented.
So we're kind of working our way down here.
We saw initially how to get the connection
and then we saw how to create an employee table.
Now we can look at how to insert employees into that table.
Okay, so I just need to comment out the last two statements
the last two bits, like so.
And then in order to avoid warnings about unused functions
or dead code, I'm just gonna add my attribute
my crate level attribute at the top to allow dead code.
Don't complain
about dead code function that I haven't called.
Right. So let's have a look
at the details for this function.
Insert employees, doesn't return anything
it just does it.
So what I've done is I've created a vector.
Remember my employee structure
it's kinda like an in-memory representation.
This is the data in my application
which I now want to insert into the database.
So an employee has an ID, name, salary and region.
So I create four employees
with an id, name, salary and region. Wales.
And there we go.
So there are four different employee objects in memory.
And then what I do, the pooled connection object
remember we're dealing with a pooled connection object here
as well as the exact methods I just mentioned, like exec
an exec_query and exec_first.
It also has a handy method called exec_batch
where it'll actually perform a bulk operation.
So that's quite handy and quite efficient.
I'm going to insert into the employee's temp.
The columns I need to pass in
will be employee id, name, salary and region.
The values to pass in are parameterized, okay, like that.
And then what I do is I give it a bunch
of different data so it'll execute the insert four times.
So I take my employees
this bit is quite nice, that's my vector.
I iterate over the vector and upon each iteration
I call the map function.
Okay? So it's gonna basically call map
upon the first employee,
and then it'll call map upon the second employee.
Each time round this iteration loop
it'll pass in a different employee into my closure.
This here's a closure.
It'll taken employee or first time it's going to be me.
Did I mention that I live in Wales?
And what it'll do is it'll take that employee
and it invokes the params macro.
Now that params macro is defined in my SQL.
We looked at it about two demos ago.
It's a handy way
of saying basically fill in named parameters.
It says if it knows
that we're talking about an SQL statement here.
Okay? The params macro is all about MySQL.
So it knows that there's some kind of SQL statement involved
and it knows that it's parameterized.
Or what it basically does is it assigns the value
to each parameter.
The macro will say you give it a dictionary, okay
like a hash map.
Like so.
And you say, here are the values
for each name parameter for this employee.
So first time round, for the first employee, that's me,
employee one, Andy, I only earn 25,000 units of pay.
I do it for the love really.
And then the region as Wales.
So those are the employee details, and it'll say,
okay then I need to, what's the value of the i parameter?
The value of the I parameter will be
the current employee's id.
The current employee being me,
and my ID is one, and it'll say,
also, I need to fill in the value for the n parameter.
I called it short names here to make it more concise.
That would be the employee's name
and then the employee's salary, and that's that.
And then the employee's region, and that's that.
You have to be a bit careful passing strings
to pass them in by reference, like so.
Right, because strings don't implement the copy trait.
So we have to be a bit careful there.
So that's really nice.
So what that's gonna do internally
it's gonna execute this insert statement,
basically four times to insert each employee.
It'll basically fill in the parameters
with that employee's ID, and that employee's name,
and that employee's salary, and that employee's region.
And it'll do that for each employee
in my collection and insert those four employees.
Like all the other functions, it returns a result, enum
which I could check to make sure it's worked.
I'm gonna assume that it is gonna work
and I'm gonna run the application.
So as before, I'm not gonna see any tangible output yet.
I'm just gonna hopefully avoid getting
an error message back.
If there was something wrong
I'd get a massive stack trace and an error message.
So the absence of a massive stack trace
and an error message is what I'm looking for here.
Magnificent.