Solved

Perl and MySQL....

Posted on 2000-02-13
13
1,634 Views
Last Modified: 2008-02-20
Hi,

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:

#!/usr/bin/perl

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);

$db->selectdb($database);

$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,
D
0
Comment
Question by:dgb001
  • 6
  • 5
  • 2
13 Comments
 
LVL 3

Expert Comment

by:RobWMartin
ID: 2517396

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.

Rob
0
 
LVL 1

Author Comment

by:dgb001
ID: 2517492
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:

DROP TABLE MyTable\g

CREATE TABLE MyTable(
     ID INT,
     Field1 CHAR (1),
     Field2 CHAR (50),
     Field3 CHAR (50),
     Field4 CHAR (50),
     Field5 CHAR (50),
     KEY (Field2),
     PRIMARY KEY (Field1),
     KEY (Field4)
)\g

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,
D
0
 
LVL 1

Author Comment

by:dgb001
ID: 2517501
<< 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.  :-)

D
0
 
LVL 3

Expert Comment

by:RobWMartin
ID: 2517527
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.

Rob
0
 
LVL 3

Expert Comment

by:RobWMartin
ID: 2517536
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.

Rob
0
 
LVL 3

Expert Comment

by:RobWMartin
ID: 2517555
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.

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:thoellri
ID: 2517589
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?

0
 
LVL 1

Author Comment

by:dgb001
ID: 2517591
:-)

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,
D
0
 
LVL 5

Expert Comment

by:thoellri
ID: 2517610
D,

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
  Tobias



#!/usr/local/bin/perl

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,
                    $dbi_user,
                    $dbi_passwd,
                    { PrintError => 0 }
                   ) || die "Can't connect to database! - $DBI::errstr";
if (!existsTable($DBH, "sample")) {
   print "Creatign table sample\n";
   createTable($DBH,"sample",
                "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);
   $sth->finish;
} else {
   print "Table sample exists\n";
   print "Looking for Tom ...\n";
   my($sth)=$DBH->prepare(qq{select * from sample where name = ?});
   $sth->execute("Tom");
   while(my @row = $sth->fetchrow_array) {
     print "Found: $row[0], $row[1], $row[2]\n";
   }
   $sth->finish;
   print "Enumerating all records\n";
   $sth=$DBH->prepare(qq{select * from sample});
   $sth->execute;
   while(my $href=$sth->fetchrow_hashref) {
     foreach (keys %$href) {
       print "$_: $$href{$_}  ";
     }
     print "\n";
   }
   
}


$DBH->disconnect;

sub existsTable {
  my($handle, $table) = @_;
  my($aref);
  $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";
}
0
 
LVL 3

Accepted Solution

by:
RobWMartin earned 200 total points
ID: 2517650
D,

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


Rob
0
 
LVL 1

Author Comment

by:dgb001
ID: 2518499
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,
D
0
 
LVL 1

Author Comment

by:dgb001
ID: 2519253
Adjusted points to 200
0
 
LVL 1

Author Comment

by:dgb001
ID: 2519255
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,
D
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now