Home

MySQL++ v3.0.9 User Manual

image

Contents

1. Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 try Establish the connection to the database server mysqlpp Connection con db server user pass Get contents of main example table mysqlpp Query query con query select from stock mysqlpp StoreQueryResult res query store Show info about each field in that table char widths 12 22 46 cout setf ios left cout lt lt setw widths 0 lt lt Field lt lt setw widths 1 lt lt SQL Type lt lt setw widths 2 lt lt Equivalent C Type lt lt endl for size_t i 0 i lt sizeof widths sizeof widths 0 i cout lt lt string widths i 1 lt lt cout lt lt endl for size_t i 0 i lt res field names gt size i Suppress C type name outputs when run under dtest as they re system specific const char cname dtest_mode n a res field type i name mysqlpp FieldTypes value_type ft res field type i ostringstream os os lt lt ft sql _name lt lt lt lt ft id lt lt cout lt lt setw widths 0 lt lt res field name i c_str lt lt setw widths 1 lt lt os str lt lt setw widths 2 lt lt cname lt lt endl cout lt lt endl Simple type check if
2. e Under Configuration Properties General change Common Language Runtime support to the clr setting e Under C C Code Generation change Enable C Exceptions from Yes EHsc to Yes With SEH Excep tions EHa If you have already built MySQL be sure to perform a complete rebuild after changing these options The compiler will emit several C4835 warnings after making those changes which are harmless when using the DLL with a C CLI program but which warn of real problems when using it with unmanaged C This is why MySQL s Windows installer install hta offers the option to install the CLR version into a separate directory use it if you need both managed and unmanaged versions installed For the same reason you might give some thought about where you install mysql pp d11 on your end user s machines when distributing your program My recommendation is to install it in the same directory as the exe file that uses it rather than installing into a system directory where it could conflict with amysqlpp d11 built with different settings Once you have MySQL built with CLR support open your program s project If you don t already have a project set up open Visual Studio say File New Project then choose Visual C CLR Windows Forms Application Go through the wizard setting up the project as you see fit The configuration process isn t much different from that for an MFC project so go
3. 14 MySQL v3 0 9 User Manual mysqlpp Null lt unsigned char mysqlpp NulliIsZero gt myfield mysqlpp null cout lt lt myfield lt lt endl cout lt lt int myfield lt lt endl This will print 0 twice If you had used the default for the second Nul 1 template parameter the first output statement would have printed NULL and the second wouldn t even compile 3 8 Using Transactions The Transaction class makes it easier to use SQL transactions in an exception safe manner Normally you create the Transaction object on the stack before you issue the queries in your transaction set Then when all the queries in the transaction set have been issued you call Transaction commit which commits the transaction set If the Transaction object goes out of scope before you call commit the transaction set is rolled back This ensures that if some code throws an exception after the transaction is started but before it is committed the transaction isn t left unresolved examples transaction cpp illustrates this include cmdline h include printdata h include stock h include lt iostream gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 try Establish the connection to
4. 3 3 A More Complicated Example The simplel example above was pretty trivial Let s get a little deeper Here is examples simple2 cpp include cmdline h include printdata h include lt mysql h gt include lt iostream gt include lt iomanip gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 Connect to the sample database mysqlpp Connection conn false if conn connect db server user pass Retrieve the sample stock table set up by resetdb mysqlpp Query query conn query select from stock mysqlpp StoreQueryResult res query store Display results if res Display header 10 MySQL v3 0 9 User Manual cout setf ios left cout lt lt setw 31 lt lt Item lt lt setw 10 lt lt Num lt lt setw 10 lt lt Weight lt lt setw 10 lt lt Price lt lt Date lt lt endl lt lt endl Get each row in result set and print its contents for size_t i 0 i lt res num_rows i cout lt lt setw 30 lt lt res i item lt lt lt lt setw 9 lt lt res i num lt lt lt lt setw 9 lt lt res i weight lt lt lt lt setw 9 lt lt res i price lt lt lt lt setw 9
5. 15 BECAUSE THE LIBRARY IS LICENSED FREE OF CHARGE THERE IS NO WARRANTY FOR THE LIB RARY TO THE EXTENT PERMITTED BY APPLICABLE LAW EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND OR OTHER PARTIES PROVIDE THE LIBRARY AS IS WITHOUT WARRANTY OF ANY KIND EITHER EXPRESSED OR IMPLIED INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE LIBRARY IS WITH YOU SHOULD THE LIBRARY PROVE DEFECTIVE YOU ASSUME THE COST OF ALL NECESSARY SERVICING REPAIR OR CORRECTION 16 INNO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING WILL ANY COPYRIGHT HOLDER OR ANY OTHER PARTY WHO MAY MODIFY AND OR REDISTRIBUTE THE LIBRARY AS PERMITTED ABOVE BE LIABLE TO YOU FOR DAMAGES INCLUDING ANY GENERAL SPECIAL INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE LIBRARY INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE LIBRARY TO OPERATE WITH ANY OTHER SOFTWARE EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES END OF TERMS AND CONDITIONS How to Apply These Terms to Your New Libraries If you develop a new library and you want it to be of the greatest possible use to the public we recommend making it free software that everyone can redistribute and change Y
6. 17 MySQL v3 0 9 User Manual query lt lt select from deadlock _test lt lt lock lt lt where x lt lt lock lt lt for update query store cout lt lt Acquired lock lt lt lock lt lt Press Enter to cout lt lt i 0 try next lock exit cout lt lt lt lt flush cin getline dummy sizeof dummy catch mysqlpp BadQuery e if e errnum ER_LOCK_ DEADLOCK cerr lt lt Transaction deadlock detected lt lt endl cerr lt lt Connection errnum lt lt con errnum lt lt BadQuery errnum lt lt e errnum lt lt endl else cerr lt lt Unexpected query error lt lt e what lt lt endl return 1 catch mysqlpp Exception e cerr lt lt General error lt lt e what lt lt endl return 1 return 0 This example works a little differently than the others You run one copy of the example then when it pauses waiting for you to press Enter you run another copy Then depending on which one you press Enter in one of the two will abort with the deadlock exception You can see from the error message you get that it matters which method you call to get the error number What you do about it is up to you as it depends on your program s design and system architecture 3 9 Which Query Type to Use There are three major ways to execute a query in MySQL Query execute Query store
7. Establish the connection to the database server mysqlpp Connection con db server user pass Gather and display the stats for the entire stock table mysqlpp Query query con query std cout lt lt There are lt lt query for_each stock gather_stock_stats lt lt lt lt std endl catch const mysqlpp BadQuery amp e Something went wrong with the SOL query std cerr lt lt Query failed lt lt e what lt lt std endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions std cerr lt lt Error lt lt er what lt lt std endl return 1 return 0 You only need to read the main function to get a good idea of what the program does The key line of code passes an SSQLS examplar and a functor to Query for_each for_each uses the SSQLS instance to build a select from TABLE query stock in this case It runs that query internally calling gather_stock_stats on each row This is a pretty contrived example you could actually do this in SQL but we re trying to prevent the complexity of the code from getting in the way of the demonstration here Just as with store _if described above there are two other overloads for for _each that let you use your own query string 3 12 Connection Options MySQL has a large number of options that control how it makes the connection to the database server and how t
8. static thread_return_t CALLBACK_SPECIFIER worker thread thread_arg_t running flag Ask the underlying C API to allocate any per thread resources it needs in case it hasn t happened already In this particular program it s almost guaranteed that the grab call below will create a new connection the first time through and thus allocate these resources implicitly but there s a nonzero chance that this won t happen Anyway this is an example program meant to show good style so we take the high road and ensure the 61 MySQL v3 0 9 User Manual resources are allocated before we do any queries mysqlpp Connection thread_start Pull data from the sample table a bunch of times releasing the connection we use each time for size_t i 0 i lt 6 i Go get a free connection from the pool or create a new one if there are no free conns yet mysqlpp Connection cp poolptr gt grab if cp cerr lt lt Failed to get a connection from the pool lt lt endl break Pull a copy of the sample stock table and print a dot for each row in the result set mysqlpp Query query cp gt query select from stock mysqlpp StoreQueryResult res query store for size_t j 0 j lt res num_rows 3 cout put Immediately release the connection once we re done using it If we don t the pool can t detect idle connections reliably po
9. Base row functionality added to the SSOLS through inheritance bool do something _interesting int data hi Now Derived is an SSQLS You might wonder if you can use protected inheritance above to redefine the SSQLS s public interface For instance OO purists might object to the public data members in an SSQLS You could encapsulate these public data members in the derived class by using protected inheritance exposing access to the base class s data members with public accessor methods The problem with this is that each SSQLS has dozens of public member functions These are needed by needed by mechanisms like Query storein anything using an STL container which usually require default ctors for contained data structures takes the COMPCOUNT subset of the SSQLS s data members used for making comparison exemplars used with Query update and similar mechanisms see S 12 Used in taking raw row data from a SQL result set and converting it to SSQLS form 52 MySQL v3 0 9 User Manual MySQL internals so unless you re exposed all of them as we did with the constructors above you d again have an SSQLS derivative that is not an SSQLS Simply put only public inheritance is practical with SSQLSes 5 14 SSQLS and BLOB Columns It takes special care to use SSQLS with BLOB columns It s safest to declare the SSQLS field as of type mysqlpp sql_blob This is currently a typedef alias for String which is th
10. Because thread enabled builds are only the default on Windows it s quite possible for this program to do nothing on other platforms See above for in structions on enabling a thread aware build If you write your code without checks for thread support like you see in the code above and link it to a build of MySQL that isn t thread aware it will still try to run The threading mechanisms fall back to a single threaded mode when threads aren t available A particular danger is that the mutex lock mechanism used to keep the pool s internal data consistent while multiple threads access it will just quietly become a no op if MySQL is built without thread 14The file examples threads h contains a few macros and such to abstract away the differences between the two threading models 63 MySQL v3 0 9 User Manual support We do it this way because we don t want to make thread support a MySQL prerequisite And although it would be of limited value this lets you use ConnectionPool in single threaded programs You might wonder why we don t just work around this weakness in the C API transparently in MySQL instead of suggesting design guidelines to avoid it We d like to do just that but how If you consider just the threaded case you could argue for the use of mutexes to protect a connection from trying to execute two queries at once The cure is worse than the disease it turns a design error into a performance sap as the
11. MySQL v3 0 9 User Manual Kevin Atkinson Sinisa Milivojevic Monty Widenius Warren Young Copyright 1998 2001 2005 2008 Kevin Atkinson original author MySQL ABEducational Technology Resources February 04 2009 Table of Contents TL ImtrO duc 0 eet sealed ae acess ai ses eed A nee ees Slate ca de aba alate ei ae oats aes 3 LIA Brief History of My SQUARE cocida illa tae pil ita veriodat died 3 1 2 If You Have Questa e ii des 4 O NO 5 2T The Connection Object ira A ae ae AA eerie ido 5 22 The Quety Object nao rare 5 233 ResUl Sets ct o 5 2A EXCODUONS cli AA AA At EE E 7 A RT 8 3 1 Running the Examples vitoria rata proa acta 8 32 A Simple Example ir id a ie 9 333 4 More Complicated Examples ive ata AA vepeederee boned sey eueeeeees 10 SAR EXCEPUONS suet eos age fa A AAA AA A ae eae a ees hs do ios 11 3 53 Quotins and Escaping orae rrea AE E E EET aT AR abets ere A REEI ARE 12 3 6 C Equivalents of SQL Column Types see an EE ASEA A ARTEA EASE AN 13 3 7 Handling SQL Nu 8 2 r ia U ER EE ei 14 3 8 UsIO 8 Transactions 22 0 a a A eas AA AAA A AA AI 15 3 9 Which Query Type to Use sortida ota E E adan eo copiosa E es loteo 18 3 10 Conditional Result Row Handling coooccoccnccnnconnconnconnccnnconnconnconnconnrnnronnrnn nono nrnnrrnnccnnccnnccnnnnns 20 3 11 Executing Code for Each Row In a Result Set oooocoooccnoconoconoconccnnccnnconncnn nono nonnccnnccnnccnncrnncnnnnnns 22 3 12 Connection Options it A A
12. and Query use Which should you use and why execute is for queries that do not return data per se For instance CREATE INDEX You do get back some in formation from the MySQL server which execute returns to its caller in a SimpleResult object In addition to the obvious a flag stating whether the query succeeded or not this object also contains things like the number of rows that the query affected If you only need the success status it s a little more efficient to call Query exec instead as 1t simply returns bool If your query does pull data from the database the simplest option is store AIl of the examples up to this point have used this method This returns a StoreQueryResult object which contains the entire result set It s especially convenient because StoreQueryResult derives from std vector lt mysqlpp Row gt so it opens the whole panoply of STL operations for accessing the rows in the result set Access rows randomly with subscript notation iterate forwards and backwards over the result set run STL algorithms on the set it all works naturally If you like the idea of storing your results in an STL container but don t want to use std vector you can call Query storein instead It lets you store the results in any standard STL container yes both sequential and set associative types instead of using StoreQueryResult You do miss out on some of the additional database informati
13. ve fixed up any other issues that may prevent your program from building with the new MySQL you can turn it back off and fix up any class name differences If you were only using ColData ina BLOB context you should use sq1_blob or one of the related typedefs defined inlib sql_types h instead to insulate your code from changes like these The SOLString change shouldn t affect you as this class was not designed to be used by end user code But due to the old name and the fact that it used to derive from std string some might have been tempted to use it as an enhanced std string Such code will undoubtedly break but can probably be fixed by just changing it to use std string instead Connection class changes The option setting mechanism has been redesigned Yes again There used to be an enum in Connection with a value for each option we understood and an overload of Connection set_option for each argument type we understood It was possible to pass any option value to any set_option overload and the problem would only be detected at run time Now each option is represented by a class derived from the new Option abstract base class and set_option simply takes a pointer to one of these objects See examples multiquery cpp for the syntax Since each Option subclass takes only the parameter types it actually understands it s now completely type safe at compile time The new option setting mechanism also has the virtu
14. 1 amp amp run_mode 2 cerr lt lt argv 0 lt lt must be run with ml or m2 as one of its command line arguments lt lt endl return 1 mysqlpp Connection con try Establish the connection to the database server con connect db server user pass Start a transaction set Transactions create mutex locks on modified rows so if two programs both touch the same pair of rows but in opposite orders at the wrong time one of the two programs will deadlock The MySQL server knows how to detect this situation and its error return causes MySQL to throw a BadQuery exception The point of this example is that if you want to detect this problem you would check the value of BadQuery errnum not Connection errnum because the transaction rollback process executes a query which succeeds setting the MySQL C API s last error number value to 0 The exception object carries its own copy of the error number at the point the exception was thrown for this very reason mysqlpp Query query con query mysqlpp Transaction trans con Build and run the queries with the order depending on the m flag so that a second copy of the program will deadlock if run while the first is waiting for Enter char dummy 100 for int i 0 i lt 2 i int lock run_mode run_mode i i cout lt lt Trying lock lt lt lock lt lt lt lt endl
15. DLLs on Windows etc Unless your program is licensed under the GPL or LGPL you shouldn t have been using the static libraries from previous versions anyway e Removed the backwards compatibility headers sqlplus hh and mysql hh If you were still using these you will have to change to mysql h which will put all symbols in namespace mysqlpp e Can no longer use arrow operator gt on the iterators into the Fields Result and Row containers 10 1 5 v2 2 0 Code like this will have to change query lt lt delete from mytable where myfield 0 myvalue query parse query def myvalue some_value query execute to something more like this query lt lt delete from mytable where myfield 0 query parse query execute some_value The first code snippet abuses the default template query parameter mechanism Query def to fill out the template instead of using one of the overloaded forms of execute store or use taking one or more SQLString parameters The purpose of Query def is to allow for default template parameters over multiple queries In the first snippet above there is only one parameter so in order to justify the use of template queries in the first place it must be changing with each query Therefore it isn t really a default parameter at all We did not make this change maliciously but you can understand why we are not in any hurry to restore this feature Incide
16. INSERT INTO lt lt v table lt lt lt lt v field list lt lt VALUES lt lt v value _list lt lt where v is the SSQLS you re asking the Query object to insert into the database Now let s look at a complete example which uses one of the more complicated forms of equal_ list This example builds a query with fewer hard coded strings than the most obvious technique requires which makes it more robust in the face of change Here is examples ssqls5 cpp include cmdline h include printdata h include stock h include lt iostream gt include lt vector gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse command line argc argv amp db server amp user amp pass 48 MySQL v3 0 9 User Manual return 1 try Establish the connection to the database server mysqlpp Connection con db server user pass Get all the rows in the stock table mysqlpp Query query con query select from stock vector lt stock gt res query storein res if res size gt 0 Build a select query using the data from the first row returned by our previous query query lt lt select from stock where lt lt res 0 equal_list and stock weight stock price Display the finished query cout lt lt Custom query
17. It s a configuration option though so your server may be set differently 60 MySQL v3 0 9 User Manual db db db server server server user user user password password password The destructor We _must_ call ConnectionPool clear here because our superclass can t do it for us SimpleConnectionPool clear protected Superclass overrides mysqlpp Connection create Create connection using the parameters we were passed upon creation This could be something much more complex but for the purposes of the example this suffices cout put C cout flush indicate connection creation return new mysqlpp Connection db_ empty 0 db_ c_str server_ empty 0 server_ c_str user empty 0 user_ c_str password_ empty password_ c_str void destroy mysqlpp Connection cp Our superclass can t know how we created the Connection so it delegates destruction to us to be safe cout put D cout flush indicate connection destruction delete cp unsigned int max_idle time Set our idle time at an example friendly 3 seconds A real pool would return some fraction of the server s connection idle timeout instead return 3 private Our connection parameters std string db_ server_ user_ password hi SimpleConnectionPool poolptr 0 if defined HAVE_THREADS
18. Null lt mysqlpp sql_tinyint_unsigned gt myfield Template instantiations are first class types in the C language on par with any other type You can use Nul 1 template instantiations anywhere you d use the plain version of that type You can see a complete list of Nul 1 template instan tiations for all column types that MySQL understands at the top of lib type_info cpp There s a secondary distinction between SQL null and anything available in the standard C type system SQL null is a distinct value equal to nothing else We can t use C s NULL for this because it is ambiguous being equal to 0 in integer context MySQL provides the global nul 1 object which you can assign to a Nul1 template instance to make it equal to SQL null myfield mysqlpp null By default MySQL enforces the uniqueness of SQL null at compile time If you try to convert a SQL null to any other data type the compiler will emit an error message saying something about CannotConvertNullToAnyOtherData Type It s safe to insert a SQL null into a C stream though you get NULL If you don t like this behavior you can change it by passing a different value for the second parameter to template Null By default this parameter is NullIsNull meaning that we should enforce the uniqueness of SQL null To relax this distinction you can instantiate the Null template with a different behavior type NullIsZero or NullIsBlank Consider this code
19. if cgi_query 4 if strlen cgi_ query lt 4 memcmp cgi_query id 3 std cout lt lt Content type text plain lt lt std endl lt lt std endl std cout lt lt ERROR Bad query string lt lt std endl return 1 else img_id atoi cgi_query 3 else std cerr lt lt Put this program into a web server s cgi bin directory then lt lt std endl std cerr lt lt invoke it with a URL like this lt lt std endl std cerr lt lt std endl std cerr lt lt http server name com cgi bin cgi_jpeg id 2 lt lt std endl std cerr lt lt std endl std cerr lt lt This will retrieve the image with ID 2 lt lt std endl std cerr lt lt std endl std cerr lt lt You will probably have to change some of the defines 53 MySQL v3 0 9 User Manual at the top of lt lt std endl std cerr lt lt examples cgi_jpeg cpp to allow the lookup to work lt lt std endl return 1 try mysqlpp Connection con IMG DATABASE IMG HOST IMG _USER IMG_PASSWORD mysqlpp Query query con query query lt lt SELECT FROM images WHERE id lt lt img_id mysqlpp UseQueryResult res query use if res images img res fetch_row std cout lt lt Content type image jpeg lt lt std endl std cout lt lt Content length lt lt img data length lt lt n n std cout lt lt img data else
20. n lt lt query lt lt endl catch const mysqlpp BadQuery amp er Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const mysqlpp BadConversion amp er Handle bad conversions cerr lt lt Conversion error lt lt er what lt lt endl lt lt tretrieved data size lt lt er retrieved lt lt actual size lt lt er actual_size lt lt endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 return 0 This example uses the list form of equal_list The arguments stock weight and stock_price are enum values equal to the position of these columns within the stock table sql_create_ generates this enum for you automat ically The boolean argument form of that equal_ list call would look like this query lt lt select from stock where lt lt res 0 equal_list and false false true true false It s a little more verbose as you can see And if you want to get really complicated use the vector form vector lt bool gt v 5 false v stock_weight true v stock_ price true query lt lt select from stock where lt lt res 0 equal_list and v 49 MySQL v3 0 9 User Manual This form makes the most sense if you are building many other quer
21. second thread is blocked indefinitely waiting for the connection to free up Much better to let the program get the Commands out of sync error which will guide you to this section of the manual which tells you how to avoid the error with a better design Another option would be to bury Connect ionPool functionality within MySQL itself so the library could create new connections at need That s no good because the above example is the most complex in MySQL so if it were mandatory to use connection pools the whole library would be that much more complex to use The whole point of MySQL is to make using the database easier MySQL offers the connection pool mechanism for those that really need it but an option it must remain 7 3 Helper Functions Connection has several thread related static methods you might care about when using MySQL with threads You can call Connection thread_aware to determine whether MySQL and the underlying C API library were both built to be thread aware Again I stress that thread awareness is not the same thing as thread safety it s still up to you to make your code thread safe If this method returns true it just means it s possible to achieve thread safety If your program s connection management strategy allows a thread to use a Connection object that another thread created before it creates a connection of its own you must call Connection thread_start from that thread before it
22. ui A da de 68 9 De Visual CAE detal ii vacua nde labio lote ida 68 9 2 Unixy Platforms Linux BSD OS X Cygwin Solaris 0ooooocooccccnoccccnncnncnncnconncnnonncnnonncnnonncnncnnss 69 DIOS ita A AAA EA Add 70 IA MIO MI da ida 70 9 DVECH A RO NRO 71 10 Incompatible Library Chant ii 73 10 1 APE Chan 6S seva dr ta it dele a 73 102ABT CHEESE A A eee ae ces sete IRA aera Aa ace 82 MR ven seca weesd i ade eaves 84 11 1 GNU Lesser General Public License oooccoccnnccnnccnnconnconnconnconaconncnnrnnnrnn eecaeeaaeeaa eeu eeaeeeaeeeeneees 85 11 2 MySQL User Mantial License suis essed geveened sade a sy dai 92 MySQL v3 0 9 User Manual 1 Introduction MySQL is a powerful C wrapper for MySQL s C API Its purpose is to make working with queries as easy as working with STL containers The latest version of MySQL can be found at the official web site Support for MySQL can be had on the mailing list That page hosts the mailing list archives and tells you how you can subscribe 1 1 A Brief History of MySQL MySQL was created in 1998 by Kevin Atkinson It started out MySQL specific but there were early efforts to try and make it database independent and call it SQL This is where the old library name sqlplus came from This is also why the old versions prefixed some class names with Mysql but not others the others were supposed to be the database independent parts All of Kevin s releases had p
23. 20 The library used to have two names for many core classes a short one such as Row and a longer one Mysql Row The library now uses the shorter names exclusively All symbols within MySQL are in the mysqlpp namespace now if you use the new mysql h header If you use the older sqlplus hh or mysql hh headers these symbols are hoist up into the global namespace The older headers cause the compiler to emit warnings if you use them and they will go away someday 10 1 4 v2 0 0 10 1 4 1 Connection class changes e Connection create_db and drop_db return true on success They returned false in v1 7 x This change will only affect your code if you have exceptions disabled e Renamed Connection real_connect to connect made several more of its parameters default and removed the old connect method as it s now a strict subset of the new one The only practical consequence is that if your program was using real_connect you will have to change it to connect e Replaced Connection read_option with new set_option mechanism In addition to changing the name programs using this function will have to use the new Connection Option enumerated values accept a true return value as meaning success instead of 0 and use the proper argument type Regarding the latter read_option took a const char argument but because it was just a thin wrapper over the MySQL C API function mysql options the actual value being
24. 25 to int but not when you convert 1 00 to int In the latter case MySQL knows that it can safely throw away the fractional part e Ifyou use template queries and don t pass enough parameters when instantiating the template Query will throw a BadParamCount exception e Ifyou use a C data type in a query that MySQL doesn t know to convert to SQL MySQL will throw a TypeLookupFailed exception It typically happens with Section 5 Specialized SQL Structures especially when using data types other than the ones defined in lib sql_types h It s educational to modify the examples to force exceptions For instance misspell a field name use an out of range index or change a type to force a String conversion error 3 5 Quoting and Escaping SQL syntax often requires certain data to be quoted Consider this query SELECT FROM stock WHERE item Hotdog Buns Because the string Hotdog Buns contains a space it must be quoted With MySQL you don t have to add these quote marks manually string s Hotdog Buns query lt lt SELECT FROM stock WHERE item lt lt quote only lt lt s That code produces the same query string as in the previous example We used the MySQL quote_only manipulator which causes single quotes to be added around the next item inserted into the stream This works for any type of data 12 MySQL v3 0 9 User Manual that can be converted to MySQL s SQLT
25. GNU operating system as well as its variant the GNU Linux operating system Although the Lesser General Public License is Less protective of the users freedom it does ensure that the user of a program that is linked with the Library has the freedom and the wherewithal to run that program using a modified version of the Library The precise terms and conditions for copying distribution and modification follow Pay close attention to the difference between a work based on the library and a work that uses the library The former contains code derived from the library whereas the latter must be combined with the library in order to run GNU LESSER GENERAL PUBLIC LICENSE TERMS AND CONDITIONS FOR COPYING DISTRIBUTION AND MODIFICATION 0 This License Agreement applies to any software library or other program which contains a notice placed by the copyright holder or other authorized party saying it may be distributed under the terms of this Lesser General Public License also called this License Each licensee is addressed as you A library means a collection of software functions and or data prepared so as to be conveniently linked with applic ation programs which use some of those functions and data to form executables The Library below refers to any such software library or work which has been distributed under these terms A work based on the Library means either the Library or any derivative wo
26. If they are the macro will generate two initialization con structors with identical parameter lists which is illegal in C You might be asking why does there need to be a constructor for comparison to begin with It s often convenient to be able to say something like x stock Hotdog This requires that there be a constructor taking COMPCOUNT arguments to create the temporary stock instance used in the comparison This limitation is not a problem in practice If you want the same number of parameters in the initialization constructor as the number of fields used in comparisons pass 0 for SETCOUNT This suppresses the duplicate constructor you d get if you used the COMPCOUNT value instead This is most useful in very small SSQLSes since it s easier for the number of key fields to equal the number of fields you want to compare on sql_create_1 stock_item 1 0 mysqlpp sql_char item 39 MySQL v3 0 9 User Manual 5 3 Retrieving data Let s put SSQLS to use This is examples ssqlsl cpp include cmdline h include printdata h include stock h include lt iostream gt include lt vector gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 mu try Establish the connection to the datab
27. We don t need any error checking code for such simple routines All of this assumes you re using Windows NT or one of its direct descendants Windows 2000 Windows XP Windows Vista or any Server variant of Windows Windows 95 and its descendants 98 ME and CE do not support UCS 57 MySQL v3 0 9 User Manual 2 They still have the W APIs for compatibility but they just smash the data down to 8 bit and call the A version for you 6 4 For More Information The Unicode FAQs page has copious information on this complex topic When it comes to Unix and UTF 8 specific items the UTF 8 and Unicode FAQ for Unix Linux is a quicker way to find basic information 58 MySQL v3 0 9 User Manual 7 Using MySQL in a Multithreaded Program MySQL is not thread safe in any meaningful sense MySQL contains very little code that actively prevents trouble with threads and all of it is optional We have done some work in MySQL to make thread safety achievable but it doesn t come for free The main reason for this is that MySQL is generally I O bound not processor bound That is if your program s bottleneck is MySQL the ultimate cause is usually the I O overhead of using a client server database Doubling the number of threads will just let your program get back to waiting for I O twice as fast Since threads are evil and generally can t help MySQL the only optional thread awareness features
28. absolutely required that your SSQLS had its fields declared in exactly the same order as the fields in the database server and there could be no gaps An ALTER TABLE command would almost always necessitate redefining the corresponding SSQLS and rebuilding your program Some alterations actually made using SSQLS impossible For the most part this change just gives your program addi tional flexibility in the face of future changes However code that was taking advantage of this low level fact will break when moving to v3 Before I explain how let s go over the high level functional changes you ll find in v3 s SSQLS mechanism Because MySQL no longer needs the SSQLS field order to match the SQL field order the sql1_create_c_or der_ SSQLS creation macro was dropped in v3 We were also able to drop the ordering parameters from sql_create_complete_ That in turn means there is no longer a difference between the way it and sq1_cre ate_c_names_ work so the latter was also dropped Thus there are now only two groups of SSQLS creation 80 MySQL v3 0 9 User Manual macros left sql_create_ which works pretty much as it always has and sql_create complete which is the same except for the lack of ordering parameters In general you should be using sq1_create_ for all SSQLSes unless you need to use different names for data members in C than you use for the corresponding columns in SQL In that case use sql_create complete i
29. arguments are optional If you don t give s the underlying MySQL C API assumes the server is on the local machine It chooses one of several different IPC options based on the platform configuration There are many different forms you can give as server_addr with s to override this default behavior e localhost this is the default it doesn t buy you anything e On Windows a simple period tells the underlying MySQL C API to use named pipes if it s available e 172 20 0 252 12345 this would connect to IP address 172 20 0 252 on TCP port 12345 e my server name svc_name this would first look up TCP service name svc_name in your system s network services database etc services on Unixy systems and something like c windows sys tem32 drivers etc services on modern Windows variants If it finds an entry for the service it then tries to connect to that port on the domain name given For the TCP forms you can mix names and numbers for the host and port service parts in any combination If the server name doesn t contain a colon it uses the default port 3306 MySQL v3 0 9 User Manual If you don t give u it assumes your user name on the database server is the same as your login name on the local machine If you don t give p it will assume the MySQL user doesn t have a password One hopes this isn t the case When running resetdb the user name needs to be for an account with permission
30. copyright disclaimer for the library if necessary Here is a sample alter the names Yoyodyne Inc hereby disclaims all copyright interest in the library Frob a library for tweaking knobs written by James Random Hacker lt signature of Ty Coon gt April 1990 Ty Coon President of Vice That s all there is to it 91 MySQL v3 0 9 User Manual 11 2 MySQL User Manual License 11 2 1 11 2 2 11 2 2 1 I COPYRIGHT The copyright to the MySQL User Manual is owned by its authors Il LICENSE The MySQL User Manual may be reproduced and distributed in whole or in part in any medium physical or elec tronic provided that this license notice is displayed in the reproduction Commercial redistribution is permitted and encouraged Thirty days advance notice via email to the authors of redistribution is appreciated to give the authors time to provide updated documents A REQUIREMENTS OF MODIFIED WORKS All modified documents including translations anthologies and partial documents must meet the following require ments 1 The modified version must be labeled as such 2 The person making the modifications must be identified 3 Acknowledgement of the original author must be retained 4 The location of the original unmodified document be identified 5 The original authors names may not be used to assert or imply endorsement of the resulting document without the original authors per
31. data types for most of these columns long int instead of mysqlpp sql_bigint for example the MySQL equivalents have several advantages Consider the description field definition above It uses two MySQL features in combination to express a data type that has no exact C type equivalent The difference between SQL s MEDIUMTEXT and C s std string type is small so MySQL s typedef just aliases the two However there s no equivalent of SQL s null in the C type system MySQL offers the Null template which bridges this difference between the two type systems For more on this topic see Section 3 7 Handling SQL Nulls The general format of this set of macros is sql_create_ NAME COMPCOUNT SETCOUNT TYPE1 ITEM1 TYPE ITEM Where is the number of member variables NAME is the name of the structure you wish to create TYPEx is the type of a member variable and ITEMx is that variable s name 38 MySQL v3 0 9 User Manual The COMPCOUNT and SETCOUNT arguments are described in the next section 5 2 SSQLS Comparison and Initialization The sql_create_ macro adds member functions and operators to each SSQLS that allow you to compare one SSQLS instance to another These functions compare the first COMPCOUNT fields in the structure In the example above COMPCOUNT is 1 so only the item field will be checked when comparing two stock structures This feature works best when your table s
32. expand that macro The script assumes that your system s preprocessor is called cpp and that its command line interface follows Unix conventions If you run it from the top MySQL directory as shown above it will use the header files in the distribution s 1ib subdirectory Otherwise it assumes the MySQL headers are in their default location usr include mysql If you want to use headers in some other location you ll need to change the directory name in the I flag at the top of the script 5 12 Customizing the SSQLS Mechanism The SSQLS header ssqls h is automatically generated by the Perl script ssqls p1 Although it is possible to change this script to get additional functionality most of the time it s better to just derive a custom class from the generated SSQLS to add functionality to it See the next section to see how to do this correctly That said ssqls p1 does have a few configurables you might want to tweak The first configurable value sets the maximum number of data members allowed in an SSQLS It s 25 out of the box set by the max_data_members variable at the top of ssqls p1 Beware making this value larger increases the size of ssqls h exponentially this will increase compile time and can even make the file uncompilable due to compiler limits Before you increase this value take a good hard look at your database schema and ask if it s really the best design Almost always having so many columns
33. had the tiny int class for many years now specifically to provide a true 8 bit integer without the semantic confusion surrounding the old C char type Either use tiny int or use the SQL type aliases sql_tinyint and sql_tinyint_unsigned instead The r and R template query parameter modifiers were removed They made the library do quoting and both quoting and escaping respectively regardless of the data type of the parameter There are no corresponding Query stream manipulators so for symmetery we had to decide whether to add such manipulators or remove the tquery modifiers There should never be a reason to force quoting or escaping other than to work around a MySQL bug and it s better to just fix the bug than work around it so removed the tquery modifiers Query store next andResult fetch_row no longer throw the EndOfResults and EndOfRes ultSets exceptions these are not exceptional conditions These methods simply return false when you hit the end of the result set now Renamed Query def to Query template defaults to make its purpose clearer Removed Query preview The most direct replacement for this set of overloaded methods is the parallel set of str methods which were just aliases before Chose str over preview because it s standard C no menclature But if you re just looking to get a copy of a built query string and you aren t using template queries you can now insert the Query into a
34. ia 23 3 13 Getting Field Meta Intormation uc arein eeii EE AE ATE A iris acta 26 3 14 MySQiE s Special String Types viii A A ea ee aa dy 28 3 15 Dealing with Binary Data uti it ideal 29 3 16 Concurrent Queries on a CONNECTION 2 0 cece cece cece een een eee e nen ee nen EEE EEE EEE EEG EE EEE GREE EG EE EEE GEES 33 A Template Queries conca ARNA eE idiota A E 34 4 1 Setting up Template QUETIES ui A A AD een de 35 4 2 Setting the Parameters at Execution Time 0 cece cece eee cece ceaeceaeeeeeeeeeeeeeeaeeeaeeeaeeaa seas esaaes 35 43 Deta lt Parameters ui A lada ici 36 44 Error Handi icon iia pia es ppal 37 3 Specialized SOL Strictures en E aE T EERE see Hagan gue clans veda oban ds E ESE E Ea 38 Dela SQA A AT RAR 38 5 2 SSQLS Comparison and Initialization ooocooncnnccnnconnccnnccnncnnncnnncnnncnnncnnnnnnrnn nono nono nonncrnncnnnccnninnn 39 3 35 REMOVING data iii ii id 40 34 Adding Did A A da 42 5 Moditying data aired peere eiie AE OIE EE tati 43 MySQL v3 0 9 User Manual 5 6 Less thanscomparable iii ti 44 5 7 Additional Features of Specialized SQL Structures ooooooccnnccnnconccnnoconccnnncnnronnrnnnonnccnnccnnccnnccnninnns 46 5 8 Using an SSQLS in Multiple Modules necro e a an ia o A ecue cea eeaeeeaneeaes 46 5 92 Harnessing SSOLS Internals iii A dade tates ie 47 5 10 Having Different Field Names in C and SQL 00 cece cee ceeeceeece teen teen nono tesa eeaseeaseeaneeaes 50 511 Expandme SSOLS
35. in writing and you have a real mess Since no standards body held sway over things like international character encoding in the early days of computing many different character sets were invented These character sets weren t even standardized between operating systems so heaven help you if you needed to move localized Greek text on a DOS box to a Russian Macintosh The only way we got any international communication done at all was to build standards on top of the common 7 bit ASCII subset Either people used approximations like a plain c instead of the French or they invented things like HTML entities amp ccedil in this case to encode these additional characters using only 7 bit ASCII Unicode solves this problem It encodes every character used for writing in the world using up to 4 bytes per character The subset covering the most economically valuable cases takes two bytes per character so most Unicode aware pro grams deal in 2 byte characters for efficiency Unfortunately Unicode was invented about two decades too late for Unix and C Those decades of legacy created an immense inertia preventing a widespread move away from 8 bit characters MySQL and C come out of these older traditions and so they share the same practical limitations MySQL currently doesn t have any code in it for Unicode conversions it just passes data along unchanged from the underlying MySQL C API so you still need to be aware of these
36. is a sign that you need to refactor the table 50 MySQL v3 0 9 User Manual The second configurable is the default floating point precision used for comparison As described above Section 5 2 SSQLS Comparison and Initialization SSQLSes can be compared for equality The only place this is tricky is with floating point numbers since rounding errors can make two equal values compare as distinct This property of floating point numbers means we almost never want to do exact comparison MySQL lets you specify the precision you want it to use If the difference between two values is under a given threshold MySQL considers the values equal The default threshold is 0 00001 This threshold works well for human scale values but because of the way floating point numbers work it can be wildly inappropriate for very large or very small quantities like those used in scientific applications There are actually two ways to change this threshold If you need a different system wide default edit ssql1s pl and change the fp_min_delta variable at the top of the file then rebuild ssqls h as described below If you need different thresholds per file or per project it s better to set the C macro MYSQLPP_FP MIN DELTA instead The Perl variable sets this macro s default if you give a different value before including ssgls h it will use that instead To rebuild ssqls h after changing ssqls p1 you ll need a Perl interpret
37. is a special result type SimpleResult that simply reports the state resulting from the query whether the query was successful how many rows it impacted if any etc 2 3 2 Queries That Return Data MySQL Data Structures The most direct way to retrieve a result set is to use Query store This returns a StoreQueryResult object which derives from std vector lt mysqlpp Row gt making it a random access container of Rows In turn each Row object is like a std vector of String objects one for each field in the result set Therefore you can treat StoreQueryResult as a two dimensional array you can get the 5th field on the 2nd row by simply saying res ult 1 4 You can also access row elements by field name like this result 2 price A less direct way of working with query results is to use Query use which returns a UseQueryResult object This class acts like an STL input iterator rather than a std vector you walk through your result set processing one row at a time always going forward You can t seek around in the result set and you can t know how many results are in the set until you find the end In payment for that inconvenience you get better memory efficiency because the entire result set doesn t need to be stored in RAM This is very useful when you need large result sets 2 3 3 Queries That Return Data Specialized SQL Structures Accessing results through MySQL s data structures is a pre
38. it for it results begin it results end it print_stock_row it gt item c_str it gt num it gt weight it gt price it gt sdate catch const mysqlpp BadQuery amp e Something went wrong with the SOL query std cerr lt lt Query failed lt lt e what lt lt std endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions std cerr lt lt Error lt lt er what lt lt std endl return 1 return 0 I doubt anyone really needs to select rows from a table that have a prime number in a given field This example is meant to be just barely more complex than SQL can manage to avoid obscuring the point That point being the Query store_if call here gives you a container full of results meeting a criterion that you probably can t express in SQL You will no doubt have much more useful criteria in your own programs If you need a more complex query than the one store_if knows how to build when given an SSQLS examplar there are two overloads that let you use your own query string One overload takes the query string directly and the other uses the query string built with Query s stream interface 21 MySQL v3 0 9 User Manual 3 11 Executing Code for Each Row In a Result Set SQL is more than just a database query language Modern database engines can actually do some calculations on the data on the server side But t
39. key fields are the first ones in the SSQLS and you set COMPCOUNT equal to the number of key fields That way a check for equality between two SSQLS structures in your C code will give the same results as a check for equality in SQL COMPCOUNT must be at least 1 The current implementation of sq1_create_ cannot create an SSQLS without comparison member functions Because our stock structure is less than comparable you can use it in STL algorithms and containers that require this such as STL s associative containers std set lt stock gt result query storein result cout lt lt result lower_bound stock Hamburger gt item lt lt endl This will print the first item in the result set that begins with Hamburger The third parameter to sq1_create_ is SETCOUNT If this is nonzero it adds an initialization constructor and a set member function taking the given number of arguments for setting the first N fields of the structure For example you could change the above example like so sql create 6 stock 1 2 mysqlpp sql_char item mysqlpp sql_bigint num mysqlpp sql_double weight mysqlpp sql_decimal price mysqlpp sql_ date sdate mysqlpp Null lt mysqlpp sql_mediumtext gt description stock foo Hotdog 52 In addition to this 2 parameter constructor this version of the stock SSQLS will have a similar 2 parameter set member function The COMPCOUNT and SETCOUNT values cannot be equal
40. lt endl Create the stored procedure print_multiple results query Call the stored procedure and display its results query lt lt CALL get_stock relish cout lt lt Query lt lt query lt lt endl print_multiple results query endif return 0 catch const BadOption amp err cerr lt lt err what lt lt endl cerr lt lt This example requires MySQL 4 1 1 or later lt lt endl return 1 catch const ConnectionFailed amp err cerr lt lt Failed to connect to database server lt lt err what lt lt endl return 1 catch const Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 This is a fairly complex example demonstrating the multi query and stored procedure features in newer versions of MySQL Because these are new features and they change the communication between the client and server you have to enable these features in a connection option The key line is right up at the top of main where it creates a MultiStatementsOption object and passes it to Connection set_option That method will take a pointer to any derivative of Option you just create such an object on the heap and pass it in which gives Connection the data values it needs to set the option You don t need to worry about releasing the memory used by the Option objects it s done automatically The only tri
41. lt lt res i sdate lt lt endl else cerr lt lt Failed to get stock table lt lt query error lt lt endl return 1 return 0 else cerr lt lt DB connection failed lt lt conn error lt lt endl return 1 The main point of this example is that we re accessing fields in the row objects by name instead of index This is slower but obviously clearer We re also printing out the entire table not just one column 3 4 Exceptions By default MySQL uses exceptions to signal errors We ve been suppressing this in all the examples so far by passing false to Connection s constructor This kept these early examples simple at the cost of some flexibility and power in error handling In a real program we recommend that you leave exceptions enabled You do this by either using the default Connection constructor or by using the create and connect constructor All of MySQL s custom exceptions derive from a common base class Exception That in turn derives from Standard C s std exception class Since the library can indirectly cause exceptions to come from the Standard C Library it s possible to catch all exceptions from MySQL by just catching std exception However it s better to have individual catch blocks for each of the concrete exception types that you expect and add a handler for either Exception or std exception to act as a catch all for unexpected excep
42. may add an explicit geographical distribution limitation excluding those countries so that distribution is permitted only in or among countries not thus excluded In such case this License incorporates the limitation as if written in the body of this License 13 The Free Software Foundation may publish revised and or new versions of the Lesser General Public License from time to time Such new versions will be similar in spirit to the present version but may differ in detail to address new problems or concerns Each version is given a distinguishing version number If the Library specifies a version number of this License which applies to it and any later version you have the option of following the terms and conditions either of that version or of any later version published by the Free Software Foundation If the Library does not specify a license version number you may choose any version ever published by the Free Software Foundation 14 If you wish to incorporate parts of the Library into other free programs whose distribution conditions are incompatible with these write to the author to ask for permission For software which is copyrighted by the Free Software Foundation write to the Free Software Foundation we sometimes make exceptions for this Our decision will be guided by the two goals of preserving the free status of all derivatives of our free software and of promoting the sharing and reuse of software generally NO WARRANTY
43. nor complete supersets of std string As a result end user code generally doesn t deal with these classes directly because std string is a better general purpose string type In fact MySQL itself uses std string most of the time too But the places these specialized stringish types do get used are so important to the way MySQL works that it s well worth taking the time to understand them 3 14 1 SQLTypeAdapter The simpler of the two is SOLTypeAdapter or STA for short gt As its name suggests its only purpose is to adapt other data types to be used with SQL It has a whole bunch of con version constructors one for all data types we expect to be used with MySQL for values in queries SQL queries are strings so constructors that take stringish types just make a copy of that string and all the others stringize the value in the format needed by SQL The conversion constructors preserve type information so this stringization process doesn t throw away any essential information STA is used anywhere MySQL needs to be able to accept any of several data types for use in a SQL query Major users are Query s template query mechanism and the Query stream quoting and escaping mechanism You care about STA because any time you pass a data value to MySQL to be used in building a SQL query it goes through STA STA is one of the key pieces in MySQL that makes it easy to generate syntactically correct SQL queries SI
44. purge was an internal implementation detail not something for end user code to call e raw_result end user code shouldn t be digging down to the C API data structures but if you really need something like this look at the implementation of Query storein Its workings will probably be educa tional e reset_names no reason to call this especially now that the field name list is initialized once at startup and then never changed e reset field names just an alias for previous e reset _types same argument as for reset_names e reset field types just an alias for previous ResUse field _num would unconditionally throw a BadFieldName exception when you asked for a field that doesn t exist Now if exceptions are disabled on the object it just returns 1 SimpleResult s member variables are all now private and have read only accessor functions of the same name Code like this used to work mysqlpp Row row mysqlpp Result size type i for i 0 row res i i 79 MySQL v3 0 9 User Manual 10 1 7 8 10 1 7 9 Do something with row here That is indexing past the end of a store result set would just return an empty row object which tests as false in bool context so it ends the loop Now that StoreQueryResult is a std vector derivative this either crashes your program or causes the standard library to throw an exception depending on what debugging features your v
45. queries on a single connection and you end up with the same problem The simplest recipie for disaster is UseQueryResult rl query use select garbage from plink where foobie tamagotchi UseQueryResult r2 query use select blah from bonk where bletch smurf The second use call fails because the first result set hasn t been consumed yet 33 MySQL v3 0 9 User Manual 4 Template Queries Another powerful feature of MySQL is being able to set up template queries These are kind of like C s printf facility you give MySQL a string containing the fixed parts of the query and placeholders for the variable parts and you can later substitute in values into those placeholders The following program demonstrates how to use this feature This is examples tquery1 cpp include cmdline h include printdata h include lt iostream gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 try Establish the connection to the database server mysqlpp Connection con db server user pass Build a template query to retrieve a stock item given by item name mysqlpp Query query con query select from stock where item 0q query parse Retrieve an item added by resetdb it won t be
46. query execution methods without passing all of the values If the query takes four parameters and you ve set defaults for the last three you can execute the query using as little as just one explicit parameter Now you can see why we numbered the template query parameters the way we did a few sections earlier We ordered them so that the ones less likely to change have higher numbers so we don t always have to pass them We can just give them defaults and take those defaults when applicable This is most useful when some parameters in a template query vary less often than other parameters For example query template _defaults field1 item query template _ defaults field2 price StoreQueryResult resl query store Hamburger Buns item StoreQueryResult res2 query store 1 25 price This stores the result of the following queries in res1 and res2 respectively select item price from stock where item Hamburger Buns select item price from stock where price 1 25 Default parameters are useful in this example because we have two queries to issue and parameters 2 and 3 remain the same for both while parameters O and 1 vary Some have been tempted into using this mechanism as a way to set all of the template parameters in a query query template _defaults what Hamburger Buns query template defaults wheref item 36 MySQL v3 0 9 User Manual query template _defaults field
47. std cout lt lt Content type text plain lt lt std endl lt lt std endl std cout lt lt ERROR No such image with ID lt lt img_id lt lt std endl catch const mysqlpp BadQuery amp er Handle any query errors std cout lt lt Content type text plain lt lt std endl lt lt std endl std cout lt lt QUERY ERROR lt lt er what lt lt std endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions std cout lt lt Content type text plain lt lt std endl lt lt std endl std cout lt lt GENERAL ERROR lt lt er what lt lt std endl return 1 return 0 5 15 SSQLS and Visual C 2003 SSQLS works on all platforms supported by MySQL except for Visual C 2003 Because the rest of MySQL works just fine with Visual C 2003 we haven t removed this platform from the supported list entirely If you do need SSQLS and are currently on Visual C 2003 you have these options 1 The simplest option is to upgrade to a newer version of Visual C The compiler limitations that break SSQLS are all fixed in Visual C 2005 and newer Visual C Express is free and is apparently here to stay coupled with the free wx Widgets library it lacks little compared to Visual C Professional A bonus of using wx Widgets is that it s cross platform and better supported than MFC If you can t upgrade your compile
48. the same parameters as this constructor stock row Hot Dogs 100 1 5 1 75 mysqlpp sql_date 1998 09 25 mysqlpp null Form the query to insert the row into the stock table mysqlpp Query query con query query insert row Show the query about to be executed cout lt lt Query lt lt query lt lt endl Execute the query We use execute because INSERT doesn t return a result set query execute Retrieve and print out the new table contents print_stock_table query catch const mysqlpp BadQuery amp er 42 MySQL v3 0 9 User Manual Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const mysqlpp BadConversion amp er Handle bad conversions cerr lt lt Conversion error lt lt er what lt lt endl lt lt tretrieved data size lt lt er retrieved lt lt actual size lt lt er actual_size lt lt endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 return 0 That s all there is to it There is one subtlety MySQL automatically quotes and escapes the data when building SQL queries using SSQLS structures It s efficient too MySQL is smart enough to quote and escape data only for those data types that actually require it B
49. there if tquery or ssqls3 is run since resetdb mysqlpp StoreQueryResult resl query store N rnberger Brats if resl empty throw mysqlpp BadQuery UTF 8 bratwurst item not found in table run resetdb Replace the proper German name with a 7 bit ASCII approximation using a different template query query reset forget previous template query data query lt lt update stock set item 0q where item lq query parse mysqlpp SimpleResult res2 query execute Nuerenberger Bratwurst res1 0 0 c_str Print the new table contents print_stock_table query catch const mysqlpp BadQuery amp er Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const mysqlpp BadConversion amp er Handle bad conversions cerr lt lt Conversion error lt lt er what lt lt endl lt lt 34 MySQL v3 0 9 User Manual tretrieved data size lt lt er retrieved lt lt actual size lt lt er actual_size lt lt endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 return 0 The line just before the call to query parse sets the template and the parse call puts it into effect From that point on you can re use this query by calling any of several Query member fu
50. thread is going to need this memory for its entire run time Memory debuggers aren t smart enough to know all this though so they will gripe about a memory leak unless you call this from each thread that uses MySQL before that thread exits Although its name suggests otherwise Connection thread_id has nothing to do with anything in this chapter 7 4 Sharing MySQL Data Structures We re in the process of making it safer to share MySQL s data structures across threads 64 MySQL v3 0 9 User Manual By way of illustration let me explain a problem we had up until MySQL v3 0 When you issue a database query that returns rows you also get information about the columns in each row Since the column information is the same for each row in the result set older versions of MySQL kept this information in the result set object and each Row kept a pointer back to the result set object that created it so it could access this common data at need This was fine as long as each result set object outlived the Row objects it returned It required uncommon usage patterns to run into trouble in this area in a single threaded program but in a multi threaded program it was easy For example there s frequently a desire to let one connection do the queries and other threads process the results You can see how avoiding lifetime problems here would require a careful locking strategy We got around this in MySQL v3 0 by giving these
51. through the list above first Then make the following changes particular to INET and C CLI e Under Configuration Properties General change the setting from clr pure to clr You need mixed assembly support to allow a C CLI program to use a plain C library like MySQL e For the Linker Input settings you don t need wsock32 1ib The mere fact that you re using NET takes care of that dependency for you In the MFC instructions above it said that you need to build it using the Multi threaded DLL version of the C Runtime Library That s not strictly true for MFC but it s an absolute requirement for C CLI See the Remarks in the MSDN article on the clr switch for details You may want to study examples vstudio wforms wforms vcproj to see all this in action Note that some of the paths will be different because it can use relative paths for mysqlpp_d d11 and mysglpp dl1 9 2 Unixy Platforms Linux BSD OS X Cygwin Solaris There are lots of ways to build programs on Unixy platforms We ll cover just the most generic way here Makefiles We ll use a very simple example so it s clear how to translate this to more sophisticated build systems such as GNU Autotools or Bakefile Hello world for MySQL might look something like this include lt mysql h gt int main mysqlpp String greeting Hello world std cout lt lt greeting lt lt std endl return 0 Here s a Makefi
52. underlying issues During the development of the Plan 9 operating system a kind of successor to Unix Ken Thompson invented the UTF 8 encoding UTF 8 is a superset of 7 bit ASCII and is compatible with C strings since it doesn t use 0 bytes anywhere as multi byte Unicode encodings do As a result many programs that deal in text will cope with UTF 8 data even though they have no explicit support for UTF 8 Follow the last link above to see how the design of UTF 8 allows this Thus when explicit support for Unicode was added in MySQL v4 1 they chose to make UTF 8 the native encoding to preserve backward compatibility with programs that had no Unicode support 6 2 Unicode on Unixy Systems Linux and Unix have system wide UTF 8 support these days If your operating system is of 2001 or newer vintage it probably has such support On such a system the terminal I O code understands UTF 8 encoded data so your program doesn t require any special code to correctly display a UTF 8 string If you aren t sure whether your system supports UTF 8 natively just run the simplel example if the first item has two high ASCII characters in place of the ti in Niirnberger Brats you know it s not handling UTF 8 If your Unix doesn t support UTF 8 natively it likely doesn t support any form of Unicode at all for the historical reasons I gave above Therefore you will have to convert the UTF 8 data to the local 8 bit character set The
53. user pass Retrieve a subset of the sample stock table set up by resetdb and display it mysqlpp Query query conn query select item from stock if mysqlpp StoreQueryResult res query store cout lt lt We have lt lt endl for size_t i 0 i lt res num_rows i cout lt lt t lt lt res i 0 lt lt endl MySQL v3 0 9 User Manual else cerr lt lt Failed to get item list return 1 lt lt query error lt lt endl return 0 else cerr lt lt DB connection failed lt lt conn error lt lt endl return 1 This example simply gets the entire item column from the example table and prints those values out Notice that MySQL s StoreQueryResult derives from std vector and Row provides an interface that makes ita vector work alike This means you can access elements with subscript notation walk through them with iterators run STL algorithms on them etc Row provides a little more in this area than a plain old vector you can also access fields by name using subscript notation The only thing that isn t explicit in the code above is that we delegate command line argument parsing to parse command line inthe excommon module This function exists to give the examples a consistent interface not to hide important details You can treat it like a black box it takes argc and argv as inputs and sends back database connection parameters
54. 1 item query template _defaults field2 price StoreQueryResult resl query store This can work but it is not designed to In fact it s known to fail horribly in one common case You will not get sympathy if you complain on the mailing list about it not working If your code doesn t actively reuse at least one of the parameters in subsequent queries you re abusing MySQL and it is likely to take its revenge on you 4 4 Error Handling If for some reason you did not specify all the parameters when executing the query and the remaining parameters do not have their values set via Query template_defaults the query object will throw a BadParamCount object If this happens you can get an explanation of what happened by calling BadParamCount what like so query template _defaults field1 item query template defaults field2 price StoreQueryResult res query store 1 25 This would throw BadParamCount because the where f is not specified In theory this exception should never be thrown If the exception is thrown it probably a logic error in your program 37 MySQL v3 0 9 User Manual 5 Specialized SQL Structures The Specialized SQL Structure SSQLS feature lets you easily define C structures that match the form of your SQL tables At the most superficial level an SSQLS has a member variable corresponding to each field in the SQL table But an SSQLS also has several methods o
55. E txt files included with the MySQL distribution that are relevant to your platform We won t repeat all of that here Most of the examples require a test database created by resetdb You can run it like so resetdb s server_addr u user p password Actually there s a problem with that It assumes that the MySQL library is already installed in a directory that the operating system s dynamic linker can find MySQL is almost never built statically Unless you re installing from RPMs you ve had to build the library from source and you should run at least a few of the examples before installing the library to be sure it s working correctly Since your operating system s dynamic linkage system can t find the MySQL libraries without help until they re installed we ve created a few helper scripts to help run the examples MySQL comes with the exrun shell script for Unixy systems and the exrun bat batch file for Windows You pass the example program and its arguments to the exrun helper which sets up the library search path so that it will use the as yet uninstalled version of the MySQL library in preference to any other on your system exrun resetdb s server addr u user p password That s the typical form for a Unixy system You leave off the bit on Windows You can leave it off on a Unixy system too if you have in your PATH Not a recommendation just an observation All of the program
56. GNU make which is just about universal these days 9 3 OS X 9 3 1 Makefiles The generic Makefile instructions above cover most of what you need to know about using Makefiles on OS X One thing that may trip you up on OS X is that it uses an uncommon dynamic linkage system The easiest way to cope with this is to link your executables with the compiler rather than call 1d directly Another tricky bit on OS X is the concept of Universal binaries See README Mac OS X txt for details on building a Universal version of the MySQL library if you need one By default you only get a version tuned for the system type you build it on 9 3 2 Xcode Ihave no information on how to incorporate MySQL in an Xcode project Send a message to the MySQL mailing list if you can help out here 9 4 MinGW 9 4 1 Makefiles The generic Makefile instructions above apply to MinGW s version of GNU make as well You will have some differ ences due to the platform so here s the adjusted Makefile CXX g MYSQL DIR c Program Files MySQL MySQL Server 5 0 CXXFLAGS I MYSQL DIR include Ic MySQL include LDFLAGS L MYSQL DIR lib opt Le MySQL lib MinGW lmysqlclient lmysqlpp EXECUTABLE hello 70 MySQL v3 0 9 User Manual all EXECUTABLE clean del EXECUTABLE Note the use of forward slashes Also we use del instead of rm in the clean target this assumes there is no sh exe in your PATH which m
57. Macros iii A de Soot aay A ie Bic A ects 50 5 12 Customizing the SSQLS Mechanism onneen ieri a eaa cece cena EER a E E ET A E TERENE 50 5 13 Deriving from an SSQLS sist iiss awh AA AA A 51 3 14 SSOLS and BLOB Coli 00000 aii 53 IAN SSOES and Visual Ca 2003 A A A A ARS 54 6 Using Unicode with MySQL oi coa Titi da 56 6 1 A Short History Of Unicode ni A A iio 56 6 2 Unicode on Unixy SyStOMs vectra rd ay RERET AVE E EE traida ti n 56 6 3 Unicode on WO WS tc AA A nae A A eee 57 6 4 For More Information isis a e a ita tario tE Daiana 58 7 Using MySQL in a Multithreaded Program cece cece cece cece cence ne ceaeeeaeeeeeeeeeaeeeaeseaeeaaeeaaesaaeeaaes 59 Tele Build Issue EEE RE a A AA A eer 59 72 Connection Management it A A A A A eee eaten Sheed 60 7 3 Helper Functions vicodin it AAE AE A E TAE dead yaene seb E eres caadtestades 64 7 4 Sharing MySQL 4 Data Structures iee e ce eeee cece EE N E TAA TA SE EE RAEE TAAS 64 8 Configuring MySQL kecsin ahea ea i e a a i bau eden a a eai aAa 66 8 1 The Location of the MySQL Development Files 2 0 0 0 cece cee cee ce eece teen seca tena seas ecaaeeueeaneeaes 66 8 2 The Maximum Number of Columns Allowed 0 0 0 00 cee ceeece ence ence ence eeceeeeaeecaeeeaeeaaeeaaeeaeeeaeeeaneeaes 66 8 3 Buried MySQL E APL Beaders ct A A Ai 66 8 4 Building MySQL on Systems Without Complete C99 Support ooccocccnccnnccnnconnconncnnnconaconncnnncnanons 67 9 Using MySQL 1n Your Own Project
58. MySQL documentation isn t actually part of the Linux Documentation Project so the main changes are to LDP related language Also generic language such as author s or authors has been replaced with specific language because the license applies to only this one document These licenses basically state that you are free to use distribute and modify these works whether for personal or commercial purposes as long as you grant the same rights to those you distribute the works to whether you changed them or not See the licenses below for full details 84 MySQL v3 0 9 User Manual 11 1 GNU Lesser General Public License 11 1 1 Version 2 1 February 1999 Copyright C 1991 1999 Free Software Foundation Inc 59 Temple Place Suite 330 Boston MA 02111 1307 USA Everyone is permitted to copy and distribute verbatim copies of this license document but changing it is not allowed This is the first released version of the Lesser GPL It also counts as the successor of the GNU Library Public License version 2 hence the version number 2 1 Preamble The licenses for most software are designed to take away your freedom to share and change it By contrast the GNU General Public Licenses are intended to guarantee your freedom to share and change free software to make sure the software is free for all its users This license the Lesser General Public License applies to some specially designated software package
59. SQL in some other way such as with Dev Cpp based on MinGW you re on your own to enable thread awareness 2 Link your program to a thread aware build of the MySQL C API library If you use a binary distribution of MySQL on Unixy systems you usually get two different versions of the MySQL C API library one with thread support and one without These are typically called 1ibmysqlclient and libmysqlclient_r the latter being the thread safe one The _r means reentrant If you re using the Windows binary distribution of MySQL there are two versions of the client library but both are thread aware One just has debugging symbols and the other doesn t See README Visual C txt or README MinGW txt for details If you build MySQL from source you might only get one version of the MySQL C API library and it can have thread awareness or not depending on your configuration choices This is the case with Cygwin where you currently have no choice but to build the C API library from source See README Cygwin txt 3 Enable threading in your program s build options This is different for every platform but it s usually the case that you don t get thread aware builds by default Depending on the platform you might need to change compiler options linker options or both See your devel 59 MySQL v3 0 9 User Manual opment environment s documentation or study how MySQL itself turns on thread aware build options wh
60. SSQLS sql_create_1 stock_subset 1 0 string item vector lt stock_subset gt res query storein res etC MySQL is flexible about populating SSQLSes It works much like the Web a design that s enabled the development of the largest distributed system in the world Just as a browser ignores tags and attributes it doesn t understand you can populate an SSQLS from a query result set containing columns that don t exist in the SSQLS And as a browser uses sensible defaults when the page doesn t give explicit values you can have an SSQLS with more fields defined than are in the query result set and these SSQLS fields will get default values Zero for numeric types false for bool and a type specific default for anything more complex like mysqlpp DateTime Programs built against versions of MySQL prior to 3 0 would crash at almost any mismatch between the database schema and the SSQLS definition It s no longer necessary to keep the data design in lock step between the client and database server A mismatch can result in data loss but not a crash 41 MySQL v3 0 9 User Manual In more concrete terms the example above is able to populate the stock objects using as much information as it has and leave the remaining fields at their defaults Conversely you could also stuff the results of SELECT FROM stock into the stock_subset SSQLS declared above the extra fields would just be ignored We re tra
61. _row otherwise if conn errnum 19 MySQL v3 0 9 User Manual cerr lt lt Error received in fetching a row lt lt conn error lt lt endl return 1 return 0 else cerr lt lt Failed to get stock item lt lt query error lt lt endl return 1 else cerr lt lt DB connection failed lt lt conn error lt lt endl return 1 This example does the same thing as simple2 only with a use query instead of a store query Valuable as use queries are they should not be the first resort in solving problems of excessive memory use It s better if you can find a way to simply not pull as much data from the database in the first place Maybe you re saying SELECT even though you don t immedidately need all the columns from the table Or maybe you re filtering the result set with C code after you get it from the database server If you can do that filtering with a more restrictive WHERE clause on the SELECT it ll not only save memory it ll save bandwidth between the database server and client and can even save CPU time If the filtering criteria can t be expressed in a WHERE clause however read on to the next section 3 10 Conditional Result Row Handling Sometimes you must pull more data from the database server than you actually need and filter it in memory SQL s WHERE clause is powerful but not as powerful as C Instead of storing t
62. a derivative of the original library The ordinary General Public License therefore permits such linking only if the entire combination fits its criteria of freedom The Lesser General Public License permits more lax criteria for linking other code with the library 85 MySQL v3 0 9 User Manual 11 1 2 We call this license the Lesser General Public License because it does Less to protect the user s freedom than the ordinary General Public License It also provides other free software developers Less of an advantage over competing non free programs These disadvantages are the reason we use the ordinary General Public License for many libraries However the Lesser license provides advantages in certain special circumstances For example on rare occasions there may be a special need to encourage the widest possible use of a certain library so that it becomes a de facto standard To achieve this non free programs must be allowed to use the library A more frequent case is that a free library does the same job as widely used non free libraries In this case there is little to gain by limiting the free library to free software only so we use the Lesser General Public License In other cases permission to use a particular library in non free programs enables a greater number of people to use a large body of free software For example permission to use the GNU C Library in non free programs enables many more people to use the whole
63. alled in a nonstandard location you will have to tell the configure script where these files are with some combination of the with mysql with mysql include and with mysql lib flags See README Unix txt for details No other platform allows this sort of auto discovery so the build files for these platforms simply hard code the default installation location for the current Generally Available version of MySQL For example the Visual C project files currently assume MySQL is inc Program Files MySQL MySQL Server 5 0 If you re using some other release of MySQL or you installed it in a nonstandard location you will have to modify the build files How you do this exactly varies based on platform and what tools you have on hand See README Visual C txt README MinGW txt or README Mac O0S X txt as appropriate 8 2 The Maximum Number of Columns Allowed MySQL offers two ways to automatically build SQL queries at run time Template Queries and SSQLS There s a limit on the number of template query parameters and the number of SSQLS fields due to the way these mechanisms work Both are set to 25 by default We arrived at these limits empirically partly by looking at good database designs and by testing compilers to find their limits We wanted a limit that doesn t often need to be raised without unduly narrowing the list of supported platforms by exceeding compiler limits If it happens that your database design does need more than 25
64. amp num_results gt 0 cout lt lt Result set lt lt index lt lt has lt lt num_results lt lt row lt lt num_results 1 s lt lt lt lt endl else cout lt lt Result set lt lt index lt lt is empty lt lt endl return Figure out the widths of the result set s columns IntVectorType widths int size res num_fields for int i 0 i lt size i widths push_back max res field i max_length res field_name i size Print result set header print_row_separator widths print_header widths res print_row_separator widths Display the result set contents for StoreQueryResult size type i 0 i lt num_results i print_row widths res i 24 MySQL v3 0 9 User Manual Print result set footer print_row_separator widths static void print multiple results Query amp query 1 Execute query and print all result sets StoreQueryResult res query store print_result res 0 for int i 1 query more_results i res query store_next print_result res i int main int argc char argv Get connection parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 try Enable multi queries Notice that you almost always set MySQL connection options b
65. ase server mysqlpp Connection con db server user pass Retrieve a subset of the stock table s columns and store the data in a vector of stock SSQLS structures See the user manual for the consequences arising from this quiet ability to store a subset of the table in the stock SSQLS mysqlpp Query query con query select item description from stock vector lt stock gt res query storein res Display the items cout lt lt We have lt lt endl vector lt stock gt iterator it for it res begin it res end it cout lt lt t lt lt it gt item if it gt description mysqlpp null cout lt lt lt lt it gt description lt lt cout lt lt endl catch const mysqlpp BadQuery amp er Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const mysqlpp BadConversion amp er Handle bad conversions e g type mismatch populating stock cerr lt lt Conversion error lt lt er what lt lt endl lt lt tretrieved data size lt lt er retrieved lt lt actual size lt lt er actual_size lt lt endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 40 MySQL v3 0 9 User Manual return 0 Here is the stock h header used by that e
66. ata type to almost any other You d get a tangle of ambiguous data type conversion errors from the most innocent code 29 MySQL v3 0 9 User Manual 3 15 1 Loading a binary file into a BLOB column This example shows how to insert binary data into a MySQL table s BLOB column with MySQL and also how to get the value of the auto increment column from the previous insert This MySQL feature is usually used to create unique IDs for rows as they re inserted The program requires one command line parameter over that required by the other examples you ve seen so far the path to a JPEG file This is examples load_jpeg cpp include cmdline h include printdata h include lt mysql h gt include lt fstream gt using namespace std using namespace mysqlpp Pull in a state variable used by att_getopt implementation so we can pick up where standard command line processing leaves off Feel free to ignore this implementation detail extern int ag_optind static bool is _jpeg const unsigned char img data 1 return img_data 0 OxFF amp amp img data 1 0xD8 88 memcmp img_data 6 JFIF 4 0 memcmp img_data 6 Exif 4 0 int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db amp Server amp user amp pass jpeg_file
67. ay not be true if you have Cygwin or MSYS installed Read on to see how to cope with that 9 4 1 1 Working with MinGW Under Cygwin Compared to Unix the biggest difference you ll find is that MinGW calls its make executable mingw32 make As I understand it this is to allow it to coexist with Cygwin since the two versions have some behavioral differences despite both being based on GNU Make A Makefile written for one is likely to fail to work correctly with the other so you have to be able to specify which one you mean If you have both MinGW and Cygwin installed you may be tempted to use Cygwin s superior command line environment over a Windows command shell or MSYS If you re like me you type make reflexively now typing mingw32 make instead isn t going to work Another problem with having Cygwin and MinGW on the same system is that this puts a sh exe program in your system s PATH which makes MinGW make send shell commands to it instead of cmd exe as it normally would I find it best to set up a special MinGW environment to avoid problems stemming from these platform differences I ve created a pair of scripts that let me work in Cygwin mode most of the time and temporarily drop down into MinGW mode only when necessary I call the first script mingw and put it somewhere in the Cygwin PATH bin sh PATH cygdrive c mingw bin cygdrive c windows cygdrive c windows system32 cygdrive c cygwin bin echo Say exit to lea
68. can t run without it lt lt endl return 1 poolptr gt release cp catch mysqlpp Exception amp e cerr lt lt Failed to set up initial pooled connection lt lt e what lt lt endl return 1 Setup complete Now let s spin some threads cout lt lt endl lt lt Pool created and working correctly Now to do some real work lt lt endl srand time 0 bool running true true true true true true true true true true true true true true const size t num_threads sizeof running sizeof running 0 size t i for i 0 i lt num threads i if int err create_thread worker_thread running i cerr lt lt Failed to create thread lt lt i lt lt error code lt lt err lt lt endl return 1 Test the running flags every second until we find that they re all turned off indicating that all threads are stopped cout put W cout flush indicate waiting for completion do sleep 1 i 0 while i lt num threads amp amp running i i while i lt num_threads cout lt lt endl lt lt All threads stopped lt lt endl Shut it all down delete poolptr cout lt lt endl else void argc warning squisher cout lt lt argv 0 lt lt requires that threads be enabled lt lt endl endif return 0 The example works with both Windows native threads and with POSIX threads
69. cky thing about setting options is that only a few of them can be set after the connection is up Most need to be set just as shown in the example above create an unconnected Connection object set your connection options and only then establish the connection The option setting mechanism takes care of applying the options at the correct time in the connection establishment sequence If you re familiar with setting connection options in the MySQL C API you ll have to get your head around the fact that MySQL s connection option mechanism is a much simpler higher level design that doesn t resemble the C API in any way The C API has something like half a dozen different mechanisms for setting options that control the connection The flexibility of the C type system allows us to wrap all of these up into a single high level mechanism while actually getting greater type safety than the C API allows 3 13 Getting Field Meta Information The following example demonstrates how to get information about the fields in a result set such as the name of the field and the SQL type This is examples fieldinf cpp 26 MySQL v3 0 9 User Manual include cmdline h include printdata h include lt iostream gt include lt iomanip gt using namespace std Access the flag that s set when running under the dtest framework so we modify our output to be testable extern bool dtest_mode int main int argc char argv
70. columns or template query parameters first look to see if there s a good way to change the design It s usually a sign of too many unrelated things in one table if you need so many columns If you decide the design is good you can raise these limits by re generating the Lib ssqls h and or lib querydef h headers using Perl scripts with the same name except with a p1 extension Instructions for this are at the top of each script If you re on a platform that uses Autoconf o you can change these scripts like you would any other part of the library After making your changes just say make to rebuild the library including these headers This requires a Perl interpreter on the system to work but Perl is nearly ubiquitous on systems that also use autoconf these days On all other platforms you ll have to rebuild these headers by running Perl by hand Just say perl ssqls pl or perl querydef pl in the 1ib subdirectory of the MySQL sources then build the library as you normally would 8 3 Buried MySQL C API Headers It s common these days on Unixy systems to install the MySQL C API headers in a mysql directory under some common include directory If the C API headers are in usr include mysql we say they are buried under neath the system s main include directory usr include Since the MySQL headers depend on these C API headers it can be useful for MySQL to know this fact Linux Solaris the BSDs Mac OS X command line as
71. ctors but our derived class defines only one causing that one to hide all of the ones in the base class Many of the MySQL 51 MySQL v3 0 9 User Manual mechanisms that use SSQLSes rely on having these contructors so our Derived above is not a Base and so it isn t an SSQLS If you try to use Derived as an SSQLS you ll get compiler errors wherever MySQL tries to access one of these other constructors There s another minor flaw as well Our lone constructor above takes its parameters by value but the corresponding constructor in the SSQLS takes them by const reference Our derived class has technically hidden a fourth base class constructor this way but this particular case is more a matter of efficiency than correctness Code that needs the full creation constructor will still work with our code above but passing stringish types like sq1_ varchar by value instead of by const reference is inefficient This is the corrected version of the above code sql_create_2 Base 1 2 mysqlpp sql_ varchar a mysqlpp sql_int b class Derived public Base public default constructor Derived Base 0 for comparison constructor Derived const mysqlpp sql_varchar amp a Base _a full creation constructor Derived const mysqlpp sql_varchar amp _a const mysqlpp sql_int amp b Base _a _b 1 population constructor Derived const mysqlpp Row amp row
72. derive from mysql time and DateTime used to derive from both of those All three of these classes used to derive from mysql dt_base Allofthe mysql _ classes functionality and data has been folded into the leaf classes and now the only thing shared between them is their dependence on the DTbase template Since the leaf classes interface has not changed and end user code shouldn t have been using the other classes this shouldn t affect the API in any prac tical way mysql type_info now always initializes its private num member Previously this would go uninitialized if you used the default constructor Now there is no default ctor but the ctor taking one argument which sets num has a default 10 2 6 v3 0 0 Removed reset_query parameters from Query member functions None of these have been honored at least going back to v1 7 9 so this is not an API change As of this version Query now automatically detects when it can safely reset itself after executing a query so it s not necessary to ask for a reset except when using template queries Removed overloads of Query execute store and use that take only a const char This is not an API change because there was an equivalent call chain for this already This change just snaps a layer of indirection Query error is now const and returns const char instead of a std string by value Removed Lockable mechanism as it was conceptually flawed Connection and Query conse
73. dinary GNU General Public License has appeared then you can specify that version instead if you wish Do not make any other change in these notices Once this change is made in a given copy it is irreversible for that copy so the ordinary GNU General Public License applies to all subsequent copies and derivative works made from that copy This option is useful when you wish to copy part of the code of the Library into a program that is not a library 4 You may copy and distribute the Library or a portion or derivative of it under Section 2 in object code or executable form under the terms of Sections and 2 above provided that you accompany it with the complete corresponding ma chine readable source code which must be distributed under the terms of Sections and 2 above on a medium custom arily used for software interchange If distribution of object code is made by offering access to copy from a designated place then offering equivalent access to copy the source code from the same place satisfies the requirement to distribute the source code even though third parties are not compelled to copy the source along with the object code 87 MySQL v3 0 9 User Manual 5 A program that contains no derivative of any portion of the Library but is designed to work with the Library by being compiled or linked with it is called a work that uses the Library Such a work in isolation is not a derivative work of the Library and
74. ding run time efficiency for flexibility here usually the right thing in a distributed system Since MySQL is a networked database server many uses of it will qualify as distributed systems You can t count on being able to update both the server s and all the clients at the same time so you have to make them flexible enough to cope with differences while the changes propagate As long as the new database schema isn t too grossly different from the old your programs should continue to run until you get around to updating them to use the new schema There s a danger that this quiet coping behavior may mask problems but considering that the previous behavior was for the program to crash when the database schema got out of synch with the SSQLS definition it s likely to be taken as an improvement 5 4 Adding data SSQLS can also be used to add data to a table This is examples ssqls2 cpp include cmdline h include printdata h include stock h include lt iostream gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 try Establish the connection to the database server mysqlpp Connection con db server user pass Create and populate a stock object We could also have used the set member which takes
75. dle Unicode data depends on whether you re using the native Windows API or the newer NET API First the native case Convert a C string in UTF 8 format to UCS 2 format void ToUCS2 LPTSTR pcOut int nOutLen const char kpcIn MultiByteToWideChar CP_UTF8 0 kpcIn 1 pcOut nOutLen Convert a UCS 2 string to C string in UTF 8 format void ToUTF8 char pcOut int nOutLen LPCWSTR kpcIn WideCharToMultiByte CP_UTF8 0 kpcIn 1 pcOut nOutLen 0 0 These functions leave out some important error checking so see examples vstudio mfc mfc_dlg cpp for the complete version If you re building a NET application such as perhaps because you re using Windows Forms it s better to use the NET libraries for this Convert a C string in UTF 8 format to a NET String in UCS 2 format String ToUCS2 const char utf8 1 return gcnew String utf8 0 strlen utf8 System Text Encoding UTF8 Convert a NET String in UCS 2 format to a C string in UTF 8 format System Void ToUTF8 char pcOut int nOutLen String sIn array lt Byte gt bytes System Text Encoding UTF8 gt GetBytes sIn nOutLen Math Min nO0utLen 1 bytes gt Length System Runtime InteropServices Marshal Copy bytes 0 IntPtr pcOut nOutLen pcOut nOutLen 0 Unlike the native API versions these examples are complete since the NET platform handles a lot of things behind the scenes for us
76. does anything with MySQL If a thread creates a new connection before it uses a connection created by another thread though it doesn t need to call Connection thread_start because the per thread resources this allocates are implicitly created upon creation of a connection if necessary This is why the simple Connect ion per thread strategy works each thread that uses MySQL creates a connection in that thread implicitly allocating the per thread resources at the same time You never need to call Connec tion thread_start in this instance It s not harmful to call this function just unnecessary A good counterexample is using ConnectionPool you probably do need to call Connec tion thread_start at the start of each worker thread because you can t usually tell whether you re getting a new connection from the pool or reusing one that another thread returned to the pool after allocating it It s possible to conceive of situations where you can guarantee that each pool user always allocates a fresh connection the first time it calls ConnectionPool grab but thread programming is complex enough that it s best to take the safe path and always call Connection thread_start early in each worker thread Finally there s the complementary method Connection thread_end Strictly speaking it s not necessary to call this The per thread memory allocated by the C API is small it doesn t grow over time and a typical
77. e 9 You are not required to accept this License since you have not signed it However nothing else grants you permission to modify or distribute the Library or its derivative works These actions are prohibited by law if you do not accept this License Therefore by modifying or distributing the Library or any work based on the Library you indicate your acceptance of this License to do so and all its terms and conditions for copying distributing or modifying the Library or works based on it 10 Each time you redistribute the Library or any work based on the Library the recipient automatically receives a license from the original licensor to copy distribute link with or modify the Library subject to these terms and conditions You may not impose any further restrictions on the recipients exercise of the rights granted herein You are not re sponsible for enforcing compliance by third parties with this License 11 If as a consequence of a court judgment or allegation of patent infringement or for any other reason not limited to patent issues conditions are imposed on you whether by court order agreement or otherwise that contradict the conditions of this License they do not excuse you from the conditions of this License If you cannot distribute so as to satisfy simultaneously your obligations under this License and any other pertinent obligations then as a consequence you may not distribute the Library at all For example if a
78. e original values are stock orig row row Change the stock object s item to use only 7 bit ASCII and to deliberately be wider than normal column widths printed by print stock _table row item Nuerenberger Bratwurst Form the query to replace the row in the stock table query update orig_row row Show the query about to be executed cout lt lt Query lt lt query lt lt endl Run the query with execute since UPDATE doesn t return a result set query execute Retrieve and print out the new table contents print_stock_table query catch const mysqlpp BadQuery amp er Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const mysqlpp BadConversion amp er Handle bad conversions cerr lt lt Conversion error lt lt er what lt lt endl lt lt tretrieved data size lt lt er retrieved lt lt actual size lt lt er actual_size lt lt endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 return 0 Don t forget to run resetdb after running the example 5 6 Less than comparable SSQLS structures can be sorted and stored in STL associative containers as demonstrated in the next example This is examples ssqls4 cpp include cmdline h inc
79. e C s printf function you set up a fixed query string with tags inside that indicate where to insert the variable parts If you have multiple queries that are structurally similar you simply set up one template query and use that in the various locations of your program A third method for building queries is to use Query with SSQLS This feature lets you create C structures that mirror your database schemas These in turn give Query the information it needs to build many common SQL queries for you It can INSERT REPLACE and UPDATE rows in a table given the data in SSQLS form It can also generate SELECT FROM SomeTable queries and store the results as an STL collection of SSQLSes 2 3 Result Sets The field data in a result set are stored in a special std string like class called String This class has conversion operators that let you automatically convert these objects to any of the basic C data types Additionally MySQL defines classes like DateTime which you can initialize from a MySQL DATETIME string These automatic conversions are protected against bad conversions and can either set a warning flag or throw an exception depending on how you set the library up As for the result sets as a whole MySQL has a number of different ways of representing them MySQL v3 0 9 User Manual 2 3 1 Queries That Do Not Return Data Not all SQL queries return data An example is CREATE TABLE For these types of queries there
80. e form the data is in just before the SSQLS mechanism populates the structure Thus when the data is copied from the internal MySQL data structures into your SSQLS you get a direct copy of the String object s contents without interference Because C strings handle binary data just fine you might think you can use std string instead of sq1_blob but the current design of String converts to std string via aC string As a result the BLOB data is truncated at the first embedded null character during population of the SSQLS There s no way to fix that without completely redesigning either String or the SSQLS mechanism The sq1_blob typedef may be changed to alias a different type in the future so using it instead of String ensures that your code tracks these library changes automatically Besides St ring is only intended to be an internal mechanism within MySQL The only reason the layering is so thin here is because it s the only way to prevent BLOB data from being corrupted while avoiding that looming redesign effort You can see this technique in action in the cgi_ jpeg example include lt mysql h gt include lt ssqls h gt define IMG DATABASE mysql _cpp data define IMG HOST localhost define IMG_USER root define IMG PASSWORD nunyabinness sql_create_2 images Te De mysqlpp sql_int_unsigned id mysqlpp sql_blob data int main unsigned int img_id 0 char cgi_query getenv QUERY STRING
81. e of being more powerful so it let us replace several existing things within Connection with new options e Replaced enable _ss1 withSslOption e Replaced the compress parameter to the Connection create and connect constructor and Connection con nect method with CompressOption e Replaced the connect_timeout parameter with ConnectTimeoutOption Defined Option subclasses for each of the flags you would previously set using the client_flag parameter There are about a dozen of these so instead of listing them look in 1ib options h for something with a sim ilar name 76 MySQL v3 0 9 User Manual Collapsed Connection s host port and socket_name parameters down into a new combined server parameter which is parsed to determine what kind of connection you mean These interfaces are still compatible with v2 3 and earlier up through the port parameter Moved Connection affected_rows info and insert _id methods to class Query as they relate to the most recently executed query Changed the return type of Connection ping from int to bool If you were calling ping in bool context or using its return value in bool context you will need to reverse the sense of the test because the previous return code used zero to mean success Now it returns true to indicate success Renamed several methods e Use client version instead of api_version orclient_info e Use ipc_version instead of host_info e U
82. e paths to the v1 7 behavior A better fix is to rework your program to avoid or deal with the new exceptions All custom MySQL exceptions now derive from the new Exception interface The practical upshot of this is that the variability between the various exception types has been eliminated For instance to get the error string the BadQuery exception had a string member called error plus a method called what Both did the same thing and the what method is more common so the error string was dropped from the interface None of the example programs had to be changed to work with the new exceptions so if your program handles MySQL exceptions the same way they do your program won t need to change either Renamed SQLQueryNEParams exception to BadParamCount to match style of other exception names Added BadOption ConnectionFailed DBSelectionFailed EndOfResults EndOfResultSets LockFailed and Ob jectNotInitialized exception types to fix overuse of BadQuery Now the latter is used only for errors on query execution If your program has a catch all block taking a std exception for each try block containing MySQL statements you probably won t need to change your program Otherwise the new exceptions will likely show up as program crashes due to unhandled exceptions 10 1 4 3 Query class changes In previous versions Connection had a querying interface similar to class Query s These methods were intended only for Q
83. ecause this example modifies the sample database you may want to run resetdb after running this program 5 5 Modifying data It almost as easy to modify data with SSQLS as to add it This is examples ssqls3 cpp include cmdline h include printdata h include stock h include lt iostream gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 try Establish the connection to the database server mysqlpp Connection con db server user pass Build a query to retrieve the stock item that has Unicode characters encoded in UTF 8 form mysqlpp Query query con query select from stock where item Niirnberger Brats Retrieve the row throwing an exception if it fails mysqlpp StoreQueryResult res query store if res empty 43 MySQL v3 0 9 User Manual throw mysqlpp BadQuery UTF 8 bratwurst item not found in table run resetdb Because there should only be one row in the result set there s no point in storing the result in an STL container We can store the first row directly into a stock structure because one of an SSQLS s constructors takes a Row object stock row res 0 Create a copy so that the replace query knows what th
84. efine IMG HOST localhost define IMG_USER root define IMG PASSWORD nunyabinness sql_create_2 images Te De mysqlpp sql_int_unsigned id mysqlpp sql_ blob data int main unsigned int img_id 0 char cgi_query getenv QUERY STRING if cgi_query if strlen cgi_ query lt 4 memcmp cgi_query id 3 std cout lt lt Content type text plain lt lt std endl lt lt std endl std cout lt lt ERROR Bad query string lt lt std endl return 1 else img_id atoi cgi_query 3 else std cerr lt lt Put this program into a web server s cgi bin directory then lt lt std endl std cerr lt lt invoke it with a URL like this lt lt std endl std cerr lt lt std endl std cerr lt lt http server name com cgi bin cgi_jpeg id 2 lt lt std endl std cerr lt lt std endl std cerr lt lt This will retrieve the image with ID 2 lt lt std endl std cerr lt lt std endl std cerr lt lt You will probably have to change some of the defines at the top of lt lt std endl std cerr lt lt examples cgi_jpeg cpp to allow the lookup to work lt lt std endl return 1 try mysqlpp Connection con IMG DATABASE IMG HOST IMG_USER IMG_PASSWORD mysqlpp Query query con query query lt lt SELECT FROM images WHERE id lt lt img_id mysqlpp UseQueryResult res query use if res images img res fe
85. efore establishing the server connection and options are always set using this one interface If you re familiar with the underlying C API you know that there is poor consistency on these matters MySQL abstracts these differences away Connection con con set_option new MultiStatementsOption true Connect to the database if con connect db server user pass return 1 Set up query with multiple queries Query query con query query lt lt DROP TABLE IF EXISTS test_table lt lt CREATE TABLE test_table id INT lt lt INSERT INTO test_table VALUES 10 lt lt UPDATE test_table SET id 20 WHERE id 10 lt lt SELECT FROM test_table lt lt DROP TABLE test_table cout lt lt Multi query lt lt endl lt lt query lt lt endl Execute statement and display all result sets print_multiple results query if MYSQL VERSION _ID gt 50000 If it s MySQL v5 0 or higher also test stored procedures which return their results the same way multi queries do query lt lt DROP PROCEDURE IF EXISTS get_stock lt lt CREATE PROCEDURE get_stock lt lt i item varchar 20 lt lt 25 MySQL v3 0 9 User Manual BEGIN lt lt SET i_item concat i_item lt lt SELECT FROM stock WHERE lower item like lower i_item lt lt END 5 cout lt lt Stored procedure query lt lt endl lt lt query lt
86. en requested 7 2 Connection Management The MySQL C API underpinning MySQL does not allow multiple concurrent queries on a single connection You can run into this problem in a single threaded program too which is why we cover the details elsewhere in Section 3 16 Concurrent Queries on a Connection It s a thornier problem when using threads though The simple fix is to just create a separarate Connection object for each thread that needs to make database queries This works well if you have a small number of threads that need to make queries and each thread uses its connection often enough that the server doesn t time out waiting for queries If you have lots of threads or the frequency of queries is low the connection management overhead will be excessive To avoid that we created the ConnectionPool class It manages a pool of Connection objects like library books a thread checks one out uses it and then returns it to the pool as soon as it s done with it This keeps the number of active connections low ConnectionPool has three methods that you need to override in a subclass to make it concrete create destroy andmax_idle_ time These overrides let the base class delegate operations it can t successfully do itself to its subclass The ConnectionPool can t know how to create the Connection objects because that depends on how your program gets login parameters server information etc ConnectionPool1 also
87. er The only modern Unixy system I m aware of where Perl isn t installed by default is Cygwin and it s just a setup exe choice away there You ll probably only have to download and install a Perl interpreter if you re on Windows and don t want to use Cygwin If you re on a system that uses autoconf building MySQL automatically updates ssqls h any time ssqls pl changes Otherwise you ll need to run the Perl interpreter by hand c mysql gt cd lib c lib gt perl ssqls pl 5 13 Deriving from an SSQLS Specialized SQL Structures make good base classes They re simple and have few requirements on any class that derives from them There are some gotchas to look out for however Consider this sql_create_2 Base 1 2 mysqlpp sql_ varchar a mysqlpp sql_int b class Derived public Base public constructor Derived mysqlpp sql_varchar _a mysqlpp sql_int _b Base _a _b functionality added to the SSOLS through inheritance bool do_something_interesting int data y We ve derived a class from an SSQLS in order to add a method to it Easy right Sadly too easy The code has a rather large flaw which makes our derived class unusable as an SSQLS In C if a derived class has a function of the same name as one in the base class the base class versions of that function are all hidden by those in the derived class This applies to constructors too an SSQLS defines several constru
88. ersion of STL has The proper technique is mysqlpp Row row mysqlpp StoreQueryResult size type i for i 0 i lt res num_rows i row res i Do something with row here Or in a more C ish idiom mysqlpp Row row mysqlpp StoreQueryResult const_iterator it for it res begin it res end it row it Do something with row here Row class changes Removed Row raw_data raw_size and raw_string These were useful with BLOB data back when MySQL didn t handle embedded null characters very well and when copies of ColData objects were ex pensive Neither is true now so they have no value any more Equivalent calls are mysqlpp String s row 0 s data raw_data equivalent s length raw_size equivalent std string s data s length raw_string equivalent Row operator const char would unconditionally throw a BadFieldName exception when you asked for a field that doesn t exist Now if exceptions are disabled on the Row object it just returns a reference to an empty String object You can tell when this happens because such an object tests as false in bool context Specialized SQL Structure SSQLS changes Renamed custom to ssqls There is a backwards compatibility header custom h which includes ssqls h for you but it will go away in a future version of MySQL SSQLSes get populated by field name now not by field order In v2 it was
89. ery errnum or Connection errnum same thing but what you ll almost certainly get instead is 0 meaning no error Why It s because you re probably using a Transaction object to get automatic roll backs in the face of exceptions In this case the roll back happens before your exception handler is called by issuing a ROLLBACK query to the database server Thus QUery er rnum returns the error code associated with this roll back query not the deadlocked transaction that caused the exception 16 MySQL v3 0 9 User Manual To avoid this problem a few of the exception objects as of MySQL v3 0 include this last error number in the exception object itself It s populated at the point of the exception so it can differ from the value you would get from Query errnum later on when the exception handler runs The example examples deadlock cpp demonstrates the problem include cmdline h include lt mysql h gt include lt mysqld_error h gt include lt iostream gt using namespace std Bring in global holding the value given to the m switch extern int run_mode int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 Check that the mode parameter was also given and it makes sense if run_mode
90. ery storein SSOLS const Row amp other etc worked fine regardless of whether your SSQLS field names matched those in the corresponding SQL table because the SSQLS was populated by position not by field name Thus if all you used SSQLS for was data retrieval you could define your structures with sq1_create_ in v2 This was never recommended because such an SSQLS wouldn t work with other features of MySQL like Query insert because they depend on being able to map names from C to SQL and back You needed to use sql_create_c_names_ to make these features work in v2 in the face of a naming scheme difference between C and SQL These typedefs have been available since MySQL v2 1 81 MySQL v3 0 9 User Manual Removed success in Connection Query and SimpleResult ne ResNSel and simply made these classes testable in bool context to get the same information An additional change in Connection is that it used to be considered unsuccessful when the connection was down Since the sense of this test is now whether the object is in a good state it only returns false when the connection attempt fails Call Connection is connected if you just want to test whether the connection is up The debug mode build of the library now has a _d suffix for Visual C and Xcode This lets you have both versions installed without conflict The release build uses the current naming scheme If you have an existing program build
91. estions if it knows it won t result in syntactically incorrect SQL It s also important to realize that quoting and escaping in Query streams and template queries is never implicit You must use manipulators and template query flags as necessary to tell MySQL where quoting and escaping is necessary It would be nice if MySQL could do quoting and escaping implicitly based on data type but this isn t possible in all cases Since MySQL can t reliably guess when quoting and escaping is appropriate and the programmer doesn t need tof MySQL makes you tell it 3 6 C Equivalents of SQL Column Types MySQL declares a C typedef corresponding to almost every data type MySQL understands They re in lib sgl types h The typedefs begin with sql_ and end with a lowercase version of the standard SQL type name with spaces replaced by underscores For instance the SQL type TINYINT UNSIGNED is represented in MySQL by mysqlpp sql _tinyint unsigned MySQL doesn t force you to use these typedefs It tries to be flexible with regard to data conversions so you could probably use int anywhere you use mysglpp sql _tinyint unsigned for example That said the MySQL typedefs give several advantages ISoLQueryParms is used as a stream only as an implementation detail within the library End user code simply sees it as a std vector de rivative By contrast the Quer y methods that take an SSQLS do add quotes and escape strings
92. hat connection behaves The defaults are sufficient for most programs so only one of the MySQL example programs make any connection option changes Here is examples multiquery cpp include cmdline h include printdata h include lt mysql h gt include lt iostream gt include lt iomanip gt include lt vector gt using namespace std using namespace mysqlpp typedef vector lt int gt IntVectorType static void 23 MySQL v3 0 9 User Manual print_header IntVectorType amp widths StoreQueryResult amp res cout lt lt lt lt setfill for size_t i 0 i lt res field names gt size i 4 cout lt lt lt lt setw widths at i lt lt res field name i lt lt cout lt lt endl static void print_row IntVectorType amp widths Row amp row cout lt lt lt lt setfill for size_t i 0 i lt row size i cout lt lt lt lt setw widths at i lt lt row i lt lt cout lt lt endl static void print_row_separator IntVectorType amp widths cout lt lt lt lt setfill for size t i 0 i lt widths size i cout lt lt lt lt setw widths at i lt lt lt lt cout lt lt endl static void print_result StoreQueryResult amp res int index Show how many rows are in result if any StoreQueryResult size type num_results res size if res amp
93. he full result set and then picking over it to find the rows you want to keep use Query store_if This is examples store_if cpp include cmdline h include printdata h include stock h include lt mysql h gt include lt iostream gt include lt math h gt Define a functor for testing primality struct is prime bool operator const stock amp s if s num 2 s num 3 return true 2 and 3 are trivial cases else if s num lt 2 s num 2 0 return false can t be prime if lt 2 or even else The only possibility left is that it s divisible by an odd number that s less than or equal to its square root for int i 3 i lt sqrt double s num i 2 if s num i 0 20 MySQL v3 0 9 User Manual return false return true y int main int argc char argv 1 Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 try Establish the connection to the database server mysqlpp Connection con db server user pass Collect the stock items with prime quantities std vector lt stock gt results mysqlpp Query query con query query store if results stock is_prime Show the results print_stock_header results size std vector lt stock gt const_iterator
94. his isn t always the best way to get something done When you need to mix code and a query MySQL s Query for_each facility might be just what you need This is ex amples for_each cpp include cmdline h include printdata h include stock h include lt mysql h gt include lt iostream gt include lt math h gt Define a functor to collect statistics about the stock table class gather _stock_stats public gather _stock_stats items 0 weight _ 0 cost_ 0 void operator const stock amp s items s num weight_ s num s weight cost_ s num s price private mysqlpp sql_bigint items mysqlpp sql_ double weight_ cost_ friend std ostream amp operator lt lt std ostream amp os const gather_stock_stats amp ss y Dump the contents of gather_stock_stats to a stream in human readable form std ostream amp operator lt lt std ostream amp os const gather stock _statsg ss 1 os lt lt ss items_ lt lt items lt lt weighing lt lt ss weight_ lt lt stone and lt lt costing lt lt ss cost_ lt lt cowrie shells return os int main int argc char argv Get database access parameters from command line 22 MySQL v3 0 9 User Manual const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db amp Server amp user amp pass return 1 try
95. ice lt lt endl It lacks a certain syntactic elegance but it has its uses MySQL v3 0 9 User Manual 2 4 Exceptions By default the library throws exceptions whenever it encounters an error You can ask the library to set an error flag instead if you like but the exceptions carry more information Not only do they include a string member telling you why the exception was thrown there are several exception types so you can distinguish between different error types within a single try block MySQL v3 0 9 User Manual 3 Tutorial The previous chapter introduced the major top level mechanisms in MySQL Now we ll dig down a little deeper and get into real examples We start off with the basics that every MySQL program will have to deal with then work up to more complex topics that are still widely interesting You can stop reading the manual after this chapter and still get a lot out of MySQL ignoring the more advanced parts we present in later chapters 3 1 Running the Examples All of the examples are complete running programs If you built the library from source the examples should have been built as well If you use RPMs instead the example programs source code and a simplified Makefile are in the mysql devel package They are typically installed in usr share doc mysql devel ex amples but it can vary on different Linuxes Before you get started please read through any of the READM
96. ies and so can re use that vector object Many of these methods accept manipulators and custom delimiters The defaults are suitable for building SQL queries but if you re using these methods in a different context you may need to override these defaults For instance you could use these methods to dump data to a text file using different delimiters and quoting rules than SQL At this point we ve seen all the major aspects of the SSQLS feature The final sections of this chapter look at some of the peripheral aspects 5 10 Having Different Field Names in C and SQL There s a more advanced SSQLS creation macro which all the others are built on top of Currently the only feature it adds over what s described above is that it lets you name your SSQLS fields differently from the names used by the database server Perhaps you want to use Hungarian notation in your C program without changing the SQL database schema sql_create_complete 5 stock 1 5 mysqlpp sql_char m_sItem item mysqlpp sql_bigint m_nNum num mysqlpp sql_double m_fWeight weight mysqlpp sql_ decimal m_fPrice price mysqlpp sql_date m_Date sdate 5 11 Expanding SSQLS Macros If you ever need to see the code that a given SSQLS declaration expands out to use the utility doc ssqls pretty like so doc ssqls pretty lt myprog cpp less This Perl script locates the first SSQLS declaration in that file then uses the C preprocessor to
97. implicitly It can do this because SSQLS knows all the SQL code and data types so it never has to guess whether quoting or escaping is appropriate Unless you re smarter than I am you don t immediately see why explicit manipulators are necessary We can tell when quoting and escaping is not appropriate based on type so doesn t that mean we know when it is appropriate Alas no For most data types it is possible to know or at least make an awfully good guess but it s a complete toss up for C strings const char A C string could be either a literal string of SQL code or it can be a value used in a query Since there s no easy way to know and it would damage the library s usability to mandate that C strings only be used for one purpose or the other the library requires you to be explicit 4One hopes the programmer knows 13 MySQL v3 0 9 User Manual e Space efficiency the MySQL types are no larger than necessary to hold the MySQL data e Portability if your program has to run on multiple different system types even just 32 and 64 bit versions of the same OS and processor type using the MySQL typedefs insulates your code from platform changes e Clarity using C types named similarly to the SQL types reduces the risk of confusion when working with code in both languages at the same time e Compatibility using the MySQL types ensures that data conversions between SQL and C forms are compatible Naive use of
98. ing against MySQL on these platforms you ll need to change your build options to use the new name in debug mode Renamed NO_LONG_LONGS to MYSQLPP_NO_LONG_LONGS to avoid a risk of collision in the global macro namespace 10 1 8 v3 0 7 Most MySQL classes with at or operator methods now throw the new BadIndex exception when you pass an out of range index These methods variously either did not check their indices or threw std out_of range when passed a bad index The one exception that comes to mind is Fields which is just a typedef for a specialization of std vector and the Standard has its own rules for index checking 10 2 ABI Changes This section documents those library changes that require you to rebuild your program so that 1t will link with the new library Most of the items in the previous section are also ABI changes but this section is only for those items that shouldn t require any code changes in your program If you were going to rebuild your program after installing the new library anyway you can probably ignore this section 10 2 1 v1 7 18 The Query classes now subclass from stringstream instead of the deprecated strstream 10 2 2 v1 7 19 Fixed several const incorrectnesses in the Query classes 10 2 3 v1 7 22 Removed reset query parameters from several Query class members This is not an API change because the para meters were given default values and the library would ignore a
99. ion must still compute square roots These requirements apply to the modified work as a whole If identifiable sections of that work are not derived from the Library and can be reasonably considered independent and separate works in themselves then this License and its terms do not apply to those sections when you distribute them as separate works But when you distribute the same sections as part of a whole which is a work based on the Library the distribution of the whole must be on the terms of this License whose permissions for other licensees extend to the entire whole and thus to each and every part regardless of who wrote it Thus it is not the intent of this section to claim rights or contest your rights to work written entirely by you rather the intent is to exercise the right to control the distribution of derivative or collective works based on the Library In addition mere aggregation of another work not based on the Library with the Library or with a work based on the Library on a volume of a storage or distribution medium does not bring the other work under the scope of this License 3 You may opt to apply the terms of the ordinary GNU General Public License instead of this License to a given copy of the Library To do this you must alter all the notices that refer to this License so that they refer to the ordinary GNU General Public License version 2 instead of to this License If a newer version than version 2 of the or
100. ir rights We protect your rights with a two step method 1 we copyright the library and 2 we offer you this license which gives you legal permission to copy distribute and or modify the library To protect each distributor we want to make it very clear that there is no warranty for the free library Also if the library is modified by someone else and passed on the recipients should know that what they have is not the original version so that the original author s reputation will not be affected by problems that might be introduced by others Finally software patents pose a constant threat to the existence of any free program We wish to make sure that a company cannot effectively restrict the users of a free program by obtaining a restrictive license from a patent holder Therefore we insist that any patent license obtained for a version of the library must be consistent with the full freedom of use specified in this license Most GNU software including some libraries is covered by the ordinary GNU General Public License This license the GNU Lesser General Public License applies to certain designated libraries and is quite different from the ordinary General Public License We use this license for certain libraries in order to permit linking those libraries into non free programs When a program is linked with a library whether statically or using a shared library the combination of the two is legally speaking a combined work
101. l else cout lt lt endl lt lt Sorry no hotdog buns in stock lt lt endl catch const mysqlpp BadQuery amp er Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const mysqlpp BadConversion amp er Handle bad conversions cerr lt lt Conversion error lt lt er what lt lt endl lt lt tretrieved data size lt lt er retrieved lt lt actual size lt lt er actual_size lt lt endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions 45 MySQL v3 0 9 User Manual cerr lt lt Error lt lt er what lt lt endl return 1 return 0 The find call works because of the way the SSQLS was declared It s properly covered elsewhere but suffice it to say the 1 in the declaration of stock above tells it that only the first field needs to be checked in comparing two SSQLSes In database terms this makes it the primary key Therefore when searching for a match our exemplar only had to have its first field populated 5 7 Additional Features of Specialized SQL Structures Up to this point we haven t been using all of the features in the SSQLS structures we ve been generating What else can we do with SSQLSes Consider this query insert s This does exactly what you think it does it builds an INSERT query to insert the content
102. l h gt include lt iostream gt include lt iomanip gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 Connect to the sample database mysqlpp Connection conn false if conn connect db server user pass Ask for all rows from the sample stock table and display them Unlike simple2 example we retreive each row one at a time instead of storing the entire result set in memory and then iterating over it mysqlpp Query query conn query select from stock if mysqlpp UseQueryResult res query use Display header cout setf ios left cout lt lt setw 31 lt lt Item lt lt setw 10 lt lt Num lt lt setw 10 lt lt Weight lt lt setw 10 lt lt Price lt lt Date lt lt endl lt lt endl Get each row in result set and print its contents while mysqlpp Row row res fetch_row cout lt lt setw 30 lt lt row item lt lt lt lt setw 9 lt lt row num lt lt lt lt setw 9 lt lt row weight lt lt lt lt setw 9 lt lt row price lt lt lt lt setw 9 lt lt row sdate lt lt endl Check for error can t distinguish end of results and error cases in return from fetch
103. le for building that program CXX g CXXFLAGS I usr include mysql I usr local include mysql LDFLAGS L usr local lib lmysqlpp lmysqlclient 69 MySQL v3 0 9 User Manual EXECUTABLE hello all EXECUTABLE clean rm f EXECUTABLE o The first three lines are where all of the assumptions about file and path names are laid out Probably at least one of these assumptions isn t true for your system and so will require changing The trickiest line is the third one MySQL programs need to get built against both the MySQL and MySQL lib raries because MySQL is built on top of the MySQL C API library If you re building a threaded program use lmysqliclient_r instead See Section 7 Using MySQL in a Multithreaded Program for more details on building thread aware programs On some systems the order of libraries in the LDFLAGS line is important these linkers collect symbols from right to left so the rightmost library needs to be the most generic In this example MySQL depends on MySQL so the MySQL C API library is rightmost You might need to add more libraries to the LDFLAGS line 1ns1 1z and 1m are common If you study how MySQL itself gets built on your system you can see what it uses and emulate that Beyond that we have a pretty vanilla Makefile We don t have any special dependency or build rules because the default rules should work fine particularly if you re using
104. lude printdata h 44 MySQL v3 0 9 User Manual include stock h include lt iostream gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 try Establish the connection to the database server mysqlpp Connection con db server user pass Retrieve all rows from the stock table and put them in an STL set Notice that this works just as well as storing them in a vector which we did in ssqlsl cpp It works because SSQLS objects are less than comparable mysqlpp Query query con query select from stock set lt stock gt res query storein res Display the result set Since it is an STL set and we set up the SSQLS to compare based on the item column the rows will be sorted by item print_stock_header res size set lt stock gt iterator it cout precision 3 for it res begin it res end it print_stock_row it gt item c_str it gt num it gt weight it gt price it gt sdate Use set s find method to look up a stock item by item name This also uses the SSQLS comparison setup it res find stock Hotdog Buns if it res end cout lt lt endl lt lt Currently lt lt it gt num lt lt hotdog buns in stock lt lt end
105. lude it directly include my _ssqls h If there are many modules that need the SSQLS adding all those defines can be a pain In that case it s easier if you flip the above pattern on its head File my_ssqls h if defined EXPAND MY SSQLS STATICS define MYSQLPP_SSQLS NO STATICS endif sql_create X Y Z the SSOLS definition File foo cpp a mere user of the SSQLS include my _ssqls h File my_ssqls cpp which owns the SSQLS define EXPAND MY SSOLS STATICS include my _ssqls h 5 9 Harnessing SSQLS Internals The sql_create macros define several methods for each SSQLS These methods are mostly for use within the library but some of them are useful enough that you might want to harness them for your own ends Here is some pseudocode showing how the most useful of these methods would be defined for the stock structure used in all the ssqls cpp examples Basic form template lt class Manip gt stock _value_list lt Manip gt value_list cchar d PAT Manip m mysqlpp quote const template lt class Manip gt stock_field_list lt Manip gt field_list cchar d Dp ep Manip m mysqlpp do_nothing const template lt class Manip gt stock _equal_list lt Manip gt equal_list cchar d cchar e Manip m mysqlpp quote const Boolean argument form template lt class Manip gt 47 MySQL v3 0 9 User Manual stock_cus_value_list lt Manip gt value_list cchar d Ma
106. makes the subclass destroy the Connection objects it created it could assume that they re simply allocated on the heap with new but it can t be sure so the base class delegates destruction too Finally the base class can t know what the connection idle timeout policy in the client would make the most sense so it asks its subclass via the max idle time method ConnectionPool also allows you to override release if needed For simple uses it s not necessary to override this In designing your ConnectionPool derivative you might consider making it a Singleton see Gamma et al since there should only be one pool in a program Here is an example showing how to use connection pools with threads include cmdline h include threads h include lt iostream gt using namespace std Define a concrete ConnectionPool derivative Takes connection parameters as inputs to its ctor which it uses to create the connections we re called upon to make Note that we also declare a global pointer to an object of this type which we create soon after startup this should be a common usage pattern as what use are multiple pools class SimpleConnectionPool public mysqlpp ConnectionPool public The object s only constructor SimpleConnectionPool const char db const char server const char user const char password Bay default current MySQL servers have an 8 hour idle timeout on connections
107. may also combine or link a work that uses the Library with the Library to produce a work containing portions of the Library and distribute that work under terms of your choice provided that the terms permit modification of the work for the customer s own use and reverse engineering for debugging such modifications You must give prominent notice with each copy of the work that the Library is used in it and that the Library and its use are covered by this License You must supply a copy of this License If the work during execution displays copyright notices you must include the copyright notice for the Library among them as well as a reference directing the user to the copy of this License Also you must do one of these things a Accompany the work with the complete corresponding machine readable source code for the Library including whatever changes were used in the work which must be distributed under Sections 1 and 2 above and if the work is an executable linked with the Library with the complete machine readable work that uses the Library as object code and or source code so that the user can modify the Library and then relink to produce a modified executable containing the modified Library It is understood that the user who changes the contents of definitions files in the Library will not neces sarily be able to recompile the application to use the modified definitions b Use a suitable shared library mechanism for li
108. mission In addition it is requested that 1 The modifications including deletions be noted 2 The authors be notified by email of the modification in advance of redistribution if an email address is provided in the document Mere aggregation of the MySQL User Manual with other documents or programs on the same media shall not cause this license to apply to those other works All translations derivative documents or modified documents that incorporate the MySQL User Manual may not have more restrictive license terms than these except that you may require distributors to make the resulting document available in source format 92
109. n version 2 of MySQL and earlier SOLTypeAdapter was called SOLString but it was confusing because its name and the fact that it derived from std string suggested that it was a general purpose string type MySQL even used it this way in a few places internally In v3 we made it a simple base class and renamed it to reflect its proper limited function SOLTypeAdapter doesn t do quoting and escaping itself That happens elsewhere right at the point that the STA gets used to build a query 28 MySQL v3 0 9 User Manual 3 14 2 String 3 14 3 If MySQL can be said to have its own generic string type it s String but it s not really functional enough for general use It s possible that in future versions of MySQL we ll expand its interface to include everything std string does so that s why it s called that The key thing String provides over std string is conversion of strings in SQL value formats to their plain old C data types For example if you initialize it with the string 2007 11 19 you can assign the String to a Date not because Date knows how to initialize itself from String but the reverse String has a bunch of implicit conversion operators defined for it so you can use it in any type context that makes sense in your application Because Row operator returns String you can say things like this int x row x In a very real sense String is the inverse of STA String conve
110. name of the table for an SSQLS instance in a single threaded program if it gets used for many operations over an extended span of code 5 8 Using an SSQLS in Multiple Modules It s convenient to define an SSQLS in a header file so you can use it in multiple modules You run into a bit of a problem though because each SSQLS includes a few static data members to hold information common to all structures of that type The table name and the list of field names When you include that header in more than one module you get a multiply defined symbol error at link time 46 MySQL v3 0 9 User Manual The way around this is to define the preprocessor macro MYSQLPP_SSOQLS NO STATICS in all but one of the modules that use the header definining the SSQLS When this macro is defined it suppresses the static data members in any SSQLS defined thereafter Imagine we have a file my_ssqls h which includes a sql_create_N macro call to define an SSQLS and that that SSQLS is used in at least two modules One we ll call foo cpp and we ll say it s just a user of the SSQLS it doesn t own it Another of the modules my_ssqls cpp uses the SSQLS more heavily so we ve called it the owner of the SSQLS If there aren t very many modules this works nicely File foo cpp which just uses the SSQLS but doesn t own it define MYSQLPP SSQLS NO STATICS include my_ssqls h File my_ssqls cpp which owns the SSQLS so we just inc
111. nctions that accept query template parameters In this example we re using Query execute Let s dig into this feature a little deeper 4 1 Setting up Template Queries To set up a template query you simply insert it into the Query object using numbered placeholders wherever you want to be able to change the query Then you call the parse function to tell the Query object that the query string is a template query and it needs to parse it query lt lt select 2 fieldl 3 field2 from stock where l wheref 0q what query parse The format of the placeholder is s modifier name Where is a number up to three digits It is the order of parameters given to a SQLQueryParms object starting from 0 modifier can be any one of the following Print an actual mai Don t quote or escape no matter what q This will escape the item using the MySQL C API function mysql escape string and add single quotes around it as necessary depending on the type of the value you use Q Quote but don t escape based on the same rules as for q This can save a bit of processing time if you know the strings will never need quoting name is for an optional name which aids in filling SQLQueryParms Name can contain any alpha numeric characters or the underscore You can have a trailing colon which will be ignored If you need to represent an actual colon after the name follow
112. need to change If you see this exception it does mean you need to look into your use of data types though The table that controls this is mysql_type_info types defined at the top of lib type info cpp Every data type in lib sql_types h has a corresponding record in this table so if you stick to those types you ll be fine It s also okay to use types your C compiler can convert directly to these predefined types The _table static member variable for each SSQLS is now private The recommended way to access this remains unchanged the table static member function table used to return a modifiable reference to the table name Now there are two overloads one which returns an unmodifiable pointer to the table name and the other which takes const char so you can override the default table name So the code we used to recommend for changing the SSQLS s table name my ssqls_type table MyTableName now needs to be my _ssqls_type table MyTableName 10 1 7 10 Miscellaneous changes MySQL does quoting and escaping much more selectively now Basically if the library can tell you re not building a SQL query using one of the standard methods it assumes you re outputting values for human consumption so it disables quoting and SQL escaping If you need to build your own mechanism to replace this quoting is easy to do and Query escape _string can do SQL escaping for you 181 MySQL v2 data retreival Qu
113. nfiguration For the Release configuration make it Multi threaded DLL MD e Append the following to Linker General Additional Library Directories for the Debug configuration C Program Files MySQL MySQL Server 5 0 lib debug C mysql vc debug For the Release configuration make it the same but change the debug directory names to opt e Under Linker Input add the following to Additional Dependencies for the Debug configuration Libmysql 1lib wsock32 1lib mysqlpp d lib amd then for the Release configuration Llibmysql lib wsock32 lib mysqlpp lib This difference is because MySQL s Debug DLL and import library have a _d suffix so you can have both in the same directory without conflicts You may want to study examples vstudio mfc mfc vcproj to see this in action Note that some of the paths will be different because it can use relative paths for mysqlpp d1l 9 1 2 Using MySQL in a Windows Forms C CLI Project Before you start work on getting MySQL working with your own program you need to make some changes to the MySQL build settings Open mysqlpp s1n then right click on the mysqlpp target and select Properties Make the following changes for both the Debug and Release configurations TMySQL has many more header files but don t include any of them directly mysql1 h includes all of them for you except ssqls h of course in the correct order 68 MySQL v3 0 9 User Manual
114. ng this time The fork was healed officially in 1999 but there s always a delay of a few years between the release of a new GCC and widespread adoption The post EGCS versions of GCC were only beginning to become popular by 2001 when development on MySQL halted As a result it became increasingly difficult to get MySQL to build cleanly as newer compilers came out Since MySQL uses templates heavily this affected end user programs as well MySQL code got included directly in your program so any warnings or errors it caused became your pro gram s problem As a result most of the patches contributed to the MySQL project during this period were to fix up standards com pliance issues Because no one was bothering to officially test and bless these patches you ended up with the worst aspects of a bazaar development model complete freedom of development but no guiding hand to select from the good stuff and reject the rest Many of the patches were mutually incompatible Some would build upon other patches so you had to apply them in the proper sequence Others did useful things but didn t give a fully functional copy of MySQL Figuring out which patch es to use was an increasingly frustrating exercise as the years wore on and newer GCCs became popular In early August of 2004 Warren Young got fed up with this situation and took over He released 1 7 10 later that month which did little more than make the code build with GCC 3 3 wi
115. nip m bool il bool i2 false bool i5 false const List form template lt class Manip gt stock_cus_value_list lt Manip gt value_list cchar d Manip m stock_enum il stock _enum i2 stock_NULL stock_enum i5 stock_NULL const Vector form template lt class Manip gt stock_cus_value_list lt Manip gt value_list cchar d Manip m vector lt bool gt i const Plus the obvious equivalents for field list and equal_list Rather than try to learn what all of these methods do at once let s ease into the subject Consider this code stock s Dinner Rolls 75 0 95 0 97 sql _date 1998 05 25 cout lt lt Value list lt lt s value_list lt lt endl cout lt lt Field list lt lt s field_list lt lt endl cout lt lt Equal list lt lt s equal_list lt lt endl That would produce something like Value list Dinner Rolls 75 0 95 0 97 1998 05 25 Field list item num weight price sdate Equal list item Dinner Rolls num 75 weight 0 95 price 0 97 sdate 1998 05 25 That is a value list is a list of data member values within a particular SSQLS instance a field list is a list of the fields columns within that SSQLS and an equal list is a list in the form of an SQL equals clause Just knowing that much it shouldn t surprise you to learn that Query insert is implemented more or less like this this lt lt
116. nking with the Library A suitable mechanism is one that 1 uses at run time a copy of the library already present on the user s computer system rather than copying library functions into the executable and 2 will operate properly with a modified version of the library if the user installs one as long as the modified version is interface compatible with the version that the work was made with c Accompany the work with a written offer valid for at least three years to give the same user the materials specified in Subsection 6a above for a charge no more than the cost of performing this distribution d If distribution of the work is made by offering access to copy from a designated place offer equivalent access to copy the above specified materials from the same place e Verify that the user has already received a copy of these materials or that you have already sent this user a copy 88 MySQL v3 0 9 User Manual For an executable the required form of the work that uses the Library must include any data and utility programs needed for reproducing the executable from it However as a special exception the materials to be distributed need not include anything that is normally distributed in either source or binary form with the major components compiler kernel and so on of the operating system on which the executable runs unless that component itself accompanies the executable It may happen that this requi
117. nly copies a pointer to the data buffer and increments its reference count If the object has new data assigned to it or it s otherwise modified it decrements its reference count and creates its own copy of the buffer This has a lot of practical import such as the fact that Row operator can return String by value and it s still efficient 3 15 Dealing with Binary Data The tricky part about dealing with binary data in MySQL is to ensure that you don t ever treat the data as aC string which is really easy to do accidentally C strings treat zero bytes as special end of string characters but they re not special at all in binary data Recent releases of MySQL do a better job of letting you keep data in forms that don t have this problem but it s still possible to do it incorrectly These examples demonstrate correct techniques The you used MySQL before v3 String used to be called ColData It was renamed because starting in v2 3 we began using it for holding more than just column data I considered renaming it SOLString instead but that would have confused old MySQL users to no end Instead I followed the example of Set MySQL s specialized std set variant SDuring the development of MySQL v3 0 I tried merging SOLTypeAdapter and String into a single class to take advantage of this The resulting class gave the C compiler the freedom to tie itself up in knots because it was then allowed to convert almost any d
118. nstead In v2 it was possible to have different SQL column names than SSQLS data member names while still using sql create if you only used SSQLS for data retrieval In v3 you must use sql_create complete for absolutely all uses of SSQLS when you want the C field names to differ from the SQL column names The new Nul1 lt T gt support in SSQLSes causes an internal compiler error in Visual C 2003 VC 2005 and newer have no trobule with it A poll on the mailing list says there aren t many people still stuck on this version so we just ifdef d out the SSQLS mechanism and all the examples that use it when built with VC 2003 If this affects you see Section 5 15 SSQLS and Visual C 2003 for suggestions on ways to cope If you are using types other than MySQL s sql_ ones 19 in your SSQLSes code that previously worked may now see TypeLookupFailed exceptions This can be thrown even if exceptions are otherwise disabled in MySQL This version of MySQL is stricter about mapping SQL to C type information and vice versa If the library can t find a suitable mapping from one type system to the other it throws this exception because its only other option would be to crash or raise an assertion This typically happens when building SQL queries so you can probably handle it the same way as if the subsequent query excecution failed If you re catching the generic mysql pp Exception your error handling code might not
119. ntally this change was made to allow better support for BLOB columns 10 1 6 v2 3 0 Connection set_option calls now set the connection option immediately instead of waiting until just before the connnection is actually established Code that relied on the old behavior could see unhandled exceptions since option setting errors are now thrown from a different part of the code You want to wrap the actual set_option call now not Connection connect FieldNames and FieldTypes are no longer exported from the library If you are using these classes directly from Visual C or MinGW your code won t be able to dynamically link to a DLL version of the library any more These are internal classes however so no one should be using them directly 75 MySQL v3 0 9 User Manual 10 1 7 10 1 7 1 10 1 7 2 v3 0 0 Class name changes Several classes changed names in this release e ColData is now String e NullisBlank is now NullIsBlank Note the capital J Similar changes for NullisNull and NullisZero e ResNSel is now SimpleResult e Result is now StoreQueryResult ResUse is now UseQueryResult e SQLString is now SQLTypeAdapter When first building existing code against this version you may find it helpful to define the macro MYSQLPP OLD CLASS NAMES in your program s build options This will turn on some macros that set up aliases for the new class names matching their corresponding old names Then when you
120. ny value other than the default So any program that tried to make them take another value wouldn t have worked anyway 10 2 4 v1 7 24 Some freestanding functions didn t get moved into namespace mysqlpp when that namespace was created This release fixed that It doesn t affect the API if your program s C source files say using namespace mysqlpp within them 10 2 5 v2 0 0 Removed Connection infoo I d call this an API change if I thought there were any programs out there actually using this 82 MySQL v3 0 9 User Manual Collapsed the Connection constructor taking a bool for setting the throw_exceptions flag and the default constructor into a single constructor using a default for the parameter Classes Connection and Query are now derived from the Lockable interface instead of implementing their own lock unlock functions In several instances functions that took objects by value now take them by const reference for efficiency Merged SQLQuery class s members into class Query Merged RowTemplate class s members into class Row Reordered member variable declarations in some classes The most common instance is when the private section was declared before the public section it is now the opposite way This can change the object s layout in memory so a program linking to the library must be rebuilt Simplified the date and time class hierarchy Date used to derive from mysql_date Time used to
121. o now this manual has only discussed MySQL in conjunction with the example programs that come with it This chapter covers the steps you need to take to incorporate MySQL into your own projects The first thing you have to do is include mysq1 h in each module that uses MySQL In modules that use SSQLS you also need to include ssqls h At this point your project probably won t compile and it certainly won t link The steps to fix this are dependent on your particular platform The rest of this chapter is broken up into several sections one for each major platform type These sections are independent of each other Feel free to ignore the sections for platforms you don t use 9 1 Visual C 9 1 1 Using MySQL in an MFC Project If you don t already have a project set up open Visual Studio say File New Project then choose Visual C MFC MFC Application Go through the wizard setting up the project as you see fit Once you have your project open right click on your top level executable in the Solution Explorer choose Properties and make the following changes Where it doesn t specify Debug or Release make the same change to both configur ations e Append the following to C C General Additional Include Directories C Program Files MySOL MySOL Server 5 0 include C mysql include e Under C C Code Generation change Runtime Library to Multi threaded Debug DLL MDd for the Debug co
122. obscure error message about Commands out of sync from the underlying C API library You get it ina MySQL exception unless you have exceptions disabled in which case you get a failure code and Connection error returns this message The easiest way to cause this error is in a multithreaded program where you have a single Connection object but allow multiple threads to issue queries on it Unless you put in a lot of work to synchronize access this is almost guaranteed to fail If you give each thread that issues queries has its own Connection object you can still run into trouble if you pass the data you get from queries around to other threads What can happen is that one of these child objects indirectly calls back to the Connection at a time where it s involved with another query There are other ways to run into trouble when sharing MySQL data structures among threads but the whole topic is complex enough to deserve its own chapter Section 7 Using MySQL in a Multithreaded Program It s possible to run into this problem in a single threaded program as well As discussed above Section 3 9 Which Query Type to Use one of the options MySQL offers for executing a query lets you issue the query then consume the rows one at a time on demand it s the use query If you don t consume all rows from a query before you issue another on that connection you are effectively trying to have multiple concurrent
123. olptr gt release cp Delay 1 4 seconds before doing it again Because this can delay longer than the idle timeout we ll occasionally force the creation of a new connection on the next loop sleep rand 3 4 1 Tell main that this thread is no longer running reinterpret_cast lt bool gt running flag false Release the per thread resources before we exit mysqlpp Connection thread_end return 0 endif int main int argc char argv if defined HAVE_THREADS Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 Create the pool and grab a connection We do it partly to test that the parameters are good before we start doing real work and partly because we need a Connection object to call thread_aware on to check that it s okay to start doing that real work This latter check should never fail on Windows but will fail on most other systems unless you take positive steps to build with thread awareness turned on See README txt for your platform poolptr new SimpleConnectionPool db server user pass try mysqlpp Connection cp poolptr gt grab 62 MySQL v3 0 9 User Manual if cp gt thread_aware cerr lt lt MySQL wasn t built with thread awareness lt lt argv 0 lt lt
124. on and pick the project root with the Browse Workspace button to set the working directory 72 MySQL v3 0 9 User Manual 10 Incompatible Library Changes This chapter documents those library changes since the epochal 1 7 9 release that break end user programs You can dig this stuff out of the ChangeLog but the ChangeLog focuses more on explaining and justifying the facets of each change while this section focuses on how to migrate your code between these library versions Since pure additions do not break programs those changes are still documented only in the ChangeLog 10 1 API Changes This section documents files functions methods and classes that were removed or changed in an incompatible way If your program uses the changed item you will have to change something in your program to get it to compile after upgrading to each of these versions 10 1 1 v1 7 10 Removed Row operator overloads except the one for size_type and added Row lookup_by_name to provide the subscript by string functionality In practical terms this change means that the row field syntax no longer works you must use the new lookup_by_name method instead Renamed the generated library on POSIX systems from libsqlplus to Libmysqlpp 10 1 2 v1 7 19 Removed SQLQuery operator and the same for its Query subclass Use the copy constructor instead if you need to copy one query to another query object 10 1 3 v1 7
125. on held by StoreQueryResult s other base class ResultBase however store queries are convenient but the cost of keeping the entire result set in main memory can sometimes be too high It can be surprisingly costly in fact A MySQL database server stores data compactly on disk but it returns query 18 MySQL v3 0 9 User Manual data to the client in a textual form This results in a kind of data bloat that affects numeric and BLOB types the most MySQL and the underlying C API library also have their own memory overheads in addition to this So if you happen to know that the database server stores every record of a particular table in 1 KB pulling a million records from that table could easily take several GB of memory with a store query depending on what s actually stored in that table For these large result sets the superior option is a use query This returns a UseQueryResult object which is sim ilar to StoreQueryResult but without all of the random access features This is because a use query tells the database server to send the results back one row at a time to be processed linearly It s analogous to a C stream s input iterator as opposed to a random access iterator that a container like vector offers By accepting this limitation you can process arbitrarily large result sets This technique is demonstrated in examples simple3 cpp include cmdline h include printdata h include lt mysq
126. onds to a MySQL class or class hierarchy An overview of each follows 2 1 The Connection Object A Connection object manages the connection to the MySQL server You need at least one of these objects to do anything Because the other MySQL objects your program will use often depend at least indirectly on the Connection instance the Connection object needs to live at least as long as all other MySQL objects in your program MySQL supports many different types of data connection between the client and the server TCP IP Unix domain sockets and Windows named pipes The generic Connection class supports all of these figuring out which one you mean based on the parameters you pass to Connection connect But if you know in advance that your program only needs one particular connection type there are subclasses with simpler interfaces For example there s TCPConnection if you know your program will always use a networked database server 2 2 The Query Object Most often you create SQL queries using a Query object created by the Connection object Query acts as a standard C output stream so you can write data to it like you would to std cout or std ostringstream This is the most C ish way MySQL provides for building up a query string The library includes stream manipulators that are type aware so it s easy to build up syntactically correct SQL Query also has a feature called Template Queries which work something lik
127. opposed to the Xcode IDE Cygwin Basically Unix or anything that works like it ditto 66 MySQL v3 0 9 User Manual When MySQL includes one of the C API headers it normally does so in the obvious way include lt mysql h gt But if you define the MYSQLPP_MYSQL_ HEADERS BURIED macro it switches to this style include lt mysql mysql h gt In common situations like the usr include mysq 1 one this simplifies the include path options you pass to your compiler 8 4 Building MySQL on Systems Without Complete C99 Sup port MySQL uses the C99 header stdint h for portable fixed size integer typedefs where possible The C99 extensions aren t yet officially part of the C Standard so there are still some C compilers that don t offer this header MySQL works around the lack of this header where it knows it needs to but your platform might not be recognized causing the build to break If this happens you can define the MYSQLPP_NO_STDINT_H macro to make MySQL use its best guess for suitable integer types instead of relying on stdint h MySQL also uses C99 s long long data type where available MySQL has workarounds for platforms where this is known not to be available but if you get errors in common h about this type you can define the macro MYSQLPP_NO_LONG_LONGS to make MySQL fall back to portable constructs 67 MySQL v3 0 9 User Manual 9 Using MySQL in Your Own Project Up t
128. or the physical act of transferring a copy and you may at your option offer warranty protection in exchange for a fee 86 MySQL v3 0 9 User Manual 2 You may modify your copy or copies of the Library or any portion of it thus forming a work based on the Library and copy and distribute such modifications or work under the terms of Section 1 above provided that you also meet all of these conditions a The modified work must itself be a software library b You must cause the files modified to carry prominent notices stating that you changed the files and the date of any change c You must cause the whole of the work to be licensed at no charge to all third parties under the terms of this License d If a facility in the modified Library refers to a function or a table of data to be supplied by an ap plication program that uses the facility other than as an argument passed when the facility is invoked then you must make a good faith effort to ensure that in the event an application does not supply such function or table the facility still operates and performs whatever part of its purpose remains meaningful For example a function in a library to compute square roots has a purpose that is entirely well defined independent of the application Therefore Subsection 2d requires that any application supplied function or table used by this function must be optional if the application does not supply it the square root funct
129. ou can do so by permitting redistribution under these terms or alternatively under the terms of the ordinary General Public License To apply these terms attach the following notices to the library It is safest to attach them to the start of each source file to most effectively convey the exclusion of warranty and each file should have at least the copyright line and a pointer to where the full notice is found lt one line to give the library s name and a brief idea of what it does gt 90 MySQL v3 0 9 User Manual Copyright lt year gt lt name of author gt This library is free software you can redistribute it and or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation either version 2 1 of the License or at your option any later version This library is distributed in the hope that it will be useful but WITHOUT ANY WARRANTY without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE See the GNU Lesser General Public License for more details You should have received a copy of the GNU Lesser General Public License along with this library if not write to the Free Software Foundation Inc 59 Temple Place Suite 330 Boston MA 02111 1307 USA Also add information on how to contact you by electronic and paper mail You should also get your employer if you work as a programmer or your school if any to sign a
130. patent license would not permit royalty free redistribution of the Library by all those who receive copies directly or indirectly through you then the only way you could satisfy both it and this License would be to refrain entirely from distribution of the Library If any portion of this section is held invalid or unenforceable under any particular circumstance the balance of the section is intended to apply and the section as a whole is intended to apply in other circumstances It is not the purpose of this section to induce you to infringe any patents or other property right claims or to contest validity of any such claims this section has the sole purpose of protecting the integrity of the free software distribution system which is implemented by public license practices Many people have made generous contributions to the wide range of software distributed through that system in reliance on consistent application of that system it is up to the author donor to decide if he or she is willing to distribute software through any other system and a licensee cannot impose that choice 89 MySQL v3 0 9 User Manual 11 1 3 This section is intended to make thoroughly clear what is believed to be a consequence of the rest of this License 12 If the distribution and or use of the Library is restricted in certain countries either by patents or by copyrighted in terfaces the original copyright holder who places the Library under this License
131. perators and data members used by MySQL s internals to provide neat functionality which we cover in this chapter You define SSQLSes using the macros defined in ssqls h This is the only MySQL header not automatically included for you by mysql h You have to include it in code modules that use the SSQLS feature 5 1 sql_create Let s say you have the following SQL table CREATE TABLE stock item CHAR 30 NOT NULL num BIGINT NOT NULL weight DOUBLE NOT NULL price DECIMAL 6 2 NOT NULL sdate DATE NOT NULL description MEDIUMTEXT NULL You can create a C structure corresponding to this table like so sql_create_6 stock 1 6 mysqlpp sql_char item mysqlpp sql_bigint num mysqlpp sql_double weight mysqlpp sql_ decimal price mysqlpp sql_ date sdate mysqlpp Null lt mysqlpp sql_mediumtext gt description This declares the stock structure which has a data member for each SQL column using the same names The structure also has a number of member functions operators and hidden data members but we won t go into that just now The parameter before each field name in the sq1_create_ call is the C data type that will be used to hold that value in the SSQLS MySQL defines C types for almost every data type MySQL understands of the form sql_ We re using them for all of the columns above for reasons given in Section 3 6 C Equivalents of SQL Column Types While you could use plain old C
132. plain old C types can result in data truncation TypeLookupFailed exceptions and worse Type compatibility is important not just at the time you write your program it also helps forward compatibility we occasionally change the definitions of the MySQL typedefs to reduce the differences between the C and SQL type systems Code using the MySQL typedefs just needs to be recompiled to track these changes automat ically Most of these typedefs use standard C data types but a few are aliases for a MySQL specific type For instance the SQL type DATETIME is mirrored in MySQL by mysqlpp DateTime For consistency sql_types h includes a typedef alias for DateTime called mysqlpp sql_ datetime MySQL doesn t have typedef for the most exotic data types like those for the geospatial types Patches to correct this will be thoughtfully considered 3 7 Handling SQL Nulls There is no equivalent of SQL s null in the standard C type system The primary distinction is one of type in SQL null is a column attribute which affects whether that column can hold a SQL null Just like the const keyword in the C type system this effectively doubles the number of SQL data types To emulate this MySQL provides the Null template to allow the creation of distinct nullable versions of existing C types So for example if you have a TINYINT UNSIGNED column that can have nulls the proper declaration for MySQL would be mysqlpp
133. pointed to could be any of several types This new mechanism is properly type safe 73 MySQL v3 0 9 User Manual 10 1 4 2 Exception related changes Classes Connection Query Result ResUse and Row now derive from OptionalExceptions which gives these classes a common interface for disabling exceptions In addition almost all of the per method exception disabling flags were removed The preferred method for disabling exceptions on these objects is to create an instance of the new NoExceptions class on the stack which disables exceptions on an OptionalExceptions subclass as long as the NOExceptions instance is in scope You can instead call disable exceptions on any of these objects but if you only want them disabled temporarily it s easy to forget to re enable them later In the previous version of MySQL those classes that supported optional exceptions that could create instances of other such classes were supposed to pass this flag on to their children That is if you created a Connection object with exceptions enabled and then asked it to create a Query object the Query object also had exceptions disabled The problem is this didn t happen in all cases where it should have in v1 7 This bug is fixed in v2 0 If your program begins crashing due to uncaught exceptions after upgrading to v2 0 this is the most likely cause The most expeditious fix in this situation is to use the new NoExceptions feature to return these cod
134. qlpp escape lt lt img data lt lt SimpleResult res query execute If we get here insertion succeeded cout lt lt Inserted lt lt img_name lt lt into images table lt lt img data size lt lt bytes ID lt lt res insert_id lt lt endl catch const BadQuery amp er Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const BadConversion amp er Handle bad conversions cerr lt lt Conversion error lt lt er what lt lt endl lt lt tretrieved data size lt lt er retrieved lt lt lt lt er actual_size lt lt endl actual size return 1 catch const Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 return 0 Notice that we used the escape manipulator when building the INSERT query above This is because we re not using one of the MySQL types that does automatic escaping and quoting 31 MySQL v3 0 9 User Manual 3 15 2 Serving images from BLOB column via CGI This example is also a very short one considering the function that it performs It retreives data loaded by load_jpeg and prints it out in the form a web server can accept for a CGI call This is examples cgi_jpeg cpp include lt mysql h gt include lt ssqls h gt define IMG DATABASE mysql _cpp data d
135. quently no longer derive from Lockable Since it was basically useless in prior versions it can t be construed as an API change 10 2 7 v3 0 1 Connection thread_aware thread_start andthread_end are now static methods so a program can call them before creating a connection Ditto for DBDriver methods of the same name ConnectionPool release is now virtual so a subclass can override it 10 2 8 v3 0 2 ConnectionPool grab is now virtual same reason as above Query can now be tested in bool context as was intended for v3 0 0 Had to change the safe bool method signature to make it happen so technically it s an API change but it s still used the same way 83 MySQL v3 0 9 User Manual 11 Licensing The primary copyright holders on the MySQL library and its documentation are Kevin Atkinson 1998 MySQL AB 1999 through 2001 and Educational Technology Resources Inc 2004 through the date of this writing There are other contributors who also retain copyrights on their additions see the ChangeLog file in the MySQL distribution tarball for details The MySQL library and its Reference Manual are released under the GNU Lesser General Public License LGPL reproduced below The MySQL User Manual excepting some example code from the library reproduced within it is offered under a license closely based on the Linux Documentation Project License LDPL v2 0 included below The
136. r you may be able to downgrade to MySQL v2 x The SSQLS feature in these older versions worked with Visual C 2003 but didn t let you use a given SSQLS in more than one module in a program If you can live with that limitation and have a Perl interpreter on your system you can re generate 1ib ssqls h to remove the multiple module SSQLS support To do this you run the command perl ssqls pl v from within MySQL s lib subdirectory before you build and install the library There s a plan to replace the current SSQLS mechanism with an entirely new code base Although this is being done primarily to get new features that are too difficult to add within the current design it also means we ll have 54 MySQL v3 0 9 User Manual the chance to test step by step along the way that we don t reintroduce code that Visual C 2003 doesn t support This may happen without you doing anything but if there s someone on the team who cares about this that will naturally increase the chances that it does happen 55 MySQL v3 0 9 User Manual 6 Using Unicode with MySQL 6 1 A Short History of Unicode with a focus on relevance to MySQL In the old days computer operating systems only dealt with 8 bit character sets That only allows for 256 possible characters but the modern Western languages have more characters combined than that alone Add in all the other languages of the world plus the various symbols people use
137. re 1 0 version numbers Then in 1999 MySQL AB took over development of the library In the beginning Monty Widenius himself did some of the work but later gave it over to another MySQL employee Sinisa Milivojevic MySQL released versions 1 0 and 1 1 and then Kevin gave over maintenance to Sinisa officially with 1 2 and ceased to have any involvement with the library s maintenance Sinisa went on to maintain the library through 1 7 9 released in mid 2001 It seems to be during this time that the dream of multiple database compatibility died for obvious reasons With version 1 7 9 MySQL went into a period of stasis lasting over three years Perhaps it was the ennui and re trenchment following the collapse of the bubble that caused them to lose interest During this time Sinisa ran the MySQL mailing list and supported its users but made no new releases Contributed patches were either ignored or put up on the MySQL web site for users to try without any official blessing The biggest barrier to using MySQL during this period is that the popular C compilers of 2001 weren t all that compatible with the C Standard As a result MySQL used many nonstandard constructs to allow for compatib ility with older compilers Each new compiler released in the following years increased compliance either warning about or rejecting code using pre Standard constructs In particular GCC was emerging from the mess following the EGCS fork duri
138. rement contradicts the license restrictions of other proprietary libraries that do not normally accompany the operating system Such a contradiction means you cannot use both them and the Library together in an executable that you distribute 7 You may place library facilities that are a work based on the Library side by side in a single library together with other library facilities not covered by this License and distribute such a combined library provided that the separate distribution of the work based on the Library and of the other library facilities is otherwise permitted and provided that you do these two things a Accompany the combined library with a copy of the same work based on the Library uncombined with any other library facilities This must be distributed under the terms of the Sections above b Give prominent notice with the combined library of the fact that part of it is a work based on the Library and explaining where to find the accompanying uncombined form of the same work 8 You may not copy modify sublicense link with or distribute the Library except as expressly provided under this License Any attempt otherwise to copy modify sublicense link with or distribute the Library is void and will automatically terminate your rights under this License However parties who have received copies or rights from you under this License will not have their licenses terminated so long as such parties remain in full complianc
139. res field type 0 typeid string 4 cout lt lt SQL type of item field most closely resembles the C string type lt lt endl 27 MySQL v3 0 9 User Manual Tricky type check the if path shouldn t happen because the description field has the NULL attribute We need to dig a little deeper if we want to ignore this in our type checks if res field type 5 typeid string 4 cout lt lt Should not happen Type check failure lt lt endl else if res field type 5 typeid mysqlpp Null lt mysqlpp String gt cout lt lt SOL type of description field resembles a nullable variant of the C string type lt lt endl else cout lt lt Weird fifth field s type is now lt lt res field type 5 name lt lt endl cout lt lt Did something recently change in resetdb lt lt endl catch const mysqlpp BadQuery amp er Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 return 0 3 14 MySQL s Special String Types MySQL has two classes that work like std string to some degree String and SQLTypeAdapter These classes exist to provide functionality that std string doesn t provide but they are neither derivatives of
140. return 1 try Establish the connection to the database server mysqlpp Connection con db server user pass Assume that the last command line argument is a file Try to read that file s data into img_data and check it to see if it appears to be a JPEG file Bail otherwise string img _ name img data if argc ag_optind gt 1 img_name argv ag_optind ifstream img file img_name c_str ios ate if img file size_t img size img file tellg if img size gt 10 img _file seekg 0 ios beg unsigned char img buffer new unsigned char img size img _file read reinterpret cast lt char gt img buffer img_size if is _jpeg img buffer 4 30 MySQL v3 0 9 User Manual img_data assign reinterpret cast lt char gt img buffer img_size else cerr lt lt lt lt img file lt lt isn t a JPEG lt lt endl delete img_buffer else cerr lt lt File is too short to be a JPEG lt lt endl if img_data empty print_usage argv 0 jpeg_file return 1 Insert image data into the BLOB column in the images table We re inserting it as an std string instead of using the raw data buffer allocated above because we don t want the data treated as a C string which would truncate the data at the first null character Query query con query query lt lt INSERT INTO images data VALUES lt lt mys
141. rk under copyright law that is to say a work containing the Library or a portion of it either verbatim or with modifications and or translated straightforwardly into another language Hereinafter translation is included without limitation in the term modification Source code for a work means the preferred form of the work for making modifications to it For a library complete source code means all the source code for all modules it contains plus any associated interface definition files plus the scripts used to control compilation and installation of the library Activities other than copying distribution and modification are not covered by this License they are outside its scope The act of running a program using the Library is not restricted and output from such a program is covered only if its contents constitute a work based on the Library independent of the use of the Library in a tool for writing it Whether that is true depends on what the Library does and what the program that uses the Library does 1 You may copy and distribute verbatim copies of the Library s complete source code as you receive it in any medium provided that you conspicuously and appropriately publish on each copy an appropriate copyright notice and disclaimer of warranty keep intact all the notices that refer to this License and to the absence of any warranty and distribute a copy of this License along with the Library You may charge a fee f
142. rts SQL value strings to C data types and STA converts C data types to SQL value strings String has two main uses By far the most common use is as the field value type of Row as exemplified above It s not just the return type of Row operator though it s actually the value type used within Row s internal array As a result any time MySQL pulls data from the database it goes through String when converting it from the string form used in SQL result sets to the C data type you actually want the data in It s the core of the structure population mechanism in the SSQLS feature for example Because String is the last pristine form of data in a result set before it gets out of MySQL s internals where end user code can see it MySQL s sql_blob and related typedefs are aliases for String Using anything else would require copies while the whole networked database server thing means most of MySQL can be quite inefficient and still not affect benchmark results meaningfully BLOBs tend to be big so making unnecessary copies can really make a difference Which brings us to Reference Counting To avoid unnecessary buffer copies both STA and String are implemented in terms of a reference counted copy on write buffer scheme Both classes share the same underlying mechanism and so are interoperable This means that if you construct one of these objects from another it doesn t actually copy the string data it o
143. s typically libraries of the Free Software Foundation and other authors who decide to use it You can use it too but we suggest you first think carefully about whether this license or the ordinary General Public License is the better strategy to use in any particular case based on the explanations below When we speak of free software we are referring to freedom of use not price Our General Public Licenses are designed to make sure that you have the freedom to distribute copies of free software and charge for this service if you wish that you receive source code or can get it if you want it that you can change the software and use pieces of it in new free programs and that you are informed that you can do these things To protect your rights we need to make restrictions that forbid distributors to deny you these rights or to ask you to surrender these rights These restrictions translate to certain responsibilities for you if you distribute copies of the library or if you modify it For example if you distribute copies of the library whether gratis or for a fee you must give the recipients all the rights that we gave you You must make sure that they too receive or can get the source code If you link other code with the library you must provide complete object files to the recipients so that they can relink them with the library after making changes to the library and recompiling it And you must show them these terms so they know the
144. s of s into the database You have only to call query execute to actually insert it This is possible because SSQLSes have functions that Query can call to get the list of fields and such which it uses to build the INSERT query query update and query replace also rely on these features Another feature you might find a use for is changing the table name MySQL uses to build queries involving SSQLSes By default the database server table is assumed to have the same name as the SSQLS structure type But if this is in convenient you can globally change the table name used in queries like this stock table MyStockData It s also possible to change the name of a table on a per instance basis stock s s instance_table AlternateTable This is useful when you have an SSQLS definition that is compatible with multiple tables so the table name to use for each instance is different The simplest way this can happen is if the tables all have identical definitions it saves you from having to define a separate SSQLS for each table It is also useful for mapping a class hierarchy onto a set of table definitions The common SSQLS definition is the superclass for a given set of tables Strictly speaking you only need to use this feature in multithreaded programs Changing the static table name before using each instance is safe if all changes happen within a single thread That said it may still be convenient to change the
145. se protocol version instead of proto_info e Use server version instead of server_info Use status instead of stat Also removed close in favor of disconnect which has always done the same thing 10 1 7 3 Date and Time class changes The sql_timestamp typedef is now an alias for DateTime not Time There used to be implicit conversion constructors from ColData now String std string and const char for the Date DateTime and Time classes It s still possible to do these conversions but only explicitly This had to be done to make Nul1 lt T gt work in SSQLSes The most likely place to run into problems as a result of this change is in code like this void some function const mysqlpp DateTimes dt some function 2007 12 22 The function call needs to be changed to some function mysqlpp DateTime 2007 12 22 10 1 7 4 Exception changes If an error occurs during the processing of a use query as opposed to the initial execution we throw the new UseQueryError exception instead of BadQuery If you pass bad values to the Row ctor so that it can t initialize itself properly it throws the ObjectNotInitialized exception instead of BadQuery Together these two changes mean that BadQuery is now used solely to indicate a problem executing the actual SQL query statement 77 MySQL v3 0 9 User Manual 10 1 7 5 Field and Fields class changes Field is now a real C class not jus
146. select _ db a db that might not exist yet Our DB doesn t exist yet so create and select it here no need to push handling of this case way off in an exception handler When one OptionalExceptions derivative passes its exceptions flag to another such object it is only passing a copy the two objects flags operate independently There s no way to globally enable or disable this flag on existing objects in a single call If you re using the NOExceptions feature and you re still seeing optional exceptions thrown you disabled exceptions on the wrong object The exception thrower could be unrelated to the object you disabled ex ceptions on it could be its parent or it could be a child created before you disabled optional exceptions MySQL throws some exceptions unconditionally e MySQL checks array indices always For instance if your code said row 21 on a row containing only 5 fields you d get a BadIndex exception If you say row fred on a row without a fred field you get a BadFieldName exception In the past MySQL delegated some of its index checking to the STL containers underpinning it so you could get std range_error instead As of MySQL v3 0 7 this should no longer happen but there may be instances where it still does e String will always throw BadConversion when you ask it to do an improper type conversion For example you ll get an exception if you try to convert 1
147. shared data structures a lifetime independent of the result set object that intitially creates it These shared data structures stick around until the last object needing them gets destroyed Although this is now a solved problem I bring it up because there are likely other similar lifetime and sequencing problems waiting to be discovered inside MySQL If you would like to help us find these by all means share data between threads willy nilly We welcome your crash reports on the MySQL mailing list But if you d prefer to avoid problems it s better to keep all data about a query within a single thread Between this and the previous section s advice you should be able to use threads with MySQL without trouble 65 MySQL v3 0 9 User Manual 8 Configuring MySQL The default configuration of MySQL is suitable for most purposes but there are a few things you can change to make it meet special needs 8 1 The Location of the MySQL Development Files MySQL is built on top of the MySQL C API It relies on this low level library for all communication with the database server Consequently the build process for MySQL may fail if it can t find the C API headers and library On platforms that use Autoconf 15 the configure script can usually figure out the location of the C API development files by itself It simply tries a bunch of common installation locations until it finds one that works If your MySQL server was inst
148. standard Unix function iconv can help here If your system doesn t have the iconv facility there is a free implement ation available from the GNU Project Another library you might check out is IBM s ICU This is rather heavy weight so if you just need basic conversions iconv should suffice 56 MySQL v3 0 9 User Manual 6 3 Unicode on Windows Each Windows API function that takes a string actually comes in two versions One version supports only 1 byte ANSI characters a superset of ASCII so they end in A Windows also supports the 2 byte subset of Unicode called UCS 2 Some call these wide characters so the other set of functions end in W The MessageBox API for instance is actually a macro not a real function If you define the UNICODE macro when building your program the MessageBox macro evaluates to MessageBoxW otherwise to MessageBoxA Since MySQL uses the UTF 8 Unicode encoding and Windows uses UCS 2 you must convert data when passing text between MySQL and the Windows API Since there s no point in trying for portability no other OS I m aware of uses UCS 2 you might as well use platform specific functions to do this translation Since version 2 2 2 MySQL ships with two Visual C specific examples showing how to do this in a GUI program In earlier versions of MySQL we did Unicode conversion in the console mode programs but this was unrealistic How you han
149. stream and get the same result For example a lot of code in the examples that used to say things like cout lt lt query preview lt lt endl now looks like this cout lt lt query lt lt endl 78 MySQL v3 0 9 User Manual 10 1 7 7 Result ResUse and ResNSel class changes In addition to the class name changes described above UseQueryResult is no longer StoreQueryResult s base class There is a new abstract class called ResultBase containing much of what used to be in ResUse and it is the base of both of these concrete result set types This should only affect your code if you were using ResUse references to refer to Result objects Removed a bunch of duplicate methods e Use num _fields instead of columns e Usefield names instead of names e Use num_rows instead of rows e Usefield types instead of types Renamed several methods for grammar reasons For example some methods returned a single object but had a plural name implying that it returned a container of objects In cases like this we changed the name to agree with the return value Some of these also fall into the duplicate method category above e Use field unsigned int instead of fields unsigned int e Usefield num const std string8 instead of names const std string8 e Usefield name int instead of names int e Usefield type int instead of types int Removed several smelly methods e
150. t tretrieved data size lt lt er retrieved lt lt actual size lt lt er actual_size lt lt endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions cerr lt lt Error lt lt er what lt lt endl return 1 return 0 One of the downsides of transactions is that the locking it requires in the database server is prone to deadlocks The classic case where this happens is when two programs both want access to the same two rows within a single transaction each but they modify them in opposite orders If the timing is such that the programs interleave their lock acquisitions the two come to an impasse neither can get access to the other row they want to modify until the other program commits its transaction and thus release the row locks but neither can finish the transaction because they re waiting on row locks the database server is holding on behalf of the other program The MySQL server is smart enough to detect this condition but the best it can do is abort the second transaction This breaks the impasse allowing the first program to complete its transaction The second program now has to deal with the fact that its transaction just got aborted There s a subtlety in detecting this situation when using MySQL By default MySQL signals errors like these with exceptions In the exception handler you might expect to get ER_LOCK_DEADLOCK from Qu
151. t a typedef for the corresponding C API class Major portability impacts are e Ithas no public data members Where sensible there is a public accessor function of the same name as the corres ponding field in the C API structure e The main exception to this is the flags data member This is a bitfield in the C API data structure and you had to use MySQL specific constants to break values out of it MySQL s new Field class provides a public member function returning bool for each of these flags e The new class doesn t include all of the data members from the C API version We left out those that aren t used within MySQL or its examples or whose function we couldn t understand Basically if we couldn t document a reason to use it we left it out Fields used to be a std vector work alike which worked with the C API to access fields and return them as though they were simply contained directly within the Fields object Now that we have a real MySQL class to hold information about each field without reference to the C API we were able to replace the Fields class with typedef std vector lt Field gt Fields If anything this should give a pure superset of the old functionality but it s possible it could break end user code 10 1 7 6 Query class changes If you were using char as an 8 bit integer in query building there are several places in MySQL v3 where it will now be treated as a single character string MySQL has
152. tch_row std cout lt lt Content type image jpeg lt lt std endl std cout lt lt Content length lt lt img data length lt lt n n std cout lt lt img data else 32 MySQL v3 0 9 User Manual std cout lt lt Content type text plain lt lt std endl lt lt std endl std cout lt lt ERROR No such image with ID lt lt img_id lt lt std endl catch const mysqlpp BadQuery amp er Handle any query errors std cout lt lt Content type text plain lt lt std endl lt lt std endl std cout lt lt QUERY ERROR lt lt er what lt lt std endl return 1 catch const mysqlpp Exception amp er Catch all for any other MySQL exceptions std cout lt lt Content type text plain lt lt std endl lt lt std endl std cout lt lt GENERAL ERROR lt lt er what lt lt std endl return 1 return 0 You install this in a web server s CGI program directory then call it with a URL like http my serv er com cgi bin cgi_jpeg id 1 That retrieves the JPEG with ID 1 from the table and returns it to the web server which will send it on to the browser 3 16 Concurrent Queries on a Connection An important limitation of the MySQL C API library which MySQL is built atop so it shares this limitation is that you can t have two concurrent queries running on a single connection If you try you get an
153. the database server mysqlpp Connection con db server user pass Show initial state mysqlpp Query query con query cout lt lt Initial state of stock table lt lt endl print_stock_table query Insert a few rows in a single transaction set mysqlpp Transaction trans con stock row Sauerkraut 42 1 2 0 75 mysqlpp sql_date 2006 03 06 mysqlpp null query insert row query execute cout lt lt nRow inserted but not committed lt lt endl cout lt lt Verify this with another program e g simplel then hit Enter lt lt endl getchar cout lt lt nCommitting transaction gives us lt lt endl trans commit 15 MySQL v3 0 9 User Manual print_stock_table query Now let s test auto rollback mysqlpp Transaction trans con cout lt lt nNow adding catsup to the database lt lt endl stock row Catsup 3 3 9 2 99 mysqlpp sql_date 2006 03 06 mysqlpp null query insert row query execute cout lt lt nNo yuck We don t like catsup Rolling it back lt lt endl print_stock_table query catch const mysqlpp BadQuery amp er Handle any query errors cerr lt lt Query error lt lt er what lt lt endl return 1 catch const mysqlpp BadConversion amp er Handle bad conversions cerr lt lt Conversion error lt lt er what lt lt endl lt l
154. the name with two colons The first one will end the name and the second one won t be processed 4 2 Setting the Parameters at Execution Time To specify the parameters when you want to execute a query simply use Query store const SQLString amp parm0 const SQLString amp parm11 This type of multiple overload also exists for 35 MySQL v3 0 9 User Manual Query storein Query use and Query execute parm0 corresponds to the first parameter etc You may specify up to 25 parameters For example StoreQueryResult res query store Dinner Rolls item item price with the template query provided above would produce select item price from stock where item Dinner Rolls The reason we didn t put the template parameters in numeric order select 0 fieldl 1 field2 from stock where 2 wheref 3q what will become apparent shortly 4 3 Default Parameters The template query mechanism allows you to set default parameter values You simply assign a value for the parameter to the appropriate position in the Query template defaults array You can refer to the parameters either by position or by name query template _defaults 1 item query template defaults wheref item Both do the same thing This mechanism works much like C s default function parameter mechanism if you set defaults for the parameters at the end of the list you can call one of Query s
155. therefore falls outside the scope of this License However linking a work that uses the Library with the Library creates an executable that is a derivative of the Library because it contains portions of the Library rather than a work that uses the library The executable is therefore covered by this License Section 6 states terms for distribution of such executables When a work that uses the Library uses material from a header file that is part of the Library the object code for the work may be a derivative work of the Library even though the source code is not Whether this is true is especially significant if the work can be linked without the Library or if the work is itself a library The threshold for this to be true is not precisely defined by law If such an object file uses only numerical parameters data structure layouts and accessors and small macros and small inline functions ten lines or less in length then the use of the object file is unrestricted regardless of whether it is legally a derivative work Executables containing this object code plus portions of the Library will still fall under Section 6 Otherwise if the work is a derivative of the Library you may distribute the object code for the work under the terms of Section 6 Any executables containing that work also fall under Section 6 whether or not they are linked directly with the Library itself 6 As an exception to the Sections above you
156. thout warnings Since then with a little help from his friends on the Net MySQL has lost a lot of bugs gained a lot of features gained a few more bugs lost them again MySQL is alive and healthy now MySQL v3 0 9 User Manual 1 2 If You Have Questions If you want to email someone to ask questions about this library we greatly prefer that you send mail to the MySQL mailing list The mailing list is archived so if you have questions do a search to see if the question has been asked before You may find people s individual email addresses in various files within the MySQL distribution Please do not send mail to them unless you are sending something that is inherently personal Not all of the principal developers of MySQL are still active in its development those who have dropped out have no wish to be bugged about MySQL Those of us still active in MySQL development monitor the mailing list so you aren t getting any extra coverage by sending messages to additional email addresses MySQL v3 0 9 User Manual 2 Overview MySQL has a lot of complexity and power to cope with the variety of ways people use databases but at bottom it doesn t work all that differently than other database access APIs The usage pattern looks like this 1 Open the connection 2 Form and execute the query 3 If successful iterate through the result set 4 Else deal with errors Each of these steps corresp
157. tions When exceptions are suppressed MySQL signals errors by returning either an error code or an object that tests as false or by setting an error flag on the object Classes that allow you to suppress exceptions derive from the OptionalEx ceptions interface When an OptionalExceptions derivative creates another object that also derives from this interface it passes on its exception flag Since everything flows from the Connection object disabling exceptions on it at the start of the program disables all optional exceptions This is why passing false for the Connection constructor s throw exceptions parameter suppresses all optional exceptions in the simple 1 3 examples It keeps them well simple This exception suppression mechanism is quite granular It s possible to leave exceptions enabled most of the time but suppress them in sections of the code where they aren t helpful To do this put the section of code that you want to not throw exceptions inside a block and create a NoExceptions object at the top of that block When created it saves the exception flag of the OptionalExceptions derivative you pass to it and then disables exceptions on it When 11 MySQL v3 0 9 User Manual the NoExceptions object goes out of scope at the end of the block it restores the exceptions flag to its previous state mysqlpp Connection con default ctor so exceptions enabled mysqlpp NoExceptions ne con if con
158. to create the test database Once the database is created you can use any account when running the other examples that has DELETE INSERT SELECT and UPDATE permissions for the test database The MySQL root user can do all this of course but you might want to set up a separate user having only the permissions necessary to work with the test database CREATE USER mysqlpp test IDENTIFIED BY nunyabinness GRANT ALL PRIVILEGES ON mysql cpp data TO mysqlpp test You could then create the sample database with the following command exrun resetdb u mysqlpp test p nunyabinness Again leave off the bit on Windows You may have to re run resetdb after running some of the other examples as they change the database See README examples txt for more details on running the examples 3 2 A Simple Example The following example demonstrates how to open a connection execute a simple query and display the results This is examples simplel cpp include cmdline h include printdata h include lt mysql h gt include lt iostream gt include lt iomanip gt using namespace std int main int argc char argv Get database access parameters from command line const char db 0 server 0 user 0 pass if parse_command_line argc argv amp db server amp user amp pass return 1 Connect to the sample database mysqlpp Connection conn false if conn connect db server
159. tty low level of abstraction It s better than using the MySQL C API but not by much You can elevate things a little closer to the level of the problem space by using the SSQLS feature This lets you define C structures that match the table structures in your database schema In addition it s easy to use SSQLSes with regular STL containers and thus algorithms so you don t have to deal with the quirks of MySQL s data structures The advantage of this method is that your program will require very little embedded SQL code You can simply execute a query and receive your results as C data structures which can be accessed just as you would any other structure The results can be accessed through the Row object or you can ask the library to dump the results into an STL container sequential or set associative it doesn t matter for you Consider this vector lt stock gt v query lt lt SELECT FROM stock query storein v for vector lt stock gt iterator it v begin it v end it cout lt lt Price lt lt it gt price lt lt endl Isn t that slick If you don t want to create SSQLSes to match your table structures as of MySQL v3 you can now use Row here instead vector lt mysqlpp Row gt v query lt lt SELECT FROM stock query storein v for vector lt mysqlpp Row gt iterator it v begin it v end it cout lt lt Price lt lt it gt at pr
160. uery s use no example ever used this interface directly so no end user code is likely to be affected by this change A more likely problem arising from the above change is code that tests for query success by calling the Connection object s success method or by casting it to bool This will now give misleading results because queries no longer go through the Connection object Class Query has the same success testing interface so use it instead Query now derives from std ostream instead of std stringstream 10 1 4 4 Result ResUse class changes Renamed ResUse mysql_ result to raw_result so it s database server neutral Removed ResUse eof as it wrapped the deprecated and unnecessary MySQL C API function mysql eof See the simple3 and usequery examples to see the proper way to test for the end of a result set 10 1 4 5 Row class changes Removed field name form of Row field _list It was pointless 74 MySQL v3 0 9 User Manual Row subscripting works more like v1 7 9 one can subscript a Row with a string e g row myfield or with an integer e g row 5 lookup_by_ name was removed Because row 0 is ambiguous 0 could mean the first field or be a null pointer to const char there is now Row at which can look up any field by index 10 1 4 6 Miscellaneous changes e Where possible all distributed Makefiles only build dynamic libraries Shared objects on most Unices
161. ve MinGW shell and restore Cygwin environment usr bin bash rcfile mingwrc Then there s a tiny little file called mingwrc that goes in your Cygwin home directory alias make mingw32 make PS1 MinGW WAS This split is necessary due to the way Bash works The first script sets up most of the MinGW environment putting the MinGW and Windows directories ahead of the Cygwin directory so programs in those locations take precedence Then the second script finishes setting up the MinGW sub shell causing the make command to invoke MinGW s make program instead of Cygwin s and changing the command prompt to remind you that you re in a sub shell Just say exit to get back to Cygwin mode 9 4 2 Third Party MinGW IDEs Dev C Code Blocks I have no information on how to do this We ve received reports on the mailing list from people that have made it work but no specifics on what all needs to be done The Makefile discussion above should give you some hints 9 5 Eclipse As far as I can tell the simplest way to build a C project with Eclipse is to set up a Makefile for it as described above then add an external run configuration for your local make tool Get the project building from the command line with make then go to Run External Tools Open External Tools Dialog and add a new launch configuration 71 MySQL v3 0 9 User Manual For example on my OS X system I use usr bin gnumake for the program locati
162. we turn on in the shipping version of MySQL are those few that have no practical negative consequences Everything else is up to you the programmer to evaluate and enable as and when you need it We re going to assume that you either agree with these views but find yourself needing to use threads for some other reason or are foolishly disregarding these facts and are going to use threads anyway Our purpose here is limited to setting down the rules for avoiding problems with MySQL in a multi threaded program We won t go into the broader issues of thread safety outside the scope of MySQL You will need a grounding in threads in general to get the full value of this advice 7 1 Build Issues Before you can safely use MySQL with threads there are several things you must do to get a thread aware build 1 Build MySQL itself with thread awareness turned on On Linux Cygwin and Unix OS X BSD Solaris pass the enable thread check flag to the con figure script Beware this is only a request to the configure script to look for thread support on your system not a requirement to do or die if the script doesn t find what it needs to do threading MySQL will just get built without thread support See README Unix txt for more details On Windows if you use the Visual C project files or the MinGW Makefile that comes with the MySQL distribution threading is always turned on due to the nature of Windows If you build My
163. xample and by several others below include lt mysql h gt include lt ssqls h gt The following is calling a very complex macro which will create struct stock which has the member variables sql char item sete Null lt sql_mediumtext gt description plus methods to help populate the class from a MySQL row See the SSQLS sections in the user manual for further details sql_create_6 stock 1 6 The meaning of these values is covered in the user manual mysqlpp sql_char item mysqlpp sql_bigint num mysqlpp sql_double weight mysqlpp sql_ double price mysqlpp sql_ date sdate mysqlpp Null lt mysqlpp sql_mediumtext gt description This example produces the same output as simplel cpp see Section 3 2 A Simple Example but it uses higher level data structures paralleling the database schema instead of MySQL s lower level generic data structures It also uses MySQL s exceptions for error handling instead of doing everything inline For small example programs like these the overhead of SSQLS and exceptions doesn t pay off very well but in a real program they end up working much better than hand rolled code Notice that we are only pulling a single column from the stock table but we are storing the rows in a std vec tor lt stock gt It may strike you as inefficient to have five unused fields per record It s easily remedied by defining a subset
164. ypeAdapter type plus the Set template SSQLS also uses these manipulators internally Quoting is pretty simple but SQL syntax also often requires that certain characters be escaped Imagine if the string in the previous example was Frank s Brand Hotdog Buns instead The resulting query would be SELECT FROM stock WHERE item Frank s Brand Hotdog Buns That s not valid SQL syntax The correct syntax is SELECT FROM stock WHERE item Frank s Brand Hotdog Buns As you might expect MySQL provides that feature too through its escape manipulator But here we want both quoting and escaping That brings us to the most widely useful manipulator string s Frank s Brand Hotdog Buns query lt lt SELECT FROM stock WHERE item lt lt quote lt lt s The quote manipulator both quotes strings and escapes any characters that are special in SQL MySQL provides other manipulators as well See the manip h page in the reference manual It s important to realize that MySQL s quoting and escaping mechanism is type aware Manipulators have no effect unless you insert the manipulator into a Query or SQLQueryParms stream l Also values are only quoted and or escaped if they are of a data type that may need it For example Date must be quoted but never needs to be escaped and integer types need neither quoting nor escaping Manipulators are suggestions to the library not commands MySQL will ignore these sugg

Download Pdf Manuals

image

Related Search

Related Contents

Télécharger - Sainte Brigide  "user manual"  Scotch-Weld™ Polyurethane Reactive Adhesive Applicator  Grace Bravado GDI-IRD4400M User's Manual  Mode d`emploi de base - Migros  Avaya B179 User's Manual  Samsung Galaxy S4 GT-I9500 16GB White  Adulte  iTools 9 User Manual  Catalogue de pièces détachées avec prix  

Copyright © All rights reserved.
Failed to retrieve file