Mysql Connect

[efeerk]

I want connect mysql tables in my game without online system. I should change table variables with events.( with script command) and I want do them with this script. I did read instruction but I colud not understand anything because I m amateur about it. Please help me and make a demo if you can...

Script :

It is berka's script

Spoiler

#======================================================================
# Net::Mysql
# 29-05-2010 www.rpgmakervx-fr.com Rgss1&2 v.1
# par berka
#--------------------------------------------------------------------------------------------------------------
# This script is free to use. Do not post anywhere without my permission. Credits needed.
#--------------------------------------------------------------------------------------------------------------
# Warning: if your game is cracked and decrypted, your mysql login will be available !
# Do not use with a database containing personal information.
# Your mysql host should accept external connections.
# Check with it for remote SSH access to your database.
#--------------------------------------------------------------------------------------------------------------
# This script allows interractions with a mysql database directly in the game
# It requires a file "libmysql.dll" in the game folder
#--------------------------------------------------------------------------------------------------------------
# Attention: en cas de décryptage de votre jeu, vos identifiants mysql seront accessibles !
# Ne pas utiliser de base de donnée contenant des informations personnelles.
# Votre hébergeur Mysql doit accepter les connexions mysql externes.
# Vérifiez auprès de lui que vous avec un accès distant SSH à votre base de données.
#--------------------------------------------------------------------------------------------------------------------------
# Ce script permet d'interragir avec une base de données mysql directement via le jeu.
# Il nécessite un fichier "libmysql.dll" à placer dans le dossier du jeu.
#--------------------------------------------------------------------------------------------------------------------------
# �—� md5() support
# �—� Mysql functions:
# - Net::Mysql.new([host,user,pass,base,port]) : return : mysql connection handle
# - @mysql.close : return : bool
# - @mysql.list_tables([filter]) : return : ["table1", "table2"]
# - @mysql.select_db(base_name) : return : true if the db exists or false
# - @mysql.query("query",ret=false) : return : if ret = true : rows else result handle
# - @mysql.get_fields([handle result]) : return : ["field1", "field2"]
# - @mysql.get_rows([handle result]) : return : [["l1 row1", "l1 row2"], ["l2 row1", "l2 row2"]]
# - @mysql.fetch_assoc : return : {"field" => ["row1", "row2"] }
# - @mysql.num_rows([handle result]) : return : integer
# �—� Html functions:
# - "string".to_ruby : return : true, false, nil, Integer, Float, self, etc.
# - "<berka>".htmlspecialchars : return : "&lr;berka&gt;"
# - "<berka>".urlencode : return : "%3Cberka%3E"
# - "%3Cberka%3E".urldecode : return : "<berka>"
#--------------------------------------------------------------------------------------------------------------------------
# SQL queries samples
# �—� "SELECT * FROM table"
# �—� "INSERT INTO table (fields) VALUES (values)"
# �—� "INSERT INTO table SET field = value WHERE field = value"
# �—� "UPDATE table SET field = value WHERE field = value"
#--------------------------------------------------------------------------------------------------------------------------
# Sample :
# @mysql = Net::Mysql.new
# @mysql.query("SELECT * FROM `members`)
# res = @mysql.fetch_assoc
# => {:id=>["1","2"], :nom=>["berka","rgss"], :age=>["19",""]}
#======================================================================

module Berka
module Mysql
Host = "localhost" # mysql server(local : 127.0.0.1)
User = "root" # mysql user
Pass = "trgamer" # mysql password
Base = "rpgturk" # base name
Port = 3306 # server port (default: 3306)

Err_Con = "Mysql:\nUnable to connect to the database"
Err_Req = "Mysql:\nUnable to send the query"
end

module Html
Spec_Char=["$","&","+",",","/",";",":","=","@","?"," ","<",">","#","%","{","}","|","\\","^","~","[","]","`"]
end
end

