EDA (Sqlite3) in console app

General help: new users, installing the Ecere SDK, using the IDE to compile and run applications.
Post Reply
samsam598
Posts: 212
Joined: Thu Apr 14, 2011 9:44 pm

EDA (Sqlite3) in console app

Post by samsam598 »

Greetings!

I tried to make use of EDA and make a simple program in console mode under windows,but I can't continue to the next step after this below.Simply put,I want to read and modify and write back some record from a sqlite3 database.How to make it under consoles?In the slider file under samples/db,I guess it makes use of some GUI controls which I don't want if in Console mode.
Thanks.

Code: Select all

 
DataSource ds;
Database db;
 
class MovieCollectionApp : GuiApplication
{
   MovieCollectionApp()
   {
      SetDefaultIdField("ID");
      SetDefaultNameField("Name");
      ds = DataSource { driver = "SQLite" };
      db = database_open(ds, "collection");
   }
   ~MovieCollectionApp() { delete db; delete ds; }
}
 
 
......
 
RowMovies r { };//=====???
while(r.Next())
{
   String name = r.name;
   PrintLn("\"", name, "\", added ",
      r.dateAdded);
   delete name;
}
delete r;
 
jerome
Site Admin
Posts: 608
Joined: Sat Jan 16, 2010 11:16 pm

Re: EDA (Sqlite3) in console app

Post by jerome »

Hi Sam,

EDA does provide some controls which makes it easy to build a GUI to edit database fields.

For example the ListSection / EditSection system which is used by the extras/genericEditor and in that MovieCollection sample, the generic FieldDataBox, and the more specific controls e.g. TableDropBox, DropDataBox, EditDropDataBox, FieldCheckButton, FieldDropDataBox, EditFieldDropDataBox.

But you are in no way tied to use the GUI, the main EDA API is just database access.

The core EDA classes are DataSource, Database, Table, Field and Row.

You open a database from a data source, and you can open a table from a database.
The Row class is used to add new rows, query existing contents or update contents.
With the SQLite driver you can use SQL language with Row::Query().
You can also use the eC active records system to access the database in an object oriented manner.

Usually when building an EDA app you will write your database schema using the dbtable keyword. (See movieSchema.ec)
This will list all fields in each table, with their type, the name in the database, and the identifier to use when acessing them OO style. This will also automatically create these row classes specific to each table. e.g. defininig the dbtable "Movies" automatically create both a RowMovies class as well as a Movie identifier class.

