NewsForumsCodeLogsGallerySpecsSearch

bah.database

BlitzMax Forums/Brucey's Modules/bah.database

Chris C(Posted 2007) [#1]
just been messing with Bruceys new database abstraction module and I have to say its up to Bruceys impeccable standards, from first sight it seem to have everthing you could possibly need wrapped in an easy and logical set of objects

very well done and thanks brucey ;)

and yes if its up to your usual standards you should build a Date/Calendar module :p !!
(max is sadly lacking in this department...)


gman(Posted 2007) [#2]
i concur! fantastic work.


ninjarat(Posted 2007) [#3]
wow.... 8)


Brucey(Posted 2007) [#4]
Thanks.

Have now added a PostgreSQL module too.

Finally managed to get some Mac testing done, so there's an update to the ODBC module (1.01) to fix a couple of pointer/cast issues (ack, I hate C :-p )

I can confirm that all my tests for each module perform the same on all platforms. That is, I get the same results :-)

Notes for Mac users. Some modules require it link to the database-client shared objects (.dylib) - like how Linux works. However, the current version of bmk doesn't support this without tweaking (the next version, when it arrives should be fixed). Follow this tweak for a temporary fix.

Firebird next, I guess (via ibpp)... though there are more bmk issues to fix before most people will be able to use it.

:-)


