Top: Library add-ons: pmysql
This module is a simple portable wrapper for MySQL client library. It features:
This module is not part of PTypes. You are free to use and/or modify it. Short description of the interface is given below. Download the module:
#include <pmysql.h>
USING_PTYPES
class pmysql {
pmysql();
pmysql(string host, string user, string password, string db, int flags = 0);
// connect to the MySQL server
void open();
void open(string host, string user, string password, string db, int flags = 0);
void close();
// run a query
void query(const char* q); // textual query
void query(string q); // query that may contain binary data
void queryf(const char* fmt, ...); // printf-style formatted textual query
large affected_rows(); // for UPDATE and DELETE queries
int field_count();
// row access
variant fetch_array(bool associative = false);
bool fetch_row();
// field value retrieval, use fetch_row() first
string field(int i);
string field(const char* name);
large int_field(int i);
large int_field(const char* name);
double float_field(int i);
double float_field(const char* name);
datetime datetime_field(int i);
datetime datetime_field(const char* name);
variant var_field(int i);
variant var_field(const char* name);
// field manipulation, use fetch_row() first
int field_length(int i);
int field_type(int i); // returns MySQL type FIELD_TYPE_XXX
const char* field_name(int i);
int find_field(const char* name); // returns the field index or -1 on error
// getting the result set for SELECT, SHOW, DESCRIBE, EXPLAIN queries
bool use_result(); // called automatically from fetch_row(), XXX_field()
bool store_result(); // called automatically from data_seek(), row_seek(), row_tell()
void* row_tell();
void* row_seek(void* offset);
void data_seek(large row_number);
void free_result(); // called automatically when necessary
// utilities
string escape(string);
}
class emysql {
emysql();
string get_message();
}
All data retrieval functions convert results to either built-in C types or library
data types, such like string or datetime.
Functions fetch_array() and var_field()
return data as variants; note that SQL date/time types are converted into an integer
variant holding a datetime value.
All member functions except the constructor and the destructor may throw exceptions of type (emysql*). You can access the error message using the emysql::get_message() method. Like all exceptions in the library, the emysql exception is allocated dynamically, so it must be freed in the catch() clause.
There is a short example at the bottom of this page. You need the standard MySQL client library libmysql to be linked to your application.
pmysql::pmysql() creates a pmysql object.
pmysql::pmysql(string host, string user, string password, string db, int flags = 0) creates a pmysql object and assigns host, user name, password, database name and optional flags for use in open().
pmysql::open() opens a connection with a MySQL server using previously assigned host, user name, password, database name and flags.
pmysql::query(const char*) sends a textual query to the server.
pmysql::query(string) sends a query to the server; may contain binary data.
pmysql::queryf(const char* fmt, ...) sends a textual or binary query to the server. The query string can be formatted using the same rules as for outstm::putf() in PTypes.
large pmysql::affected_rows() returns the number of rows affected by the last UPDATE or DELETE query.
variant pmysql::fetch_array(bool associative = false) returns the next row in the result set as either an indexed array or an associative array, where keys are column names. The array items are field values transformed into the corresponding PTypes data types, as described above. If there are no more rows left this function returns a NULL variant. (For indexed array manipulation use PTypes function aget(const variant& array, int index), currently undocumented.)
bool pmysql::fetch_row() retrieves the next row in the result set. The field values are accessible through XXX_field() functions described below. If there are no more rows left this function returns false.
string pmysql::field(<index>) returns a field of any type as a string. You can use this function to retrieve both text and binary fields. Index can be either a field number or a symbolic column name.
large pmysql::int_field(<index>) returns a field of any type as a 64-bit integer. If the field can not be converted to an integer this function returns 0. Index can be either a field number or a symbolic column name.
double pmysql::float_field(<index>) returns a field of any type as a floating point value. If the field can not be converted to double this function returns 0. Index can be either a field number or a symbolic column name.
datetime pmysql::datetime_field(<index>) returns a field of any type as a datetime value. Any MySQL date/time or timestamp field can be retrieved using this function. If the field can not be converted to datetime this function returns invdatetime. Index can be either a field number or a symbolic column name.
variant pmysql::var_field(<index>) returns a field as a variant of a corresponding type. Date/time values are returned as integer variants. Index can be either a field number or a symbolic column name.
int pmysql::field_count() returns the number of fields (columns) in the result set.
int pmysql::field_length(int index) returns the length of a field in the current row. Must be called after fetch_row().
int pmysql::field_type(int index) returns the type of a field. Must be called after fetch_row(). The returned value is a constant described in MySQL manual, section "C API Datatypes".
const char* pmysql::field_name(int index) returns the column name. Must be called after fetch_row().
int pmysql::find_field(const char* name) returns an index of a field by name, or -1 if a field with that name does not exist. Must be called after fetch_row().
bool pmysql::use_result() preperaes the result set for sequential retrieval. Must be called after query() and before any row retrieval function. Use_result() is called automatically from fetch_XXX() functions.
bool pmysql::store_result() prepares the result set for storing locally. This method allows to retrieve rows in random order. Must be called after query() and before any row retrieval function. Store_result() is called automatically from row_tell(), row_seek() and data_seek().
void* pmysql::row_tell() returns the current position of the row cursor for the last fetch_row(). This value can be used as an argument to row_seek().
void* pmysql::row_seek(void* offset) sets the row cursor to an arbitrary row in a query result set.
void pmysql::data_seek(large row_number) seeks to an arbitrary row in a query result set.
void pmysql::free_result() - this function is called automatically whenever necessary. You may want to call it explicitly to free memory resources associated with the result set.
string pmysql::escape(string str) encodes the string str to an escaped SQL string, taking into account the current character set of the connection. Characters encoded are NUL (ASCII 0), `\n', `\r', `\', `'', `"', and Control-Z.
Example
#include <ptypes.h>
#include <pmysql.h>
USING_PTYPES
int main()
{
pmysql mysql("localhost", "web", "", "mydb");
try
{
mysql.open();
mysql.query("SELECT id, name, registered FROM customers");
variant row;
while (!isnull(row = mysql.fetch_array(true)))
pout.putf("%lld: %s [%t]\n", large(row["id"]), pconst(string(row["name"])),
datetime(row["registered"]));
}
catch (emysql* e)
{
perr.putf("\nError: %s\n", pconst(e->get_message()));
delete e;
return 1;
}
return 0;
}