Database

[johnno56]

Quick question.

Does anyone have instruction on how to create a simple database using QB64pe?

I need to record my blood sugar levels on a daily basis. Only three fields required. Date (automatic), Level and Comments.

Probably menu driven. Load, Save, Add, Edit, Exit etc.

I am not requesting anyone to write this. I would like to do that myself but I would need to know how...

Any advice or suggestions would be appreciated....

J

ps: Yes, I could probably use a DB application like LibreOffice to create it, but where is the fun in that? lol

[mdijkens]

For such a relative small use-case, I'd still prefer to read/write to a .csv-file.
Now that we can use all internal memory for our data, it isn't even a problem anymore to read a 1GB csv file in memory if that would be needed...

[mnrvovrfc]

Does anyone have instruction on how to create a simple database using QB64pe?

I need to record my blood sugar levels on a daily basis. Only three fields required. Date (automatic), Level and Comments.

Remember that if you need to sort any of the fields, the date field should start with the four-digit year. Maybe could get away with two digits, then month, then day. Visual Basic or some other BASIC had a "DateSerial" function but that could be a pain for human readable stuff. I don't know if that was ever ported to QB64(PE). Maybe all this doesn't matter since you have indicated the date as the most important field.

[bplus]

The simplest is 3 strings: one for date, one for level, one for comment and repeat.
You can do this on a word processor.

Next level towards Database for Basic is setup a User Defined Type
Type Blood
  Date as string * 10
  Level as Integer
  Comment as string * 255 ' <<< you have to decide a maximum number or characters if you hope to file it using RA*
End Type

* But this is a setup for doing it with Random Access but there are a 1000 other ways to do it without requiring fixed strings.
But for first learning Database app I recommend Random Access a step above 3 lines and repeat....

[SpriggsySpriggs]

One could use SQLite or MySQL. Both work great in QB64.

[SMcNeill]

I'd go with bplus's type approach with using a user type to store my data, but I'd arrange my data a little differently.

Type Blood
  Year as Integer
  Month as Integer
  Day as Integer
  Hour as Integer
  Minute as Integer
  Level as Integer
  Comment as string * 255 ' <<< you have to decide a maximum number or characters if you hope to file it using RA*
End Type


Why would I be so much more complex?

For when I needed to reference that data.   The way our data is written out here, we can now do a quick search for what our levels were on certain days/times.

2022-10-11-23-11...  This data would be for October 11th, 2022, at 11:11PM...  This format is easy as heck to sort your data and make it sequential, if that's ever needed.

Most dates are day, month, year, in string format...  If you ever sort your data, it'll make things a mess to work with!  You'll get stuff like:
"01-01-2000"
"01-01-2001"
"01-01-2002"

That'll be a PITA to deal with over time.  Now sure, you could parse that string, strip out the year, month, day, and then sort it so it'd be in a reasonable order...  OR... You could just be certain to structure your data to keep those parts separate and unique to begin with, and save yourself that hassle. 

[mdijkens]

It really depends heavily on what you want/need to do with it now or in the future.
If you manage/use it only in your QB-program, a random-file with a type-structure could work very well.
If you are yet unsure how you'll want to use the data in the long run, I'd opt for a 'output append' style csv, which you can open in excel any time to do your sorts/filters/graphs etc.
For storing the date you also have several options depending on the use:
'any local format' if it is only used for human reading
yyyymmddhhmmss if you want to stay flexible in formatting and sorting
epoch timestamp if you need to calculate with it (days=finish-start, hours=days*24, etc)

Depending on your needs/choices I can provide you with some functions for csv's (fields,io,sorts) and epoch date/time

[bplus]

Yes you are right mdijkens, it depends allot what you will want to do in future!

And I like Steve's breakdown of Type Blood for ease of later manipulations of data for figuring averages for instance. And surely you will want to include time of day for this particular appp.

PLUS you don't have to enter all those separate fields but build them off Date$ and Time$ with string manipulations.

@johnno56 are you sure you don't want us to write this for you? I'd like to see Spriggsy break it down with SQL ;-))

[Kernelpanic]

A lot of possibilities. Excel would be the easiest.

The date runs automatically.

[Kernelpanic]

One could use SQLite or MySQL. Both work great in QB64.

Interesting! How does that work? I just have an example from an older book on QuickBasic about accessing a dBase database. Thanks!