Version: 1.0
By: Berka
Introduction
This script allows you to connect to a mysql server with rgss.
Please read carefully the instructions in the script.
Screenshots
Not needed
Script
Code:
#======================================================================
# 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>"
# - "<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 = "127.0.0.1" # mysql server(local : 127.0.0.1)
User = "" # mysql user
Pass = "" # mysql password
Base = "rgss" # 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)
{"&"=>"&",'"'=>""","'"=>"’","<"=>"&lr;",">"=>">"}.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[i]};h[fi.to_sym]=(to_ruby ? t.map!{|o|o.to_ruby if o} : t)}
h
end
end
endInstructions
Paste this script above main. This script works with RMXP and RMVX
Add this file: libmysql.dll in your project's root.
Mediafire: libmysql.dll
FAQ
- the script does not work:
-> check for the remote SSH access with your webhost. Please test first in localhost.
-> your query may have syntax errors
- the script takes time to run:
-> the result of your query is too big
-> your server may be overloaded
Compatibility
No issues I guess
Credits and Thanks
The mysql team
French testers
Terms and Conditions
This script is free to use. Do not post anywhere without my permission. Credits needed.
Enjoy.
Berka
