Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Jay Listo <jay dot listo at gmail dot com>
Subject: Re: Probably Trivial: Finding Largest Primary Key used in Table
Date: Jul 9 2005 4:27PM

BTW, how do I do this in Perl using DBI and DBD::Sybase

I tried...

my $maxsth = $dbh->primary_key_info(undef, $dbuser, 'TABLE_NAME');
  die "Unable to prepare SELECT MAX Key statement $DBI::errstr"
    unless $maxsth;

  $getMaxKey = $maxsth->fetchall_arrayref
       || die "Couldn't get the MAX KEY $DBI::errstr";

....but $getMaxKey is not yet the value from "select max(keycol) from 
TABLE_NAME" wrote:

>First of all, a primary key can be a composite value, a string, or some
>other value that isn't simply incrementable.
>But if you want to limit yourself to tables that have numeric primary keys
>then a "select max(keycol) from table" will give you that information.
>HOWEVER, this is not a safe method in an environment with fairly high
>volume, as the max value is likely to change between the time you fetch the
>value and the time you want to insert the next entry.
>The usual technique is to use a key table which holds the next available
>key value, and get this value in a transaction.
>You can read more about this issue at
> - 30/06/2005 13:16
>Sent by:
>To:    sybperl-l
>Subject:    Probably Trivial: Finding Largest Primary Key used in Table
>Hi All,
>This may sound trivial...but I've been trying to find out how to find
>the largest primary key that's been assigned/used within a table.
>I'm new to DB development while I have done some DBA.
>It would save me trouble when I want to keep control of the primary
>keys, make sure that when I insert a new record I can assign a unique
>primary key.
>I would like to do this using a perl script...not as some stored
>procedure or constraint so as to keep it as DB independent (regardless
>of Sybase, MySQL, Oracle, etc) as possible.
>Best Regards,
>This message and any attachments (the "message") is
>intended solely for the addressees and is confidential. 
>If you receive this message in error, please delete it and 
>immediately notify the sender. Any use not in accord with 
>its purpose, any dissemination or disclosure, either whole 
>or partial, is prohibited except formal approval. The internet
>can not guarantee the integrity of this message. 
>BNP PARIBAS (and its subsidiaries) shall (will) not 
>therefore be liable for the message if modified. 
>                ---------------------------------------------
>Ce message et toutes les pieces jointes (ci-apres le 
>"message") sont etablis a l'intention exclusive de ses 
>destinataires et sont confidentiels. Si vous recevez ce 
>message par erreur, merci de le detruire et d'en avertir 
>immediatement l'expediteur. Toute utilisation de ce 
>message non conforme a sa destination, toute diffusion 
>ou toute publication, totale ou partielle, est interdite, sauf 
>autorisation expresse. L'internet ne permettant pas 
>d'assurer l'integrite de ce message, BNP PARIBAS (et ses
>filiales) decline(nt) toute responsabilite au titre de ce 
>message, dans l'hypothese ou il aurait ete modifie.