Need recommendation on Database

[argumentum]

2 minutes ago, Trax said:

Local Const $SQLITE_DLL = "C:\SQLite\sqlite3.dll"

that is so you can load your DLL in your PC. Your path. Change C:\SQLite\sqlite3.dll to C:\<your path>\sqlite3.dll in your PC. If you dont have the DLL, you can get it from this site.

[Trax]

Thanks @argumentum

[jchd]

Sorry for not mentionning that. Also if you're using x64 (which I don't recommend for now), then the Dll has to be named sqlite3_x64.dll

[Trax]

This has been interesting  @jchd I am pulling things from your snippets to get started and of course am struggling. I did finally manage to get the database and table created. Here is the table statement:"

"CREATE TABLE MeterReadings (Meter INT PRIMARY KEY not null, Date CHAR(10) not null, Time CHAR(8) not null, Reading REAL not null, Error CHAR(1));". via the sqlite3.exe I have verified that the database and table exists. What I haven't been able to do is add records to that table. It keeps throwing an error. Here is the insert command:

insert into MeterReadings values (1,2020/03/06,09:03:57,0,Y)

I know I have some finer point to master, especially storing dates and times, but I am taking baby steps. Any idea why my simple insert command is throwing an error?

 

 

[jchd]

I'm leaving right now and I'll be back in the (my) evening (Paris time).

Meter isn't a primary key since a PK must be unique. Use the implied rowid or make Id an alias; think of that as a meaningless unique identifier for a row, like a handle of a Windows control or the address of a function.

"CREATE TABLE MeterReadings (Id INT PRIMARY KEY not null, Meter INT, Date CHAR not null default CURRENT_DATE, Time CHAR not null default CURRENT_TIME, Reading REAL not null, Error INT default 0);"

To insert:

insert into MeterReadings (Meter, Reading) values (1, 57.0), (2, 43.5);           (for meters 1 & 2 w/o error)
insert into MeterReadings (Meter, Reading, Error) values (1, 57.0, 0), (3,39.1, 1), (2, 43.4, 0);           (for meters 1, 3 & 2, 3 with error)

This take advantage of the default values.

More later if needed.