1
00:00:06,570 --> 00:00:08,880
- Imagine we want to insert an employee

2
00:00:08,880 --> 00:00:12,120
into our temporary employee's table.

3
00:00:12,120 --> 00:00:15,480
We could write a parameterized
SQL statement like this

4
00:00:15,480 --> 00:00:19,440
insert into employee's temp,
and then you specify the names

5
00:00:19,440 --> 00:00:23,010
of the columns that you want to pass in,

6
00:00:23,010 --> 00:00:26,850
the names of the columns,
employee id, name, salary, region

7
00:00:26,850 --> 00:00:28,560
and the values you want to pass in.

8
00:00:28,560 --> 00:00:31,473
You can actually pass in named parameters.

9
00:00:32,940 --> 00:00:34,320
These are named parameters,

10
00:00:34,320 --> 00:00:37,670
placeholders that will
need to be filled in when

11
00:00:37,670 --> 00:00:39,060
we actually execute the statement.

12
00:00:39,060 --> 00:00:40,350
That's a very common technique.

13
00:00:40,350 --> 00:00:43,050
Having a parameterized SQL statement.

14
00:00:43,050 --> 00:00:45,780
The structure of the statement looks good.

15
00:00:45,780 --> 00:00:46,890
The values of it

16
00:00:46,890 --> 00:00:49,710
I can plug those values in
when I actually execute it.

17
00:00:49,710 --> 00:00:53,583
For the ID and the name and
the salary and the region.

18
00:00:54,510 --> 00:00:57,960
Okay. That parameter would
be passed in for the id.

19
00:00:57,960 --> 00:00:59,730
This parameter would be the name.

20
00:00:59,730 --> 00:01:01,140
This parameter would be the salary

21
00:01:01,140 --> 00:01:02,910
and this parameter would be the region.

22
00:01:02,910 --> 00:01:05,760
Obviously it's up to you what
you call these placeholders.

23
00:01:05,760 --> 00:01:08,250
I've given them short names
here to make my code look

24
00:01:08,250 --> 00:01:09,150
nice and snappy.

25
00:01:09,150 --> 00:01:11,403
Short and snappy, like a baby crocodile.

26
00:01:12,270 --> 00:01:15,180
Right, so that's the SQL
that I'd like to execute

27
00:01:15,180 --> 00:01:17,850
to insert some sample data

28
00:01:17,850 --> 00:01:20,043
into my temporary employee's table.

29
00:01:20,880 --> 00:01:24,480
So as we discussed in the previous demo

30
00:01:24,480 --> 00:01:27,930
the PooledConn structure in Rust

31
00:01:27,930 --> 00:01:31,050
it implements the Queryable
trait as we've seen.

32
00:01:31,050 --> 00:01:33,630
And in the previous
demo we saw that it had

33
00:01:33,630 --> 00:01:35,460
a bunch of methods like query,

34
00:01:35,460 --> 00:01:38,130
and query_iter, and query_map.

35
00:01:38,130 --> 00:01:40,560
It has similar methods called exec,

36
00:01:40,560 --> 00:01:43,860
which can be used for
parameterized statements.

37
00:01:43,860 --> 00:01:46,838
So it has functions such as exec,

38
00:01:46,838 --> 00:01:51,480
and exec_iter and exec_first
and exec_map and exec_drop.

39
00:01:51,480 --> 00:01:54,930
Very similar to the Queryable examples

40
00:01:54,930 --> 00:01:57,600
we looked at in the last section,

41
00:01:57,600 --> 00:02:01,500
except that the SQL that
you provide has parameters

42
00:02:01,500 --> 00:02:03,933
that you need to fill
in when you execute it.

43
00:02:05,130 --> 00:02:08,220
So what we're gonna do in
the example is I'm going to

44
00:02:08,220 --> 00:02:10,650
execute a parameterized
statement to insert

45
00:02:10,650 --> 00:02:14,460
many employees into the
database all at once.

46
00:02:14,460 --> 00:02:16,440
This is a batch statement.

47
00:02:16,440 --> 00:02:20,520
I'm gonna give it a bunch of
employees, four in particular,

48
00:02:20,520 --> 00:02:22,770
and that all of them
are going to be inserted

49
00:02:22,770 --> 00:02:24,120
into the database.

50
00:02:24,120 --> 00:02:26,100
Okay? So in the main code

51
00:02:26,100 --> 00:02:27,360
we'll obviously have a
look at the main function

52
00:02:27,360 --> 00:02:29,490
first of all, the function that does

53
00:02:29,490 --> 00:02:32,280
the interesting bits is insert_employees.

54
00:02:32,280 --> 00:02:33,730
So let's take a look at that.

55
00:02:34,590 --> 00:02:36,690
Okay then, so in main,

56
00:02:36,690 --> 00:02:38,910
again, I've reverted my main code

57
00:02:38,910 --> 00:02:41,610
back to its original starting point.

58
00:02:41,610 --> 00:02:46,610
So in my main function,
everything here is uncommented.

59
00:02:47,220 --> 00:02:49,140
So we're kind of working
our way down here.

60
00:02:49,140 --> 00:02:51,630
We saw initially how to get the connection

61
00:02:51,630 --> 00:02:54,360
and then we saw how to
create an employee table.

62
00:02:54,360 --> 00:02:57,430
Now we can look at how to insert
employees into that table.

63
00:02:57,430 --> 00:03:01,500
Okay, so I just need to comment
out the last two statements

64
00:03:01,500 --> 00:03:04,440
the last two bits, like so.

65
00:03:04,440 --> 00:03:08,580
And then in order to avoid
warnings about unused functions

66
00:03:08,580 --> 00:03:12,900
or dead code, I'm just
gonna add my attribute

67
00:03:12,900 --> 00:03:16,200
my crate level attribute at
the top to allow dead code.

68
00:03:16,200 --> 00:03:17,310
Don't complain

69
00:03:17,310 --> 00:03:19,683
about dead code function
that I haven't called.

70
00:03:21,270 --> 00:03:22,710
Right. So let's have a look

71
00:03:22,710 --> 00:03:24,270
at the details for this function.

72
00:03:24,270 --> 00:03:28,320
Insert employees, doesn't return anything

73
00:03:28,320 --> 00:03:29,223
it just does it.

74
00:03:30,330 --> 00:03:34,110
So what I've done is
I've created a vector.

75
00:03:34,110 --> 00:03:36,630
Remember my employee structure

76
00:03:36,630 --> 00:03:38,820
it's kinda like an
in-memory representation.

77
00:03:38,820 --> 00:03:40,740
This is the data in my application

78
00:03:40,740 --> 00:03:44,580
which I now want to
insert into the database.

79
00:03:44,580 --> 00:03:47,823
So an employee has an ID,
name, salary and region.

80
00:03:48,750 --> 00:03:51,330
So I create four employees

81
00:03:51,330 --> 00:03:56,280
with an id, name, salary
and region. Wales.

82
00:03:56,280 --> 00:03:57,660
And there we go.

83
00:03:57,660 --> 00:04:01,200
So there are four different
employee objects in memory.

84
00:04:01,200 --> 00:04:04,440
And then what I do, the
pooled connection object

85
00:04:04,440 --> 00:04:07,462
remember we're dealing with a
pooled connection object here

86
00:04:07,462 --> 00:04:10,740
as well as the exact methods
I just mentioned, like exec

87
00:04:10,740 --> 00:04:13,110
an exec_query and exec_first.

88
00:04:13,110 --> 00:04:15,930
It also has a handy
method called exec_batch

89
00:04:15,930 --> 00:04:19,740
where it'll actually
perform a bulk operation.

90
00:04:19,740 --> 00:04:23,460
So that's quite handy and quite efficient.

91
00:04:23,460 --> 00:04:26,820
I'm going to insert into
the employee's temp.

92
00:04:26,820 --> 00:04:28,140
The columns I need to pass in

93
00:04:28,140 --> 00:04:32,190
will be employee id,
name, salary and region.

94
00:04:32,190 --> 00:04:36,120
The values to pass in are
parameterized, okay, like that.

95
00:04:36,120 --> 00:04:38,370
And then what I do is I give it a bunch

96
00:04:38,370 --> 00:04:42,630
of different data so it'll
execute the insert four times.

97
00:04:42,630 --> 00:04:44,230
So I take my employees

98
00:04:45,150 --> 00:04:48,420
this bit is quite nice, that's my vector.

99
00:04:48,420 --> 00:04:52,770
I iterate over the vector
and upon each iteration

100
00:04:52,770 --> 00:04:54,480
I call the map function.

101
00:04:54,480 --> 00:04:56,430
Okay? So it's gonna basically call map

102
00:04:56,430 --> 00:04:58,500
upon the first employee,

103
00:04:58,500 --> 00:05:01,420
and then it'll call map
upon the second employee.

104
00:05:01,420 --> 00:05:05,550
Each time round this iteration loop

105
00:05:05,550 --> 00:05:08,400
it'll pass in a different
employee into my closure.

106
00:05:08,400 --> 00:05:09,510
This here's a closure.

107
00:05:09,510 --> 00:05:14,220
It'll taken employee or first
time it's going to be me.

108
00:05:14,220 --> 00:05:16,530
Did I mention that I live in Wales?

109
00:05:16,530 --> 00:05:19,530
And what it'll do is
it'll take that employee

110
00:05:19,530 --> 00:05:21,813
and it invokes the params macro.

111
00:05:22,651 --> 00:05:25,560
Now that params macro
is defined in my SQL.

