- In the previous demo, we showed how to get a connection
to a database represented by a PooledConn object.
Once we've got the PooledConn object,
you can then invoke functions upon it to execute queries.
So you need to find out what functions
does the PooledConn structure have to execute those queries.
So we need to look at the documentation.
Okay, so here's the documentation for the mysql crate,
particularly looking at the PooledConn structure.
Let's see what functions it provides.
Okay, so here's the PooledConn struct
here in the mysql crate,
and it's got a little bit of an example here.
If you scroll down over here,
you can see all the different functions
provided by PooledConn.
So obviously when you're learning a new API,
part of the task is to figure out which of these structures
and enums and macros and things are important
and which ones you can just chiefly ignore,
and, you know, what you don't wanna do is sit down
and just read this from top to bottom
because you'll read in stuff you'll never need.
The best approach to learn is to have a task
that you're trying to fulfill,
and in order to fulfill a task,
that will lead you to inquire about
the functions that you need,
and that's what I decided to do with this demo.
So with that in mind, we can observe
that PooledConn implements the Queryable trait.
Okay, so that's worth looking at back in the documentation.
In the documentation,
it shows methods available directly in PooledConn,
but also it shows methods that it implements
based on a particular trait.
So it implements these traits here.
It implements Debug, Deref.
Deref means you can automatically,
you can put a star in front of it.
Drop, okay, so the connection will be closed
when you exit the function, and Queryable.
So that's the trait that's interesting.
The Queryable trait provides functions
which allow us query the database via the connection.
Let's click on that, and what we see here
are a bunch of different functions
to do different types of query
and execute different types of command.
Okay, so just gonna give you a quick overview
of some of the important functions in this Queryable trait.
So there are a bunch of methods called query something
to execute simple queries,
simple SQL statements.
So for example, there are various different functions
called query, query_iter, query_first,
query_map and query_drop and other ones as well.
So let me just give you a quick run through
of what these do.
You can have a look in the documentation
if you want the full technical details
but just so you can understand how it works.
The idea is you'll have a PooledConnection object here,
and upon that PooledConnection,
you'll call one of these functions.
You'd say something like a PooledConnection.query, okay?
When you call the query function,
what it'll do is it'll kind of execute the SQL
on the database, give you back a results set,
and it will collect that results set into a vector of rows.
So what you'll get back in that case is a collection
of all the rows in the results set
already collected and bundled, okay,
so you can then loop through it in a normal loop.
All right, so that's what the pooled,
what the query function does.
It gives you back a vector.
I'm gonna draw this diagram in a slightly different way
because what's kind of important
is that the PooledConnection,
it will always come back with a results set of some type,
and then depending which of
these query functions you've called,
it'll kind of adapt that return.
So if you called a query function like this one here,
it will collect all those rows together
and give you back a vector, okay,
which is my preferred approach.
So that's what happens when you call the query function.
Now let's discuss what happens when you call query_iter,
query_iter, okay?
So when you call the query_iter, it's not quite the same.
It'll still execute the query, you know,
the select statement for example,
and it'll still come back with a results set,
but it doesn't bundle it into a vector.
What it gives you back instead is an iterator,
like a cursor which you can say
you can then loop through the elements one by one yourself.
It hasn't bundled it into a pre-aggregated vector.
It just gives back an iterator, hence the name query_iter,
which you can then use to go into the next row
and the next row and the next row, row by row like that.
All right, so that kinda makes sense, and again,
you can have a look at the documentation online for that,
and it'll explain what I've just done.
What about query_first?
Well, I think you can probably imagine what that one does.
When you call query_first,
it'll give you back the first row.
So I'll say query_first.
I'll call the query_first function.
I give it an SQL as a parameter.
It'll execute the query, pull back the results set,
but it'll just give me back the first row
from the results set, okay, just that.
Query_map is interesting. Let's discuss query_map.
That's quite a handy one actually.
When you say query_map,
what that does is it will pull back a results set.
When you call query_map,
one of the parameters you pass in is a lambda or closure,
and it'll invoke that closure upon each row
in the results set to transform it into something else,
and that can be quite useful.
So for example, if you'd executed a query,
select staff from employees,
each row in the results set would be an employee row,
you know, name, salary, region and ID.
When you call a query_map,
you could pass in a closure here, a lambda,
which would say for each row,
convert it into an employee object,
and what you'd get back would be an employee object
for the first row and an employee object for the second row
and an employee object for the third row,
all kind of collated together into a collection,
pre-converted.
So that's really quite handy.
I quite like that function, and then query_drop.
With query_drop, that's useful if you are not particularly,
you want to perform some kind of SQL,
but you're not particularly,
you don't particularly care about
the results you wanna get back, right?
So if I just sanitize my diagram again.
So you execute a query. It returns a results set.
Typically this is something like
some kind of insert or update.
I don't care about the result. I just wanna do it.
So there will be some kind of results set that comes back,
but it just drops that results set
immediately having executed the statement.
All right, so hopefully that gives you an idea
about the kind of functionality available.
Have a look at the documentation,
and it goes into more detail about
the technicalities of those functions,
and there are other functions as well.
I'm not attempting to be comprehensive here
because there's too much detail.
You'd never take it all in,
but as long as you kind of get a feel
for the kind of functionality that exists
and know where to look to get the full detail,
that's the main thing.
So let's have a look at an example.
What we're gonna do is we're gonna execute a SQL statement
to create a temporary table in the database.
Our database already has tables,
employee, employee two, skills, contracts, whatever.
I'm gonna execute a temporary,
execute a SQL statement to create a temporary table
which will exist while my application is running
but then gets dropped as soon as the application terminates.
Okay, so in the main code,
we're gonna look at the main function,
and obviously that's a given,
and then we're gonna look at another function
in my demo called create_temp_table.
Right, so I've reset my code example.
So in main, it's basically as it was
in the original distribution.
Basically all the code is uncommented,
and I could potentially be getting warnings
if I start commenting bits out.
So I'm gonna comment out.
I'm gonna keep the first two statements
to get the connection, and then to create a temporary table
using the connection, has to be mutable,
I'm gonna comment out the rest of it.
We're gonna look at that later on,
and then in order to avoid getting warnings about things,
I'm gonna add back in my crate level attribute
to allow dead code.
I actually don't need to allow unused variables now
because my connection is actually being used,
and it is being used in a mutable kind of way.
So I can actually get rid of these warnings now.
I don't need to suppress those warnings.
I just need to avoid warnings about functions
that haven't been invoked.
Okay, so that looks a bit nicer.
Okay, so I guess we need to look at the code
in this function to see how to execute a query.
So here we are then.
You just call the function connection.query_drop
and give it, oh, so here, I've given it a raw string.
Remember r? It means you can have a multi-line string.
That's very handy here, a giant chunk of SQL.
This, every database has its own particular
SQL variant or dialect.
This is the SQL that you'd need for MySQL database.
It creates a temporary table called employees_temp.
There's already an employees table in the database.
This is just a temporary one that'll disappear
when my application finishes, but it's got the same idea.
There'll be an employee_id, which is an integer,
which, by the way, is the PRIMARY KEY,
and then a string for the person's name
and their salary and their region, okay?
Like most functions, when you're doing database handling,
it returns a result enum,
which you could check to see if it's okay or an error.
Just for the purposes of a simple demo,
I'm just gonna unwrap the result
and hope that it's all okay.
So when I run the application,
it'll create that temporary table,
and then basically it'll stop, okay,
because I'm not actually doing anything with it,
but at least, well, I'm hoping, fingers crossed,
I'm not gonna get any errors.
So if I save the example and just run it,
I'm hoping for, please, dear example,
don't give me any errors.
Cargo run and fingers crossed.
Okay. Great.
So the application ran without any errors.
We're heading in the right direction.