Heidegger has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, I have to make some reports on a legacy mySQL database. I'm using DBIx::Recordset to connect to it: 
use DBIx::Recordset;

my %db_setup =
(
  '!DataSource'     => "DBI:mysql:$database_name:$url",
  '!Username'     => $user,
  '!Password'         => $password,
  '!Table'        => 'users'    
);

*users = DBIx::Recordset->Setup(\%db_setup) or die "Couldn't connect t
+o the database: $DBI::errstr.\n";

[download]
The problem is: the table name in the database is Users. My script cannot connect to that table. It must be an upper case problem, doesn't matter how I write it, "users", "Users" or "USERS". The script writes, that $database_name.users does not exist. I cannot change the table name, I have to connect to this table somehow. I've heard somewhere, that when you connect to mysql, it's best when table names are in lower case. Has anyone encountered this upper case mysql table name problem? Thank you. P.S. My script runs on Windows platform, the database server is on Linux.
  • Comment on Connecting to mySQL using DBIx::Recordset - table name upper case problem
  • Download Code

Replies are listed 'Best First'.
Re: Connecting to mySQL using DBIx::Recordset - table name upper case problem
by gmax (Abbot) on Jan 27, 2003 at 10:17 UTC
    Table names in MySQL are case sensitive.
    update Only on Unix machines. Windows servers don't enforce this. Thanks to PodMaster for reminding me.

    'Users' is different from 'users' or 'USERS'. Check the real name for that table before running your script.

    That said, make sure that you can connect to that table using any other means, i.e. the standard MySQL command line client or the MySQL Control Center.

    The database server tells you that a table does not exist in two cases: (1) when the table is not there and (2) when you are not authorized to see it. The error messages should be different, though. Check also if the return message is the one generated by the database server or it is coming from an intermediate wrapper.

    You may try to access using the plain DBI
    #!/usr/bin/perl -w
use strict;
use DBI;
my $database_name = "mydb";
my $user = 'I_am';
my $password = "smart";
my $url="db.strangehost.com";
my $dbh=DBI->connect("dbi:mysql:$database_name;host=$url",
$user,$password, {RaiseError=>1});

for ($dbh->tables) {
    print "$_\n";
}

$dbh->disconnect();

    [download]
    This script will give you the list of tables you are authorized to see in that database.
     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
      Thanks for your help, I solved my problem by setting the following variable: 
      $DBIx::Recordset::PreserveCase = 1;

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom