Perl Guru Forums: Perl Programming Help: DBI: DBI Record modification

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: DBI:
DBI Record modification

skanky
New User

Jul 25, 2002, 2:01 AM

Post #1 of 3 (2605 views)

DBI Record modification	Can't Post

Hi there,

I am new to Perl and databases, so sorry if this question is lame/old. Most of my work has been with MS ADO, so I am wondering how I can do similar functions in Perl.

My question is best illustrated by example - lets think of a sub that reads a number from a table in the database, increments the number in the database, and returns it.

in MS ADO it would would be something like this (terrible pseudocode follows)
----
Connect to database
recordset.open "select number from table where blah", <other options>, lock optimistic
i = recordset("number")
recordset("number") = recordset("number") + 1
recordset.close
return i

So basically we just use one recordset and one query ( appropriately locked), where we can read the value, and update the value at the same time.

All the tutorials I have seen in DBI seem to just run queries separately - i.e. not able to both read and write at once. Is there a way to do. At the moment I would have to write the sub in perl like this
-----
Connect to database
query = database->prepare "select number from table where blah" query->execute
($num) = query->fetchrow_array()
query->finish
query = database->prepare "update table set number = $num+1 where blah"
query->exeucte
query->finish
return $num

As you can see that I have to do two prepares, one to get the data, and one to update. This isn't so bad in trivial examples such as this, but when you have more complicated code it is rather long winded. Also, there is a
potential problem that if two processes run the same subroutine in the perl version, that they both may do the select and read the same number, then they may both do the update - i.e. there is (?) no automatic record locking etc.

I was wondering if someone can show me how, if possible, to access the database in a similar way to ADO - i.e. do the above stuff with only one query.

Thanks for your help

Paul
Enthusiast

Jul 25, 2002, 3:16 AM

Post #2 of 3 (2604 views)

Re: [skanky] DBI Record modification [In reply to]	Can't Post

$dbh->prepare("UPDATE Table SET number = number + 1 WHERE blah") or die DBI->errstr;

(This post was edited by RedRum on Jul 25, 2002, 3:17 AM)

skanky
New User

Jul 28, 2002, 2:48 AM

Post #3 of 3 (2596 views)

Re: [RedRum] DBI Record modification [In reply to]	Can't Post

Thanks Redrum. I think my example was a little to trivial in that it could be done in one sql update. What if I wanted to do something like grab the maximum number in the column, add one to that, then insert into the same table using that value. That would require multiple prepares (maybe unless you nested a query?).
e.g. select number + 1 as newnumber from table; separate prepare/exec,
insert into table values (newnumber, x,y, z); separate preprare exec.

Though having said that, I am having some trouble even getting something like the simple that redrum posted that to work. Basically when it updates, it should return the rows affected. But I am unable to get the info back after the update statement. Here is some code ...

$qry = &Prepare_Query($dbh, "update tblSetup set next_val_id = next_val_id + 1");
#$qry = &Prepare_Query($dbh, "select * from tblSetup");
$qry->execute() || die "error";
print "no rows = " . $qry->rows() ."\n";
while (@data = $qry->fetchrow_array){
print "\nsome stuff = " . $data[0]
}

In the above example, the select works fine (which is commented out). The update correctly updates the database, and the number of rows reflect the number of rows updated in the DB, but I can't get any information from those returned rows (such as the values for the numbers). I continually get errors saying 'fetch wihtout execute', (or variations). Ive tried fetching, binding columns etc - all to no avail.

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives