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!

Thank you for the very useful pointers. They will be used

I went ahead and made changes based on your suggestions. Here is what I now have.

sub NewTable
{
   $table = $_[0];
   $column = $_[1];
   $indexname = $_[2];

   $sqltext =  "CREATE TABLE $table (
        id INTEGER  PRIMARY KEY AUTOINCREMENT,
        $column VARCHAR)";
   $dbh->do($sqltext);
   $insertrow = $dbh->prepare ("INSERT into $table ($column) values(?)
+");
   $insertrow->execute("Dummy");
   $sqltext = "CREATE INDEX $indexname ON $table ($column)";
   $dbh->do ($sqltext);
   $id = $dbh->last_insert_id(undef, undef, undef, undef);
}
[download]

Box, bookleafplate and other tables not seen need to be separat tables for normalization purposes, for example, a (book in this case) may have come from a box labeled 'Skowhegan Me No 345' and may or may not have a bookleafplate ( fronts piece image ). I have one table which contains all of the unique information, and then id's to tables where duplication may occur, thus the need for the last_insert_id call.
I spent many years using Sybase, Oracle and other relational databases, but not within perl until now. I like SQLite because all I have to worry about is one file which makes for easy (physical) portability.

I did use perl for a while shortly after it emerged in the 80's while working on a CAD/CAM software project, but only now have become serious about it.
Ted Stefanik (while at MIT) reviewed the first PERL book for Larry Wall & Tim O'Reilly (who's book publishing company was located at Fresh Pond in Cambridge Ma. at that time)
I worked with him, and it is because of him that I started using perl. Ted has contributed a lot to perl, some of his early work is still available on the web see: http://stuff.mit.edu:8001/afs/net/dev/dev/ project/tcl/tcl-6.7/perl-tcl-debug"
Again, thanks a lot for your very useful advise.
Largins