sybperl-l Archive

From: Michael Peppler <mpeppler at peppler dot org>
Subject: How to Count Rows?
Date: Jun 2 1999 5:42PM

Oliver Scheit writes:
 > Hi everybody !
 > I'm having trouble ! Here's part of the script:
 > 
 > $dbrec = 0;
 > $dbrec = $q->ct_sql("select count (*) from my_table");
 > $dbrec++;
 > $q->ct_execute("insert into my_table values ($dbrec, 'Test')");
 > 
 > What I'm trying to do is:
 > 
 > Number    Text
 > --------    ------
 > 1               Test
 > 2               Test
 > 3               Test
 > 
 > I'm used to using dBase tables - there you can define an
 > auto-incremental Column.
 > Or just call Table.RecordCount using Delphi, etc etc.
 > 
 > If anybody could help me, I'd be grateful !

You should get a good book on SQL, and read the appropriate manuals
from Sybase on Transact-SQL.

Sybase has lots of features (auto-incrementing columns among them).

However, your code is broken because you use ct_sql() incorrectly.
ct_sql() doesn't return a value. It returns a reference to an array,
where each element is a reference to another array (for each row of
data returned by the SQL query).

So your code should be written like this:

$ret = $dbh->ct_sql("select count(*) from my_table");
$ret->[0]->[0]++;
$q->ct_execute("insert into my_table values ($ret->[0]->[0], 'Test')");

etc...

However, this won't work right if you have more than one user adding
data to your table - the rowcount could very well change between your
select and your insert.

So you should look at the IDENTITY column property, which does what
you want.

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@peppler.org    -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com