Getting Data From Mariadb
From; https://mariadb.com/kb/en/getting-data-from-mariadb/
Getting Data from MariaDB
The simplest way to retrieve data from MariaDB is to use the SELECT
statement. Since the SELECT statement is an essential SQL statement, it has
many options available with it. It's not necessary to know or use them
all—you could execute very basic SELECT statements if that satisfies your
needs. However, as you use MariaDB more, you may need more powerful SELECT
statements. In this article we will go through the basics of SELECT and will
progress to more involved SELECT statements;we will move from the beginner
level to the more intermediate and hopefully you will find some benefit from
this article regardless of your skill level. For absolute beginners who are
just starting with MariaDB, you may want to read the MariaDB Basics article.
Basic Elements
The basic, minimal elements of the SELECT statement call for the keyword
SELECT, of course, the columns to select or to retrieve, and the table from
which to retrieve rows of data. Actually, for the columns to select, we can
use the asterisk as a wildcard to select all columns in a particular table.
Using a database from a fictitious bookstore, we might enter the following
SQL statement to get a list of all columns and rows in a table containing
information on books:
SELECT * FROM books;
This will retrieve all of the data contained in the books table. If we want
to retrieve only certain columns, we would list them in place of the
asterisk in a comma-separated list like so:
SELECT isbn, title, author_id FROM books;
This narrows the width of the results set by retrieving only three columns,
but it still retrieves all of the rows in the table. If the table contains
thousands of rows of data, this may be more data than we want. If we want to
limit the results to just a few books, say five, we would include what is
known as a LIMIT clause:
SELECT isbn, title, author_id FROM books LIMIT 5;
This will give us the first five rows found in the table. If we want to get
the next ten found, we would add a starting point parameter just before the
number of rows to display, separated by a comma:
SELECT isbn, title, author_id FROM books LIMIT 5, 10;
Selectivity and Order
The previous statements have narrowed the number of columns and rows
retrieved, but they haven't been very selective. Suppose that we want only
books written by a certain author, say Dostoevsky. Looking in the authors
table we find that his author identification number is 4729. Using a WHERE
clause, we can retrieve a list of books from the database for this
particular author like so:
SELECT isbn, title FROM books WHERE author_id = 4729 LIMIT 5;
I removed the author_id from the list of columns to select, but left the
basic LIMIT clause in because we want to point out that the syntax is fairly
strict on ordering of clauses and flags. You can't enter them in any order.
You'll get an error in return.
The SQL statements we've looked at thus far will display the titles of books
in the order in which they're found in the database. If we want to put the
results in alphanumeric order based on the values of the title column, for
instance, we would add an ORDER BY clause like this:
SELECT isbn, title FROM books WHERE author_id = 4729
ORDER BY title ASC LIMIT 5;
Notice that the ORDER BY clause goes after the WHERE clause and before the
LIMIT clause. Not only will this statement display the rows in order by book
title, but it will retrieve only the first five based on the ordering. That
is to say, MariaDB will first retrieve all of the rows based on the WHERE
clause, order the data based on the ORDER BY clause, and then display a
limited number of rows based on the LIMIT clause. Hence the reason for the
order of clauses. You may have noticed that we slipped in the ASC flag. It
tells MariaDB to order the rows in ascending order for the column name it
follows. It's not necessary, though, since ascending order is the default.
However, if we want to display data in descending order, we would replace
the flag with DESC. To order by more than one column, additional columns may
be given in the ORDER BY clause in a comma separated list, each with the ASC
or DESC flags if preferred.
Friendlier and More Complicated
So far we've been working with one table of data containing information on
books for a fictitious bookstore. A database will usually have more than one
table, of course. In this particular database, there's also one called
authors in which the name and other information on authors is contained. To
be able to select data from two tables in one SELECT statement, we will have
to tell MariaDB that we want to join the tables and will need to provide a
join point. This can be done with a JOIN clause as shown in the following
SQL statement, with the results following it:
SELECT isbn, title,
CONCAT( name_first, ' ', name_last) AS author
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title ASC
LIMIT 5;
+-------------+------------------------+-------------------+
| isbn | title | author |
+-------------+------------------------+-------------------+
| 0553212168 | Brothers Karamozov | Fyodor Dostoevsky |
| 0679420290 | Crime & Punishment | Fyodor Dostoevsky |
| 0553211757 | Crime & Punishment | Fyodor Dostoevsky |
| 0192834118 | Idiot | Fyodor Dostoevsky |
| 067973452X | Notes from Underground | Fyodor Dostoevsky |
+-------------+------------------------+-------------------+
5 rows in set (0.00 sec)
Our SELECT statement is getting hefty, but it's the same one to which we've
been adding. Don't let the clutter fluster you. Looking for the new
elements, let's focus on the JOIN clause first. There are a few possible
ways to construct a join. This method works if you're using a newer version
of MariaDB and if both tables contain a column of the same name and value.
Otherwise you'll have to redo the JOIN clause to look something like this:
...
JOIN authors ON author_id = row_id
...
This excerpt is based on the assumption that the key field in the authors
table is not called author_id, but row_id instead. There's much more that
can be said about joins, but that would make for a much longer article. If
you want to learn more on joins, look at MariaDB's documentation page on
JOIN syntax.
Looking again at the last full SQL statement above, you must have spotted
the CONCAT() function that we added to the on-going example statement. This
string function takes the values of the columns and strings given and pastes
them together, to give one neat field in the results. We also employed the
AS parameter to change the heading of the results set for the field to
author. This is much tider. Since we joined the books and the authors tables
together, we were able to search for books based on the author's last name
rather than having to look up the author ID first. This is a much friendlier
method, albeit more complicated. Incidentally, we can have MariaDB check
columns from both tables to narrow our search. We would just add column =
value pairs, separated by commas in the WHERE clause. Notice that the string
containing the author's name is wrapped in quotes—otherwise, the string
would be considered a column name and we'd get an error.
The name Dostoevsky is sometimes spelled Dostoevskii, as well as a few other
ways. If we're not sure how it's spelled in the authors table, we could use
the LIKE operator instead of the equal-sign, along with a wildcard. If we
think the author's name is probably spelled either of the two ways
mentioned, we could enter something like this:
SELECT isbn, title,
CONCAT(name_first, ' ', name_last) AS author
FROM books
JOIN authors USING (author_id)
WHERE name_last LIKE 'Dostoevsk%'
ORDER BY title ASC
LIMIT 5;
This will match any author last name starting with Dostoevsk. Notice that
the wildcard here is not an asterisk, but a percent-sign.
Some Flags
There are many flags or parameters that can be used in a SELECT statement.
To list and explain all of them with examples would make this a very lengthy
article. The reality is that most people never use some of them anyway. So,
let's take a look at a few that you may find useful as you get more involved
with MariaDB or if you work with large tables on very active servers.
The first flag that may be given, it goes immediately after the SELECT
keyword, is ALL. By default, all rows that meet the requirements of the
various clauses given are selected, so this isn't necessary. If instead we
would only want the first occurrence of a particular criteria to be
displayed, we could add the DISTINCT option. For instance, for authors like
Dostoevsky there will be several printings of a particular title. In the
results shown earlier you may have noticed that there were two copies of
Crime & Punishment listed, however they have different ISBN numbers and
different publishers. Suppose that for our search we only want one row displayed
for each title. We could do that like so:
SELECT DISTINCT isbn, title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title;
We've thinned out the ongoing SQL statement a bit for clarity. This statement
will result in only one row displayed for Crime & Punishment and it will be the
first one found.
If we're retrieving data from an extremely busy database, by default any other
SQL statements entered simultaneously which are changing or updating data will
be executed before a SELECT statement. SELECT statements are considered to be of
lower priority. However, if we would like a particular SELECT statement to be
given a higher priority, we can add the keyword HIGH_PRIORITY. Modifying the
previous SQL statement for this factor, we would enter it like this:
SELECT DISTINCT HIGH_PRIORITY isbn, title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title;
You may have noticed in the one example earlier in which the results are
shown, that there's a status line displayed that specifies the number of
rows in the results set. This is less than the number of rows that were
found in the database that met the statement's criteria. It's less because
we used a LIMIT clause. If we add the SQL_CALC_FOUND_ROWS flag just before
the column list, MariaDB will calculate the number of columns found even if
there is a LIMIT clause.
SELECT SQL_CALC_FOUND_ROWS isbn, title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
LIMIT 5;
To retrieve this information, though, we will have to use the FOUND_ROWS()
function like so:
SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 26 |
+--------------+
This value is temporary and will be lost if the connection is terminated. It
cannot be retrieved by any other client session. It relates only to the
current session and the value for the variable when it was last calculated.
Conclusion
There are several more parameters and possibilities for the SELECT statement
that we had to skip to keep this article a reasonable length. A popular one that
we left out is the GROUP BY clause for calculating aggregate data for columns
(e.g., an average). There are several flags for caching results and a clause for
exporting a results set to a text file. If you would like to learn more about
SELECT and all of the options available, look at the on-line documentation for
SELECT statements.
C-examples
Basically, you call mysql_store_result() or mysql_use_result() to access the
result set, the former loads all the rows into memory on the client side, the
latter accesses rows one at a time from the server. If you use
mysql_use_result(), you need to call mysql_fetch_row() to access each row until
the function returns NULL. Each successful call to mysql_fetch_row() will return
a MYSQL_ROW which you can use to access the individual field values.
Since the fields are not nul-terminated, you need to use mysql_fetch_lengths()
to get the lengths of each of the fields so that you can copy them somewhere
else via memcpy, etc.
Since the field values are not nul-terminated you will need to add your own NULL
character when you make the copy if you want to use it as a string. Be aware
that the field values may contain binary data, so if you do treat it as a
string, functions that expect a C string will stop processing data if it
encounters a nul-character in the data.
Here is an example from the documentation that should help you put all this
together:
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields( result );
while( (row = mysql_fetch_row( result ) ) )
{
unsigned long *lengths;
lengths = mysql_fetch_lengths( result );
for(i = 0; i < num_fields; i++ )
{
printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL");
}
printf("\n");
}
Example 2
C doesn't really have support for key value arrays like (I'm assuming your
thinking of) PHP. The closest thing you could get would be to get an array of
the column names, search it for the one you need, and use that index. For
instance:
mysql_query( _MySQLConnection, query );
MYSQL_RES *result;
unsigned int num_fields;
MYSQL_ROW row;
MYSQL_FIELD *field;
unsigned int name_field;
char *field_name = "name";
char *headers[num_fields];
result = mysql_store_result( _MySQLConnection );
num_fields = mysql_num_fields( result );
for( unsigned int i = 0; (field = mysql_fetch_field( result )); i++)
{
headers[i] = field->name;
if( strcmp( field_name, headers[i]) == 0)
{
name_field = i;
}
}
while( (row = mysql_fetch_row( result )))
{
//do something with row[name_field]
printf("Name: %s\n", row[name_field]);
}
mysql_free_result( result );
Example 3
To get the field type, you can access the field information after fetching a row
by using mysql_fetch_field() as suggested by Barmar in his comment.
The structure MYSQL_FIELD has a member named type which is an enum. The enum
enum_field_types can be found in field_types.h.
A complete, self-contained example program might look like this:
/*H*******************************************************
*
*********************************************************/
#include <mysql.h>
#include <stdio.h>
//=================== DEFINES ===========================
//=================== PROTOTYPES ===========================
void error( MYSQL *con, const char *msg, const char *error );
char *type_info( enum enum_field_types type );
//=================== VARIABLES ===========================
/*F*******************************************************
*
*********************************************************/
int
main( void )
{
MYSQL *con = mysql_init( NULL );
if( con == NULL )
{
error( con, "initialization failed", mysql_error( con ));
}
if( !mysql_real_connect( con, "localhost", "root", "admin123"
, "db", 3306, NULL, 0))
{
error( con, "couldn't connect", mysql_error( con ));
}
if( mysql_query( con, "select * from animals;" ))
{
error(con, "query failed", mysql_error( con ));
}
MYSQL_RES *result = mysql_use_result( con );
if( result == NULL )
{
printf("empty result\n");
}
if( mysql_fetch_row( result ))
{
MYSQL_FIELD *field;
while( (field = mysql_fetch_field( result )))
{
printf( "|%s ", field->name );
printf( "%s| ", type_info( field->type ));
}
printf( "\n" );
}
printf( "\n" );
mysql_close( con );
return( 0 );
}
/*F*******************************************************
*
*********************************************************/
void
error( MYSQL *con, const char *msg, const char *error)
{
fprintf( stderr, "%s: %s\n", msg, error);
if( con )
{
mysql_close( con );
}
exit( 1 );
}
/*F*******************************************************
*
*********************************************************/
char*
type_info( enum enum_field_types type )
{
switch( type )
{
case MYSQL_TYPE_VARCHAR:
return( "VARCHAR" );
case MYSQL_TYPE_NEWDECIMAL:
return( "NEWDECIMAL" );
case MYSQL_TYPE_LONG:
return( "LONG" );
case MYSQL_TYPE_VAR_STRING:
return( "VAR_STRING" );
//...several cases omitted see field_types.h
default:
return( "UNKNOWN" );
}
}
Example 4
When one uses Prepared Statements in MySQL C API to handle TEXT field result,
one has to specify the length of the string for an out binding:
MYSQL_BIND out_bind;
char str_data[STRING_SIZE];
my_bool is_null;
my_bool error;
....
/* STRING COLUMN */
out_bind.buffer_type = MYSQL_TYPE_STRING;
out_bind.buffer = str_data;
out_bind.buffer_length = STRING_SIZE;
out_bind.is_null= &is_null;
out_bind.length= &length;
out_bind.error= &error;
mysql_stmt_bind_result(statement, out_bind)
In the given example STRING_SIZE is the known constant, but how to be with TEXT
fields where data length can vary from small sizes to megabytes?
Is there standard approaches for this?
Access rows by column name
From: https://stackoverflow.com/questions/4910650/mysql-c-api-access-rows-by-column-name
C doesn't really have support for key value arrays like (I'm assuming your
thinking of) PHP. The closest thing you could get would be to get an array of
the column names, search it for the one you need, and use that index. For
instance:
MYSQL_RES *result;
unsigned int num_fields;
MYSQL_ROW row;
MYSQL_FIELD *field;
unsigned int name_field;
char *field_name = "name";
char *headers[num_fields];
mysql_query( _MySQLConnection, query );
result = mysql_store_result( _MySQLConnection );
num_fields = mysql_num_fields( result );
for( unsigned int i = 0; (field = mysql_fetch_field( result )); i++)
{
headers[i] = field->name;
if( strcmp( field_name, headers[i]) == 0)
{
name_field = i;
}
}
while( (row = mysql_fetch_row( result )))
{
//do something with row[name_field]
printf( "Name: %s\n", row[name_field]);
}
mysql_free_result( result );
How to work with row from mysql_fetch_row()?
From: https://stackoverflow.com/questions/11932487/mysql-c-api-how-to-work-with-row-from-mysql-fetch-row
int page_id;
string page_name;
enum COLUMNS {
PAGE_ID,
PAGE_NAME
};
if( mysql_query( conn, "SELECT page_id, page_name FROM pages"))
{
exit_mysql_error( conn );
}
MYSQL_RES *res = mysql_use_result( conn );
while( MYSQL_ROW row = mysql_fetch_row( res ))
{
page_id = *(int*)res[PAGE_ID];
page_name = res[PAGE_NAME];
// do some stuff where I need the page_id and page_name int/string
}
page_id = atoi(row[0]);
page_name = row[1]; // not really sure
while ((row = mysql_fetch_row (query_result)))
{
/* Each field in represented by the string row[i] */
/* If the field you want to extract is an int, so you need to convert it */
page_id = atoi(row[i]);
page_name = row[2];
last_filed = row[number_of_fields_in_your_table - 1];
}
while( (row = mysql_fetch_row( query_result )))
{
memset( buffer, '\0', sizeof( buffer ));
for( i = 0; i < num_fields - 1; i++)
{
strncat( buffer, row[i], strlen( row[i]) + 1);
strncat( buffer, ";", 2);
}
// After this your buffer will contain buffer = "page_id;page_name;...;"
// and you can retreive all fields with snprintf for example
snprintf( buffer, sizeof( buffer ), "%d;%s;...", page_id
, page_name,...);
}
Get Result Field Type
/*H*******************************************************
*
*********************************************************/
#include <mysql.h>
#include <stdio.h>
// =================== DEFINES ===========================
// =================== PROTOTYPES ========================
void error( MYSQL *con, const char *msg, const char *error );
char *type_info( enum enum_field_types type );
// =================== VARIABLES =========================
/*F*******************************************************
*
*********************************************************/
int
main( void )
{
MYSQL *con = mysql_init( NULL );
if( con == NULL )
{
error( con, "initialization failed", mysql_error(con));
}
if( !mysql_real_connect( con, "localhost", "root", "admin123", "db"
, 3306, NULL, 0))
{
error( con, "couldn't connect", mysql_error( con ));
}
if( mysql_query( con, "select * from animals;"))
{
error( con, "query failed", mysql_error( con ));
}
MYSQL_RES *result = mysql_use_result( con );
if( result == NULL )
{
printf( "empty result\n");
}
if( mysql_fetch_row( result ))
{
MYSQL_FIELD *field;
while( (field = mysql_fetch_field( result )))
{
printf( "|%s ", field->name );
printf( "%s| ", type_info( field->type ));
}
printf( "\n" );
}
printf( "\n" );
mysql_close( con );
return( 0 );
}
/*F*******************************************************
*
*********************************************************/
void
error( MYSQL *con, const char *msg, const char *error )
{
fprintf( stderr, "%s: %s\n", msg, error );
if( con )
{
mysql_close( con );
}
exit( 1 );
}
/*F*******************************************************
*
*********************************************************/
char*
type_info( enum enum_field_types type)
{
switch( type )
{
case MYSQL_TYPE_VARCHAR:
return( "VARCHAR" );
case MYSQL_TYPE_NEWDECIMAL:
return( "NEWDECIMAL" );
case MYSQL_TYPE_LONG:
return( "LONG" );
case MYSQL_TYPE_VAR_STRING:
return( "VAR_STRING" );
//...several cases omitted see field_types.h
default:
return "UNKNOWN";
}
}