Unlike SQL Server, you cannot reliably move a MySQL database by moving the binary files in the MySQL directory. Instead, you should use the mysqldump utility.

Open a command line window to the MySQL bin directory and make sure both mysql and mysqldump binaries are both present. On Linux this location is likely /usr/local/mysql/bin and on Windows this is most likely C:\Program Files\MySQL\MySQL Server 5.0\

Dump the database using a command like this:
./mysqldump --user=user --password=pw database_name_here > db.sql

Then connect using the MySQL command line client and create the database:
./mysql --user=user --password=pw
create database new_database_name
use new_database_name
source db.sql

Labels: MySQL, SQL

In SQL Server, there are three ways to get the identity of the row you had just inserted: IDENT_CURRENT(), @@IDENTITY, and SCOPE_IDENTITY().

IDENT_CURRENT() is not limited by scope and session; it is limited to a table. For example, you can use IDENT_CURRENT('TableName') to get the last identity generated for table 'TableName'.

SCOPE_IDENTITY() and @@IDENTITY will return last identity values generated in any table in the current session. However, only SCOPE_IDENTITY() returns values inserted within your current scope (the scope of the SQL you just executed). @@IDENTITY is dangerous to use in my opinion because even though it is limited to session, it doesn't take into account identities that could have been made by triggers. SCOPE_IDENTITY() is always safe to use after making your insert to get exactly the identity you need.

For more information, check out the official MSDN documentation:
http://msdn.microsoft.com/en-us/library/aa259185(SQL.80).aspx

Labels: SQL, SQL Server

Sometimes you might want to randomize your dataset. In the SQL driven MP3 player I worked on, I wrote a stored procedure to handle putting together a playlist for the user (since all the song data is already in the database). There are probably a lot of other good uses for random selections and the code sample below works great.

In your select clause you can add an ORDER BY statement and use NEWID().
SELECT * FROM
TableName
ORDER BY NEWID()

Labels: SQL, SQL Server

By default MySQL stores its dates in 'YYYY-MM-DD HH:MM:SS' format. When you have to integrate with .NET, it's nice to get the date in a format which is readable by DateTime.Parse().

In your SQL query, you can format the date by using the date_format function.
SELECT date_format(YourDateHere, '%a %D %b %Y') FROM TableName;
Here are some example formatting strings and examples of their output (replace the formatting in the red part of the query above).

Labels: MySQL, SQL

The homepage for SQLite claims that it is the most deployed widely SQL database in the world. I don't know if that is true but SQLite is a powerful light-weight SQL database.
http://sqlite.org/

I discovered this library while I was talking to some folks on IRC (#C++ on EFNET). I was working on the Kahnadex Jukebox program at the time and needed a "default" database. The first version of the program I wrote required the MSDE to be installed which was way too heavy / hard to configure for end users. I dug into SQLite (version 3.x) and one immediate downside was that I had to get the source code and hack around before I could use it. Even though the documentation wasn't great I was pleased with the result.

Setup
Start off by just downloading the DLL. On the download page grab the version for Windows that says "This is a DLL of the SQLite library without the TCL bindings". After downloading the zip, you'll notice there is only a .DLL and a .DEF file. You'll need a header and a .lib file in order to compile against the DLL.

Open up the Visual Studio command prompt and navigate to the folder you unzipped the DLL/DEF to. You can then run this command:
lib /machine:i386 /def:sqlite3.def
This will generate the .lib and .exp for the DLL. What I did to find the header is download the source code for the library itself. I grabbed the "sqlite3.h" header file from the source code that was grouped as "individual source files". Once you grab the header just add the link to the .lib file in your compiler and you're ready.

Opening and closing the database
#include "sqlite3.h"

sqlite3* db;

if(sqlite3_open("my_database.db",&db)){
    printf("Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    //handle error here...
}
When you're done using the database, close it using:
sqlite3_close(db);
Executing Non-Queries
std::string sql = "UPDATE Song SET AlbumID = NULL;";

if(sqlite3_exec(db,sql.c_str(),0,0,0)){
    printf("Failed to update rows: %s\n",sqlite3_errmsg(db));
    //handle error here...
}
Executing Queries (SELECTing data)
Executing SELECT queries in SQLite is pretty horrible in my opinion. You execute it the same way as you do a non-query, except you will need to use the callback parameter.

//SQLite DB, SQL query, callback function name, data to pass to callback, error msg
sqlite3_exec(db,sql_query_here,callback_here,NULL,0);
//this callback gets called for EVERY ROW returned by the query
int SampleCallback(void* SpecialData,int argc,char **argv,char** ColumnName){
    //returned record is in argv
    //column names are in ColumnName
    //the SpecialData is a pointer which you can optionally pass in the exec statement
    int SongID = atoi(argv[0]);
    std::string SongName = std::string(argv[1]);
    return(0);
}
Other notes
In my experience SQLite is pretty slow. If you're after performance you'll definitely want to choose MS SQL Server, Oracle, or MySQL. SQLite only allows for one "connection" so to speak. You'll want to wrap a mutex around your DB calls or else you'll get unexpected behavior.

If you're a big MS SQL Server programmer like I am, you are probably used to getting the ID of the entry you just inserted. This is possible in SQLite. For example, if I insert a Song into my database and I want to get the identity of the Song entry that was just inserted... in SQL Server (in a stored procedure) you would use SCOPE_IDENTITY(). I had to ask around and then I found the SQLite function:
int myRowId = sqlite3_last_insert_rowid(db);

Labels: C++, SQL, SQLite