GMan's Mods & Stuff → Pub.ISQLDB (WIP) → Dev Status
nothing really to see yet. i have defined and declared the functions i wish to include in the mod and begun initial testing. i have successfully connected to a server, selected the DB, performed select statements and am currently working on processing the returned results. the goal is then to wrap usage of these functions up into types in a similar fashion as the Irrlicht mods. if all goes well i would like to have this mod wrapped up in the week or so.
stay tuned...
UPDATE 2006/01/15: have all results processing fine so C wrap is completely functional. functions defined in the C wrap are:
Extern "C"
Function bmx_mysql_init:Int()
Function bmx_mysql_close(MYSQL:Int)
Function bmx_mysql_errno:Int(MYSQL:Int)
Function bmx_mysql_error:Byte Ptr(MYSQL:Int)
Function bmx_mysql_connect:Int(MYSQL:Int,host$z,user$z,passwd$z,db$z,port:Int=3306,unix_socket$z=Null,clientflag:Int=0)
Function bmx_mysql_select_db:Int(MYSQL:Int,db$z) ' returns true/false
Function bmx_mysql_real_query:Int(MYSQL:Int,qry$z,length:Long) ' returns true/false
Function bmx_mysql_affected_rows:Int(MYSQL:Int)
Function bmx_mysql_num_rows:Int(MYSQL_RES:Int)
Function bmx_mysql_num_fields:Int(MYSQL_RES:Int)
Function bmx_mysql_field_length:Int(MYSQL_RES:Int,fieldnr:Int)
Function bmx_mysql_store_result:Int(MYSQL:Int) ' returns MYSQL_RES*
Function bmx_mysql_use_result:Int(MYSQL:Int) ' returns MYSQL_RES*
Function bmx_mysql_fetch_row:Byte Ptr Ptr(MYSQL_RES:Int) ' returns MYSQL_ROW (array of char*)
Function bmx_mysql_eof:Int(MYSQL_RES:Int) ' returns true/false
Function bmx_mysql_insert_id:Int(MYSQL:Int)
Function bmx_mysql_free_result(MYSQL_RES:Int)
Function bmx_mysql_real_escape_string:Int(MYSQL:Int,strTo:Byte Ptr,strFrom:Byte Ptr,fromLength:Int) ' returns length placed in To
Function bmx_mysql_field_name:Byte Ptr(MYSQL_RES:Int,fieldnr:Int)
Function bmx_mysql_field_value:Byte Ptr(MYSQL_ROW:Byte Ptr Ptr,fieldnr:Int)
Function bmx_mysql_isfieldnull:Int(MYSQL_ROW:Byte Ptr Ptr,fieldnr:Int) ' returns true/false
EndExterni am now working on getting some types created that will make accessing MySQL pretty easy for simple functionality. i plan to break this out into 2 modules: Pub.MySQL and Pub.ISQLDB. Pub.ISQLDB is meant to be an interface set of types so that there can be a universal interface for different SQL database back ends. for example, there would be the same set of the methods to access MySQL, SQLLite, and PostgreSQL so to change backends would be trivial as long as you used the SQLDB interface to access functionality. type ISQLDB interface types i have defined so far are:
' interface for an SQL database connection
Type ISQLDB
' get/set properties for connection settings
Method setHost(host:String) Abstract
Method getHost:String() Abstract
Method setDB(database:String) Abstract
Method getDB:String() Abstract
Method setUsername(username:String) Abstract
Method getUsername:String() Abstract
Method setPassword(password:String) Abstract
Method getPassword:String() Abstract
Method setPort(port:Int) Abstract
Method getPort:Int() Abstract
Method setFlags(flags:Int=0) Abstract
Method getFlags:Int() Abstract
Method getError:Int() Abstract
Method getErrorMsg:String() Abstract
' methods for working the connection
Method openConnection:Int() Abstract ' returns true/false
Method closeConnection() Abstract
Method queryDB:ISQLResult(query:String,bPrefetch:Int=True,handler:IOnResultEventHandler=Null) Abstract
Method selectDB:Int(database:String) Abstract
EndType
Type IOnResultEventHandler Abstract
Method OnResult() Abstract
EndType
' contains info on the result of a query
Type ISQLResult
Method getConnection:ISQLDB() Abstract
Method getQuery:String() Abstract ' the query that was processed
Method getError:Int() Abstract
Method getErrorMsg:String() Abstract
Method getSuccess:Int() Abstract
Method getNumRows:Int() Abstract
Method getNumCols:Int() Abstract
Method getColName:String(col:Int) Abstract
Method getColWidth:Int(col:Int) Abstract
Method getVal:String(row:Int,col:Int) Abstract
Method getValByName:String(row:Int,col:String) Abstract
Method getIntVal:Int(row:Int,col:Int) Abstract
Method getIntValByName:Int(row:Int,col:String) Abstract
Method getLongVal:Long(row:Int,col:Int) Abstract
Method getLongValByName:Long(row:Int,col:String) Abstract
Method getFloatVal:Float(row:Int,col:Int) Abstract
Method getFloatValByName:Float(row:Int,col:String) Abstract
Method getDoubleVal:Float(row:Int,col:Int) Abstract
Method getDoubleValByName:Float(row:Int,col:String) Abstract
Method getInsertId:Int() Abstract
EndTypethe MySQL version of ISQLDB looks like:
Type MySQLDB Extends ISQLDB
Field _host:String
Field _username:String
Field _password:String
Field _port:Int=3306
Field _flags:Int=0
Field _conn:Int ' MYSQL*
Field _db:String
Field _unixsocket:String=Null
Field _lastErrMsg:String
Field _lastErr:Int
Function create:MySQLDB(bConnect:Int,host:String,user:String,passwd:String,db:String=Null,port:Int=3306,unix_socket:String=Null,clientflags:Int=0)
Local retval:MYSQLDB=New MYSQLDB
retval.setHost(host)
retval.setUsername(user)
retval.setPassword(passwd)
retval.setDB(db)
retval.setPort(port)
retval.setUnixSocket(unix_socket)
retval.setFlags(clientflags)
If bConnect Then retval.openConnection()
Return retval
EndFunction
' get/set properties for connection settings
Method setHost(host:String)
_host=host
EndMethod
Method getHost:String()
Return _host
EndMethod
Method setDB(database:String)
_db=database
EndMethod
Method getDB:String()
Return _db
EndMethod
Method setUsername(username:String)
_username=username
EndMethod
Method getUsername:String()
Return _username
EndMethod
Method setPassword(password:String)
_password=password
EndMethod
Method getPassword:String()
Return _password
EndMethod
Method setPort(port:Int=3306)
_port=port
EndMethod
Method getPort:Int()
Return _port
EndMethod
Method setFlags(flags:Int=0)
_flags=flags
EndMethod
Method getFlags:Int()
Return _flags
EndMethod
Method setUnixSocket(unix_socket:String=Null)
_unixsocket=unix_socket
EndMethod
Method getUnixSocket:String()
Return _unixsocket
EndMethod
' methods for working the connection
Method openConnection:Int()
Local retval:Int=False
_conn=bmx_mysql_init()
If _conn
If Not bmx_mysql_connect(_conn,_host,_username,_password,_db,_port,_unixsocket,_flags)
FillError() ' error, fill with info from MySQL
EndIf
Else
' error, but not loggable because no valid connection
FillError(ERR_INIT_FAIL,"Failed to intialize MySQL client library")
EndIf
Return retval
EndMethod
Method closeConnection()
If _conn Then bmx_mysql_close(_conn)
FillError() ' clear/fill the error info
EndMethod
Method Delete()
closeConnection() ' close out if there is an open connection
EndMethod
Method selectDB:Int(database:String)
Local retval:Int=False
retval=bmx_mysql_select_db(_conn,database)
If retval Then setDB(database)
FillError() ' clear/fill the error info
Return retval
EndMethod
Method queryDB:ISQLResult(query:String,bPrefetch:Int=True,handler:IOnResultEventHandler=Null)
EndMethod
Method FillError(errno:Int=-9999,errmsg:String=Null)
If errno=-9999 Then _lastErr=bmx_mysql_errno(_conn) Else _lastErr=errno
If Not errmsg Then _lastErrMsg=String.FromCString(bmx_mysql_error(_conn)) Else _lastErrMsg=errmsg
EndMethod
Method getError:Int()
Return _lastErr
EndMethod
Method getErrorMsg:String()
Return _lastErrMsg
EndMethod
EndTypefeel like im rambling here so back to work. looks like im on target for a release of this weekend.
things are progressing very quickly. i broke out the interface into its own mod and Pub.MySQL is all but done. ive already begun looking at implementing Pub.PostgreSQL and hope to release both by sometime this weekend. if i continue on this pace, i may even have SQLite implemented as well.
Pub.MySQL is complete. Pub.PostgreSQL is 90%. looks like i will definately be able to get to SQLite. this is cool 
woah... Man you've been busy lol
I saw this today, I have the Firebird SQL headers translated and have some classes that I started to wrap the API to be easier to use.
I could probably implement your interfaces except for one thing is missing and it is a biggie. Not a fan of Mysql and havent looked at Postgress but you dont seem to have any way to manage transactions which is huge if you want to do any real DB work. Firebird needs transactional contexts to be efficent.
Does postgress or Mysql support transactions? Reviewing your API it seems kina modeled after the .net interfaces what am i missing for transaction support.
Doug Stastny
aka Budman
hey Budman! glad to see you checkin these out and would love to have another SQL server added to the list i basically hadnt gotten that far yet (was going for basic SQL stuff first) but you are correct it should have transaction methods built in. another thing thats missing that id like to get in there is support for prepared SQL statements.
after thinking a bit what do you think of the following?
Method setTransactions:Int(bEnabled:Int=False) Abstract ' would return .T. if trans were successfully enabled
Method TransactionBegin:Int() Abstract ' returns true if successful
Method TransactionCommit() Abstract
Method TransactionRollback() Abstract
Method TransactionCount:Int() Abstract ' returns transaction nesting levelthoughts?
just finished up Pub.PostgreSQL... just need to write the instructions. should have it up first thing in the morning. cant wait to start on the http proxy implementations, especially since thats what i needed in the first place it will be fairly limited since its stateless, but it will get the job done.
Not sure what SetTransaction would do unless you have a Transaction Object.
Something to consider would be to have the ISQL Object Create a Transaction Object then execute your sql functions(ExecuteScaler) in the context of the Transaction object. That way RDBMS that allow multiple transactions per connection to have simultanous runnning transactions.
With Firebird SQL and MSSQL you have to have a transaction context to execute SQL.
.Net interfaces are a really good reference look up IDBConnection, IDBCommand and IDBTransaction for a neat and clean abstraction concept. IDBConnection is like your ISQLDB
Here is simple concept adding some methods of abstraction to your objects
Type ISQLDB
Method BeginTransaction:ISQLTransaction(Options:String) abstract
Method CreateDBCommand :ISQLCommand(Transaction : ISQLTransaction = null) abstract ' allow null so you can share a command bettween transactions
End Type
Type ISQLTransaction
Commit()abstract
RollBack(SavePoint : String) abstract ' Save Points are better for nesting... and method used by SQL Standards
BeginTransaction(SavePoint:String)abstract
End Type
' This is what you will need to do prepared Statements
Type ISQLCommand
Const SQLQUERYCOMMAND=1
Const SQLSTOREDPROCEDURE=2
Method SetTransaction (Transaction : ISQLTransaction) abstract
Method GetTransaction:ISQLTransaction() abstract
Method SetParam(Index:Int, Value:String) ' these should be ISQLParameters collection which is list of ISQLParamter
Method GetParam:String(Index:Int) abstract
Method SetCommandText(SQLStatement:String) abstract
Method SetCommandType(SQLCommandType:Int) abstract' const declared above
Method Execute:ISQLResultSet() abstract
Method ExecuteScaler:String() abstract
End TypeThis is rough but i think if you look at this and look at the .NET Interfaces I sited above you can get the idea.
I would be more than glad to help out and develope the Firebird Driver for you. Its one of the many projects I have cooking but no time to finish.... have I shown you my DX9 Driver for Max2d. I am close on that just need to finish the support for MAXGUI and thats done. Just dont know if BRL will let me post it. As it requires lots of fixes to the Pub.DirectX
Anyway its late. Any help I can provide on the interface declareactions so that we to keep it DB neutral but still rich enough I will be more than willing.
Doug Stastny
thx Budman! im going to do some analysis and come up with a proposal. probably will be ready by tomorrow morning i would think. going a bit faster than i expected it will be a good thing, since implementing commands would allow complete cross-db support.
GMan's Mods & Stuff → Pub.ISQLDB (WIP) → Dev Status
Powered by PunBB, supported by Informer Technologies, Inc.