Any users of vf_db in VFLib?

#1

Any users of vf::db out there? Since VFLib lacks documentation for the vf::db module, I’m trying got get started by following the SOCI documentation instead:

http://soci.sourceforge.net/doc/3.2/

I am, however facing some difficulties mapping the simple SOCI examples to vf::db.

First problem, opening the database. The following does not compile:

session sql("c:/mydatabase.db");

I figured I probably had to do it like this instead:

session sql;
sql.open("c:/mydatabase.db");

Then, running some queries. This does not compile:

int count;
sql << "select count(*) from phonebook", into(count);
DBG("We have " + String(count) + " entries in the phonebook.\n");

Due to:

Am I supposed to perform the queries like this instead? Via the get_query_stream()?

int count;
sql.get_query_stream() << "select count(*) from phonebook", into(count);
DBG("We have " + String(count) + " entries in the phonebook.\n");

This compiles, but fails to actually return any results from the database. I also tried other queries like inserting new rows into tables, but the database seems unaffected.

Please share any experience you might have with vf::db. Working code examples would be great!

#2

Yep sorry for the lack of documentation. You will probably have to use a statement object. soci used exceptions to return errors, which I disliked. Using “sql <<” there is no way to return an error object.

Here’s an example of working code:

  vf::Error error;
  vf::db::statement st = ( session.prepare <<
                           "SELECT "
                           "	VERSION, "
                           "	BYTES "
                           "FROM SEEK WHERE "
                           "	TRACKID=?"
                           , vf::db::into (version)
                           , vf::db::into (uncompressedBytes)
                           , vf::db::use (rid));

  if (st.execute_and_fetch (error))
  {
    //...
  }
  else
  {
    if (!error)
      error.fail (__FILE__, __LINE__, vf::Error::fileNotFound);
  }

You are correct that you have to use session.open(), also for the same reason that there is no way to return an error from the constructor other than by throwing an exception.

If you have other questions let me know. If you want to contribute documentation…I’m all for it :slight_smile:

#3

Thanks! That helped a lot.

I do have another question. Is it possible to fetch multiple results directly into juce arrays? (StringArray etc).

Right now, I’m using a while-loop to fetch one result at the time:

String filename;
statement st = (sql.prepare << "select filename from myfiles", into(filename));
if (st.execute().code() == Error::success)
{
	while (st.fetch(error))
	{
		if (error.code() == Error::success)
		{
			DBG("FILENAME: " + filename);
		}
	}
}

But would be nice to fetch directly into an array. It seems like SOCI is supposed to support fetch into STL containers, but haven’t figured out how to do it, and not sure if juce arrays are supported?

#4

It is not currently possible to fetch into an array. But this is something you could easily implement as its own stand alone function that just loops.

The original soci supported arrays but the syntax was clunky and there is the problem of reporting the error. So I cut it out.

#5

Thanks again, good to know (and it makes sense to always be able to get a proper error report). Fetching results one at the time works equally well I think. Should be sufficient.

#6

Note that vf::Error provides a conversion to bool using the safe_bool idiom so you can just do “if (!error) …”