How to use the Sqlite library with Juce

[Gabriel1]

Hi !

I’m generating a database on sqlite, everything works in the main.cpp, and now I want to implement it in my VST (legacy).
With VS code 2019, I try to include the sqlite3.h without success.

At first, I tried to use “Amalgamation” with the :

• shellc
• sqlite3.c
• sqlite3.h
• sqlite3ext.h

By integrating them in my project folder in the Projucer. Without success

After some research, I make a second attempt using the Projucer.

Here is my second attempt:

• I download the .dll of sqlite
  
  
• I put it (sqlite3.dll & sqlite3.def) in the C:\ directory
• I declare the sqlite3 library in the external libraries to link
• I declare the path of the library in the Debug and Release files.

At each attempt, the IDE informs me that sqlite3 is not recognized.

How can I solve this problem please?

[mcmartin]

Hi Gabriel!

I know a bit about SQLite and I’d like to help you out.

@Gabriel1

everything works in the main.cpp,

Is this a JUCE Console Application project or something else? I’d like to understand in what context you got it working exactly.

Including the amalgamated source file is the best option, especially since it is quite hard for a VST plugin (almost impossible on Windows) to depend on dynamic libraries.

So you should try to go back to adding sqlite3.c and sqlite3.h to your Projucer project. Please give us more details about what you did exactly and what errors or unexpected behavior you get.

[xenakios]

@Gabriel1

At first, I tried to use “Amalgamation” with the :

• shellc
• sqlite3.c
• sqlite3.h
• sqlite3ext.h

By integrating them in my project folder in the Projucer.

Note that adding header files into the IDE project does not automatically make them available for the compiler. The compiler looks for header files in its header/include search paths. So you may need to add the directory where the sqlite3.h file is, into those.

[Gabriel1]

It works ! By declaring the path in the IDE where the amalgamated source file is located everything related to sqlite3 is well taken into account by VS code.
However, after compiling the project, I have 3 errors that appear with sqlite functions that I use in the project.

Is there any other configuration to do in the project parameters besides the path declaration of the sqlite files?

[xenakios]

That looks like now in turn you haven’t added the sqlite3.c file into the IDE project. (Contrary to how the header files work, .c/.cpp files can be passed for the compiler/linker by the IDE.)

Also as mentioned by mcmartin above, do not attempt to use sqlite as a DLL in a plugin, that’s very tricky to get working right.

[Gabriel1]

@xenakios

That looks like now in turn you haven’t added the sqlite3.c file into the IDE project. (Contrary to how the header files work, .c/.cpp files can be passed for the compiler/linker by the IDE.)

If I understand correctly, is there a place (compiler/linker) where I have to declare my different .c/.cpp files? If so, I don’t see where it is in the IDE, Do I have to install something?

@mcmartin

I’d like to understand in what context you got it working exactly.

I beg your pardon for not contextualizing!

My goal is to read a thousand data accessible offline. The problem is that the data will evolve and I would like to be able to modify them directly from my Google Sheet if needed, rather than in “editor.cpp” for simplicity.
And so Sqlite seemed to be just the thing to do the job.

@xenakios

Also as mentioned by mcmartin above, do not attempt to use sqlite as a DLL in a plugin, that’s very tricky to get working right.

Is there an alternative database system that can handle offline data in my VST or is it best to do this manually (in tables)?

[xenakios]

I mean you need to have the sqlite3.c file in your files to be compiled (you can add the file in the Projucer File Explorer). But do not add the shell.c file as that would cause conflicts with the Juce project.

Using sqlite3 for your use case might be overkill, but I can’t of course say for sure without knowing more details. Juce does already have facilities to use files, JSON and XML without the need to use external libraries.

[Mrugalla]

@Gabriel1

Is there an alternative database system that can handle offline data in my VST or is it best to do this manually (in tables)?

you can add any type of information to a ValueTree obj and write that into or read from an xml file. juce has a bunch of different objects for doing this per instance, per user and per computer etc

[railjonrogut]

I used the sqlite3 from VFLib… Just add it to the Pro Jucer project and exclude the 2 files as indicated in the image below:

VFlib

just include the header file:

#include “…/VFlib/vf_sqlite/vf_sqlite.h”

I have a few utilities:

String sqlite3TextToString (const uint8* pSqlite3TextValue, int iLength)
{
    if (pSqlite3TextValue == nullptr)
        return {};

    const CharPointer_UTF8::CharType* c = reinterpret_cast<const CharPointer_UTF8::CharType*>(pSqlite3TextValue);

    return String (CharPointer_UTF8 (c), CharPointer_UTF8 (c + iLength));
}

String sqlite3TextToString (sqlite3_stmt* statement, int column)
{
    return sqlite3TextToString (sqlite3_column_text (statement, column), sqlite3_column_bytes (statement, column));
}

bool sqlite3ReadBool (sqlite3_stmt* statement, int column)
{
    return sqlite3_column_int (statement, column) != 0;
}

NOTE: The above are SQLITE3 copies from my project which have some minor changes to set the SQLITE_THREADSAFE macro value to 1 in vf_sqlite.c – you should adjust that to your needs.

Rail

[Gabriel1]

@xenakios

need to have the sqlite3.c file in your files to be compiled

Yes, that was it! The project generated the .dll! However, no value from the database appeared in my display. And it’s impossible to know if the problem comes from the data recovery or from the insertion in the Compobox

How i integrate of data in the Compobox

std::list<std::string> lsTonalites;
HexapadDB::listTonalites(lsTonalites);
std::list<std::string>::iterator itTonalite = lsTonalites.begin();
int i(0);
for (; itTonalite != lsTonalites.end(); itTonalite++)
        m_tonalite.addItem((*itTonalite).c_str(), i++);

@railjonrogut

the sqlite3 from VFLib

I tested following your instructions, the integration with Projucer is userfriendly! However, in the vf_sqlite.c there is the following line which makes the program crash.

#include "AppConfig.h"

So I removed it and now I get the same result presented at the beginning of this post, generation of .dll but nothing in the Compobox.

Is the way I integrate the data in the Compobox correct?

[railjonrogut]

You get a run time crash when

#include "AppConfig.h"

is included??

Do you have the option enabled to “Use Global AppConfig Header” in your Pro Jucer project?

Rail

[Gabriel1]

Indeed, I had this setting at Disabled! Now the compilation is done without any problem, thanks Ray !

On the other hand, I still get no result on the display side. The Compobox is still empty, no data appears.

How can I display in the debugger output the data I get from the database?

[railjonrogut]

Use the DBG macro to get logging in Debug… or a FileLogger to get logging in debug and Release.

You haven’t indicated how you created or opened the database… and where you’re reading in the strings from the database… Personally I’d use a JUCE StringArray… but that’s just me.

Rail

[Gabriel1]

Finally I turned to .xml !

Thank you all for your help !

Wishing you a happy new year !