MySQL

[dwfait]

Hi, ive searched for this, but couldnt find anything i could understand XD

Anyway, could anyone show me example code of how to connect to a remote MySQL database from PB? I know all the SQL commands, i just need to know how to open a connection ready to send queries to it..Im a PB n00b, so anything i could understand would be much appreciated.

Thanks in advance, teh dwfait.

[Tranquil]

There are several examples on the forum how to do it.
You have to choose to use the ODBC way or the mysql.dll way.

Here is an example of fangbeast using the dll:

viewtopic.php?t=12475&highlight=mysql

[dwfait]

Ive seen that code..and its confusing. Could you provide an example code that simply connects to a MySQL database on a spoof ip with a spoof user/pass, and sends a query?

I know this sounds a lot like "do my code for me", but that really just confuses me.

[Andras]

I think, there are 3 main-ways to do this (under windows):

1. Use the Standard-PB-DatabaseLibrary (ODBC)
2. Use the MySQL-API (libmySQL.dll)
3. Use a UserLibrary (I guess someone already has created one?)

Using ODBC you will have to install the "MySQL Connector/ODBC" which can be found at http://dev.mysql.com/downloads/connector/odbc/3.51.html .
Unfortunately it is not enough to only have the driver installed... we need to have a DSN to connect to a database (at least in PB)... but this is no big problem, we can do this directly in the program using the windowsAPI... take a look at this:

Code: Select all

Dim DatabaseType.s(4)
DatabaseType(0) = "Unknown"
DatabaseType(1) = "Numeric"
DatabaseType(2) = "String"
DatabaseType(3) = "Float"

#DriverID.s="MySQL ODBC 3.51 Driver"
#ODBC_ADD_SYS_DSN = 4
;Replace the ServerName with the Name or IP of your server and the Databasename...:
#DSNAttributes.s="DSN=MyMySQLDSN;SERVER=localhost;database=mysql"

*MyMem=AllocateMemory(Len(#DSNAttributes)+2)
PokeS(*MyMem,#DSNAttributes)

For i=0 To Len(#DSNAttributes)-1
  If PeekB(*MyMem+i)=Asc(";")
    PokeB(*MyMem+i,0)
  EndIf
Next i

If InitDatabase() = 0
  MessageRequester("Error", "Can't initialize Database (ODBC v3 or better) environment", 0)
  End
EndIf

OpenConsole()

;Unfortunately we cannot directly connect without a DSN :(
;... so let's create one... (you'll need admin-rights to do this):

If SQLConfigDataSource_(0, #ODBC_ADD_SYS_DSN, #DriverID, *MyMem)=#False 
  PrintN("DSN-Creation failed...")
  Delay(1500)
  End
EndIf

;Now we can connect...

If OpenDatabase(#PB_Any, "MyMySQLDSN", "root", "")  ;use your login-data ;)

  Repeat
    Print("SQL Command: ")
    Command$ = Input()
    PrintN("")
    
    Select UCase(Command$)
      
      Case "EXIT"
        Quit = 1
        
      Default
        
        If DatabaseQuery(Command$)
          
          NbColumns = DatabaseColumns()
          PrintN("NbColums: " + Str(NbColumns))
          
          For k=0 To NbColumns-1
            PrintN(DatabaseColumnName(k) + " - " + DatabaseType(DatabaseColumnType(k)))
          Next
          
          PrintN("")
          Print ("Press return to continue") : Input()
          PrintN("")
          PrintN("Query Result -------------------------------------")
          
          While NextDatabaseRow()
            PrintN(GetDatabaseString(0))
          Wend
          
          PrintN("--------------------------------------------------")
        Else
          PrintN("Bad Query !")
        EndIf
        
    EndSelect
    
  Until Quit = 1
  
Else
  PrintN("Error opening database...")
  Delay(1500)
EndIf

End
; ExecutableFormat=Console
; EOF

Part of the code was taken from the PB-example...

This might be interesting for you too:

http://dev.mysql.com/doc/mysql/en/conne ... eters.html
http://msdn.microsoft.com/library/defau ... source.asp

[Fangbeast]

Ive seen that code..and its confusing. Could you provide an example code that simply connects to a MySQL database on a spoof ip with a spoof user/pass, and sends a query?

I know this sounds a lot like "do my code for me", but that really just confuses me.

A lot of code was written in a 'long winded' fashion to make it easier to use and follow. All routines, aliases and gosubs are 'stretched' out to make it easier to read for me.

Max2 said he was working on a library at some stage which would clean out most of my mess, should ask him how he is going.

[dwfait]

What i dont get, is the part where you connect to the DB. Where is the line where you supply the MySQL server's IP?

[Andras]

If you mean my example:

ServerName or ServerIP & DatabaseName:
#DSNAttributes.s="DSN=MyMySQLDSN;SERVER=localhost;database=mysql"

UserName & Password:
If OpenDatabase(#PB_Any, "MyMySQLDSN", "root", "") ...

[Tommeh]

using MySQL I'd always use Libmysql, its great!

Here is an example of how to use it:
http://www.purearea.net/pb/CodeArchiv/D ... Example.pb

and search google for libmysql.dll, its free and allows you to do anything with MySQL you need and is very easy to use

[dwfait]

Thanks for the lib, works great..kinda...

If i do this:

Code: Select all

  If OpenLibrary(#libmysql,"libmysql.dll")
    Result=CallFunction(#libmysql,"mysql_init",dbHnd)
    If Result
    dbHnd = Result
      If CallFunction(#libmysql,"mysql_real_connect",dbHnd, host, user, passwd, db, port, "", 0) = 0
      ;GetError("error")
     ; Debug "error" 
      GetError(dbHnd,1)
      Else
     
  EndIf

    EndIf
  EndIf

will i be able to call this from anywhere in the loop?

Code: Select all

            SQL = "SELECT name,member_login_key FROM ibf_members WHERE name='"+Username+"' AND member_login_key='"+md5pass+"'"
      If CallFunction(#libmysql,"mysql_real_query", dbHnd, SQL, Len(SQL))
        ;GetError(dbHnd,1)
      Else
        *mysqlResult=CallFunction(#libmysql,"mysql_store_result",dbHnd)

          EndIf  

Becuase at the moment, its spewing a read/write error at me when it gets to the second code..