bah.odbc and Booleans

BlitzMax Modules Forums/Brucey's Modules/bah.odbc and Booleans

Glenn Dodd
 #1
Field VolumeActivity:Int
Field FrequencyUpdateable:Int
Field SpecialAllowance:Int

These are all Booleans in the database.
The queryrecordset shows the correct values of 0 or 1 (1,0,0 respectively)
but once i put them in the Activity type they all show 1.

I scanned the dbodbc.bmx file and found the valid types of value (int, float, double, string etc).
There is nothing for Boolean but i think BMax doesn't have a boolean type either.

Any idea what is wrong with my code below?

Cheer
Glenn


SuperStrict

Import BaH.DBODBC
'Include "DatabaseTypes.bmx"
Type TActivity
	Field ActivityID:Int
	Field ActivityName:String
	Field ActivityDescription:String
	Field VolumeActivity:Int
	Field FrequencyUpdateable:Int
	Field SpecialAllowance:Int
	Field ActivityNumberForSorting:Int
End Type

' You need to load a FILE DSN.
Local db:TDBConnection = LoadDatabase("ODBC", "C2D-Productivity", Null, Null, Null, Null, "C2D-Productivity.dsn")
' Check that the DB connection has been made
If Not db Then
	DebugLog("Didn't work...")
	DebugLog("Unable to Open the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
	Notify("Unable to Open the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
	End
Else
	DebugLog("Connected to the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
End If

' Check if the database has an error on opening
If db.hasError() Then
	errorAndClose(db)
End If
DebugLog( "Database Loaded")

If db.isOpen() Then
	DebugLog ("open the tblActivity table")
	Local query:TDatabaseQuery = db.executeQuery("SELECT * from tblActivity")
	If db.hasError() Then
		errorAndClose(db)
	End If
	' create a list to hold the activities
	Local ActivityList:TList=CreateList()
	DebugLog("create a list to hold the activities")
	While query.nextrow()
		Local record:TQueryRecord = query.rowRecord()
		DebugStop
		Local Activity:TActivity = New TActivity
		Activity.ActivityID = TDBInt(record.value(6)).value
		DebugLog(Activity.ActivityID)
		Activity.ActivityName = TDBString(record.value(1)).value
		DebugLog(Activity.ActivityName)
		Activity.ActivityDescription = TDBString(record.value(2)).value
		DebugLog(Activity.ActivityDescription)
		Activity.VolumeActivity = TDBInt(record.value(6)).value
		DebugLog(Activity.VolumeActivity)
		Activity.FrequencyUpdateable = TDBInt(record.value(6)).value
		DebugLog(Activity.FrequencyUpdateable)
		Activity.SpecialAllowance = TDBInt(record.value(6)).value
		DebugLog(Activity.SpecialAllowance)
		Activity.ActivityNumberForSorting = TDBInt(record.value(6)).value
		DebugLog(Activity.ActivityNumberForSorting)
		ListAddLast(ActivityList,Activity)
	Wend
End If
db.close()

Function errorAndClose(db:TDBConnection)
	DebugLog(db.error().toString())
	db.close()
	Notify("Read the DebugLog to see what the error was")
	End
End Function


DavidDC
 #2
It looks like you are loading the same database value into many different Type fields? record.value(6) sure gets a workout. But perhaps this is intentional?

Glenn Dodd
 #3
As Homer Simpson would say "DOH !!!"
To much copying and not enough modifying...

Thanks

GfK
 #4
Just to clarify - Blitzmax doesn't have a Boolean type as such. You'd use a Byte.

(You could obviously store up to 8 "booleans" in a single byte, if you were feeling particularly creative) :)

Glenn Dodd
 #5
Next Question:
This is what I had above and it works.

SuperStrict

Import BaH.DBODBC
'Include "DatabaseTypes.bmx"
Type TActivity
	Field ActivityID:Int
	Field ActivityName:String
	Field ActivityDescription:String
	Field VolumeActivity:Int
	Field FrequencyUpdateable:Int
	Field SpecialAllowance:Int
	Field ActivityNumberForSorting:Int
End Type

' You need to load a FILE DSN.
Local db:TDBConnection = LoadDatabase("ODBC", "C2D-Productivity", Null, Null, Null, Null, "C2D-Productivity.dsn")
' Check that the DB connection has been made
If Not db Then
	DebugLog("Didn't work...")
	DebugLog("Unable to Open the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
	Notify("Unable to Open the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
	End
Else
	DebugLog("Connected to the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
End If

' Check if the database has an error on opening
If db.hasError() Then
	errorAndClose(db)
End If
DebugLog( "Database Loaded")

If db.isOpen() Then
	DebugLog ("open the tblActivity table")
	Local query:TDatabaseQuery = db.executeQuery("SELECT * from tblActivity")
	If db.hasError() Then
		errorAndClose(db)
	End If
	' create a list to hold the activities
	Local ActivityList:TList=CreateList()
	DebugLog("create a list to hold the activities")
	While query.nextrow()
		Local record:TQueryRecord = query.rowRecord()
		DebugStop
		Local Activity:TActivity = New TActivity
		Activity.ActivityID = TDBInt(record.value(0)).value
		DebugLog(Activity.ActivityID)
		Activity.ActivityName = TDBString(record.value(1)).value
		DebugLog(Activity.ActivityName)
		Activity.ActivityDescription = TDBString(record.value(2)).value
		DebugLog(Activity.ActivityDescription)
		Activity.VolumeActivity = TDBInt(record.value(3)).value
		DebugLog(Activity.VolumeActivity)
		Activity.FrequencyUpdateable = TDBInt(record.value(4)).value
		DebugLog(Activity.FrequencyUpdateable)
		Activity.SpecialAllowance = TDBInt(record.value(5)).value
		DebugLog(Activity.SpecialAllowance)
		Activity.ActivityNumberForSorting = TDBInt(record.value(6)).value
		DebugLog(Activity.ActivityNumberForSorting)
		ListAddLast(ActivityList,Activity)
	Wend
End If
db.close()

Function errorAndClose(db:TDBConnection)
	DebugLog(db.error().toString())
	db.close()
	Notify("Read the DebugLog to see what the error was")
	End
End Function


now i am trying to put the database reads into types. I would end up with the base type of TDatabaseTables and an extended type for each table. Currently i am only getting tblActivity.

What I have is this:

SuperStrict

Import BaH.DBODBC

Type TDatabaseTables
	' You need to load a FILE DSN.
	Global db:TDBConnection = LoadDatabase("ODBC", "C2D-Productivity", Null, Null, Null, Null, "C2D-Productivity.dsn")
	' Check that the DB connection has been made
	If Not db Then
		DebugLog("Didn't work...")
		DebugLog("Unable to Open the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
		Notify("Unable to Open the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
		End
	Else
		DebugLog("Connected to the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
	End If

	' Check if the database has an error on opening
	If db.hasError() Then
		errorAndClose(db)
	End If
	DebugLog( "Database Loaded")

	Global query:TDatabaseQuery
	Global record:TQueryRecord

End Type

Type TActivity Extends TDatabaseTables
	Field ActivityID:String
	Field ActivityName:String
	Field ActivityDescription:String
	Field VolumeActivity:Int
	Field FrequencyUpdateable:Int
	Field SpecialAllowance:Int
	Field ActivityNumberForSorting:Int

	Global ActivityList:TList

	Function Create()
		query = db.executeQuery("SELECT * from tblActivity")
		While query.nextrow()
			record = query.rowRecord()
			Local Activity:TActivity = New TActivity
			Activity.ActivityID = TDBInt(record.value(0)).value
			DebugLog(Activity.ActivityID)
			Activity.ActivityName = TDBString(record.value(1)).value
			DebugLog(Activity.ActivityName)
			Activity.ActivityDescription = TDBString(record.value(2)).value
			DebugLog(Activity.ActivityDescription)
			Activity.VolumeActivity = TDBInt(record.value(3)).value
			DebugLog(Activity.VolumeActivity)
			Activity.FrequencyUpdateable = TDBInt(record.value(4)).value
			DebugLog(Activity.FrequencyUpdateable)
			Activity.SpecialAllowance = TDBInt(record.value(5)).value
			DebugLog(Activity.SpecialAllowance)
			Activity.ActivityNumberForSorting = TDBInt(record.value(6)).value
			DebugLog(Activity.ActivityNumberForSorting)

			If Not ActivityList Then ActivityList = CreateList()
			ListAddLast(ActivityList,Activity)
		Wend
	EndFunction
	
	db.close()
	
End Type

'----------------------------------------------------------
TActivity.Create()	
For Local ActivityData:TActivity = EachIn ActivityList
	DebugLog("ActivityID = " + ActivityData.ActivityID)
	DebugLog("ActivityName" + ActivityData.ActivityName)
	DebugLog("ActivityDescription" + ActivityData.ActivityDescription)
	DebugLog("VolumeActivity" + ActivityData.VolumeActivity)
	DebugLog("FrequencyUpdateable" + ActivityData.FrequencyUpdateable)
	DebugLog("SpecialAllowance" + ActivityData.SpecialAllowance)
	DebugLog("ActivityNumberForSorting" + ActivityData.ActivityNumberForSorting)
	DebugLog("   ")
Next
'----------------------------------------------------------

Function errorAndClose(db:TDBConnection)
	DebugLog(db.error().toString())
	db.close()
	Notify("Read the DebugLog to see what the error was")
	End
End Function


I am not calling the actual opening of the database (db) but i don't know where or how to do this.
And no doubt there is much that needs to be fixed here to get it working...

Cheers
Glenn

Brucey
 #6
That's not compiling, is it? :-)

All your normal code, needs to be in a Function/Method in your types, for starters.

You might change the base type to something like :
Type TDatabaseTables

	Global db:TDBConnection

	Method Init()
	
		If Not db Then
			db =  = LoadDatabase("ODBC", "C2D-Productivity", Null, Null, Null, Null, "C2D-Productivity.dsn")
		End If
	
		' You need to load a FILE DSN.
		' Check that the DB connection has been made
		If Not db Then
			DebugLog("Didn't work...")
			DebugLog("Unable to Open the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
			Notify("Unable to Open the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
			End
		Else
			DebugLog("Connected to the Database C2D-Productivity.mdb from the FILE DSN C2D-Productivity.dsn")
		End If
	
		' Check if the database has an error on opening
		If db.hasError() Then
			errorAndClose(db)
		End If
		DebugLog( "Database Loaded")
	End Method
	
	Field query:TDatabaseQuery
	Field record:TQueryRecord

End Type

perhaps calling Init() from your Create() in the sub-type :
Activity.Init()

You may also want to test that the db isOpen().

Also, if you want to get rid of some of the "noise", you can use these convenience methods from the query record :
Activity.ActivityID = record.GetInt(0)


Glenn Dodd
 #7
Thanks Bruce,
I knew in my mind what i needed to do but couldn't turn it into blitz code.

I assume if i looked harder i would find the "convenience" methods inside dbodbc.bmx?

Cheers

Brucey
 #8
In the Database mod docs, under TQueryRecord :-)