Question about Perl Query via DBI

[SavageDeath]

Alright so I know this has been asked a few times before but I'm new to this so please put up with me for a bit =)

My question is how can I get Perl to pull a value from a custom table via a query? I can get it to insert new values and such into the new table I made easy enough but getting it to pull a numerical value is where I have no clue what to do. I have seen the other posts where you guys were using a fetch to pull the values, but how would I get the NPC to tell me what those values are in a say or how could I get the script to use those values as a check ?

Here is what I have.

sub EVENT_SAY {

if($text=~/Hail/i)
{
use DBI;
use DBD::mysql;
$tablename = "test";
my $connect = DBConnect();
# Create the Query
$query = "INSERT INTO $tablename (id, name, description, value) VALUES('$charid', '$name', 'test', 20)";
$query_handle = $connect->prepare($query);
$query_handle->execute();
$connect->disconnect();
}

if($text=~/try/i)
{
use DBI;
use DBD::mysql;
my $connect = DBConnect();

$query = "SELECT value FROM test WHERE id = $charid";

$query_handle = $connect->prepare($query);
$query_handle->execute();
##I'm not sure how to get it to push out what I want in a quest say here, pretty sure I need a fetch of some sort.
$connect->disconnect();
}
}


It connects and inserts a new value when I hail the NPC which is great. But I want to be able to pull the value from the value column and use it as a check. Or to tell the player oh yeah you have x amount of value in storage or whatever.

I know I can do this with globals but I rather not I was hoping to use a completely different table for it.

Any help would be greatly appreciated, if you could post an example or point me in the right direction that would be great, thanks in advance!


Note: I am aware I'm not showing where I have the database info to connect, figured that wasn't need for you guys to help. I made a sub routine for it to connect just a heads up.

[lerxst2112]

http://www.perl.com/pub/1999/10/DBI.html

[SavageDeath]

I tried to read that before and didn't understand it was hoping you guys could give me an example, sorry kinda new to it.

[Drajor]

http://www.eqemulator.net/wiki/wikka.php?wakka=CustomCharacterData

[lerxst2112]

The link I provided has some pretty simple examples. Here's another set of examples: http://stein.cshl.org/~lstein/talks/perl_conference/advanced/simple_dbi.html

[SavageDeath]

Thanks guys I'll give those a try tomorrow in the morning. I like the idea of a new quest::command.

[NatedogEZ]

$sth = $dbh->prepare("SELECT name FROM items WHERE id = $looted_id");
$sth->execute();
my $row = $sth->fetchrow_array();


I was using this in an EVENT_LOOT -- just for testing how to use DBI.

But this would fetch the item name of whatever you looted.. was mainly just for testing purposes..

Just change it a bit and I'm sure you could easily fetch something from one of your custom tables!

If you need help with something specific I don't mind shooting you some help.

[c0ncrete]

i'm at a loss as to why you wouldn't want to use qglobals, but at the very least, you probably want to keep the connection persistent for the lifetime of the npc. maybe set a timer to where it closes the connection after a certain amount of inactivity. perhaps look into caching the results as well. you don't want to give players the opportunity to create new database connections (or load perl modules, for that matter) at the press of a button.

[NatedogEZ]

I forgot to copy paste that part in...



$sth = $dbh->prepare("SELECT name FROM items WHERE id = $looted_id");
$sth->execute();
my $row = $sth->fetchrow_array();
$dbh->disconnect();


This would only just show the name of the item someone looted ... like I said before this was just for testing.. DBI. This is definitely not something you want to do for looting.