Re: Oracle schema names: sqlplus 'v' Perl
On Fri, 9 Jun 2006 01:17:06 -0400, Jeffrey Seger wrote: Hi Jeffrey So a similar query to what you are looking for in table_info is: select table_name, owner from all_tables where table_name like '%STATE%'; Hmmm. SQL select table_name, owner from all_tables where table_name like '%STATE%'; TABLE_NAME OWNER -- -- SCHEDULER$_STEP_STATE SYS WRI$_SQLSET_STATEMENTS SYS WWV_FLOW_TREE_STATEFLOWS_020100 STATE SYSTEM LOGMNR_DICTSTATE$ SYSTEM Nice try, but that gives the same value as before, SYSTEM, which doesn't work :-((. However it does give some tables with OWNER=SYS which I didn't get before... -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
Re: Oracle XE (V 10.2.0.1.0) for Windows and ODBC
On Fri, 12 May 2006 14:39:02 +1000, Ron Savage wrote:
Hi Ron
Just for the archives:
When I connect via Perl, eg using:
dsn=dbi:ODBC:dsn_name
I get this error:
DBI connect('dsn_name','system',...) failed: Specified driver could
not be loaded due to system error 1114 (Oracle in XE). (SQL-
IM003)(DBD: db_login/SQLConnect err=-1) at D:\scripts\create.pl
line 17
It's starting working after 1 reboot or 3.
--
Ron Savage
[EMAIL PROTECTED]
http://savage.net.au/index.html
DBD::DB2 execute and finish problem
Hi,
I'm using DBD::DB2 0.78 and DBI 1.51. I am finding that code which is working
to DBD::ODBC and DBD::mysql fails with invalid cursor state but inserting a
call to finish makes it work. Up until now, I've never used finish because the
docs say:
If execute() is called on a statement handle that's still active
($sth-{Active} is true) then it should effectively call finish() to tidy up
the previous execution results before starting this new execution.
and
The finish method is rarely needed, and frequently overused, but can sometimes
be helpful in a few very specific situations to allow the server to free up
resources (such as sort buffers).
When all the data has been fetched from a SELECT statement, the driver should
automatically call finish for you. So you should not normally need to call it
explicitly except when you know that you've not fetched all the data from a
statement handle. The most common example is when you only want to fetch one
row, but in that case the selectrow_* methods are usually better anyway. Adding
calls to finish after each fetch loop is a common mistake, don't do it, it can
mask genuine problems like uncaught fetch errors.
An example is:
create table fred (a int not null primary key)
insert into fred values (1)
insert into fred values (2)
insert into fred values (3)
perl -w -e 'use DBI; my $dbh = DBI-connect(dbi:DB2:mydsn, xxx,
yyy); my @a = (1,2,3); $sth = $dbh-prepare(q/select * from fred where a
= ?/); foreach my $a (@a) {$sth-execute($a); my @row = $sth-fetchrow_array;}'
which returns:
DBD::DB2::st execute failed: [IBM][CLI Driver] CLI0115E Invalid cursor state.
SQLSTATE=24000 at -e line 1.
This seems to fall into the category of the first quote from the docs which
suggest finish should be called for you. I don't want to add the finish if it
should not be required and this is a huge amount of code to work through
anyway. I know I could possible avoid the issue if I used selectall_* but here
again, I'd have to check a lot of code to make this change.
Is this a bug in DBD::DB2?
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Oracle schema names: sqlplus 'v' Perl
I On Jun 9, 2006, at 2:08 AM, Ron Savage wrote: On Fri, 9 Jun 2006 01:17:06 -0400, Jeffrey Seger wrote: So a similar query to what you are looking for in table_info is: select table_name, owner from all_tables where table_name like '%STATE%'; Hmmm. SQL select table_name, owner from all_tables where table_name like '%STATE%'; TABLE_NAME OWNER -- -- SCHEDULER$_STEP_STATE SYS WRI$_SQLSET_STATEMENTS SYS WWV_FLOW_TREE_STATEFLOWS_020100 STATE SYSTEM LOGMNR_DICTSTATE$ SYSTEM Nice try, but that gives the same value as before, SYSTEM, which doesn't work :-((. However it does give some tables with OWNER=SYS which I didn't get before... Just for the sake of completeness, are you logging in as 'SYSTEM' in your perl script? Or are you logging in as a user which might not have permission to see the table? -Chris -- Technically, you would only need one time traveler convention - Dorothy Gambrell, Cat and Girl
Re: DBD::DB2 execute and finish problem
WAG here: will the DBD::DB2 driver implicitly finish the resources if
you try calling $sth-fetchrow_array() again?
I don't know if DBD::DB2 can know whether there are more rows left in
the result set until you try fetching the next row, ergo it keeps the
statement handle active.
Dan
On 09/06/06, Martin J. Evans [EMAIL PROTECTED] wrote:
Hi,
I'm using DBD::DB2 0.78 and DBI 1.51. I am finding that code which is working
to DBD::ODBC and DBD::mysql fails with invalid cursor state but inserting a
call to finish makes it work. Up until now, I've never used finish because the
docs say:
If execute() is called on a statement handle that's still active
($sth-{Active} is true) then it should effectively call finish() to tidy up
the previous execution results before starting this new execution.
and
The finish method is rarely needed, and frequently overused, but can sometimes
be helpful in a few very specific situations to allow the server to free up
resources (such as sort buffers).
When all the data has been fetched from a SELECT statement, the driver should
automatically call finish for you. So you should not normally need to call it
explicitly except when you know that you've not fetched all the data from a
statement handle. The most common example is when you only want to fetch one
row, but in that case the selectrow_* methods are usually better anyway. Adding
calls to finish after each fetch loop is a common mistake, don't do it, it can
mask genuine problems like uncaught fetch errors.
An example is:
create table fred (a int not null primary key)
insert into fred values (1)
insert into fred values (2)
insert into fred values (3)
perl -w -e 'use DBI; my $dbh = DBI-connect(dbi:DB2:mydsn, xxx,
yyy); my @a = (1,2,3); $sth = $dbh-prepare(q/select * from fred where a
= ?/); foreach my $a (@a) {$sth-execute($a); my @row = $sth-fetchrow_array;}'
which returns:
DBD::DB2::st execute failed: [IBM][CLI Driver] CLI0115E Invalid cursor state.
SQLSTATE=24000 at -e line 1.
This seems to fall into the category of the first quote from the docs which
suggest finish should be called for you. I don't want to add the finish if it
should not be required and this is a huge amount of code to work through
anyway. I know I could possible avoid the issue if I used selectall_* but here
again, I'd have to check a lot of code to make this change.
Is this a bug in DBD::DB2?
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: DBD::DB2 execute and finish problem
On 09-Jun-2006 Dan Scott wrote:
WAG here: will the DBD::DB2 driver implicitly finish the resources if
you try calling $sth-fetchrow_array() again?
Yes.
I don't know if DBD::DB2 can know whether there are more rows left in
the result set until you try fetching the next row, ergo it keeps the
statement handle active.
Yes, but the docs say that if execute is called again then the next execute on
an active statement implicitly calls finish and that is what happens in other
drivers - just not DBD::DB2.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Dan
On 09/06/06, Martin J. Evans [EMAIL PROTECTED] wrote:
Hi,
I'm using DBD::DB2 0.78 and DBI 1.51. I am finding that code which is
working
to DBD::ODBC and DBD::mysql fails with invalid cursor state but inserting a
call to finish makes it work. Up until now, I've never used finish because
the
docs say:
If execute() is called on a statement handle that's still active
($sth-{Active} is true) then it should effectively call finish() to tidy up
the previous execution results before starting this new execution.
and
The finish method is rarely needed, and frequently overused, but can
sometimes
be helpful in a few very specific situations to allow the server to free up
resources (such as sort buffers).
When all the data has been fetched from a SELECT statement, the driver
should
automatically call finish for you. So you should not normally need to call
it
explicitly except when you know that you've not fetched all the data from a
statement handle. The most common example is when you only want to fetch one
row, but in that case the selectrow_* methods are usually better anyway.
Adding
calls to finish after each fetch loop is a common mistake, don't do it, it
can
mask genuine problems like uncaught fetch errors.
An example is:
create table fred (a int not null primary key)
insert into fred values (1)
insert into fred values (2)
insert into fred values (3)
perl -w -e 'use DBI; my $dbh = DBI-connect(dbi:DB2:mydsn, xxx,
yyy); my @a = (1,2,3); $sth = $dbh-prepare(q/select * from fred where a
= ?/); foreach my $a (@a) {$sth-execute($a); my @row =
$sth-fetchrow_array;}'
which returns:
DBD::DB2::st execute failed: [IBM][CLI Driver] CLI0115E Invalid cursor
state.
SQLSTATE=24000 at -e line 1.
This seems to fall into the category of the first quote from the docs which
suggest finish should be called for you. I don't want to add the finish if
it
should not be required and this is a huge amount of code to work through
anyway. I know I could possible avoid the issue if I used selectall_* but
here
again, I'd have to check a lot of code to make this change.
Is this a bug in DBD::DB2?
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Oracle schema names: sqlplus 'v' Perl
Just for the sake of completeness, are you logging in as 'SYSTEM' in your perl script? Or are you logging in as a user which might not have permission to see the table? -Chris In his original post, he did show that he was logged in, at least on sqlplus, as system. I assumed he was in DBD::Oracle as well. Perhaps I shouldn't have? I took a brief look at sub table_info in Oracle.pm and the base query looks like this: SELECT * FROM ( SELECT /*+ RULE*/ NULL TABLE_CAT , t.OWNER TABLE_SCHEM , t.TABLE_NAME TABLE_NAME , decode(t.OWNER , 'SYS', 'SYSTEM ' , 'SYSTEM' , 'SYSTEM ' , '' ) || t.TABLE_TYPE TABLE_TYPE , c.COMMENTS REMARKS FROM ALL_TAB_COMMENTS c , ALL_CATALOG t WHERE c.OWNER (+) = t.OWNER AND c.TABLE_NAME (+) = t.TABLE_NAME AND c.TABLE_TYPE (+) = t.TABLE_TYPE ) with some rules after to determine what chunks of the where clause to add to it. I haven't traced it all the way through, but I suggest sticking a debug warning in there to let you know what is actually getting executed. Something like: warn $Sql; Right before: my $sth = $dbh-prepare($Sql) or return undef; $sth-execute or return undef; $sth; } Then examine STDOUT to see what is actually getting executed, or at least attempted to. I notice that if it fails prepare, it just returns undef. Also, if nothing gets written to STDOUT, then it's probably bailing on one of the conditionals before it gets this far. -- -- The darkest places in hell are reserved for those who maintain their neutrality in times of moral crisis. Dante Alighieri (1265 - 1321) They who would give up an essential liberty for temporary security, deserve neither liberty or security. Benjamin Franklin Our lives begin to end the day we become silent about things that matter. Martin Luther King The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment IV to the Constitution of the United States --
Re: Oracle schema names: sqlplus 'v' Perl
On Fri, 9 Jun 2006 15:13:04 -0400, Jeffrey Seger wrote: Hi Jeffrey et al Just for the sake of completeness, are you logging in as 'SYSTEM' in your perl script? Or are you logging in as a user which might not have permission to see the table? I'm logging in as 'system' to create all tables, and populate some static tables, so I /must/ be able to see them. In his original post, he did show that he was logged in, at least on sqlplus, as system. I assumed he was in DBD::Oracle as well. Perhaps I shouldn't have? My apologies. I should have said this before: I'm using the ODBC driver Oracle installs when Oracle itself is installed (this in under Windows). Also, it's noon Saturday here now, and Monday's a public holiday, so I'll try sql trace Real Soon Now. Thanx for the suggestion. I don't expect it to help, since I know what I'm sending, I just don't know what I /should be/ sending... -- Cheers Ron Savage, [EMAIL PROTECTED] on 10/06/2006 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
