SCM

Task Detail

View Subprojects | Next Release | Gantt Chart | Import/Export CSV

Submitted by:
Cristiano Duarte (cunha17)
Permalink:
http://pgfoundry.org/pm/t_follow.php/1000148
Category
None
Task Detail Information (JSON):
application/json or text/plain
Percent Complete:
100%
Priority:
3
Subscribe to task
Start Date:
2007-09-15
End Date:
2007-09-19
Task Summary:
Use cursors for fetching data
Original Comment:
Replace de standard DBI behaviour of fetching all resultset when executing a query on the remote database, specially on Complete refreshes.
Also make use of cursors on Fast refreshes or whenever is possible.
Assigned to
cunha17
Dependent on task
Test cursors on PostgreSQL
Test cursors on Oracle
Use cursors on Complete refresh
Use cursors on Fast refresh
Hours
8
Status
Closed

No Tasks are Dependent on This Task

Related Tracker Items

Artifact Summary Tracker Open Date Remove Relation
Use cursors for fetching data Feature Requests 2007-09-12 20:06

Followups

Comment Date By
Tests done with Oracle, Pg and SQL Server/ODBC. 2007-09-18 03:15 cunha17
DONT FORGET: Need to implement cursors on FastRefreshes also. 2007-09-14 02:29 cunha17
Support added to CVS.
Since I don't have access to an Oracle server right now (I'll try the free version), I couln't test the "Oracle Cursor". So I'll leave this task as 95% complete until I can test it.
2007-09-14 02:21 cunha17
Oracle example using CURSOR:
[quote]
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect(...);
my $sth1 = $dbh->prepare(q{
BEGIN OPEN :cursor FOR
SELECT table_name, tablespace_name
FROM user_tables WHERE tablespace_name = :space;
END;
});
$sth1->bind_param(":space", "USERS");
my $sth2;
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute;
# $sth2 is now a valid DBI statement handle for the cursor
while ( my @row = $sth2->fetchrow_array ) { ... }
[/quote]
2007-09-13 02:12 cunha17
Since DBD:Pg does not support cursors and maybe some other implementations do the same( or have non standard implementations), it might be necessary to write an database-independent function that calls a "driver" function that returns the cursor if possible or execute the statement when there is no other way. 2007-09-13 02:05 cunha17
Example
[quote]
### Fetch the rows of result data from the database
### as an array ref....
while ( $array_ref = $sth->fetchrow_arrayref ) {
### Print out a wee message....
print "Megalithic site $arrayref->[0] is a $array_ref->[1]\n";
}
die "Fetch failed due to $DBI::errstr" if $DBI::err;

[/quote]
2007-09-13 01:41 cunha17
Also try to replace "fetchrow_array" with "fetchrow_arrayref" for better performance. 2007-09-13 01:39 cunha17

Task Change History

Field Old Value Date By
dependent_on_id1000152 2007-09-18 03:15 cunha17
dependent_on_id1000149 2007-09-18 03:15 cunha17
percent_complete95 2007-09-18 03:15 cunha17
status_id1 2007-09-18 03:15 cunha17
dependent_on_id1000153 2007-09-18 03:15 cunha17
dependent_on_id1000150 2007-09-18 03:15 cunha17
dependent_on_id1000149 2007-09-14 23:54 cunha17
dependent_on_id1000150 2007-09-14 23:54 cunha17
dependent_on_id1000149 2007-09-14 22:32 cunha17
dependent_on_id100 2007-09-14 22:31 cunha17
hours5 2007-09-14 22:31 cunha17
dependent_on_id100 2007-09-14 02:29 cunha17
dependent_on_id100 2007-09-14 02:21 cunha17
end_date2007-09-24 2007-09-14 02:21 cunha17
hours30 2007-09-14 02:21 cunha17
percent_complete0 2007-09-14 02:21 cunha17
dependent_on_id100 2007-09-13 02:12 cunha17
dependent_on_id100 2007-09-13 02:05 cunha17
dependent_on_id100 2007-09-13 01:41 cunha17
dependent_on_id100 2007-09-13 01:39 cunha17
Powered By FusionForge