• notice
  • Congratulations on the launch of the Sought Tech site

Use c to execute SQL statements (2) in the mysql database (2)

A statement that returns data

Now is the time to discuss the most common use of SQL, the SELECT statement that retrieves data from a database.

MySQL also supports SHOW, DESCRIBE, and EXPLAIN SQL statements that return results, but they are not considered here.By convention, manuals contain descriptions of these statements.

As you'll remember from the PostgreSQL chapter, you can retrieve data from a SQL SELECT statement in PQexec, where all data is fetched at once, or use cursors to retrieve data row by row from the database for big data.

For exactly the same reasons, MySQL's retrieval method is almost identical, although it does not actually describe row-by-row retrieval in the form of a cursor.However, it provides APIs that narrow the differences between the two methods, and it generally makes it easier to interchange the two methods if needed.

Generally, there are 4 stages to retrieving data from a MySQL database:

Send a query

Retrieve data

Processing data

Perform any finishing needed

As before, we use mysql_query to issue queries.Data retrieval is done using mysql_store_result or mysql_use_result, depending on how you want to retrieve the data, followed by a sequence of calls to mysql_fetch_row to process the data.Finally, mysql_free_result must be called to allow MySQL to perform any required collation.

All immediate data retrieval functions

All data can be retrieved from a SELECT statement (or other statement that returns data), in a single call, using mysql_store_result:

MYSQL_RES *mysql_store_result(MYSQL *connection);

This function must be called after mysql_query has retrieved data to store that data in the result set.This function retrieves all data from the server and immediately stores it in the client.It returns a pointer to a structure we have never encountered before (result set structure).Returns NULL if the statement fails.

When using the PostgreSQL equivalent, you should know that returning NULL means that an error has occurred, and this is not the same as when no data was retrieved.Even if the return value is not NULL, it does not mean that there is currently data to process.

If NULL is not returned, you can call mysql_num_rows and retrieve the actual number of rows returned, which of course may be 0.

my_ulonglong mysql_num_rows(MYSQL_RES *result);

It takes the returned result structure from mysql_store_result and returns the number of rows, possibly 0, in that result set.If mysql_store_result succeeds, so does mysql_num_rows.

This combination of mysql_store_result and mysql_num_rows is an easy and straightforward way to retrieve data.Once mysql_store_result returns successfully, all query data is already stored on the client and we know it can be retrieved from the result structure without worrying about database or network errors, since all data is local to the program.The number of rows returned can also be found immediately, which can make coding easier.As before, it sends all results back to the client immediately.For large result sets, it can consume significant server, network, and client resources.For these reasons, when working with larger datasets, it is best to retrieve only the data you need.Soon, we will discuss how to use the mysql_use_result function to accomplish this.

Once the data has been retrieved, it can be retrieved using mysql_fetch_row, and the result set can be manipulated using mysql_data_seek, mysql_row_seek, mysql_row_tell.Before we start the retrieving data phase, let's discuss these functions first.

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

This function takes the result structure obtained from the stored result, and retrieves a single row from it, returning the data assigned to you in the row structure.Returns NULL when there is no more data or an error occurs.Later, we'll come back to the data in this row.

void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);

This function allows you to enter the result set and set the rows that will be returned by the next fetch operation.offset is the row number, which must be in the range from 0 to the number of rows in the result set minus 1.Passing 0 will cause the first row to be returned on the next call to mysql_fetch_row.

MYSQL_ROW_OFFEST mysql_row_tell(MYSQL_RES *result);

This function returns an offset value that represents the current position in the result set.It is not a row number, it cannot be used for mysql_data_seek.However, it can be used to:

MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset);

It moves the current position in the result set and returns to the previous position.

Sometimes this pair of functions is useful for jumping between known points in the result set.Be careful not to confuse the offset values ​​used by row tell and row seek with the row numbers used by data_seek.These are not commutative and the result will be what you want to see.

void mysql_free_result(MYSQL_RES *result);

This function must always be called when the result set is completed, to allow the MySQL library to tidy up the objects assigned to it.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

order atorvastatin 40mg sale & lt;a href="https://lipiws.top/"& gt;order atorvastatin 10mg sale& lt;/a& gt; atorvastatin buy online

Qyakfz

2024-03-08

Leave a Reply

+