Rechercher dans le manuel MySQL
28.7.11.11 mysql_stmt_fetch()
int mysql_stmt_fetch(MYSQL_STMT *stmt)
Description
mysql_stmt_fetch()
returns the
next row in the result set. It can be called only while the
result set exists; that is, after a call to
mysql_stmt_execute()
for a
statement such as SELECT
that
produces a result set.
mysql_stmt_fetch()
returns row
data using the buffers bound by
mysql_stmt_bind_result()
. It
returns the data in those buffers for all the columns in the
current row set and the lengths are returned to the
length
pointer. All columns must be bound
by the application before it calls
mysql_stmt_fetch()
.
mysql_stmt_fetch()
typically
occurs within a loop, to ensure that all result set rows are
fetched. For example:
int status;
while (1)
{
status = mysql_stmt_fetch(stmt);
if (status == 1 || status == MYSQL_NO_DATA)
break;
/* handle current row here */
}
/* if desired, handle status == 1 case and display error here */
By default, result sets are fetched unbuffered a row at a time
from the server. To buffer the entire result set on the
client, call
mysql_stmt_store_result()
after binding the data buffers and before calling
mysql_stmt_fetch()
.
If a fetched data value is a NULL
value,
the *is_null
value of the corresponding
MYSQL_BIND
structure contains TRUE (1).
Otherwise, the data and its length are returned in the
*buffer
and *length
elements based on the buffer type specified by the
application. Each numeric and temporal type has a fixed
length, as listed in the following table. The length of the
string types depends on the length of the actual data value,
as indicated by data_length
.
Type | Length |
---|---|
MYSQL_TYPE_TINY |
1 |
MYSQL_TYPE_SHORT |
2 |
MYSQL_TYPE_LONG |
4 |
MYSQL_TYPE_LONGLONG |
8 |
MYSQL_TYPE_FLOAT |
4 |
MYSQL_TYPE_DOUBLE |
8 |
MYSQL_TYPE_TIME |
sizeof(MYSQL_TIME) |
MYSQL_TYPE_DATE |
sizeof(MYSQL_TIME) |
MYSQL_TYPE_DATETIME |
sizeof(MYSQL_TIME) |
MYSQL_TYPE_STRING |
data length |
MYSQL_TYPE_BLOB |
data_length |
In some cases, you might want to determine the length of a
column value before fetching it with
mysql_stmt_fetch()
. For
example, the value might be a long string or
BLOB
value for which you want
to know how much space must be allocated. To accomplish this,
use one of these strategies:
Before invoking
mysql_stmt_fetch()
to retrieve individual rows, passSTMT_ATTR_UPDATE_MAX_LENGTH
tomysql_stmt_attr_set()
, then invokemysql_stmt_store_result()
to buffer the entire result on the client side. Setting theSTMT_ATTR_UPDATE_MAX_LENGTH
attribute causes the maximal length of column values to be indicated by themax_length
member of the result set metadata returned bymysql_stmt_result_metadata()
.Invoke
mysql_stmt_fetch()
with a zero-length buffer for the column in question and a pointer in which the real length can be stored. Then use the real length withmysql_stmt_fetch_column()
.real_length= 0; bind[0].buffer= 0; bind[0].buffer_length= 0; bind[0].length= &real_length mysql_stmt_bind_result(stmt, bind); mysql_stmt_fetch(stmt); if (real_length > 0) { data= malloc(real_length); bind[0].buffer= data; bind[0].buffer_length= real_length; mysql_stmt_fetch_column(stmt, bind, 0, 0); }
Return Value | Description |
---|---|
0 | Success, the data has been fetched to application data buffers. |
1 | Error occurred. Error code and message can be obtained by calling
mysql_stmt_errno() and
mysql_stmt_error() . |
MYSQL_NO_DATA |
Success, no more data exists |
MYSQL_DATA_TRUNCATED |
Data truncation occurred |
MYSQL_DATA_TRUNCATED
is returned when
truncation reporting is enabled. To determine which column
values were truncated when this value is returned, check the
error
members of the
MYSQL_BIND
structures used for fetching
values. Truncation reporting is enabled by default, but can be
controlled by calling
mysql_options()
with the
MYSQL_REPORT_DATA_TRUNCATION
option.
Commands were executed in an improper order.
Although
mysql_stmt_fetch()
can produce this error, it is more likely to occur for the following C API call ifmysql_stmt_fetch()
is not called enough times to read the entire result set (that is, enough times to returnMYSQL_NO_DATA
).Out of memory.
The MySQL server has gone away.
The connection to the server was lost during the query.
An unknown error occurred.
The buffer type is
MYSQL_TYPE_DATE
,MYSQL_TYPE_TIME
,MYSQL_TYPE_DATETIME
, orMYSQL_TYPE_TIMESTAMP
, but the data type is notDATE
,TIME
,DATETIME
, orTIMESTAMP
.All other unsupported conversion errors are returned from
mysql_stmt_bind_result()
.
The following example demonstrates how to fetch data from a
table using
mysql_stmt_result_metadata()
,
mysql_stmt_bind_result()
, and
mysql_stmt_fetch()
. (This
example expects to retrieve the two rows inserted by the
example shown in Section 28.7.11.10, “mysql_stmt_execute()”.) The
mysql
variable is assumed to be a valid
connection handler.
#define STRING_SIZE 50
#define SELECT_SAMPLE "SELECT col1, col2, col3, col4 \
FROM test_table"
MYSQL_STMT *stmt;
MYSQL_BIND bind[4];
MYSQL_RES *prepare_meta_result;
MYSQL_TIME ts;
unsigned long length[4];
int param_count, column_count, row_count;
short small_data;
int int_data;
char str_data[STRING_SIZE];
bool is_null[4];
bool error[4];
/* Prepare a SELECT query to fetch data from test_table */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
{
fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
fprintf(stdout, " prepare, SELECT successful\n");
/* Get the parameter count from the statement */
param_count= mysql_stmt_param_count(stmt);
fprintf(stdout, " total parameters in SELECT: %d\n", param_count);
if (param_count != 0) /* validate parameter count */
{
fprintf(stderr, " invalid parameter count returned by MySQL\n");
exit(0);
}
/* Fetch result set meta information */
prepare_meta_result = mysql_stmt_result_metadata(stmt);
if (!prepare_meta_result)
{
fprintf(stderr,
" mysql_stmt_result_metadata(), \
returned no meta information\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Get total columns in the query */
column_count= mysql_num_fields(prepare_meta_result);
fprintf(stdout,
" total columns in SELECT statement: %d\n",
column_count);
if (column_count != 4) /* validate column count */
{
fprintf(stderr, " invalid column count returned by MySQL\n");
exit(0);
}
/* Execute the SELECT query */
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, " mysql_stmt_execute(), failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Bind the result buffers for all 4 columns before fetching them */
memset(bind, 0, sizeof(bind));
/* INTEGER COLUMN */
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= &is_null[0];
bind[0].length= &length[0];
bind[0].error= &error[0];
/* STRING COLUMN */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)str_data;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= &is_null[1];
bind[1].length= &length[1];
bind[1].error= &error[1];
/* SMALLINT COLUMN */
bind[2].buffer_type= MYSQL_TYPE_SHORT;
bind[2].buffer= (char *)&small_data;
bind[2].is_null= &is_null[2];
bind[2].length= &length[2];
bind[2].error= &error[2];
/* TIMESTAMP COLUMN */
bind[3].buffer_type= MYSQL_TYPE_TIMESTAMP;
bind[3].buffer= (char *)&ts;
bind[3].is_null= &is_null[3];
bind[3].length= &length[3];
bind[3].error= &error[3];
/* Bind the result buffers */
if (mysql_stmt_bind_result(stmt, bind))
{
fprintf(stderr, " mysql_stmt_bind_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Now buffer all results to client (optional step) */
if (mysql_stmt_store_result(stmt))
{
fprintf(stderr, " mysql_stmt_store_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Fetch all rows */
row_count= 0;
fprintf(stdout, "Fetching results ...\n");
while (!mysql_stmt_fetch(stmt))
{
row_count++;
fprintf(stdout, " row %d\n", row_count);
/* column 1 */
fprintf(stdout, " column1 (integer) : ");
if (is_null[0])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %d(%ld)\n", int_data, length[0]);
/* column 2 */
fprintf(stdout, " column2 (string) : ");
if (is_null[1])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %s(%ld)\n", str_data, length[1]);
/* column 3 */
fprintf(stdout, " column3 (smallint) : ");
if (is_null[2])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %d(%ld)\n", small_data, length[2]);
/* column 4 */
fprintf(stdout, " column4 (timestamp): ");
if (is_null[3])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %04d-%02d-%02d %02d:%02d:%02d (%ld)\n",
ts.year, ts.month, ts.day,
ts.hour, ts.minute, ts.second,
length[3]);
fprintf(stdout, "\n");
}
/* Validate rows fetched */
fprintf(stdout, " total rows fetched: %d\n", row_count);
if (row_count != 2)
{
fprintf(stderr, " MySQL failed to return all rows\n");
exit(0);
}
/* Free the prepared result metadata */
mysql_free_result(prepare_meta_result);
/* Close the statement */
if (mysql_stmt_close(stmt))
{
/* mysql_stmt_close() invalidates stmt, so call */
/* mysql_error(mysql) rather than mysql_stmt_error(stmt) */
fprintf(stderr, " failed while closing the statement\n");
fprintf(stderr, " %s\n", mysql_error(mysql));
exit(0);
}
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-mysql-stmt-fetch.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.