Database

[johnno56]

First. I want to thank you all for your responses.

Second. I do not know how to add "user links"

This is what I actually do each morning:
Write the date in my log book.
Record the blood sugar level.
Write a comment if level is too high or too low.
Maybe once a month record my weight.

The log book is purely for reference. Since I started recording in December of 2021, I have no need to sort anything, as everything is logged 'by date'. My blood sugar meter keeps track of the last 7, 14, 30, 60 and 90 day averages.

I can understand the need to 'break down' the fields for refining sorting and the purpose of reporting. I have never used SQL or SQLITE and it 'seems' awfully complicated to manage a simple text file of data.

Bplus. "Are you sure you don't want us to write this for you?"

This old saying would answer that question better than I could... "Give a man a fish, and you feed him for a day. Teach a man to fish, and you feed him for a lifetime."

Is there a tutorial, using basic, to create a simple database? If not, no drama, I will just continue using the log book. Just thought it might be fun to make an application. I do appreciate all your suggestions and apologise if my responses sound ungrateful. That was not my intent....

This was all a 'spur of the moment' idea... (bplus. stop chuckling...) Perhaps I need to research this more deeply. In the meantime, to quote a questionably rated SciFi movie, 'Shall we play a game?' lol

[mnrvovrfc]

You'll have to put into careful consideration what data to include if you write it yourself. Now you say something about your weight. If you write your stuff with only three fields, then decide later you need a fourth, then you'll have to write a "converter" that goes from the "old" file with three fields, to a new file with four. IJS.

This is as opposed to using a spreadsheet or database program where the user could add or delete fields at will. Or it should be that way.

I think in that case otherwise a CSV creator and parser is good for the possibility of adding a field when one has hundreds of records already. Each entry for the new field could be added "by hand", ie. using an ordinary text file although it's clunky.

[bplus]

Tut for @johnno56

Breaking the ice with Random access

Code: (Select All)

'Tutorial (with help from Wiki):

' save this bas file in the folder where you want you BloodData.dat file to go first!

Type Blood
    date As String * 10
    level As Integer
    comment As String * 50
End Type

'Make a record:
Dim Shared Record As Blood, lenRecord&, nRecs&
lenRecord& = Len(Record)

' start up the data file
Open "BloodData.dat" For Random As #1 Len = lenRecord&


Record.date = "2022/11/18"
Record.level = 101
Record.comment = "This is my comment."

'Store a record:
Put #1, 1, Record

nRecs = LOF(1) / lenRecord&
Print "number recs:"; nRecs ' good 1 lets get it

' get record
Get #1, 1, Record
Print Record.date; ":"; Record.level; ", "; Record.comment

' stick another record in there
nRecs = nRecs + 1
Record.date = "2022/11/19"
Record.level = 105
Record.comment = "This is my next comment."
Put #1, nRecs, Record

' check records
nRecs = LOF(1) / lenRecord&
Print "number recs:"; nRecs ' good 2 lets see em

For i = 1 To nRecs
    Get #1, i, Record
    Print Record.date; ":"; Record.level; ", "; Record.comment
Next

[mnrvovrfc]

Tut for @johnno56

Breaking the ice with Random access

Don't forget to close the file when done.

This "tutorial" should have had "ERASE Record" right after the first put, then show the "comments" field to demonstrate a blank record is going to have data read back in again, then use "GET" to retrieve that first record.

But what if the user wants another field, after he/she did a bunch of records in this manner? What if the user wants "comments" to have more than 60 letters, a long time after he/she created his/her first database file? Why does "date" field have to have 10 characters and any date delimeters at all? These are a few questions and more that have to be answered...

[CharlieJV]

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

Yeah, I'd be heading to SQLite because I'm a fan of it.  No idea how to set that up with QB64.

Sounds like a fun project if anybody is interested: https://qb64phoenix.com/forum/archive/in...d-488.html

Other informative links:

• https://www.reddit.com/r/qb64/comments/p...e_in_qb64/
  
• https://www.sqlite.org/index.html
  
• https://www.sqlite.org/cintro.html
  

[bplus]

