SQLite3 getAllRows

Scarlet Thread · 10-14-2013 08:32 AM

Hi all,

I create a database in SQLite Administrator. Make a Raw File in EE2.0 editor and link that raw file to the Database file I made in SQLite Admin.

I call sql.connectSQLite(EncodeFileName(UID)); and it returns true.

I then call sql.getAllRows("Table_Name", Str* msg); and it returns false with the message saying "No Such Table".

Anyone tried this in EE2.0 with loading from UID. I think what its doing is its creating the database instead. Not sure what I'm doing wrong.

Here's the code:

Code:

void ItemLibrary::Load()

{

   SQL  sql;

   Str  messages;

   Clear();

   if(sql.connectSQLite(EncodeFileName(UID(1062835271, 1239402909, 1806623637, 3017244838))))

   {

      if(sql.getAllRows("RetailProducts", &messages))

      {

         DragDropItemData* newItem;

         for( ;  sql.getNextRow() ;  )

         {

            newItem = new DragDropItemData(sql);

            itemData[newItem->GetType()].push_back(newItem);

         }

      }

      else

      {

         MsgManager::Inst()->CreateMessage( S + "Warning: Cannot Load Table:\n" + messages); 

      }

   }

   else

   {

      MsgManager::Inst()->CreateMessage( "Warning: Could Not Open Database" ); 

   }

}

**Esenthel** · 10-14-2013 09:14 AM

Hi!

Quick question: after creating the table in the "SQLite Administrator", did you do "Reload" on the raw file element in EE?

Scarlet Thread · 10-14-2013 11:16 AM

(10-14-2013 09:14 AM)Esenthel Wrote: Hi!

Quick question: after creating the table in the "SQLite Administrator", did you do "Reload" on the raw file element in EE?

Yes

I've attached the test database file that I imported to EE. I tried 2 different visual SQLite Database editors and both gave me the same result.

**Esenthel** · 10-14-2013 01:01 PM

Hi!

Thanks! I've figured it out smile

The SQL.connectSQLite does not take into account neither DataPath file path or Paks, that's because SQLite supports operating on databases only as a file (and not a file inside another file, like in Pak's)

I'll need to investigate if it's possible at all to make it work with either reading sql database from memory, or from file inside another file (for read-only).

For now you could make it work by specifying the full path to the file:
sql.connect..(DataPath()+EncodeFileName(..));
however this will not work after publishing, because for publishing the DataPath is not set, but resources are loaded from Paks (and as I've stated earlier, reading sqlite from Paks is not available at the moment), for publishing you could workaround it by first extracting the sql db file from the pak file. Like this:
FCopy(EncodeFileName(), S+"Bin/"+EncodeFileName());
then read from the file sql.connect(S+"Bin/"+EncodeFileName());

I'll try to do something about it soon.
Or if anyone knows how to setup sqlite to read databases from memory then please let me know.

Scarlet Thread · 10-14-2013 01:23 PM

Thanks for the help wink

(10-14-2013 01:01 PM)Esenthel Wrote: for publishing you could workaround it by first extracting the sql db file from the pak file. Like this:
FCopy(EncodeFileName(), S+"Bin/"+EncodeFileName());
then read from the file sql.connect(S+"Bin/"+EncodeFileName());

I'll definitely give this a try.

**Esenthel** · 10-14-2013 01:26 PM

Or alternatively if you need a fully working solution right now (before I figure out the sql from paks), then you could also consider using XmlData or the new TextData, they both support reading from paks and DataPath.
TextData data; data.load(EncodeFileName(..));

para · 10-14-2013 05:44 PM

I think It can be done but it's a bit tricky (implementing a new virtual file system layer):
http://sqlite.org/c3ref/vfs.html
http://sqlite.org/c3ref/io_methods.html

**Esenthel** · 10-17-2013 11:59 AM

Thank you Scarlet Thread for reporting the issue, and para for providing the links!
Great news - it will work ok in next release, just finished adding this smile

Scarlet Thread · 10-17-2013 01:14 PM

Thank you very much for another quick fix smile

SQLite3 getAllRows
Author	Message