From cd62fdf6d0794f6d71ffa6f38e1213dca27b3e9a Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 9 Aug 2001 23:40:37 -0500 Subject: DocTOC Chapter 8 Complete! --- Docs/manual.texi | 3284 ++++++++++++++++++++++++++++-------------------------- 1 file changed, 1703 insertions(+), 1581 deletions(-) (limited to 'Docs') diff --git a/Docs/manual.texi b/Docs/manual.texi index b07e9a889de..135600104da 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -115,10 +115,8 @@ distribution for that version. * MySQL Optimization:: * Reference:: @strong{MySQL} language reference * Table types:: @strong{MySQL} table types -* Extending MySQL:: -* ODBC:: @strong{MySQL} ODBC Support -* Common programs:: Using @strong{MySQL} with some common programs * Clients:: @strong{MySQL} client tools and APIs +* Extending MySQL:: * Problems:: Problems * Environment variables:: @strong{MySQL} environment variables * Users:: Some @strong{MySQL} users @@ -26965,7 +26963,6 @@ Things that are not yet supported: - @node Reference, Table types, MySQL Optimization, Top @chapter MySQL Language Reference @@ -34734,7 +34731,7 @@ parameters to @code{FULLTEXT} in @code{CREATE/ALTER TABLE}). -@node Table types, Extending MySQL, Reference, Top +@node Table types, Clients, Reference, Top @chapter MySQL Table Types @cindex table types, choosing @@ -37217,1037 +37214,662 @@ Finland -@node Extending MySQL, ODBC, Table types, Top -@chapter Extending MySQL +@node Clients, Extending MySQL, Table types, Top +@chapter MySQL APIs + +@cindex client tools +@cindex APIs +@cindex @code{mysqlclient} library +@cindex buffer sizes, client +@cindex library, @code{mysqlclient} @menu -* Adding functions:: -* Adding procedures:: -* MySQL internals:: +* PHP:: @strong{MySQL} PHP API +* Perl:: @strong{MySQL} Perl API +* ODBC:: +* C:: @strong{MySQL} C API +* Cplusplus:: @strong{MySQL} C++ APIs +* Java:: @strong{MySQL} Java connectivity (JDBC) +* Python:: @strong{MySQL} Python APIs +* Tcl:: @strong{MySQL} Tcl APIs +* Eiffel:: @strong{MySQL} Eiffel wrapper @end menu +This chapter describes the APIs available for @strong{MySQL}, where to get +them, and how to use them. The C API is the most extensively covered, as it +was developed by the @strong{MySQL} team, and is the basis for most of the +other APIs. -@node Adding functions, Adding procedures, Extending MySQL, Extending MySQL -@section Adding New Functions to MySQL -@cindex functions, new -@cindex adding, new functions -@cindex user-defined functions, adding -@cindex UDFs, defined -@cindex functions, user-defined +@node PHP, Perl, Clients, Clients +@section MySQL PHP API -There are two ways to add new functions to @strong{MySQL}: +@cindex PHP API -@itemize @bullet -@item You can add the function through the user-definable function (UDF) -interface. User-definable functions are added and removed dynamically using -the @code{CREATE FUNCTION} and @code{DROP FUNCTION} statements. -@xref{CREATE FUNCTION, , @code{CREATE FUNCTION}}. +PHP is a server-side, HTML-embedded scripting language that may be used to +create dynamic Web pages. It contains support for accessing several +databases, including @strong{MySQL}. PHP may be run as a separate program +or compiled as a module for use with the Apache Web server. -@item You can add the function as a native (built in) @strong{MySQL} function. -Native functions are compiled into the @code{mysqld} server and become -available on a permanent basis. -@end itemize +The distribution and documentation are available at the +@uref{http://www.php.net/, PHP web site}. -Each method has advantages and disadvantages: +@menu +* PHP problems:: Common problems with MySQL and PHP +@end menu + +@node PHP problems, , PHP, PHP +@subsection Common Problems with MySQL and PHP @itemize @bullet -@item -If you write a user-definable function, you must install the object file -in addition to the server itself. If you compile your function into the -server, you don't need to do that. -@item -You can add UDFs to a binary @strong{MySQL} distribution. Native functions -require you to modify a source distribution. -@item -If you upgrade your @strong{MySQL} distribution, you can continue to use your -previously installed UDFs. For native functions, you must repeat your -modifications each time you upgrade. +@item Error: "Maximum Execution Time Exceeded" +This is a PHP limit; Go into the @file{php3.ini} file and set the maximum +execution time up from 30 seconds to something higher, as needed. +It is also not a bad idea to double the ram allowed per script to 16MB instead of +8 MB. +@item Error: "Fatal error: Call to unsupported or undefined function mysql_connect() in .." +This means that your PHP version isn't compiled with @strong{MySQL} support. +You can either compile a dynamic @strong{MySQL} module and load it into PHP or +recompile PHP with built-in @strong{MySQL} support. This is described in +detail in the PHP manual. +@item Error: "undefined reference to `uncompress'" +This means that the client library is compiled with support for a compressed +client/server protocol. The fix is to add @code{-lz} last when linking +with @code{-lmysqlclient}. @end itemize -Whichever method you use to add new functions, they may be used just like -native functions such as @code{ABS()} or @code{SOUNDEX()}. -@menu -* CREATE FUNCTION:: -* Adding UDF:: Adding a new user-definable function -* Adding native function:: Adding a new native function -@end menu +@node Perl, ODBC, PHP, Clients +@section MySQL Perl API +@cindex APIs, Perl +@cindex Perl API -@node CREATE FUNCTION, Adding UDF, Adding functions, Adding functions -@subsection @code{CREATE FUNCTION/DROP FUNCTION} Syntax +This section documents the Perl @code{DBI} interface. The former interface +was called @code{mysqlperl}. @code{DBI}/@code{DBD} now is the +recommended Perl interface, so @code{mysqlperl} is obsolete and is not +documented here. -@findex CREATE FUNCTION -@findex DROP FUNCTION -@findex UDF functions -@findex User-defined functions -@findex Functions, user-defined +@menu +* DBI with DBD:: @code{DBI} with @code{DBD::mysql} +* Perl DBI Class:: The @code{DBI} interface +* DBI-info:: More @code{DBI}/@code{DBD} information +@end menu -@example -CREATE [AGGREGATE] FUNCTION function_name RETURNS @{STRING|REAL|INTEGER@} - SONAME shared_library_name -DROP FUNCTION function_name -@end example +@node DBI with DBD, Perl DBI Class, Perl, Perl +@subsection @code{DBI} with @code{DBD::mysql} -A user-definable function (UDF) is a way to extend @strong{MySQL} with a new -function that works like native (built in) @strong{MySQL} functions such as -@code{ABS()} and @code{CONCAT()}. +@cindex @code{DBI} interface -@code{AGGREGATE} is a new option for @strong{MySQL} Version 3.23. An -@code{AGGREGATE} function works exactly like a native @strong{MySQL} -@code{GROUP} function like @code{SUM} or @code{COUNT()}. +@code{DBI} is a generic interface for many databases. That means that +you can write a script that works with many different database engines +without change. You need a DataBase Driver (DBD) defined for each +database type. For @strong{MySQL}, this driver is called +@code{DBD::mysql}. -@code{CREATE FUNCTION} saves the function's name, type, and shared library -name in the @code{mysql.func} system table. You must have the -@strong{insert} and @strong{delete} privileges for the @code{mysql} database -to create and drop functions. +For more information on the Perl5 DBI, please visit the @code{DBI} Web +page and read the documentation: +@example +@uref{http://www.symbolstone.org/technology/perl/DBI/index.html} +@end example +For more information on Object Oriented Programming +(OOP) as defined in Perl5, see the Perl OOP page: +@example +@uref{http://language.perl.com/info/documentation.html} +@end example -All active functions are reloaded each time the server starts, unless -you start @code{mysqld} with the @code{--skip-grant-tables} option. In -this case, UDF initialization is skipped and UDFs are unavailable. -(An active function is one that has been loaded with @code{CREATE FUNCTION} -and not removed with @code{DROP FUNCTION}.) +Note that if you want to use transactions with Perl, you need to have +@code{Msql-Mysql-modules} version 1.2216 or newer. -For instructions on writing user-definable functions, see @ref{Adding -functions}. For the UDF mechanism to work, functions must be written in C or -C++, your operating system must support dynamic loading and you must have -compiled @code{mysqld} dynamically (not statically). +Installation instructions for @strong{MySQL} Perl support are given in +@ref{Perl support}. +@node Perl DBI Class, DBI-info, DBI with DBD, Perl +@subsection The @code{DBI} Interface -@node Adding UDF, Adding native function, CREATE FUNCTION, Adding functions -@subsection Adding a New User-definable Function +@cindex @code{DBI} Perl module -@cindex adding, user-definable functions -@cindex user-defined functions, adding -@cindex functions, user-definable, adding +@noindent +@strong{Portable DBI Methods} -@menu -* UDF calling sequences:: UDF calling sequences -* UDF arguments:: Argument processing -* UDF return values:: Return values and error handling -* UDF compiling:: Compiling and installing user-definable functions -@end menu +@multitable @columnfractions .3 .7 +@item @code{connect} @tab Establishes a connection to a database server. +@item @code{disconnect} @tab Disconnects from the database server. +@item @code{prepare} @tab Prepares a SQL statement for execution. +@item @code{execute} @tab Executes prepared statements. +@item @code{do} @tab Prepares and executes a SQL statement. +@item @code{quote} @tab Quotes string or @code{BLOB} values to be inserted. +@item @code{fetchrow_array} @tab Fetches the next row as an array of fields. +@item @code{fetchrow_arrayref} @tab Fetches next row as a reference array of fields. +@item @code{fetchrow_hashref} @tab Fetches next row as a reference to a hashtable. +@item @code{fetchall_arrayref} @tab Fetches all data as an array of arrays. +@item @code{finish} @tab Finishes a statement and lets the system free resources. +@item @code{rows} @tab Returns the number of rows affected. +@item @code{data_sources} @tab Returns an array of databases available on localhost. +@item @code{ChopBlanks} @tab Controls whether @code{fetchrow_*} methods trim spaces. +@item @code{NUM_OF_PARAMS} @tab The number of placeholders in the prepared statement. +@item @code{NULLABLE} @tab Which columns can be @code{NULL}. +@item @code{trace} @tab Perform tracing for debugging. +@end multitable +@noindent +@strong{MySQL-specific Methods} -For the UDF mechanism to work, functions must be written in C or C++ and your -operating system must support dynamic loading. The @strong{MySQL} source -distribution includes a file @file{sql/udf_example.cc} that defines 5 new -functions. Consult this file to see how UDF calling conventions work. +@multitable @columnfractions .3 .7 +@item @code{insertid} @tab The latest @code{AUTO_INCREMENT} value. +@item @code{is_blob} @tab Which columns are @code{BLOB} values. +@item @code{is_key} @tab Which columns are keys. +@item @code{is_num} @tab Which columns are numeric. +@item @code{is_pri_key} @tab Which columns are primary keys. +@item @code{is_not_null} @tab Which columns CANNOT be @code{NULL}. See @code{NULLABLE}. +@item @code{length} @tab Maximum possible column sizes. +@item @code{max_length} @tab Maximum column sizes actually present in result. +@item @code{NAME} @tab Column names. +@item @code{NUM_OF_FIELDS} @tab Number of fields returned. +@item @code{table} @tab Table names in returned set. +@item @code{type} @tab All column types. +@end multitable -For @code{mysqld} to be able to use UDF functions, you should configure MySQL -with @code{--with-mysqld-ldflags=-rdynamic} The reason is that to on -many platforms (including Linux) you can load a dynamic library (with -@code{dlopen()}) from a static linked program, which you would get if -you are using @code{--with-mysqld-ldflags=-all-static} If you want to -use an UDF that needs to access symbols from @code{mysqld} (like the -@code{methaphone} example in @file{sql/udf_example.cc} that uses -@code{default_charset_info}), you must link the program with -@code{-rdynamic}. (see @code{man dlopen}). +The Perl methods are described in more detail in the following sections. +Variables used for method return values have these meanings: -For each function that you want to use in SQL statements, you should define -corresponding C (or C++) functions. In the discussion below, the name -``xxx'' is used for an example function name. To distinquish between SQL and -C/C++ usage, @code{XXX()} (uppercase) indicates a SQL function call, and -@code{xxx()} (lowercase) indicates a C/C++ function call. +@table @code +@item $dbh +Database handle -The C/C++ functions that you write to implement the interface for -@code{XXX()} are: +@item $sth +Statement handle -@table @asis -@item @code{xxx()} (required) -The main function. This is where the function result is computed. -The correspondence between the SQL type and return type of your C/C++ -function is shown below: +@item $rc +Return code (often a status) -@multitable @columnfractions .2 .8 -@item @strong{SQL type} @tab @strong{C/C++ type} -@item @code{STRING} @tab @code{char *} -@item @code{INTEGER} @tab @code{long long} -@item @code{REAL} @tab @code{double} -@end multitable +@item $rv +Return value (often a row count) +@end table -@item @code{xxx_init()} (optional) -The initialization function for @code{xxx()}. It can be used to: +@noindent +@strong{Portable DBI Methods} -@itemize @bullet -@item -Check the number of arguments to @code{XXX()}. -@item -Check that the arguments are of a required type or, alternatively, -tell @strong{MySQL} to coerce arguments to the types you want when -the main function is called. -@item -Allocate any memory required by the main function. -@item -Specify the maximum length of the result. -@item -Specify (for @code{REAL} functions) the maximum number of decimals. -@item -Specify whether or not the result can be @code{NULL}. -@end itemize +@table @code -@item @code{xxx_deinit()} (optional) -The deinitialization function for @code{xxx()}. It should deallocate any -memory allocated by the initialization function. -@end table +@findex DBI->connect() +@findex connect() DBI method +@item connect($data_source, $username, $password) +Use the @code{connect} method to make a database connection to the data +source. The @code{$data_source} value should begin with +@code{DBI:driver_name:}. +Example uses of @code{connect} with the @code{DBD::mysql} driver: +@example +$dbh = DBI->connect("DBI:mysql:$database", $user, $password); +$dbh = DBI->connect("DBI:mysql:$database:$hostname", + $user, $password); +$dbh = DBI->connect("DBI:mysql:$database:$hostname:$port", + $user, $password); +@end example +If the user name and/or password are undefined, @code{DBI} uses the +values of the @code{DBI_USER} and @code{DBI_PASS} environment variables, +respectively. If you don't specify a hostname, it defaults to +@code{'localhost'}. If you don't specify a port number, it defaults to the +default @strong{MySQL} port (@value{default_port}). -When a SQL statement invokes @code{XXX()}, @strong{MySQL} calls the -initialization function @code{xxx_init()} to let it perform any required -setup, such as argument checking or memory allocation. If @code{xxx_init()} -returns an error, the SQL statement is aborted with an error message and the -main and deinitialization functions are not called. Otherwise, the main -function @code{xxx()} is called once for each row. After all rows have been -processed, the deinitialization function @code{xxx_deinit()} is called so it -can perform any required cleanup. +As of @code{Msql-Mysql-modules} Version 1.2009, +the @code{$data_source} value allows certain modifiers: -All functions must be thread safe (not just the main function, -but the initialization and deinitialization functions as well). This means -that you are not allowed to allocate any global or static variables that -change! If you need memory, you should allocate it in @code{xxx_init()} -and free it in @code{xxx_deinit()}. +@table @code +@item mysql_read_default_file=file_name +Read @file{filename} as an option file. For information on option files, +see @ref{Option files}. +@item mysql_read_default_group=group_name +The default group when reading an option file is normally the +@code{[client]} group. By specifying the @code{mysql_read_default_group} +option, the default group becomes the @code{[group_name]} group. -@node UDF calling sequences, UDF arguments, Adding UDF, Adding UDF -@subsubsection UDF Calling Sequences +@item mysql_compression=1 +Use compressed communication between the client and server (@strong{MySQL} +Version 3.22.3 or later). -@cindex calling sequences, UDF +@item mysql_socket=/path/to/socket +Specify the pathname of the Unix socket that is used to connect +to the server (@strong{MySQL} Version 3.21.15 or later). +@end table -The main function should be declared as shown below. Note that the return -type and parameters differ, depending on whether you will declare the SQL -function @code{XXX()} to return @code{STRING}, @code{INTEGER}, or @code{REAL} -in the @code{CREATE FUNCTION} statement: +Multiple modifiers may be given; each must be preceded by a semicolon. -@noindent -For @code{STRING} functions: +For example, if you want to avoid hardcoding the user name and password into +a @code{DBI} script, you can take them from the user's @file{~/.my.cnf} +option file instead by writing your @code{connect} call like this: @example -char *xxx(UDF_INIT *initid, UDF_ARGS *args, - char *result, unsigned long *length, - char *is_null, char *error); +$dbh = DBI->connect("DBI:mysql:$database" + . ";mysql_read_default_file=$ENV@{HOME@}/.my.cnf", + $user, $password); @end example -@noindent -For @code{INTEGER} functions: +This call will read options defined for the @code{[client]} group in the +option file. If you wanted to do the same thing but use options specified +for the @code{[perl]} group as well, you could use this: @example -long long xxx(UDF_INIT *initid, UDF_ARGS *args, - char *is_null, char *error); +$dbh = DBI->connect("DBI:mysql:$database" + . ";mysql_read_default_file=$ENV@{HOME@}/.my.cnf" + . ";mysql_read_default_group=perl", + $user, $password); @end example -@noindent -For @code{REAL} functions: - +@findex DBI->disconnect +@findex disconnect DBI method +@item disconnect +The @code{disconnect} method disconnects the database handle from the database. +This is typically called right before you exit from the program. +Example: @example -double xxx(UDF_INIT *initid, UDF_ARGS *args, - char *is_null, char *error); +$rc = $dbh->disconnect; @end example -The initialization and deinitialization functions are declared like this: - +@findex DBI->prepare() +@findex prepare() DBI method +@item prepare($statement) +Prepares a SQL statement for execution by the database engine +and returns a statement handle @code{($sth)}, which you can use to invoke +the @code{execute} method. +Typically you handle @code{SELECT} statements (and @code{SELECT}-like statements +such as @code{SHOW}, @code{DESCRIBE}, and @code{EXPLAIN}) by means of +@code{prepare} and @code{execute}. +Example: @example -my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message); - -void xxx_deinit(UDF_INIT *initid); +$sth = $dbh->prepare($statement) + or die "Can't prepare $statement: $dbh->errstr\n"; @end example -The @code{initid} parameter is passed to all three functions. It points to a -@code{UDF_INIT} structure that is used to communicate information between -functions. The @code{UDF_INIT} structure members are listed below. The -initialization function should fill in any members that it wishes to change. -(To use the default for a member, leave it unchanged.): - -@table @code -@item my_bool maybe_null -@code{xxx_init()} should set @code{maybe_null} to @code{1} if @code{xxx()} -can return @code{NULL}. The default value is @code{1} if any of the -arguments are declared @code{maybe_null}. - -@item unsigned int decimals -Number of decimals. The default value is the maximum number of decimals in -the arguments passed to the main function. (For example, if the function is -passed @code{1.34}, @code{1.345}, and @code{1.3}, the default would be 3, -because @code{1.345} has 3 decimals. - -@item unsigned int max_length -The maximum length of the string result. The default value differs depending -on the result type of the function. For string functions, the default is the -length of the longest argument. For integer functions, the default is 21 -digits. For real functions, the default is 13 plus the number of decimals -indicated by @code{initid->decimals}. (For numeric functions, the length -includes any sign or decimal point characters.) - -@item char *ptr -A pointer that the function can use for its own purposes. For example, -functions can use @code{initid->ptr} to communicate allocated memory -between functions. In @code{xxx_init()}, allocate the memory and assign it -to this pointer: - +@findex DBI->execute +@findex execute DBI method +@item execute +The @code{execute} method executes a prepared statement. For +non-@code{SELECT} statements, @code{execute} returns the number of rows +affected. If no rows are affected, @code{execute} returns @code{"0E0"}, +which Perl treats as zero but regards as true. If an error occurs, +@code{execute} returns @code{undef}. For @code{SELECT} statements, +@code{execute} only starts the SQL query in the database; you need to use one +of the @code{fetch_*} methods described below to retrieve the data. +Example: @example -initid->ptr = allocated_memory; +$rv = $sth->execute + or die "can't execute the query: $sth->errstr; @end example -In @code{xxx()} and @code{xxx_deinit()}, refer to @code{initid->ptr} to use -or deallocate the memory. -@end table - - -@node UDF arguments, UDF return values, UDF calling sequences, Adding UDF -@subsubsection Argument Processing - -@cindex argument processing -@cindex processing, arguments +@findex DBI->do() +@findex do() DBI method +@item do($statement) +The @code{do} method prepares and executes a SQL statement and returns the +number of rows affected. If no rows are affected, @code{do} returns +@code{"0E0"}, which Perl treats as zero but regards as true. This method is +generally used for non-@code{SELECT} statements that cannot be prepared in +advance (due to driver limitations) or that do not need to be executed more +than once (inserts, deletes, etc.). Example: +@example +$rv = $dbh->do($statement) + or die "Can't execute $statement: $dbh- >errstr\n"; +@end example -The @code{args} parameter points to a @code{UDF_ARGS} structure that thas the -members listed below: +Generally the 'do' statement is MUCH faster (and is preferable) +than prepare/execute for statements that don't contain parameters. -@table @code -@item unsigned int arg_count -The number of arguments. Check this value in the initialization function -if you want your function to be called with a particular number of arguments. -For example: +@findex DBI->quote() +@findex quote() DBI method +@cindex quoting strings +@cindex strings, quoting +@item quote($string) +The @code{quote} method is used to "escape" any special characters contained in +the string and to add the required outer quotation marks. +Example: +@example +$sql = $dbh->quote($string) +@end example +@findex DBI->fetchrow_array +@findex fetchrow_array DBI method +@item fetchrow_array +This method fetches the next row of data and returns it as an array of +field values. Example: @example -if (args->arg_count != 2) -@{ - strcpy(message,"XXX() requires two arguments"); - return 1; +while(@@row = $sth->fetchrow_array) @{ + print qw($row[0]\t$row[1]\t$row[2]\n); @} @end example - -@item enum Item_result *arg_type -The types for each argument. The possible type values are -@code{STRING_RESULT}, @code{INT_RESULT}, and @code{REAL_RESULT}. - -To make sure that arguments are of a given type and return an -error if they are not, check the @code{arg_type} array in the initialization -function. For example: - +@findex DBI->fetchrow_arrayref +@findex fetchrow_arrayref DBI method +@item fetchrow_arrayref +This method fetches the next row of data and returns it as a reference +to an array of field values. Example: @example -if (args->arg_type[0] != STRING_RESULT || - args->arg_type[1] != INT_RESULT) -@{ - strcpy(message,"XXX() requires a string and an integer"); - return 1; +while($row_ref = $sth->fetchrow_arrayref) @{ + print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n); @} @end example -As an alternative to requiring your function's arguments to be of particular -types, you can use the initialization function to set the @code{arg_type} -elements to the types you want. This causes @strong{MySQL} to coerce -arguments to those types for each call to @code{xxx()}. For example, to -specify coercion of the first two arguments to string and integer, do this in -@code{xxx_init()}: - -@example -args->arg_type[0] = STRING_RESULT; -args->arg_type[1] = INT_RESULT; +@findex DBI->fetchrow_hashref +@findex fetchrow_hashref DBI method +@item fetchrow_hashref +This method fetches a row of data and returns a reference to a hash +table containing field name/value pairs. This method is not nearly as +efficient as using array references as demonstrated above. Example: +@example +while($hash_ref = $sth->fetchrow_hashref) @{ + print qw($hash_ref->@{firstname@}\t$hash_ref->@{lastname@}\t\ + $hash_ref- > title@}\n); +@} @end example -@item char **args -@code{args->args} communicates information to the initialization function -about the general nature of the arguments your function was called with. For a -constant argument @code{i}, @code{args->args[i]} points to the argument -value. (See below for instructions on how to access the value properly.) -For a non-constant argument, @code{args->args[i]} is @code{0}. -A constant argument is an expression that uses only constants, such as -@code{3} or @code{4*7-2} or @code{SIN(3.14)}. A non-constant argument is an -expression that refers to values that may change from row to row, such as -column names or functions that are called with non-constant arguments. - -For each invocation of the main function, @code{args->args} contains the -actual arguments that are passed for the row currently being processed. - -Functions can refer to an argument @code{i} as follows: +@findex DBI->fetchall_arrayref +@findex fetchall_arrayref DBI method +@item fetchall_arrayref +This method is used to get all the data (rows) to be returned from the +SQL statement. It returns a reference to an array of references to arrays +for each row. You access or print the data by using a nested +loop. Example: +@example +my $table = $sth->fetchall_arrayref + or die "$sth->errstr\n"; +my($i, $j); +for $i ( 0 .. $#@{$table@} ) @{ + for $j ( 0 .. $#@{$table->[$i]@} ) @{ + print "$table->[$i][$j]\t"; + @} + print "\n"; +@} +@end example -@itemize @bullet -@item -An argument of type @code{STRING_RESULT} is given as a string pointer plus a -length, to allow handling of binary data or data of arbitrary length. The -string contents are available as @code{args->args[i]} and the string length -is @code{args->lengths[i]}. You should not assume that strings are -null-terminated. +@findex DBI->finish +@findex finish DBI method +@item finish +Indicates that no more data will be fetched from this statement +handle. You call this method to free up the statement handle and any +system resources associated with it. Example: +@example +$rc = $sth->finish; +@end example -@item -For an argument of type @code{INT_RESULT}, you must cast -@code{args->args[i]} to a @code{long long} value: +@findex DBI->rows +@findex rows DBI method +@item rows +Returns the number of rows changed (updated, deleted, etc.) by the last +command. This is usually used after a non-@code{SELECT} @code{execute} +statement. Example: +@example +$rv = $sth->rows; +@end example +@findex DBI->@{NULLABLE@} +@findex NULLABLE DBI method +@item NULLABLE +Returns a reference to an array of boolean values; for each element of +the array, a value of TRUE indicates that this +column may contain @code{NULL} values. +Example: @example -long long int_val; -int_val = *((long long*) args->args[i]); +$null_possible = $sth->@{NULLABLE@}; @end example -@item -For an argument of type @code{REAL_RESULT}, you must cast -@code{args->args[i]} to a @code{double} value: +@findex DBI->@{NUM_OF_FIELDS@} +@findex NUM_OF_FIELDS DBI method +@item NUM_OF_FIELDS +This attribute indicates +the number of fields returned by a @code{SELECT} or @code{SHOW FIELDS} +statement. You may use this for checking whether a statement returned a +result: A zero value indicates a non-@code{SELECT} statement like +@code{INSERT}, @code{DELETE}, or @code{UPDATE}. +Example: +@example +$nr_of_fields = $sth->@{NUM_OF_FIELDS@}; +@end example +@findex DBI->data_sources() +@findex data_sources() DBI method +@item data_sources($driver_name) +This method returns an array containing names of databases available to the +@strong{MySQL} server on the host @code{'localhost'}. +Example: @example -double real_val; -real_val = *((double*) args->args[i]); +@@dbs = DBI->data_sources("mysql"); @end example -@end itemize -@item unsigned long *lengths -For the initialization function, the @code{lengths} array indicates the -maximum string length for each argument. For each invocation of the main -function, @code{lengths} contains the actual lengths of any string arguments -that are passed for the row currently being processed. For arguments of -types @code{INT_RESULT} or @code{REAL_RESULT}, @code{lengths} still contains -the maximum length of the argument (as for the initialization function). -@end table +@findex DBI->@{ChopBlanks@} +@findex ChopBlanks DBI method +@item ChopBlanks +This attribute determines whether the @code{fetchrow_*} methods will chop +leading and trailing blanks from the returned values. +Example: +@example +$sth->@{'ChopBlanks'@} =1; +@end example +@findex DBI->trace +@findex trace DBI method +@item trace($trace_level) +@itemx trace($trace_level, $trace_filename) +The @code{trace} method enables or disables tracing. When invoked as a +@code{DBI} class method, it affects tracing for all handles. When invoked as +a database or statement handle method, it affects tracing for the given +handle (and any future children of the handle). Setting @code{$trace_level} +to 2 provides detailed trace information. Setting @code{$trace_level} to 0 +disables tracing. Trace output goes to the standard error output by +default. If @code{$trace_filename} is specified, the file is opened in +append mode and output for @emph{all} traced handles is written to that +file. Example: +@example +DBI->trace(2); # trace everything +DBI->trace(2,"/tmp/dbi.out"); # trace everything to + # /tmp/dbi.out +$dth->trace(2); # trace this database handle +$sth->trace(2); # trace this statement handle +@end example -@node UDF return values, UDF compiling, UDF arguments, Adding UDF -@subsubsection Return Values and Error Handling +@tindex DBI_TRACE environment variable +@tindex Environment variable, DBI_TRACE +You can also enable @code{DBI} tracing by setting the @code{DBI_TRACE} +environment variable. Setting it to a numeric value is equivalent to calling +@code{DBI->(value)}. Setting it to a pathname is equivalent to calling +@code{DBI->(2,value)}. -@cindex UDFs, return values -@cindex return values, UDFs -@cindex errors, handling for UDFs -@cindex handling, errors +@end table -The initialization function should return @code{0} if no error occurred and -@code{1} otherwise. If an error occurs, @code{xxx_init()} should store a -null-terminated error message in the @code{message} parameter. The message -will be returned to the client. The message buffer is -@code{MYSQL_ERRMSG_SIZE} characters long, but you should try to keep the -message to less than 80 characters so that it fits the width of a standard -terminal screen. +@noindent +@strong{MySQL-specific Methods} -The return value of the main function @code{xxx()} is the function value, for -@code{long long} and @code{double} functions. A string functions should -return a pointer to the result and store the length of the string in the -@code{length} arguments. @code{result} is a buffer at least 255 bytes long. -Set these to the contents and length of the return value. For example: +The methods shown below are @strong{MySQL}-specific and not part of the +@code{DBI} standard. Several of them are now deprecated: +@code{is_blob}, @code{is_key}, @code{is_num}, @code{is_pri_key}, +@code{is_not_null}, @code{length}, @code{max_length}, and @code{table}. +Where @code{DBI}-standard alternatives exist, they are noted below: +@table @code +@findex DBI->@{insertid@} +@findex insertid DBI method +@tindex AUTO_INCREMENT, using with DBI +@item insertid +If you use the @code{AUTO_INCREMENT} feature of @strong{MySQL}, the new +auto-incremented values will be stored here. +Example: @example -memcpy(result, "result string", 13); -*length = 13; +$new_id = $sth->@{insertid@}; @end example -If your string functions that needs to return a string longer than 255 -bytes, you must allocate the space for it with @code{malloc()} in your -@code{xxx_init()} function or your @code{xxx()} function and free it in -your @code{xxx_deinit()} function. You can store the allocated memory -in the @code{ptr} slot in the @code{UDF_INIT} structure for reuse by -future @code{xxx()} calls. @xref{UDF calling sequences}. - -To indicate a return value of @code{NULL} in the main function, set -@code{is_null} to @code{1}: +As an alternative, you can use @code{$dbh->@{'mysql_insertid'@}}. +@findex DBI->@{is_blob@} +@findex is_blob DBI method +@item is_blob +Returns a reference to an array of boolean values; for each element of the +array, a value of TRUE indicates that the +respective column is a @code{BLOB}. +Example: @example -*is_null = 1; +$keys = $sth->@{is_blob@}; @end example -To indicate an error return in the main function, set the @code{error} -parameter to @code{1}: - +@findex DBI->@{is_key@} +@findex is_key DBI method +@item is_key +Returns a reference to an array of boolean values; for each element of the +array, a value of TRUE indicates that the +respective column is a key. +Example: @example -*error = 1; +$keys = $sth->@{is_key@}; @end example -If @code{xxx()} sets @code{*error} to @code{1} for any row, the function -value is @code{NULL} for the current row and for any subsequent rows -processed by the statement in which @code{XXX()} was invoked. (@code{xxx()} -will not even be called for subsequent rows.) @strong{NOTE:} In -@strong{MySQL} versions prior to 3.22.10, you should set both @code{*error} -and @code{*is_null}: - +@findex DBI->@{is_num@} +@findex is_num DBI method +@item is_num +Returns a reference to an array of boolean values; for each element of the +array, a value of TRUE indicates that the +respective column contains numeric values. +Example: @example -*error = 1; -*is_null = 1; +$nums = $sth->@{is_num@}; @end example - -@node UDF compiling, , UDF return values, Adding UDF -@subsubsection Compiling and Installing User-definable Functions - -@cindex compiling, user-defined functions -@cindex UDFs, compiling -@cindex installing, user-defined functions - -Files implementing UDFs must be compiled and installed on the host where the -server runs. This process is described below for the example UDF file -@file{udf_example.cc} that is included in the @strong{MySQL} source -distribution. This file contains the following functions: - -@itemize @bullet -@item -@code{metaphon()} returns a metaphon string of the string argument. -This is something like a soundex string, but it's more tuned for English. -@item -@code{myfunc_double()} returns the sum of the ASCII values of the -characters in its arguments, divided by the sum of the length of its arguments. -@item -@code{myfunc_int()} returns the sum of the length of its arguments. -@item -@code{sequence([const int])} returns an sequence starting from the given -number or 1 if no number has been given. -@item -@code{lookup()} returns the IP number for a hostname. -@item -@code{reverse_lookup()} returns the hostname for an IP number. -The function may be called with a string @code{"xxx.xxx.xxx.xxx"} or -four numbers. -@end itemize - -A dynamically loadable file should be compiled as a sharable object file, -using a command something like this: - +@findex DBI->@{is_pri_key@} +@findex is_pri_key DBI method +@item is_pri_key +Returns a reference to an array of boolean values; for each element of the +array, a value of TRUE indicates that the respective column is a primary key. +Example: @example -shell> gcc -shared -o udf_example.so myfunc.cc +$pri_keys = $sth->@{is_pri_key@}; @end example -You can easily find out the correct compiler options for your system by -running this command in the @file{sql} directory of your @strong{MySQL} -source tree: - +@findex DBI->@{is_not_null@} +@findex is_not_null DBI method +@item is_not_null +Returns a reference to an array of boolean values; for each element of the +array, a value of FALSE indicates that this column may contain @code{NULL} +values. +Example: @example -shell> make udf_example.o +$not_nulls = $sth->@{is_not_null@}; @end example -You should run a compile command similar to the one that @code{make} displays, -except that you should remove the @code{-c} option near the end of the line -and add @code{-o udf_example.so} to the end of the line. (On some systems, -you may need to leave the @code{-c} on the command.) - -Once you compile a shared object containing UDFs, you must install it -and tell @strong{MySQL} about it. Compiling a shared object from -@file{udf_example.cc} produces a file named something like -@file{udf_example.so} (the exact name may vary from platform to platform). -Copy this file to some directory searched by @code{ld}, such as -@file{/usr/lib}. On many systems, you can set the @code{LD_LIBRARY} or -@code{LD_LIBRARY_PATH} environment variable to point at the directory where -you have your UDF function files. The @code{dlopen} manual page tells you -which variable you should use on your system. You should set this in -@code{mysql.server} or @code{safe_mysqld} and restart @code{mysqld}. +@code{is_not_null} is deprecated; it is preferable to use the +@code{NULLABLE} attribute (described above), because that is a DBI standard. -After the library is installed, notify @code{mysqld} about the new -functions with these commands: +@findex DBI->@{length@} +@findex length DBI method +@findex DBI->@{max_length@} +@findex max_length DBI method +@item length +@itemx max_length +Each of these methods returns a reference to an array of column sizes. The +@code{length} array indicates the maximum possible sizes that each column may +be (as declared in the table description). The @code{max_length} array +indicates the maximum sizes actually present in the result table. Example: @example -mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so"; -mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so"; -mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so"; -mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so"; -mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so"; +$lengths = $sth->@{length@}; +$max_lengths = $sth->@{max_length@}; @end example -Functions can be deleted using @code{DROP FUNCTION}: - +@findex DBI->@{NAME@} +@findex NAME DBI method +@item NAME +Returns a reference to an array of column names. +Example: @example -mysql> DROP FUNCTION metaphon; -mysql> DROP FUNCTION myfunc_double; -mysql> DROP FUNCTION myfunc_int; -mysql> DROP FUNCTION lookup; -mysql> DROP FUNCTION reverse_lookup; +$names = $sth->@{NAME@}; @end example -The @code{CREATE FUNCTION} and @code{DROP FUNCTION} statements update the -system table @code{func} in the @code{mysql} database. The function's name, -type and shared library name are saved in the table. You must have the -@strong{insert} and @strong{delete} privileges for the @code{mysql} database -to create and drop functions. - -You should not use @code{CREATE FUNCTION} to add a function that has already -been created. If you need to reinstall a function, you should remove it with -@code{DROP FUNCTION} and then reinstall it with @code{CREATE FUNCTION}. You -would need to do this, for example, if you recompile a new version of your -function, so that @code{mysqld} gets the new version. Otherwise the server -will continue to use the old version. - -Active functions are reloaded each time the server starts, unless you start -@code{mysqld} with the @code{--skip-grant-tables} option. In this case, UDF -initialization is skipped and UDFs are unavailable. (An active function is -one that has been loaded with @code{CREATE FUNCTION} and not removed with -@code{DROP FUNCTION}.) +@findex DBI->@{table@} +@findex table DBI method +@item table +Returns a reference to an array of table names. +Example: +@example +$tables = $sth->@{table@}; +@end example +@findex DBI->@{type@} +@findex type DBI method +@item type +Returns a reference to an array of column types. +Example: +@example +$types = $sth->@{type@}; +@end example -@node Adding native function, , Adding UDF, Adding functions -@subsection Adding a New Native Function +@end table -@cindex adding, native functions -@cindex native functions, adding -@cindex functions, native, adding -The procedure for adding a new native function is described below. Note -that you cannot add native functions to a binary distribution because -the procedure involves modifying @strong{MySQL} source code. You must -compile @strong{MySQL} yourself from a source distribution. Also note -that if you migrate to another version of @strong{MySQL} (for example, -when a new version is released), you will need to repeat the procedure -with the new version. +@node DBI-info, , Perl DBI Class, Perl +@subsection More @code{DBI}/@code{DBD} Information -To add a new native @strong{MySQL} function, follow these steps: +@cindex @code{DBI/DBD} -@enumerate -@item -Add one line to @file{lex.h} that defines the function name in the -@code{sql_functions[]} array. -@item -If the function prototype is simple (just takes zero, one, two or three -arguments), you should in lex.h specify SYM(FUNC_ARG#) (where # is the -number of arguments) as the second argument in the -@code{sql_functions[]} array and add a function that creates a function -object in @file{item_create.cc}. Take a look at @code{"ABS"} and -@code{create_funcs_abs()} for an example of this. +You can use the @code{perldoc} command to get more information about +@code{DBI}. -If the function prototype is complicated (for example takes a variable number -of arguments), you should add two lines to @file{sql_yacc.yy}. One -indicates the preprocessor symbol that @code{yacc} should define (this -should be added at the beginning of the file). Then define the function -parameters and add an ``item'' with these parameters to the -@code{simple_expr} parsing rule. For an example, check all occurrences -of @code{ATAN} in @file{sql_yacc.yy} to see how this is done. -@item -In @file{item_func.h}, declare a class inheriting from @code{Item_num_func} or -@code{Item_str_func}, depending on whether your function returns a number or a -string. -@item -In @file{item_func.cc}, add one of the following declarations, depending -on whether you are defining a numeric or string function: @example -double Item_func_newname::val() -longlong Item_func_newname::val_int() -String *Item_func_newname::Str(String *str) +perldoc DBI +perldoc DBI::FAQ +perldoc DBD::mysql @end example -If you inherit your object from any of the standard items (like -@code{Item_num_func} you probably only have to define one of the above -functions and let the parent object take care of the other functions. -For example, the @code{Item_str_func} class defines a @code{val()} function -that executes @code{atof()} on the value returned by @code{::str()}. +You can also use the @code{pod2man}, @code{pod2html}, etc., tools to +translate to other formats. -@item -You should probably also define the following object function: +You can find the latest @code{DBI} information at +the @code{DBI} Web page: @example -void Item_func_newname::fix_length_and_dec() +@uref{http://www.symbolstone.org/technology/perl/DBI/index.html} @end example -This function should at least calculate @code{max_length} based on the -given arguments. @code{max_length} is the maximum number of characters -the function may return. This function should also set @code{maybe_null -= 0} if the main function can't return a @code{NULL} value. The -function can check if any of the function arguments can return -@code{NULL} by checking the arguments @code{maybe_null} variable. You -can take a look at @code{Item_func_mod::fix_length_and_dec} for a -typical example of how to do this. -@end enumerate -All functions must be thread safe (In other words, don't use any global or -static variables in the functions without protecting them with mutexes). -If you want to return @code{NULL}, from @code{::val()}, @code{::val_int()} -or @code{::str()} you should set @code{null_value} to 1 and return 0. +@node ODBC, C, Perl, Clients +@section MySQL ODBC Support -For @code{::str()} object functions, there are some additional -considerations to be aware of: +@cindex ODBC +@cindex Windows +@cindex MyODBC -@itemize @bullet -@item -The @code{String *str} argument provides a string buffer that may be -used to hold the result. (For more information about the @code{String} type, -take a look at the @file{sql_string.h} file.) -@item -The @code{::str()} function should return the string that holds the result or -@code{(char*) 0} if the result is @code{NULL}. -@item -All current string functions try to avoid allocating any memory unless -absolutely necessary! -@end itemize +@menu +* Installing MyODBC:: How to install MyODBC +* ODBC administrator:: How to fill in the various fields in the ODBC administrator program +* MyODBC connect parameters:: +* ODBC Problems:: How to report problems with @strong{MySQL} ODBC +* MyODBC clients:: Programs known to work with @strong{MyODBC} +* ODBC and last_insert_id:: How to get the value of an @code{AUTO_INCREMENT} column in ODBC +* MyODBC bug report:: Reporting problems with MyODBC +@end menu -@node Adding procedures, MySQL internals, Adding functions, Extending MySQL -@section Adding New Procedures to MySQL +@strong{MySQL} provides support for ODBC by means of the @strong{MyODBC} +program. This chapter will teach you how to install @strong{MyODBC}, +and how to use it. Here, you will also find a list of common programs that +are known to work with @strong{MyODBC}. -@cindex procedures, adding -@cindex adding, procedures -@cindex new procedures, adding -In @strong{MySQL}, you can define a procedure in C++ that can access and -modify the data in a query before it is sent to the client. The modification -can be done on row-by-row or @code{GROUP BY} level. +@node Installing MyODBC, ODBC administrator, ODBC, ODBC +@subsection How To Install MyODBC -We have created an example procedure in @strong{MySQL} Version 3.23 to -show you what can be done. +@strong{MyODBC} is a 32-bit ODBC (2.50) level 0 (with level 1 and level +2 features) driver for connecting an ODBC-aware application to +@strong{MySQL}. @strong{MyODBC} works on Windows95, Windows98, NT, and +on most Unix platforms. -Additionally we recommend you to take a look at 'mylua', which you can find in the Contrib directory. @xref{Contrib}. Which this you can use the LUA -language to load a procedure at runtime into @code{mysqld}. +@strong{MyODBC} is in public domain, and you can find the newest version +at @uref{http://www.mysql.com/downloads/api-myodbc.html}. -@menu -* procedure analyse:: Procedure analyse -* Writing a procedure:: Writing a procedure. -@end menu +If you have problem with @strong{MyODBC} and your program also works +with OLEDB, you should try the OLEDB driver that you can find in the +Contrib section. @xref{Contrib}. - -@node procedure analyse, Writing a procedure, Adding procedures, Adding procedures -@subsection Procedure Analyse - -@code{analyse([max elements,[max memory]])} - -This procedure is defined in the @file{sql/sql_analyse.cc}. This -examines the result from your query and returns an analysis of the -results: - -@itemize @bullet -@item -@code{max elements} (default 256) is the maximum number of distinct values -@code{analyse} will notice per column. This is used by @code{analyse} to check if -the optimal column type should be of type @code{ENUM}. -@item -@code{max memory} (default 8192) is the maximum memory @code{analyse} should -allocate per column while trying to find all distinct values. -@end itemize - -@example -SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]]) -@end example - - -@node Writing a procedure, , procedure analyse, Adding procedures -@subsection Writing a Procedure - -For the moment, the only documentation for this is the source. - -You can find all information about procedures by examining the following files: - -@itemize @bullet -@item @file{sql/sql_analyse.cc} -@item @file{sql/procedure.h} -@item @file{sql/procedure.cc} -@item @file{sql/sql_select.cc} -@end itemize - - -@node MySQL internals, , Adding procedures, Extending MySQL -@section MySQL Internals - -@cindex internals -@cindex threads - -This chapter describes a lot of things that you need to know when -working on the @strong{MySQL} code. If you plan to contribute to MySQL -development, want to have access to the bleeding-edge in-between -versions code, or just want to keep track of development, follow the -instructions in @xref{Installing source tree}. If you are interested in MySQL -internals, you should also subscribe to @email{internals@@lists.mysql.com}. -This is a relatively low traffic list, in comparison with -@email{mysql@@lists.mysql.com}. - -@menu -* MySQL threads:: MySQL threads -* MySQL test suite:: MySQL test suite -@end menu - - -@node MySQL threads, MySQL test suite, MySQL internals, MySQL internals -@subsection MySQL Threads - -The @strong{MySQL} server creates the following threads: - -@itemize @bullet - -@item -The TCP/IP connection thread handles all connection requests and -creates a new dedicated thread to handle the authentication and -and SQL query processing for each connection. - -@item -On Windows NT there is a named pipe handler thread that does the same work as -the TCP/IP connection thread on named pipe connect requests. - -@item -The signal thread handles all signals. This thread also normally handles -alarms and calls @code{process_alarm()} to force timeouts on connections -that have been idle too long. - -@item -If @code{mysqld} is compiled with @code{-DUSE_ALARM_THREAD}, a dedicated -thread that handles alarms is created. This is only used on some systems where -there are problems with @code{sigwait()} or if one wants to use the -@code{thr_alarm()} code in ones application without a dedicated signal -handling thread. - -@item -If one uses the @code{--flush_time=#} option, a dedicated thread is created -to flush all tables at the given interval. - -@item -Every connection has its own thread. - -@item -Every different table on which one uses @code{INSERT DELAYED} gets its -own thread. - -@item -If you use @code{--master-host}, a slave replication thread will be -started to read and apply updates from the master. -@end itemize - -@code{mysqladmin processlist} only shows the connection, @code{INSERT DELAYED}, -and replication threads. - - -@node MySQL test suite, , MySQL threads, MySQL internals -@subsection MySQL Test Suite - -@cindex mysqltest, MySQL Test Suite -@cindex testing mysqld, mysqltest - -Until recently, our main full-coverage test suite was based on proprietary -customer data and for that reason has not been publicly available. The only -publicly available part of our testing process consisted of the @code{crash-me} -test, a Perl DBI/DBD benchmark found in the @code{sql-bench} directory, and -miscellaneous tests located in @code{tests} directory. The lack of a -standardized publicly available test suite has made it difficult for our users, -as well developers, to do regression tests on the @strong{MySQL} code. To -address this problem, we have created a new test system that is included in -the source and binary distributions starting in Version 3.23.29. - -The current set of test cases doesn't test everything in @strong{MySQL}, but it -should catch most obvious bugs in the SQL processing code, OS/library -issues, and is quite thorough in testing replication. Our eventual goal -is to have the tests cover 100% of the code. We welcome contributions -to our test suite. You may especially want to contribute tests that -examine the functionality critical to your system, as this will ensure -that all future @strong{MySQL} releases will work well with your -applications. - -@menu -* running mysqltest:: -* extending mysqltest:: -* Reporting mysqltest bugs:: -@end menu - - -@node running mysqltest, extending mysqltest, MySQL test suite, MySQL test suite -@subsubsection Running the MySQL Test Suite - -The test system consist of a test language interpreter -(@code{mysqltest}), a shell script to run all -tests(@code{mysql-test-run}), the actual test cases written in a special -test language, and their expected results. To run the test suite on -your system after a build, type @code{make test} or -@code{mysql-test/mysql-test-run} from the source root. If you have -installed a binary distribution, @code{cd} to the install root -(eg. @code{/usr/local/mysql}), and do @code{scripts/mysql-test-run}. -All tests should succeed. If not, you should try to find out why and -report the problem if this is a bug in @strong{MySQL}. -@xref{Reporting mysqltest bugs}. - -If you have a copy of @code{mysqld} running on the machine where you want to -run the test suite you do not have to stop it, as long as it is not using -ports @code{9306} and @code{9307}. If one of those ports is taken, you should -edit @code{mysql-test-run} and change the values of the master and/or slave -port to one that is available. - -You can run one individual test case with -@code{mysql-test/mysql-test-run test_name}. - -If one test fails, you should test running @code{mysql-test-run} with -the @code{--force} option to check if any other tests fails. - - -@node extending mysqltest, Reporting mysqltest bugs, running mysqltest, MySQL test suite -@subsubsection Extending the MySQL Test Suite - -You can use the @code{mysqltest} language to write your own test cases. -Unfortunately, we have not yet written full documentation for it - we plan to -do this shortly. You can, however, look at our current test cases and use -them as an example. The following points should help you get started: - -@itemize -@item -The tests are located in @code{mysql-test/t/*.test} - -@item -A test case consists of @code{;} terminated statements and is similar to the -input of @code{mysql} command line client. A statement by default is a query -to be sent to @strong{MySQL} server, unless it is recognized as internal -command ( eg. @code{sleep} ). - -@item -All queries that produce results, e.g. @code{SELECT}, @code{SHOW}, -@code{EXPLAIN}, etc., must be preceded with @code{@@/path/to/result/file}. The -file must contain the expected results. An easy way to generate the result -file is to run @code{mysqltest -r < t/test-case-name.test} from -@code{mysql-test} directory, and then edit the generated result files, if -needed, to adjust them to the expected output. In that case, be very careful -about not adding or deleting any invisible characters - make sure to only -change the text and/or delete lines. If you have to insert a line, make sure -the fields are separated with a hard tab, and there is a hard tab at the end. -You may want to use @code{od -c} to make sure your text editor has not messed -anything up during edit. We, of course, hope that you will never have to edit -the output of @code{mysqltest -r} as you only have to do it when you find a -bug. - -@item -To be consistent with our setup, you should put your result files in -@code{mysql-test/r} directory and name them @code{test_name.result}. If the -test produces more than one result, you should use @code{test_name.a.result}, -@code{test_name.b.result}, etc. - -@item -If a statement returns an error, you should on the line before the statement -specify with the @code{--error error-number}. The error number can be -a list of possible error numbers separated with @code{','}. - -@item -If you are writing a replication test case, you should on the first line of -the test file, put @code{source include/master-slave.inc;}. To switch between -master and slave, use @code{connection master;} and @code{connection slave;}. -If you need to do something on an alternate connection, you can do -@code{connection master1;} for the master, and @code{connection slave1;} for -the slave. - -@item -If you need to do something in a loop, you can use something like this: -@example -let $1=1000; -while ($1) -@{ - # do your queries here - dec $1; -@} -@end example - -@item -To sleep between queries, use the @code{sleep} command. It supports fractions -of a second, so you can do @code{sleep 1.3;}, for example, to sleep 1.3 -seconds. - -@item -To run the slave with additional options for your test case, put them -in the command-line format in @code{mysql-test/t/test_name-slave.opt}. For -the master, put them in @code{mysql-test/t/test_name-master.opt}. - -@item -If you have a question about the test suite, or have a test case to contribute, -e-mail to @email{internals@@lists.mysql.com}. As the list does not accept -attachments, you should ftp all the relevant files to: -@url{ftp://support.mysql.com/pub/mysql/Incoming} - -@end itemize - - -@node Reporting mysqltest bugs, , extending mysqltest, MySQL test suite -@subsubsection Reporting Bugs in the MySQL Test Suite - -If your @strong{MySQL} version doesn't pass the test suite you should -do the following: - -@itemize @bullet -@item -Don't send a bug report before you have found out as much as possible of -what when wrong! When you do it, please use the @code{mysqlbug} script -so that we can get information about your system and @code{MySQL} -version. @xref{Bug reports}. -@item -Make sure to include the output of @code{mysql-test-run}, as well as -contents of all @code{.reject} files in @code{mysql-test/r} directory. -@item -If a test in the test suite fails, check if the test fails also when run -by its own: - -@example -cd mysql-test -mysql-test-run --local test-name -@end example - -If this fails, then you should configure @strong{MySQL} with -@code{--with-debug} and run @code{mysql-test-run} with the -@code{--debug} option. If this also fails send the trace file -@file{var/tmp/master.trace} to ftp://support.mysql.com/pub/mysql/secret -so that we can examine it. Please remember to also include a full -description of your system, the version of the mysqld binary and how you -compiled it. - -@item -Try also to run @code{mysql-test-run} with the @code{--force} option to -see if there is any other test that fails. - -@item -If you have compiled @strong{MySQL} yourself, check our manual for how -to compile @strong{MySQL} on your platform or, preferable, use one of -the binaries we have compiled for you at -@uref{http://www.mysql.com/downloads/}. All our standard binaries should -pass the test suite ! - -@item -If you get an error, like @code{Result length mismatch} or @code{Result -content mismatch} it means that the output of the test didn't match -exactly the expected output. This could be a bug in @strong{MySQL} or -that your mysqld version produces slight different results under some -circumstances. - -Failed test results are put in a file with the same base name as the -result file with the @code{.reject} extension. If your test case is -failing, you should do a diff on the two files. If you cannot see how -they are different, examine both with @code{od -c} and also check their -lengths. - -@item -If a test fails totally, you should check the logs file in the -@code{mysql-test/var/log} directory for hints of what went wrong. - -@item -If you have compiled @strong{MySQL} with debugging you can try to debug this -by running @code{mysql-test-run} with the @code{--gdb} and/or @code{--debug} -options. -@xref{Making trace files}. - -If you have not compiled @strong{MySQL} for debugging you should probably -do that. Just specify the @code{--with-debug} options to @code{configure}! -@xref{Installing source}. -@end itemize - - - - -@node ODBC, Common programs, Extending MySQL, Top -@chapter MySQL ODBC Support - -@cindex ODBC -@cindex Windows -@cindex MyODBC - -@menu -* Installing MyODBC:: How to install MyODBC -* ODBC administrator:: How to fill in the various fields in the ODBC administrator program -* MyODBC connect parameters:: -* ODBC Problems:: How to report problems with @strong{MySQL} ODBC -* MyODBC clients:: Programs known to work with @strong{MyODBC} -* ODBC and last_insert_id:: How to get the value of an @code{AUTO_INCREMENT} column in ODBC -* MyODBC bug report:: Reporting problems with MyODBC -@end menu - - -@strong{MySQL} provides support for ODBC by means of the @strong{MyODBC} -program. This chapter will teach you how to install @strong{MyODBC}, -and how to use it. Here, you will also find a list of common programs that -are known to work with @strong{MyODBC}. - - -@node Installing MyODBC, ODBC administrator, ODBC, ODBC -@section How To Install MyODBC - -@strong{MyODBC} is a 32-bit ODBC (2.50) level 0 (with level 1 and level -2 features) driver for connecting an ODBC-aware application to -@strong{MySQL}. @strong{MyODBC} works on Windows95, Windows98, NT, and -on most Unix platforms. - -@strong{MyODBC} is in public domain, and you can find the newest version -at @uref{http://www.mysql.com/downloads/api-myodbc.html}. - -If you have problem with @strong{MyODBC} and your program also works -with OLEDB, you should try the OLEDB driver that you can find in the -Contrib section. @xref{Contrib}. - -Normally you only need to install @strong{MyODBC} on Windows machines. -You only need @strong{MyODBC} for Unix if you have a program like -ColdFusion that is running on the Unix machine and uses ODBC to connect -to the databases. +Normally you only need to install @strong{MyODBC} on Windows machines. +You only need @strong{MyODBC} for Unix if you have a program like +ColdFusion that is running on the Unix machine and uses ODBC to connect +to the databases. If you want to install @strong{MyODBC} on a Unix box, you will also need an @strong{ODBC} manager. @strong{MyODBC} is known to work with @@ -38319,7 +37941,7 @@ you run into problems. @node ODBC administrator, MyODBC connect parameters, Installing MyODBC, ODBC -@section How to Fill in the Various Fields in the ODBC Administrator Program +@subsection How to Fill in the Various Fields in the ODBC Administrator Program @cindex ODBC, administrator @@ -38376,7 +37998,7 @@ You can use all options that are usable by @code{mysql_options()}. @node MyODBC connect parameters, ODBC Problems, ODBC administrator, ODBC -@section Connect parameters for MyODBC +@subsection Connect parameters for MyODBC One can specify the following parameters for @strong{MyODBC} on the @code{[Servername]} section of an @code{ODBC.INI} file or @@ -38434,7 +38056,7 @@ you should instead use @code{MYODBCD.DLL}. To install this file, copy @node ODBC Problems, MyODBC clients, MyODBC connect parameters, ODBC -@section How to Report Problems with MyODBC +@subsection How to Report Problems with MyODBC @strong{MyODBC} has been tested with Access, Admndemo.exe, C++-Builder, Borland Builder 4, Centura Team Developer (formerly Gupta SQL/Windows), @@ -38465,7 +38087,7 @@ try to figure out why things go wrong. @node MyODBC clients, ODBC and last_insert_id, ODBC Problems, ODBC -@section Programs Known to Work with MyODBC +@subsection Programs Known to Work with MyODBC Most programs should work with @strong{MyODBC}, but for each of those listed below, we have tested it ourselves or received confirmation from @@ -38754,7 +38376,7 @@ You should use the option flag @code{Don't optimize column widths}. @node ODBC and last_insert_id, MyODBC bug report, MyODBC clients, ODBC -@section How to Get the Value of an @code{AUTO_INCREMENT} Column in ODBC +@subsection How to Get the Value of an @code{AUTO_INCREMENT} Column in ODBC @cindex AUTO-INCREMENT, ODBC @@ -38784,7 +38406,7 @@ SELECT * FROM tbl_name WHERE auto IS NULL; @node MyODBC bug report, , ODBC and last_insert_id, ODBC -@section Reporting Problems with MyODBC +@subsection Reporting Problems with MyODBC @cindex reporting, MyODBC problems @cindex problems, ODBC @@ -38844,88 +38466,18 @@ Remember that the more information you can supply to us, the more likely it is that we can fix the problem! -@node Common programs, Clients, ODBC, Top -@chapter Using MySQL with Some Common Programs - -@menu -* Borland C++:: -@end menu - -This chapter describes how to use @strong{MySQL} with some common programs. - -In this chapter you will: - -@itemize @bullet -@item -Learn how to easily store your Apache log files in a @strong{MySQL} -database. -@item -Find some tips on how to compile @strong{MySQL} and @strong{MySQL}-based -programs using Borland C++. -@end itemize - - -@cindex Borland C++ compiler -@node Borland C++, , Common programs, Common programs -@section Borland C++ - -You can compile the @strong{MySQL} Windows source with Borland C++ 5.02. -(The Windows source includes only projects for Microsoft VC++, for -Borland C++ you have to do the project files yourself). - -One known problem with Borland C++ is that it uses a different structure -alignment than VC++. This means that you will run into problems if you -try to use the default @code{libmysql.dll} libraries (that was compiled -with VC++) with Borland C++. You can do one of the following to avoid -this problem. - -@itemize @bullet -@item -You can use the static @strong{MySQL} libraries for Borland C++ that you -can find on @uref{http://www.mysql.com/downloads/os-win32.html}. -@item -Only call @code{mysql_init()} with @code{NULL} as an argument, not a -pre-allocated MYSQL struct. -@end itemize - - - - -@node Clients, Problems, Common programs, Top -@chapter MySQL APIs - -@cindex client tools -@cindex APIs -@cindex @code{mysqlclient} library -@cindex buffer sizes, client -@cindex library, @code{mysqlclient} - -@menu -* C:: @strong{MySQL} C API -* Perl:: @strong{MySQL} Perl API -* Eiffel:: @strong{MySQL} Eiffel wrapper -* Java:: @strong{MySQL} Java connectivity (JDBC) -* PHP:: @strong{MySQL} PHP API -* Cplusplus:: @strong{MySQL} C++ APIs -* Python:: @strong{MySQL} Python APIs -* Tcl:: @strong{MySQL} Tcl APIs -@end menu - -This chapter describes the APIs available for @strong{MySQL}, where to get -them, and how to use them. The C API is the most extensively covered, as it -was developed by the @strong{MySQL} team, and is the basis for most of the -other APIs. +@node C, Cplusplus, ODBC, Clients +@section MySQL C API @cindex C API, datatypes @cindex datatypes, C API -@node C, Perl, Clients, Clients -@section MySQL C API @menu * C API datatypes:: C API Datatypes * C API function overview:: C API Function Overview * C API functions:: C API Function Descriptions * C API problems:: +* Building clients:: * Thread-safe clients:: @end menu @@ -38969,10 +38521,11 @@ is closed, at which time client memory is reclaimed. For programming with threads, consult the 'how to make a thread-safe client' chapter. @xref{Thread-safe clients}. + @node C API datatypes, C API function overview, C, C @subsection C API Datatypes -@table @code +@table @code @tindex MYSQL C type @item MYSQL This structure represents a handle to one database connection. It is @@ -39131,11 +38684,13 @@ status of the @code{flags} value: The number of decimals for numeric fields. @end table -@cindex C API, functions -@cindex functions, C API + @node C API function overview, C API functions, C API datatypes, C @subsection C API Function Overview +@cindex C API, functions +@cindex functions, C API + The functions available in the C API are listed below and are described in greater detail in the next section. @xref{C API functions}. @@ -39411,6 +38966,7 @@ functions. These return the error code or error message for the most recently invoked function that can succeed or fail, allowing you to determine when an error occurred and what it was. + @node C API functions, C API problems, C API function overview, C @subsection C API Function Descriptions @@ -39498,10 +39054,12 @@ find out which of these occurred by calling @code{mysql_errno()}. A string representation of the error may be obtained by calling @code{mysql_error()}. -@findex @code{mysql_affected_rows()} + @node mysql_affected_rows, mysql_close, C API functions, C API functions @subsubsection @code{mysql_affected_rows()} +@findex @code{mysql_affected_rows()} + @code{my_ulonglong mysql_affected_rows(MYSQL *mysql)} @subsubheading Description @@ -39543,10 +39101,12 @@ Note that when one uses a @code{REPLACE} command, old row. This is because in this case one row was inserted and then the duplicate was deleted. -@findex @code{mysql_close()} + @node mysql_close, mysql_connect, mysql_affected_rows, C API functions @subsubsection @code{mysql_close()} +@findex @code{mysql_close()} + @code{void mysql_close(MYSQL *mysql)} @subsubheading Description @@ -39562,10 +39122,12 @@ None. None. -@findex @code{mysql_connect()} + @node mysql_connect, mysql_change_user, mysql_close, C API functions @subsubsection @code{mysql_connect()} +@findex @code{mysql_connect()} + @code{MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)} @subsubheading Description @@ -39595,10 +39157,12 @@ Same as for @code{mysql_real_connect()}. Same as for @code{mysql_real_connect()}. -@findex @code{mysql_change_user()} + @node mysql_change_user, mysql_character_set_name, mysql_connect, C API functions @subsubsection @code{mysql_change_user()} +@findex @code{mysql_change_user()} + @code{my_bool mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char *db)} @@ -39657,10 +39221,12 @@ if (mysql_change_user(&mysql, "user", "password", "new_database")) @} @end example -@findex @code{mysql_character_set_name()} + @node mysql_character_set_name, mysql_create_db, mysql_change_user, C API functions @subsubsection @code{mysql_character_set_name()} +@findex @code{mysql_character_set_name()} + @code{const char *mysql_character_set_name(MYSQL *mysql)} @subsubheading Description @@ -39675,10 +39241,11 @@ The default character set None. -@findex @code{mysql_create_db()} @node mysql_create_db, mysql_data_seek, mysql_character_set_name, C API functions @subsubsection @code{mysql_create_db()} +@findex @code{mysql_create_db()} + @code{int mysql_create_db(MYSQL *mysql, const char *db)} @subsubheading Description @@ -39718,10 +39285,12 @@ if(mysql_create_db(&mysql, "my_database")) @} @end example -@findex @code{mysql_data_seek()} + @node mysql_data_seek, mysql_debug, mysql_create_db, C API functions @subsubsection @code{mysql_data_seek()} +@findex @code{mysql_data_seek()} + @code{void mysql_data_seek(MYSQL_RES *result, unsigned long long offset)} @subsubheading Description @@ -39740,10 +39309,12 @@ None. @subsubheading Errors None. -@findex @code{mysql_debug()} + @node mysql_debug, mysql_drop_db, mysql_data_seek, C API functions @subsubsection @code{mysql_debug()} +@findex @code{mysql_debug()} + @code{void mysql_debug(char *debug)} @subsubheading Description @@ -39768,10 +39339,12 @@ The call shown below causes the client library to generate a trace file in mysql_debug("d:t:O,/tmp/client.trace"); @end example -@findex @code{mysql_drop_db()} + @node mysql_drop_db, mysql_dump_debug_info, mysql_debug, C API functions @subsubsection @code{mysql_drop_db()} +@findex @code{mysql_drop_db()} + @code{int mysql_drop_db(MYSQL *mysql, const char *db)} @subsubheading Description @@ -39806,10 +39379,12 @@ if(mysql_drop_db(&mysql, "my_database")) mysql_error(&mysql)); @end example -@findex @code{mysql_dump_debug_info()} + @node mysql_dump_debug_info, mysql_eof, mysql_drop_db, C API functions @subsubsection @code{mysql_dump_debug_info()} +@findex @code{mysql_dump_debug_info()} + @code{int mysql_dump_debug_info(MYSQL *mysql)} @subsubheading Description @@ -39833,10 +39408,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_eof()} + @node mysql_eof, mysql_errno, mysql_dump_debug_info, C API functions @subsubsection @code{mysql_eof()} +@findex @code{mysql_eof()} + @code{my_bool mysql_eof(MYSQL_RES *result)} @subsubheading Description @@ -39910,10 +39487,12 @@ if(mysql_errno(&mysql)) // mysql_fetch_row() failed due to an error @} @end example -@findex @code{mysql_errno()} + @node mysql_errno, mysql_error, mysql_eof, C API functions @subsubsection @code{mysql_errno()} +@findex @code{mysql_errno()} + @code{unsigned int mysql_errno(MYSQL *mysql)} @subsubheading Description @@ -39933,10 +39512,12 @@ An error code value. Zero if no error occurred. @subsubheading Errors None. -@findex @code{mysql_error()} + @node mysql_error, mysql_escape_string, mysql_errno, C API functions @subsubsection @code{mysql_error()} +@findex @code{mysql_error()} + @code{char *mysql_error(MYSQL *mysql)} @subsubheading Description @@ -39971,10 +39552,12 @@ occurred. @subsubheading Errors None. -@findex @code{mysql_escape_string()} + @node mysql_escape_string, mysql_fetch_field, mysql_error, C API functions @subsubsection @code{mysql_escape_string()} +@findex @code{mysql_escape_string()} + You should use @code{mysql_real_escape_string()} instead! This is identical to @code{mysql_real_escape_string()} except that it @@ -39984,10 +39567,12 @@ according to the current character set while @code{mysql_escape_string()} does not respect the current charset setting. -@findex @code{mysql_fetch_field()} + @node mysql_fetch_field, mysql_fetch_fields, mysql_escape_string, C API functions @subsubsection @code{mysql_fetch_field()} +@findex @code{mysql_fetch_field()} + @code{MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)} @subsubheading Description @@ -40030,10 +39615,12 @@ while((field = mysql_fetch_field(result))) @} @end example -@findex @code{mysql_fetch_fields()} + @node mysql_fetch_fields, mysql_fetch_field_direct, mysql_fetch_field, C API functions @subsubsection @code{mysql_fetch_fields()} +@findex @code{mysql_fetch_fields()} + @code{MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)} @subsubheading Description @@ -40064,10 +39651,12 @@ for(i = 0; i < num_fields; i++) @} @end example -@findex @code{mysql_fetch_field_direct()} + @node mysql_fetch_field_direct, mysql_fetch_lengths, mysql_fetch_fields, C API functions @subsubsection @code{mysql_fetch_field_direct()} +@findex @code{mysql_fetch_field_direct()} + @code{MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result, unsigned int fieldnr)} @subsubheading Description @@ -40100,10 +39689,12 @@ for(i = 0; i < num_fields; i++) @} @end example -@findex @code{mysql_fetch_lengths()} + @node mysql_fetch_lengths, mysql_fetch_row, mysql_fetch_field_direct, C API functions @subsubsection @code{mysql_fetch_lengths()} +@findex @code{mysql_fetch_lengths()} + @code{unsigned long *mysql_fetch_lengths(MYSQL_RES *result)} @subsubheading Description @@ -40150,10 +39741,12 @@ if (row) @} @end example -@findex @code{mysql_fetch_row()} + @node mysql_fetch_row, mysql_field_count, mysql_fetch_lengths, C API functions @subsubsection @code{mysql_fetch_row()} +@findex @code{mysql_fetch_row()} + @code{MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)} @subsubheading Description @@ -40210,10 +39803,12 @@ while ((row = mysql_fetch_row(result))) @} @end example -@findex @code{mysql_field_count()} + @node mysql_field_count, mysql_field_seek, mysql_fetch_row, C API functions @subsubsection @code{mysql_field_count()} +@findex @code{mysql_field_count()} + @code{unsigned int mysql_field_count(MYSQL *mysql)} If you are using a version of @strong{MySQL} earlier than Version 3.22.24, you @@ -40282,10 +39877,12 @@ error from @code{mysql_store_result()} rather than inferring from the value of @code{mysql_field_count()} whether or not the statement was a @code{SELECT}. -@findex @code{mysql_field_seek()} + @node mysql_field_seek, mysql_field_tell, mysql_field_count, C API functions @subsubsection @code{mysql_field_seek()} +@findex @code{mysql_field_seek()} + @code{MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)} @subsubheading Description @@ -40303,10 +39900,12 @@ The previous value of the field cursor. @subsubheading Errors None. -@findex @code{mysql_field_tell()} + @node mysql_field_tell, mysql_free_result, mysql_field_seek, C API functions @subsubsection @code{mysql_field_tell()} +@findex @code{mysql_field_tell()} + @code{MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)} @subsubheading Description @@ -40322,10 +39921,12 @@ The current offset of the field cursor. @subsubheading Errors None. -@findex @code{mysql_free_result()} + @node mysql_free_result, mysql_get_client_info, mysql_field_tell, C API functions @subsubsection @code{mysql_free_result()} +@findex @code{mysql_free_result()} + @code{void mysql_free_result(MYSQL_RES *result)} @subsubheading Description @@ -40342,10 +39943,12 @@ None. @subsubheading Errors None. -@findex @code{mysql_get_client_info()} + @node mysql_get_client_info, mysql_get_host_info, mysql_free_result, C API functions @subsubsection @code{mysql_get_client_info()} +@findex @code{mysql_get_client_info()} + @code{char *mysql_get_client_info(void)} @subsubheading Description @@ -40359,10 +39962,12 @@ A character string that represents the @strong{MySQL} client library version. @subsubheading Errors None. -@findex @code{mysql_get_host_info()} + @node mysql_get_host_info, mysql_get_proto_info, mysql_get_client_info, C API functions @subsubsection @code{mysql_get_host_info()} +@findex @code{mysql_get_host_info()} + @code{char *mysql_get_host_info(MYSQL *mysql)} @subsubheading Description @@ -40377,10 +39982,12 @@ A character string representing the server host name and the connection type. @subsubheading Errors None. -@findex @code{mysql_get_proto_info()} + @node mysql_get_proto_info, mysql_get_server_info, mysql_get_host_info, C API functions @subsubsection @code{mysql_get_proto_info()} +@findex @code{mysql_get_proto_info()} + @code{unsigned int mysql_get_proto_info(MYSQL *mysql)} @subsubheading Description @@ -40395,10 +40002,12 @@ connection. @subsubheading Errors None. -@findex @code{mysql_get_server_info()} + @node mysql_get_server_info, mysql_info, mysql_get_proto_info, C API functions @subsubsection @code{mysql_get_server_info()} +@findex @code{mysql_get_server_info()} + @code{char *mysql_get_server_info(MYSQL *mysql)} @subsubheading Description @@ -40412,10 +40021,12 @@ A character string that represents the server version number. @subsubheading Errors None. -@findex @code{mysql_info()} + @node mysql_info, mysql_init, mysql_get_server_info, C API functions @subsubsection @code{mysql_info()} +@findex @code{mysql_info()} + @code{char *mysql_info(MYSQL *mysql)} @subsubheading Description @@ -40452,10 +40063,12 @@ query. @subsubheading Errors None. -@findex @code{mysql_init()} + @node mysql_init, mysql_insert_id, mysql_info, C API functions @subsubsection @code{mysql_init()} +@findex @code{mysql_init()} + @code{MYSQL *mysql_init(MYSQL *mysql)} @subsubheading Description @@ -40475,10 +40088,12 @@ insufficient memory to allocate a new object. @subsubheading Errors In case of insufficient memory, @code{NULL} is returned. -@findex @code{mysql_insert_id()} + @node mysql_insert_id, mysql_kill, mysql_init, C API functions @subsubsection @code{mysql_insert_id()} +@findex @code{mysql_insert_id()} + @code{my_ulonglong mysql_insert_id(MYSQL *mysql)} @subsubheading Description @@ -40506,10 +40121,12 @@ the query did not update an @code{AUTO_INCREMENT} value. @subsubheading Errors None. -@findex @code{mysql_kill()} + @node mysql_kill, mysql_list_dbs, mysql_insert_id, C API functions @subsubsection @code{mysql_kill()} +@findex @code{mysql_kill()} + @code{int mysql_kill(MYSQL *mysql, unsigned long pid)} @subsubheading Description @@ -40533,10 +40150,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_list_dbs()} + @node mysql_list_dbs, mysql_list_fields, mysql_kill, C API functions @subsubsection @code{mysql_list_dbs()} +@findex @code{mysql_list_dbs()} + @code{MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)} @subsubheading Description @@ -40569,10 +40188,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_list_fields()} + @node mysql_list_fields, mysql_list_processes, mysql_list_dbs, C API functions @subsubsection @code{mysql_list_fields()} +@findex @code{mysql_list_fields()} + @code{MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)} @subsubheading Description @@ -40606,10 +40227,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_list_processes()} + @node mysql_list_processes, mysql_list_tables, mysql_list_fields, C API functions @subsubsection @code{mysql_list_processes()} +@findex @code{mysql_list_processes()} + @code{MYSQL_RES *mysql_list_processes(MYSQL *mysql)} @subsubheading Description @@ -40637,10 +40260,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_list_tables()} + @node mysql_list_tables, mysql_num_fields, mysql_list_processes, C API functions @subsubsection @code{mysql_list_tables()} +@findex @code{mysql_list_tables()} + @code{MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)} @subsubheading Description @@ -40671,11 +40296,13 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_num_fields()} -@findex @code{mysql_field_count()} + @node mysql_num_fields, mysql_num_rows, mysql_list_tables, C API functions @subsubsection @code{mysql_num_fields()} +@findex @code{mysql_num_fields()} +@findex @code{mysql_field_count()} + @code{unsigned int mysql_num_fields(MYSQL_RES *result)} or @@ -40749,10 +40376,12 @@ is to replace the @code{mysql_errno(&mysql)} call with a check if @code{mysql_field_count(&mysql)} is = 0. This will only happen if something went wrong. -@findex @code{mysql_num_rows()} + @node mysql_num_rows, mysql_options, mysql_num_fields, C API functions @subsubsection @code{mysql_num_rows()} +@findex @code{mysql_num_rows()} + @code{my_ulonglong mysql_num_rows(MYSQL_RES *result)} @subsubheading Description @@ -40773,10 +40402,12 @@ The number of rows in the result set. @subsubheading Errors None. -@findex @code{mysql_options()} + @node mysql_options, mysql_ping, mysql_num_rows, C API functions @subsubsection @code{mysql_options()} +@findex @code{mysql_options()} + @code{int mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg)} @subsubheading Description @@ -40853,10 +40484,12 @@ The above requests the client to use the compressed client/server protocol and read the additional options from the @code{odbc} section in the @code{my.cnf} file. -@findex @code{mysql_ping()} + @node mysql_ping, mysql_query, mysql_options, C API functions @subsubsection @code{mysql_ping()} +@findex @code{mysql_ping()} + @code{int mysql_ping(MYSQL *mysql)} @subsubheading Description @@ -40883,10 +40516,12 @@ The @strong{MySQL} server has gone away. An unknown error occurred. @end table -@findex @code{mysql_query()} + @node mysql_query, mysql_real_connect, mysql_ping, C API functions @subsubsection @code{mysql_query()} +@findex @code{mysql_query()} + @code{int mysql_query(MYSQL *mysql, const char *query)} @subsubheading Description @@ -40920,10 +40555,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_real_connect()} + @node mysql_real_connect, mysql_real_escape_string, mysql_query, C API functions @subsubsection @code{mysql_real_connect()} +@findex @code{mysql_real_connect()} + @code{MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, @@ -41078,10 +40715,12 @@ flag (part of the MYSQL structure) to a value of @code{1}. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up. -@findex @code{mysql_real_escape_string()} + @node mysql_real_escape_string, mysql_real_query, mysql_real_connect, C API functions @subsubsection @code{mysql_real_escape_string()} +@findex @code{mysql_real_escape_string()} + @code{unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length)} @subsubheading Description @@ -41137,10 +40776,12 @@ terminating null character. @subsubheading Errors None. -@findex @code{mysql_real_query()} + @node mysql_real_query, mysql_reload, mysql_real_escape_string, C API functions @subsubsection @code{mysql_real_query()} +@findex @code{mysql_real_query()} + @code{int mysql_real_query(MYSQL *mysql, const char *query, unsigned int length)} @subsubheading Description @@ -41177,10 +40818,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_reload()} + @node mysql_reload, mysql_row_seek, mysql_real_query, C API functions @subsubsection @code{mysql_reload()} +@findex @code{mysql_reload()} + @code{int mysql_reload(MYSQL *mysql)} @subsubheading Description @@ -41208,10 +40851,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_row_seek()} + @node mysql_row_seek, mysql_row_tell, mysql_reload, C API functions @subsubsection @code{mysql_row_seek()} +@findex @code{mysql_row_seek()} + @code{MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset)} @subsubheading Description @@ -41233,10 +40878,12 @@ subsequent call to @code{mysql_row_seek()}. @subsubheading Errors None. -@findex @code{mysql_row_tell()} + @node mysql_row_tell, mysql_select_db, mysql_row_seek, C API functions @subsubsection @code{mysql_row_tell()} +@findex @code{mysql_row_tell()} + @code{MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)} @subsubheading Description @@ -41255,10 +40902,12 @@ The current offset of the row cursor. @subsubheading Errors None. -@findex @code{mysql_select_db()} + @node mysql_select_db, mysql_shutdown, mysql_row_tell, C API functions @subsubsection @code{mysql_select_db()} +@findex @code{mysql_select_db()} + @code{int mysql_select_db(MYSQL *mysql, const char *db)} @subsubheading Description @@ -41288,10 +40937,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_shutdown()} + @node mysql_shutdown, mysql_stat, mysql_select_db, C API functions @subsubsection @code{mysql_shutdown()} +@findex @code{mysql_shutdown()} + @code{int mysql_shutdown(MYSQL *mysql)} @subsubheading Description @@ -41316,10 +40967,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_stat()} + @node mysql_stat, mysql_store_result, mysql_shutdown, C API functions @subsubsection @code{mysql_stat()} +@findex @code{mysql_stat()} + @code{char *mysql_stat(MYSQL *mysql)} @subsubheading Description @@ -41346,10 +40999,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_store_result()} + @node mysql_store_result, mysql_thread_id, mysql_stat, C API functions @subsubsection @code{mysql_store_result()} +@findex @code{mysql_store_result()} + @code{MYSQL_RES *mysql_store_result(MYSQL *mysql)} @subsubheading Description @@ -41417,10 +41072,12 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@findex @code{mysql_thread_id()} + @node mysql_thread_id, mysql_use_result, mysql_store_result, C API functions @subsubsection @code{mysql_thread_id()} +@findex @code{mysql_thread_id()} + @code{unsigned long mysql_thread_id(MYSQL *mysql)} @subsubheading Description @@ -41439,10 +41096,12 @@ The thread ID of the current connection. @subsubheading Errors None. -@findex @code{mysql_use_result()} + @node mysql_use_result, , mysql_thread_id, C API functions @subsubsection @code{mysql_use_result()} +@findex @code{mysql_use_result()} + @code{MYSQL_RES *mysql_use_result(MYSQL *mysql)} @subsubheading Description @@ -41502,11 +41161,13 @@ The connection to the server was lost during the query. An unknown error occurred. @end table -@node C API problems, Thread-safe clients, C API functions, C + +@node C API problems, Building clients, C API functions, C @subsection Common questions and problems when using the C API @tindex @code{mysql_query()} @tindex @code{mysql_store_result()} + @menu * NULL mysql_store_result:: * Query results:: @@ -41514,6 +41175,7 @@ An unknown error occurred. * C API linking problems:: @end menu + @node NULL mysql_store_result, Query results, C API problems, C API problems @subsubsection Why Is It that After @code{mysql_query()} Returns Success, @code{mysql_store_result()} Sometimes Returns @code{NULL?} @@ -41553,6 +41215,7 @@ You can test for an error by calling @code{mysql_error()} or * C API linking problems:: @end menu + @node Query results, Getting unique ID, NULL mysql_store_result, C API problems @subsubsection What Results Can I Get From a Query? @@ -41588,12 +41251,14 @@ for the format of the string that it returns. @code{mysql_info()} returns a @code{NULL} pointer if there is no additional information. @end itemize + +@node Getting unique ID, C API linking problems, Query results, C API problems +@subsubsection How Can I Get the Unique ID for the Last Inserted Row? + @cindex unique ID @cindex last row, unique ID @cindex ID, unique @cindex tables, unique ID for last row -@node Getting unique ID, C API linking problems, Query results, C API problems -@subsubsection How Can I Get the Unique ID for the Last Inserted Row? If you insert a record in a table containing a column that has the @code{AUTO_INCREMENT} attribute, you can get the most recently generated @@ -41630,11 +41295,13 @@ INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table @end example -@cindex linking, problems -@cindex C API, linking problems + @node C API linking problems, , Getting unique ID, C API problems @subsubsection Problems Linking with the C API +@cindex linking, problems +@cindex C API, linking problems + When linking with the C API, the following errors may occur on some systems: @example @@ -41649,10 +41316,33 @@ ld: fatal: Symbol referencing errors. No output written to client If this happens on your system, you must include the math library by adding @code{-lm} to the end of the compile/link line. + +@node Building clients, Thread-safe clients, C API problems, C +@subsection Building Client Programs + +@cindex client programs, building +@cindex linking +@cindex building, client programs +@cindex programs, client + +If you compile @strong{MySQL} clients that you've written yourself or that +you obtain from a third party, they must be linked using the +@code{-lmysqlclient -lz} option on the link command. You may also need to +specify a @code{-L} option to tell the linker where to find the library. For +example, if the library is installed in @file{/usr/local/mysql/lib}, use +@code{-L/usr/local/mysql/lib -lmysqlclient -lz} on the link command. + +For clients that use @strong{MySQL} header files, you may need to specify a +@code{-I} option when you compile them (for example, +@code{-I/usr/local/mysql/include}), so the compiler can find the header +files. + + +@node Thread-safe clients, , Building clients, C +@subsection How to Make a Thread-safe Client + @cindex clients, thread-safe @cindex thread-safe clients -@node Thread-safe clients, , C API problems, C -@subsection How to Make a Thread-safe Client The client library is almost thread safe. The biggest problem is that the subroutines in @file{net.c} that read from sockets are not @@ -41731,637 +41421,1089 @@ You may get some errors because of undefined symbols when linking your client with @code{mysqlclient_r}. In most cases this is because you haven't included the thread libraries on the link/compile line. -@cindex APIs, Perl -@cindex Perl API -@node Perl, Eiffel, C, Clients -@section MySQL Perl API -This section documents the Perl @code{DBI} interface. The former interface -was called @code{mysqlperl}. @code{DBI}/@code{DBD} now is the -recommended Perl interface, so @code{mysqlperl} is obsolete and is not -documented here. +@node Cplusplus, Java, C, Clients +@section MySQL C++ APIs @menu -* DBI with DBD:: @code{DBI} with @code{DBD::mysql} -* Perl DBI Class:: The @code{DBI} interface -* DBI-info:: More @code{DBI}/@code{DBD} information +* Borland C++:: @end menu -@cindex @code{DBI} interface -@node DBI with DBD, Perl DBI Class, Perl, Perl -@subsection @code{DBI} with @code{DBD::mysql} -@code{DBI} is a generic interface for many databases. That means that -you can write a script that works with many different database engines -without change. You need a DataBase Driver (DBD) defined for each -database type. For @strong{MySQL}, this driver is called -@code{DBD::mysql}. +@cindex C++ APIs -For more information on the Perl5 DBI, please visit the @code{DBI} Web -page and read the documentation: -@example -@uref{http://www.symbolstone.org/technology/perl/DBI/index.html} -@end example -For more information on Object Oriented Programming -(OOP) as defined in Perl5, see the Perl OOP page: -@example -@uref{http://language.perl.com/info/documentation.html} -@end example +Two APIs are available in the @strong{MySQL} +@uref{http://www.mysql.com/Downloads/Contrib/,Contrib directory}. -Note that if you want to use transactions with Perl, you need to have -@code{Msql-Mysql-modules} version 1.2216 or newer. -Installation instructions for @strong{MySQL} Perl support are given in -@ref{Perl support}. +@node Borland C++, , Cplusplus, Cplusplus +@subsection Borland C++ -@cindex @code{DBI} Perl module -@node Perl DBI Class, DBI-info, DBI with DBD, Perl -@subsection The @code{DBI} Interface +@cindex Borland C++ compiler -@noindent -@strong{Portable DBI Methods} +You can compile the @strong{MySQL} Windows source with Borland C++ 5.02. +(The Windows source includes only projects for Microsoft VC++, for +Borland C++ you have to do the project files yourself). -@multitable @columnfractions .3 .7 -@item @code{connect} @tab Establishes a connection to a database server. -@item @code{disconnect} @tab Disconnects from the database server. -@item @code{prepare} @tab Prepares a SQL statement for execution. -@item @code{execute} @tab Executes prepared statements. -@item @code{do} @tab Prepares and executes a SQL statement. -@item @code{quote} @tab Quotes string or @code{BLOB} values to be inserted. -@item @code{fetchrow_array} @tab Fetches the next row as an array of fields. -@item @code{fetchrow_arrayref} @tab Fetches next row as a reference array of fields. -@item @code{fetchrow_hashref} @tab Fetches next row as a reference to a hashtable. -@item @code{fetchall_arrayref} @tab Fetches all data as an array of arrays. -@item @code{finish} @tab Finishes a statement and lets the system free resources. -@item @code{rows} @tab Returns the number of rows affected. -@item @code{data_sources} @tab Returns an array of databases available on localhost. -@item @code{ChopBlanks} @tab Controls whether @code{fetchrow_*} methods trim spaces. -@item @code{NUM_OF_PARAMS} @tab The number of placeholders in the prepared statement. -@item @code{NULLABLE} @tab Which columns can be @code{NULL}. -@item @code{trace} @tab Perform tracing for debugging. -@end multitable +One known problem with Borland C++ is that it uses a different structure +alignment than VC++. This means that you will run into problems if you +try to use the default @code{libmysql.dll} libraries (that was compiled +with VC++) with Borland C++. You can do one of the following to avoid +this problem. -@noindent -@strong{MySQL-specific Methods} +@itemize @bullet +@item +You can use the static @strong{MySQL} libraries for Borland C++ that you +can find on @uref{http://www.mysql.com/downloads/os-win32.html}. +@item +Only call @code{mysql_init()} with @code{NULL} as an argument, not a +pre-allocated MYSQL struct. +@end itemize -@multitable @columnfractions .3 .7 -@item @code{insertid} @tab The latest @code{AUTO_INCREMENT} value. -@item @code{is_blob} @tab Which columns are @code{BLOB} values. -@item @code{is_key} @tab Which columns are keys. -@item @code{is_num} @tab Which columns are numeric. -@item @code{is_pri_key} @tab Which columns are primary keys. -@item @code{is_not_null} @tab Which columns CANNOT be @code{NULL}. See @code{NULLABLE}. -@item @code{length} @tab Maximum possible column sizes. -@item @code{max_length} @tab Maximum column sizes actually present in result. -@item @code{NAME} @tab Column names. -@item @code{NUM_OF_FIELDS} @tab Number of fields returned. -@item @code{table} @tab Table names in returned set. -@item @code{type} @tab All column types. -@end multitable -The Perl methods are described in more detail in the following sections. -Variables used for method return values have these meanings: +@node Java, Python, Cplusplus, Clients +@section MySQL Java Connectivity (JDBC) -@table @code -@item $dbh -Database handle +@cindex Java connectivity +@cindex JDBC -@item $sth -Statement handle +There are 2 supported JDBC drivers for @strong{MySQL} (the mm driver and +the Reisin JDBC driver). You can find a copy of the mm driver at +@uref{http://mmmysql.sourceforge.net/} or +@uref{http://www.mysql.com/Downloads/Contrib/} and the Reisin driver at +@uref{http://www.caucho.com/projects/jdbc-mysql/index.xtp} For +documentation consult any JDBC documentation and the driver's own +documentation for @strong{MySQL}-specific features. -@item $rc -Return code (often a status) -@item $rv -Return value (often a row count) -@end table +@node Python, Tcl, Java, Clients +@section MySQL Python APIs -@noindent -@strong{Portable DBI Methods} +@cindex Python APIs -@table @code +The @strong{MySQL} @uref{http://www.mysql.com/Downloads/Contrib/,Contrib directory} +contains a Python interface written by Joseph Skinner. -@findex DBI->connect() -@findex connect() DBI method -@item connect($data_source, $username, $password) -Use the @code{connect} method to make a database connection to the data -source. The @code{$data_source} value should begin with -@code{DBI:driver_name:}. -Example uses of @code{connect} with the @code{DBD::mysql} driver: -@example -$dbh = DBI->connect("DBI:mysql:$database", $user, $password); -$dbh = DBI->connect("DBI:mysql:$database:$hostname", - $user, $password); -$dbh = DBI->connect("DBI:mysql:$database:$hostname:$port", - $user, $password); -@end example -If the user name and/or password are undefined, @code{DBI} uses the -values of the @code{DBI_USER} and @code{DBI_PASS} environment variables, -respectively. If you don't specify a hostname, it defaults to -@code{'localhost'}. If you don't specify a port number, it defaults to the -default @strong{MySQL} port (@value{default_port}). +You can also use the Python interface to iODBC to access a +@strong{MySQL} server. +@uref{http://starship.skyport.net/~lemburg/,mxODBC} -As of @code{Msql-Mysql-modules} Version 1.2009, -the @code{$data_source} value allows certain modifiers: -@table @code -@item mysql_read_default_file=file_name -Read @file{filename} as an option file. For information on option files, -see @ref{Option files}. +@node Tcl, Eiffel, Python, Clients +@section MySQL Tcl APIs -@item mysql_read_default_group=group_name -The default group when reading an option file is normally the -@code{[client]} group. By specifying the @code{mysql_read_default_group} -option, the default group becomes the @code{[group_name]} group. +@cindex Tcl APIs -@item mysql_compression=1 -Use compressed communication between the client and server (@strong{MySQL} -Version 3.22.3 or later). +@uref{http://www.binevolve.com/~tdarugar/tcl-sql/, Tcl at binevolve}. +The +@uref{http://www.mysql.com/Downloads/Contrib,Contrib directory} contains a Tcl +interface that is based on msqltcl 1.50. -@item mysql_socket=/path/to/socket -Specify the pathname of the Unix socket that is used to connect -to the server (@strong{MySQL} Version 3.21.15 or later). -@end table -Multiple modifiers may be given; each must be preceded by a semicolon. +@node Eiffel, , Tcl, Clients +@section MySQL Eiffel wrapper -For example, if you want to avoid hardcoding the user name and password into -a @code{DBI} script, you can take them from the user's @file{~/.my.cnf} -option file instead by writing your @code{connect} call like this: +@cindex Eiffel Wrapper +@cindex wrappers, Eiffel -@example -$dbh = DBI->connect("DBI:mysql:$database" - . ";mysql_read_default_file=$ENV@{HOME@}/.my.cnf", - $user, $password); -@end example +The @strong{MySQL} @uref{http://www.mysql.com/Downloads/Contrib/,Contrib directory} +contains an Eiffel wrapper written by Michael Ravits. -This call will read options defined for the @code{[client]} group in the -option file. If you wanted to do the same thing but use options specified -for the @code{[perl]} group as well, you could use this: +You can also find this at: +@url{http://www.netpedia.net/hosting/newplayer/} -@example -$dbh = DBI->connect("DBI:mysql:$database" - . ";mysql_read_default_file=$ENV@{HOME@}/.my.cnf" - . ";mysql_read_default_group=perl", - $user, $password); -@end example -@findex DBI->disconnect -@findex disconnect DBI method -@item disconnect -The @code{disconnect} method disconnects the database handle from the database. -This is typically called right before you exit from the program. -Example: -@example -$rc = $dbh->disconnect; -@end example -@findex DBI->prepare() -@findex prepare() DBI method -@item prepare($statement) -Prepares a SQL statement for execution by the database engine -and returns a statement handle @code{($sth)}, which you can use to invoke -the @code{execute} method. -Typically you handle @code{SELECT} statements (and @code{SELECT}-like statements -such as @code{SHOW}, @code{DESCRIBE}, and @code{EXPLAIN}) by means of -@code{prepare} and @code{execute}. -Example: -@example -$sth = $dbh->prepare($statement) - or die "Can't prepare $statement: $dbh->errstr\n"; -@end example -@findex DBI->execute -@findex execute DBI method -@item execute -The @code{execute} method executes a prepared statement. For -non-@code{SELECT} statements, @code{execute} returns the number of rows -affected. If no rows are affected, @code{execute} returns @code{"0E0"}, -which Perl treats as zero but regards as true. If an error occurs, -@code{execute} returns @code{undef}. For @code{SELECT} statements, -@code{execute} only starts the SQL query in the database; you need to use one -of the @code{fetch_*} methods described below to retrieve the data. -Example: -@example -$rv = $sth->execute - or die "can't execute the query: $sth->errstr; -@end example +@node Extending MySQL, Problems, Clients, Top +@chapter Extending MySQL -@findex DBI->do() -@findex do() DBI method -@item do($statement) -The @code{do} method prepares and executes a SQL statement and returns the -number of rows affected. If no rows are affected, @code{do} returns -@code{"0E0"}, which Perl treats as zero but regards as true. This method is -generally used for non-@code{SELECT} statements that cannot be prepared in -advance (due to driver limitations) or that do not need to be executed more -than once (inserts, deletes, etc.). Example: -@example -$rv = $dbh->do($statement) - or die "Can't execute $statement: $dbh- >errstr\n"; -@end example +@menu +* Adding functions:: +* Adding procedures:: +* MySQL internals:: +@end menu -Generally the 'do' statement is MUCH faster (and is preferable) -than prepare/execute for statements that don't contain parameters. -@findex DBI->quote() -@findex quote() DBI method -@cindex quoting strings -@cindex strings, quoting -@item quote($string) -The @code{quote} method is used to "escape" any special characters contained in -the string and to add the required outer quotation marks. -Example: -@example -$sql = $dbh->quote($string) -@end example +@node Adding functions, Adding procedures, Extending MySQL, Extending MySQL +@section Adding New Functions to MySQL + +@cindex functions, new +@cindex adding, new functions +@cindex user-defined functions, adding +@cindex UDFs, defined +@cindex functions, user-defined + +There are two ways to add new functions to @strong{MySQL}: + +@itemize @bullet +@item You can add the function through the user-definable function (UDF) +interface. User-definable functions are added and removed dynamically using +the @code{CREATE FUNCTION} and @code{DROP FUNCTION} statements. +@xref{CREATE FUNCTION, , @code{CREATE FUNCTION}}. + +@item You can add the function as a native (built in) @strong{MySQL} function. +Native functions are compiled into the @code{mysqld} server and become +available on a permanent basis. +@end itemize + +Each method has advantages and disadvantages: + +@itemize @bullet +@item +If you write a user-definable function, you must install the object file +in addition to the server itself. If you compile your function into the +server, you don't need to do that. +@item +You can add UDFs to a binary @strong{MySQL} distribution. Native functions +require you to modify a source distribution. +@item +If you upgrade your @strong{MySQL} distribution, you can continue to use your +previously installed UDFs. For native functions, you must repeat your +modifications each time you upgrade. +@end itemize + +Whichever method you use to add new functions, they may be used just like +native functions such as @code{ABS()} or @code{SOUNDEX()}. + +@menu +* CREATE FUNCTION:: +* Adding UDF:: Adding a new user-definable function +* Adding native function:: Adding a new native function +@end menu + + +@node CREATE FUNCTION, Adding UDF, Adding functions, Adding functions +@subsection @code{CREATE FUNCTION/DROP FUNCTION} Syntax + +@findex CREATE FUNCTION +@findex DROP FUNCTION +@findex UDF functions +@findex User-defined functions +@findex Functions, user-defined -@findex DBI->fetchrow_array -@findex fetchrow_array DBI method -@item fetchrow_array -This method fetches the next row of data and returns it as an array of -field values. Example: @example -while(@@row = $sth->fetchrow_array) @{ - print qw($row[0]\t$row[1]\t$row[2]\n); -@} +CREATE [AGGREGATE] FUNCTION function_name RETURNS @{STRING|REAL|INTEGER@} + SONAME shared_library_name + +DROP FUNCTION function_name @end example -@findex DBI->fetchrow_arrayref -@findex fetchrow_arrayref DBI method -@item fetchrow_arrayref -This method fetches the next row of data and returns it as a reference -to an array of field values. Example: +A user-definable function (UDF) is a way to extend @strong{MySQL} with a new +function that works like native (built in) @strong{MySQL} functions such as +@code{ABS()} and @code{CONCAT()}. + +@code{AGGREGATE} is a new option for @strong{MySQL} Version 3.23. An +@code{AGGREGATE} function works exactly like a native @strong{MySQL} +@code{GROUP} function like @code{SUM} or @code{COUNT()}. + +@code{CREATE FUNCTION} saves the function's name, type, and shared library +name in the @code{mysql.func} system table. You must have the +@strong{insert} and @strong{delete} privileges for the @code{mysql} database +to create and drop functions. + +All active functions are reloaded each time the server starts, unless +you start @code{mysqld} with the @code{--skip-grant-tables} option. In +this case, UDF initialization is skipped and UDFs are unavailable. +(An active function is one that has been loaded with @code{CREATE FUNCTION} +and not removed with @code{DROP FUNCTION}.) + +For instructions on writing user-definable functions, see @ref{Adding +functions}. For the UDF mechanism to work, functions must be written in C or +C++, your operating system must support dynamic loading and you must have +compiled @code{mysqld} dynamically (not statically). + + + +@node Adding UDF, Adding native function, CREATE FUNCTION, Adding functions +@subsection Adding a New User-definable Function + +@cindex adding, user-definable functions +@cindex user-defined functions, adding +@cindex functions, user-definable, adding + +@menu +* UDF calling sequences:: UDF calling sequences +* UDF arguments:: Argument processing +* UDF return values:: Return values and error handling +* UDF compiling:: Compiling and installing user-definable functions +@end menu + + +For the UDF mechanism to work, functions must be written in C or C++ and your +operating system must support dynamic loading. The @strong{MySQL} source +distribution includes a file @file{sql/udf_example.cc} that defines 5 new +functions. Consult this file to see how UDF calling conventions work. + +For @code{mysqld} to be able to use UDF functions, you should configure MySQL +with @code{--with-mysqld-ldflags=-rdynamic} The reason is that to on +many platforms (including Linux) you can load a dynamic library (with +@code{dlopen()}) from a static linked program, which you would get if +you are using @code{--with-mysqld-ldflags=-all-static} If you want to +use an UDF that needs to access symbols from @code{mysqld} (like the +@code{methaphone} example in @file{sql/udf_example.cc} that uses +@code{default_charset_info}), you must link the program with +@code{-rdynamic}. (see @code{man dlopen}). + +For each function that you want to use in SQL statements, you should define +corresponding C (or C++) functions. In the discussion below, the name +``xxx'' is used for an example function name. To distinquish between SQL and +C/C++ usage, @code{XXX()} (uppercase) indicates a SQL function call, and +@code{xxx()} (lowercase) indicates a C/C++ function call. + +The C/C++ functions that you write to implement the interface for +@code{XXX()} are: + +@table @asis +@item @code{xxx()} (required) +The main function. This is where the function result is computed. +The correspondence between the SQL type and return type of your C/C++ +function is shown below: + +@multitable @columnfractions .2 .8 +@item @strong{SQL type} @tab @strong{C/C++ type} +@item @code{STRING} @tab @code{char *} +@item @code{INTEGER} @tab @code{long long} +@item @code{REAL} @tab @code{double} +@end multitable + +@item @code{xxx_init()} (optional) +The initialization function for @code{xxx()}. It can be used to: + +@itemize @bullet +@item +Check the number of arguments to @code{XXX()}. +@item +Check that the arguments are of a required type or, alternatively, +tell @strong{MySQL} to coerce arguments to the types you want when +the main function is called. +@item +Allocate any memory required by the main function. +@item +Specify the maximum length of the result. +@item +Specify (for @code{REAL} functions) the maximum number of decimals. +@item +Specify whether or not the result can be @code{NULL}. +@end itemize + +@item @code{xxx_deinit()} (optional) +The deinitialization function for @code{xxx()}. It should deallocate any +memory allocated by the initialization function. +@end table + +When a SQL statement invokes @code{XXX()}, @strong{MySQL} calls the +initialization function @code{xxx_init()} to let it perform any required +setup, such as argument checking or memory allocation. If @code{xxx_init()} +returns an error, the SQL statement is aborted with an error message and the +main and deinitialization functions are not called. Otherwise, the main +function @code{xxx()} is called once for each row. After all rows have been +processed, the deinitialization function @code{xxx_deinit()} is called so it +can perform any required cleanup. + +All functions must be thread safe (not just the main function, +but the initialization and deinitialization functions as well). This means +that you are not allowed to allocate any global or static variables that +change! If you need memory, you should allocate it in @code{xxx_init()} +and free it in @code{xxx_deinit()}. + + +@node UDF calling sequences, UDF arguments, Adding UDF, Adding UDF +@subsubsection UDF Calling Sequences + +@cindex calling sequences, UDF + +The main function should be declared as shown below. Note that the return +type and parameters differ, depending on whether you will declare the SQL +function @code{XXX()} to return @code{STRING}, @code{INTEGER}, or @code{REAL} +in the @code{CREATE FUNCTION} statement: + +@noindent +For @code{STRING} functions: + @example -while($row_ref = $sth->fetchrow_arrayref) @{ - print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n); -@} +char *xxx(UDF_INIT *initid, UDF_ARGS *args, + char *result, unsigned long *length, + char *is_null, char *error); @end example -@findex DBI->fetchrow_hashref -@findex fetchrow_hashref DBI method -@item fetchrow_hashref -This method fetches a row of data and returns a reference to a hash -table containing field name/value pairs. This method is not nearly as -efficient as using array references as demonstrated above. Example: +@noindent +For @code{INTEGER} functions: + @example -while($hash_ref = $sth->fetchrow_hashref) @{ - print qw($hash_ref->@{firstname@}\t$hash_ref->@{lastname@}\t\ - $hash_ref- > title@}\n); +long long xxx(UDF_INIT *initid, UDF_ARGS *args, + char *is_null, char *error); +@end example + +@noindent +For @code{REAL} functions: + +@example +double xxx(UDF_INIT *initid, UDF_ARGS *args, + char *is_null, char *error); +@end example + +The initialization and deinitialization functions are declared like this: + +@example +my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message); + +void xxx_deinit(UDF_INIT *initid); +@end example + +The @code{initid} parameter is passed to all three functions. It points to a +@code{UDF_INIT} structure that is used to communicate information between +functions. The @code{UDF_INIT} structure members are listed below. The +initialization function should fill in any members that it wishes to change. +(To use the default for a member, leave it unchanged.): + +@table @code +@item my_bool maybe_null +@code{xxx_init()} should set @code{maybe_null} to @code{1} if @code{xxx()} +can return @code{NULL}. The default value is @code{1} if any of the +arguments are declared @code{maybe_null}. + +@item unsigned int decimals +Number of decimals. The default value is the maximum number of decimals in +the arguments passed to the main function. (For example, if the function is +passed @code{1.34}, @code{1.345}, and @code{1.3}, the default would be 3, +because @code{1.345} has 3 decimals. + +@item unsigned int max_length +The maximum length of the string result. The default value differs depending +on the result type of the function. For string functions, the default is the +length of the longest argument. For integer functions, the default is 21 +digits. For real functions, the default is 13 plus the number of decimals +indicated by @code{initid->decimals}. (For numeric functions, the length +includes any sign or decimal point characters.) + +@item char *ptr +A pointer that the function can use for its own purposes. For example, +functions can use @code{initid->ptr} to communicate allocated memory +between functions. In @code{xxx_init()}, allocate the memory and assign it +to this pointer: + +@example +initid->ptr = allocated_memory; +@end example + +In @code{xxx()} and @code{xxx_deinit()}, refer to @code{initid->ptr} to use +or deallocate the memory. +@end table + + +@node UDF arguments, UDF return values, UDF calling sequences, Adding UDF +@subsubsection Argument Processing + +@cindex argument processing +@cindex processing, arguments + +The @code{args} parameter points to a @code{UDF_ARGS} structure that thas the +members listed below: + +@table @code +@item unsigned int arg_count +The number of arguments. Check this value in the initialization function +if you want your function to be called with a particular number of arguments. +For example: + +@example +if (args->arg_count != 2) +@{ + strcpy(message,"XXX() requires two arguments"); + return 1; @} @end example -@findex DBI->fetchall_arrayref -@findex fetchall_arrayref DBI method -@item fetchall_arrayref -This method is used to get all the data (rows) to be returned from the -SQL statement. It returns a reference to an array of references to arrays -for each row. You access or print the data by using a nested -loop. Example: + +@item enum Item_result *arg_type +The types for each argument. The possible type values are +@code{STRING_RESULT}, @code{INT_RESULT}, and @code{REAL_RESULT}. + +To make sure that arguments are of a given type and return an +error if they are not, check the @code{arg_type} array in the initialization +function. For example: + @example -my $table = $sth->fetchall_arrayref - or die "$sth->errstr\n"; -my($i, $j); -for $i ( 0 .. $#@{$table@} ) @{ - for $j ( 0 .. $#@{$table->[$i]@} ) @{ - print "$table->[$i][$j]\t"; - @} - print "\n"; +if (args->arg_type[0] != STRING_RESULT || + args->arg_type[1] != INT_RESULT) +@{ + strcpy(message,"XXX() requires a string and an integer"); + return 1; @} @end example -@findex DBI->finish -@findex finish DBI method -@item finish -Indicates that no more data will be fetched from this statement -handle. You call this method to free up the statement handle and any -system resources associated with it. Example: -@example -$rc = $sth->finish; -@end example +As an alternative to requiring your function's arguments to be of particular +types, you can use the initialization function to set the @code{arg_type} +elements to the types you want. This causes @strong{MySQL} to coerce +arguments to those types for each call to @code{xxx()}. For example, to +specify coercion of the first two arguments to string and integer, do this in +@code{xxx_init()}: + +@example +args->arg_type[0] = STRING_RESULT; +args->arg_type[1] = INT_RESULT; +@end example + +@item char **args +@code{args->args} communicates information to the initialization function +about the general nature of the arguments your function was called with. For a +constant argument @code{i}, @code{args->args[i]} points to the argument +value. (See below for instructions on how to access the value properly.) +For a non-constant argument, @code{args->args[i]} is @code{0}. +A constant argument is an expression that uses only constants, such as +@code{3} or @code{4*7-2} or @code{SIN(3.14)}. A non-constant argument is an +expression that refers to values that may change from row to row, such as +column names or functions that are called with non-constant arguments. + +For each invocation of the main function, @code{args->args} contains the +actual arguments that are passed for the row currently being processed. + +Functions can refer to an argument @code{i} as follows: + +@itemize @bullet +@item +An argument of type @code{STRING_RESULT} is given as a string pointer plus a +length, to allow handling of binary data or data of arbitrary length. The +string contents are available as @code{args->args[i]} and the string length +is @code{args->lengths[i]}. You should not assume that strings are +null-terminated. + +@item +For an argument of type @code{INT_RESULT}, you must cast +@code{args->args[i]} to a @code{long long} value: + +@example +long long int_val; +int_val = *((long long*) args->args[i]); +@end example + +@item +For an argument of type @code{REAL_RESULT}, you must cast +@code{args->args[i]} to a @code{double} value: + +@example +double real_val; +real_val = *((double*) args->args[i]); +@end example +@end itemize + +@item unsigned long *lengths +For the initialization function, the @code{lengths} array indicates the +maximum string length for each argument. For each invocation of the main +function, @code{lengths} contains the actual lengths of any string arguments +that are passed for the row currently being processed. For arguments of +types @code{INT_RESULT} or @code{REAL_RESULT}, @code{lengths} still contains +the maximum length of the argument (as for the initialization function). +@end table + + +@node UDF return values, UDF compiling, UDF arguments, Adding UDF +@subsubsection Return Values and Error Handling + +@cindex UDFs, return values +@cindex return values, UDFs +@cindex errors, handling for UDFs +@cindex handling, errors + +The initialization function should return @code{0} if no error occurred and +@code{1} otherwise. If an error occurs, @code{xxx_init()} should store a +null-terminated error message in the @code{message} parameter. The message +will be returned to the client. The message buffer is +@code{MYSQL_ERRMSG_SIZE} characters long, but you should try to keep the +message to less than 80 characters so that it fits the width of a standard +terminal screen. + +The return value of the main function @code{xxx()} is the function value, for +@code{long long} and @code{double} functions. A string functions should +return a pointer to the result and store the length of the string in the +@code{length} arguments. @code{result} is a buffer at least 255 bytes long. +Set these to the contents and length of the return value. For example: + +@example +memcpy(result, "result string", 13); +*length = 13; +@end example + +If your string functions that needs to return a string longer than 255 +bytes, you must allocate the space for it with @code{malloc()} in your +@code{xxx_init()} function or your @code{xxx()} function and free it in +your @code{xxx_deinit()} function. You can store the allocated memory +in the @code{ptr} slot in the @code{UDF_INIT} structure for reuse by +future @code{xxx()} calls. @xref{UDF calling sequences}. + +To indicate a return value of @code{NULL} in the main function, set +@code{is_null} to @code{1}: + +@example +*is_null = 1; +@end example + +To indicate an error return in the main function, set the @code{error} +parameter to @code{1}: + +@example +*error = 1; +@end example + +If @code{xxx()} sets @code{*error} to @code{1} for any row, the function +value is @code{NULL} for the current row and for any subsequent rows +processed by the statement in which @code{XXX()} was invoked. (@code{xxx()} +will not even be called for subsequent rows.) @strong{NOTE:} In +@strong{MySQL} versions prior to 3.22.10, you should set both @code{*error} +and @code{*is_null}: + +@example +*error = 1; +*is_null = 1; +@end example + + +@node UDF compiling, , UDF return values, Adding UDF +@subsubsection Compiling and Installing User-definable Functions + +@cindex compiling, user-defined functions +@cindex UDFs, compiling +@cindex installing, user-defined functions + +Files implementing UDFs must be compiled and installed on the host where the +server runs. This process is described below for the example UDF file +@file{udf_example.cc} that is included in the @strong{MySQL} source +distribution. This file contains the following functions: + +@itemize @bullet +@item +@code{metaphon()} returns a metaphon string of the string argument. +This is something like a soundex string, but it's more tuned for English. +@item +@code{myfunc_double()} returns the sum of the ASCII values of the +characters in its arguments, divided by the sum of the length of its arguments. +@item +@code{myfunc_int()} returns the sum of the length of its arguments. +@item +@code{sequence([const int])} returns an sequence starting from the given +number or 1 if no number has been given. +@item +@code{lookup()} returns the IP number for a hostname. +@item +@code{reverse_lookup()} returns the hostname for an IP number. +The function may be called with a string @code{"xxx.xxx.xxx.xxx"} or +four numbers. +@end itemize + +A dynamically loadable file should be compiled as a sharable object file, +using a command something like this: + +@example +shell> gcc -shared -o udf_example.so myfunc.cc +@end example + +You can easily find out the correct compiler options for your system by +running this command in the @file{sql} directory of your @strong{MySQL} +source tree: + +@example +shell> make udf_example.o +@end example + +You should run a compile command similar to the one that @code{make} displays, +except that you should remove the @code{-c} option near the end of the line +and add @code{-o udf_example.so} to the end of the line. (On some systems, +you may need to leave the @code{-c} on the command.) + +Once you compile a shared object containing UDFs, you must install it +and tell @strong{MySQL} about it. Compiling a shared object from +@file{udf_example.cc} produces a file named something like +@file{udf_example.so} (the exact name may vary from platform to platform). +Copy this file to some directory searched by @code{ld}, such as +@file{/usr/lib}. On many systems, you can set the @code{LD_LIBRARY} or +@code{LD_LIBRARY_PATH} environment variable to point at the directory where +you have your UDF function files. The @code{dlopen} manual page tells you +which variable you should use on your system. You should set this in +@code{mysql.server} or @code{safe_mysqld} and restart @code{mysqld}. + +After the library is installed, notify @code{mysqld} about the new +functions with these commands: + +@example +mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so"; +mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so"; +mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so"; +mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so"; +mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so"; +@end example + +Functions can be deleted using @code{DROP FUNCTION}: + +@example +mysql> DROP FUNCTION metaphon; +mysql> DROP FUNCTION myfunc_double; +mysql> DROP FUNCTION myfunc_int; +mysql> DROP FUNCTION lookup; +mysql> DROP FUNCTION reverse_lookup; +@end example + +The @code{CREATE FUNCTION} and @code{DROP FUNCTION} statements update the +system table @code{func} in the @code{mysql} database. The function's name, +type and shared library name are saved in the table. You must have the +@strong{insert} and @strong{delete} privileges for the @code{mysql} database +to create and drop functions. + +You should not use @code{CREATE FUNCTION} to add a function that has already +been created. If you need to reinstall a function, you should remove it with +@code{DROP FUNCTION} and then reinstall it with @code{CREATE FUNCTION}. You +would need to do this, for example, if you recompile a new version of your +function, so that @code{mysqld} gets the new version. Otherwise the server +will continue to use the old version. + +Active functions are reloaded each time the server starts, unless you start +@code{mysqld} with the @code{--skip-grant-tables} option. In this case, UDF +initialization is skipped and UDFs are unavailable. (An active function is +one that has been loaded with @code{CREATE FUNCTION} and not removed with +@code{DROP FUNCTION}.) + + +@node Adding native function, , Adding UDF, Adding functions +@subsection Adding a New Native Function + +@cindex adding, native functions +@cindex native functions, adding +@cindex functions, native, adding + +The procedure for adding a new native function is described below. Note +that you cannot add native functions to a binary distribution because +the procedure involves modifying @strong{MySQL} source code. You must +compile @strong{MySQL} yourself from a source distribution. Also note +that if you migrate to another version of @strong{MySQL} (for example, +when a new version is released), you will need to repeat the procedure +with the new version. + +To add a new native @strong{MySQL} function, follow these steps: + +@enumerate +@item +Add one line to @file{lex.h} that defines the function name in the +@code{sql_functions[]} array. +@item +If the function prototype is simple (just takes zero, one, two or three +arguments), you should in lex.h specify SYM(FUNC_ARG#) (where # is the +number of arguments) as the second argument in the +@code{sql_functions[]} array and add a function that creates a function +object in @file{item_create.cc}. Take a look at @code{"ABS"} and +@code{create_funcs_abs()} for an example of this. + +If the function prototype is complicated (for example takes a variable number +of arguments), you should add two lines to @file{sql_yacc.yy}. One +indicates the preprocessor symbol that @code{yacc} should define (this +should be added at the beginning of the file). Then define the function +parameters and add an ``item'' with these parameters to the +@code{simple_expr} parsing rule. For an example, check all occurrences +of @code{ATAN} in @file{sql_yacc.yy} to see how this is done. +@item +In @file{item_func.h}, declare a class inheriting from @code{Item_num_func} or +@code{Item_str_func}, depending on whether your function returns a number or a +string. +@item +In @file{item_func.cc}, add one of the following declarations, depending +on whether you are defining a numeric or string function: +@example +double Item_func_newname::val() +longlong Item_func_newname::val_int() +String *Item_func_newname::Str(String *str) +@end example + +If you inherit your object from any of the standard items (like +@code{Item_num_func} you probably only have to define one of the above +functions and let the parent object take care of the other functions. +For example, the @code{Item_str_func} class defines a @code{val()} function +that executes @code{atof()} on the value returned by @code{::str()}. + +@item +You should probably also define the following object function: +@example +void Item_func_newname::fix_length_and_dec() +@end example +This function should at least calculate @code{max_length} based on the +given arguments. @code{max_length} is the maximum number of characters +the function may return. This function should also set @code{maybe_null += 0} if the main function can't return a @code{NULL} value. The +function can check if any of the function arguments can return +@code{NULL} by checking the arguments @code{maybe_null} variable. You +can take a look at @code{Item_func_mod::fix_length_and_dec} for a +typical example of how to do this. +@end enumerate + +All functions must be thread safe (In other words, don't use any global or +static variables in the functions without protecting them with mutexes). + +If you want to return @code{NULL}, from @code{::val()}, @code{::val_int()} +or @code{::str()} you should set @code{null_value} to 1 and return 0. + +For @code{::str()} object functions, there are some additional +considerations to be aware of: + +@itemize @bullet +@item +The @code{String *str} argument provides a string buffer that may be +used to hold the result. (For more information about the @code{String} type, +take a look at the @file{sql_string.h} file.) +@item +The @code{::str()} function should return the string that holds the result or +@code{(char*) 0} if the result is @code{NULL}. +@item +All current string functions try to avoid allocating any memory unless +absolutely necessary! +@end itemize + + +@node Adding procedures, MySQL internals, Adding functions, Extending MySQL +@section Adding New Procedures to MySQL + +@cindex procedures, adding +@cindex adding, procedures +@cindex new procedures, adding + +In @strong{MySQL}, you can define a procedure in C++ that can access and +modify the data in a query before it is sent to the client. The modification +can be done on row-by-row or @code{GROUP BY} level. + +We have created an example procedure in @strong{MySQL} Version 3.23 to +show you what can be done. + +Additionally we recommend you to take a look at 'mylua', which you can find in the Contrib directory. @xref{Contrib}. Which this you can use the LUA +language to load a procedure at runtime into @code{mysqld}. + +@menu +* procedure analyse:: Procedure analyse +* Writing a procedure:: Writing a procedure. +@end menu + + +@node procedure analyse, Writing a procedure, Adding procedures, Adding procedures +@subsection Procedure Analyse + +@code{analyse([max elements,[max memory]])} + +This procedure is defined in the @file{sql/sql_analyse.cc}. This +examines the result from your query and returns an analysis of the +results: + +@itemize @bullet +@item +@code{max elements} (default 256) is the maximum number of distinct values +@code{analyse} will notice per column. This is used by @code{analyse} to check if +the optimal column type should be of type @code{ENUM}. +@item +@code{max memory} (default 8192) is the maximum memory @code{analyse} should +allocate per column while trying to find all distinct values. +@end itemize + +@example +SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]]) +@end example + + +@node Writing a procedure, , procedure analyse, Adding procedures +@subsection Writing a Procedure + +For the moment, the only documentation for this is the source. + +You can find all information about procedures by examining the following files: + +@itemize @bullet +@item @file{sql/sql_analyse.cc} +@item @file{sql/procedure.h} +@item @file{sql/procedure.cc} +@item @file{sql/sql_select.cc} +@end itemize + + +@node MySQL internals, , Adding procedures, Extending MySQL +@section MySQL Internals + +@cindex internals +@cindex threads + +This chapter describes a lot of things that you need to know when +working on the @strong{MySQL} code. If you plan to contribute to MySQL +development, want to have access to the bleeding-edge in-between +versions code, or just want to keep track of development, follow the +instructions in @xref{Installing source tree}. If you are interested in MySQL +internals, you should also subscribe to @email{internals@@lists.mysql.com}. +This is a relatively low traffic list, in comparison with +@email{mysql@@lists.mysql.com}. + +@menu +* MySQL threads:: MySQL threads +* MySQL test suite:: MySQL test suite +@end menu -@findex DBI->rows -@findex rows DBI method -@item rows -Returns the number of rows changed (updated, deleted, etc.) by the last -command. This is usually used after a non-@code{SELECT} @code{execute} -statement. Example: -@example -$rv = $sth->rows; -@end example -@findex DBI->@{NULLABLE@} -@findex NULLABLE DBI method -@item NULLABLE -Returns a reference to an array of boolean values; for each element of -the array, a value of TRUE indicates that this -column may contain @code{NULL} values. -Example: -@example -$null_possible = $sth->@{NULLABLE@}; -@end example +@node MySQL threads, MySQL test suite, MySQL internals, MySQL internals +@subsection MySQL Threads -@findex DBI->@{NUM_OF_FIELDS@} -@findex NUM_OF_FIELDS DBI method -@item NUM_OF_FIELDS -This attribute indicates -the number of fields returned by a @code{SELECT} or @code{SHOW FIELDS} -statement. You may use this for checking whether a statement returned a -result: A zero value indicates a non-@code{SELECT} statement like -@code{INSERT}, @code{DELETE}, or @code{UPDATE}. -Example: -@example -$nr_of_fields = $sth->@{NUM_OF_FIELDS@}; -@end example +The @strong{MySQL} server creates the following threads: -@findex DBI->data_sources() -@findex data_sources() DBI method -@item data_sources($driver_name) -This method returns an array containing names of databases available to the -@strong{MySQL} server on the host @code{'localhost'}. -Example: -@example -@@dbs = DBI->data_sources("mysql"); -@end example +@itemize @bullet -@findex DBI->@{ChopBlanks@} -@findex ChopBlanks DBI method -@item ChopBlanks -This attribute determines whether the @code{fetchrow_*} methods will chop -leading and trailing blanks from the returned values. -Example: -@example -$sth->@{'ChopBlanks'@} =1; -@end example +@item +The TCP/IP connection thread handles all connection requests and +creates a new dedicated thread to handle the authentication and +and SQL query processing for each connection. -@findex DBI->trace -@findex trace DBI method -@item trace($trace_level) -@itemx trace($trace_level, $trace_filename) -The @code{trace} method enables or disables tracing. When invoked as a -@code{DBI} class method, it affects tracing for all handles. When invoked as -a database or statement handle method, it affects tracing for the given -handle (and any future children of the handle). Setting @code{$trace_level} -to 2 provides detailed trace information. Setting @code{$trace_level} to 0 -disables tracing. Trace output goes to the standard error output by -default. If @code{$trace_filename} is specified, the file is opened in -append mode and output for @emph{all} traced handles is written to that -file. Example: -@example -DBI->trace(2); # trace everything -DBI->trace(2,"/tmp/dbi.out"); # trace everything to - # /tmp/dbi.out -$dth->trace(2); # trace this database handle -$sth->trace(2); # trace this statement handle -@end example +@item +On Windows NT there is a named pipe handler thread that does the same work as +the TCP/IP connection thread on named pipe connect requests. -@tindex DBI_TRACE environment variable -@tindex Environment variable, DBI_TRACE -You can also enable @code{DBI} tracing by setting the @code{DBI_TRACE} -environment variable. Setting it to a numeric value is equivalent to calling -@code{DBI->(value)}. Setting it to a pathname is equivalent to calling -@code{DBI->(2,value)}. +@item +The signal thread handles all signals. This thread also normally handles +alarms and calls @code{process_alarm()} to force timeouts on connections +that have been idle too long. -@end table +@item +If @code{mysqld} is compiled with @code{-DUSE_ALARM_THREAD}, a dedicated +thread that handles alarms is created. This is only used on some systems where +there are problems with @code{sigwait()} or if one wants to use the +@code{thr_alarm()} code in ones application without a dedicated signal +handling thread. -@noindent -@strong{MySQL-specific Methods} +@item +If one uses the @code{--flush_time=#} option, a dedicated thread is created +to flush all tables at the given interval. -The methods shown below are @strong{MySQL}-specific and not part of the -@code{DBI} standard. Several of them are now deprecated: -@code{is_blob}, @code{is_key}, @code{is_num}, @code{is_pri_key}, -@code{is_not_null}, @code{length}, @code{max_length}, and @code{table}. -Where @code{DBI}-standard alternatives exist, they are noted below: +@item +Every connection has its own thread. -@table @code -@findex DBI->@{insertid@} -@findex insertid DBI method -@tindex AUTO_INCREMENT, using with DBI -@item insertid -If you use the @code{AUTO_INCREMENT} feature of @strong{MySQL}, the new -auto-incremented values will be stored here. -Example: -@example -$new_id = $sth->@{insertid@}; -@end example +@item +Every different table on which one uses @code{INSERT DELAYED} gets its +own thread. -As an alternative, you can use @code{$dbh->@{'mysql_insertid'@}}. +@item +If you use @code{--master-host}, a slave replication thread will be +started to read and apply updates from the master. +@end itemize -@findex DBI->@{is_blob@} -@findex is_blob DBI method -@item is_blob -Returns a reference to an array of boolean values; for each element of the -array, a value of TRUE indicates that the -respective column is a @code{BLOB}. -Example: -@example -$keys = $sth->@{is_blob@}; -@end example +@code{mysqladmin processlist} only shows the connection, @code{INSERT DELAYED}, +and replication threads. -@findex DBI->@{is_key@} -@findex is_key DBI method -@item is_key -Returns a reference to an array of boolean values; for each element of the -array, a value of TRUE indicates that the -respective column is a key. -Example: -@example -$keys = $sth->@{is_key@}; -@end example -@findex DBI->@{is_num@} -@findex is_num DBI method -@item is_num -Returns a reference to an array of boolean values; for each element of the -array, a value of TRUE indicates that the -respective column contains numeric values. -Example: -@example -$nums = $sth->@{is_num@}; -@end example +@node MySQL test suite, , MySQL threads, MySQL internals +@subsection MySQL Test Suite -@findex DBI->@{is_pri_key@} -@findex is_pri_key DBI method -@item is_pri_key -Returns a reference to an array of boolean values; for each element of the -array, a value of TRUE indicates that the respective column is a primary key. -Example: -@example -$pri_keys = $sth->@{is_pri_key@}; -@end example +@cindex mysqltest, MySQL Test Suite +@cindex testing mysqld, mysqltest -@findex DBI->@{is_not_null@} -@findex is_not_null DBI method -@item is_not_null -Returns a reference to an array of boolean values; for each element of the -array, a value of FALSE indicates that this column may contain @code{NULL} -values. -Example: -@example -$not_nulls = $sth->@{is_not_null@}; -@end example +Until recently, our main full-coverage test suite was based on proprietary +customer data and for that reason has not been publicly available. The only +publicly available part of our testing process consisted of the @code{crash-me} +test, a Perl DBI/DBD benchmark found in the @code{sql-bench} directory, and +miscellaneous tests located in @code{tests} directory. The lack of a +standardized publicly available test suite has made it difficult for our users, +as well developers, to do regression tests on the @strong{MySQL} code. To +address this problem, we have created a new test system that is included in +the source and binary distributions starting in Version 3.23.29. -@code{is_not_null} is deprecated; it is preferable to use the -@code{NULLABLE} attribute (described above), because that is a DBI standard. +The current set of test cases doesn't test everything in @strong{MySQL}, but it +should catch most obvious bugs in the SQL processing code, OS/library +issues, and is quite thorough in testing replication. Our eventual goal +is to have the tests cover 100% of the code. We welcome contributions +to our test suite. You may especially want to contribute tests that +examine the functionality critical to your system, as this will ensure +that all future @strong{MySQL} releases will work well with your +applications. -@findex DBI->@{length@} -@findex length DBI method -@findex DBI->@{max_length@} -@findex max_length DBI method -@item length -@itemx max_length -Each of these methods returns a reference to an array of column sizes. The -@code{length} array indicates the maximum possible sizes that each column may -be (as declared in the table description). The @code{max_length} array -indicates the maximum sizes actually present in the result table. Example: +@menu +* running mysqltest:: +* extending mysqltest:: +* Reporting mysqltest bugs:: +@end menu -@example -$lengths = $sth->@{length@}; -$max_lengths = $sth->@{max_length@}; -@end example -@findex DBI->@{NAME@} -@findex NAME DBI method -@item NAME -Returns a reference to an array of column names. -Example: -@example -$names = $sth->@{NAME@}; -@end example +@node running mysqltest, extending mysqltest, MySQL test suite, MySQL test suite +@subsubsection Running the MySQL Test Suite -@findex DBI->@{table@} -@findex table DBI method -@item table -Returns a reference to an array of table names. -Example: -@example -$tables = $sth->@{table@}; -@end example +The test system consist of a test language interpreter +(@code{mysqltest}), a shell script to run all +tests(@code{mysql-test-run}), the actual test cases written in a special +test language, and their expected results. To run the test suite on +your system after a build, type @code{make test} or +@code{mysql-test/mysql-test-run} from the source root. If you have +installed a binary distribution, @code{cd} to the install root +(eg. @code{/usr/local/mysql}), and do @code{scripts/mysql-test-run}. +All tests should succeed. If not, you should try to find out why and +report the problem if this is a bug in @strong{MySQL}. +@xref{Reporting mysqltest bugs}. -@findex DBI->@{type@} -@findex type DBI method -@item type -Returns a reference to an array of column types. -Example: -@example -$types = $sth->@{type@}; -@end example +If you have a copy of @code{mysqld} running on the machine where you want to +run the test suite you do not have to stop it, as long as it is not using +ports @code{9306} and @code{9307}. If one of those ports is taken, you should +edit @code{mysql-test-run} and change the values of the master and/or slave +port to one that is available. -@end table +You can run one individual test case with +@code{mysql-test/mysql-test-run test_name}. -@cindex @code{DBI/DBD} -@node DBI-info, , Perl DBI Class, Perl -@subsection More @code{DBI}/@code{DBD} Information +If one test fails, you should test running @code{mysql-test-run} with +the @code{--force} option to check if any other tests fails. -You can use the @code{perldoc} command to get more information about -@code{DBI}. -@example -perldoc DBI -perldoc DBI::FAQ -perldoc DBD::mysql -@end example +@node extending mysqltest, Reporting mysqltest bugs, running mysqltest, MySQL test suite +@subsubsection Extending the MySQL Test Suite -You can also use the @code{pod2man}, @code{pod2html}, etc., tools to -translate to other formats. +You can use the @code{mysqltest} language to write your own test cases. +Unfortunately, we have not yet written full documentation for it - we plan to +do this shortly. You can, however, look at our current test cases and use +them as an example. The following points should help you get started: -You can find the latest @code{DBI} information at -the @code{DBI} Web page: -@example -@uref{http://www.symbolstone.org/technology/perl/DBI/index.html} -@end example +@itemize +@item +The tests are located in @code{mysql-test/t/*.test} -@cindex Eiffel Wrapper -@cindex wrappers, Eiffel -@node Eiffel, Java, Perl, Clients -@section MySQL Eiffel wrapper +@item +A test case consists of @code{;} terminated statements and is similar to the +input of @code{mysql} command line client. A statement by default is a query +to be sent to @strong{MySQL} server, unless it is recognized as internal +command ( eg. @code{sleep} ). -The @strong{MySQL} @uref{http://www.mysql.com/Downloads/Contrib/,Contrib directory} -contains an Eiffel wrapper written by Michael Ravits. +@item +All queries that produce results, e.g. @code{SELECT}, @code{SHOW}, +@code{EXPLAIN}, etc., must be preceded with @code{@@/path/to/result/file}. The +file must contain the expected results. An easy way to generate the result +file is to run @code{mysqltest -r < t/test-case-name.test} from +@code{mysql-test} directory, and then edit the generated result files, if +needed, to adjust them to the expected output. In that case, be very careful +about not adding or deleting any invisible characters - make sure to only +change the text and/or delete lines. If you have to insert a line, make sure +the fields are separated with a hard tab, and there is a hard tab at the end. +You may want to use @code{od -c} to make sure your text editor has not messed +anything up during edit. We, of course, hope that you will never have to edit +the output of @code{mysqltest -r} as you only have to do it when you find a +bug. -You can also find this at: -@url{http://www.netpedia.net/hosting/newplayer/} +@item +To be consistent with our setup, you should put your result files in +@code{mysql-test/r} directory and name them @code{test_name.result}. If the +test produces more than one result, you should use @code{test_name.a.result}, +@code{test_name.b.result}, etc. -@cindex Java connectivity -@cindex JDBC -@node Java, PHP, Eiffel, Clients -@section MySQL Java Connectivity (JDBC) +@item +If a statement returns an error, you should on the line before the statement +specify with the @code{--error error-number}. The error number can be +a list of possible error numbers separated with @code{','}. -There are 2 supported JDBC drivers for @strong{MySQL} (the mm driver and -the Reisin JDBC driver). You can find a copy of the mm driver at -@uref{http://mmmysql.sourceforge.net/} or -@uref{http://www.mysql.com/Downloads/Contrib/} and the Reisin driver at -@uref{http://www.caucho.com/projects/jdbc-mysql/index.xtp} For -documentation consult any JDBC documentation and the driver's own -documentation for @strong{MySQL}-specific features. +@item +If you are writing a replication test case, you should on the first line of +the test file, put @code{source include/master-slave.inc;}. To switch between +master and slave, use @code{connection master;} and @code{connection slave;}. +If you need to do something on an alternate connection, you can do +@code{connection master1;} for the master, and @code{connection slave1;} for +the slave. -@cindex PHP API -@node PHP, Cplusplus, Java, Clients -@section MySQL PHP API +@item +If you need to do something in a loop, you can use something like this: +@example +let $1=1000; +while ($1) +@{ + # do your queries here + dec $1; +@} +@end example -PHP is a server-side, HTML-embedded scripting language that may be used to -create dynamic Web pages. It contains support for accessing several -databases, including @strong{MySQL}. PHP may be run as a separate program -or compiled as a module for use with the Apache Web server. +@item +To sleep between queries, use the @code{sleep} command. It supports fractions +of a second, so you can do @code{sleep 1.3;}, for example, to sleep 1.3 +seconds. -The distribution and documentation are available at the -@uref{http://www.php.net/, PHP web site}. +@item +To run the slave with additional options for your test case, put them +in the command-line format in @code{mysql-test/t/test_name-slave.opt}. For +the master, put them in @code{mysql-test/t/test_name-master.opt}. -@menu -* PHP problems:: Common problems with MySQL and PHP -@end menu +@item +If you have a question about the test suite, or have a test case to contribute, +e-mail to @email{internals@@lists.mysql.com}. As the list does not accept +attachments, you should ftp all the relevant files to: +@url{ftp://support.mysql.com/pub/mysql/Incoming} + +@end itemize -@node PHP problems, , PHP, PHP -@subsection Common Problems with MySQL and PHP + +@node Reporting mysqltest bugs, , extending mysqltest, MySQL test suite +@subsubsection Reporting Bugs in the MySQL Test Suite + +If your @strong{MySQL} version doesn't pass the test suite you should +do the following: @itemize @bullet -@item Error: "Maximum Execution Time Exceeded" -This is a PHP limit; Go into the @file{php3.ini} file and set the maximum -execution time up from 30 seconds to something higher, as needed. -It is also not a bad idea to double the ram allowed per script to 16MB instead of -8 MB. -@item Error: "Fatal error: Call to unsupported or undefined function mysql_connect() in .." -This means that your PHP version isn't compiled with @strong{MySQL} support. -You can either compile a dynamic @strong{MySQL} module and load it into PHP or -recompile PHP with built-in @strong{MySQL} support. This is described in -detail in the PHP manual. -@item Error: "undefined reference to `uncompress'" -This means that the client library is compiled with support for a compressed -client/server protocol. The fix is to add @code{-lz} last when linking -with @code{-lmysqlclient}. -@end itemize +@item +Don't send a bug report before you have found out as much as possible of +what when wrong! When you do it, please use the @code{mysqlbug} script +so that we can get information about your system and @code{MySQL} +version. @xref{Bug reports}. +@item +Make sure to include the output of @code{mysql-test-run}, as well as +contents of all @code{.reject} files in @code{mysql-test/r} directory. +@item +If a test in the test suite fails, check if the test fails also when run +by its own: -@cindex C++ APIs -@node Cplusplus, Python, PHP, Clients -@section MySQL C++ APIs +@example +cd mysql-test +mysql-test-run --local test-name +@end example -Two APIs are available in the @strong{MySQL} -@uref{http://www.mysql.com/Downloads/Contrib/,Contrib directory}. +If this fails, then you should configure @strong{MySQL} with +@code{--with-debug} and run @code{mysql-test-run} with the +@code{--debug} option. If this also fails send the trace file +@file{var/tmp/master.trace} to ftp://support.mysql.com/pub/mysql/secret +so that we can examine it. Please remember to also include a full +description of your system, the version of the mysqld binary and how you +compiled it. -@cindex Python APIs -@node Python, Tcl, Cplusplus, Clients -@section MySQL Python APIs +@item +Try also to run @code{mysql-test-run} with the @code{--force} option to +see if there is any other test that fails. -The @strong{MySQL} @uref{http://www.mysql.com/Downloads/Contrib/,Contrib directory} -contains a Python interface written by Joseph Skinner. +@item +If you have compiled @strong{MySQL} yourself, check our manual for how +to compile @strong{MySQL} on your platform or, preferable, use one of +the binaries we have compiled for you at +@uref{http://www.mysql.com/downloads/}. All our standard binaries should +pass the test suite ! -You can also use the Python interface to iODBC to access a -@strong{MySQL} server. -@uref{http://starship.skyport.net/~lemburg/,mxODBC} +@item +If you get an error, like @code{Result length mismatch} or @code{Result +content mismatch} it means that the output of the test didn't match +exactly the expected output. This could be a bug in @strong{MySQL} or +that your mysqld version produces slight different results under some +circumstances. -@cindex Tcl APIs -@node Tcl, , Python, Clients -@section MySQL Tcl APIs +Failed test results are put in a file with the same base name as the +result file with the @code{.reject} extension. If your test case is +failing, you should do a diff on the two files. If you cannot see how +they are different, examine both with @code{od -c} and also check their +lengths. -@uref{http://www.binevolve.com/~tdarugar/tcl-sql/, Tcl at binevolve}. -The -@uref{http://www.mysql.com/Downloads/Contrib,Contrib directory} contains a Tcl -interface that is based on msqltcl 1.50. +@item +If a test fails totally, you should check the logs file in the +@code{mysql-test/var/log} directory for hints of what went wrong. + +@item +If you have compiled @strong{MySQL} with debugging you can try to debug this +by running @code{mysql-test-run} with the @code{--gdb} and/or @code{--debug} +options. +@xref{Making trace files}. + +If you have not compiled @strong{MySQL} for debugging you should probably +do that. Just specify the @code{--with-debug} options to @code{configure}! +@xref{Installing source}. +@end itemize -@node Problems, Environment variables, Clients, Top +@node Problems, Environment variables, Extending MySQL, Top @appendix Problems and Common Errors @cindex problems, common errors @@ -53175,12 +53317,11 @@ That's all there is to it! @menu * Installing binary:: -* Building clients:: * Perl support:: * Group by functions:: @end menu -@node Installing binary, Building clients, Placeholder, Placeholder +@node Installing binary, Perl support, Placeholder, Placeholder @appendixsec Installing a MySQL Binary Distribution @cindex installing, binary distribution @@ -53397,31 +53538,12 @@ shell> bin/safe_mysqld --user=mysql & @xref{Post-installation}. -@node Building clients, Perl support, Installing binary, Placeholder -@appendixsec Building Client Programs - -@cindex client programs, building -@cindex linking -@cindex building, client programs -@cindex programs, client - -If you compile @strong{MySQL} clients that you've written yourself or that -you obtain from a third party, they must be linked using the -@code{-lmysqlclient -lz} option on the link command. You may also need to -specify a @code{-L} option to tell the linker where to find the library. For -example, if the library is installed in @file{/usr/local/mysql/lib}, use -@code{-L/usr/local/mysql/lib -lmysqlclient -lz} on the link command. - -For clients that use @strong{MySQL} header files, you may need to specify a -@code{-I} option when you compile them (for example, -@code{-I/usr/local/mysql/include}), so the compiler can find the header -files. -@node Perl support, Group by functions, Building clients, Placeholder +@node Perl support, Group by functions, Installing binary, Placeholder @appendixsec Perl Installation Comments @cindex Perl, installing -- cgit v1.2.1