--- [EMAIL PROTECTED] wrote:
>
>
>
>
>
> Hello list!
> I have run into problems trying to use SQLite via perl
> DBA.
> According to the SQLite.org web site, DECLARE CURSOR
> should be supported
> (actually, it is not in the list of the only SQL not
> supported).
> I get a error saying "error near DECLARE". I have tried
> to declare cursor
> in many ways but always get the same error.
> If "cursro" is not supported, does anybody have a
> suggestion on how to
> achieve the same end?
As I remember, the PERL DBI doesn't support cursors.
Instead, depending on how you requested the data, you
end-up with something like an array, and you iterate
through it. I leave it vague here, because, like with most
other things PERL, there are numerous ways to get what you
want :-)
I for small to moderate sized datasets, like the DBI
utility methods. $dbh->selectall_arrayref('select a, b, c
from t1'); returns an array (records) of arrays (columns in
record).
Otherwise, you can do the 3 or 4-step process of:
$sth = $dbh->prepare('select a, b, c from t1');
$sth->execute();
$sth->bind_columns(undef, \$col_a, \$col_b, \$col_c);
while ($sth->fetchrow_arrayref()) {
#do something with the row data
print "$col_a\t$col_b\t$col_c\n"; #perhaps
}
When you use the multi-step prepare/execute/fetch
algorithm, once a row is fetched, you can't back-up later
and fetch it again.
If you use the utility methods like
$dbh->selectall_arrayref() and its cousins, you iterate
through the array, and can re-set the iterator to jump
forward or back at any time.
Have a look at the DBI documentation. I always need more
explanation then those PERL module docs provide, and I
found the O'Reilly PERL DBI book very useful.
There's also some useful explanations and usage tips
available. Just do a Google search on "perl dbi".
Of particular interest for me was this presentation
http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/index.htm
-Clark
