MySQL, Perl and setting-up database

[Ultimater]

I've been using plain files for databases for quite a while and my database looks something like this when viewed in Excel:

+------+----------+--------+--------+
|MEM ID|BUS NAME |BUS INFO|CONTACT |
+------+----------+--------+--------+
|000007|aplustv |repairs |Charles |
|000034|zenith |company |Jack |
|000002|mitsubishi|company |Bill |
+------+----------+--------+--------+



sub createDataBase{
open(CSVFILE, "> ".$da_csv) or die "fatal error creating file: $da_csv";print CSVFILE "MEM ID,BUS NAME,BUS INFO,CONTACT";close(CSVFILE);chmod 0700, $da_csv;
}


sub getPost{
unless(-e $da_csv){&createDataBase;}

unless(-e $da_csv){
print "<span style=\'color:red;\'>The file: ".$da_csv." Could not be created!<\/span>";
;exit}
open(CSVFILE, $da_csv) or die "fatal error opening file: $da_csv";
my @incoming=<CSVFILE>;
close(CSVFILE);
grep(chomp $_, @incoming);
our $getRecord = $_[0];

foreach my $Line (@incoming){
our ($userid1,$busname1,$businfo1,$contactperson1,)= split(/\,/, $Line);
if($userid1 eq $getRecord){
our @tmp=($userid1,$busname1,$businfo1,$contactperson1);

}#if
}#foreach
for(my $i=0; $i<=$#tmp;$i++){
$tmp[$i]=unescape($tmp[$i]);

}

return @tmp;
}#sub

sub databaseWrite{
local ($da_csv,$userid,$busname,$businfo,$contactperson)=@_;
unless(-e $da_csv){open(CSVFILE, "> ".$da_csv) or die "fatal error creating file: Events.csv";print CSVFILE "MEM ID,BUS NAME,BUS INFO,CONTACT PERSON";close(CSVFILE);chmod 0700, $da_csv;}
open(CSVFILE, ">> ".$da_csv) or die "fatal error appending to file: $da_csv";
print CSVFILE "\r\n".$userid.",".escape($busname).",".escape($businfo).",".escape($contactperson);
close(CSVFILE);
}


Any ideas how to convert this to a MySQL database? I've never used MySQL before, All I know is I'm running on a windows server and that it supports MySQL. Is it as easy as creating a SQL file or something? Even a simple example would help that uses a single field. Thanks in advance.