class Numeric
def copymem(len)
# move memory to convert c structs to ruby objects
Win32API.new("kernel32", "RtlMoveMemory", "ppl", "").call(buf="\0"*len,self,len);buf
end
end

class String

def to_ruby
# detect if the string is a md5 hash
return self if self=~/^[a-f0-9]{32}$/
# converts syntax of a string to ruby controls
eval(self)rescue self
end

def htmlspecialchars
# converts special chars to html compatibles chars (ASCII)
{"&"=>"&amp;",'"'=>"&quot;","'"=>"'","<"=>"&lr;",">"=>"&gt;"}.each_pair{|k,v|self.gsub!(k,v)}
self
end

def urlencode
# converts special char of url
o="";self.scan(/./).each{|c|c="%"+c.unpack('H*')[0]if Berka::Html::Spec_Char.include?(c);o<<c};o
end

def urldecode
# converts encoded special char of url to normal chars
self.gsub!(/\%(\w\w)/){|c|c.gsub!("%","").hex.chr}
end
end

module Net
class Mysql
MI=Win32API.new("libmysql.dll","mysql_init","l","l")
MC=Win32API.new("libmysql.dll","mysql_close","l","l")
MQ=Win32API.new("libmysql.dll","mysql_query","lp","l")
MLT=Win32API.new("libmysql.dll","mysql_list_tables","lp","l")
MFL=Win32API.new("libmysql.dll","mysql_fetch_lengths","p","l")
MFR=Win32API.new("libmysql.dll","mysql_fetch_row","p","l")
MNF=Win32API.new("libmysql.dll","mysql_num_fields","p","l")
MFC=Win32API.new("libmysql.dll","mysql_field_count","p","l")
MSR=Win32API.new("libmysql.dll","mysql_store_result","l","l")
MRC=Win32API.new("libmysql.dll","mysql_real_connect","lpppplpl","l")
MNR=Win32API.new("libmysql.dll","mysql_num_rows","p","l")
MFFD=Win32API.new("libmysql.dll","mysql_fetch_field_direct","pi","l")
MFRE=Win32API.new("libmysql.dll","mysql_free_result","p","l")
MSDB=Win32API.new("libmysql.dll","mysql_select_db","p","l")

attr_reader :handle

def initialize(h=Berka::Mysql::Host,u=Berka::Mysql::User,p=Berka::Mysql::Pass,b=Berka::Mysql::Base,po=Berka::Mysql::Port)
# @handle : handle of mysql initialization
@handle=MI.call(0)
# establishes the mysql connection
(print(Berka::Mysql::Err_Con))if MRC.call(@handle,h,u,p,b,po,nil,0)==0
# returns: handle
@handle
end

def close
# closes the current connection
MC.call(@handle)
end

def select_db(base)
# selects a current database
MSDB.call(base)==true
end

def list_tables(m="")
# lists tables request -> fetch the result -> to ruby string
l=MFR.call(MLT.call(@my,m)).copymem(1024)
# splits the string to array -> list of tables
l.scan(/\t(\w+)\0/).flatten
end

def query(req,ret=false)
# sends the query (msg error)
(return print(Berka::Mysql::Err_Req+req))if !MQ.call(@handle,req)
# previous results are released
MFRE.call(@result)if @result
# gets the results from the query -> c struct handle
@result=MSR.call(@handle)
ret ? get_rows(@result) : @result
end

# Proc: gets the name of the field (cstruct) -> to ruby string of handles -> to ruby string
# returns the fieldname or nil if the field is not found.
ReadField=Proc.new{|r,i,of|MFFD.call(r,i).copymem(1024).unpack("iissi")[0].copymem(of).delete!("\0")}

