Flat File Database Example

[Gablea]

Hi everyone,

I am sure I have read on here a long time ago a tutorial on how to save, update and delete from a Flat file database in Freebasic (like the ones use to use on QBasic) but i can not find it. and it has been a long time since I used flat file databases I was hoping if someone has a tutorial online that I can read up on so I can remember how to use these data storage methods.

I ask as I am hoping to create a simple Price check program for DOS (this is what the Price check terminal is running) and i was going to create the database from a csv data feed from a Windows MySQL Server (all ready have that side sorted)

the Idea is like this

the terminal boots and maps the shared folder from the server to a drive letter (works)
the system then boots into the Price check application and copys over from the shared folder the data feed (file is call dat.csv) and this works
then the system would read in each line of the csv file and then re generates the local flat file database (and this is where I am stuck)
Once the database file(s) have been created the system would the allow price checking to happen as it would use the local database.

The server application automatically generates a csv file if a price is changed how would I get the Price check app to keep its local database file in sync with the changes on the server? Should I use a time stamp on the file name and have it pull files that is newer then a given time? or just process the hole file again (can have from 1,000 to 25,000 lines in the database) or should I just have the server output a csv for the items that HAVE changed and then have the price checker just process them files?

[MrSwiss]

Short answers only ...

First: you're confusing two things here:

• "flat data file", e.g. *.cvs
  with
  "flat database file", e.g. SQLite etc.

Second: since you're referring to *.cvs files:

