1 (edited by gman 2006-01-15 18:40:15)

Topic: 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...

Re: Dev Status

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

EndExtern

i 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
EndType

the 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
    
EndType

feel like im rambling here so back to work.  looks like im on target for a release of this weekend.

Re: Dev Status

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.

Re: Dev Status

Pub.MySQL is complete.  Pub.PostgreSQL is 90%.  looks like i will definately be able to get to SQLite.  this is cool smile

Re: Dev Status

woah... Man you've been busy lol

Re: Dev Status

ckob wrote:

woah... Man you've been busy lol

you could say that smile

i went back and revamped some of the interface, implemented some new functionality, and then documented the mods.  once again, the interface module is complete and the mysql module is complete.  im on to reworking the postgresql module but will hopefully be done with that by the weekend.  i have decided my next implementation will be an HTTP proxy implementation that will allow DB access to your web server database even if your host provider doesnt allow external connections.  the proxies will be implemented for both MySQL and PostgreSQL.  also, i scrounged up a DLL i wrote a few years back (ggODBC.dll) that made ODBC easy to use from BlitzPlus so ive added that to the list of implementations.

on a side note, all the code posted above was WIP and has since changed.

Re: Dev Status

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

Re: Dev Status

hey Budman!  glad to see you checkin these out and would love to have another SQL server added to the list smile  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 level

thoughts?

Re: Dev Status

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 smile  it will be fairly limited since its stateless, but it will get the job done.

Re: Dev Status

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 Type

This 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

Re: Dev Status

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 smile  it will be a good thing, since implementing commands would allow complete cross-db support.