There are actually plans to make this more obvious by revising the dbtable keyword in favor of declaring it as a class ( http://ecere.com/mantis/view.php?id=515 )

I suggest you take a look at the accompanying slides for that sample at https://github.com/ecere/ecere-sdk/raw/ ... tation.odp if you haven't already.

But here are some key points:

Code: Select all

RowMovies r { };
r.Add();  // This would add a new row to the "Movies" table
r.name = "Crouching Tiger, Hidden Dragon"; // This sets an entry, the name property is automatically set up in the RowMovies class
delete r;
You could also use the generic Row class to do this but it would be more work:

Code: Select all

Row r { table = dbtable("Movies") };
r.Add();
r.SetData(dbfield("Movies", name);
delete r;
This still relies on having a schema however.

If you're trying to open a SQLite table that was not created with EDA and a schema, you'd need to do something like:

Code: Select all

DataSource ds { driver = "SQLite" };
Database db = ds.OpenDatabase("someDB.sqlite", no);
Table tbl = db.OpenTable("MyTable", { tableRows });
Field fld = tbl.FindField("SomeField");
Row r { tbl = tbl };
String name = null; // If we have a string field
r.sysID = rowIDtoOpen; // Or you can use while(r.Next()) to iterate through all rows...
r.GetData(fld, name);
However, the support to auto-generate the eda_table_fields table (which lists fields in all table with their types) from the SQLite schema (sqlite_master table) seems to either be broken or have some limitations at the moment.

Please let me know whether this helps and if you have any further questions :)

Best regards,

-Jerome
jerome
Site Admin
Posts: 608
Joined: Sat Jan 16, 2010 11:16 pm

Re: EDA (Sqlite3) in console app

Post by jerome »

I've filed this issue regarding opening tables not created by EDA:

http://ecere.com/mantis/view.php?id=1086
samsam598
Posts: 212
Joined: Thu Apr 14, 2011 9:44 pm

Re: EDA (Sqlite3) in console app

Post by samsam598 »

Below compiled but just crashed when running,sqlite3.dll and world.db3 are ready:

Code: Select all

 
import "EDA"
import "ecere"
 
 
class App:Application
{
   void Main()
   {
      DataSource ds{driver="SQLite"};
 
      Database db=ds.OpenDatabase("world.db3",no);
      Table tbl=db.OpenTable("city",{tableRows});
      Field fld=tbl.FindField("Name");
      Row row{tbl=tbl};
 
      String name=null;
      row.Query("select * from city");
      while(row.Next())
      {
         int i=0;
         row.GetData(fld,name);
         printf("Name No. %d: %s\n",i++,name);
      }
      system("pause");
   }
}
 
by the way,what does below suppose to do?

Code: Select all

 
Row row{tbl=tbl};
 
Gread thanks for all the help.
jerome
Site Admin
Posts: 608
Joined: Sat Jan 16, 2010 11:16 pm

Re: EDA (Sqlite3) in console app

Post by jerome »

Hi Sam,

Could you maybe send me that DB for me to take a look at it?
When you use Query() with GetData() or the Row OO members this way, query results must always be "ROWID, *":

Code: Select all

row.Query("select ROWID, * from city");
Otherwise Query() is meant to be used with BindQueryData() for filling '?' in the query, and GetColumn() to read data from the query's rows.

Do you have a field called Name in the table?
Is fld null?

As mentioned in my earlier post, if there is any 'references' or 'primary key' or anything other than basic field name / types using a table not created by EDA it won't work at the moment.

Row row { tbl = tbl } set up a Row object to be used with that table.

Cheers,

-Jerome
samsam598
Posts: 212
Joined: Thu Apr 14, 2011 9:44 pm

Re: EDA (Sqlite3) in console app

Post by samsam598 »

Sorry I should have provided the db file to you for your closer look at.

Here attached world.db3.

Thanks again.
Attachments
World.7z
(99.55 KiB) Downloaded 1378 times
jerome
Site Admin
Posts: 608
Joined: Sat Jan 16, 2010 11:16 pm

Re: EDA (Sqlite3) in console app

Post by jerome »

Hi Sam,

I took a look at world.db3 and you will run into issue
http://ecere.com/mantis/view.php?id=1086
because of the NOT NULL and other stuff in the table schema.

I also filed issue http://ecere.com/mantis/view.php?id=1088 to support a SQLite database with a different extension, that is the first problem you run into when your DB is named World.db3, it will not even try to open the right file.

The work around for 1086 is to manually create the eda_table_fields table in the DB with the proper name and types. Work around for 1088 is to rename your file with a .sqlite extension.

Again these issues only come up if this DB file will be coming from or be used with other non EDA-based software.

Please let me know if these workarounds do not work for you and you would like to see this fixed urgently.

Regards,

-Jerome
samsam598
Posts: 212
Joined: Thu Apr 14, 2011 9:44 pm

Re: EDA (Sqlite3) in console app

Post by samsam598 »

Thanks Jerome.

Here I tried to create eda_table,and made a copy of world.db3 and changed its extension to *.sqite(world.sqlite).it runs ,but no record printed in the consoole.It seems that just ran

Code: Select all

"system("pause")
.

Please help to figure me out where the problem is(I think there is something I did wrong when I tried to retrieve the records from the table,but I just don't know exactly what it is.):

Code: Select all

 
import "EDA"
import "ecere"
 
dbtable "city" city
{      
   // For each field:     
   int id "id";
   String name "name";
   String CountryCode "CountryCode";
   String District "District";
   int Population "Population";
};
 
//const char dbname[512]="E:\\learning\\ecere\\ex\\sqliteeda\\sqleda\\world.sqlite";
class App:Application
{
   void Main()
   {
      DataSource ds{driver="SQLite"};
 
      //Database db=ds.OpenDatabase("E:\\learning\\ecere\\ex\\sqliteeda\\sqleda\\world.sqlite",no);
      Database db=ds.OpenDatabase("world.sqlite",no);
      Table tbl=db.OpenTable("city",{tableRows});
      Field fld=tbl.FindField("Name");
      Row row{tbl=tbl};
 
      String name=null;
      row.Query("select * from city");
      while(row.Next())
      {
         int i=0;
         row.GetData(fld,name);
         printf("Name No. %d: %s\n",i++,name);
      }
      system("pause");
   }
}
 
jerome
Site Admin
Posts: 608
Joined: Sat Jan 16, 2010 11:16 pm

Re: EDA (Sqlite3) in console app

Post by jerome »

Hi Sam,

Again, terribly sorry that I missed those posts. Gmail had been classifying those forums notifications has spam lately :( If it takes more than a couple days for me to answer any question again, please e-mail me directly .

One thing I note in your sample there is that your Query call is not including the ROWID before the *, as mentioned in my earlier post:

row.Query("select ROWID, * from city");

However, it should have printed something if there was any entry in the cities?
How many rows were there in the DB? Is there more than one?
If there is only one it's possible that the Query() was already at the first row and then Next() went past the last record... Please let me know if this is still an issue as you've asked this over a month ago, and if it is please attach your DB with the eda_table_fields table you created so that I can test the code.

Regards,

-Jerome
Post Reply