Using libmysqld with Microsoft Visual C++ 2008Express
[ Note: this post was updated on Dec 2, 2009 to correct dashes. In the original post, dash dash -- was showing up as a single dash. This is noted in the comments and has been corrected in the text below.]
These are very (very) basic instructions for setting up a very simple application with Microsoft Visual C++ 2008 Express to use the MySQL Embedded Library (libmysqld).
The MySQL Embedded Library is a full version of the server that is available as a dynamic or static library. Developers can run the library inside their application. It’s extremely fast, easy to distribute and it’s ideal for stand-alone applications. More information about the library can be found at http://dev.mysql.com/doc/refman/5.1/en/libmysqld.html
Having little experience using Visual C++, I’ve struggled to find instructions for setting up a project to use a dynamic library. I finally got it to work thanks to Ulf Wendel’s blog about building a windows client using the Connector/C++ ( http://blog.ulf-wendel.de/?p=215 ). I was able to take his tutorial, substitute the appropriate libmysqld entries and compile the project. If you get lost following my instructions, refer back to Ulf’s blog.
To develop a libmysqld application, you’ll need data files, the error message file ( errmsg.sys ), the appropriate libraries and header files. To get these files, I’d recommend downloading and installing the full version of the server. Also, it’s easier to design and build your database using the standard version of the server. Once you have some data, stop the MySQL service and then create your application using libmysqld.
Here’s the process that I used.
Download the Windows MSI Installer (x86) from http://dev.mysql.com/downloads. For this example, I downloaded and installed version 5.1.34. I selected the option for a standard install. After the install, you can create tables and design your database as needed. For this example, I’m going to use the “user” table, which installs with MySQL.
After the install, go to the services control panel ( Start | Control Panel | Administrative Tools | Services ) and stop MySQL. You might also change the Startup type to “manual” to ensure that it doesn’t start on reboot and cause a conflict with your project.
Locate required directories for setting up the project:
1) Data Files
On Windows, the default location for data files is C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data We’ll be accessing the user.MYD MyISAM table, which is located in the .\mysql subdirectory. Similarly, If you were to create a database named world with a city table, there would be a corresponding world directory and city.MYD MyISAM table in the data directory.
2) Error Message File
The error message file ( errmsg.sys ) is required to start libmysqld. By default, it’s located at C:\Program Files\MySQL\MySQL Server 5.1\share\english
3) Header Files
C:\Program Files\MySQL\MySQL Server 5.1\include
4) Embedded Library
C:\Program Files\MySQL\MySQL Server 5.1\Embedded\DLL\release
Copy the libmysqld.dll from the embedded library path (above) to the c:\windows\system32 directory (or anywhere in your search path). For deployment, you’ll probably want to include the embedded library with the executable.
Time to setup up Microsoft Visual C++ Express
Launch Microsoft Visual C++ Express. From the main menu, select File | New| Project
Choose a Win32 Console Application
Select OK and Finish.
Right-click the project name in the Solution Explorer and select Properties.
Expand Configuration Properties, C/C++ Properties and addthe following for the Additional Include Directories:
C:\Program Files\MySQL\MySQL Server 5.1\include
Expand Linker, select General andadd the following toAdditional Library Directories: C:\Program Files\MySQL\ MySQL Server 5.1\ Embedded\ DLL\release
Select Linker, Input and add the following for Additional Dependencies: libmysqld.lib
Select OK to save the setting and return to the project.
Copy the following into the source file ( my copy is named sample.cpp ) Overwrite any existing code in the file.
#include “stdafx.h”
#include <stdlib.h>
#include <iostream>
#include <stdio.h>
#include <stdarg.h>
#include <windows.h>
#include <mysql.h>
MYSQL *mysql;
MYSQL_RES *results;
MYSQL_ROW record;
static char *server_options[] = { “mysql_test”, “--datadir=C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/data“,
“--language=C:/Program Files/MySQL/MySQL Server 5.1/share/english", NULL };
int num_elements = sizeof(server_options)/ sizeof(char *) – 1;
static char *server_groups[] = { “libmysqld_server”, NULL };
int main(int argc, char* argv[])
{
int retval;
retval = mysql_library_init(num_elements, server_options, (char **) server_groups);
mysql = mysql_init(NULL);
mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
mysql_real_connect(mysql, NULL,“root”,NULL, “mysql”, 0, NULL, 0);
mysql_query(mysql, “SELECT user, host FROM user”);
results = mysql_store_result(mysql);
while((record = mysql_fetch_row(results))) {
printf(“%s – %s \n”, record[0], record[1]);
}
mysql_free_result(results);
mysql_close(mysql);
return 0;
}
At this point, you should be able to build and run your project. The output should look like this.
It’s a simple listing of the users in the mysql table.
The following line of code is critical:
static char *server_options[] = { “mysql_test”, “--datadir=C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/data“,
“--language=C:/Program Files/MySQL/MySQL Server 5.1/share/english", NULL };
You must have a datadir and a language file. Note the forward slash in the directory paths. Of course, these paths can be located anywhere. You’ll probably want to move these folders to a subdirectory under your application install. If you don’t see an error log in the data directory, that’s also an indicator that these paths are incorrect.
I hope someone finds this useful.
25 Comments »
Hi,
Thanks for putting this together.
I followed your instructions (I am using VS 2008 C++ full version though). And I get the following run-time error in the console at:
retval = mysql_library_init(num_elements, server_options, (char **) server_groups);“The thread ‘Win32 Thread’ (0×1ba0) has exited with code 1 (0×1).
The thread ‘Win32 Thread’ (0×90c) has exited with code 1 (0×1).
The thread ‘Win32 Thread’ (0×264c) has exited with code 1 (0×1).
The thread ‘Win32 Thread’ (0×25c4) has exited with code 1 (0×1).
The program ‘[6200] mysql_embedded.exe: Native’ has exited with code 1 (0×1).”(My app is called mysql_embedded.exe)
Do you know why this is happening? Thanks.
Comment by gmk — June 4, 2009 @ 3:41 am |
Is it possible that your data directory is not specified correctly? If I change my datadir path to an invalid path, I get similar errors. Perhaps you can post the following for your project along w/ what you have in that directory:
static char *server_options[] = { “mysql_test”, “–datadir=C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/data”, “–language=C:/Program Files/MySQL/MySQL Server 5.1/share/english”, NULL };
I don’t think it’s getting this far, but you might also check for the error log in your data directory to see if there is any info.
Comment by Lee Stigile — June 4, 2009 @ 3:43 pm |
Hi gmk,
I had the same problem before, and I am using VS 2003 full version. Everything worked after I used “–data” and “–language” instead of “-data” and “-language”. Hope that helps.
Thanks Lee for a much needed and helpful article! =)
Kind regards,
ITComment by IT — June 15, 2009 @ 4:14 am |
Sorry, it appears that the double ‘-’ are turned into single dashes…
Comment by IT — June 15, 2009 @ 4:18 am |
Hi Lee and IT,
Thanks for your feedback but it still does not work. A couple of things:1. I have copied the data directory and language to a simpler folder structure (c:\mysql_embedded), but still no luck.
2. I am using Vista.
3. your “mysql_test” argument in your server_options array, is that supposed to be a sub directory in the data directory? mine is just called “mysql”.
4. tried that double dash, but nothing.
5. there is no err file being generated in the data directory.thanks for your help.
Comment by gmk — June 15, 2009 @ 4:57 am |
Regarding #3, the first argument in the server_options array is ignored (http://dev.mysql.com/doc/refman/5.1/en/mysql-library-init.html).
Perhaps there’s a version mismatch between libmysqld.dll, libmysqld.lib and errmsg.sys? You might search your system for these files and update with the latest 5.1 versions.
Also, feel free to send the project files directly to me, I’d be glad to run on a couple systems here.
Comment by Lee Stigile — June 15, 2009 @ 3:35 pm |
Hi,
Thaks for your tutorial,can you give an idea about how to relate with Visual c++ 2005 and mysql.Comment by TT — July 13, 2009 @ 9:02 am |
Hi,
That’s a nice tutorial, but since I am completely new in mysql and I am dealing with VC++ from relatively soon, I am encountering errors I don’t have a clue of.
As I am executing this sample program in debug mode I get some weird “first chance” exception and execution stops at line:
mysql_real_connect(mysql, NULL,”root”, NULL, “mysql”, 0, NULL, 0);I’ve tried putting the password for the root account in the place of the second NULL with no success.
This is what is residing in the Output tab:’sample.exe’: Loaded ‘C:\Documents and Settings\Pavlin\My Documents\Visual Studio 2008\Projects\sample\Release\sample.exe’, Symbols loaded.
’sample.exe’: Loaded ‘C:\WINDOWS\system32\ntdll.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\kernel32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\libmysqld.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\wsock32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\ws2_32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\advapi32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\rpcrt4.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\secur32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\msvcrt.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\ws2help.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\user32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\gdi32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\WinSxS\x86_Microsoft.VC90.CRT_1fc8b3b9a1e18e3b_9.0.30729.1_x-ww_6f74963e\msvcr90.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\imm32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\mswsock.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\dnsapi.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\winrnr.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\wldap32.dll’
’sample.exe’: Loaded ‘C:\WINDOWS\system32\rasadhlp.dll’
First-chance exception at 0×10018bc1 in sample.exe: 0xC0000005: Access violation writing location 0×0000035c.
Unhandled exception at 0×10018bc1 in sample.exe: 0xC0000005: Access violation writing location 0×0000035c.
The program ‘[2484] sample.exe: Native’ has exited with code 0 (0×0).Do you have an idea what could be wrong?
Comment by Pavlin — August 24, 2009 @ 1:25 pm |
Is there anything in the error log ( *.err file in the data directory) ? Is there any chance that the port is already in use?
Comment by Lee Stigile — August 24, 2009 @ 9:03 pm |
On every program run the .err file gets:
“InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090825 10:21:26 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…”
at the end. And it is located exactly at “C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/data/”
And after few tests and running step by step, I noticed, that it actually throws the exception right after executing:
mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
and then the green arrow points to the next line.If I understand right, the port is set – 0.
mysql_real_connect(mysql, NULL,”root”, NULL, “mysql”, 0, NULL, 0)
So I don’t know what the chance is that it is used. But I am not sure if it matters, if it breaks before executing that line.Comment by Pavlin — August 25, 2009 @ 7:28 am |
Sorry I bothered you. There was mistake in MY code causing mysql to be NULL at execution of mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
Thats what the problem was. Sorry one more time and thanks for the nice howto.Comment by Pavlin — August 25, 2009 @ 7:43 am |
Yes, using libmysqld.dll is easy and convinient.
But do you know that you do NOT have a right to redistribute libmysqld.dll with your app unless you either:
1. open your app source code to the whole world (GPL license)
or
2. buy commercial license from MySQL (now SUN) – it is not clear how much it costs
Does anybody wants to comment this?Comment by careful_guy — August 26, 2009 @ 5:30 am |
First, thanks for this set of instructions.
If you copy the code into Visual Studio, you’ll run into problems:
1. All the double quote marks have to be replaced with ordinary quote marks.
2. The minus sign in the following line of code became a dash, confusing the compiler, which gave a bogus error, confusing two programmers. It needs to be replaced:int num_elements = sizeof(server_options)/ sizeof(char *) – 1;
Comment by Steve Miller — August 27, 2009 @ 5:25 pm |
why can’t I use an InnoDB database? with libmysqld can you only read MyISAM tables? (like all tables mysql are)
Comment by EvIl_DeViL — September 8, 2009 @ 1:58 pm |
Hello — You should be able to access the innodb database. You might want to check your error log for specific error information.
Comment by Lee Stigile — September 8, 2009 @ 7:24 pm |
with the same code i can access to MyISAM tables… if I change the table type to Inno I can’t access them. here’s my InnoDB log… I copied it from Data directory and pasted to my project dir.
————————————InnoDB LogFile————————————
090909 1:08:58 [Note] Plugin ‘FEDERATED’ is disabled.
090909 1:08:58 InnoDB: Started; log sequence number 0 172027
090909 1:08:58 [Note] Event Scheduler: Loaded 0 events
090909 1:08:58 [Note] C:\Programmi\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: ‘5.1.36-community’ socket: ” port: 3306 MySQL Community Server (GPL)
InnoDB: Error: log file d:\Program\Program\database\ib_logfile0 is of different size 0 25165824 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
————————————InnoDB LogFile————————————the error is 1286: unknow table engine ‘InnoDB’
i didn’t use skip-innodb and in “my.ini” is commented. (i don’t think my.ini influence that because it’s an embedded version but my db was created with navicat so… (and with MyISAM works!))Comment by EvIl_DeViL — September 8, 2009 @ 11:39 pm
I recommend removing (renaming or deleting) the ib_logfile0 and ib_logfile1 (if exists) and try the app again. There may have been an old file from a previous install or a full server install. If the log files don’t match your config, it won’t start.
Comment by Lee Stigile — September 9, 2009 @ 2:29 am
wow! you’re right!!! I deleted the two log files and it worked!!! many many many thanks!!!
Comment by EvIl_DeViL — September 9, 2009 @ 8:00 am
hi, i tried it out, and i’m getting an error on statement
“mysql = mysql_init(NULL);”. Can someone help ?Comment by Hitesh — October 4, 2009 @ 6:07 pm |
also the return value in the statement
“retval = mysql_library_init(num_elements, server_options, (char **) server_groups);” is 1. why is that so ?Comment by Hitesh — October 4, 2009 @ 6:25 pm |
Hi — please take a look at comment #4 above from IT. He had similar errors and noted that –data and –language need two dashes, instead of one. Could this be the case for you?
Comment by Lee Stigile — October 5, 2009 @ 8:47 pm |
I have the same problem and I have verified it is double dash (e.g. –datadir=…).
Still mysql_library_init returns 1.
I use VC++ express and Mysql 5.1 and libmysqld as it comes with install package.
err file is created in the project_dir/data regardless of datadir parameter. I think this is because something is wrong in the parameters and init does not do its job.
The code is really minimal, see below.
Pls someone upload a working example including solution file and libmysqld.dll so I can try it and see the dif.
Thanks a lot!// ———————— Code ————————
#include “stdafx.h”
#include
#include
#include
#include
#include
#includeMYSQL *mysql;
MYSQL_RES *results;
MYSQL_ROW record;static char *server_options[] = {“mysql_test”,
“-–datadir=./data”,
“-–language=C:/Program Files/MySQL/MySQL Server 5.1/share/english”,
NULL
};
int num_elements = (sizeof(server_options)/ sizeof(char *)) – 1;
static char *server_groups[] = { “libmysqld_server”, NULL };void test()
{
int retval;
retval = mysql_library_init(num_elements, server_options, (char **)server_groups);Comment by Din — November 21, 2009 @ 4:15 pm
'Computer Science' 카테고리의 다른 글
| 탑 10 윈도우 모바일 어플리케이션 (0) | 2010.03.14 |
|---|---|
| 가트너 선정 2012에 예상되는 탑 10 모바일 에플리케이션 (0) | 2010.03.14 |
| JavaScript 지나온 날짜와 남은 날짜(D-day) 계산 (0) | 2010.02.28 |
| PMBok 버전3에서 버전4로의 변화내용 (0) | 2010.02.27 |
| PMP, Project Manager Professional 요약 (0) | 2010.02.27 |
I would not recommend to set datadir to the installation directory/data on Vista or later.
The file permissions/integrity settings are such that resulting program needs elevation to run properly, even for Administrators (http://bugs.mysql.com/bug.php?id=38373)
It is safer to copy the datadir somewhere else.
Comment by wlad — May 19, 2009 @ 11:17 pm |