Perl and MySQL....

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

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

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

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

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

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?

:-)

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

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";
}

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

Adjusted points to 200

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