• simply copy them over to the client (at client start)
  refresh only, if a change happened (to the servers file)
  [to check above: use a diff-utility (don't simply rely on Date/Time stamps)]

Above can be done with a preset Time-Interval (each 5 Min.) or similar.
Only copy if a change is detected ...
But don't forget, to re-read the updated file (before continuing).

[Gablea]

When I mean flat data file I mean the sort of files that was made when you did

Type Products
Dim barcodenunber as string *13
Dim description as string *20
Dim pricetype as string *1
Dim productprice as integer * 20
End type

Dim ProductDatabase as Products

I'm not sure if the code is right but that is what I mean by database.

[MrSwiss]

... that is what I mean by database

That is called a Type definition (FB specific).
You could at best, describe it, as a Record of a DB-Table (see below). One of many Entry's.

A DataBase (short DB) is something else entirely. Check Wikipedia ...

[Gablea]

Oh sorry I just always called it a database

That does save the record to a file if I ever right to find a entry you have to loop though each record to find it.

Would I be right in remembering there was a article or
a tutorial on how to create save and edit these types of files.

[MrSwiss]

That does save the record to a file (or read or change it), if I ever right to find a entry you have to loop though each record to find it.

Correct, but I'd read the entire file to Memory first, into an array of 'Type Record'.
After that you can expand your different Procedures doing 'handling' of the type:

• read file (to array)
• search (in array, for speed)
• output (screen, print etc.)
• sort (the array, for speed)
• change (in array, for speed)
• write back (to same or different file)

You can of course, later use any of them, in any combination, to:

• create a sorted ... new or overwritten file
  create a changed and then sorted ... new or overwritten file
  etc.

Just some ideas ... you'll probably end up, writing your own Record-Handling-Library.

But, there is a lot of work, coming your way.

Would I be right in remembering there was a article or a tutorial on how to create save and edit these types of files.

Don't know right now, search the Forum, DOC etc. ...

[Gablea]

@MrSwiss

But, there is a lot of work, coming your way.

Thanks for the heads up I was expecting there to be a lot of work as it is DOS and I have linux on the main server (MySQL Database) wish there was a simple libary to allow access from DOS to a MySQL Server.

[sancho2]

I had the thought that you might be talking about random files. So I searched google for "Flat File Database" and Wikipedia has a good definition.
After reading you replies here I think random files are what you are thinking. I had been working on a project with random files and I stripped out the functions and created a demo.
In my version I do not read the whole file in. In fact there are probably many places where improvements can be made.
These kinds of files are best suited to small data sets. Since there is no key/indexing system file operations like search are done sequentially. Of course you could add an indexing system, but then the argument is "if you need an index, you should use a relational base".
Deleting an item in my version is accomplished by writing all the records to a temp file except for the deleted item and then deleting the dbase file and renaming the temp file back to the dbase file.
Another strategy is to add a field to the record and just mark the record as deleted without actually removing it from the file.
My code is mostly stripped from another program and some methods employed could be improved.

There are 5 files.
1. The main file with the main loop, menu, and a bunch of display routines.
2. File.bi - this module holds the declaration of a FileRec (file record) and DBFileType which is a class that encapsulates the file access.
3. File.bas - this holds the definitions for the member procedures of DBFileType
4. Search.bi - this holds the declarations of an object used to genereate a list of matches in a record search
5. Search.bas - this hold the definitions for the member procedures of the SearchList object

The Main File:

Code: Select all

'-----------------------------------------------------------------------------------------
' 07-22-2016 - 23:01:27 (mm/dd/yyyy)                                                      
' FFDBEx.bas
' Flat File DBase example                                                                                        
'-----------------------------------------------------------------------------------------
Const DBFILE_NAME = "C:\Freebasic Stuff\FFDBEx\FFDBase.dbf"
Const DBFILE_TMP = "C:\Freebasic Stuff\FFDBEx\FFDBase.tmp"	' used for deleting record

Const KEY_UP As String = Chr(255) + "H"
Const KEY_PGUP As String = Chr(255) + "I"
Const KEY_DOWN As String = Chr(255) + "P"
Const KEY_PGDN As String = Chr(255) + "Q"
Const KEY_ENTER As String = Chr(13)

Const KEY_LEFT_ARROW As String = Chr(255) + "K"
Const KEY_RIGHT_ARROW As String = Chr(255) + "M"

'-----------------------------------------------------------------------------------------
#Include Once "C:\Freebasic Stuff\FFDBEx\File.bas"
#Include Once "C:\Freebasic Stuff\FFDBEx\Search.bas"
'-----------------------------------------------------------------------------------------
' Nearly all of these procedures are used to create the display and menus 
Declare Sub ClearSearchListArea()		
Declare Function rnd_range (first As Double, last As Double) As Double
Declare Function GetRandomWord() As String
Declare Sub WriteRandomRecords(ByRef n As Integer = 100)
Declare Function SelectMatch(ByRef sList As SearchList) As Integer
Declare Sub MatchRecords()
Declare Sub SearchRecord()
Declare Sub EditRecord()
Declare Sub DeleteRecord()
Declare Function DoubleCheck(ByRef s As String) As boolean
Declare Sub ClearRecordInfo()
Declare Sub UpdateRecordCount()
Declare Function Validate(ByRef s As String) As boolean
Declare function AddRecord() As Integer
Declare Sub PrintMenu()
Declare Sub PrintRecordInfo()
Declare Sub PrintRecordHeaders()
Declare Sub PrintFileInfo()
Declare Sub PrintTitle()
'-----------------------------------------------------------------------------------------
' this is the main variable that is used to access the dbase
Dim Shared As DBFileType db = DBFileType(DBFILE_NAME)
'-----------------------------------------------------------------------------------------

' Uncomment the following two lines to create random records to play with 
' WriteRandomRecords()
' End 


ScreenRes 800,600
Width 800 \ 8, 600 \ 16		' this line gives us 8x16 fonts  

PrintTitle()			' create the display 
PrintFileInfo()
PrintRecordHeaders()
PrintRecordInfo()
PrintMenu()

Dim As String key
Dim As Integer prevRec

If db.RecordCount > 0 Then
	db.MoveToRecord(1)
EndIf

Do
	key = InKey()

	If prevRec <> db.CurrentRecordNumber Then
		PrintRecordInfo()
		prevRec = db.CurrentRecordNumber
	EndIf

	Select Case key
		Case "n", KEY_RIGHT_ARROW		' you can use the arrow keys to move to next 
			db.MoveToNextRecord()		' and previous records
		Case "p", KEY_LEFT_ARROW
			db.MoveToPrevRecord()
		Case "a"
			AddRecord()
			UpdateRecordCount()
			prevRec = 0
		Case "d"
			DeleteRecord()
			UpdateRecordCount()
			prevRec = 0
		Case "e"
			EditRecord()
			prevRec = 0
		Case "s"
			SearchRecord()
			prevRec = 0
		Case "h"
			MatchRecords()
			prevRec = 0
		Case "x"
			key = Chr(27)
	End Select
Loop While key <> Chr(27)
End
'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------
' MatchRecords - Create a list of records that match in part to the entered name 
'-----------------------------------------------------------------------------------------
Sub MatchRecords()
	'
	Dim As String s
	Dim As Integer n, w = LoWord(Width), selectedIndex
	Dim As FileRec records(any)
	ClearRecordInfo()

	Locate 7, 21:Print "Match for..."

	Do 
		Locate 9, 15:Input "", s
		If s = "" Then
			' we are short circuiting out of here
			Return 
		EndIf
	
	Loop While db.FindName(s) = TRUE   	
	
	n = db.FindName(s, records())
	
	If n > 0 Then
		Locate 23, 20: Print "Found "; n; " matching records. Press any key"
		Sleep
		Do:Loop While InKey <> ""
		
		Dim sList As SearchList = SearchList(n, records())
		
		selectedIndex = SelectMatch(sList)
		If selectedIndex > 0 Then
			db.MoveToRecord(selectedIndex)
		EndIf
	Else
		Locate 19, 20:Print "No matches found for "; s; ". Press any key" 	
		Sleep
		Do:Loop While InKey <> ""

		Locate 19, 1:Print String(w, " ")
	EndIf
	
	
End Sub
Function SelectMatch(ByRef sList As SearchList) As Integer
	' return the file index of the selected record
	Dim As Integer prevTopIndex = 0, prevCursorRow = 0, w = LoWord(Width), selectedIndex
	Dim As String key
	Dim As Integer result = 0

	' draw search menu
	Locate 23, 4:	
	Print "<Arrow Keys> Move cursor   <PGUP> Prev Page   <PGDN> Next Page   <ENTER> Select   <ESC> Exit"

	Do
		If prevTopIndex <> sList.topIndex Then
			sList.DrawVisibleItems()
			prevTopIndex = sList.topIndex
		EndIf

		If prevCursorRow <> sList.cursorRow Then
			If sList.cursorRow > sList.cursorMax Then
				sList.cursorRow = sList.cursorMax
			EndIf
			sList.DrawCursor()
			prevCursorRow = sList.cursorRow
		EndIf

		key = InKey()
		Select Case key
			Case KEY_PGDN		' next page
				ClearSearchListArea()
				sList.NextPage()
				prevCursorRow = 0
				prevTopIndex = 0
			Case KEY_PGUP		' prev page
				ClearSearchListArea()
				sList.PrevPage()
				prevCursorRow = 0
				prevTopIndex = 0
			Case KEY_UP			' move cursor up
				sList.MoveCursorUp()
			Case KEY_DOWN		' move cursor down
				sList.MoveCursorDown()
			Case KEY_ENTER		' select
				result = sList.GetSelectItem()
				key = Chr(27)
		End Select
		
	Loop While key <> Chr(27)
	
	ClearSearchListArea()
	Locate 23, 1: Print String(w - 1, " ") 

	Return result

End Function

Sub ClearSearchListArea()
	'
	Dim w As Integer = LoWord(Width)
	
	For x As Integer = 24 To 36
		Locate x, 1: Print String(w -1, " ")
	Next
End Sub
'-----------------------------------------------------------------------------------------
' SearchRecord - Find the first record that exactly matches the entered name 
'-----------------------------------------------------------------------------------------
Sub SearchRecord()
	'
	Dim As String s
	Dim As Integer n

	ClearRecordInfo()

	Locate 7, 21:Print "Searching for..."

	Do 
		Locate 9, 15:Input "", s
		If s = "" Then
			' we are short circuiting out of here
			Return 
		EndIf
	
	Loop While db.FindName(s) = TRUE   	
	
	n = db.FindName(s)
	
	If n > 0 Then
		db.MoveToRecord(n)
	EndIf
	
End Sub
'-----------------------------------------------------------------------------------------
' EditRecord - Change the name, street, or city of the current record 
'-----------------------------------------------------------------------------------------
Sub EditRecord()
	'
	Dim As String s

	Locate 9, 15:Input "", s
	
	if Len(s) > 0 AndAlso db.FindName(s) <> TRUE Then
		db.currentRecord.Name = s
	EndIf
	
	s = ""
	Locate 10, 15:Input "", s
	If Len(s) > 0 Then
		db.currentRecord.Street = s
	EndIf
	
	s=""
	Locate 11, 15:Input "", s
	If Len(s) > 0 Then
		db.currentRecord.City = s
	EndIf
	
	PrintRecordInfo()
	If DoubleCheck("Save the edits to this record?") = TRUE Then
		db.EditRecord()
	EndIf
	
End Sub
'-----------------------------------------------------------------------------------------
' DeleteRecord - Delete the current record 
'-----------------------------------------------------------------------------------------
Sub DeleteRecord()
	'
	If DoubleCheck("Are you sure you want to delete this record?") = TRUE Then
		db.DeleteRecord(db.CurrentRecordNumber)
	EndIf

End Sub 
'-----------------------------------------------------------------------------------------
' AddRecord - Add a record to the file
'-----------------------------------------------------------------------------------------
Function AddRecord() As integer
	'
	Dim As FileRec record
	Dim As String s
	
	PrintRecordHeaders()
	
	Locate 7, 21: Print "Pending..."
	Do 
		Locate 9, 15:Input "", record.Name
		If record.Name = "" Then
			Return 0
		EndIf
	
	Loop While db.FindName(record.Name) = TRUE   	
	
	Do
		Locate 10, 15:Input "", record.Street
	Loop While Validate(record.Street) = FALSE
	
	Do
		Locate 11, 15:Input "", record.City
	Loop While validate(record.City) = FALSE	

	Return db.AddRecord(record)

End Function
Function Validate(ByRef s As String) As boolean
	' Make sure the entered record field is not empty
	' other validation could go here
	If Len(s) < 1 Then
		Return FALSE
	EndIf
	
	Return TRUE
End Function
'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------
Function DoubleCheck(ByRef s As String) As boolean
	'
	Dim As Integer w = LoWord(Width), result = FALSE
	Dim As Integer l  
	Dim As String key
	
	s += " <Y>es or <N>o"
	l = (w - Len(s)) \ 2
	Locate 19, l:Print s 
	
	Do
		key = InKey
		Select Case key
			Case "y"
				result = TRUE
				key = Chr(27)
			Case "n"
				result = FALSE
				key = Chr(27) 
		End Select
	Loop While key <> Chr(27)
	
	Locate 19, 1:Print String(w, " ")
	Return result 

End Function
Sub PrintMenu()
	'
	Dim w As Integer = LoWord(Width)
	Locate 20, 1:Print String(w, "=")
	Locate 21, 1:Print "    <P>revious     <N>ext    <A>dd    <E>dit    <S>earch    Matc<h>    E<X>it" 
	Locate 22, 1:Print String(w, "=")
End Sub
Sub PrintRecordHeaders()
	'
	Dim w As Integer = LoWord(Width)

	View (0, 7 * 16) - (w * 8, 19 * 16), 0
	View
	Locate 7, 4:Print "Current Record:"
	Locate 9, 4:Print "  Na<m>e:"
	Locate 10, 4:Print "S<t>reet:"
	Locate 11, 4:Print "  <C>ity:"
	
End Sub
Sub ClearRecordInfo()
	'
	Dim w As Integer = LoWord(Width)
	
	Locate 7, 21: Print String(w - 21, " ")
	Locate 9, 15: Print String(w - 15, " ")
	Locate 10, 15: Print String(w - 15, " ")
	Locate 11, 15: Print String(w - 15, " ") 
End Sub
Sub PrintRecordInfo()
	'
	Dim As String s
	
	ClearRecordInfo()
	s = IIf(db.CurrentRecordNumber = 0, "No Record", Trim(Str(db.CurrentRecordNumber)))   
	Locate 7, 21: Print s
	Locate 9, 15: Print db.currentRecord.Name
	Locate 10, 15: Print db.currentRecord.Street
	Locate 11, 15: Print db.currentRecord.City 
	
End Sub
Sub PrintTitle()
	'
	Dim s As String = "Flat File DBase Example"
	Dim w As Integer = LoWord(Width)
	Dim x As Integer = (w - Len(s)) \ 2	
	Locate 1,x:Print "Flat File DBase Example"
	Locate 2,1:Print String(w, "=")
End Sub
Sub PrintFileInfo()
	'
	Dim w As Integer = LoWord(Width)

	Locate 3, 1:Print "Dbase File Name: "
	Locate 3, 18:Print DBFILE_NAME
	Locate 4, 1:Print "Record Count: "
	Locate 4, 14:Print db.RecordCount 
	Locate 5,1:Print String(w, "=")

End Sub
Sub UpdateRecordCount()
	'
	Dim w As Integer = LoWord(Width)
	Locate 4, 14: Print String(w - 13, " ")
	Locate 4, 14: Print db.RecordCount 
End Sub
'-----------------------------------------------------------------------------------------
' WriteRandomRecords - used to create dummy test data to play with
'-----------------------------------------------------------------------------------------
Sub WriteRandomRecords(ByRef n As Integer = 100)
	'
	Dim As Integer fNum, count
	Dim As FileRec record 
	fNum = FreeFile()
	Open DBFILE_NAME For Random As fNum Len = SizeOf(FileRec)
		count = Lof(fNum)\ SizeOf(FileRec)
		For x As Integer = 1 To n
			count += 1
			record.index = count
			record.Name = GetRandomWord()
			record.Street = GetRandomWord()
			record.City = GetRandomWord()
			Put #fNum,count, record 
		Next
	Close fNum 
End Sub
Function GetRandomWord() As String
	'
	Dim As Integer letterCount
	Dim As Integer letter
	Dim As String s
	' 3 to 10 letters
	letterCount = Int(rnd_range(3, 10))
	
	For x As Integer = 1 To letterCount
		letter = Int(rnd_range(97, 122))
		s += Chr(letter)
	Next
	
	Return s
End Function
Function rnd_range (first As Double, last As Double) As Double
    Function = Rnd * ((last + 1) - first) + first
End Function

File.bi:

Code: Select all

'-----------------------------------------------------------------------------------------
' 07-22-2016 - 23:02:24 (mm/dd/yyyy)                                                      
' File.bi
' #Include Once "C:\Freebasic Stuff\FFDBEx\File.bi"
'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------
' FileRec - this is a file record
' NOTE: I am not %100 sure, but I think fixed length strings must be used with
' 	     random files
'-----------------------------------------------------------------------------------------
Type FileRec
	As Integer index
	As String * 30 Name
	As String * 30 Street 
	As String * 30 City 
End Type

'-----------------------------------------------------------------------------------------
' DBFileType - this is the DBase file handling object
'-----------------------------------------------------------------------------------------
Type DBFileType
		Public:
		'--------------------------------------------------------------------------------------
		Declare Constructor (ByRef File_Name As String)		
		'--------------------------------------------------------------------------------------
		As String fileName 
		As FileRec currentRecord
		'--------------------------------------------------------------------------------------
		Declare Function FindName(ByRef s As String) As Integer  
		Declare Function FindName(ByRef s As String, found(Any) As FileRec) As integer 
		Declare Sub MoveToLastRecord()
		Declare Sub MoveToRecord(ByRef n As Integer) 
		Declare Sub MoveToNextRecord()
		Declare Sub MoveToPrevRecord()
		Declare Function GetRecord(ByRef n As Integer, ByRef record As FileRec ) As boolean
		Declare Function EditRecord() As boolean
		Declare Function AddRecord(ByRef record As FileRec) As Integer		
		Declare Function DeleteRecord(ByRef n As Integer) As Integer
		'--------------------------------------------------------------------------------------
		'--------------------------------------------------------------------------------------
		' these are used for testing only
		Declare Sub DumpRecord(ByRef n As Integer)
		Declare Sub DumpHeader()
		'--------------------------------------------------------------------------------------
		Declare Property CurrentRecordNumber() As Integer		 
		Declare Property RecordCount() As Integer
	Private:
		As Integer _currentRecordIndex = 0
		As Integer _recordCount = 0
		'--------------------------------------------------------------------------------------
End Type

File.bas:

Code: Select all

'-----------------------------------------------------------------------------------------
' 07-22-2016 - 23:10:44 (mm/dd/yyyy)
' File.bas                                                      
' #Include Once "C:\Freebasic Stuff\FFDBEx\File.bas"
'-----------------------------------------------------------------------------------------
#Include Once "C:\Freebasic Stuff\FFDBEx\File.bi"
'-----------------------------------------------------------------------------------------
' Constructor - The constructer takes the dbase file name as a required parameter
'					 It tests that the filename is accessable and if not it ends the program.
'-----------------------------------------------------------------------------------------
Constructor DBFileType(ByRef File_Name As String)
	'
	Dim As Integer result
	
	If Dir(File_Name) = "" Then
		result = Open(File_Name For Random As #1 Len = Len(FileRec))
		If result <> 0 Then
			Cls 
			Print "ERROR: cannot access " + File_Name + " in " __FUNCTION__
			Sleep
			End 
		EndIf
		this._recordCount = 0
	EndIf
	
	this.fileName = File_Name

End Constructor
'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------
' FindName - Finds all records with a name that contain the string s, or a name 
'			    that is contained in s. It fills the parameter found with these records
'				 and returns the count of found items.  
'-----------------------------------------------------------------------------------------
Function DBFileType.FindName(ByRef s As String, found(Any) As FileRec) As Integer
	'
	Dim As Integer count, result, fNum, numRecs
	Dim As FileRec record
	Dim As FileRec r
	
	numRecs = this.RecordCount
	ReDim found(1 To numRecs) 

	fNum = FreeFile()
	Open this.fileName For Random As #fNum Len=SizeOf(FileRec)

		count = 0
		For x As Integer = 1 To numRecs 
			Get #fNum,, record
			
			If InStr(record.Name, s) <> 0 OrElse InStr(s, record.Name) <> 0 Then
				count += 1
				found(count) = record
			EndIf
		Next
	Close fNum

	If count > 0 Then
		ReDim Preserve found(1 To count)
	EndIf

	Return count
		
End Function
'-----------------------------------------------------------------------------------------
' EditRecord - This replaces the file record with the current record. It is assumed that
'				   the current record has been edited.  
'-----------------------------------------------------------------------------------------
Function DBFileType.EditRecord() As boolean
	'
	Dim As Integer fNum, count
	
	fNum = FreeFile()
	Open this.fileName For Random As #fNum Len=SizeOf(FileRec)
		
		put #1, this._currentRecordIndex, this.currentRecord
	Close fNum
		
	Return TRUE
End Function
'-----------------------------------------------------------------------------------------
' DeleteRecord - This copies all the records from the dbase to a temp file except 
'					  the record index n. The dbase file is deleted and the temp file is
'					  renamed the dbase file 
'-----------------------------------------------------------------------------------------
Function DBFileType.DeleteRecord(ByRef n As Integer) As Integer
	' return number of records
	Dim As FileRec record
	Dim As Integer count = this.RecordCount 
	Dim As Integer sourceFile, destFile, result, x
	
	If count < 1 Then
		Cls
		Print "ERROR: trying to delete from an empty file"
		Sleep
		End
	EndIf
	
	
	If n < 1 Or n > count Then
		Cls
		Print "Error: trying to delete non existant record"
		Print "n: "; n; "    count:"; count
		Sleep
		End
	EndIf

	sourceFile = FreeFile()
	Open this.fileName For Random As #sourceFile Len=SizeOf(FileRec)

		destFile = FreeFile()
		result = Open(DBFILE_TMP For Random As #destFile Len=SizeOf(FileRec))
			If result <> 0 Then
				Cls
				Print "ERROR: problem opening temp file: "; DBFILE_TMP
				Sleep
				End
			EndIf

			Do While Not Eof(sourceFile)
				
				Get #sourceFile,, record
				x += 1
				
				If x  <> n Then
					Put #destFile,, record
				EndIf
			Loop
		Close destFile
	Close sourceFile

	Kill(this.fileName)

	result = Name(DBFILE_TMP, this.fileName)

	If result <> 0 Then
		Cls
		Print "ERROR: Problem renaming temp file to source file"
		Print "Temp File: "; DBFILE_TMP
		Print "Source File: "; This.fileName  
		Sleep
		End
	EndIf
	count -= 1
	If count > 0 then
		this.MoveToNextRecord()
	Else
		this._currentRecordIndex = 0
	EndIf
	
	Return count
End Function
'-----------------------------------------------------------------------------------------
' MoveToRecord - Move the current record to the record at index n
'-----------------------------------------------------------------------------------------
Sub DBFileType.MoveToRecord(ByRef n As Integer)
	'
	Dim As Integer count = this.RecordCount
	
	If count < 1 Then
		Return
	EndIf
	
	If n < 1 OrElse n > count Then
		Return
	EndIf
	
	If this.GetRecord(n, this.currentRecord) = TRUE Then
		this._currentRecordIndex = n
	EndIf
	
End Sub
'-----------------------------------------------------------------------------------------
' GetRecord - Return by reference the record at index n and TRUE, or FALSE if n is not
'				  a valid record 
'-----------------------------------------------------------------------------------------
Function DBFileType.GetRecord(ByRef n As Integer, ByRef record As FileRec ) As boolean
	'
	Dim As Integer fNum, result, count
	
	count = this.RecordCount

	If n < 1 Or n > count Then
		Return FALSE
	EndIf

	fNum = FreeFile()
	Open this.fileName For Random As fNum Len = SizeOf(FileRec)

		Get #fNum, n, record

	Close fNum
	
	Return TRUE

End Function
'-----------------------------------------------------------------------------------------
' MoveToPrevRecord - Move to the previous record or the last record if we are already 
'							at the top of the file (wrap around). 
'-----------------------------------------------------------------------------------------
Sub DBFileType.MoveToPrevRecord()
	'
	Dim As Integer n = this._currentRecordIndex
	Dim As Integer count = this.RecordCount 
	
	If count = 0 Then
		Exit Sub
	EndIf
	
	n -= 1 
	If n < 1 Then
		n = count
	EndIf
	
	If this.GetRecord(n, this.currentRecord) = TRUE  Then 
		this._currentRecordIndex = n
	EndIf
	

End Sub
'-----------------------------------------------------------------------------------------
' MoveToNextRecord - Move to the next record or the first record if we are already at the
'							last record (wrap around).
'-----------------------------------------------------------------------------------------
Sub DBFileType.MoveToNextRecord()
	'
	Dim As Integer n = this._currentRecordIndex
	Dim As Integer count = this.RecordCount
	
	If n = 0 Then
		Return
	EndIf

	n += 1 
	If n > count Then
		n = 1
	EndIf
	
	If this.GetRecord(n, this.currentRecord) = TRUE Then
		this._currentRecordIndex = n
	EndIf

End Sub
'-----------------------------------------------------------------------------------------
' AddRecord - Add a new record to the dbase
'-----------------------------------------------------------------------------------------
Function DBFileType.AddRecord(ByRef record As FileRec) As Integer
	'
	Dim As Integer recNum, fNum

	recNum = this.RecordCount + 1

	fNum = FreeFile()
	Open this.fileName For Random As fNum Len = SizeOf(FileRec)

		Put #fNum, recNum, record
		
	Close fNum
	
	Return recNum
End Function
'-----------------------------------------------------------------------------------------
' DumpRecord - dump the record at index n to the screen
'-----------------------------------------------------------------------------------------
Sub DBFileType.DumpRecord(ByRef n As Integer)
	'
	Dim As Integer fNum
	Dim As FileRec record

	If n < 1 OrElse n > this.recordcount Then
		Return
	EndIf
	
	fNum = FreeFile()
	Open this.fileName For Random As fNum Len = SizeOf(FileRec)
		Get #fNum, n, record
	Close fNum
	
	?
	With record
		Print "  Name: "; .Name
		Print "Street: "; .Street
		Print "  City: "; .City
	End With

End Sub
'-----------------------------------------------------------------------------------------
' FindName - Return the index of the first record that exactly matches s ignoring case
'-----------------------------------------------------------------------------------------
Function DBFileType.FindName(ByRef s As String) As Integer
	'
	Dim As Integer fNum, count = 0
	Dim As FileRec record
	Dim As String s1
	
	fNum = FreeFile()
	Open this.fileName For Random As #fNum Len=SizeOf(FileRec)

		Do While Not Eof(fNum)
			Get #fNum,, record
			count += 1
			s1 = record.Name
			If LCase(s1) = LCase(s) Then
				Exit Do 
			EndIf
		Loop
	
	Close fNum

	Return count
		
End Function
'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------
Property DBFileType.CurrentRecordNumber() As Integer
	'
	Return this._currentRecordIndex
End Property
Property DBFileType.RecordCount() As Integer
	' This property opens the file to retrieve the record count. This is probably
	' not the best way to handle record counts
	Dim As Integer result, numRecs, fNum
	
	fNum = FreeFile()
	Open this.fileName For Random As #fNum Len = Len(FileRec)
		numRecs = Lof(fNum) / Len(FileRec)
	Close #fNum
	
	Return numRecs		 

End Property

Search.bi:

Code: Select all

'-----------------------------------------------------------------------------------------
' 07-26-2016 - 20:29:34 (mm/dd/yyyy)                                                      
' Search.bi
' #Include Once "C:\Freebasic Stuff\FFDBEx\Search.bi"                                                                                        
'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------
' SearchItem - This is a type that is used in selecting from a list of records
'-----------------------------------------------------------------------------------------
Type SearchItem
	As Integer listIndex
	As Integer recNum
	As String Name
End Type
'-----------------------------------------------------------------------------------------
' SearchList - This is used to create a list of records to be selected from  
'-----------------------------------------------------------------------------------------
Type SearchList
	Public: 
		Const TOP_ROW As Integer = 25
		Const BOTTOM_ROW As Integer = 36
		Const VISIBLE_ITEMS As Integer = BOTTOM_ROW - TOP_ROW + 1

		Const LEFT_COL As Integer = 4
		As SearchItem items(Any)
		As SearchItem visibleItems(1 To 14)
		As Integer itemCount
		As Integer topIndex
		As Integer cursorRow
		As Integer cursorMax
		'--------------------------------
		Declare Constructor(ByRef count As Integer, list(Any) As FileRec)
		'Declare Function AddItems(ByRef count As Integer, ByRef list(Any) As Race) As Integer
		Declare Function GetSelectItem() As Integer
		Declare Sub DrawVisibleItems()
		Declare Sub DrawCursor()
		Declare Sub UnDrawCursor()
		Declare Sub MoveCursorToTop()
		Declare Sub MoveCursorDown()
		Declare Sub MoveCursorUp()				
		Declare Sub NextPage() 
		Declare Sub PrevPage()
	Private:
		Declare Sub _DrawItem(ByRef itemIndex As Integer, ByRef row As Integer)
			 
End Type

Search.bas:

Code: Select all

'-----------------------------------------------------------------------------------------
' 07-26-2016 - 20:28:21 (mm/dd/yyyy)                                                      
' Search.bas
' #Include Once "C:\Freebasic Stuff\FFDBEx\Search.bas"                                                                                        
'-----------------------------------------------------------------------------------------
#Include Once "C:\Freebasic Stuff\FFDBEx\Search.bi"
'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------
' Constructor - The constructor takes a count of items and an array of those items as 
'					 required parameters
'-----------------------------------------------------------------------------------------
Constructor SearchList(ByRef count As Integer, list(Any) As FileRec)
	'
	'ReDim this.visibleItems(1 To 14)
	If count < 1 Then
		Cls
		Print "ERROR: attempting a search selection with no matching records"
		Sleep
		End
	EndIf
	
	this.cursorRow = this.TOP_ROW
	this.topIndex = 1
	this.itemCount = count
	ReDim this.items(1 To count)
	
	For x As Integer = 1 To count
		With this.items(x)
			.listIndex = x 
			.recNum = list(x).Index
			.name = list(x).Name
		End With
	Next
End Constructor
'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------
' GetSelectItem - Returns the file index of the selected item
'-----------------------------------------------------------------------------------------
Function SearchList.GetSelectItem() As Integer
	'
	Return items((this.cursorRow - this.TOP_ROW) + this.topIndex).recNum
End Function
'-----------------------------------------------------------------------------------------
' MoveCursorToTop - Move the selection cursor to the top row
'-----------------------------------------------------------------------------------------
Sub SearchList.MoveCursorToTop()
	'
	this.UnDrawCursor()
	this.cursorRow = this.TOP_ROW
	this.DrawCursor()
End Sub
'-----------------------------------------------------------------------------------------
' MoveCursorUp - Move the cursor up one item or wrap to the last item  
'-----------------------------------------------------------------------------------------
Sub SearchList.MoveCursorUp()
	'
	Dim n As Integer = this.cursorRow
	
	this.UnDrawCursor()
	n -= 1
	If n < this.TOP_ROW Then
		n = this.cursorMax
	EndIf
	
	this.cursorRow = n
	
	this.DrawCursor()
End Sub
'-----------------------------------------------------------------------------------------
' MoveCursorDown - Move the cursor down one item or wrap to the first item 
'-----------------------------------------------------------------------------------------
Sub SearchList.MoveCursorDown()
	'
	Dim n As Integer = this.cursorRow
	
	this.UnDrawCursor()
	n += 1
	If n > this.BOTTOM_ROW OrElse n > this.cursorMax Then
		n = this.TOP_ROW
	EndIf
	this.cursorRow = n
	
	this.DrawCursor()
End Sub
'-----------------------------------------------------------------------------------------
' UnDrawCursor - Erase the cursor from the screen 
'-----------------------------------------------------------------------------------------
Sub SearchList.UnDrawCursor()
	'
	Dim As Integer l, r
	l = Len(Trim(Str((this.topIndex + (this.cursorRow - this.TOP_ROW)))))
	Locate this.cursorRow, this.LEFT_COL + 3 - l 
	Print " "

	r = len(this.items(this.topIndex + (this.cursorRow - this.TOP_ROW)).name)
	Locate this.cursorRow, this.LEFT_COL + 6 + r
	
	Print " "
		
End Sub
'-----------------------------------------------------------------------------------------
' DrawCursor - Draw the cursor on the screen
'-----------------------------------------------------------------------------------------
Sub SearchList.DrawCursor()
	'
	Dim As Integer l, r
	
	l = Len(Trim(Str((this.topIndex + (this.cursorRow - this.TOP_ROW)))))
	Locate this.cursorRow, this.LEFT_COL + 3 - l  
	Print Chr(178) '">"  'Chr(175)	'">"
	
	r = len(this.items(this.topIndex + (this.cursorRow - this.TOP_ROW)).name)
	Locate this.cursorRow, this.LEFT_COL + 6 + r
	
	Print Chr(178) '"<" 'Chr(174)	'"<"

End Sub
'-----------------------------------------------------------------------------------------
' PrevPage - Advance the list to the next page of items 
'-----------------------------------------------------------------------------------------
Sub SearchList.PrevPage()
	'
	Dim As Integer n = this.topIndex
	
	n -= this.VISIBLE_ITEMS
	If n < 1 Then
		Exit Sub
	EndIf

	this.topIndex = n
	
End Sub
'-----------------------------------------------------------------------------------------
' NextPage - Advance the list to the next page
'-----------------------------------------------------------------------------------------
Sub SearchList.NextPage()
	'
	Dim As Integer n = this.topIndex
	
	n += this.VISIBLE_ITEMS
	If n > this.ItemCount Then
		Exit Sub
	EndIf
	
	this.topIndex = n
End Sub
'-----------------------------------------------------------------------------------------
' DrawVisibleItems - Draw the items that are visible to the screen
'-----------------------------------------------------------------------------------------
Sub SearchList.DrawVisibleItems()
	'
	Dim As Integer x, n

	With This
		x = this.TOP_ROW
		n = .topIndex	
		While x <= .BOTTOM_ROW AndAlso n <= .itemCount  
			._DrawItem(n, x)
			n += 1
			x += 1
		Wend
		.cursorMax = x - 1
	End With
	
End Sub
'-----------------------------------------------------------------------------------------
' _DrawItem - Draw a list item to the screen 
'-----------------------------------------------------------------------------------------
Sub SearchList._DrawItem(ByRef itemIndex As Integer, ByRef row As Integer)
	'
	Dim As String indexTxt  

	With This.items(itemIndex)
		Locate row, LEFT_COL 
		indexTxt = String(4, " ") 
		RSet indexTxt, Trim(Str(.listIndex))
		Print indexTxt; ":"
		Locate row, LEFT_COL + 6
		Print .name
	End With
End Sub

Hope it helps.

[RockTheSchock]

If I remember correctly in QB PDS ISAM(Indexed Sequential Access Method) was introduced for RANDOM files, so that you could actually use theese files like a real database. The index file speeds up the search (reading) at the cost of complexity (lower write speed). You could simulate something like that in FreeBasic. If you expect to have more data than fits into RAM or later you want multiuser capability, you shouldn't use RANDOM files.

Why bothe with low level database stuff at all if you could just use SQLLite or MySQL or ...?

[MrSwiss]

I was expecting there to be a lot of work as it is DOS and I have linux on the main server (MySQL Database) wish there was a simple libary to allow access from DOS to a MySQL Server.

Well, in that case, you could 'embed' the required 'SQL-Query(s)' into your Application (calling MySQL on the
Server, over the Network). However, then, if, for any reason, the Network is 'down', you'll not be getting at
the Data! On the up-side of things: no syncing required at all!

At exactly this point, an engineering decision has to be made: which one, of the two approaches, is it to be???

As in many other project, this should have been thought about, in advance (preferably). Whilst building a
project plan ... sorry, this seems to be a pet-subject of mine: plan first, code later!
This will save you a lot, of later headaches, before they even become apparent, e.g. in this case: you'll
know to need:

• 1) Server (running a DBMS, MySQL in this case)
  2) Network connection (Server & Client side)
  3) SQL-language implementation (in the DOS App.)

See Paul Squires SQL implementation, here in the Forum, to get some ideas ...

The beauty of it: you don't need to care about, which OS is behind it (Server / Client), SQL does it all!

This is, what is called: using Middle-Ware, here SQL-language, for multi OS support, in Network(s) (aka:
Client Server Architecture).
This above is just to get the terminology straight (which you don't seem to be understanding properly).

[Gablea]

I would love to have the SQL commands embedded into the application and have it talking direct to the MySQL database. But I can not work that out as there is no mysqllib for dos (but I can see the Server in dos by pinning its IP address)

Does anyone know how I can talk to the MySQL server direct with out having to use the MySQL client lib? As the ones that are included with FreeBASIC do not complie in dos