Re^3: Concatenation of scaler reference

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!

Comment on Re^3: Concatenation of scaler reference Select or Download Code

Replies are listed 'Best First'.
Re^4: Concatenation of scaler reference by Largins (Acolyte) on Dec 14, 2011 at 13:30 UTC
Thank you for the very useful pointers. They will be used	[reply]
Re^4: Concatenation of scaler reference by Largins (Acolyte) on Dec 15, 2011 at 16:26 UTC
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	[reply] [d/l]