SQLite vs. MySQL Embedded

Just wondering if anyone might have experience using MySQL embedded and how it is to work with? I have a project that I've started with SQLite, but thinking of switching to MySQL embedded. Just not sure if it would actually be any better.

Far from knowing but having seen similar questions to me it seems a bit like the old what filesystem should I use? The answer is the one that works for you.

As I hadn't ever seen mysql embedded I only have google but I'm seeing a mixed bag, why stop at those 2 for example, HSQLDB, H2 and Apache Derby (A few I saw mentioned). Then you have things like language api.

But from my crude understanding I really wouldn't expect it to be too different from each other i.e I would expect the pysqlite vs pymysql to be syntactically similar sql is sql. My only real knowledge of SQLite is from django and the cli interface though(vs standard MySQL with php and python), but I found it quite similar statement wise to mysql just have to remember the little syntax quirks.

So really I've not answered but said the one that has a better language api and performs best for your use case scenario.(I have seen mentions that for embedded mysql may not be the best choice, but I still think other things come into play, i.e scalability, language api, DB limits etc..). So to me it would sound like a bench testing or comparison of limits.

Yeah I was hoping someone might have knowledge of personally working with MySQL embedded. I've used standard MySQL quite a bit and like it, but for the project I'm working on making sure that the MySQL Engine is running would be a PITA. Plus its another piece to install.

I like SQLite for the fact of the database being 1 file and no server. Just having some troubles with it properly handling foreign keys. The tools for designing and managing the database aren't real impressive, except for SQLite Maestro. Its the best I found, but its missing 1 feature I need. My googlefu is yet to turn up a program that can diff SQLite database structures.

Until last night I didn't realize that MySQL embedded didn't run a separate engine process. I'm going to switch my project to using it, and see how I get along. MySQL has always worked well for me, and the tools are readily available.

The finished program will be a standalone single user Windows app. The end user won't be highly computer literate, so that's why I'm going for simple.

I should really step out you clearly know what you need and are after(I suspect c/c++ as well) but I did find 2 here http://www.filebuzz.com/files/Database_Diff_Tool/1.html that are windows based but prop and not freeware.

http://www.guiffy.com/ or http://www.sqlitecompare.com/

Then the other thing can't you do it in the code(for example http://python.org/doc/2.5/lib/module-difflib.html)? i.e.

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

I realise that would only give tables then you would need to do the columns, but as only structure surely the overhead would be minimal.

Any way from the sound of things it seems it's choosing the DB you use for you.

In regards to using my googlefu seems to be finding it is just starting the server that is different from normal mysql, the rest looks syntactically the same regardless of language binding(Well php and python).

Well as it turns out I've changed to standard MySQL. Hopefully the separate engine won't be a problem. SQLite3's single data file would have been nice, but the database kept corrupted on me. Wasn't handling foreign keys properly, and I don't have time to fight it. I at least know MySQL is stable and will easily handle what I'm doing.

Curious did you actually try mysql embedded? i.e was there a reason for choosing normal mysql over it?

You had me intrigued but as I only really play and lately I've been letting django do all the sql stuff didn't really feel motivated enough.

There was no native C# connectors for mysql embedded. Another option is to package the mysql server with your program with a new name and start it from your program. Seemed like a PITA to me, and a lot of extra space to waste.

It really did choose it for you.

Strange there isn't as unless I'm mistaken there is a c/c++ one(I would of thought a c# binding would be utilised a fair bit) but I have to admit I know little about them. So quickly went of looking to see how to achieve it with the languages I knew.

There is definitely a C++. The C# is coming, but may not be here until MySQL 6.