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

In reply to Re^2: Concatenation of scaler reference by Largins
in thread Concatenation of scaler reference by Largins

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.