def get_fields(res=nil)
# if a result handle is provided
r=res.nil? ? @result : res
# gets the number of fields, offset: 8bytes-2 (cf. loop)
nf,ch,of=MFC.call(@handle),[],6
# each field: if the fieldname is not found: increase the offset of bytes.
nf.times{|i|a=ReadField.call(r,i,of+=2)until a
# add to the fields array
ch<<a
# reinitialize the offset for the next iteration
of=6}
# returns an array of fields
ch
end

def get_rows(res=nil)
# if a result handle is provided
r=res.nil? ? @result : res
# nr: number of rows, nf: number of fields
nr,nf,en=MNR.call(r),MNF.call(r),[]
# each row:
nr.times{|i|
# gets each row: c struct -> to ruby string -> to array (handles)
c=MFR.call(r).copymem(4).unpack("i")[0]
# gets each field length: c struct -> to ruby string -> to array (handles)
tf=MFL.call(r).copymem(4*nf).unpack("i*")
# size of field: offset of each field
sf=tf.inject(0){|n,i|n+i}
# handle of row -> to string (offset) -> to array
en<<c.copymem(sf+nf).split("\0")
}
# returns each row as an array
en
end

def num_rows(res=nil)
# if a result handle is provided
r=res.nil? ? @result : res
# returns: number of rows
MNR.call(r)
end

def fetch_assoc(to_ruby=false)
# gets rows and fields
h,f,r={},get_fields,get_rows
# each field: read the rows and store them to an hash : h[:field]=[rows]
# rows are converted to ruby objects if to_ruby == true
f.each_with_index{|fi,i|t=[];r.each{|l|t<<l};h[fi.to_sym]=(to_ruby ? t.map!{|o|o.to_ruby if o} : t)}
h
end
end
end

 

[Drago del Fato]

Hmm let's see first thing is you need to post the script using [co*de=rgss][/co*de] (remove * sign) so it wont take much space and be formatted nicely.

Second, I belive I can help you but I'm writing this with only this information you have with this script so if it doesn't work then it doesn't, I at least can give you this much help...

First, find in this script modify this and fill in your data:

 

    module Berka

     module Mysql

      Host = "localhost" # mysql server(local : 127.0.0.1)

      User = "root" # mysql user

      Pass = "trgamer" # mysql password

      Base = "rpgturk" # base name

      Port = 3306 # server port (default: 3306)

 

      Err_Con = "Mysql:\nUnable to connect to the database"

      Err_Req = "Mysql:\nUnable to send the query"

 end

 

Host - Your website host if you are using a website, if you are using it only on your computer (must have MySQL installed then) then leave it like this
User - Your username of the MySQL database, then again if you are using it only on your computer (locally) then you should have at least some bare knowledge of configuring the MySQL database
Pass - Your database password
Base - Database you are using to fetch, update, modify data
Port - Port you are using to connect to the mysql, usually it's 3306, but in case you modified it then change it also in the script.

Err_Con and Err_req you don't need to modify since they are errors which will be shown if an error of that kind happens.

Put this small snippet below this MySQL script in your RMXP project

 

  class MQuery

    attr_reader :max_rows

  

    def initialize(query)

        @mysql = Net::Mysql.new

        @mysql.query(query)

        @max_rows = @mysql.num_rows

      end

    end

    

   def fetch_one_value

        return @mysql.fetch_assoc

   end

 

 

  def dispose

     @mysql.close

  end

end

 

 
Next when sending queries using events you need to write script like this.
 
For example if you want to fetch/update/delete/etc ONE value from the table do this. I am assuming you're going to use events to change scripts
 
Make an event with script call like this:
 

 

   query = "Your MySQL query here"

   mysql = MQuery.new(query)

   mysql.dispose

 

 
In case you want to list some stuff from the table (score list or something I don't know) do this:
 
Make an event with script call like this:
 

 

   query = "Your MySQL query here"

   mysql = MQuery.new(query)

   for i in 0..mysql.max_rows

     fetch = mysql.fetch_one_value

     # you need to figure out now what to do with the variable yourself

   end

   mysql.dispose