kronholm(Posted 2007) [#5]
very nice, will definitely try it out once I get to that part of my game


Brucey(Posted 2007) [#6]
Added an mSQL database module to the site today. ( http://brucey.net/programming/blitz/ )

Takes the database support tally to 5 currently :

mSQL
MySQL
ODBC
PostreSQL
SQLite

(and scarily, the total module count to 20 !)

Haven't as yet tested mSQL on Win32, but it works great on Linux/Mac for now.

Still on my list of maybe's :

Informix
DB2
Oracle
Firebird

:o)


Brucey(Posted 2007) [#7]
Hi People,

Another new Database module announcement....

This one is called DBXbase, an implementation of the open-source Xbase and xbSQL libraries.

Xbase is a cross-platform dBase, FoxPro, Clipper etc. compatible library (that is, .dbf files and the likes).
xbSQL wraps that library in SQL queries.
And of course, DBXbase wraps up all of that into yet another database module.

Tested and working on all three platforms.


All my modules are available from : http://brucey.net/programming/blitz/

Current Database module availability :
* mSQL
* MySQL
* ODBC
* PostreSQL
* SQLite
* Xbase


Enjoy ;-)


Russell(Posted 2007) [#8]
Nice work!

Russell


siread(Posted 2007) [#9]
Hi Brucey. I've been playing with bah.DBSQLite but have run into some problems.

If I have a table with no rows the following code gives me the error: "Attempt to access field or method of Null object." 

        Local id:Int
	Local query:TDatabaseQuery = db.executeQuery("SELECT * FROM fixtures")
	
	If db.hasError() Then
		errorAndClose(db)
	End If
	
	While query.nextRow() 	
		Local record:TQueryRecord = query.rowRecord()
--->          id = record.value(0).getInt()
        Wend


If the table has no rows then the program shouldn't enter the While loop. Am I doing something wrong?


Brucey(Posted 2007) [#10]
Am I doing something wrong?


No, I should probably fix that.

SQLite is "special" in that you need to retrieve the first row to get the field types etc. (internally that is... not that *you* have to).

Still... no excuse ;-)


Brucey(Posted 2007) [#11]
New version of dbsqlite is available from the website.

Sorry about the problems...


siread(Posted 2007) [#12]
Wow, that's fast work. Excellent.


siread(Posted 2007) [#13]
Hi Brucey. I keep getting errors from seemingly simple queries...

Local query:TDatabaseQuery = db.executeQuery("SELECT * FROM club WHERE id=1")
If db.hasError() Then errorAndClose(db)

Produces...
DebugLog:(3) Error preparing statement :

If i run the same statement on the database in another app it's fine. Any ideas?


Blueapples(Posted 2007) [#14]
You're missing a quotation mark there.


siread(Posted 2007) [#15]
Ah, yes. That isn't the issue though, I just made a mistake copying the code from my program.


siread(Posted 2007) [#16]
Oh man. My bad. I've been going round in circles for hours, loading different dbs, trying different queries and stuff...

Turns out the error was from a previous statement which I hadn't error checked. I'd assumed that a new query would flush any existing error codes. (To assume makes an ass out of me.)

So, it's full steam ahead again. Great work Brucey!


Brucey(Posted 2007) [#17]
I'd assumed that a new query would flush any existing error codes


It probably should... I'll take a look at where it's resetting things.
Have you just been using ExecuteQuery ?

Sorry you've been having troubles with it.


siread(Posted 2007) [#18]
Well, the troubles are of my own making really. Things like calling "DROP TABLE" when a table doesn't exist throws an exception in bah.database, but the mod I was using before simply ignored these queries. I was happy to just let things run on as it was working ok but the consistent error checking in your bah.database has highlighted quite a few mistakes in my sql code. So it's been a day well spent. :)

It would be nice if the error messages were a little more descriptive though. Things like "Column does not exist". That would have saved me a headache. ;)


Brucey(Posted 2007) [#19]
I've just updated both BaH.Database and BaH.DBSQLite with some improvements, which are available from the usual place.

BaH.Database (1.03)
 * Fixed clearing of lasterror after successful query prepare/execute.


BaH.DBSQLite (1.04)
 * Improved error message details.


After calling executeQuery on the db, or prepare/execute on the query, if they are successful "hasError()" should return false.

For sqlite, I'm now returning the full error details, which should highlight the general location in the SQL that are broken - dunno why I wasn't doing this before.

:o)


siread(Posted 2007) [#20]
I have a problem Brucey. If I open a connection to a database (SQLite), run a query, then close the connection, it's not possible to delete the file using DeleteFile().

It's as if the file is still in use. I notice that if I take out the query on the database it can be deleted as normal.


Brucey(Posted 2007) [#21]
Hi Si,

Looks like a query was holding a lock on the database.
Changed now so that Close makes sure that all the it's queries are freed before itself attempts to close.

Yet another new version available from the usual place.

Perhaps my testing has a lot to be desired... :-/


siread(Posted 2007) [#22]
Perhaps my testing has a lot to be desired... :-/


With support like this I'm happy to help iron out the bugs. :)

Sorry if I'm getting annoying, but I've been running some speed comparisons between bah.database and teamonkeys sqlite mod (with pyropixel's SQLiter).

When SELECTing 1650 rows with "SELECT * from person" I get the following results...

bah.database
------------------
Local query:TDatabaseQuery = db.executeQuery("SELECT * from person")
While query.nextRow()
Local record:TQueryRecord = query.rowRecord()
Local name$ = record.getFieldByName("lastname").value.getString()
Wend
~410 millisecs 

Local query:TDatabaseQuery = db.executeQuery("SELECT * from person")
While query.nextRow()
Local name$ = Query.rowRecord().getFieldByName("lastname").value.getString()
Wend
~370 millisecs

Local query:TDatabaseQuery = db.executeQuery("SELECT * from person")
While query.nextRow()
Wend
~160 millisecs

This suggests creating the TRecord is taking a while, but obviously it's needed to retrieve the data.



pyropixel.sqliter
--------------------
db.Query("SELECT * FROM person")
     
	 While db.NextRecord()
        Local name$ = db.F("lastname")
    Wend
~38795 millisecs

Local prepQ:Int = db.PrepareQuery("SELECT * FROM person")
     
	 While db.StepQuery(prepQ) = SQLITE_ROW
        Local name$ = db.P(prepQ, "lastname")
    Wend
~30 millisecs

The prepared querys with SQliter are lightning fast and I really need that sort of speed as the game often needs to retrieve 100s of records. I tried the bah.database prepared queries but they don't make any difference to the retrieval speed. Is there anything I can do to speed up selection?

BTW Bah.Database is faster at INSERTs. :D


Brucey(Posted 2007) [#23]
Are you using all fields from the SELECT ?

Because mine puts all the fields that you select, into the record.
In your test you SELECT *, but are only using the "lastname" field.
And it looks like, in the SQLiter example, you are only extracting "lastname" from the returned record data, which is a lot less work.

What kind of difference does the test timing look like if you change the SQL to "SELECT lastname FROM person" ?
(just curious ;-)

I can have a look at it anyways, but my code doesn't do anything special that would cause it to take a significant amount of extra time, to my knowledge.
But remember the Framework is abstracted to an extent that allows it to work on different databases, rather than hitting the library directly. So you trade some time for extra error-checking, and ease of use.

... and Query.rowRecord().getFieldByName("lastname").value.getString() looks a bit messy, me thinks... I can have a look at that.


Brucey(Posted 2007) [#24]
I tried the bah.database prepared queries but they don't make any difference to the retrieval speed.


The idea with prepared queries, is that you prepared them "somewhere", and when you need it, you just execute it... as many times as you like, without having the expense of "recompiling" the SQL each time you want to execute.
In theory, it's much more efficient to use the "prepare once, execute many" method.

The actual retrieval (nextRow) itself will be the same speed for prepared or non-prepared queries.

Here's an example from a project of mine, which shows caching of a query :
	Function sqlAddRoute(id:Int)
		Global addRouteQuery:TDatabaseQuery
		
		If conn And conn.isOpen() Then
		
			If Not addRouteQuery Then
				addRouteQuery = TDatabaseQuery.Create(conn)
				sqlPrepareQuery(addRouteQuery, "INSERT INTO route (id) VALUES (?)")
			Else
				addRouteQuery.clearBindValues()
			End If
					
			sqlAddBindInt(addRouteQuery, id)
		
			addRouteQuery.execute()
							
		End If
		
	End Function


The query is prepared once, and reused after that. (this example is actually running on MySQL, but it doesn't matter, it would be the same for SQLite too)


Anyways, I'll be updating BaH.Database soon with some more helper methods, that let you do :

Local name:String = record.getStringByName("surname")

Which is a bit less coding.


siread(Posted 2007) [#25]
It's a lot quicker (sub 10 millisecs) if I change it to SELECT lastname, however in my real code I often need all the fields to create a TList of people (and there are a lot of fields in my person table).

Using SQLiter was still very fast despite calling db.P() for every field.


Brucey(Posted 2007) [#26]
using "getFieldByName()" is also slower in my framework, because it's doing a lookup via the index (eg. if name = name[i]...), which is a tad crap ;-)

I'm changing it so that the name lookup comes off a TMap cache instead, so retrieving any field by name (whether it's the first or the last) will take the same amount of time.

I've always just used the "Index" lookup myself, which I suppose is why the "Name" lookup hasn't had much optimization done on it.

Still, thanks to your using it, it gets better :-)


Brucey(Posted 2007) [#27]
And... it's also doing UTF-8 string conversion.. on the fly ;-)


Anyhoo, I've uploaded a new BaH.Database (1.04) release, which has :
 * Improved getFieldByName efficiency.
 * Added TQueryRecord helper methods for type/name retrieval - getXXXByName().



So, you could now change :
While query.nextRow()
    Local name$ = Query.rowRecord().getFieldByName("lastname").value.getString()
Wend


to

While query.nextRow()
    Local name$ = Query.rowRecord().getStringByName("lastname")
Wend

... or even

For Local record:TQueryRecord = eachin query
    Local name$ = record.getStringByName("lastname")
Next


If you decide that SQLiter is better for your needs, cool... but thanks for helping improve mine :-)


siread(Posted 2007) [#28]
I'd prefer to use your mod as the support is great and it's more user friendly. However, speed is a very important issue. Now if there was a way to step through query results without populating an entire TSQLiteResultSet, and simply access the sqlite3_column directly then it would save a lot of time when dealing with 1000s of records.

I'll maybe add some methods for my own use, if you don't mind. I realise that kind of defeats the object of having a framework, but it means I can have the best of both worlds. :)


Brucey(Posted 2007) [#29]
Well, if you ever want to discuss ways in which you can get the most of the framework, feel free to drop me a mail.

And remember, if you need to select a single column only, make an SQL just for that. Have another for selecting the entire row, etc...
Don't be afraid to have a few prepared SQLs lying around ready to be used. Think of it like creating an array of cached data that you can pull up at any time - it's quicker to create that array once, rather than recreate the same thing over and over again...

;-)


mikkyx(Posted 2007) [#30]
Hi Brucey,
I've just downloaded your BaH.database module but it looks like I got v1.02 instead of v1.04? It's working but when I tried to use getStringByName it, of course, wasn't there. Can you stick 1.04 up somewhere?


Brucey(Posted 2007) [#31]
Hi,

Not sure how that happened... but the correct version is up on the site again...

Sorry :-)


mikkyx(Posted 2007) [#32]
Thanks :)


Vilu(Posted 2007) [#33]
I'm having hard time getting two processes to update the same SQLite database file at the same time. I know it's not possible for two processes to commit exactly at the same time, but I'd love to have executeQuery() and/or commit() to return an error when a conflict happens. Currently they seem not to do it.

Here's a snippet I've been trying to demonstrate this with:

SuperStrict

Framework bah.DBSQLite

Local db:TDBConnection = LoadDatabase("SQLITE", "test.db") 

If db.isOpen() Then
  db.executeQuery("Create TABLE tbl (id integer primary key AUTOINCREMENT, name varchar(30))") 
	
  For Local i:Int = 1 To 20
    Repeat
      db.executeQuery("INSERT INTO tbl values (NULL, 'Name " + i + "')") 
      If db.hasError() Then
        DebugLog db.error().ToString() 
        Delay(2000) 
      EndIf
    Until Not db.hasError() 
    Delay(2000) 
  Next
  db.Close() 
  DebugLog "Done"
End If


What it basically does is insert 20 records in 2 second intervals into the table, and if an update fails, it retries until it succeeds. The problem is that it never fails when the database is being updated by another process at the same time. It just happily moves on to the next record in the For-loop. Kinda makes it hard to create a foolproof record insertion method.

The following example program updates the first record 50 time per second and is guaranteed to cause update conflicts when the two programs are run at the same time:

SuperStrict

Framework bah.DBSQLite
Import brl.timer

Local db:TDBConnection = LoadDatabase("SQLITE", "test.db") 

If db.isOpen() Then
  Local timer:Int = MilliSecs() 
  Repeat
	db.executeQuery("UPDATE tbl SET name = 'Updated name' WHERE id = 1") 
	If db.hasError() Then DebugLog db.error().toString() 
	Delay(20) 
  Until MilliSecs() - timer > 40000
  
  db.Close() 
End If


The first program fails to insert on average every third record if the second program is running at the same time.

That would be OK if I had means to check whether or not the insertion succeeded. Any ideas?


Vilu(Posted 2007) [#34]
I did not get this to work even with the prepare query approach.

Brucey, have you had time to look into this?


Brucey(Posted 2008) [#35]
Sorry for the rather late reply..

The latest version in SVN fixes the problem. I've also included your example into the tests to show that feature working as expected.


Vilu(Posted 2008) [#36]
Great news! Thanks, Brucey! :)


lotonah(Posted 2008) [#37]
Hi Brucey,

I'm using BlitzMax 1.28 on a Mac, and I'm trying to get sqlite to work. It's in the mod folder (as is database.mod), and building the modules works fine.

Compiling date_test_01.bmx gives the error "Can't find interface for module 'bah.dbsqlite'.

I'm new to databases and modules, so obviously I'm at the stupid stage. Please help. Thanks!


DavidDC(Posted 2008) [#38]
Try checking your file paths and case of the filenames. They should be:

BlitzMax/mod/bah.mod/database.mod
BlitzMax/mod/bah.mod/dbsqlite.mod


Brucey(Posted 2008) [#39]
building the modules works fine.

This is one of the BlitzMax quirks... if the module is in the wrong place, it will simply ignore it completely.

As David says, check the location of the modules.

BlitzMax modules are split into two levels. One is like a "namespace", and the other is the specific module. So, for bah.dbsqlite, you should find one folder called bah.mod, and inside that another folder for the module itself, dbsqlite.mod.

It's actually quite a nice way to group modules together.


Artemis(Posted 2008) [#40]
Hi,

I've just updated your modules via svn but when trying to build them I get this error:

Compiling:sqlite3.c
D:/BlitzMax/mod/bah.mod/dbsqlite.mod/src/sqlite3.c:18431:3: #error SQLite will not work correctly with the -ffast-math option of GCC.
Build Error: failed to compile D:/BlitzMax/mod/bah.mod/dbsqlite.mod/src/sqlite3.c


svn updated the following sqlite-files.

U    dbsqlite.mod\src\sqlite3.c
U    dbsqlite.mod\src\sqlite3.h
U    dbsqlite.mod\src\sqlite3ext.h
U    dbsqlite.mod\dbsqlite.bmx


I'm using a normal Mingw installation on Windows XP.


Brucey(Posted 2008) [#41]
Oh... goody... I guess a new bmk tweak is required for this then. :-(

Didn't complain on OS X, so I'm guessing the fast-math option is Win32 only in BlitzMax.


Brucey(Posted 2008) [#42]
I've commented out the error message for now... ack... not sure how terrible it is to do that, but for now it compiles up.

Long term solution is to disable fast-math for that module compile - but atm bmk doesn't support that level on configuration. (well, the official one doesn't ;-)