112
00:05:25,560 --> 00:05:28,020
We looked at it about two demos ago.

113
00:05:28,020 --> 00:05:29,190
It's a handy way

114
00:05:29,190 --> 00:05:33,240
of saying basically fill
in named parameters.

115
00:05:33,240 --> 00:05:34,800
It says if it knows

116
00:05:34,800 --> 00:05:37,170
that we're talking about
an SQL statement here.

117
00:05:37,170 --> 00:05:40,590
Okay? The params macro is all about MySQL.

118
00:05:40,590 --> 00:05:43,590
So it knows that there's some
kind of SQL statement involved

119
00:05:43,590 --> 00:05:45,920
and it knows that it's parameterized.

120
00:05:45,920 --> 00:05:47,880
Or what it basically does
is it assigns the value

121
00:05:47,880 --> 00:05:48,900
to each parameter.

122
00:05:48,900 --> 00:05:53,670
The macro will say you
give it a dictionary, okay

123
00:05:53,670 --> 00:05:54,633
like a hash map.

124
00:05:55,800 --> 00:05:56,730
Like so.

125
00:05:56,730 --> 00:05:59,670
And you say, here are the values

126
00:05:59,670 --> 00:06:02,400
for each name parameter for this employee.

127
00:06:02,400 --> 00:06:06,180
So first time round, for the
first employee, that's me,

128
00:06:06,180 --> 00:06:10,890
employee one, Andy, I only
earn 25,000 units of pay.

129
00:06:10,890 --> 00:06:12,240
I do it for the love really.

130
00:06:12,240 --> 00:06:15,090
And then the region as Wales.

131
00:06:15,090 --> 00:06:18,420
So those are the employee
details, and it'll say,

132
00:06:18,420 --> 00:06:21,930
okay then I need to, what's
the value of the i parameter?

133
00:06:21,930 --> 00:06:23,760
The value of the I parameter will be

134
00:06:23,760 --> 00:06:25,920
the current employee's id.

135
00:06:25,920 --> 00:06:28,680
The current employee being me,

136
00:06:28,680 --> 00:06:31,920
and my ID is one, and it'll say,

137
00:06:31,920 --> 00:06:35,490
also, I need to fill in the
value for the n parameter.

138
00:06:35,490 --> 00:06:39,270
I called it short names here
to make it more concise.

139
00:06:39,270 --> 00:06:41,080
That would be the employee's name

140
00:06:41,970 --> 00:06:46,970
and then the employee's
salary, and that's that.

141
00:06:47,130 --> 00:06:49,452
And then the employee's
region, and that's that.

142
00:06:49,452 --> 00:06:51,870
You have to be a bit
careful passing strings

143
00:06:51,870 --> 00:06:54,423
to pass them in by reference, like so.

144
00:06:55,440 --> 00:06:57,840
Right, because strings don't
implement the copy trait.

145
00:06:57,840 --> 00:06:59,550
So we have to be a bit careful there.

146
00:06:59,550 --> 00:07:00,390
So that's really nice.

147
00:07:00,390 --> 00:07:01,920
So what that's gonna do internally

148
00:07:01,920 --> 00:07:03,960
it's gonna execute this insert statement,

149
00:07:03,960 --> 00:07:08,730
basically four times to
insert each employee.

150
00:07:08,730 --> 00:07:10,530
It'll basically fill in the parameters

151
00:07:10,530 --> 00:07:13,860
with that employee's ID,
and that employee's name,

152
00:07:13,860 --> 00:07:16,110
and that employee's salary,
and that employee's region.

153
00:07:16,110 --> 00:07:18,300
And it'll do that for each employee

154
00:07:18,300 --> 00:07:23,190
in my collection and insert
those four employees.

155
00:07:23,190 --> 00:07:26,130
Like all the other functions,
it returns a result, enum

156
00:07:26,130 --> 00:07:28,473
which I could check to
make sure it's worked.

157
00:07:29,610 --> 00:07:31,320
I'm gonna assume that it is gonna work

158
00:07:31,320 --> 00:07:33,330
and I'm gonna run the application.

159
00:07:33,330 --> 00:07:38,330
So as before, I'm not gonna
see any tangible output yet.

160
00:07:38,460 --> 00:07:40,440
I'm just gonna hopefully avoid getting

161
00:07:40,440 --> 00:07:42,060
an error message back.

162
00:07:42,060 --> 00:07:43,740
If there was something wrong

163
00:07:43,740 --> 00:07:46,830
I'd get a massive stack
trace and an error message.

164
00:07:46,830 --> 00:07:49,230
So the absence of a massive stack trace

165
00:07:49,230 --> 00:07:51,903
and an error message is
what I'm looking for here.

166
00:07:59,190 --> 00:08:00,123
Magnificent.