• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1715
  • Last Modified:

Perl and MySQL....


Couple of questions here, from a complete MySQL newbie.

I've just got my hosting company to set me up with MySQL, and now am trying to get it running - my problem is that I cant seem to get it to do anything, but I think that maybe I'm missing a couple of big chunks of knowledge somewhere along the line. So, I had an Access database which I converted to MySQL, using a little Macro which I found - but the thing is I have no idea what a MySQL database looks like anyway, so I'm not sure if I need to do something else with it or not, so if someone has a snippet of what a MySQL database actually looks like then that would be useful.

Secondly, my host gave me three bits of info - database name for my account, the userid and the password. I'm not really sure what they mean by "database name" - surely I can use any database name I want to cant I?

The code I'm using to try and test it out goes like this:


use Mysql;

$host="mydomain.com";    # I assume that's my domain name
$database="testdb.db";   # I guess this is the name of the database that I want to use
$user="myusername";   # as provided
$password="mypassword";  # as provided

$db = Mysql->connect($host, $database, $user, $password);


$querystring = $db->quote($querystring);

$query = $db->query($querystring);

while (%hash = $query->fetchhash) {
print $hash{'Name'}." Has an number of ";
print $hash{'Phone'};

I haven't actually done the querystring yet, because it wont even go that far, but I think I must be missing the point somewhere along the line.

Also, where do I put the database ? Can I just put it in cgi-bin or somewhere, or must it be in a certain place? And can I call it anything I want....etc.

Bottom line is this; I have an Access database which I believe to have been converted properly to MySQL, but I want to see another MySQL db to make certain it's ok; I want to call it testdb.db; I want to put it into cgi-bin; I want to make queries to it, pass the values back to the Perl script and then do my stuff with it - that's it. All I need to know is how.

Hoping very much that someone can answer all of the above for me - please explain everything in detail - I'm a total MySQL beginner, and pretty new at Perl.

Many thanks in advance,
  • 6
  • 5
  • 2
1 Solution

First of all, unless you were given create database privileges, you cannot call the database anything you want; without the aid of the database admin at your ISP, that is.  It's not that big a deal, anyhow.  It doesn't really matter what you call the database, it's the table names have some real value.  BTW: just as in Access you can have many tables in a MySQL database.

Secondly, you don't actually need to concern yourself with the location of the database files (if i get your meaning).  Since MySQL has a relatively sophisticated ownership and permission scheme, it needs to have control of the files.

I'm not sure I understand your concept of putting the database somewhere and querying it, etc.  MySQL runs as a daemon that listens for connections to a database, requests for information from that database, actions to perform on that database's tables, etc.  To know what the database "looks like" involves querying MySQL for the specific information you want to know about it.  What does some table look like (i.e. what are its columns and their types)?  What specific data is in some table?  etc.  If you have a shell account with your ISP, you can start up the interactive program for dealing with MySQL and get all kinds of info, including doing specific ad hoc queries on the tables in your database.

As for the code snippet you included, I'm not familiar with the Mysql module; I use DBI, since it is more portable.  Nontheless, how can it run without he $querystring part being specified?

Another thing, how did you "convert" the Access database to MySQL without  workable access to MySQL?  Did the macro you mentioned create a so-called dump file that can be piped into the MySQL database that your ISP setup?

Sorry for the rambling, out-of-sequence treatment of my comment.

dgb001Author Commented:
Hello Rob,

Thanks for the comments...

Ok, maybe I'm getting the term "database" confused with "tables". My ISP said that my "database" was called "xyz" (example), so I guess that what I'm really saying is that I'm trying to access a table (but what I used to call a database in Access). So I guess I can have as many "tables" as I want under the database name on my server ?

Location...I guess I'm getting confused here too, because I'm thinking that, ok I've converted an Access database to MySQL, and now I have a new file in a different format, and therefore all I need to do is to deliver it to some directory on my server, and using a Perl script I can access the database by using MySQL. Am I wrong here ?

Your last comment kinda makes me think that you're right about having created something that can be piped into something else - which makes me think I'm maybe missing the big picture here - what would it get piped into - and how ?

Here's a snippet of the file produced from the macro which I thought was converting it to MySQL:


     ID INT,
     Field1 CHAR (1),
     Field2 CHAR (50),
     Field3 CHAR (50),
     Field4 CHAR (50),
     Field5 CHAR (50),
     KEY (Field2),
     PRIMARY KEY (Field1),
     KEY (Field4)

INSERT INTO MyTable VALUES (11,'B','data...','data...','data...','data...',')\g
INSERT INTO MyTable VALUES (11,'B','data...','data...','data...','data...',')\g
INSERT INTO MyTable VALUES (11,'B','data...','data...','data...','data...',')\g

I've chopped a few fields off, and a few field def's just for ease of reading...but that's what I have after "converting" - but on looking at it, it does look much more like something that is going to get squirted into something - so if that is what the file is for, then I'll need to know how to go about doing that also. (The code for the conversion is at: http://www.mysql.com/Contrib/access_to_mysql.txt)

I think maybe I have the MySQL concept a bit wonky, so maybe some kindly soul might enlighten me on that too :-)

Anyway Rob, hoping very much that you'll be able to hang on for the ride whilst I get this lot sorted out, and hopefully I can then up the points some more.

Many thanks for your help so far,
dgb001Author Commented:
<< Nontheless, how can it run without he $querystring part being specified? >>

Sorry, forgot to mention about that bit...yes of course you're right, what I meant was that it was erroring on a database access error before it was even getting to the query.  :-)

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Ok.  The converted file is what I thought it was.  They are really just MySQL commands that can be run through the interactive program I mentioned earlier.  Actually, in this case it would no longer be acting interactively, but as a batch processor, so to speak.  You need to have shell access to do this; and some way to upload the file.

BTW:  The program I keep mentioning is called mysql.  It's really only a piece of the greater thing called MySQL.  There are actually several programs/utilities that interact with a backend program ( in *nix land these are called daemons ) that does the real work.  That backend program is mysqld.  It is always running as a server, in the client/server sense.  So it can actually respond to remote clients over a network.  Very powerful.  Anyhow, the interactive program, mysql, gives you the ability to issue commands to the MySQL server, mysqld.  There are many commands you can issue, not the least of which is a very substantial subset of ANSI SQL.  You can create tables, query tables, insert data, modify data, modify table structures, etc.  It's not GUI, but it's very flexible.  There is another utility called mysqldump that gives output much like the file you've got.  It is literally a file that contains commands to rebuild the database, creating necesary tables, indexes, functions, etc.  and commands to populate the database (i.e. the INSERT commands).  You need shell access to get mysql to load this file.

If you don't have shell access, we can explore the possibilities of using a perl cgi script to do it for you.

Hope this helps.

I should mention that I don't consider myself to be a MySQL expert/master/guru/etc.  No where near it.  It's just that I have used it in many relatively simple applications.  There could be better ( read that "simpler") ways to do it, but I'll help you with what I know until some master comes along to show us both a better way.

Something else.  In MySQL a database provides an administrative boundary for granting access to users.  The database administrator can grant privileges to users, based on the host the user is connecting from and the database the user is connecting to.  It is actually a little more complex, but that's the way I think of it.  Once the database admin has given you privileges to create tables, etc. on a database, you can forget about it and think in terms of tables, columns, queries, etc.

Sorry,  I'm just blurting things out as I think of them.

So, Rob, what problem(s) remain to be solved? I just lost it in all the back-and-forth above. Can you tell us what you're looking for?

dgb001Author Commented:

This all sounds pretty good...

I've gotten it into my head now that the "database" that my hosting company  mentions is really just an umbrella for a heap of as many tables as I want (which I would previously referred to as stand-alone databases in Access) - but regardless, I always need to know the name of the "database" in order to access the tables - much the same as in any other database environment - so I think I'm ok on that one now.

In actual fact as you mentioned earlier, I really don't need to know where the file (or table) is stored - it's not like a file I can "see" any longer is it - it should now have been piped into MySQL land, which as far as I'm now concerned is just somewhere out there in cyberspace, and don't really need to know.

Yes, I do have telnet access - and did find my way through a few pages of a brief tutorial which helped a bit - managed to create a table and pump in a couple of records from a mysql> prompt, so I'm kinda getting the idea of what it's all about now. I think I'll be ok on the Perl bit of it once I get the database sorted out (famous last words, which I'm sure will come back to haunt me) - my big thing now, as you mentioned, is to find/write a nice litte script that will pump the sql-dump-text into a database. I would cut and paste it to a mysql> prompt (quite a sad idea for a programmer really), but there are just over 50,000 records, so probably not the best idea in the world. I cant imagine it would be too difficult to write a few lines to read through the sql-dump and then pump the commands into the database. I guess I could define the database before running the script even, I mean I don't need to go to the hassle of actually "creating" the database in the script as well do I.

Hmmm, this is getting clearer as I ramble on - amazing how quickly you can pick things up when you need to.

Ok, so I'm just about at the stage now where I need to find/write a bit of code to squirt these records into the database...if you have anything handy then that would be useful...but if not then I'll take a crack at it myself in the next couple of days.

Meanwhile, thanks very much for your explanation of how it all hangs together, I must admit I was way off the mark when I first posted this question - you have certainly helped to enlighten me.

Must go for now...getting late,
Hope we can liase more soon,

this is a piece of code I used to illustrate DBI/mysql usage - hope you find it useful: This sample does not use the Mysql interface, but the layer on top of it DBI (with the mysql-driver DBD::mysql). Check if DBI is (use DBI;) is available on the server. If yes, use it, because it offers the advantage of an independent layer on top of CSV-files, Oracle, Sybase,mSQL, MySQL etc.

Hope this helps


use strict;
use DBI;

my $dbi_db             ="xyz";
my $dbi_user           ="username";
my $dbi_passwd         ="password";
my $dbi_datasource     ="dbi:mysql:$dbi_db";
my ($DBH);

$DBH = DBI->connect($dbi_datasource,
                    { PrintError => 0 }
                   ) || die "Can't connect to database! - $DBI::errstr";
if (!existsTable($DBH, "sample")) {
   print "Creatign table sample\n";
                "id int not null auto_increment, name varchar(255), value int, primary key(id)");
   my($sth)=$DBH->prepare(qq{insert into sample (name, value) values (?,?)});
   print "Inserting data into sample\n";
   $sth->execute("Sample", 1234);
   $sth->execute("Joe", 100);
   $sth->execute("Tom", 815);
   $sth->execute("Dana", 1);
} else {
   print "Table sample exists\n";
   print "Looking for Tom ...\n";
   my($sth)=$DBH->prepare(qq{select * from sample where name = ?});
   while(my @row = $sth->fetchrow_array) {
     print "Found: $row[0], $row[1], $row[2]\n";
   print "Enumerating all records\n";
   $sth=$DBH->prepare(qq{select * from sample});
   while(my $href=$sth->fetchrow_hashref) {
     foreach (keys %$href) {
       print "$_: $$href{$_}  ";
     print "\n";


sub existsTable {
  my($handle, $table) = @_;
  $aref = $handle->selectcol_arrayref(q{show tables}) ||
     die "Can't get tables - $DBI::errstr";
  foreach (@$aref) {
    return 1 if (/^$table$/);
  return 0;

sub createTable {
  my($handle, $table, $def)=@_;
  my($sql)=qq{create table $table ($def)};
  $handle->do($sql) || die "can't create table - $DBI::errstr";

Here's the moment you've been waiting for:

mysql -pyour-password xyz < the_infamous_access_2_mysql_file

Assuming you've got the file uploaded to your home directory on the ISP, login and issue the command above, substituting where necessary.  xyz is the database name you mentioned above, so I use it here.  The shell will do the squirting for you (that's what the < is for), so you don't have to write a script afterall.

If that procedure bombs, we can explore other possibilities, but I've used it many times before without serious problems.

One thing to watch out for.  You said you had created some tables to play with.  Make sure the play tables' names don't clash with the real ones you are importing.  If they do, get into mysql interactively and drop those tables first.  Like so:

mysql -ppassword xyz
> drop table play1;
> drop table play2;
> exit

dgb001Author Commented:
Firstly, Tobias, many thanks for the code which you supplied, yes, I think I will check if DBI is available - seems to be the way to go from other bits and pieces I've seen too - so, thanks for taking the time to post your comments, much appreciated.

Rob, wow, that looks like the thing I need doesn't it! Yeah, definitely better than hacking around with a script for the next few hours. I'm not in a position to do it right now, bit give me till the end of the day and I'll get back to you and let you know how it went. The dummy table I created was just called "test", so no worries about clashing (until the next time I go to create a table called "test") - :-)

Many thanks again, more later,
dgb001Author Commented:
Adjusted points to 200
dgb001Author Commented:
Yep, that works a treat. I needed to sort out the table definition just a little (add a couple of NOT NULL's etc), but really that was just where the macro hadn't done it's job - other than that, with a little bit of trial and error I managed to get it working ok. I haven't done the whole database yet, but should certainly be able to now. Made a couple of queries ok, and well....everything is just great! My next bit will be just to get a few Perl scripts knocked up to query it...but hopefully shouldn't have too many probs with that.

Thanks very much indeed for your help, very much appreciated, and I have increased the points to 200.

Best wishes,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now