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 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 ...

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.