ADO [was community poll]
Tobias Martinsson
>I was interested in more complex examples (albeit I was vague on my
>original post) for accessing data stored in a relational database, in
>particular SQL Server or Access.
OK.
>For example, I needed to filter a Recordset a few months back, so I
>used the VBScript example in ASP in a Nutshell. I converted to
>PerlScript, but it took me 5 hours to get it working.
The book that I mentioned as upcoming will help many with ADO from
filtering recordsets to shaping hierarchical recordsets.
>Shortly thereafter, I need to break pages up logically, and someone
>made a reference to a perlscripters.com article, which helped
>immensely. Mostly due to the fact everything was written in
>PerlScript. I had the whole thing working within an hour.
It's a somewhat rough article, but deals with the ADO-parts and leaves
the HTML implementation to the reader.
>Some examples on some of the lesser used ADO Properties or Methods
>would be helpful. For example, Recordset Properties: Bookmark,
>MaxRecords, MarshalOptions, Recordset Methods: Addnew, Clone, GetRows,
>NextRecordset, Requery, Resync, Update, UpdateBatch
I will do this simplisticly, and starting with the methods:
AddNew - this method I use with array references that looks like "[elem,
elem, elem]" because the API expects array references, thus
"(elem,elem,elem)" will not work. You can also use single names such as
"fieldName" instead of a list,and you can use an index such as "1"
either in single of list-context. Phew. So, the method call in one
example looks like this: AddNew(['firstname', 'lastname'], ['bob',
'brock']);
UpdateBatch - If you did a series of calls to AddNew in batch-update
mode (which is set by the locktype adLockBatchOptimistic), it will cache
the new records, say that you added ten new records, for example, and
only write them to disk when you call UpdateBatch. $rst->UpdateBatch();
Update - If you added one record by AddNew not in batch-update mode, it
may happen that ADO calls the Update-method automatically, but to ensure
that it is called and that the changes are made to the database, you
should call it yourself: $rst->Update();
Resync - Consider that more clients than one are using the database. Say
that five clients have the ability to update and add new records to the
database. If you use a cursor such as forward-only or static, you simply
have a static recordset and changes made to the database are not visible
to you. For this purpose, you would call resync to refresh your current
recordset with whatever has been added to the database. $rst->Resync();
Requery - This one simply reissues the original command, such as SQL
command, that created the recordset. It closes the recordset and then
opens it again.
NextRecordset - You can build an SQL command like this "select * from a;
select * from b; select * from c;", and the first time you call open
with the Recordset, it will return all records from table "a". However,
NextRecordset allows you to execute the next SQL statement and either
return a new recordset, or update the old one, so you can use it the
following way:
$newRst = $rst->NextRecordset(); or just $rst->NextRecordset();
It will go through each command separated by semicolon for each call th
at you make to NextRecordset.
GetRows - Get rows return the number of rows copied from the Recordset
into a two-dimentional array as were specified in the method call, and
if not specified, it returns all records. For example,
$numrecords = 10;
$records = $rst->GetRows($numrecords);
Clone - This returns a copy of your current recordset.
$clonedRst = $rst->Clone();
###################
Properties
###################
Bookmark - This property returns an identifier for the current record at
which you are positioned. This identifier allows you to set the position
of the recordset to that record at any time by setting the Bookmark
property to a valid identifier. When a recordset is returned, each
record automatically has a bookmark, and that is why this works.
MaxRecords - Set this property before the Recordset is opened, and it
will return only the number of records set in the property:
$rst = $Server->CreateObject("ADODB.Recordset");
$rst->{MaxRecords} = 25; # Will return only 25 records
$rst->Open("SELECT * FROM sometable" ...etc... );
MarshalOptions - Marshaling describes how the records are transported
back to the underlying datasource, and by tweaking MarshalOptions, you
may retrieve better performance. This property is set with client-side
recordset (adUseClient). You can set the property to 0 (adMarshalAll)
and it will return all rows to the server, or you can set it to 1
(adMarshalModifiedOnly) and it will return modified rows to the server.
>Are they unnecessary or most people haven't taken the time to learn how
>to use them?
In short, ADO is underestimated. :)
--
Tobias
Sent via Deja.com http://www.deja.com/
Before you buy.
Pat Kolis
Thanks for the detailed response. Can you recommend an
ADO book, or should I wait for the PerlScript book
to come out? BTW, do you know who the publisher is?
Pat
In article <83tpah$qji$1...@nnrp1.deja.com>,
Tobias Martinsson
>Thanks for the detailed response. Can you recommend an ADO book, or
>should I wait for the PerlScript book to come out? BTW, do you know
>who the publisher is?
The book is from Wiley Computer Publishing, http://www.wiley.com, and
the ADO stuff in it is good for both Perl and PerlScript, with a CD
that has examples in both flavors available. Wait for the book. It
treats ADO 2.5, which introduces two new objects, and ADO 2.5 comes as
a system component of Windows 2000, and it will probably be
downloadable when Windows 2000 is released.