C++ connecting to sqlite

[telepunk]

Hi, c++ newb, here, fluent in PHP and MySql but having a hard time getting started with c++ and sqlite...

I've created a table in sqlite with two rows and insert some data (values)
First thing I noticed was when I closed sqlite and restarted it, the table was no longer there *mind blown* so obviously I'm really clueless if I don't know why this is happening right? Any help with that, thanks!

Secondly, assuming I do get the hang of creating tables, I have some rather large MySql tables I'll be figuring out how to import soon, how then, do I access that table using c++? I got this so far...

#include <iostream>
using namespace std;


int main ()
{
    
  //Connect to sqlite database
  // Do something with the data    
   
}

So, as you can see I just need to know how to access said database and then how to specifically, select * from saidTable and while there is a result do some stuff, the stuff part I can figure out.

Sorry to be such a newb, but any help and links in the right direction would be greatly appreciated! Thanks!! Great forum you have here, I'll contribute just as soon as I'm able.

[telepunk]

Update... for someone in 2018 that might find themselves in my shoes... I figured out how to import a csv file, what I did:

Create the table
create table fas10 (id smallint, thedate varchar(20), thetime varchar(20), open smallint, high smallint, low smallint, close smallint);

then just made sure the csv file was like this...

1, 02/21/2012, 10:40, 66.59, 67.20, 66.51, 66.57

and then I saved the csv file as import.csv and put into the same folder as the sqlite file (not sure if that matters) then typed

.separator ","
.import import.csv fas10

then...
type select * from fas10;

voila! streaming text for miles

but still stuck as ever on the other questions...

[Alrededor]

Here is a simple example that opens an sqlite3 database and reads a single row from a table.

The table contains two columns, a symbol column and a location column. The symbol column contains a stock symbol and the location column contains a full filespec to a text file on the disk containing historical price data for the stock. The class EquityDataSource contains two member fields, symbol_ and filespec_.

class EquityDataSource
{
public:
	.
	.
	.
	std::string symbol_;
	std::string filespec_;
	.
	.
	.
};

The function is a member of the class EquityDataLocator and its purpose is to retrieve the location of the price data from the database for a specific stock which is then stored in the EquityDataSource object. The stock symbol whose location is to be found is stored in eds.symbol_.

bool EquityDataLocator::getLocation( const std::string& databaseFilespec, const std::string& tableName, EquityDataSource& eds )
{
	int rc;
	rc = sqlite3_initialize();   // Initializes the library. If the library has already been initialized it has no effect.
	if ( rc != SQLITE_OK )
	{
		return false;
	}
	sqlite3 * db =0;   // This is a pointer to an sqlite3 database
	const char* vfs = 0;  // vfs stands for virtual file system which we are not using

	// Note the filespec is a C style string and the second parameter &db is a pointer to a pointer
	rc = sqlite3_open_v2( databaseFilespec.c_str(), &db, SQLITE_OPEN_READONLY, vfs );
	if ( rc != SQLITE_OK )
	{
		sqlite3_close( db );
		return false;
	}

	// Now we create an SQL command which is stored in an sqlite3_stmt data structure.
	// Note symColName_ is a member of EquityDataLocator
	sqlite3_stmt * stmt = 0;
	std::string s = "SELECT * FROM " + tableName + " WHERE " + symColName_ + " = '" + eds.symbol_ + "';";
	rc = sqlite3_prepare_v2( db, s.c_str(), s.size() + 1, &stmt, 0 );
	if ( rc != SQLITE_OK )
	{
		sqlite3_finalize( stmt );
		sqlite3_close( db );
		return false;
	}

	// Now we retrieve the row
	rc = sqlite3_step( stmt );
	if ( rc == SQLITE_ROW )
	{
		// Here we get a pointer to the location text ( stored in the second column of the table )
		// The 1 in sqlite3_column_text( stmt, 1 ) is the column number (zero based).
		// sqlite3_column_text( sqlite_stmt* stmt, int cidx ) returns const unsigned char* so the casts are necessary.
		void* p  = const_cast< unsigned char* > ( sqlite3_column_text( stmt, 1 ) );
		eds.filespec_ = static_cast< const char* > ( p );
	}
	else
	{
		sqlite3_finalize( stmt );
		sqlite3_close( db );
		return false;
	}

	sqlite3_finalize( stmt );
	sqlite3_close( db );

	return true;
}

I have added extra comments to the above to try and show what is happening.

I suggest looking at the online documentation at http://www.sqlite.org/docs.html

Also a good book on SQLite is Using SQLite by Jay A. Kreibich

I haven't used this very much so I'm not an expert. Every time I need to do something I have to dig out the reference manuals. Hope this helps a little.

[telepunk]

thanks! I'll check this out at work