C++ and some type of SQL Database

[bigbearjeff]

Hi there. I am an experienced programmer from outside of the windows world. I have dabbled in C, but wanted to take the summer to delve deeper. I am curious if there is a certain SQL type database platform I should be looking to work with? Hopefully, something I can download free just to play around with tables etc.

In addition, I would appreciate any good books regarding interacting with databases in the C++ world.

Thanks in advance for any thoughts or insight you may have.

[minime]

SQLite is an excellent way to used SQL databases and is used by a lot of applications. There is the original SQLite source at http://www.sqlite.org/ and a Visual Studio System.data.sqlite (quick google for this will bring it up).
Its all well documented though

[andywestken]

SQLite is very popular, but it is mainly used for handling local databases. When you use SQLite, the client is linked directly to the database engine; there is no client/server separation as there is with "traditonal" databases.

The most well known open source SQL servers are (none of which are Windows specific)

MySQL -- The world's most popular open source database
http://www.mysql.com/
http://dev.mysql.com/

PostgreSQL: The world's most advanced open source database
http://www.postgresql.org/

Firebird: The true open source database for Windows, Linux, Mac OS X, and more
http://www.firebirdsql.org/

In addition, Microsoft, Oracle, and Sybase (and more, I presume) provide free versions of their servers. (In case you want to get experience with commercial systems)

SQL Server Express Edition
http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

Oracle Database 11g Express Edition
http://www.oracle.com/technetwork/products/express-edition/overview/index.html

Adaptive Server Enterprise version 15.7 Developer's Edition
http://www.sybase.com/ase_1500devel

When is comes to the API, in the case of SQLite you often talk directly to the database engine, so you using it's native API.

System.Data.SQLite is an ADO.NET adapter for SQLite. Being .Net, it is for use from C++/CLI code, not (regular) C++. You could write a mixed-mode app with the ADO.NET related code marked as managed and the rest of the code as native. But regular C++ code can use SQLite's normal C API, or a C++ wrapper if you really want to. e.g.

How to: Marshal ANSI Strings for ADO.NET (C++/CLI)
http://msdn.microsoft.com/en-us/library/ms235262.aspx

Regarding (other) database APIs available on Windows:

ADO.NET is the .Net way to talk to databases (Microsoft also provide LINQ which can be used to talk to SQL databases as well as other data sources.)

Before this there were/are:
* ADO - an older COM-based mechanism
* ADOX - a COM-based database management API (companion to ADO, which is for querying)
* OLE DB - another, lower-level, COM-based mechanism (now on the way out)
* ODBC - a C API
* DAO - a deprecated API

And then there are the various native APIs
* Connector/C (aka libmysql). for MySQL
* SQL Server Native Client API, for MSSQL
* ...

Note that while OLE DB was supposed to become the dominant API (ADO sitting on top of it), this position was revised not so long ago:

Microsoft is Aligning with ODBC for Native Relational Data Access
http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

Also, SQLite has become of interest to developers working on Windows 8 Metro apps. Metro apps have no way to communicate with remote databases via the normal route (i.e. the usual ODBC, ADO.NET route); they have to go via the cloud, or they're on their own with local file storage, which is exactly what SQLite provides.

Correct way to use databases in Windows 8 and Windows Phone 8
http://stackoverflow.com/questions/15165859/windows-8-metro-app-how-to-connect-to-ms-access-via-oledb

Finally, I was not sure whether you were just interested in working with SQL API's or if you wanted to gain some basic DBA skills, too. But from a job point of view, this is the current demand according to http://www.itjobswatch.co.uk

Jobs overall (marked as developer)

SQL Server  19862 (403)
Oracle      11674 (429)
MySQL        6177 ( 45)
Sybase        982 ( 15)
PostgreSQL    742
SQLite        109
Firebird        9

And then there are the dialects of SQL to worry about -- Transact SQL aka T-SQL (Microsoft and Sybase), PL/SQL (Oracle), etc.

Andy

Windows Data Access Components SDK
http://msdn.microsoft.com/en-us/library/windows/desktop/aa968814%28v=vs.85%29.aspx

System.Data.SQLite
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

What is a good OO C++ wrapper for sqlite
http://stackoverflow.com/questions/120295/what-is-a-good-oo-c-wrapper-for-sqlite
(The overall opinion was that it was better to use the SQLite C API directly)