p.s. I know that there is a Text::CSV (http://cpan.uwinnipeg.ca/htdocs/Text-CSV/Text/CSV.html) module and I could have used it in my above code but I really need a database that doesn't slow down with each write -- so my old CSV file approch is no longer an alternitive.

[Jeff Mott]

Any ideas how to convert this to a MySQL database?Well... once the database, tables and table fields have been created, reading and writing from an SQL database is synomous with SELECTing and INSERTing. It's hard to give more details than that without having to write a tutorial on SQL.

To learn about SQL I'd recommend SQL Bible (http://www.amazon.com/exec/obidos/tg/detail/-/0764525840/qid=1128351897/sr=2-1/ref=pd_bbs_b_2_1/002-7557507-0344853?v=glance&s=books). This book covers the SQL standards, which parts of those standards are actually implemented in Oracle, DB2 and MS SQL Server as well as good relational design. If for now you just want to learn about MySQL, however, then there is no better read than the MySQL Reference Manual (http://dev.mysql.com/doc/mysql/en/index.html).

[Ultimater]

Thanks Jeff, I'll do some reading up.

[Ultimater]

I collected the administrative information about my MySQL server and looked around for a
script to connect to my MySQL server and here is what I executed:

The code I ran:

#!/usr/bin/perl -w

use CGI;
use strict;
use DBI;

my $dsn = "DBI:mysql:";
my $host = "xxxx.xxxx.xxx";
my $database = "xxxxxxxxx";
my $user = "xxxxxxxxxxxx";
my $pass = "xxxxxxxx";

sub test {
my @drivers = DBI->available_drivers();
foreach my $driver ( @drivers ) {
print "Driver: $driver\n";
my @datasources = DBI->data_sources( $driver );
foreach my $datasource ( @datasources ) {
print "\tData Source is $datasource\n";
};
print "\n";
};
};

my $dbh = DBI->connect("$dsn:$host:$database",$user,$pass) or die "no connection";
&test();

__END__

which outputed:

Driver: ExampleP
Data Source is dbi:ExampleP:dir=.

Driver: File
Data Source is DBI:File:f_dir=folder1
Data Source is DBI:File:f_dir=folder2
Data Source is DBI:File:f_dir=folder3
Data Source is DBI:File:f_dir=folder4
Data Source is DBI:File:f_dir=folder5
Data Source is DBI:File:f_dir=folder6
Data Source is DBI:File:f_dir=folder7
Data Source is DBI:File:f_dir=folder8
Data Source is DBI:File:f_dir=folder9
Data Source is DBI:File:f_dir=folder10
Data Source is DBI:File:f_dir=folder11
Data Source is DBI:File:f_dir=folder12

Driver: Proxy

Driver: XBase

Driver: mysql



Because all the diretories listed on that page matched exactly all the directories in my Server's cgi-bin, the place where I executed the above Program,
it seems -- my MySQL server and my server are one!

note: all red text in this post is personal information which I removed.

[Ultimater]

Now, is there a way to do the following through Perl without having to pull-up the commandline to enter it? (I don't have access to the commandline)

mysql > create database michigan

Then create a table through Perl without having to pull-up the command line?

CREATE TABLE `business` (
`id` int NOT NULL auto_increment primary key,
`busname` TEXT NOT NULL,
`businfo` TEXT NOT NULL,
`contact` TEXT NOT NULL
);

[Jeff Mott]

Because all the diretories listed on that page matched exactly all the directories in my Server's cgi-bin, the place where I executed the above Program,
it seems -- my MySQL server and my server are one!Well, those results came from the DBI::File driver, not the mysql driver. So it doesn't really mean anything about where the SQL server is.

my $dbh = DBI->connect("$dsn:$host:$database",$user,$pass) or die "no connection";You should be able to execute the same statement but without specifying a particular database. You would then use this handle to create your database.$dbh->do( q`CREATE DATABASE BLAH` );This only needs to be done once so you can use this DB now in the DBI connect. After connecting to that you can then do$dbh->do( q`CREATE TABLE blah blah blah` );

[Ultimater]

Thanks Jeff! Sorry for the late reaction, I tried creating a new database but it seems the MySQL server I am on doesn't allow me to have more than one database.

Access denied for user: 'xxxxxxxxxxxx' to database 'business'

and the code that generated that error

my $t=$dbh->do( q`CREATE DATABASE business` );
unless($t){print $dbh->errstr,"\n<br>\n";}
else {print "database created ok","\n<br>\n";}



I ended-up using the one database I am allowed and connected to it like so:

my $dsn = "DBI:mysql:database=$database;host=$hostname";
my $dbh = DBI->connect($dsn,$user,$pass) or die "no connection";

Then I went straight to creating the tables.

I gave this a run and it printed table created ok.

my $t;
$t=$dbh->do( "CREATE TABLE business (id int NOT NULL auto_increment primary key,
busname TEXT NOT NULL,
businfo TEXT NOT NULL,
contact TEXT NOT NULL)" );
unless($t){print $dbh->errstr,"\n<br>\n";}
else {print "table created ok","\n<br>\n";}

I ran it a second time and it printed Table 'business' already exists .

That's a good sign. I'll make a new thread for managing the tables when I get stuck.
Thanks again for your most-appreciated assistance, as usual.


note: all red text in this post is personal information which I removed.