Tut for @johnno56

Breaking the ice with Random access

Don't forget to close the file when done.

This "tutorial" should have had "ERASE Record" right after the first put, then show the "comments" field to demonstrate a blank record is going to have data read back in again, then use "GET" to retrieve that first record.

But what if the user wants another field, after he/she did a bunch of records in this manner? What if the user wants "comments" to have more than 60 letters, a long time after he/she created his/her first database file? Why does "date" field have to have 10 characters and any date delimeters at all? These are a few questions and more that have to be answered...

Yeah, yeah, yeah  I am already in danger of doing too much for johhno56. 
Closing the file is important if you didn't know it gets closed when program run ends, good to do it officially.

The next thing I would do with this after I knew how make a record and add it to file would be to start a central Menu of tasks and first on that list would be to append a new record. Start a data entry screen... for adding and editing.

255 was reduced to 50 so record fit across screen without starting a new line. BTW I had to delete old file when I changed from 255 chars to 50 for comment because after first old entry it was reporting 4.xx records, ha!

To erase or delete a record is a little more advanced than a simple edit, you'd have to rewrite the file to reduce the record count, same with changing or adding fields...

Gotta leave some fun for Johnno to figure out ;-))

Lets see how serious he is about all this data-basing stuff. I've already done more than he in his research I bet.

[johnno56]

Serious? Not at all... The idea was just 'impulse' to "see if I can". I do not want you guys putting too much into this project. After all, I am not trying to create an MS-Access-type monster. Crumbs, the "log book", is basically (no pun intended) a "pencil and paper" venture. I have not produced a "basic-based" DB-type program before and figured, "Why not?". Worse case scenario is, if I find it too hard, then I will just continue with the log book... No sleep lost. If it works, then I have provided another reason to justify the existence of my PC... Cool...

Time to brush up on file I/O and string manipulation...  I may have some questions... Moo Ha Ha Ha....

[bplus]

It is a great exercise, glad to help

[Pete]

I've had to do so much work with files throughout the years. I don't find it fun for some reason. Maybe because it is not very creative. RA files have the advantage of being easy to overwrite records, but you either need to know the entry length max values or you have to do a hack where the length you open the file is 2-byes longer than the record you are entering. This works, but if you ever open up the file in notepad, it's rally ugly. Nothing lines up data field-wise. Sequential files don't have the file length dependency, but a record cannot be easily overwritten. To do requires you rewrite the entire file. The speed of QB64, especially when using the OPEN FOR BINARY as a faster way to access a SEQUENTIAL file, that I don't really consider it that much slower than using the RA method. Inserting entries for both requires either shuffling for RA files or rewrite part, insert new, and rewrite to end for sequential files. Finally, a neat thing I like about sequential organization is delimited data. You can pick a delimiter like a comma or the pipping symbol "|" or really what ever you want, including ASCII characters 0 - 255 if using _CONTROLCHR OFF. Anyway, for comma delimited files you would have...

date,time,value,notes or for piping: date|time|value|notes

Easy to retrieve the data as a single line and then just parse out the date, time, value, and notes with INSTR(). Programs like Excel Spreadsheets use this CSV (comma-separated values) format.

Good luck with t, and you have plenty of people resources here who can help you out along the way.

Pete

[mnrvovrfc]

The example bplus provided should be sufficient. One modification I would make is adding a "filler" field adding 100 bytes or so to each record, which bloats the data file but allows adding a couple of fields later if they are needed, only by adjusting the program.

We chose QB64(PE) to do this, which remains like Q(uick)BASIC (and maybe like Turbo Pascal) about handling FLS internally. No need to worry about having to add one more character to make room for CHR$(0). In part that's why I said what I said earlier about the date delimeters: in Freebasic absolutely you would have to do without them inside a field of STRING * 10 instead of STRING * 11.

This doesn't matter for someone who has to write such a program from scratch with either programming system. But it does matter a lot to somebody who has hundreds of data files carefully preserved and maintained from using 16-bit user programs regularly, and knows nothing about programming in C. What should we do about those people that used ISAM instead? There should have been a converter from it.