Simple save data to SQL plugin

Other plugins for extending OpenKore's functionality. This forum is only for posting new plugins and commenting on existing plugins. For support, use the Support forum.

Moderator: Moderators

Message
Author
glow
Noob
Noob
Posts: 4
Joined: 22 Jan 2011, 20:55
Noob?: No

Simple save data to SQL plugin

#1 Post by glow »

A simple plugin to save data to MSSQL so that you later can do anything you want with it.
Should work with mysql too just change the driver.

Code: Select all

#########################################
# koreSavetoSQL - written by gl0w 2011
#
# This plugin just saves data to MSSQL
# Right now monsters and items
#
#########################################

package koreSavetoSQL;

use Plugins;
use Globals qw($char %config %timeout);
use Settings;
use FindBin qw($RealBin);
use Log qw(message);
use Misc;
use DBI;

#koreSavetoSQL 1
#koreSavetoSQL_db openkore
#koreSavetoSQL_server localhost

Plugins::register('koreSavetoSQL','Allows your bot to save data to MSSQL.', \&onUnload);
my $hooks = Plugins::addHooks(
   ['start3', \&onLoad, undef],
   ['item_gathered', \&onItemFound, undef],
   ['target_died', \&onKill, undef],
);


sub onLoad {
	if($config{koreSavetoSQL} == 1) {
	# this is an integrated security connection string
		my $dsn = "driver={SQL Server};Server=$config{koreSavetoSQL_server};database=$config{koreSavetoSQL_db};";
		our $dbh = DBI->connect("dbi:ODBC:$dsn") or die 'Cannot connect to SQL.\n';
	}
}

sub onItemFound {
   my @args = @_;
   saveItem("$args[1]{item}", "item");
}
sub onKill {
	my @args = @_;
	saveMonster("$args[1]{name}", "kill");
}

sub saveItem {
	my ($msg, $domain) = @_;
	my $sth = $dbh->prepare(qq{
		INSERT INTO Items (itemName,charName,pickupDate) VALUES (?,?,getdate())
	});
	$sth->execute("$msg","$char->{name}");
}

sub saveMonster {
	my ($msg, $domain) = @_;
	my $sth = $dbh->prepare(qq{
		INSERT INTO Monsters (monsterName,charName,killDate) VALUES (?,?,getdate())
	});
	$sth->execute("$msg","$char->{name}");
	
}

sub onUnload {
   Plugins::delHooks($hooks);
   $dbh->disconnect;
   undef $ucname;
}

1;
To create database and tables use these scripts.

Code: Select all

CREATE DATABASE [openkore]
GO
USE [openkore]
CREATE TABLE [dbo].[Items](
	[itemName] [nvarchar](max) NOT NULL,
	[pickupDate] [datetime] NOT NULL,
	[charName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Monsters](
	[monsterName] [nvarchar](max) NOT NULL,
	[killDate] [datetime] NOT NULL,
	[charName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO
Wanna try if it works?

Code: Select all

select itemName, COUNT(itemName) as NumOccurences from Items --where pickupDate > '2011-01-23 01:54' 
group by itemName having (COUNT(itemName) > 1)
Oh yeah if you put koreSavetoSQL 0 in config you will get errors. Easy to fix but im pretty sleepy from all the beer now.

Edit: deleted a testline. And i totally forgot that i had to change target_died hook to give me the name of the monster.

Code: Select all

Plugins::callHook("target_died", {name => $monsters_old{$ID}{'name'}}); 
Last edited by glow on 23 Jan 2011, 15:29, edited 1 time in total.

Mortimal
Developers
Developers
Posts: 389
Joined: 01 Nov 2008, 15:31
Noob?: No

Re: Simple save data to SQL plugin

#2 Post by Mortimal »

Can u explain how to connect to secured mysql database) please.
Please use pin function for uploading your file contents!

glow
Noob
Noob
Posts: 4
Joined: 22 Jan 2011, 20:55
Noob?: No

Re: Simple save data to SQL plugin

#3 Post by glow »

Mortimal wrote:Can u explain how to connect to secured mysql database) please.

Should work with this but i have not tried it.

Code: Select all

my $dsn = "driver={mysql};host=$config{koreSavetoSQL_server};database=$config{koreSavetoSQL_db};";
or something like this in the $dbh part:

Code: Select all

$connection=DBI->connect("DBI:mysql:database=$database;host=$host:port=3306",$username, $password);

Post Reply