NewsForumsCodeLogsGallerySpecsSearch

Beginner Tutorial: Using SQLite with BlitzMax

BlitzMax Forums/BlitzMax Tutorials/Beginner Tutorial: Using SQLite with BlitzMax

assari(Posted 2006) [#1]
I have been meaning to learn how to use the SQLite modules that have been written for BlitzMax. I finally had the excuse to do this as an application I'm writing required a database engine.

Special thanks to TeaMonkey and Rene Aye whose examples I studied to figure out how to use the module.

The Tutorials are in two parts: Tutorial 1 and Tutorial 2.

**Added Part 3

Have Fun.


bruZard(Posted 2006) [#2]
wow ... thank you!!


Chris C(Posted 2006) [#3]
as ever, a well laid out and clear tutorial, well done!


RepeatUntil(Posted 2006) [#4]
Great tutorial!!
Maybe you could add (in this thread or in the tutorial) how we can use SQLite for games written with BlitzMax. Some clear examples of application could clarify the use of this tool!


assari(Posted 2006) [#5]
RepeatUntil,
I have no experience using this in a game but databases are not known for their speed. Quote from TeaMonkey's site
Be warned that a database query is pretty slow. Prepared statements are a big performance boost, as are memory tables, but they're still "slow" as far as a realtime game is concerned so try and keep your main loop queries to a minimum.

I remembered reading an article about SQLite and game programming but cannot locate it now :(


allos(Posted 2006) [#6]
Thank you for your very informative SQLite tutorial
I have a question:
when in need to use a SQL function in a query (eg MAX(), COUNT(),...) how can I manage its result?
in the following example of SQL query the value returned by the query shold be the total number of records; I understand that a callback function works on all subsequent records in a query; how can I manage the result of COUNT() function, eg print or store it in a variable?
--- query example (general) ----
SELECT COUNT(field) FROM database
the query shold return the number of records in database "database" where the field "field" is not null (this is my guess)

in BlitzMax we should have something like this:
rc = sqlite3_exec(db, "SELECT COUNT(field) FROM database",callback, Null, Null)

If I don't use a callback function ( ... null,null,null) how can I manage the result of SELECT statement?
If I use a callback function (normally called on every record) what are the parameters to use (where is the returned value stored)?
bye
allox


oraclelover(Posted 2006) [#7]
"I have no experience using this in a game but databases are not known for their speed. Quote from TeaMonkey's site"

Please keep in mind when you say databases are not known for speed that is relative to what you are doing.

If you have 5 terebytes of data I bet a database will beat out your c routine. Unless your right the same logic that the database uses to scrub datafiles for data. Which in that case you would be writing a database engine. A database may not be good for real time data most of the time, it excels at mountians of data.


assari(Posted 2006) [#8]
@oraclelover - did not mean to poke an ant's nest. The speed is relative as you say.

@allos, I've added a part 3 to answer your question. Take a look Part 3. Hope that answers your questions. AFAIK you need to use callbacks to get the result of the query.


HappyCat(Posted 2006) [#9]
You can get results without using a callback using something like this:

Local StatementHandle:int
Local Tail: Byte Ptr

' Executes the Command
Local ErrorCode:int = SQLite3_Prepare(db, Command, Command.Length, StatementHandle, Varptr(Tail))

' Make sure it executed correctly
If ErrorCode = 0 then

	' The Tail contains any part of the Command which wasn't executed
	' I've never had reason to use this myself
	Local UnusedSQL:string = string.FromCString(Tail)

	' Loop round all the rows in the resulting data set	
	Local Done = False
	Repeat
			
		' Move to the next row in the data set
		Local StepResult:Int = SQLite3_Step(StatementHandle)
			
		Select StepResult
		
			' Step has found the next row
			Case 100
				' If for example the first column is an integer
				Print SQLite3_Column_Int(StatementHandle, 0)
				' The next column is a string
				Print SQLite3_Column_Text(StatementHandle, 1)
				' The next column is a float
				Print SQLite3_Column_Double(StatementHandle, 2)
				
			' Step has reached the end of the data set
			Case 101
				Done = true
				
			' Step has resulted in an error
			Default
				RuntimeError(StepResult)
				
		End Select
	
	Until Done
	
	' Closes the data set
	SQLite3_Finalize(StatementHandle)
	
End If

Note that this code has been extracted from SQLite wrapper classes I was working on with the BMX v1.09 demo. The demo expired months ago and I haven't bought BMX yet so I haven't been able to finish the wrapper classes or test this code :-)

I've been planning to release my SQLite wrapper classes once I've bought BMX and finished them off :-)

Edit: As for using SQLite in games - I'm using it (in Blitz3D) for storing player profiles (options, high scores, stats etc) in Eridani. I guess I could use it for storing level data too, but then I'd have to write an editor for it and I find it much easier to just put that stuff into XML files.


allos(Posted 2006) [#10]
thank you Assari, I knew my question would have found an answer
great! (see you at the next stop)

bye
Allos


assari(Posted 2006) [#11]
@HappyCat, thanks for that info. I've yet to try out the SQLite3_prepare and its associated functionalities. I now understand a bit better how that works.

@allos, you're welcomed.


siread(Posted 2006) [#12]
Just trying to run part 1 but get this when compiling:

Linking:untitled1.debug.exe
C:/Program Files/BlitzMaxDemo/tmp/.bmx/untitled1.bmx.gui.debug.win32.o(code+0x47): undefined reference to `__bb_sqlite_sqlite_'
Build Error: Failed to link C:/Program Files/BlitzMaxDemo/tmp/untitled1.debug.exe


I've put TeaMonkey's mod in the mod folder but I'm just using the BlitzMax demo, so i'm not sure if that's an issue. Any ideas?


siread(Posted 2006) [#13]
No worries, it works fine in the new version.


Hosh(Posted 2006) [#14]
I'm running BlitzMax demo version 1.12(downloaded it yesterday).
I downloaded both versions of SQLite for BlitzMax.
(tm.mod and user.mod)
When running the example program in tutorial 1 I get the following error when using tm.mod

Linking:untitled2.debug.exe
C:/Program Files/BlitzMaxDemo/tmp/.bmx/untitled2.bmx.gui.debug.win32.o(code+0x47): undefined reference to `__bb_sqlite_sqlite_'
Build Error: Failed to link C:/Program Files/BlitzMaxDemo/tmp/untitled2.debug.exe
Process complete


When using user.mod I get the following error...

Linking:sqlite_trial.debug.exe
C:/Program Files/BlitzMaxDemo/bin/ld.exe: cannot find C:/Program Files/BlitzMaxDemo/mod/user.mod/sqlite.mod/sqlite.debug.win32.a
Build Error: Failed to link C:/Program Files/BlitzMaxDemo/sqlite_trial.debug.exe
Process complete

Neither one works for me. Apparently I just don't have the files it is looking for? I'm mystified.

Thanks for reading this!


KevCoder(Posted 2015) [#15]
Wow, this tutorial post is 9 years old. I guess it doesn't exist anymore ?

Regards
KevCoder


xlsior(Posted 2015) [#16]
Wow, this tutorial post is 9 years old. I guess it doesn't exist anymore ?


Archive.org still has a cached copy from February 2015:

http://web.archive.org/web/20150202010021/http://www.2dgamecreators.com/tutorials/database/

That said: IIRC the tm module is fairly basic, and quite outdated by now (it's over ten years old) -- If you're looking at SQLite, I'd strongly suggest you look for Brucey's SQLite module instead.


KevCoder(Posted 2015) [#17]
Thank you xisior, greatly appreciated.

I am migrating from VisualBasic and am very impressed with what can be done in BlitzMax.

Purchasing Logic GUI was a huge help in making the transition.

Thanks again for taking the time to put up the link.

I will follow your advise :-)