Please read Why it's stupid to `use a variable as a variable name'

Either I'm missing something important... or you're going waaaay out of your way to achieve this:

#!/usr/bin/env perl
use Modern::Perl;
# 943467

my $MaxScanId = 45;
my $a = "Scan";

print "$a, \$MaxScanId: $MaxScanId";
[download]

well, let me see if I can clarify what I am trying to do:
Given:
1. I have a series of database tables that have primary keys with the same name as the table and an added 'Id',
for example, tablename Person, key PersonId, tablename scan, key scanId etc.
2. Data is being added to these tables on the fly, based on information contained in an xml document
3. When the perl program is first opened, it gets the max(Id's) for each table and stores them in scalers
so that new items can be added to the database when required (not already there) without the need for
reselecting the maxid of the table (It's already in the scaler which will be incremented after each use)
4. Since I have intentionally designed these tables to have the same root for each column, it should be easy
to dynamically generate their names if a root is given.
Here is a portion of the code, run once which extracts the MaxId's

#!/usr/bin/env perl
use strict;
use warnings;

# Max Values
my $MaxbookplateleafId = 0;
my $MaxboxId = 0;
my $MaxcameraId = 0;
my $MaxcollectionId = 0;

sub GetMaxDbItem
{
   $col1   = $_[0];
   $table  = $_[1];
   $id     = $_[2];

   $query = "SELECT max($col1) FROM $table\;";
   $queryhandle = $dbh->prepare($query);
   $queryhandle->execute();
   $queryhandle->bind_col(1, \${$id});
   $queryhandle->fetch();
   if($debug eq 2) { print "$table MaxId: ${$id}\n"; }
   $queryhandle->finish();
}

sub PreloadMaxValues
{
   # BookplateLeaf
   GetMaxDbItem ("BookplateLeafId", "BookplateLeaf", \$Maxbookplatelea
+fId);

   # Box
   GetMaxDbItem ("BoxId", "Box", \$MaxboxId);

   # Camera
   GetMaxDbItem ("CameraId", "Camera", \$MaxcameraId);
}
[download]

Once these values are loaded, the remainder of the XML is parsed and the values
are stored in the individual normalized tables, and the links and static data is stored in
a main 'Entity' table. to do this effectively, the XML has been parsed and loaded into a hash.
When it comes time to add the data to the database, i can get the data by keyname (which also happen
to be the database primary keys). Next I must assign the table id to the entity rec and that requires
a database query to see if the value has already been entered (keeping it normalized). If already there,
I use the existing key and im done with the item.
The tricky part is when it is not there. in this case, i have to:
1. Get the next available Id for the given table (remember, same rootname as the table, and also the hash key
This is where I want to build the max key Id scaler name dynamically. I don't know what it is, but know the format
and can therefore build it on the fly. Once I have that, I can dynamically build the SQL insert statement
The code below is the unfinished subroutine and calling sequence. Much of it works already.

sub AddId
{
   my $Index = $_[0];
   my $Value;
   my $MaxPtr = "";

   ${$id} = "";
   if(exists $TableEntries{ $Index }) {
      $Value  = $TableEntries{ $Index }[0];
      print "AddId $Index\n";
      print "Value: $Value\n";
      $query = "SELECT $Index"."Id FROM $Index  where $Index"."name = 
+\'$Value\'\;";
      if( $debug eq 2 ) { print "\nquery: $query\n"; }
      $queryhandle = $dbh->prepare($query);
      $queryhandle->execute();
      $queryhandle->bind_col(1, \${$id});
      $queryhandle->fetch();
      if($debug eq 2) { print "value: $Value id: ${$id} [end]\n"; }
      if( ${$id} eq "" ) {
         # Insert new item
         $MaxPtr = "\$"."Max"."$Index"."Id";
         print "MaxPtr: $MaxPtr, ${$MaxPtr}\n";
         my $newinsert = "Insert into $Index values(";
         # Rest goes here
      }
      $queryhandle->finish();
   }
}
[download]


It is called thusly



sub CreateTableEntries()
{
   $ItemNo = 0;
   &FixedEntity;
   if  ( $EntityRec ne "" ) {
      if ($debug eq 2) { print "EntityRec: $EntityRec\n" };
      if ($count++ eq 25) { die; }   #debug only, limit to 25 XML docu
+ments   
   } else {
      print "Identifier missing for file $filename\n";
      return;
   }
   AddId("bookplateleaf");
   AddId("box");
}
[download]


The schema for the two tables shown bookplateleaf and box are:



CREATE TABLE bookplateleaf (
  bookplateleafId INTEGER  NOT NULL  ,
  bookplateleafname VARCHAR      ,
PRIMARY KEY(bookplateleafId));
insert into bookplateleaf values(0, "Dummy");

CREATE TABLE box (
  boxId INTEGER  NOT NULL  ,
  boxname VARCHAR      ,
PRIMARY KEY(BoxId));
insert into box values(0, "Dummy");
[download]

Hope this clarifies the purpose of my madness.
largins

Speaking for myself, I still don't see why you need to build the name of a scalar variable dynamically. It would be much easier and neater to use a hash to store the values, and build the hash key dynamically.

You can create variable names dynamically in Perl, but it is very rairly justified.

Off topic, but I notice that you are using eq for comparing numerics. eq is for string comparisons, better to use == instead.

BTW, your supplied code will not compile, you use strict (which is good) but many of your variables are not declared using my.

When you create the table, you can specify that the primary key gets autoincremented...

$dbh->do ("CREATE TABLE bookplateleaf
             (id        integer PRIMARY KEY AUTOINCREMENT,
              name      varchar(20), 
             )            
          ");

my $insert_name = prepare ("INSERT into bookplateleaf
                            name = ?");

$insert_name->execute("someNaME");
[download]

The DB will keep track of the primary key which is an integer and will auto increment it so that every "name" will have a unique id number.

Does that help?

Update: I not quite sure what this "box" this is. I could be that just one table will be all that you need.

$dbh->do ("CREATE TABLE bookplateleaf
             (id        integer PRIMARY KEY AUTOINCREMENT,
              name      varchar(20), 
              box       varchar(8192),
             )            
          ");

my $insert_row = prepare ("INSERT into bookplateleaf
                            name = ?,
                            box = ? ");
$insert_row->execute($name,$box);
[download]

For some DB's, like SQLite, the varchar(X), the X is just a "hint" and the actual data can be much larger. It is ok for name to appear more than once because it is not the primary key. There is another step to index the database according to more than the primary key - so perhaps that you can search for "name" efficiently.

I don't think that you are "stupid"..on the contrary for 30 days you have gotten quite far along!